spring-boot整合mybatis和druid连接池（动态数据源——读写分离）

上一篇文章介绍了 spring-boot整合mybatis+druid连接池实现多数据源，在这个例子中我们为每个DataSource都初始化一个与之对应的SessionFactory，从而实现连接化多个数据源。再仔细体会一下，会发现这种方式有一点静态的味道。

在实际需求中，我们经常会遇到mysql的读写分离、冷热分离等需求，这种情况虽然也可以看成多数据源的一种情况，用上面的方式去处理，但是有没有更好的解决方法呢？

答案是：有。我们可以设计一种数据源动态切换的架构，整个系统中就初始化一个SessionFactory，设计一个DynamicDataSource类，把所有数据源放进去，然后根据不同需求动态的切换数据源，实现mysql的读写分离、冷热分离。。。

基于这种方式，不仅可是实现真正意义上的多数据源的切换（第二种实现多数据源的思路），还可以实现在程序的运行过程中，实现动态添加一个或多个新的数据源。这里重点关注的是配置文件之间的关系，对象模型如下：

首先分析一下AbstractRoutingDataSource抽象类的源码：

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;

    public AbstractRoutingDataSource() {
    }

    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)(dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
    }

    public void afterPropertiesSet() {
        if (this.targetDataSources == null) {
            throw new IllegalArgumentException("Property 'targetDataSources' is required");
        } else {
            this.resolvedDataSources = new HashMap(this.targetDataSources.size());
            Iterator var1 = this.targetDataSources.entrySet().iterator();

            while(var1.hasNext()) {
                Entry<Object, Object> entry = (Entry)var1.next();
                Object lookupKey = this.resolveSpecifiedLookupKey(entry.getKey());
                DataSource dataSource = this.resolveSpecifiedDataSource(entry.getValue());
                this.resolvedDataSources.put(lookupKey, dataSource);
            }

            if (this.defaultTargetDataSource != null) {
                this.resolvedDefaultDataSource = this.resolveSpecifiedDataSource(this.defaultTargetDataSource);
            }

        }
    }

    protected Object resolveSpecifiedLookupKey(Object lookupKey) {
        return lookupKey;
    }

    protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
        if (dataSource instanceof DataSource) {
            return (DataSource)dataSource;
        } else if (dataSource instanceof String) {
            return this.dataSourceLookup.getDataSource((String)dataSource);
        } else {
            throw new IllegalArgumentException("Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
        }
    }

    public Connection getConnection() throws SQLException {
        return this.determineTargetDataSource().getConnection();
    }

    public Connection getConnection(String username, String password) throws SQLException {
        return this.determineTargetDataSource().getConnection(username, password);
    }

    public <T> T unwrap(Class<T> iface) throws SQLException {
        return iface.isInstance(this) ? this : this.determineTargetDataSource().unwrap(iface);
    }

    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return iface.isInstance(this) || this.determineTargetDataSource().isWrapperFor(iface);
    }

    protected DataSource determineTargetDataSource() {
        Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
        Object lookupKey = this.determineCurrentLookupKey();
        DataSource 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 + "]");
        } else {
            return dataSource;
        }
    }

    protected abstract Object determineCurrentLookupKey();
}

对于该抽象类，关注两组变量： Map<Object, Object> targetDataSources 和 Object defaultTargetDataSource 、 Map<Object, DataSource> resolvedDataSources 和 DataSource resolvedDefaultDataSource ；这两组变量是相互对应的；在熟悉多实例数据源切换代码的不难发现，当有多个数据源的时候，一定要指定一个作为默认的数据源，在这里也同理，当同时初始化多个数据源的时候，需要显示的调用setDefaultTargetDataSource方法指定一个作为默认数据源； 

　　我们需要关注的是Map<Object, Object> targetDataSources和Map<Object, DataSource> resolvedDataSources，targetDataSources是暴露给外部程序用来赋值的，而resolvedDataSources是程序内部执行时的依据，因此会有一个赋值的操作，如下图所示：

根据这段源码可以看出，每次执行时，都会遍历targetDataSources内的所有元素并赋值给resolvedDataSources；这样如果我们在外部程序新增一个新的数据源，都会添加到内部使用，从而实现数据源的动态加载。

　　继承该抽象类的时候，必须实现一个抽象方法：protected abstract Object determineCurrentLookupKey()，该方法用于指定到底需要使用哪一个数据源。

到此基本上清楚了该抽象类的使用方法，接下来贴下具体的实现代码。

1、pom.xml

同上一篇文章中的pom；

2、application.properties和数据源配置：

1）application.properties

#---------------------使用durid连接池
ds1.w.datasource.url=jdbc:mysql://jy.ttengine.w.abc.db:1883/ttengine?useUnicode=true&characterEncoding=utf8
ds1.w.datasource.username=ttengine
ds1.w.datasource.password=TTengine123
ds1.w.datasource.driverClassName=com.mysql.jdbc.Driver

ds1.r.datasource.url=jdbc:mysql://jy.ttengine.r.abc.db:1883/ttengine?useUnicode=true&characterEncoding=utf8
ds1.r.datasource.username=ttengine
ds1.r.datasource.password=TTengine123
ds1.r.datasource.driverClassName=com.mysql.jdbc.Driver

ds1.datasource.initialSize=20
ds1.datasource.minIdle=20
ds1.datasource.maxActive=200
ds1.datasource.maxWait=60000
ds1.datasource.timeBetweenEvictionRunsMillis=60000
ds1.datasource.minEvictableIdleTimeMillis=300000
ds1.datasource.testWhileIdle=true
ds1.datasource.testOnBorrow=false
ds1.datasource.testOnReturn=false
ds1.datasource.poolPreparedStatements=true
ds1.datasource.maxPoolPreparedStatementPerConnectionSize=20


# 页面默认前缀目录
spring.mvc.view.prefix=/WEB-INF/page/
spring.mvc.view.suffix=.jsp

2）自定义数据源切换类：

package cn.edu.nuc.Test1.dao.mysql.config;

public class DatabaseContextHolder {

	private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

	public static void setDBKey(String dataSourceKey) {
		contextHolder.set(dataSourceKey);
	}

	public static String getDBKey() {
		return contextHolder.get();
	}

	public static void clearDBKey() {
		contextHolder.remove();
	}
}

通过ThreadLocal维护一个全局唯一的map来实现数据源的动态切换。

3）自定义DynamicDataSource：

package cn.edu.nuc.Test1.dao.mysql.config;

import java.util.HashMap;
import java.util.Map;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 动态数据源
 * @author kevinliu
 *
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
	private static DynamicDataSource instance;
	private static byte[] lock=new byte[0];
	private static Map<Object,Object> dataSourceMap=new HashMap<Object, Object>();
	
	@Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(targetDataSources);
        dataSourceMap.putAll(targetDataSources);
        super.afterPropertiesSet();// 必须添加该句，否则新添加数据源无法识别到
    }

    public Map<Object, Object> getDataSourceMap() {
        return dataSourceMap;
    }
	
	@Override
	protected Object determineCurrentLookupKey() {
		String dbKey = DatabaseContextHolder.getDBKey();
		/*if (StringUtils.isBlank(dbKey)) {
			dbKey = "read";
		}*/
		return dbKey;
	}

	private DynamicDataSource() {}
	
	public static synchronized DynamicDataSource getInstance(){
        if(instance==null){
            synchronized (lock){
                if(instance==null){
                    instance=new DynamicDataSource();
                }
            }
        }
        return instance;
    }
}

该类有以下几个方法：

• 私有构造方法和获取单例的方法；
• 实现了抽象类的determineCurrentLookupKey方法，从上面的map中获取数据源，然后动态切换；
• 覆盖了抽象类的setTargetDataSources方法，主要用来动态创建数据源，然后动态切换；

4）定制DataSourceConfig：

package cn.edu.nuc.Test1.dao.mysql.config;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;

@Configuration
//扫描 Mapper 接口并容器管理
@MapperScan(basePackages = DatasourceConfig.PACKAGE, sqlSessionFactoryRef = "sqlSessionFactory")
public class DatasourceConfig {
	// 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "cn.edu.nuc.Test1.dao.mysql";
    static final String MAPPER_LOCATION = "classpath:mapper/*.xml";

    @Value("${ds1.w.datasource.url}")
    private String wurl;
    @Value("${ds1.w.datasource.username}")
    private String wuser;
    @Value("${ds1.w.datasource.password}")
    private String wpassword;
    @Value("${ds1.w.datasource.driverClassName}")
    private String wdriverClass;
    
    @Value("${ds1.r.datasource.url}")
    private String rurl;
    @Value("${ds1.r.datasource.username}")
    private String ruser;
    @Value("${ds1.r.datasource.password}")
    private String rpassword;
    @Value("${ds1.r.datasource.driverClassName}")
    private String rdriverClass;
    
    @Value("${ds1.datasource.maxActive}")
    private Integer maxActive;
    @Value("${ds1.datasource.minIdle}")
    private Integer minIdle;
    @Value("${ds1.datasource.initialSize}")
    private Integer initialSize;
    @Value("${ds1.datasource.maxWait}")
    private Long maxWait;
    @Value("${ds1.datasource.timeBetweenEvictionRunsMillis}")
    private Long timeBetweenEvictionRunsMillis;
    @Value("${ds1.datasource.minEvictableIdleTimeMillis}")
    private Long minEvictableIdleTimeMillis;
    @Value("${ds1.datasource.testWhileIdle}")
    private Boolean testWhileIdle;
    @Value("${ds1.datasource.testWhileIdle}")
    private Boolean testOnBorrow;
    @Value("${ds1.datasource.testOnBorrow}")
    private Boolean testOnReturn;

    @Bean(name = "dynamicDataSource")
    public DynamicDataSource dynamicDataSource() {
    	DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();

    	//jdbc配置
        DruidDataSource wdataSource = new DruidDataSource();
        wdataSource.setDriverClassName(wdriverClass);
        wdataSource.setUrl(wurl);
        wdataSource.setUsername(wuser);
        wdataSource.setPassword(wpassword);
        
        //连接池配置
        wdataSource.setMaxActive(maxActive);
        wdataSource.setMinIdle(minIdle);
        wdataSource.setInitialSize(initialSize);
        wdataSource.setMaxWait(maxWait);
        wdataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        wdataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        wdataSource.setTestWhileIdle(testWhileIdle);
        wdataSource.setTestOnBorrow(testOnBorrow);
        wdataSource.setTestOnReturn(testOnReturn);
        wdataSource.setValidationQuery("SELECT 'x'");
        wdataSource.setPoolPreparedStatements(true);
        wdataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
        try {
        	wdataSource.setFilters("stat");
		} catch (SQLException e) {
			e.printStackTrace();
		}
        
        //jdbc配置
        DruidDataSource rdataSource = new DruidDataSource();
        rdataSource.setDriverClassName(rdriverClass);
        rdataSource.setUrl(rurl);
        rdataSource.setUsername(ruser);
        rdataSource.setPassword(rpassword);
        
        //连接池配置
        rdataSource.setMaxActive(maxActive);
        rdataSource.setMinIdle(minIdle);
        rdataSource.setInitialSize(initialSize);
        rdataSource.setMaxWait(maxWait);
        rdataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        rdataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        rdataSource.setTestWhileIdle(testWhileIdle);
        rdataSource.setTestOnBorrow(testOnBorrow);
        rdataSource.setTestOnReturn(testOnReturn);
        rdataSource.setValidationQuery("SELECT 'x'");
        rdataSource.setPoolPreparedStatements(true);
        rdataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
        try {
			rdataSource.setFilters("stat");
		} catch (SQLException e) {
			e.printStackTrace();
		}
        
        Map<Object,Object> map = new HashMap<>();
        map.put("ds1_w", wdataSource);
        map.put("ds1_r", rdataSource);
        
        dynamicDataSource.setTargetDataSources(map);
        dynamicDataSource.setDefaultTargetDataSource(rdataSource);
        
        return dynamicDataSource;
    }

    @Bean(name = "transactionManager")
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dynamicDataSource);
        sessionFactory.setTypeAliasesPackage("cn.edu.nuc.Test1.entity");
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(DatasourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
    
    
    @Bean
    public ServletRegistrationBean druidServlet() {
      ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
      servletRegistrationBean.setServlet(new StatViewServlet());
      servletRegistrationBean.addUrlMappings("/druid/*");
      Map<String, String> initParameters = new HashMap<String, String>();
      initParameters.put("loginUsername", "admin");// 用户名
      initParameters.put("loginPassword", "admin");// 密码
      initParameters.put("resetEnable", "false");// 禁用HTML页面上的“Reset All”功能
      initParameters.put("allow", ""); // IP白名单 (没有配置或者为空，则允许所有访问)
      //initParameters.put("deny", "192.168.20.38");// IP黑名单 (存在共同时，deny优先于allow)
      servletRegistrationBean.setInitParameters(initParameters);
      return servletRegistrationBean;
    }
    
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
      FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
      filterRegistrationBean.setFilter(new WebStatFilter());
      filterRegistrationBean.addUrlPatterns("/*");
      filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
      return filterRegistrationBean;
    }
}

该类和上一篇文章中的略有不同：

• 获取数据源时返回的是自定义DynamicDataSource（继承了AbstractRoutingDataSource抽象类），在其方法上，创建读和写两个DruidDataSource对象，然后放到了DynamicDataSource中；此外，下面只创建了一个sessionFactory对象；
• 在创建好DruidSource后，调用下面两个方法初始化数据源：dynamicDataSource.setTargetDataSources(map);dynamicDataSource.setDefaultTargetDataSource(rdataSource);

3、其他:

动态数据源配置好后，接下来就和正常spring-boot的用法一样了。整个结构：

1）crontroller：

package cn.edu.nuc.Test1.controller;

import javax.servlet.http.HttpServletRequest;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import cn.edu.nuc.Test1.entity.Test;
import cn.edu.nuc.Test1.service.TestService;

@Controller
public class TestController {
	private static final Logger logger = LoggerFactory.getLogger(TestController.class); 
	
	@Autowired
	private TestService testService;
	
	@RequestMapping("/test")
    @ResponseBody
    public String test(HttpServletRequest request) {
		String name = request.getParameter("name");
		Test test = null;
		try {
			test = testService.findByName(name);
		} catch (Exception e) {
			test = new Test();
			logger.error("test error.",e);
		}
		logger.info("test....{}",name);
        return test.toString();
    }
	
	@RequestMapping("/save")
    @ResponseBody
    public String saveTest(HttpServletRequest request) {
		String res = "0";
		String name = request.getParameter("name");
		try {
			testService.saveTest(name);
		} catch (Exception e) {
			res = "1";
			logger.error("save test error.",e);
		}
		logger.info("save test....{}",name);
        return res;
    }
}

两个方法：一个读方法（连接从库），一个写方法（连接主库）

2）service：

package cn.edu.nuc.Test1.service;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import cn.edu.nuc.Test1.dao.mysql.TestDao;
import cn.edu.nuc.Test1.dao.mysql.config.DatabaseContextHolder;
import cn.edu.nuc.Test1.entity.Test;

@Service
public class TestService{
	private static final Logger logger = LoggerFactory.getLogger(TestService.class); 
	
	@Autowired  
	private TestDao testDao;  
	
	public Test findByName(String name) throws Exception{
		logger.info("findByName...");
		return testDao.findByName(name);
	}
	
	public void saveTest(String name) throws Exception{
		logger.info("saveTest...");
		DatabaseContextHolder.setDBKey("ds1_w");
		Test t = new Test();
		t.setName(name);
		testDao.saveTest(t);
	}
}

3）entity：

package cn.edu.nuc.Test1.entity;

public class Test {
	private Long id;
	private String name;
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Test [id=" + id + ", name=" + name + "]";
	}
}

4）dao：

package cn.edu.nuc.Test1.dao.mysql;

import org.apache.ibatis.annotations.Param;

import cn.edu.nuc.Test1.entity.Test;

public interface TestDao {
    Test findByName(@Param("name") String n) throws Exception;
    void saveTest(Test t) throws Exception;
}

5）mapper文件：

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >  
<mapper namespace="cn.edu.nuc.Test1.dao.mysql.TestDao">  
    <resultMap id="BaseResultMap" type="cn.edu.nuc.Test1.entity.Test">  
        <result column="id" property="id" />  
        <result column="name" property="name" />  
    </resultMap>  
  
    <parameterMap id="Test" type="cn.edu.nuc.Test1.entity.Test"/>  
  
    <sql id="Base_Column_List">  
        id,name  
    </sql>  
    <select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">  
        select  
        <include refid="Base_Column_List" />  
        from test  
        where name = #{name}  
    </select>  
    
    <!-- 插入 -->
    <insert id="saveTest" parameterType="Test">
        insert into test
        (name)
        values
        (#{name})
    </insert>
</mapper>  

6）主类：

package cn.edu.nuc.Test1;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;


/**
 * Hello world!
 * 可以统一用@MapperScan指定扫描的dao，也可以在每个dao上添加@Mapper
 */
@SpringBootApplication
//mapper 接口类扫描包配置
@MapperScan("cn.edu.nuc.Test1.dao.mysql")
public class App 
{
    public static void main( String[] args )
    {
    	SpringApplication.run(App.class, args);
    }
}

参考：https://blog.csdn.net/YHYR_YCY/article/details/78894940