基于druid和spring的动态数据库以及读写分离

spring与druid可以实现动态数据源，夸库查询，读写分离等功能。现在说一下配置：

1、需要配置多个spring数据源

spring-data.xml

<!-- 动态数据源 -->
	<bean id="dynamicDataSource" class="com.myproject.common.db.util.DynamicDataSource">
		<!-- 通过key-value关联数据源 -->
		<property name="targetDataSources">
			<map>
				<entry value-ref="dataSourceWrite" key="dataSourceWrite"></entry>
				<entry value-ref="dataSourceRead" key="dataSourceRead"></entry>
			</map>
		</property>
		<property name="defaultTargetDataSource" ref="dataSourceWrite" />
	</bean>

	<!--mybatis与Spring整合 -->
	<bean id="sqlSessionFactory" name="sqlSessionFactory"
		class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="configLocation" value="classpath:mybatis.xml"></property>
		<property name="mapperLocations" value="classpath*:mapper/*.xml" />
		<property name="dataSource" ref="dynamicDataSource" />
	</bean>

	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>
	
	
	<bean id="transactionManager"  
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
        <property name="dataSource" ref="dynamicDataSource" />  
    </bean>  
    
    <tx:annotation-driven transaction-manager="transactionManager" /> 

	<!-- 数据源(DruidDataSource) -->
	<bean id="dataSourceWrite" class="com.alibaba.druid.pool.DruidDataSource"
		init-method="init" destroy-method="close">
		<property name="url" value="${urlOracle}" />
		<property name="username" value="${usernameOracle}" />
		<property name="password" value="${passwordOracle}" />
		<!-- 初始化连接大小 -->
		<property name="initialSize" value="5" />
		<!-- 连接池最大使用连接数量 -->
		<property name="maxActive" value="200" />
		<!-- 连接池最小空闲 -->
		<property name="minIdle" value="5" />
		<!-- 获取连接最大等待时间 -->
		<property name="maxWait" value="60000" />
		<!-- <property name="poolPreparedStatements" value="true" /> <property 
			name="maxPoolPreparedStatementPerConnectionSize" value="33" /> -->
		<!-- <property name="validationQuery" value="${jdbc.validationQuery}" /> -->
		<property name="testOnBorrow" value="false" />
		<property name="testOnReturn" value="false" />
		<property name="testWhileIdle" value="true" />
		<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="60000" />
		<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="25200000" />
		<!-- 打开removeAbandoned功能 -->
		<property name="removeAbandoned" value="true" />
		<!-- 1800秒，也就是30分钟 -->
		<property name="removeAbandonedTimeout" value="1800" />
		<!-- 关闭abanded连接时输出错误日志 -->
		<property name="logAbandoned" value="true" />
		<!-- 监控数据库 -->
		<!-- <property name="filters" value="mergeStat" /> -->
		<property name="filters" value="stat" />
		<property name="defaultAutoCommit" value="true" />
	</bean>
	
	<bean id="dataSourceRead" class="com.alibaba.druid.pool.DruidDataSource"
		init-method="init" destroy-method="close">
		<property name="url" value="${urlMysql}" />
		<property name="username" value="${usernameMysql}" />
		<property name="password" value="${passwordMysql}" />
		<!-- 初始化连接大小 -->
		<property name="initialSize" value="5" />
		<!-- 连接池最大使用连接数量 -->
		<property name="maxActive" value="200" />
		<!-- 连接池最小空闲 -->
		<property name="minIdle" value="5" />
		<!-- 获取连接最大等待时间 -->
		<property name="maxWait" value="60000" />
		<!-- <property name="poolPreparedStatements" value="true" /> <property 
			name="maxPoolPreparedStatementPerConnectionSize" value="33" /> -->
		<!-- <property name="validationQuery" value="${jdbc.validationQuery}" /> -->
		<property name="testOnBorrow" value="false" />
		<property name="testOnReturn" value="false" />
		<property name="testWhileIdle" value="true" />
		<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="60000" />
		<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="25200000" />
		<!-- 打开removeAbandoned功能 -->
		<property name="removeAbandoned" value="true" />
		<!-- 1800秒，也就是30分钟 -->
		<property name="removeAbandonedTimeout" value="1800" />
		<!-- 关闭abanded连接时输出错误日志 -->
		<property name="logAbandoned" value="true" />
		<!-- 监控数据库 -->
		<!-- <property name="filters" value="mergeStat" /> -->
		<property name="filters" value="stat" />
		<property name="defaultAutoCommit" value="true" />
	</bean>

 2、需要写一个DynamicDataSource类继承AbstractRoutingDataSource，并实现determineCurrentLookupKey方法

 

 

public class DynamicDataSource extends AbstractRoutingDataSource {  
	  
    
    /** 
     *  
     * override determineCurrentLookupKey 
     * <p> 
     * Title: determineCurrentLookupKey 
     * </p> 
     * <p> 
     * Description: 自动查找datasource 
     * </p> 
     *  
     * @return 
     */  
    @Override  
    protected Object determineCurrentLookupKey() {  
        return DBContextHolder.getDSType();  
    }  
  
}  

 

 

3、参考spring事务管理，使用线程变量来切换数据源

 

public class DBContextHolder {

	/**
	 * 线程threadlocal
	 */
	private static ThreadLocal<String> contextHolder = new ThreadLocal<>();
	private static Logger logger = LoggerFactory
			.getLogger(DBContextHolder.class);

	public static String getDSType() {
		try {

		} catch (Exception e) {
			e.printStackTrace();
			logger.error("get DBTYPE faild with error:[" + e.getMessage() + "]");
		}

		String db = contextHolder.get();
		 if (db == null) {
		 db =UrlConnect.getKey(ConfigHelper.getToWriteKey());// 默认是读写库
		 }
		return db;
	}

	/**
	 * 
	 * 设置本线程的dbtype
	 * 
	 * @param str
	 * @see [相关类/方法](可选)
	 * @since [产品/模块版本](可选)
	 */
	public static boolean setDSType(String str) {
		try {
			clearDBType();
			if (str != null&&!str.equals("")) {
				contextHolder.set(str);
				logger.info("change thread[" + str + "] success!");
				return true;
			} else {
				logger.info("change thread[" + str + "] faild!");
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("change thread[" + str + "] faild!");
			return false;
		}
	}

	/**
	 * clearDBType
	 * 
	 * @Title: clearDBType
	 * @Description: 清理连接类型
	 */
	public static void clearDSType() {
		contextHolder.remove();
	}
}

 

 

4、在dao中切换数据源

@Repository
public class BaseDAO extends SqlSessionDaoSupport {
       @Resource
	private SqlSessionTemplate sqlSessionTemplate;
@Resource
	public void setSqlSessionTemplate(SqlSessionTemplate sqlSessionTemplate) {
		super.setSqlSessionTemplate(sqlSessionTemplate);
	}

public  <T> PageList<T> selectPublicListPage(String countSqlID,String sqlID,
			PageList<T> page, Object obj) {
		DBContextHolder.setDbType("dataSourceRead");
		//查询总数
		Integer total = this.getSqlSession().selectOne(countSqlID, obj);
		RowBounds rowBounds=new RowBounds(page.getFirstResult(),page.getPageSize());
		// 查询列表信息
		List<T> list = this.getSqlSession().selectList(
				sqlID, obj,rowBounds);
		page.setTotalRecord(total!=null?total:0);
		page.setDataSource(list);
		page.setTotalPage((total + page.getPageSize() - 1)
				/ page.getPageSize());
		return page;
	}

	public int insert(String sqlID, Object paramObj) {
		DBContextHolder.setDbType("dataSourceWrite");
		return this.getSqlSession().insert(sqlID, paramObj);
	}


}