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

java哥 阅读:257 2021-03-31 21:42:35 评论:0

源码: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); 	 
    } 
	 
 
	 
	 
	 
	 
	 
	 
	 
	 
	 
	 
	 
	 
	 
	 
	 
 
} 



声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

发表评论
搜索
排行榜
KIKK导航

KIKK导航

关注我们