现在一般的应用都会有多个数据源.SpringBoot结合Mybatis的使用可以大大方便开发者对SQL的开发.所以掌握SpringBoot+Mybatis多数据源的配置非常重要.
现将测试通过的多数据源配置整理如下.
框架版本
| 序号 | 框架 | 版本 |
|---|---|---|
| 1 | spring-boot | 1.5.4.RELEASE |
| 2 | mybatis-spring-boot-starter | 1.3.1 |
相关POM.xml配置
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.4.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.38</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--<dependency>-->
<!--<groupId>org.springframework.boot</groupId>-->
<!--<artifactId>spring-boot-starter-data-jpa</artifactId>-->
<!--</dependency>-->
<!--<dependency>-->
<!--<groupId>org.springframework.boot</groupId>-->
<!--<artifactId>spring-boot-starter-jdbc</artifactId>-->
<!--</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-security</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
配置DataSource
对于多个数据源(如多个库, 多个schema等), 一般都要用不同的连接池对应不同的数据源.
因此首先需要配置两个连接池.
配置映射器
MyBatis在Spring-Boot中的映射器一样可以通过@Mapper扫描,但是对于不同的数据源的映射器要指定对应的SqlSessionFactory.
因此需要配置两个SqlSessionFactory
配置事务管理器
Spring中每个连接池又需要不同的事务去维护.
因此需要配置两个事务管理器
所以, 配置多个数据源主要是配置多个连接池, 每个连接池配置对应的SqlSessionFactory和事务管理器.
示例代码
@MapperScan(basePackages = "zxw.app.udpo.core.mapper.second", sqlSessionFactoryRef = SQLSESSION_FACTORY)
basePackages: 指定扫描的包,用于区分不同数据源下的Mapper. 不同数据源下的Mapper尽量放到不同的包下.
sqlSessionFactoryRef: 指定包下使用的SqlSessionFactory,即数据源
配置第一个数据源,事务管理器和SqlSessionFactory
package zxw.app.udpo.core.configure;
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.boot.autoconfigure.jdbc.DataSourceBuilder;
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.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* @title: FDsConfigure
* @description:
* @author: zhangxw
* @date: 2017/9/27 20:01
* @params:
* @returns
*/
@Configuration
@MapperScan(basePackages = "zxw.app.udpo.core.mapper.first")
public class FDsConfiguration {
public static final String DATA_SOURCE = "fds";
public static final String TRANSCATION_MANAGER = "ftm";
public static final String SQLSESSION_FACTORY = "fsf";
@Bean(name = DATA_SOURCE)
@ConfigurationProperties(prefix = "datasource.first")
@Primary
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = TRANSCATION_MANAGER)
@Primary
public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier(DATA_SOURCE) DataSource ds) {
return new DataSourceTransactionManager(ds);
}
@Bean(name = SQLSESSION_FACTORY)
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier(DATA_SOURCE) DataSource ds) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(ds);
return bean.getObject();
}
}
配置第二个数据源,事务管理器和SqlSessionFactory
package zxw.app.udpo.core.configure;
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.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import static zxw.app.udpo.core.configure.SDsConfiguration.SQLSESSION_FACTORY;
/**
* @title: SDsConfiguration
* @description:
* @author: zhangxw
* @date: 2017/9/27 20:15
* @params:
* @returns
*/
@Configuration
@MapperScan(basePackages = "zxw.app.udpo.core.mapper.second", sqlSessionFactoryRef = SQLSESSION_FACTORY)
public class SDsConfiguration {
public static final String DATA_SOURCE = "sds";
public static final String TRANSCATION_MANAGER = "stm";
public static final String SQLSESSION_FACTORY = "ssf";
@Bean(name = DATA_SOURCE)
@ConfigurationProperties(prefix = "datasource.second")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = TRANSCATION_MANAGER)
public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier(DATA_SOURCE) DataSource ds) {
return new DataSourceTransactionManager(ds);
}
@Bean(name = SQLSESSION_FACTORY)
public SqlSessionFactory sqlSessionFactory(@Qualifier(DATA_SOURCE) DataSource ds) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(ds);
return bean.getObject();
}
}
不同数据源对应的Mapper的使用
package zxw.app.udpo.core.mapper.first;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import zxw.app.udpo.core.entity.Dictionary;
/**
* @title: DictionaryMapper
* @description:
* @author: zhangxw
* @date: 2017/9/27 10:30
* @params:
* @returns
*/
@Mapper
public interface DictionaryMapper1 {
@Select("select * from dictionary where code = #{code}")
Dictionary findByCode(Integer code);
@Select("select count(*) from dictionary")
Integer count();
}
package zxw.app.udpo.core.mapper.second;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import zxw.app.udpo.core.entity.Dictionary;
/**
* @title: DictionaryMapper
* @description:
* @author: zhangxw
* @date: 2017/9/27 10:30
* @params:
* @returns
*/
@Mapper
public interface DictionaryMapper2 {
@Select("select * from dictionary where code = #{code}")
Dictionary findByCode(Integer code);
@Select("select count(*) from dictionary")
Integer count();
}
application.xml的配置
datasource:
first:
username: root
password: root
url: jdbc:mysql://192.168.56.101:3306/udpo?characterEncoding=utf8&useSSL=true
driver-class-name: com.mysql.jdbc.Driver
tomcat:
max-active: 20
max-idle: 10
max-wait: 10000
min-evictable-idle-time-millis: 60000
min-idle: 3
initial-size: 5
validation-query: select 1 limit 1
validation-interval: 30000
validation-query-timeout: 20
test-on-borrow: true
test-on-connect: true
test-on-return: true
test-while-idle: true
time-between-eviction-runs-millis: 5000
remove-abandoned: true
remove-abandoned-timeout: 300
jdbc-interceptors: org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer
rollback-on-return: true
commit-on-return: false
alternate-username-allowed: false
jmx-enabled: false
second:
username: root
password: root
url: jdbc:mysql://192.168.56.101:3306/udpo_b?characterEncoding=utf8&useSSL=true
driver-class-name: com.mysql.jdbc.Driver
tomcat:
max-active: 20
max-idle: 10
max-wait: 10000
min-evictable-idle-time-millis: 60000
min-idle: 3
initial-size: 5
validation-query: select 1 limit 1
validation-interval: 30000
validation-query-timeout: 20
test-on-borrow: true
test-on-connect: true
test-on-return: true
test-while-idle: true
time-between-eviction-runs-millis: 5000
remove-abandoned: true
remove-abandoned-timeout: 300
jdbc-interceptors: org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer
rollback-on-return: true
commit-on-return: false
alternate-username-allowed: false
jmx-enabled: false
注意
添加@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class}), 如下:
package zxw.app.udpo.core;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
/**
* Hello world!
*
*/
@SpringBootApplication
// Don't forget exclude datasource auto configuration on start class:
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
@ComponentScan(basePackages = "zxw.app.udpo.core")
public class App
{
public static void main( String[] args )
{
SpringApplication.run(App.class, args);
}
}
如果有问题,欢迎留言,一起探讨
