首页 > 程序开发 > 软件开发 > 其他 >

DruidDataSource 通过编码方式实现数据库读写分离。

2016-10-27

源码:DruidDataSourceFactory创建数据库连接DataSource package com common common util mysql; import java util Properties; import javax sql DataSource; import com alibaba druid pool DruidDataSou

源码:DruidDataSourceFactory创建数据库连接DataSource

package com.common.common.util.mysql;  
  
import java.util.Properties;  
import javax.sql.DataSource;  
import com.alibaba.druid.pool.DruidDataSourceFactory;  
  
public class DataSourceUtil {  
      /** 使用配置文件构建Druid数据源. */    
    public static final int DRUID_MYSQL_MASTER = 1;    
      /** 使用配置文件构建Druid数据源. */    
    public static final int DRUID_MYSQL_SALVE = 2;    
     
      
    public static final DataSource getDataSource(int sourceType) throws Exception {   
         DataSource dataSource = null;   
          
         Properties p =new Properties();  
         p.put("initialSize", "1");  
         p.put("minIdle", "1");  
         p.put("maxActive", "20");  
         p.put("maxWait", "60000");  
         p.put("timeBetweenEvictionRunsMillis", "60000");  
         p.put("minEvictableIdleTimeMillis", "300000");             
         p.put("validationQuery", "SELECT 'x' from dual");  
         p.put("testWhileIdle", "true");  
         p.put("testOnBorrow", "false");  
         p.put("testOnReturn", "false");  
         p.put("poolPreparedStatements", "true");  
         p.put("maxPoolPreparedStatementPerConnectionSize", "20");  
         p.put("filters", "stat");  
                             
           
       
        switch (sourceType) {    
        case DRUID_MYSQL_MASTER:  
          p.put("url", "jdbc:mysql://***?useUnicode=true&characterEncoding=utf8");  
          p.put("username", "***");  
          p.put("password", "***");  
              
          dataSource = DruidDataSourceFactory.createDataSource(p);        
          break;    
        case DRUID_MYSQL_SALVE:    
           p.put("url", "jdbc:mysql://***?useUnicode=true&characterEncoding=utf8");  
           p.put("username", "***");  
           p.put("password", "***");  
              
          dataSource = DruidDataSourceFactory.createDataSource(p);          
          break;        
        }    
        return dataSource;    
    }    
      
  
}
封装读写数据库的增删改查操作。
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 Connection connnection = null;    
    
    /**   
     * 创建PreparedStatement对象   
     */    
    private PreparedStatement preparedStatement = null;    
        
    /**   
     * 创建CallableStatement对象   
     */    
    private CallableStatement callableStatement = null;    
    
    /**   
     * 创建结果集对象   
     */    
    private ResultSet resultSet = null;    
    
  
    
    /**   
     * 建立数据库连接   
     * @return 数据库连接   
     * @throws Exception   
     */    
    public Connection getConnection(int type) throws Exception {    
        try {    
              
            // 获取连接    
            connnection =DataSourceUtil.getDataSource(type).getConnection();  
        } catch (SQLException e) {    
            System.out.println(e.getMessage());    
        }    
        return connnection;    
    }    
    
    /**   
     * insert update delete SQL语句的执行的统一方法   
     * @param sql SQL语句   
     * @param params 参数数组,若没有参数则为null   
     * @return 受影响的行数   
     * @throws Exception   
     */    
    public int executeUpdate(String sql, Object[] params){    
        // 受影响的行数    
        int affectedLine = 0;    
            
        try {    
            // 获得连接    
            connnection = this.getConnection(1);    
            // 调用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 (Exception e) {    
            System.out.println(e.getMessage());    
        } finally {    
            // 释放资源    
            closeAll();    
        }    
        return affectedLine;    
    }    
    
    /**   
     * SQL 查询将查询结果直接放入ResultSet中   
     * @param sql SQL语句   
     * @param params 参数数组,若没有参数则为null   
     * @return 结果集   
     * @throws Exception   
     */    
    private ResultSet executeQueryRS(String sql, Object[] params){    
        try {    
            // 获得连接    
            connnection = this.getConnection(2);    
                
            // 调用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 (Exception e) {    
            System.out.println(e.getMessage());    
        }    
    
        return resultSet;    
    }    
    
    /**   
     * 获取结果集,并将结果放在List中   
     *    
     * @param sql   
     *            SQL语句   
     * @return List   
     *                       结果集   
     * @throws Exception   
     */    
    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 输出参数的值   
     * @throws Exception   
     */    
    public Object excuteQuery(String sql, Object[] params,int outParamPos, int SqlType){    
        Object object = null;  
        try {   
            connnection = this.getConnection(1);    
         
            // 调用存储过程    
            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 (Exception 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());    
            }    
        }       
    }    
  
} 

实例代码封装:
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);    
    }
 }
相关文章
最新文章
热点推荐