Implementation of Spring Boot MyBatis Database Cluster Access
This example mainly introduces Spring Boot program to realize database cluster access and load balancing by reading and polling. Before reading this example, I suggest that you have The Foundation of AOP Programming The basic functions of mybatis will be used and the basic concepts of database cluster will be used so that you can understand and implement it faster.
MySql master-slave configuration
Refer to Configuration< MySQL master-slave replication configuration>
Implementation of Spring Boot
The separation of reading and writing is to choose which database to execute for an SQL. Generally speaking, there are two main ways to implement this separation: one is to select which database to execute, the other is to choose which database to execute.
- 1. Use middleware, such as Atlas, cobar, TDDL, mycat, heisenberg, Oceanus, vitess, OneProxy, etc.
- 2. Using the program itself, using the routing data source provided by Spring Boot and AOP, the implementation is simple and fast (the method introduced in this article)
Program Code Implementation
1. First, we configure pom.xml to add the necessary dependencies for the example
<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>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> </dependencies>
2. Routing DataSource. Java
Routing to a specific data source based on a specific key. It maintains a set of target data sources internally, and maps the routing key to the target data source, providing a key-based method to find the data source.
a. Class diagram
b. The code is simple, just call the get method of DBContext.
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; public class RoutingDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DBContext.get(); } }
3. Data source context class DBContext.java
import com.easy.mybatis.multidatasource.enums.DBTypeEnum; import lombok.extern.slf4j.Slf4j; import java.util.concurrent.atomic.AtomicInteger; @Slf4j public class DBContext { private static final ThreadLocal<DBTypeEnum> dbContext = new ThreadLocal<>(); private static final AtomicInteger counter = new AtomicInteger(-1); public static void set(DBTypeEnum dbType) { dbContext.set(dbType); } public static DBTypeEnum get() { return dbContext.get(); } public static void master() { set(DBTypeEnum.MASTER); log.info("switch to master library"); } public static void slave() { // Load Balancing of Reader Library (Polling Mode) int index = counter.getAndIncrement() % 2; log.info("slave Number of Threads for Library Access==>{}", counter.get()); if (index == 0) { set(DBTypeEnum.SLAVE1); log.info("switch to slave1 library"); } else { set(DBTypeEnum.SLAVE2); log.info("switch to slave2 library"); } } }
4. Database Enumeration Class DBTypeEnum.java
public enum DBTypeEnum { MASTER, SLAVE1, SLAVE2 }
Here we configure three libraries: a write library Master, two read libraries slave1,slave2.
5. Database configuration class DataSourceConfig.java
package com.easy.mybatis.multidatasource.config; import com.easy.mybatis.multidatasource.enums.DBTypeEnum; 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 javax.sql.DataSource; import java.util.HashMap; import java.util.Map; @Configuration public class DataSourceConfig { @Bean @ConfigurationProperties("spring.datasource.master") public DataSource masterDataSource() { return DataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("spring.datasource.slave1") public DataSource slave1DataSource() { return DataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("spring.datasource.slave2") public DataSource slave2DataSource() { return DataSourceBuilder.create().build(); } @Bean public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource, @Qualifier("slave1DataSource") DataSource slave1DataSource, @Qualifier("slave2DataSource") DataSource slave2DataSource) { Map<Object, Object> targetDataSources = new HashMap<>(); targetDataSources.put(DBTypeEnum.MASTER, masterDataSource); targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource); targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource); RoutingDataSource routingDataSource = new RoutingDataSource(); routingDataSource.setDefaultTargetDataSource(masterDataSource); routingDataSource.setTargetDataSources(targetDataSources); return routingDataSource; } }
6.mybatis configuration class DataSourceConfig.java
package com.easy.mybatis.multidatasource.config; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; 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 org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.annotation.Resource; import javax.sql.DataSource; @EnableTransactionManagement @Configuration @MapperScan("com.easy.mybatis.multidatasource.mapper") public class MyBatisConfig { @Resource(name = "myRoutingDataSource") private DataSource myRoutingDataSource; @Bean public SqlSessionFactory sqlSessionFactory() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(myRoutingDataSource); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml")); return sqlSessionFactoryBean.getObject(); } @Bean public PlatformTransactionManager platformTransactionManager() { return new DataSourceTransactionManager(myRoutingDataSource); } }
7. Tangent class DataSourceAop.java
package com.easy.mybatis.multidatasource.config; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Before; import org.aspectj.lang.annotation.Pointcut; import org.springframework.stereotype.Component; @Aspect @Component public class DataSourceAop { @Pointcut("@annotation(com.easy.mybatis.multidatasource.annotation.Master) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.insert*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.add*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.update*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.edit*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.delete*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.remove*(..))") public void writePointcut() { } @Pointcut("!@annotation(com.easy.mybatis.multidatasource.annotation.Master) " + "&& (execution(* com.easy.mybatis.multidatasource.service..*.select*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.get*(..)))") public void readPointcut() { } @Before("writePointcut()") public void write() { DBContext.master(); } @Before("readPointcut()") public void read() { DBContext.slave(); } }
8. Annotation class Master.java
package com.easy.mybatis.multidatasource.annotation; /** * Main Library, Readable and Writable */ public @interface Master { }
9. User's xml,mapper,service classes
UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.easy.mybatis.multidatasource.mapper.UserMapper"> <select id="selectById" resultType="com.easy.mybatis.multidatasource.entity.User" parameterType="int"> SELECT * from user WHERE id = #{id} </select> <select id="selectList" resultType="com.easy.mybatis.multidatasource.entity.User"> SELECT * from user </select> <insert id="insert" parameterType="com.easy.mybatis.multidatasource.entity.User"> INSERT into user(id,name,age,email) VALUES(#{id}, #{name},#{age},#{email}) </insert> <update id="updateById" parameterType="com.easy.mybatis.multidatasource.entity.User"> UPDATE user SET name =#{name}, age =#{age},email =#{email} WHERE id =#{id} </update> <delete id="deleteById" parameterType="int"> DELETE FROM user WHERE id =#{id} </delete> </mapper>
UserMapper.java
package com.easy.mybatis.multidatasource.mapper; import com.easy.mybatis.multidatasource.entity.User; import org.springframework.stereotype.Repository; import java.io.Serializable; import java.util.List; @Repository public interface UserMapper { /** * Insert a record * * @param entity Entity object */ int insert(User entity); /** * Delete by ID * * @param id Primary key ID */ int deleteById(Serializable id); /** * Modify according to ID * * @param entity Entity object */ int updateById(User entity); /** * Query by ID * * @param id Primary key ID */ User selectById(Serializable id); List<User> selectList(); }
UserServiceImpl.java
package com.easy.mybatis.multidatasource.service.impl; import com.easy.mybatis.multidatasource.annotation.Master; import com.easy.mybatis.multidatasource.entity.User; import com.easy.mybatis.multidatasource.mapper.UserMapper; import com.easy.mybatis.multidatasource.service.IUserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.io.Serializable; import java.util.List; @Service public class UserServiceImpl implements IUserService { @Autowired private UserMapper userMapper; /** * Insert a record * * @param entity Entity object */ @Override public int insert(User entity) { return userMapper.insert(entity); } /** * Delete by ID * * @param id Primary key ID */ @Override public int deleteById(Serializable id) { return userMapper.deleteById(id); } /** * Modify according to ID * * @param entity Entity object */ @Override public int updateById(User entity) { return userMapper.updateById(entity); } /** * Query by ID * * @param id Primary key ID */ @Master @Override public User selectById(Serializable id) { return userMapper.selectById(id); } @Override public List<User> selectList() { return userMapper.selectList(); } }
Here we notice that the slave library should have been accessed by selectById, and I've manually pointed it to the master library by annotation.
10. Finally, I pasted the yaml configuration file application.yml
# DataSource Config spring: datasource: master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/easy_web?useSSL=false&serverTimezone=UTC username: root password: 123456 slave1: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/easy_web?useSSL=false&serverTimezone=UTC username: root password: 123456 slave2: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/easy_web?useSSL=false&serverTimezone=UTC username: root password: 123456
The configuration file configures three data sources (here, for convenience, I point three data sources to the same library, the actual production environment will have different libraries and read-write users)
Write test cases, view execution results, and analyze database calls
1. Unit test class MultiDataSourceServiceTest.java
package com.easy.mybatis.multidatasource; import com.easy.mybatis.multidatasource.entity.User; import com.easy.mybatis.multidatasource.service.IUserService; import lombok.extern.slf4j.Slf4j; import org.junit.FixMethodOrder; import org.junit.Test; import org.junit.runner.RunWith; import org.junit.runners.MethodSorters; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import javax.annotation.Resource; import java.util.List; import static org.assertj.core.api.Assertions.assertThat; /** * <p> * Built-in CRUD demonstration * </p> */ @RunWith(SpringRunner.class) @SpringBootTest @Slf4j //Specify unit tests to be executed alphabetically @FixMethodOrder(value = MethodSorters.NAME_ASCENDING) public class MultiDataSourceServiceTest { @Resource private IUserService userService; @Test public void aInsert() { User user = new User(); user.setId(20l); user.setName("Lamb"); user.setAge(3); user.setEmail("abc@mp.com"); log.info("Start execution insert Method, id={}", user.getId()); assertThat(userService.insert(user)); // Successfully take the ID that can be written directly assertThat(user.getId()).isNotNull(); } @Test public void bUpdate() { User user = new User(); user.setId(20l); user.setName("Lamb update"); user.setAge(3); user.setEmail("abc@mp.com"); log.info("Start execution updateById Method, id={}", user.getId()); assertThat(userService.updateById(user) > 0); } @Test public void cSelectById() { int id = 20; log.info("Start execution selectById Method, id={}", id); log.info("The data are as follows:=={}", userService.selectById(id)); } @Test public void dDelete() { int id = 20; log.info("Start execution deleteById Method, id={}", id); assertThat(userService.deleteById(id)); } @Test public void eSelectList() { for (int i = 0; i < 5; i++) { log.info("Start execution selectList Method, index={}", i); List<User> list = userService.selectList(); log.info("The data queried are: list={}", list); } } }
2. View console execution results
2019-08-29 16:36:04.684 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Starting MultiDataSourceServiceTest on YHE6OR5UXQJ6D35 with PID 13028 (started by Administrator in E:\project\spring-boot-demo\mybatis-multi-datasource) 2019-08-29 16:36:04.685 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : No active profile set, falling back to default profiles: default 2019-08-29 16:36:08.172 INFO 13028 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor' 2019-08-29 16:36:08.814 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Started MultiDataSourceServiceTest in 4.85 seconds (JVM running for 5.918) 2019-08-29 16:36:09.008 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Start execution insert Method, id=20 2019-08-29 16:36:09.018 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : switch to master library 2019-08-29 16:36:09.054 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2019-08-29 16:36:09.256 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. 2019-08-29 16:36:09.547 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Start execution updateById Method, id=20 2019-08-29 16:36:09.548 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : switch to master library 2019-08-29 16:36:09.731 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Start execution selectById Method, id=20 2019-08-29 16:36:09.732 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : switch to master library 2019-08-29 16:36:10.213 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : The data are as follows:==User(id=20, name=Lamb update, age=3, email=abc@mp.com) 2019-08-29 16:36:10.216 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Start execution deleteById Method, id=20 2019-08-29 16:36:10.216 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : switch to master library 2019-08-29 16:36:10.402 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Start execution selectList Method, index=0 2019-08-29 16:36:10.403 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave Number of Threads for Library Access==>0 2019-08-29 16:36:10.403 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : switch to slave2 library 2019-08-29 16:36:10.405 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting... 2019-08-29 16:36:10.418 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed. 2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : The data queried are: list=[User(id=1, name=Jone, age=18, email=ab@c.c), User(id=2, name=mp, age=null, email=miemie2@baomidou.com), User(id=5, name=Billie, age=24, email=test5@tqlin.com)] 2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Start execution selectList Method, index=1 2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave Number of Threads for Library Access==>1 2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : switch to slave1 library 2019-08-29 16:36:10.422 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Starting... 2019-08-29 16:36:10.428 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Start completed. 2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : The data queried are: list=[User(id=1, name=Jone, age=18, email=ab@c.c), User(id=2, name=mp, age=null, email=miemie2@baomidou.com), User(id=5, name=Billie, age=24, email=test5@tqlin.com)] 2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Start execution selectList Method, index=2 2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave Number of Threads for Library Access==>2 2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : switch to slave2 library 2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : The data queried are: list=[User(id=1, name=Jone, age=18, email=ab@c.c), User(id=2, name=mp, age=null, email=miemie2@baomidou.com), User(id=5, name=Billie, age=24, email=test5@tqlin.com)] 2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Start execution selectList Method, index=3 2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave Number of Threads for Library Access==>3 2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : switch to slave1 library 2019-08-29 16:36:10.432 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : The data queried are: list=[User(id=1, name=Jone, age=18, email=ab@c.c), User(id=2, name=mp, age=null, email=miemie2@baomidou.com), User(id=5, name=Billie, age=24, email=test5@tqlin.com)] 2019-08-29 16:36:10.432 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Start execution selectList Method, index=4 2019-08-29 16:36:10.433 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave Number of Threads for Library Access==>4 2019-08-29 16:36:10.433 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : switch to slave2 library 2019-08-29 16:36:10.435 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : The data queried are: list=[User(id=1, name=Jone, age=18, email=ab@c.c), User(id=2, name=mp, age=null, email=miemie2@baomidou.com), User(id=5, name=Billie, age=24, email=test5@tqlin.com)] 2019-08-29 16:36:10.444 INFO 13028 --- [ Thread-2] o.s.s.concurrent.ThreadPoolTaskExecutor : Shutting down ExecutorService 'applicationTaskExecutor' 2019-08-29 16:36:10.446 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Shutdown initiated... 2019-08-29 16:36:10.463 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Shutdown completed. 2019-08-29 16:36:10.463 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Shutdown initiated... 2019-08-29 16:36:10.497 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Shutdown completed. 2019-08-29 16:36:10.497 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated... 2019-08-29 16:36:10.500 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
- We see that insert,updateById,deleteById methods of service execute writable libraries (master libraries)
- And the selectById method, because we manually cut the master library in service by annotation, so the data will not be read by slave library.
- The selectList method, which we called five times in a loop, was successfully read in the slave1 and slave2 polls