Spring Boot 2.X: MyBatis Multiple Data Source Configuration

Keywords: Programming Mybatis Spring Database xml

Preface

Mybatis multi data source configuration. Recently, in the construction of the project, a new business module needs to be expanded on the original system, and the database is specially divided into databases, so as to reduce the complexity. This article uses a simple code example to illustrate how to configure MyBatis multiple data sources.

Get ready

Create a database db_test

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'User name',
  `user_sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'User gender',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_user
-- ----------------------------
BEGIN;
INSERT INTO `t_user` VALUES (1, 'Liu Bei', 'male');
INSERT INTO `t_user` VALUES (2, 'Sun Shang Xiang', 'female');
INSERT INTO `t_user` VALUES (3, 'Zhou Yu', 'male');
INSERT INTO `t_user` VALUES (4, 'Little Joe', 'female');
INSERT INTO `t_user` VALUES (5, 'Zhu Geliang', 'male');
INSERT INTO `t_user` VALUES (6, 'Huang Yue Ying', 'female');
INSERT INTO `t_user` VALUES (7, 'Guan Yu', 'male');
INSERT INTO `t_user` VALUES (8, 'Zhang Fei', 'male');
INSERT INTO `t_user` VALUES (9, 'Zhao Yun', 'male');
INSERT INTO `t_user` VALUES (10, 'Huang Zhe', 'male');
INSERT INTO `t_user` VALUES (11, 'Cao Cao', 'male');
INSERT INTO `t_user` VALUES (12, 'Sima Yi', 'male');
INSERT INTO `t_user` VALUES (13, 'army officer's hat ornaments', 'female');
INSERT INTO `t_user` VALUES (14, 'Lv Bu', 'male');
INSERT INTO `t_user` VALUES (15, 'Ma Chao', 'male');
INSERT INTO `t_user` VALUES (16, 'Wei Yan', 'male');
INSERT INTO `t_user` VALUES (17, 'Meng Huo', 'male');
INSERT INTO `t_user` VALUES (18, 'Big Joe', 'female');
INSERT INTO `t_user` VALUES (19, 'Liu Chan', 'male');
INSERT INTO `t_user` VALUES (20, 'Jiang Wei', 'male');
INSERT INTO `t_user` VALUES (21, 'Urbanization', 'male');
INSERT INTO `t_user` VALUES (22, 'Guan Ping', 'male');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

dbb_test2


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_hero
-- ----------------------------
DROP TABLE IF EXISTS `t_hero`;
CREATE TABLE `t_hero` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `hero_code` varchar(32) DEFAULT NULL COMMENT 'Hero code',
  `hero_name` varchar(20) DEFAULT NULL COMMENT 'Hero name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_hero
-- ----------------------------
BEGIN;
INSERT INTO `t_hero` VALUES (1, '001', 'De Marcia');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Building projects, project directory structure

pom file

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.9.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>cn.zwqh</groupId>
	<artifactId>spring-boot-mybatis-mulidatasource</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>spring-boot-mybatis-mulidatasource</name>
	<description>spring-boot-mybatis-mulidatasource</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</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-jdbc</artifactId>
		</dependency>
		
		<!-- Hot deployment module -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<optional>true</optional> <!-- This needs to be true Hot Deployment Is Effective -->
		</dependency>
				
		<!-- mysql Database Driver. -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		
		<!-- mybaits -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.0</version>
		</dependency>
		
		<!-- alibaba Of druid Database connection pool -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.20</version>
		</dependency>
		
		<!-- pagehelper -->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.2.12</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

alibaba's druid database connection pool is used here. druid can provide powerful monitoring and extension functions. For the time being, we will only do simple applications.

configuration file

#master data source configuration
master.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
master.datasource.url=jdbc:mysql://127.0.0.1:3306/db_test?useUnicode=true&characterEncoding=UTF-8&useSSL=true
master.datasource.username=root
master.datasource.password=zwqh@0258
#slave data source configuration
slave.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
slave.datasource.url=jdbc:mysql://127.0.0.1:3306/db_test2?useUnicode=true&characterEncoding=UTF-8&useSSL=true
slave.datasource.username=root
slave.datasource.password=zwqh@0258
#mybatis
mybatis.mapper-locations=classpath:/mapper/**/*Mapper.xml

Data source configuration

MasterDataSourceConfig corresponding database db_test

@Configuration
@MapperScan(basePackages = "cn.zwqh.springboot.dao.master", sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
	@Value("${master.datasource.driver-class-name}")
	private String driverClassName;

	@Value("${master.datasource.url}")
	private String url;

	@Value("${master.datasource.username}")
	private String username;

	@Value("${master.datasource.password}")
	private String password;

	@Bean(name = "masterDataSource")
	@Primary
	public DataSource dataSource() {
		DruidDataSource dataSource = new DruidDataSource();
		dataSource.setDriverClassName(this.driverClassName);
		dataSource.setUrl(this.url);
		dataSource.setUsername(this.username);
		dataSource.setPassword(this.password);
		return dataSource;
	}

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

	@Bean(name = "masterTransactionManager")
	@Primary
	public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	@Bean(name = "masterSqlSessionTemplate")
	@Primary
	public SqlSessionTemplate testSqlSessionTemplate(
			@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

SlaveDataSourceConfig corresponding database db_test2

@Configuration
@MapperScan(basePackages = "cn.zwqh.springboot.dao.slave", sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourceConfig {
	@Value("${slave.datasource.driver-class-name}")
	private String driverClassName;

	@Value("${slave.datasource.url}")
	private String url;

	@Value("${slave.datasource.username}")
	private String username;

	@Value("${slave.datasource.password}")
	private String password;

	@Bean(name = "slaveDataSource")
	public DataSource dataSource() {
		DruidDataSource dataSource = new DruidDataSource();
		dataSource.setDriverClassName(this.driverClassName);
		dataSource.setUrl(this.url);
		dataSource.setUsername(this.username);
		dataSource.setPassword(this.password);
		return dataSource;
	}

	@Bean(name = "slaveSqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		bean.setMapperLocations(
				new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/slave/*Mapper.xml"));
		return bean.getObject();
	}

	@Bean(name = "slaveTransactionManager")
	public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	@Bean(name = "slaveSqlSessionTemplate")
	public SqlSessionTemplate testSqlSessionTemplate(
			@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

In the process of using multiple data sources, the main library must be specified, otherwise the error will be reported. @ MapperScan (base Packages = cn.zwqh.springboot.dao.slave) specifies the scanning path corresponding to the Dao layer.

dao layer and xml layer

The Dao layer of db_test database is under cn.zwqh.springboot.dao.master package, and the Dao layer of db_test 2 database is under cn.zwqh.springboot.dao.slave package.

UserDao

public interface UserDao {

	List<UserEntity> getAll();

}

HeroDao

public interface HeroDao {

	List<Hero> getAllHero();

}

The xml layer of db_test database is in / mapper/master / file path, and the xml layer of db_test2 database is in / mapper/slave / file path.

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.zwqh.springboot.dao.master.UserDao">
	<resultMap type="cn.zwqh.springboot.model.UserEntity" id="user">
		<id property="id" column="id"/>
		<result property="userName" column="user_name"/>
		<result property="userSex" column="user_sex"/>
	</resultMap>
	<!-- Get all users -->
	<select id="getAll" resultMap="user">
		select * from t_user
	</select>
</mapper>

HeroMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.zwqh.springboot.dao.slave.HeroDao">
	<resultMap type="cn.zwqh.springboot.model.Hero" id="hero">
		<id property="id" column="id"/>
		<result property="heroCode" column="hero_code"/>
		<result property="heroName" column="hero_name"/>
	</resultMap>
	<!-- Get all users -->
	<select id="getAllHero" resultMap="hero">
		select * from t_hero
	</select>
</mapper>

test

Testing can be done using Spring BootTest or in Controller, which is customary for individuals.

@RestController
@RequestMapping("/test")
public class TestController {
	
	@Autowired
	private UserDao userDao;
	@Autowired
	private HeroDao heroDao;
	
	/**
	 *  Find all users
	 * @return
	 */
	@RequestMapping("/getAllUser")
	public List<UserEntity> getAllUser(){
		return userDao.getAll(); 
	}
	/**
	 *  Find all the heroes
	 * @return
	 */
	@RequestMapping("/getAllHero")
	public List<Hero> getAllHero(){
		return heroDao.getAllHero();
	}
	
}

Browser direct access: http://127.0.0.1:8080/test/ plus relevant test paths.

summary

Multiple data sources are generally used in master-slave mode or in business-based repositories.

Sample code

github Code cloud

Unless otherwise specified, the copyright of this article belongs to Chaowu QingHan. Please indicate the source of reprint.

Title: Spring Boot 2.X (V): MyBatis Multi-source Configuration

Original address: https://www.zwqh.top/article/info/12

Posted by ksp on Fri, 11 Oct 2019 20:51:11 -0700

Programmer Group