This example mainly introduces the Spring Boot program to achieve database cluster access, read library polling to achieve load balancing. Before reading this example, it is recommended that you have AOP programming foundation, mybatis basic function can use, database cluster basic concept, so that you can quickly understand and implement it
Source code for this example
MySql primary/secondary configuration
For details about the configuration, see MySQL Primary/Secondary Replication Configuration.
Spring Boot implementation
Read/write separation is to select which database to execute a SQL, as for who to do the selection of database this matter, generally speaking, there are two main implementations, respectively:
- 1. Use middleware, such as Atlas, Cobar, TDDL, MyCAT, Heisenberg, Oceanus, Vitess, OneProxy, etc
- 2. Use the program to achieve their own, using Spring Boot to provide routing data sources and AOP, simple and fast implementation (this article to introduce the method)
Program code implementation
1. First let’s configure pom.xml and 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>
Copy the code2. The data source routing class function routingDatasource.java
Routing to a specific data source based on a specific key. It internally maintains a set of target data sources, and makes the mapping between routing keys and target data sources, and provides the method of searching data sources based on keys.
A. Class diagram
B. The code is very simple, just call DBContext’s get method
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey(a) {
returnDBContext.get(); }}Copy the code3. 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(a) {
return dbContext.get();
}
public static void master(a) {
set(DBTypeEnum.MASTER);
log.info("Switch to the master library");
}
public static void slave(a) {
// Read load balancing (polling mode)
int index = counter.getAndIncrement() % 2;
log.info("Slave library access thread count ==>{}", counter.get());
if (index == 0) {
set(DBTypeEnum.SLAVE1);
log.info("Switch to slave1 library");
} else {
set(DBTypeEnum.SLAVE2);
log.info("Switch to slave2 library"); }}}Copy the code4. Database enumeration class DBTypeEnum. Java
public enum DBTypeEnum {
MASTER, SLAVE1, SLAVE2
}
Copy the codeWrite library Master, read library Slave1, read library slave2
5. The 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(a) {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave1")
public DataSource slave1DataSource(a) {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave2")
public DataSource slave2DataSource(a) {
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);
returnroutingDataSource; }}Copy the code6. Mybatis configuredatasourCeconfig. 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(a) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
return sqlSessionFactoryBean.getObject();
}
@Bean
public PlatformTransactionManager platformTransactionManager(a) {
return newDataSourceTransactionManager(myRoutingDataSource); }}Copy the codeClass 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(a) {}@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(a) {}@Before("writePointcut()")
public void write(a) {
DBContext.master();
}
@Before("readPointcut()")
public void read(a) { DBContext.slave(); }}Copy the codeThe annotations class master.java
package com.easy.mybatis.multidatasource.annotation;
/** * main library, read and write */
public @interface Master {
}
Copy the code9. User XML, Mapper,service classes
UserMapper.xml
<?xml version="1.0" encoding="UTF-8"? >
<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>
Copy the codeUserMapper.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 **@paramEntity Entity object */
int insert(User entity);
/** * delete ** based on ID@paramId Primary key ID */
int deleteById(Serializable id);
/** * Change ** according to ID@paramEntity Entity object */
int updateById(User entity);
/** * query ** by ID@paramId Primary key ID */
User selectById(Serializable id);
List<User> selectList(a);
}
Copy the codeUserServiceImpl.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 **@paramEntity Entity object */
@Override
public int insert(User entity) {
return userMapper.insert(entity);
}
/** * delete ** based on ID@paramId Primary key ID */
@Override
public int deleteById(Serializable id) {
return userMapper.deleteById(id);
}
/** * Change ** according to ID@paramEntity Entity object */
@Override
public int updateById(User entity) {
return userMapper.updateById(entity);
}
/** * query ** by ID@paramId Primary key ID */
@Master
@Override
public User selectById(Serializable id) {
return userMapper.selectById(id);
}
@Override
public List<User> selectList(a) {
returnuserMapper.selectList(); }}Copy the codeNow, notice that the selectById is supposed to be accessing the slave library, so I’m annotating it to the master library manually.
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
Copy the codeThis configuration file configures three data sources (I refer to all three data sources as the same library for convenience, since the actual production environment will have different libraries and read-write users).
Write test cases, view execution results, and analyze database calls
1. The 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;
/** * * Built-in CRUD demo *
*/
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
// Specify that the unit tests are executed in alphabetical order
@FixMethodOrder(value = MethodSorters.NAME_ASCENDING)
public class MultiDataSourceServiceTest {
@Resource
private IUserService userService;
@Test
public void aInsert(a) {
User user = new User();
user.setId(20l);
user.setName("The lamb");
user.setAge(3);
user.setEmail("[email protected]");
log.info(Start insert method, id={}, user.getId());
assertThat(userService.insert(user));
// Successfully get the writable ID directly
assertThat(user.getId()).isNotNull();
}
@Test
public void bUpdate(a) {
User user = new User();
user.setId(20l);
user.setName("Lamb update");
user.setAge(3);
user.setEmail("[email protected]");
log.info("Start executing updateById method, id={}", user.getId());
assertThat(userService.updateById(user) > 0);
}
@Test
public void cSelectById(a) {
int id = 20;
log.info("Start executing selectById, id={}", id);
log.info("Data =={}", userService.selectById(id));
}
@Test
public void dDelete(a) {
int id = 20;
log.info("Start executing deleteById, id={}", id);
assertThat(userService.deleteById(id));
}
@Test
public void eSelectList(a) {
for (int i = 0; i < 5; i++) {
log.info("Start executing selectList, index={}", i);
List<User> list = userService.selectList();
log.info("List ={}", list); }}}Copy the code2. View the console execution result
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 : 开始执行insert方法，id=20
2019-08-29 16:36:09.018 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切换到master库
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 : 开始执行updateById方法，id=20
2019-08-29 16:36:09.548 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切换到master库
2019-08-29 16:36:09.731 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 开始执行selectById方法，id=20
2019-08-29 16:36:09.732 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切换到master库
2019-08-29 16:36:10.213 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 数据为==User(id=20, name=小羊update, age=3, [email protected])
2019-08-29 16:36:10.216 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 开始执行deleteById方法，id=20
2019-08-29 16:36:10.216 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切换到master库
2019-08-29 16:36:10.402 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 开始执行selectList方法，index=0
2019-08-29 16:36:10.403 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave库访问线程数==>0
2019-08-29 16:36:10.403 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切换到slave2库
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 : 查询到的数据为，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])]
2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 开始执行selectList方法，index=1
2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave库访问线程数==>1
2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切换到slave1库
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 : 查询到的数据为，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])]
2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 开始执行selectList方法，index=2
2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave库访问线程数==>2
2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切换到slave2库
2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 查询到的数据为，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])]
2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 开始执行selectList方法，index=3
2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave库访问线程数==>3
2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切换到slave1库
2019-08-29 16:36:10.432 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 查询到的数据为，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])]
2019-08-29 16:36:10.432 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 开始执行selectList方法，index=4
2019-08-29 16:36:10.433 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave库访问线程数==>4
2019-08-29 16:36:10.433 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切换到slave2库
2019-08-29 16:36:10.435 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 查询到的数据为，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])]
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.
Copy the code- We saw the service of the insert, updateById, deleteById method performs is writeable library (master)
- The selectById method will not be read from the slave library because we manually annotated the master library in the service.
- The selectList method, which we looped through five times, was successfully read in slave1 and Slave2 polling
data
- Mybatis -multi-datasource example source code
- Official website Data source configuration documents
- The resources
- Code Cloud warehouse Spring Boot, Spring Cloud example learning
- GitHub repository Spring Boot, Spring Cloud example learning