Spring boot + mybatis 多数据源解决方案

这篇文档教大家配置Spring Boot、MyBatis整合多数据源的配置方法。

一、数据库连接配置

Spring Boot启动的application.yml配置文件配置数据库的连接配置，代码如如下:

#访问端口
server:
 port: 8480
#mybatis配置
mybatis:
 config-location: classpath:mybatis/mybatis-config.xml
#mybatis分页插件
pagehelper:
 helperDialect:  mysql 
 reasonable: true
 supportMethodsArguments: true
  params : count=countSql
#jdbc url
spring:
 main:
 allow-bean-definition-overriding: true
 session:
 store-type: none
 datasource:
 test1:
  jdbc -url: jdbc:mysql://10.10.1.232:3306/zhlc_demo?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
 username: zhlc
 password: zhlc123
 driver-class-name: com.mysql.jdbc.Driver
 test2:
 jdbc-url: jdbc:mysql://10.10.1.96:31938/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
 username: root
 password: root
 driver-class-name: com.mysql.jdbc.Driver
 

其中server.port为项目启动端口号，项目访问根路径为。spring.datasource.test1为连接的第一个数据库的数据源（相应的jdbc连接url、MySQL用户名密码）。spring.datasource.test2为连接的第二个数据库的数据源。并加上相应的驱动。

注意：因为使用的是application.yml为yml格式的文件，因此每一级的层级关系为

呈递进关系，逐级空行。

mybatis-config.xml在src/main/resources/mybatis目录下

mybatis-config.xml文件的内容为：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE  Configuration  PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "">
<configuration>
 <typeAliases>
 <typeAlias alias="Integer" type="java.lang.Integer" />
 <typeAlias alias="Long" type="java.lang.Long" />
 <typeAlias alias="HashMap" type="java.util.HashMap" />
 <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
 <typeAlias alias="ArrayList" type="java.util.ArrayList" />
 <typeAlias alias="LinkedList" type="java.util.LinkedList" />
 </typeAliases>
</configuration>
 

二、数据源DataSource配置

新建包

com.ocai.core.datasource
 

在该包下新建DataSource1Config.java文件，文件上部加上包扫描注解

表示此数据源文件扫描com.ocai.core.mapper.test1包下的 DAO 文件。

数据源建立如下

spring.datasource.test1为前面application.yml中的数据源配置。

建立sqlSession factory ，如下，并注入datasource

配置事务管理器transactionManager

配置sqlSessionTemplate如下

完整的DataSourceConfig文件代码如下:

package com.ocai.core.datasource;
import com.github.pagehelper.PageInterceptor;
import org.apache. ibatis .plugin.Interceptor;
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;
import java.util.Properties;
/**
 * Created by liyue on 2019/07/12.
 */
@Configuration
@MapperScan(basePackages = "com.ocai.core.mapper.test1", sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DataSource1Config {
 @Bean(name = "test1DataSource")
 @ConfigurationProperties(prefix = "spring.datasource.test1")
 @Primary
 public DataSource testDataSource() {
 return DataSourceBuilder.create().build();
 }
 @Bean(name = "test1SqlSessionFactory")
 @Primary
 public SqlSessionFactory testSqlSessionFactory( @Qualifier ("test1DataSource") DataSource dataSource) throws Exception {
 SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
 bean.setDataSource(dataSource);
 //分页插件
 Interceptor interceptor = new PageInterceptor();
 Properties properties = new Properties();
 properties.setProperty("helperDialect", "mysql");
 properties.setProperty("offsetAsPageNum", "true");
 properties.setProperty("rowBoundsWithCount", "true");
 properties.setProperty("reasonable", "true");
 properties.setProperty("supportMethodsArguments","true");
 properties.setProperty("params","pageNum=pageNumKey;pageSize=pageSizeKey;");
 interceptor.setProperties(properties);
 bean.setPlugins(new Interceptor[] {interceptor});
 bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml"));
 return bean.getObject();
 }
 @Bean(name = "test1TransactionManager")
 @Primary
 public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
 return new DataSourceTransactionManager(dataSource);
 }
 @Bean(name = "test1SqlSessionTemplate")
 @Primary
 public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
 return new SqlSessionTemplate(sqlSessionFactory);
 }
}
 

三、项目辅助文件介绍

UserSexEnum枚举类如下

异常处理类如下

全局异常拦截处理如下:

服务器Servel异常如下:

结果处理类如下

错误码如图

封装返回结果实体

通用结果处理工具类如图

实体类User如图

四、数据源1的DAO层

在com.ocai.core.mapper.test1包下面的DAO层UserMapper代码如下:

对应的src/main/resources/mybatis/mapper/test1下面的xml文件如图

五、配置数据源2

在com.ocai.core.datasource新建DataSourceConfig2.java配置数据源2扫描的DAO

对应的MyBatis xml扫描为

DataSourceConfig2.java完整代码为

package com.ocai.core.datasource;
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.plugin.Interceptor;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.Properties;
/**
 * Created by liyue on 2019/07/12.
 */
@Configuration
@MapperScan(basePackages = "com.ocai.core.mapper.test2", sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class DataSource2Config {
 @Bean(name = "test2DataSource")
 @ConfigurationProperties(prefix = "spring.datasource.test2")
 public DataSource testDataSource() {
 return DataSourceBuilder.create().build();
 }
 @Bean(name = "test2SqlSessionFactory")
 public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
 SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
 bean.setDataSource(dataSource);
 //分页插件
 Interceptor interceptor = new PageInterceptor();
 Properties properties = new Properties();
 properties.setProperty("helperDialect", "mysql");
 properties.setProperty("offsetAsPageNum", "true");
 properties.setProperty("rowBoundsWithCount", "true");
 properties.setProperty("reasonable", "true");
 properties.setProperty("supportMethodsArguments","true");
 properties.setProperty("params","pageNum=pageNumKey;pageSize=pageSizeKey;");
 interceptor.setProperties(properties);
 bean.setPlugins(new Interceptor[] {interceptor});
 bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test2/*.xml"));
 return bean.getObject();
 }
 @Bean(name = "test2TransactionManager")
 public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
 return new DataSourceTransactionManager(dataSource);
 }
 @Bean(name = "test2SqlSessionTemplate")
 public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
 return new SqlSessionTemplate(sqlSessionFactory);
 }
}
 

第2个数据源的DAO层UserMapper在com.ocai.core.mapper.test2目录下：

数据源2MyBatis xml文件如下

六、Service层

数据源1Service层完整代码如下:

package com.ocai.core.service.impl;
import com.ocai.core.mapper.test1.UserMapper;
import com.ocai.core.model.User;
import com.ocai.core.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service("userService")
@Transactional
public class UserServiceImpl implements UserService {
 @Autowired
 private UserMapper userMapper1;
 @Override
 public List<User> getAll() {
 return userMapper1.getAll();
 }
}
 

数据源2Service层完整代码如下:

package com.ocai.core.service.impl;
import com.ocai.core.mapper.test2.UserMapper2;
import com.ocai.core.model.User;
import com.ocai.core.service.UserService2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service("userService2")
@Transactional
public class UserServiceImpl2 implements UserService2 {
 @Autowired
 private UserMapper2 userMapper2;
 @Override
 public List<User> getAll() {
 return userMapper2.getAll();
 }
}
 

七、Controller根据输入参数切换数据源

八、录入数据

数据源1users表录入数据

数据源2users表录入数据

九、Swagger配置

十、启动看结果

启动项目，打开Swagger：

num输入1，点击Try it out

num输入2，点击Try it out

同一个实体类User，数据源切换成功！