背景：
由于业务关系，线上数据库订单表量太大，于是将该表按照月份来切分为多个表并转移到另一个服务器的数据库上，但前段时间客户有个需求，就是需要查找历史数据，于是要在程序上配置多数据源以符合客户需求，由于之前没弄过，而且对SSM框架也不算太了解，再加上程序之前已经配置了数据源，新的数据源要兼容旧的数据源配置方式，在网上找的很多资料都是新项目配置多数据源，而且如果要按照那种方式的话项目需要改动量超大，所以并不适合使用，经过几天的查找资料以及实践之后终于找到了合适的方法，于是再次作个记录，以下部分源码参考至其他文章
1、配置多数据源
相关参数读者按需求调整，不一定全都配上
#database config here#
#数据源一
db_local.url=jdbc:mysql://127.0.0.1:3307/demo
db_local.username=root
db_local.password=root
db_local.driverClassName=com.mysql.jdbc.Driver
db_local.connectionProperties=useUnicode=true;characterEncoding=UTF8;rewriteBatchedStatements=true;socketTimeout=180000;autoReconnect=true;autoReconnectForPools=true;zeroDateTimeBehavior=convertToNull
db_local.defaultAutoCommit=false
db_local.initialSize=0
db_local.maxTotal=20
db_local.maxIdle=5
db_local.minIdle=2
db_local.maxWaitMillis=10000
db_local.validationQuery=select 1
db_local.testOnBorrow=true
db_local.testWhileIdle=false
db_local.testOnReturn=false
db_local.timeBetweenEvictionRunsMillis=120000
db_local.minEvictableIdleTimeMillis=300000
#数据源二
db_test.url=jdbc:mysql://127.0.0.1:3308/demo
db_test.username=root
db_test.password=root
db_test.driverClassName=com.mysql.jdbc.Driver
db_test.connectionProperties=useUnicode=true;characterEncoding=UTF8;rewriteBatchedStatements=true;socketTimeout=180000;autoReconnect=true;autoReconnectForPools=true;zeroDateTimeBehavior=convertToNull
db_test.defaultAutoCommit=false
db_test.initialSize=0
db_test.maxTotal=20
db_test.maxIdle=5
db_test.minIdle=2
db_test.maxWaitMillis=10000
db_test.validationQuery=select 1
db_test.testOnBorrow=true
db_test.testWhileIdle=false
db_test.testOnReturn=false
db_test.timeBetweenEvictionRunsMillis=120000
db_test.minEvictableIdleTimeMillis=300000
2、在spring-context.xml文件中配置连接
<!-- 加载数据库配置文件 -->
<context:property-placeholder location="classpath:conf/db.properties"
ignore-unresolvable="true" />
<!-- 数据源一数据库连接池配置 -->
<bean id="dataSourceLocal" class="org.apache.commons.dbcp2.BasicDataSource"
destroy-method="close">
<property name="url" value="${db_local.url}" />
<property name="username" value="${db_local.username}" />
<property name="password" value="${db_local.password}" />
<property name="driverClassName" value="${db_local.driverClassName}" />
<property name="connectionProperties" value="${db_local.connectionProperties}" />
<property name="defaultAutoCommit" value="${db_local.defaultAutoCommit}" />
<property name="initialSize" value="${db_local.initialSize}" />
<property name="maxTotal" value="${db_local.maxTotal}" />
<property name="minIdle" value="${db_local.minIdle}" />
<property name="maxIdle" value="${db_local.maxIdle}" />
<property name="maxWaitMillis" value="${db_local.maxWaitMillis}" />
<property name="validationQuery" value="${db_local.validationQuery}" />
<property name="testOnBorrow" value="${db_local.testOnBorrow}" />
<property name="testOnReturn" value="${db_local.testOnReturn}" />
<property name="testWhileIdle" value="${db_local.testWhileIdle}" />
<property name="timeBetweenEvictionRunsMillis" value="${db_local.timeBetweenEvictionRunsMillis}" />
<property name="minEvictableIdleTimeMillis" value="${db_local.minEvictableIdleTimeMillis}" />
</bean>
<!-- 数据源二数据库连接池配置 -->
<bean id="dataSourceTest" class="org.apache.commons.dbcp2.BasicDataSource"
destroy-method="close">
<property name="url" value="${db_test.url}" />
<property name="username" value="${db_test.username}" />
<property name="password" value="${db_test.password}" />
<property name="driverClassName" value="${db_test.driverClassName}" />
<property name="connectionProperties" value="${db_test.connectionProperties}" />
<property name="defaultAutoCommit" value="${db_test.defaultAutoCommit}" />
<property name="initialSize" value="${db_test.initialSize}" />
<property name="maxTotal" value="${db_test.maxTotal}" />
<property name="minIdle" value="${db_test.minIdle}" />
<property name="maxIdle" value="${db_test.maxIdle}" />
<property name="maxWaitMillis" value="${db_test.maxWaitMillis}" />
<property name="validationQuery" value="${db_test.validationQuery}" />
<property name="testOnBorrow" value="${db_test.testOnBorrow}" />
<property name="testOnReturn" value="${db_test.testOnReturn}" />
<property name="testWhileIdle" value="${db_test.testWhileIdle}" />
<property name="timeBetweenEvictionRunsMillis" value="${db_test.timeBetweenEvictionRunsMillis}" />
<property name="minEvictableIdleTimeMillis" value="${db_test.minEvictableIdleTimeMillis}" />
</bean>
在spring-context.xml文件中配置sessionFactory
<!-- 数据源一链接工厂 -->
<bean id="sqlSessionFactoryLocal" class="org.mybatis.spring.SqlSessionFactoryBean"
p:dataSource-ref="dataSourceLocal" p:configLocation="classpath:mybatis/configuration.xml" />
<!-- 数据源二链接工厂 -->
<bean id="sqlSessionFactoryTest" class="org.mybatis.spring.SqlSessionFactoryBean"
p:dataSource-ref="dataSourceTest" p:configLocation="classpath:mybatis/configurationTest.xml" />
4、在spring-context.xml文件中配置自动扫包，不同数据源使用不同路径
<!-- Mybatis自动扫包,使用这种方式可以只写dao接口不写实现类 -->
<!--数据源一-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.letv.shop.demoWeb.dao" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryLocal" />
</bean>
<!--数据源二-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.letv.shop.demoWeb.dao2" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryTest" />
</bean>
5、在需要用到的service文件中加上如下配置：
“@Transactional(value = "transactionManagerTest"”中的value不是必填项，但是默认是spring配置中的的最上面的哪个配置，如果有多个数据源，建议加上这个value。
@Autowired
private BizEventDao bizEventDao;
@Autowired
private BizEventTestDao bizEventTestDao;
@Override
@Transactional(value = "transactionManagerLocal", rollbackFor = Exception.class)
public void addBizEvent(BizEvent bizEvent) {
bizEvent.setBizKey("1");
bizEventDao.addBizEvent(bizEvent);
}
@Transactional(value = "transactionManagerTest", rollbackFor = Exception.class)
public void testDao(BizEvent bizEvent) {
bizEvent.setBizKey("1");
bizEventTestDao.addBizEvent(bizEvent);
}
当然，如果该service中都是使用该数据源的，那么可以将注解直接放到类名上
至此多数据源已配置完毕！
网友评论