摘要：在之前的开发中有很多场景用到了多数据源的情况，如在做数据冷热迁移的时候，将冷数据迁移到另一个库，查询冷数据时需要切换数据库；在和天猫京东等电商对接时，因为有多套系统在同时使用，在客户授权时，需要根据客户使用的版本，存储到对应的数据库中。基于此，在这里呢简单实现一个SSM系统的多数据源配置,手动切换数据源。在此基础上延伸一下，如果两个数据源是主从复制的，那么就可以实现读写分离。
1.项目搭建
可以参考本系列文章，博客地址：https://blog.csdn.net/caiqing116/article/details/84573166
或者直接下载项目，git地址：https://github.com/gitcaiqing/SSM_DEMO.git
2.搭建多个数据库服务
在这里呢我特地搭建了2个MySQL服务，端口号分别是3306，3308（方便后续读写分离直接使用 微笑），用于数据源切换演示。当然看官你也可以直接1个MySQL服务创建2个数据库实现。
搭建多个MySQL服务可参考：https://blog.csdn.net/caiqing116/article/details/84899680
创建数据库db_ssmdemo
CREATE DATABASE db_ssmdemo CHARACTER SET UTF8;
创建表tb_basic_user
CREATE TABLE `tb_basic_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`userId` varchar(32) DEFAULT NULL COMMENT '用户ID',
`utype` int(1) DEFAULT '0' COMMENT '用户类型 0管理员1普通用户',
`username` varchar(20) DEFAULT NULL COMMENT '用户名',
`password` varchar(100) DEFAULT NULL COMMENT 'MD5加密密码',
`headimg` varchar(200) DEFAULT NULL COMMENT '头像',
`realname` varchar(20) DEFAULT NULL COMMENT '真实姓名',
`sex` int(1) DEFAULT NULL COMMENT '性别',
`age` int(2) DEFAULT NULL COMMENT '年龄',
`mobile` varchar(20) DEFAULT NULL COMMENT '手机号',
`email` varchar(50) DEFAULT NULL COMMENT '邮件地址',
`credate` datetime DEFAULT NULL COMMENT '创建时间',
`upddate` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
3.项目配置
（1）config/jdbc.config配置
#连接驱动
jdbc.driverClassName=com.mysql.jdbc.Driver
#端口号3306的数据源
jdbc.url = jdbc\:mysql\://localhost\:3306/db_ssmdemo?useUnicode\=true&characterEncoding\=UTF-8&allowMultiQueries\=true
jdbc.username = root
jdbc.password = 123456
#端口号3308的数据源
jdbc.3308.url = jdbc\:mysql\://localhost\:3308/db_ssmdemo?useUnicode\=true&characterEncoding\=UTF-8&allowMultiQueries\=true
jdbc.3308.username = root
jdbc.3308.password = 123456
#定义初始连接数
jdbc.initialSize=2
#定义最大连接数
jdbc.maxActive=20
#定义最大空闲
jdbc.maxIdle=20
#定义最小空闲
jdbc.minIdle=1
#定义最长等待时间
jdbc.maxWait=60000
#验证数据库连接的有效性
jdbc.validationQuery=select 1
（2）spring/mybatis.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd">
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations">
<list>
<value>classpath:sql/*.xml</value>
</list>
</property>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.ssm.mapper" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate" scope="prototype">
<constructor-arg index="0" ref="sqlSessionFactory" />
</bean>
<!-- 使用annotation定义事务，使用cglib代理，解决同一service中事务方法相互调用的 嵌套事务失效问题 -->
<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>
<!--事务配置 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
（3）spring/dataAccessContext.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.0.xsd">
<description>数据库、事务配置</description>
<!-- 端口号3306的数据源-->
<bean id="dataSource3306" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="initialSize" value="${jdbc.initialSize}" />
<property name="maxActive" value="${jdbc.maxActive}" />
<property name="maxIdle" value="${jdbc.maxIdle}" />
<property name="minIdle" value="${jdbc.minIdle}" />
<property name="maxWait" value="${jdbc.maxWait}"></property>
<property name="validationQuery" value="${jdbc.validationQuery}" />
<!-- 监控数据库 -->
<!--<property name="filters" value="mergeStat" />-->
<property name="filters" value="stat" />
<property name="connectionProperties" value="druid.stat.mergeSql=true" />
</bean>
<!-- 端口号3308的数据源 -->
<bean id="dataSource3308" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.3308.url}" />
<property name="username" value="${jdbc.3308.username}" />
<property name="password" value="${jdbc.3308.password}" />
<property name="initialSize" value="${jdbc.initialSize}" />
<property name="maxActive" value="${jdbc.maxActive}" />
<property name="maxIdle" value="${jdbc.maxIdle}" />
<property name="minIdle" value="${jdbc.minIdle}" />
<property name="maxWait" value="${jdbc.maxWait}"></property>
<property name="validationQuery" value="${jdbc.validationQuery}" />
<!-- 监控数据库 -->
<!--<property name="filters" value="mergeStat" />-->
<property name="filters" value="stat" />
<property name="connectionProperties" value="druid.stat.mergeSql=true" />
</bean>
<!-- 数据源，需要自定义类继承AbstractRoutingDataSource，实现determineCurrentLookupKey -->
<bean id="dataSource" class="com.ssm.datasource.DynamicDataSource">
<!-- 设置默认数据源 -->
<property name="defaultTargetDataSource" ref="dataSource3306"></property>
<!-- 设置多个数据源，后台切换数据源key与这里key配置需要一致 -->
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="dataSource3306" value-ref="dataSource3306"/>
<entry key="dataSource3308" value-ref="dataSource3308"/>
</map>
</property>
</bean>
4.自定义类继承AbstractRoutingDataSource，实现determineCurrentLookupKey
首先我们来看下AbstractRoutingDataSource关键源码
package org.springframework.jdbc.datasource.lookup;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.util.Assert;
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
private Map<Object, Object> targetDataSources;
private Object defaultTargetDataSource;
private boolean lenientFallback = true;
private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
private Map<Object, DataSource> resolvedDataSources;
private DataSource resolvedDefaultDataSource;
//此方法是配置 <property name="targetDataSources">赋值map,后续切换数据源便是在此map中取值
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
this.targetDataSources = targetDataSources;
}
//设置默认数据源
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
this.defaultTargetDataSource = defaultTargetDataSource;
}
public void setLenientFallback(boolean lenientFallback) {
this.lenientFallback = lenientFallback;
}
public void setDataSourceLookup(DataSourceLookup dataSourceLookup) {
this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
}
//属性赋值之后调用此方法将目标数据源存于resolvedDataSources
@Override
public void afterPropertiesSet() {
if (this.targetDataSources == null) {
throw new IllegalArgumentException("Property 'targetDataSources' is required");
}
//配置的目标数据源
this.resolvedDataSources = new HashMap<Object, DataSource>(this.targetDataSources.size());
for (Map.Entry<Object, Object> entry : this.targetDataSources.entrySet()) {
Object lookupKey = resolveSpecifiedLookupKey(entry.getKey());
DataSource dataSource = resolveSpecifiedDataSource(entry.getValue());
this.resolvedDataSources.put(lookupKey, dataSource);
}
if (this.defaultTargetDataSource != null) {
this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
}
}
//显然是根据determineTargetDataSource()返回的最终数据源进行连接
@Override
public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}
//返回最终数据源
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
//实现determineCurrentLookupKey()返回显然需要和配置的
//<entry key="dataSource3306" value-ref="dataSource3306"/> <entry key="dataSource3308" value-ref="dataSource3308"/>
//保持一致，否则将始终是默认配置的数据源
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
}
return dataSource;
}
//此方法就是我们需要自己实现的
protected abstract Object determineCurrentLookupKey();
}
然后我们按此实现determineCurrentLookupKey方法，返回值为dataSource3306或dataSource3308
package com.ssm.datasource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 定义动态数据源，集成spring提供的AbstractRoutingDataSource，实现determineCurrentLookupKey
* @author https://blog.csdn.net/caiqing116
*/
public class DynamicDataSource extends AbstractRoutingDataSource{
private static final Logger log = LoggerFactory.getLogger(DynamicDataSource.class);
@Override
protected Object determineCurrentLookupKey() {
//使用DataSourceContextHolder保证线程安全，并且得到当前线程中的数据源
Object dataType = DataSourceContextHolder.getDataType();
log.info("当前数据源：{}",dataType);
return dataType;
}
}
5.借助ThreadLocal类，通过ThreadLocal类传递参数设置数据源
package com.ssm.datasource;
/**
* 切换数据源，清除数据源信息等
* @author https://blog.csdn.net/caiqing116
*/
public class DataSourceContextHolder {
//定义数据源标识和配置文件dataAccessContext.xml配置的bean id一致
public static final String DATASOURCE = "dataSource3306";
public static final String DATASOURCE3308 = "dataSource3308";
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
/**
* 设置当前数据源
* @param dataType 数据元类型 DATASOURCE | DATASOURCE3308
*/
public static void setDatatype(String dataType) {
contextHolder.set(dataType);
}
/**
* 获取当前数据源
* @return
*/
public static String getDataType(){
return contextHolder.get();
}
/**
* 清除
*/
public static void clear() {
contextHolder.remove();
}
/**
* 切到3306端口数据源
*/
public static void mark3306() {
setDatatype(DATASOURCE);
}
/**
* 切到3308端口数据源
*/
public static void mark3308() {
setDatatype(DATASOURCE3308);
}
}
6.测试
编写增删查改Service和实现类，这些在之前的文章中有介绍，就不重复介绍了，mapper及映射文件参考文章：https://blog.csdn.net/caiqing116/article/details/84581171
com/ssm/service/BasicUserService.java
package com.ssm.service;
import java.util.List;
import com.ssm.entity.BasicUser;
import com.ssm.entity.Page;
/**
* 用户Service
* @author https://blog.csdn.net/caiqing116
*
*/
public interface BasicUserService {
Integer insert(BasicUser basicUser);
Integer deleteById(Integer id);
BasicUser selectById(Integer id);
Integer updateById(BasicUser basicUser);
BasicUser selectByUsername(String username);
}
com/ssm/service/impl/BasicUserServiceImpl.java
package com.ssm.service.impl;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ssm.entity.BasicUser;
import com.ssm.entity.Page;
import com.ssm.mapper.BasicUserMapper;
import com.ssm.service.BasicUserService;
/**
* 用户Service实现类
* @author https://blog.csdn.net/caiqing116
*
*/
@Service
public class BasicUserServiceImpl implements BasicUserService{
@Autowired
private BasicUserMapper basicUserMapper;
/**
* 插入用户
*/
public Integer insert(BasicUser basicUser) {
return basicUserMapper.insertSelective(basicUser);
}
/**
* 根据id删除
*/
public Integer deleteById(Integer id) {
return basicUserMapper.deleteByPrimaryKey(id);
}
/**
* 根据id查询
*/
public BasicUser selectById(Integer id) {
return basicUserMapper.selectByPrimaryKey(id);
}
/**
* 根据id更新
*/
public Integer updateById(BasicUser basicUser) {
return basicUserMapper.updateByPrimaryKeySelective(basicUser);
}
/**
* 根据用户名查询
*/
public BasicUser selectByUsername(String username) {
return basicUserMapper.selectByUsername(username);
}
}
Service测试类
package com.ssm.service;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import com.ssm.datasource.DataSourceContextHolder;
import com.ssm.entity.BasicUser;
import com.ssm.util.EncryptKit;
import com.ssm.util.UuidUtil;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:spring/*.xml","classpath:servlet/*.xml" })
public class BasicUserServiceTest {
private static final Logger log = LoggerFactory.getLogger(BasicUserServiceTest.class);
@Autowired
private BasicUserService basicUserService;
@Test
public void testInsert() {
BasicUser basicUser = new BasicUser();
basicUser.setUtype(1);
basicUser.setUserid(UuidUtil.getUuid());
basicUser.setUsername("剑非道");
basicUser.setRealname("剑非道");
basicUser.setPassword(EncryptKit.MD5("123456"));
basicUser.setAge(18);
//切换到3308插入数据
DataSourceContextHolder.clear();
DataSourceContextHolder.mark3308();
int result = basicUserService.insert(basicUser);
DataSourceContextHolder.clear();
log.info("basicUser:"+basicUser);
log.info("插入行数："+result);
}
}
经过上文测试我们查询数据库结果显示在3306端口服务数据库db_ssmdemo中无记录，在3308端口服务数据库db_ssmdemo有记录，说明由默认数据源3306切换到3308。
同理我们可以简单修改切换数据源代码：
DataSourceContextHolder.mark3308(); 修改为 DataSourceContextHolder.mark3306();
执行测试方法，那么再次查询3306查询到记录则证明切换成功。
扫一扫
2068
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
