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

我不是女神ヾ 2021-05-13 16:07 666阅读 0赞

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

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

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

![20180526161342586][]

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

![20180526161444833][]

首先分析一下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是程序内部执行时的依据，因此会有一个赋值的操作，如下图所示：

![20180526161542691][]

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

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

![20180526161604968][]

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

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的用法一样了。整个结构：

![20180526162842607][]

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

[20180526161342586]: /images/20210511/d060377ed3754e039ce2e105a2673cf7.png
[20180526161444833]: /images/20210511/52eda93249644d8b9de35d285328ba29.png
[20180526161542691]: /images/20210511/fc93c5f9dc7a43a9b1e6e11ef4b643ab.png
[20180526161604968]: /images/20210511/f3284509499e46e3b6ac51a89586b8bc.png
[20180526162842607]: /images/20210511/018075a613674525b0ef02c3e72ffcec.png