spring-boot整合mybatis和druid连接池（多数据源）

柔情只为你懂 2021-05-13 16:05 559阅读 0赞

spring-boot整合mybatis和druid连接池如何配置和使用，本篇文章是上篇的扩展，如果业务中出现了需要从多个数据源中获取数据，到底该如何实现？本文主要介绍在一种最为简单的实现方案：**多数据源 - 多实例**。

在上篇文章中不难看出Spring Boot中，通过为该数据源DataSource初始化一个与之对应的SessionFactory，从而实现连接。因此在面对多数据源的时候，可以分别为每个数据源写一个mybatis的config类，使其每个DataSource都拥有一个只属于自己的SessionFactory，这样就可以根据各自的mapper（dao）映射目录找到对应的mybaits实例。

注：这种实现方法要求不同的mybatis实例的mapper映射目录（dao）不能相同。

![20180526130241154][]

1、pom.xml

<?xml version="1.0" encoding="UTF-8"?>
    
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
    
      <groupId>cn.edu.nuc</groupId>
      <artifactId>Test1</artifactId>
      <version>0.0.1-SNAPSHOT</version>
    
      <name>Test1</name>
    
      <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.7</maven.compiler.source>
        <maven.compiler.target>1.7</maven.compiler.target>
        
        <mybatis-spring-boot>1.2.0</mybatis-spring-boot>
        <mysql-connector>5.1.39</mysql-connector>
      </properties>
    
      <parent>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-parent</artifactId>
          <version>1.5.5.RELEASE</version>
      </parent>
      
      <dependencies>
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <scope>test</scope>
        </dependency>
        
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>  
                <exclusion>  
                    <groupId>org.springframework.boot</groupId>  
                    <artifactId>spring-boot-starter-logging</artifactId>  
                </exclusion>  
            </exclusions>  
        </dependency>
           
        <dependency>  
            <groupId>org.springframework.boot</groupId>  
            <artifactId>spring-boot-starter-log4j</artifactId>
            <version>1.3.8.RELEASE</version>
        </dependency> 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
    	    <groupId>org.springframework.boot</groupId>
    	    <artifactId>spring-boot-starter-tomcat</artifactId>
    	    <scope>provided</scope>
    	</dependency>
    	
    	<dependency>
    	   <groupId>org.apache.tomcat.embed</groupId>
    	    <artifactId>tomcat-embed-jasper</artifactId>
    	    <scope>provided</scope>
    	</dependency>
    	<dependency>
    	    <groupId>javax.servlet</groupId>
    	    <artifactId>jstl</artifactId>
    	</dependency>
        
        
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot}</version>
        </dependency>
        
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.13</version>
        </dependency>
         
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
         </dependency>
        
      </dependencies>
    
      <build>
      	<finalName>Test</finalName>
      	<plugins>
            
            <plugin>
    		    <groupId>org.apache.maven.plugins</groupId>
    		    <artifactId>maven-war-plugin</artifactId>
    		    <configuration>
    		        <failOnMissingWebXml>false</failOnMissingWebXml>
    		    </configuration>
    		</plugin>
        </plugins>
      </build>
    </project>

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

1）application.properties配置：

#---------------------ds1数据源（使用durid连接池）
    ds1.datasource.url=jdbc:mysql://ds1.w.abc.db:1883/ttengine?useUnicode=true&characterEncoding=utf8
    ds1.datasource.username=ds1
    ds1.datasource.password=ds1
    ds1.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
    
    ds2.datasource.url=jdbc:mysql://ds2.w.abc.db:8907/toutiao?useUnicode=true&characterEncoding=utf8
    ds2.datasource.username=ds2
    ds2.datasource.password=^ds2
    ds2.datasource.driverClassName=com.mysql.jdbc.Driver
    #---------------------ds2数据源
    ds2.datasource.initialSize=20
    ds2.datasource.minIdle=20
    ds2.datasource.maxActive=200
    ds2.datasource.maxWait=60000
    ds2.datasource.timeBetweenEvictionRunsMillis=60000
    ds2.datasource.minEvictableIdleTimeMillis=300000
    ds2.datasource.testWhileIdle=true
    ds2.datasource.testOnBorrow=false
    ds2.datasource.testOnReturn=false
    ds2.datasource.poolPreparedStatements=true
    ds2.datasource.maxPoolPreparedStatementPerConnectionSize=20
    
    
    # 页面默认前缀目录
    spring.mvc.view.prefix=/WEB-INF/page/
    spring.mvc.view.suffix=.jsp

由于我们使用druid连接池，所以我们需要定制DataSourceConfig（每个数据源都要定制一份）

2）Datasource1Config：

package nc.edu.nuc.Test.dao.mysql;
    
    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.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import com.alibaba.druid.pool.DruidDataSource;
    
    @Configuration
    //扫描 Mapper 接口并容器管理
    @MapperScan(basePackages = Datasource1Config.PACKAGE, sqlSessionFactoryRef = "ds1SqlSessionFactory")
    public class Datasource1Config {
    	// 精确到 master 目录，以便跟其他数据源隔离
        static final String PACKAGE = "nc.edu.nuc.Test.dao.mysql.ds1";
        static final String MAPPER_LOCATION = "classpath:mapper/ds1/*.xml";
    
        @Value("${ds1.datasource.url}")
        private String url;
        @Value("${ds1.datasource.username}")
        private String user;
        @Value("${ds1.datasource.password}")
        private String password;
        @Value("${ds1.datasource.driverClassName}")
        private String driverClass;
        
        @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 = "ds1DataSource")
        @Primary
        public DataSource ds1DataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(user);
            dataSource.setPassword(password);
            
            //连接池配置
            dataSource.setMaxActive(maxActive);
            dataSource.setMinIdle(minIdle);
            dataSource.setInitialSize(initialSize);
            dataSource.setMaxWait(maxWait);
            dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            dataSource.setTestWhileIdle(testWhileIdle);
            dataSource.setTestOnBorrow(testOnBorrow);
            dataSource.setTestOnReturn(testOnReturn);
            dataSource.setValidationQuery("SELECT 'x'");
            
            dataSource.setPoolPreparedStatements(true);
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
            
            return dataSource;
        }
    
        @Bean(name = "ds1TransactionManager")
        @Primary
        public DataSourceTransactionManager ds1TransactionManager() {
            return new DataSourceTransactionManager(ds1DataSource());
        }
    
        @Bean(name = "ds1SqlSessionFactory")
        @Primary
        public SqlSessionFactory ds1SqlSessionFactory(@Qualifier("ds1DataSource") DataSource ds1DataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(ds1DataSource);
            sessionFactory.setTypeAliasesPackage("nc.edu.nuc.Test.entity");
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(Datasource1Config.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    }

3）Datasource2Config：

package nc.edu.nuc.Test.dao.mysql;
    
    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.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;
    
    @Configuration
    //扫描 Mapper 接口并容器管理
    @MapperScan(basePackages = Datasource2Config.PACKAGE, sqlSessionFactoryRef = "ds2SqlSessionFactory")
    public class Datasource2Config {
    	// 精确到 master 目录，以便跟其他数据源隔离
        static final String PACKAGE = "nc.edu.nuc.Test.dao.mysql.ds2";
        static final String MAPPER_LOCATION = "classpath:mapper/ds2/*.xml";
    
        @Value("${ds2.datasource.url}")
        private String url;
        @Value("${ds2.datasource.username}")
        private String user;
        @Value("${ds2.datasource.password}")
        private String password;
        @Value("${ds2.datasource.driverClassName}")
        private String driverClass;
        
        @Value("${ds2.datasource.maxActive}")
        private Integer maxActive;
        @Value("${ds2.datasource.minIdle}")
        private Integer minIdle;
        @Value("${ds2.datasource.initialSize}")
        private Integer initialSize;
        @Value("${ds2.datasource.maxWait}")
        private Long maxWait;
        @Value("${ds2.datasource.timeBetweenEvictionRunsMillis}")
        private Long timeBetweenEvictionRunsMillis;
        @Value("${ds2.datasource.minEvictableIdleTimeMillis}")
        private Long minEvictableIdleTimeMillis;
        @Value("${ds2.datasource.testWhileIdle}")
        private Boolean testWhileIdle;
        @Value("${ds2.datasource.testWhileIdle}")
        private Boolean testOnBorrow;
        @Value("${ds2.datasource.testOnBorrow}")
        private Boolean testOnReturn;
    
        @Bean(name = "ds2DataSource")
        public DataSource ds2DataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(user);
            dataSource.setPassword(password);
            
            //连接池配置
            dataSource.setMaxActive(maxActive);
            dataSource.setMinIdle(minIdle);
            dataSource.setInitialSize(initialSize);
            dataSource.setMaxWait(maxWait);
            dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            dataSource.setTestWhileIdle(testWhileIdle);
            dataSource.setTestOnBorrow(testOnBorrow);
            dataSource.setTestOnReturn(testOnReturn);
            dataSource.setValidationQuery("SELECT 'x'");
            
            dataSource.setPoolPreparedStatements(true);
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
            
            return dataSource;
        }
    
        @Bean(name = "ds2TransactionManager")
        public DataSourceTransactionManager ds2TransactionManager() {
            return new DataSourceTransactionManager(ds2DataSource());
        }
    
        @Bean(name = "ds2SqlSessionFactory")
        public SqlSessionFactory ds2SqlSessionFactory(@Qualifier("ds2DataSource") DataSource ds2DataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(ds2DataSource);
            sessionFactory.setTypeAliasesPackage("nc.edu.nuc.Test.entity");
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(Datasource2Config.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    }

多数据源配置的时候注意，必须要有一个主数据源。

*  @Primary 标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean 优先被考虑。「多数据源配置的时候注意，必须要有一个主数据源，用 @Primary 标志该 Bean」
 *  @MapperScan 扫描 Mapper 接口并容器管理，包路径精确到 master，为了和下面 cluster 数据源做到精确区分
 *  @Value 获取全局配置文件 application.properties 的 kv 配置,并自动装配sqlSessionFactoryRef 表示定义了 key ，表示一个唯一 SqlSessionFactory 实例.

4）说明：

*  上面数据配置分别扫描nc.edu.nuc.Test.dao.mysql.ds1 和 nc.edu.nuc.Test.dao.mysql.ds2中定义的Mapper接口TestDao和DemoDao（可以在接口上配置@Mapper注解，也可以在springboot主类上统一加@MapperScan("...")）
 *  ds1对应 xml classpath:mapper/ds1下的sql文件，ds2对应 xml classpath:mapper/ds2下的sql文件，Mybatis 内部会使用反射机制运行去解析相应 SQL。

3、其他:

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

![20180526132055598][]

1）crontroller：

package nc.edu.nuc.Test.controller;
    
    import java.util.Date;
    import javax.servlet.http.HttpServletRequest;
    import nc.edu.nuc.Test.entity.Test;
    import nc.edu.nuc.Test.service.TestService;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.ui.Model;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    @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();
        }
    }

2）service：

package nc.edu.nuc.Test.service;
    
    import java.util.List;
    import nc.edu.nuc.Test.dao.mysql.ds1.TestDao;
    import nc.edu.nuc.Test.dao.mysql.ds2.DemoDao;
    import nc.edu.nuc.Test.entity.Demo;
    import nc.edu.nuc.Test.entity.Test;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    @Service
    public class TestService{
    	private static final Logger logger = LoggerFactory.getLogger(TestService.class); 
    	
    	@Autowired  
    	private TestDao testDao;  
    	@Autowired  
    	private DemoDao demoDao;  
    	
    	public Test findByName(String name) throws Exception{
    		List<Demo> demoList = demoDao.getDemoList();
    		logger.info(demoList.toString());
    		return testDao.findByName(name);
    	}
    }

3）entity：

package nc.edu.nuc.Test.entity;
    
    public class Demo {
    	private Long id;
    	private String title;
    	private String descs;
    	
    	
    	public Long getId() {
    		return id;
    	}
    	public void setId(Long id) {
    		this.id = id;
    	}
    	public String getTitle() {
    		return title;
    	}
    	public void setTitle(String title) {
    		this.title = title;
    	}
    	public String getDescs() {
    		return descs;
    	}
    	public void setDescs(String descs) {
    		this.descs = descs;
    	}
    	
    	@Override
    	public String toString() {
    		return "Demo [id=" + id + ", title=" + title + ", descs=" + descs + "]";
    	}
    }

package nc.edu.nuc.Test.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 nc.edu.nuc.Test.dao.mysql.ds1;
    
    import nc.edu.nuc.Test.entity.Test;
    import org.apache.ibatis.annotations.Param;
    
    public interface TestDao {
        Test findByName(@Param("name") String n);
    }

package nc.edu.nuc.Test.dao.mysql.ds2;
    
    import java.util.List;
    import nc.edu.nuc.Test.entity.Demo;
    
    public interface DemoDao {
    	List<Demo> getDemoList() throws Exception;
    }

5）mapper sql文件：

<?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="nc.edu.nuc.Test.dao.mysql.ds1.TestDao">  
        <resultMap id="BaseResultMap" type="nc.edu.nuc.Test.entity.Test">  
            <result column="id" property="id" />  
            <result column="name" property="name" />  
        </resultMap>  
      
        <parameterMap id="Test" type="nc.edu.nuc.Test.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>  
    </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="nc.edu.nuc.Test.dao.mysql.ds2.DemoDao">  
        <resultMap id="BaseResultMap" type="nc.edu.nuc.Test.entity.Demo">  
            <result column="id" property="id" />  
            <result column="title" property="title" />  
            <result column="descs" property="descs" />  
        </resultMap>  
      
        <parameterMap id="Demo" type="nc.edu.nuc.Test.entity.Demo"/>  
      
        <sql id="Base_Column_List">  
            id,title,descs
        </sql>  
      
        <select id="getDemoList" resultType="Demo">  
            select  
            <include refid="Base_Column_List" />  
            from demo  
        </select>  
      
    </mapper>

6）app启动类：

package nc.edu.nuc.Test;
    
    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("nc.edu.nuc.Test.dao.mysql")
    public class App {
        public static void main( String[] args ) {
        	SpringApplication.run(App.class, args);
        }
    }

[20180526130241154]: /images/20210511/a90fa38d5cba43668ca51845ae3205fc.png
[20180526132055598]: /images/20210511/266a793ef51d41e6badfff8687391a67.png