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