Spring Boot 2.X(5) : MyBatis multi-data source configuration

preface

MyBatis multi-data source configuration, in the recent project construction, a new business module needs to be extended on the original system, and the database is specially divided into libraries to reduce the complexity. This article directly with a simple code example, how to configure MyBatis multi-data source.

To prepare

Create 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 Shangxiang'.'woman');
INSERT INTO `t_user` VALUES (3.'zhou yu'.'male');
INSERT INTO `t_user` VALUES (4.'Joe'.'woman');
INSERT INTO `t_user` VALUES (5.'Zhuge Liang'.'male');
INSERT INTO `t_user` VALUES (6.'Huang Yueying'.'woman');
INSERT INTO `t_user` VALUES (7.'guan yu'.'male');
INSERT INTO `t_user` VALUES (8.'zhang fei'.'male');
INSERT INTO `t_user` VALUES (9.'zhaoyun'.'male');
INSERT INTO `t_user` VALUES (10.'Ms. Huang'.'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.'the sable cicada'.'woman');
INSERT INTO `t_user` VALUES (14.'lyu3 bu4'.'male');
INSERT INTO `t_user` VALUES (15.'d'.'male');
INSERT INTO `t_user` VALUES (16."Wei".'male');
INSERT INTO `t_user` VALUES (17.'meng huo'.'male');
INSERT INTO `t_user` VALUES (18.'big Joe'.'woman');
INSERT INTO `t_user` VALUES (19.'Sara'.'male');
INSERT INTO `t_user` VALUES (20.'jiang wei'.'male');
INSERT INTO `t_user` VALUES (21.'Gideon'.'male');
INSERT INTO `t_user` VALUES (22.'even close'.'male');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
Copy the code

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'.'Demacia');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
Copy the code

Build the project, 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 - the 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 requires hot deployment to be true -->
		</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 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>

Copy the code

Alibaba’s Druid database connection pool is used here, and Druid provides powerful monitoring and extension capabilities. We’ll just do a simple application for now.

The configuration file

Master data source configurationMaster. The datasource. The driver - class - name = com. Mysql. Cj. JDBC. The driver master. The 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 configurationSlave. The datasource. The driver - class - name = com. Mysql. Cj. JDBC. Driver slave. The 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

Copy the code

Data Source Configuration

MasterDataSourceConfig corresponds to the 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(a) {
		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 newSqlSessionTemplate(sqlSessionFactory); }}Copy the code

SlaveDataSourceConfig corresponds to the 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(a) {
		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 newSqlSessionTemplate(sqlSessionFactory); }}Copy the code

The primary library must be specified when multiple data sources are used, otherwise an error will be reported. @ MapperScan (basePackages = “cn. ZWQH. Springboot. Dao. The slave”) specifies the corresponding dao layer scanning path.

Dao layer and XML layer

Db_test database dao layer in cn. ZWQH. Springboot. Dao. The master package, db_test2 dao database layer in cn. ZWQH. Springboot. Dao. The slave under the package.

UserDao

public interface UserDao {

	List<UserEntity> getAll(a);

}
Copy the code

HeroDao

public interface HeroDao {

	List<Hero> getAllHero(a);

}
Copy the code

The DATABASE XML layer of the db_test database is in the /mapper/master/ file path, and the DATABASE XML layer of the DB_Test2 database is in the /mapper/slave/ file path.

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"? >

      
<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>
Copy the code

HeroMapper.xml

<?xml version="1.0" encoding="UTF-8"? >

      
<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>
Copy the code

test

The tests can be done using Spring Creative Ttest or in Controller, which is my personal habit.

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

Direct access to the browser: http://127.0.0.1:8080/test/ and associated test path.

conclusion

Multiple data sources are generally used in master/slave mode or by business repository.

The sample code

github

Yards cloud

The copyright of this article belongs to Chaowu And Qinghan, please indicate the source.

Spring Boot 2.x (v) : MyBatis multi-data source configuration

The original address: https://www.zwqh.top/article/info/12

If the article is helpful to you, please scan the code to pay attention to my public number, the article continues to update…