java JDBC数据库连接封装和实例代码

系统管理员 2023-10-17 09:07 206阅读 0赞

java jdbc功能代码封装：

package com.common.common.util.mysql;
    import java.sql.CallableStatement;  
    import java.sql.Connection;  
    import java.sql.DriverManager;  
    import java.sql.PreparedStatement;  
    import java.sql.ResultSet;  
    import java.sql.ResultSetMetaData;  
    import java.sql.SQLException;  
    import java.util.ArrayList;  
    import java.util.HashMap;  
    import java.util.List;  
    import java.util.Map;  
    
    public class ConnectionDB {
    	/** 
         * 数据库驱动类名称 
         */  
        private static final String DRIVER = "com.mysql.jdbc.Driver";  
      
        /** 
         * 连接字符串 
         */  
        private static final String URLSTR = "jdbc:mysql://*****?useUnicode=true&characterEncoding=utf8";  
      
        /** 
         * 用户名 
         */  
        private static final String USERNAME = "****";  
      
        /** 
         * 密码 
         */  
        private static final String USERPASSWORD = "****";  
      
        /** 
         * 创建数据库连接对象 
         */  
        private Connection connnection = null;  
      
        /** 
         * 创建PreparedStatement对象 
         */  
        private PreparedStatement preparedStatement = null;  
          
        /** 
         * 创建CallableStatement对象 
         */  
        private CallableStatement callableStatement = null;  
      
        /** 
         * 创建结果集对象 
         */  
        private ResultSet resultSet = null;  
      
        static {  
            try {  
                // 加载数据库驱动程序  
                Class.forName(DRIVER);  
            } catch (ClassNotFoundException e) {  
                System.out.println("加载驱动错误");  
                System.out.println(e.getMessage());  
            }  
        }  
      
        /** 
         * 建立数据库连接 
         * @return 数据库连接 
         */  
        public Connection getConnection() {  
            try {  
                // 获取连接  
                connnection = DriverManager.getConnection(URLSTR, USERNAME,  
                        USERPASSWORD);  
            } catch (SQLException e) {  
                System.out.println(e.getMessage());  
            }  
            return connnection;  
        }  
      
        /** 
         * insert update delete SQL语句的执行的统一方法 
         * @param sql SQL语句 
         * @param params 参数数组，若没有参数则为null 
         * @return 受影响的行数 
         */  
        public int executeUpdate(String sql, Object[] params) {  
            // 受影响的行数  
            int affectedLine = 0;  
              
            try {  
                // 获得连接  
                connnection = this.getConnection();  
                // 调用SQL   
                preparedStatement = connnection.prepareStatement(sql);  
                  
                // 参数赋值  
                if (params != null) {  
                    for (int i = 0; i < params.length; i++) {  
                        preparedStatement.setObject(i + 1, params[i]);  
                    }  
                }  
                  
                // 执行  
                affectedLine = preparedStatement.executeUpdate();  
      
            } catch (SQLException e) {  
                System.out.println(e.getMessage());  
            } finally {  
                // 释放资源  
                closeAll();  
            }  
            return affectedLine;  
        }  
      
        /** 
         * SQL 查询将查询结果直接放入ResultSet中 
         * @param sql SQL语句 
         * @param params 参数数组，若没有参数则为null 
         * @return 结果集 
         */  
        private ResultSet executeQueryRS(String sql, Object[] params) {  
            try {  
                // 获得连接  
                connnection = this.getConnection();  
                  
                // 调用SQL  
                preparedStatement = connnection.prepareStatement(sql);  
                  
                // 参数赋值  
                if (params != null) {  
                    for (int i = 0; i < params.length; i++) {  
                        preparedStatement.setObject(i + 1, params[i]);  
                    }  
                }  
                  
                // 执行  
                resultSet = preparedStatement.executeQuery();  
      
            } catch (SQLException e) {  
                System.out.println(e.getMessage());  
            }  
      
            return resultSet;  
        }  
      
        /** 
         * 获取结果集，并将结果放在List中 
         *  
         * @param sql 
         *            SQL语句 
         * @return List 
         *                       结果集 
         */  
        public List<Object> excuteQuery(String sql, Object[] params) {  
            // 执行SQL获得结果集  
            ResultSet rs = executeQueryRS(sql, params);  
              
            // 创建ResultSetMetaData对象  
            ResultSetMetaData rsmd = null;  
              
            // 结果集列数  
            int columnCount = 0;  
            try {  
                rsmd = rs.getMetaData();  
                  
                // 获得结果集列数  
                columnCount = rsmd.getColumnCount();  
            } catch (SQLException e1) {  
                System.out.println(e1.getMessage());  
            }  
      
            // 创建List  
            List<Object> list = new ArrayList<Object>();  
      
            try {  
                // 将ResultSet的结果保存到List中  
                while (rs.next()) {  
                    Map<String, Object> map = new HashMap<String, Object>();  
                    for (int i = 1; i <= columnCount; i++) {  
                        map.put(rsmd.getColumnLabel(i), rs.getObject(i));  
                    }  
                    list.add(map);  
                }  
            } catch (SQLException e) {  
                System.out.println(e.getMessage());  
            } finally {  
                // 关闭所有资源  
                closeAll();  
            }  
      
            return list;  
        }  
          
        /** 
         * 存储过程带有一个输出参数的方法 
         * @param sql 存储过程语句 
         * @param params 参数数组 
         * @param outParamPos 输出参数位置 
         * @param SqlType 输出参数类型 
         * @return 输出参数的值 
         */  
        public Object excuteQuery(String sql, Object[] params,int outParamPos, int SqlType) {  
            Object object = null;  
            connnection = this.getConnection();  
            try {  
                // 调用存储过程  
                callableStatement = connnection.prepareCall(sql);  
                  
                // 给参数赋值  
                if(params != null) {  
                    for(int i = 0; i < params.length; i++) {  
                        callableStatement.setObject(i + 1, params[i]);  
                    }  
                }  
                  
                // 注册输出参数  
                callableStatement.registerOutParameter(outParamPos, SqlType);  
                  
                // 执行  
                callableStatement.execute();  
                  
                // 得到输出参数  
                object = callableStatement.getObject(outParamPos);  
                  
            } catch (SQLException e) {  
                System.out.println(e.getMessage());  
            } finally {  
                // 释放资源  
                closeAll();  
            }  
              
            return object;  
        }  
      
        /** 
         * 关闭所有资源 
         */  
        private void closeAll() {  
            // 关闭结果集对象  
            if (resultSet != null) {  
                try {  
                    resultSet.close();  
                } catch (SQLException e) {  
                    System.out.println(e.getMessage());  
                }  
            }  
      
            // 关闭PreparedStatement对象  
            if (preparedStatement != null) {  
                try {  
                    preparedStatement.close();  
                } catch (SQLException e) {  
                    System.out.println(e.getMessage());  
                }  
            }  
              
            // 关闭CallableStatement 对象  
            if (callableStatement != null) {  
                try {  
                    callableStatement.close();  
                } catch (SQLException e) {  
                    System.out.println(e.getMessage());  
                }  
            }  
      
            // 关闭Connection 对象  
            if (connnection != null) {  
                try {  
                    connnection.close();  
                } catch (SQLException e) {  
                    System.out.println(e.getMessage());  
                }  
            }     
        }  
    
    }

mysql 实例代码调用：

package com.common.common.util.mysql;
    
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import com.smart.entity.HomeDevice;
    import com.smart.entity.HomeDeviceAlarm;
    import com.smart.entity.HomeDeviceAttrStatu;
    import com.smart.entity.HomeDeviceCommand;
    import com.smart.entity.HomeDeviceLog;
    
    public class ConnectionDBUtil {
    	
    	public List<HomeDevice> selectAlarmSubDeviceByDevUIDTypeCode(HomeDevice parame){
    		List<HomeDevice> list =new ArrayList<HomeDevice>();
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.getGatewayId(),parame.getDeviceUid(),parame.getTypeCode()};
    		List<Object> set= util.excuteQuery("select * from wlsq_data.home_device a where a.del_flag=0 AND a.gateway_id= ? and a.device_uid = ? and a.type_code=?", objs);
    		if(set !=null && set.size() >0){
    			for(Object obj:set){
    				HomeDevice homeDevice =new HomeDevice();
    				HashMap<String, Object> map =(HashMap<String, Object>)obj;				
    				homeDevice.setId((Integer)map.get("id"));
    				homeDevice.setTypeCode((Integer)map.get("type_code"));
    				homeDevice.setDeviceUid((String)map.get("device_uid"));
    				homeDevice.setDeviceName((String)map.get("device_name"));
    				homeDevice.setStatus((String)map.get("status"));
    				homeDevice.setGatewayId((Integer)map.get("gateway_id"));
    				homeDevice.setCreatorId((String)map.get("creator_id"));
    				homeDevice.setCreatedTime((Date)map.get("created_time"));
    				homeDevice.setUpTime((Date)map.get("up_time"));
    				homeDevice.setDelFlag((Integer)map.get("del_flag"));
    				list.add(homeDevice);
    			}
    			
    		}		
    		return list;		
    	}
    	
    	public List<HomeDeviceCommand> selectDevCommand(HomeDeviceCommand parame){
    		List<HomeDeviceCommand> list =new ArrayList<HomeDeviceCommand>();
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.getTypeCode()};
    		List<Object> set= util.excuteQuery("select * from wlsq_data.home_device_command where del_flag=0 and type_code = ?", objs);
    		if(set !=null && set.size() >0){
    			for(Object obj:set){
    				HomeDeviceCommand homeDeviceCommand =new HomeDeviceCommand();
    				HashMap<String, Object> map =(HashMap<String, Object>)obj;				
    				homeDeviceCommand.setId((Integer)map.get("id"));
    				homeDeviceCommand.setTypeCode((Integer)map.get("type_code"));
    				homeDeviceCommand.setCommand((String)map.get("command"));
    				homeDeviceCommand.setCommandDesc((String)map.get("command_desc"));
    				homeDeviceCommand.setCreatorId((String)map.get("creator_id"));
    				homeDeviceCommand.setCreatedTime((Date)map.get("created_time"));
    				homeDeviceCommand.setUpTime((Date)map.get("up_time"));
    				homeDeviceCommand.setDelFlag((Integer)map.get("del_flag"));
    				homeDeviceCommand.setCommandType((String)map.get("command_type"));
    				homeDeviceCommand.setCommandCode((String)map.get("command_code"));				
    				list.add(homeDeviceCommand);
    			}			
    		}		
    		return list;
    	}
    	
    	public void SaveAlarmAttrStatus(int device_id,Integer type_code,String command_type,String command_code,String comand,String command_desc){
    		//保存到设备属性表中
    		HomeDeviceAttrStatu record = new HomeDeviceAttrStatu();
    		record.setDeviceId(device_id);
    		record.setTypeCode(type_code);
    		record.setCommandType(command_type);
    		record.setCommandCode(command_code);
    		record.setComand(comand);
    		record.setComandDesc(command_desc);
    		record.setCreatedTime(new Date());
    		record.setUpTime(new Date());
    		record.setDelFlag(0);
    		
    		if(selectExistsDeviceAttr(record)>0){ 
    			updateDeviceAttr(record); 
    		}else{
    			insertDeviceAttr(record);
    		}		
    	}
    	
    	public int selectExistsDeviceAttr(HomeDeviceAttrStatu parame){
    		int result =0;
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.getDeviceId(),parame.getTypeCode(),parame.getCommandType(),parame.getCommandCode()};
    		List<Object> set= util.excuteQuery("select  count(id) as id from wlsq_data.home_device_attr_statu  where device_id = ? and type_code = ? and command_type = ? and command_code = ?", objs);
    		if(set !=null && set.size() >0){
    			for(Object obj:set){			
    				HashMap<String, Object> map =(HashMap<String, Object>)obj;				
    				result = (Integer)map.get("id");
    			}			
    		}		
    		return result;
    	}
    	
    	public void updateDeviceAttr(HomeDeviceAttrStatu parame){
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.getComand(),parame.getComandDesc(),parame.getUpTime(),parame.getDelFlag(),parame.getDeviceId(),parame.getTypeCode(),parame.getCommandType(),parame.getCommandCode()};
    		String sql ="update wlsq_data.home_device_attr_statu  set comand = ?, comand_desc = ?, up_time = ?, del_flag = ? where device_id = ? and type_code = ? and command_type = ? and command_code = ?"; 
    		util.executeUpdate(sql, objs); 		
    	}
    	
    	public void insertDeviceAttr(HomeDeviceAttrStatu parame){
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.getDeviceId(),parame.getTypeCode(),parame.getCommandType(),parame.getCommandCode(),parame.getComand(),parame.getComandDesc(),parame.getCreatedTime(),parame.getDelFlag(),parame.getUpTime()};
    		String sql = "insert into wlsq_data.home_device_attr_statu (device_id, type_code, command_type, command_code, comand, comand_desc, created_time, del_flag, up_time) values (?, ?, ?, ?, ?, ?, ?, ?,?)";   
    		util.executeUpdate(sql, objs); 		
    	}
    	
    	public void SaveDeviceLog(int gatewayId,int deviceId,String command,String commandDesc,String gatewayUid,String deviceUid,String deviceName){
    		HomeDeviceLog devLogObj = new HomeDeviceLog();
    		devLogObj.setGatewayUid(gatewayUid);
    		devLogObj.setDeviceUid(deviceUid);
    		devLogObj.setDeviceName(deviceName);
    		devLogObj.setGatewayId(gatewayId);
    		devLogObj.setDeviceId(deviceId);
    		devLogObj.setMsgType("log");
    		devLogObj.setMsgCommand(command);
    		devLogObj.setMsgContent(commandDesc);
    		devLogObj.setCreatedTime(new Date());
    		devLogObj.setUpTime(new Date());
    		devLogObj.setDelFlag(0);
    		insertDeviceLog(devLogObj);		
    	}
    	public void insertDeviceLog(HomeDeviceLog parame){
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.getGatewayId(),parame.getDeviceId(),parame.getMsgType(),parame.getMsgContent(),parame.getMsgCommand(),parame.getCreatedTime(),parame.getDelFlag()};
    		String sql = " insert into wlsq_data.home_device_log (gateway_id, device_id, msg_type, msg_content, msg_command, created_time,del_flag) values (?,?,?,?,?,?,?)";  
    		util.executeUpdate(sql, objs); 
    	}
    	
    	public HomeDeviceAlarm SaveDeviceAlarmLog(int gatewayId,int deviceId,String command,String commandDesc,String msgId){
    		HomeDeviceAlarm devAlarmObj = new HomeDeviceAlarm();
    		devAlarmObj.setGatewayId(gatewayId);
    		devAlarmObj.setDeviceId(deviceId);
    		devAlarmObj.setMsgType("alarm");
    		devAlarmObj.setMsgCommand(command);
    		devAlarmObj.setMsgContent(commandDesc);
    		devAlarmObj.setStatus(0);
    		devAlarmObj.setValid(0);
    		devAlarmObj.setCreatedTime(new Date());
    		devAlarmObj.setDelFlag(0);
    		devAlarmObj.setReportId(msgId);
    		
    		insertDeviceAlarmLog(devAlarmObj);
    		
    		return devAlarmObj;		
    	}
    	
    	public void insertDeviceAlarmLog(HomeDeviceAlarm parame){
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.getGatewayId(),parame.getDeviceId(),parame.getMsgType(),parame.getMsgContent(),parame.getMsgCommand(),parame.getValid(),parame.getStatus(),parame.getCreatedTime(),parame.getDelFlag(),parame.getReportId()};
    		String sql = "insert into wlsq_data.home_device_alarm (gateway_id, device_id,msg_type, msg_content, msg_command,valid, status, created_time, del_flag,up_time,report_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?)";    
        	util.executeUpdate(sql, objs); 		
    	}
    	
    	
    	public String selectGatewayAlias(String gateway_uid){
    		String result ="";
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{gateway_uid};
    		List<Object> set= util.excuteQuery("select gateway_alias from wlsq_data.alarm_install_position where gateway_uid=? and del_flag=0 ", objs);
    		if(set !=null && set.size() >0){
    			for(Object obj:set){			
    				HashMap<String, Object> map =(HashMap<String, Object>)obj;				
    				result = (String)map.get("gateway_alias");
    			}			
    		}		
    		return result;		
    	}
    	
    	private void UpdateRestOnLineDevice(int gatewayId,String deviceId,Integer typeCode) {
    		HomeDevice restOnLineDevObj = new HomeDevice();
    		restOnLineDevObj.setGatewayId(gatewayId);
    		restOnLineDevObj.setTypeCode(typeCode);
    		restOnLineDevObj.setDeviceUid(deviceId);
    		restOnLineDevObj.setStatus("1");
    		restOnLineDevObj.setUpTime(new Date());
    		
    		updateDeviceOffLine(restOnLineDevObj);		
    	}
    	
    	public void updateDeviceOffLine(HomeDevice parame){
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.getStatus(),parame.getUpTime(),parame.getGatewayId(),parame.getDeviceUid()};
    		String sql = "update wlsq_data.home_device a set a.status=?,a.up_time=? where a.gateway_id= ? and a.device_uid=? and a.del_flag = 0"; 			   
        	util.executeUpdate(sql, objs); 		
    	}
    	
    	public void deleteDeviceByUId(Map<String, Object> parame){
    		ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.get("device_uid"),parame.get("gateway_uid")};
    		String sql = "update wlsq_data.home_device a set a.del_flag=1,a.up_time=NOW() where a.device_uid = ? and gateway_id = (select id from wlsq_data.home_gateway where gateway_uid = ?)";		
    		util.executeUpdate(sql, objs); 			
    	}
    	
    	public void UpdateOffLineDevice(int gatewayId,String deviceId,Integer typeCode) {
    		HomeDevice offLineDevObj = new HomeDevice();
    		offLineDevObj.setGatewayId(gatewayId);
    		offLineDevObj.setTypeCode(typeCode);
    		offLineDevObj.setDeviceUid(deviceId);
    		offLineDevObj.setStatus("0");
    		offLineDevObj.setUpTime(new Date());
    		
    		updateDeviceOffLine(offLineDevObj);//更新掉线设备状态
    	}
    	
        public void updateGatewayStatus(Map<String, Object> parame){
        	ConnectionDB util = new ConnectionDB();
    		Object[] objs =new Object[]{parame.get("status"),parame.get("gateway_uid")};
    		String sql = "update  wlsq_data.home_gateway set status = ?, up_time = NOW() WHERE gateway_uid = ? and del_flag = 0";			
    		util.executeUpdate(sql, objs); 	
        }
    	
    
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    
    }