1. 业务需求
在开发中经常遇到主从模式或者业务比较复杂需要连接不同的分库来完成业务时需要使用到多数据源。在一个应用程序中需要访问主库又要访问从库,Spring Boot整合MyBatis-plus如何实现多数据源?
2. 数据库准备
新建两个数据库,zw_test1和zw_test2,在两个数据库中分别建表sys_user,并添加不同的记录,比如zw_test1库中的sys_user表存储周杰伦的数据,zw_test2库的sys_user表中存储刘德华的数据。
2.1 建表语句
# 建表
CREATE TABLE `sys_user`(
`pk_id` INT AUTO_INCREMENT,
`username` VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
`sex` CHAR(1) NOT NULL,
`birthday` DATETIME NOT NULL,
CONSTRAINT `pk_user` PRIMARY KEY(`pk_id`)
);
# 添加数据
INSERT INTO
`sys_user`(`pk_id`, `username`, `password`, `sex`, `birthday`)
VALUES
(NULL, '刘德华', '987654321', '男', NOW());
# 查询数据
SELECT * FROM `sys_user`;
3. 应用项目
- Windows 10
- IDEA 2020
- Java 1.8.0
- Maven 3.6.3
- Spring Boot 2.3.5.RELASE
3.1 项目结构
3.2 依赖管理
<?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>com.xxxx</groupId>
<artifactId>mybatis-plus-spring-boot-example</artifactId>
<version>1.0-SNAPSHOT</version>
<description>Spring Boot整合MyBatis-plus案例</description>
<!-- 版本定义 -->
<properties>
<spring-boot-version>2.3.5.RELEASE</spring-boot-version>
<mysql-version>5.1.32</mysql-version>
<mybatis-plus-version>3.4.0</mybatis-plus-version>
</properties>
<!-- 依赖管理 -->
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot-version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<!-- 依赖导入 -->
<dependencies>
<!-- spring-boot-web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql-version}</version>
</dependency>
<!-- mybatis-plus-boot -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus-version}</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
</project>
3.3 配置文件
server:
port: 80
spring:
datasource:
# 主数据源
primary:
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/zw_test1?useSSL=false
username: root
password: root
# 次数据源
second:
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/zw_test2?useSSL=false
username: root
password: root
3.4 手动配置
配置两个数据源、两个会话工厂和两个会话模板,并通过@MapperScan注解指定扫描接口和会话工厂以及会话模板,以达到不同的Mapper接口使用不同的数据源访问不同的数据库。
package com.xxxx.config;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.MybatisXMLLanguageDriver;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.transaction.SpringManagedTransactionFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
/**
* @ClassName: MyBatisPlusConfig
* @Description: MyBatisPlus配置类
* @Author: zhouwei
* @Date: 2021-6-12 - 20:49
*/
@SpringBootConfiguration
@MapperScan(basePackages = "com.xxxx.mapper.primary", sqlSessionFactoryRef = "primarySqlSessionFactory", sqlSessionTemplateRef = "primarySqlSessionTemplate")
@MapperScan(basePackages = "com.xxxx.mapper.second", sqlSessionFactoryRef = "secondSqlSessionFactory", sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class MyBatisPlusConfig
{
/**
* 主数据源
*/
@Bean(name = "primaryDataSource")
@ConfigurationProperties("spring.datasource.primary")
public DataSource primaryDataSource()
{
return DataSourceBuilder.create().build();
}
/**
* 次数据源
*/
@Bean(name = "secondDataSource")
@ConfigurationProperties("spring.datasource.second")
public DataSource secondDataSource()
{
return DataSourceBuilder.create().build();
}
/**
* 主会话工厂
*/
@Bean("primarySqlSessionFactory")
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception
{
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setTypeAliasesPackage("com.xxxx.bean.domain");
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
configuration.setJdbcTypeForNull(JdbcType.NULL);
sqlSessionFactoryBean.setConfiguration(configuration);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/xxxx/mapper/primary/*.xml"));
sqlSessionFactoryBean.setTransactionFactory(new SpringManagedTransactionFactory());
return sqlSessionFactoryBean.getObject();
}
/**
* 次会话工厂
*/
@Bean("secondSqlSessionFactory")
public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception
{
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setTypeAliasesPackage("com.xxxx.bean.domain");
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
configuration.setJdbcTypeForNull(JdbcType.NULL);
sqlSessionFactoryBean.setConfiguration(configuration);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/xxxx/mapper/second/*.xml"));
sqlSessionFactoryBean.setTransactionFactory(new SpringManagedTransactionFactory());
return sqlSessionFactoryBean.getObject();
}
/**
* 主会话模板
*/
@Bean("primarySqlSessionTemplate")
public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory)
{
SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
return sqlSessionTemplate;
}
/**
* 次会话模板
*/
@Bean("secondSqlSessionTemplate")
public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
{
SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
return sqlSessionTemplate;
}
/**
* 分页插件
*/
@Bean
public PaginationInnerInterceptor paginationInnerInterceptor()
{
return new PaginationInnerInterceptor();
}
}
3.5 启动程序
package com.xxxx;
import com.xxxx.bean.domain.SysUserDo;
import com.xxxx.mapper.primary.PrimarySysUserMapper;
import com.xxxx.mapper.second.SecondSysUserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import java.util.List;
/**
* @ClassName: SpringbootApplication
* @Description: 应用程序启动类
* @Author: zhouwei
* @Date: 2021-6-12 - 20:10
*/
@SpringBootApplication
public class SpringbootApplication implements ApplicationRunner
{
@Autowired
private PrimarySysUserMapper primarySysUserMapper;
@Autowired
private SecondSysUserMapper secondSysUserMapper;
public static void main(String[] args)
{
// 启动应用程序
SpringApplication.run(SpringbootApplication.class, args);
}
@Override
public void run(ApplicationArguments args) throws Exception
{
// 查询主库系统用户
List<SysUserDo> users1 = primarySysUserMapper.listSysUserInformation();
System.out.println("users1 = " + users1);
// 查询从库系统用户
List<SysUserDo> users2 = secondSysUserMapper.listSysUserInformation();
System.out.println("users2 = " + users2);
}
}