java JDBC資料庫連接封裝和執行個體代碼__資料庫

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);     }}