Spring Boot笔记之多数据源（Mysql+MyBatis）

SpringBoot单数据源配置

pom.xml

在pom.xml中添加相关依赖：

MySQL驱动

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>6.0.6</version>
</dependency>

SpringBoot对MyBatis的集成

<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>${mybatis.spring.boot.starter.version}</version>
</dependency>

阿里出得一个druid，据说性能很好，不是生产环境与可以不引入

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid-spring-boot-starter</artifactId>
	<version>${druid.version}</version>
</dependency>

application.yml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/demo?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&autoReconnect=true&failOverReadOnly=false&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Hongkong
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
	validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20

mybatis:
  mapper-locations: classpath:mapper/*/*.xml
  configuration:
    map-underscore-to-camel-case: true
    use-generated-keys: true
    default-fetch-size: 100
    default-statement-timeout: 300

如此即可，单数据源配置完毕，将Mapper.xml扔到classpath:mapper下面，实体、Example和mapper接口跟随公司习惯随便放。

SpringBoot多数据源配置

修改application.yml

单纯的使用yml文件已经不能满足多数据源的配置，沿袭单数据源的配置项和结构，在spring.datasource后增加一级以区分不同数据源：

spring:
  datasource:
    project:
      url: jdbc:mysql://localhost:3306/demo?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&autoReconnect=true&failOverReadOnly=false&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Hongkong
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
      initialSize: 5
      minIdle: 5
      maxActive: 20
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      validationQuery: SELECT 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
    exchange:
      jdbc-url: jdbc:mysql://localhost:3306/exchange?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&serverTimezone=Hongkong
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

说明

• project是项目的名字，exchange是另一个项目的名字，此处是两个数据源的名称。
• spring.datasource.project.url与spring.datasource.exchange.jdbc-url是不同的两种配置，其中名为project的数据源会使用com.alibaba.druid.pool.DruidDataSource，配置比较复杂（后面讲）,而名为exchange的数据源使用com.zaxxer.hikari.HikariDataSource，配置相对简单。

Configuration类配置

yml不能自动配置多数据源，需要用@Configuration进行配置

首先是比较简单的HikariDataSource

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "one.hht.api.exchange", sqlSessionTemplateRef = "exchangeSqlSessionTemplate")
public class ExchangeDataSourceConfiguration {
    @Bean(name = "exchangeDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.exchange")
    public DataSource exchangeDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "exchangeSqlSessionFactory")
    @Primary
    public SqlSessionFactory exchangeSqlSessionFactory(@Qualifier("exchangeDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/exchange/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "exchangeDataSourceTransactionManager")
    @Primary
    public DataSourceTransactionManager exchangeDataSourceTransactionManager(@Qualifier("exchangeDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "exchangeSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate exchangeSqlSessionTemplate(@Qualifier("exchangeSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

然后是DruidDataSource的配置

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "one.hht.api.project", sqlSessionTemplateRef = "projectSqlSessionTemplate")
public class ProjectDataSourceConfiguration {
    @Autowired
    private DataSourceProperties properties;

    @Value("${spring.datasource.project.initialSize}")
    private Integer initialSize;

    @Value("${spring.datasource.project.minIdle}")
    private Integer minIdle;

    @Value("${spring.datasource.project.maxActive}")
    private Integer maxActive;

    @Value("${spring.datasource.project.maxWait}")
    private Integer maxWait;

    @Value("${spring.datasource.project.timeBetweenEvictionRunsMillis}")
    private Integer timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.project.validationQuery}")
    private String validationQuery;

    @Value("${spring.datasource.project.testWhileIdle}")
    private Boolean testWhileIdle;

    @Value("${spring.datasource.project.testOnBorrow}")
    private Boolean testOnBorrow;

    @Value("${spring.datasource.project.testOnReturn}")
    private Boolean testOnReturn;

    @Value("${spring.datasource.project.poolPreparedStatements}")
    private Boolean poolPreparedStatements;

    @Value("${spring.datasource.project.maxPoolPreparedStatementPerConnectionSize}")
    private Integer maxPoolPreparedStatementPerConnectionSize;

    @Bean(name = "projectDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.project")
    public DataSource projectDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(properties.getUrl());
        dataSource.setUsername(properties.getUsername());
        dataSource.setPassword(properties.getPassword());
        dataSource.setDriverClassName(properties.getDriverClassName());
        dataSource.setInitialSize(initialSize);
        dataSource.setMinIdle(minIdle);
        dataSource.setMaxActive(maxActive);
        dataSource.setMaxWait(maxWait);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        return dataSource;
    }

    @Bean(name = "projectSqlSessionFactory")
    @Primary
    public SqlSessionFactory projectSqlSessionFactory(@Qualifier("projectDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/project/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "projectDataSourceTransactionManager")
    @Primary
    public DataSourceTransactionManager projectDataSourceTransactionManager(@Qualifier("projectDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "projectSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate projectSqlSessionTemplate(@Qualifier("projectSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

结语

如此配置，将mapper接口放到相应的basePackages指定包中，mapper.xml放到MapperLocations指定路径下，即可。。。

两种配置方法个人感觉还是用com.alibaba.druid.pool.DruidDataSource的这种比较靠谱。