Spring Boot + MyBatis 手动实现数据库RP集
2020-07-12 本文已影响0人
Tian_Peng
1.概述
本示例主要介绍了Spring Boot程序方式手动实现数据库集群访问，读库轮询方式实现负载均衡。
2.MySql主从配置
关于配置请参考《MySQL主从复制配置》
3.Spring Boot实现方式
读写分离要做的事情就是对于一条SQL该选择哪个数据库去执行，至于谁来做选择数据库这件事儿，一般来讲，主要有两种实现方式，分别为：
- 使用中间件，比如mycat(推荐)，sharding-jdbc，Atlas，cobar，TDDL，heisenberg，Oceanus，vitess，OneProxy等
- 使用程序自己实现，利用Spring Boot提供的路由数据源以及AOP，实现起来简单快捷（本文要介绍的方法）
4.程序代码实现
本次项目就使用两个数据库来模拟已经做过Mysql集群，两个数据库分别为boot_demo和boot_demo2
4.1添加依赖信息
<dependencies>
<!-- web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<!-- pageHelper 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.12</version>
</dependency>
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
<scope>runtime</scope>
</dependency>
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.22</version>
</dependency>
<!-- aop -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- 常用工具包 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.8.1</version>
</dependency>
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.2.2</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>18.0</version>
</dependency>
<!-- test -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
4.2自定义数据源路由：RoutingDataSource.java
基于特定的key路由到特定的数据源。
它内部维护了一组目标数据源，并且做了路由key与目标数据源之间的映射，提供基于key查找数据源的方法。
package com.tp.mysql.cluster.datasource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* FileName: RoutingDataSource
* Author: TP
* Description:数据源路由
* <p>
* 基于特定的key路由到特定的数据源。
* 它内部维护了一组目标数据源，并且做了路由key与目标数据源之间的映射，提供基于key查找数据源的方法。
*/
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DbContext.get();
}
}
类关系图如下：
4.3自定义数据源上下文：DbContext.java
package com.tp.mysql.cluster.datasource;
import com.tp.mysql.cluster.enums.DbTypeEnum;
import lombok.extern.slf4j.Slf4j;
import java.util.concurrent.atomic.AtomicInteger;
/**
* FileName: DbContext
* Author: TP
* Description:数据源上下文类
*/
@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("切换到master库");
}
public static void slave() {
// 读库负载均衡(轮询方式)
int index = counter.getAndIncrement() % 2;
log.info("slave库访问线程数==>{}", counter.get());
if (index == 0) {
set(DbTypeEnum.SLAVE1);
log.info("切换到slave1库");
} else {
set(DbTypeEnum.SLAVE2);
log.info("切换到slave2库");
}
}
}
4.4自定义数据库枚举类：DbTypeEnum.java
这里我们配置三个库，分别是一个写库Master，2个读库slave1、slave2
package com.tp.mysql.cluster.enums;
/**
* FileName: DBTypeEnum
* Author: TP
* Description:
*/
public enum DbTypeEnum {
MASTER,
SLAVE1,
SLAVE2
}
4.5自定义数据库配置类：DataSourceConfig.java
package com.tp.mysql.cluster.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.tp.mysql.cluster.datasource.RoutingDataSource;
import com.tp.mysql.cluster.enums.DbTypeEnum;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* FileName: DataSourceConfig
* Author: TP
* Description:数据源配置
*/
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave1")
public DataSource slave1DataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave2")
public DataSource slave2DataSource() {
return DruidDataSourceBuilder.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;
}
}
4.6自定义Mybatis配置类：MyBatisConfig.java
package com.tp.mysql.cluster.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;
/**
* FileName: MyBatisConfig
* Author: TP
* Description:
*/
@Configuration
@EnableTransactionManagement
@MapperScan("com.tp.mysql.cluster.mapper")
public class MyBatisConfig {
@Resource(name = "myRoutingDataSource")
private DataSource myRoutingDataSource;
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
// 设置mapper映射文件位置
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().
getResources("classpath*:mybatis/mapper/**/*.xml"));
// 设置mybatis配置文件位置
sqlSessionFactoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().
getResource("classpath:mybatis/mybatis-config.xml"));
return sqlSessionFactoryBean.getObject();
}
@Bean
public PlatformTransactionManager platformTransactionManager() {
return new DataSourceTransactionManager(myRoutingDataSource);
}
}
4.7编写动态数据源切换AOP切面：DataSourceAop.java
package com.tp.mysql.cluster.aop;
import com.tp.mysql.cluster.datasource.DbContext;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
/**
* FileName: DataSourceAop
* Author: TP
* Description:动态数据源切换切面定义
*/
@Aspect
@Component
public class DataSourceAop {
@Pointcut("@annotation(com.tp.mysql.cluster.annotation.Master) " +
"|| execution(* com.tp.mysql.cluster.service..*.insert*(..)) " +
"|| execution(* com.tp.mysql.cluster.service..*.add*(..)) " +
"|| execution(* com.tp.mysql.cluster.service..*.save*(..)) " +
"|| execution(* com.tp.mysql.cluster.service..*.update*(..)) " +
"|| execution(* com.tp.mysql.cluster.service..*.edit*(..)) " +
"|| execution(* com.tp.mysql.cluster.service..*.delete*(..)) " +
"|| execution(* com.tp.mysql.cluster.service..*.remove*(..))")
public void writePointcut() {
}
@Pointcut("!@annotation(com.tp.mysql.cluster.annotation.Master) " +
"&& (execution(* com.tp.mysql.cluster.service..*.select*(..)) " +
"|| execution(* com.tp.mysql.cluster.service..*.get*(..)) " +
"|| execution(* com.tp.mysql.cluster.service..*.find*(..)))")
public void readPointcut() {
}
@Pointcut("@annotation(com.tp.mysql.cluster.annotation.Slave)")
public void readPointcut2() {
}
@Before("writePointcut()")
public void write() {
DbContext.master();
}
@Before("readPointcut()")
public void read() {
DbContext.slave();
}
@Before("readPointcut2()")
public void read2() {
DbContext.slave();
}
}
4.8增加两个自定义注解
package com.tp.mysql.cluster.annotation;
/**
* FileName: Master
* Author: TP
* Description: 主库(可读写)
*/
public @interface Master {
}
package com.tp.mysql.cluster.annotation;
/**
* FileName: Slave
* Author: TP
* Description: 从库(可读)
*/
public @interface Slave {
}
4.9定义用户的xml文件、mapper、service类
- UserMapper.xml文件（因为本人数据库中从库用户有1条数据，主库有1000w条用户测试数据，因此getAllUsers我只取了前10条 ）
<?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.tp.mysql.cluster.mapper.UserMapper">
<resultMap id="resultMap" type="com.tp.mysql.cluster.entity.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="sex" property="sex"/>
<result column="status" property="status"/>
<result column="mobile" property="mobile"/>
<result column="department_name" property="departmentName"/>
<result column="company_account" property="companyAccount"/>
<result column="email" property="email"/>
<result column="remark" property="remark"/>
<result column="create_time" property="createTime"/>
<result column="create_username" property="createUsername"/>
<result column="update_time" property="updateTime"/>
<result column="update_username" property="updateUsername"/>
</resultMap>
<sql id="Base_Column_List">
id,
username,
password,
sex,
status,
mobile,
department_name,
company_account,
email,
remark,
create_time,
create_username,
update_time,
update_username
</sql>
<select id="getAllUsers" resultType="com.tp.mysql.cluster.entity.User">
select * from `user` limit 10;
</select>
<insert id="saveUser" parameterType="com.tp.mysql.cluster.entity.User" useGeneratedKeys="true" keyProperty="id"
keyColumn="id">
insert into `user`
(
username,
password,
sex,
status,
mobile,
department_name,
company_account,
email,
remark,
create_time,
create_username,
update_time,
update_username
)
value
(
#{username},
#{password},
#{sex},
#{status},
#{mobile},
#{departmentName},
#{companyAccount},
#{email},
#{remark},
#{createTime},
#{createUsername},
#{updateTime},
#{updateUsername}
)
</insert>
</mapper>
- mapper接口：UserMapper.java
package com.tp.mysql.cluster.mapper;
import com.tp.mysql.cluster.entity.User;
import java.util.List;
/**
* FileName: UserMapper1
* Author: TP
* Description:用户Mapper接口
*/
public interface UserMapper {
List<User> getAllUsers();
int saveUser(User user);
}
- IUserService.java
package com.tp.mysql.cluster.service;
import com.tp.mysql.cluster.entity.User;
import java.util.List;
/**
* FileName: IUserService
* Author: TP
* Description:用户接口服务
*/
public interface IUserService {
List<User> getAllUsers();
int saveUser(User user);
List<User> showUsers();
}
- UserServiceImpl.java
package com.tp.mysql.cluster.service.impl;
import com.tp.mysql.cluster.annotation.Slave;
import com.tp.mysql.cluster.entity.User;
import com.tp.mysql.cluster.mapper.UserMapper;
import com.tp.mysql.cluster.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* FileName: UserServiceImpl
* Author: TP
* Date: 2020-07-06 15:59
* Description:
*/
@Service
package com.tp.mysql.cluster.service.impl;
import com.tp.mysql.cluster.annotation.Slave;
import com.tp.mysql.cluster.entity.User;
import com.tp.mysql.cluster.mapper.UserMapper;
import com.tp.mysql.cluster.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* FileName: UserServiceImpl
* Author: TP
* Description:用户接口服务实现类
*/
@Service
public class UserServiceImpl implements IUserService {
@Autowired
private UserMapper userMapper;
@Override
// @Master //强制切换为主库
public List<User> getAllUsers() {
return userMapper.getAllUsers();
}
@Override
public int saveUser(User user) {
if(null != user){
return userMapper.saveUser(user);
}
return 0;
}
@Override
@Slave
public List<User> showUsers() {
return userMapper.getAllUsers();
}
}
4.10 配置文件信息：application.yml
spring:
datasource:
master:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/boot_demo?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: tp123456
slave1:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/boot_demo2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: tp123456
slave2:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/boot_demo2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: tp123456
5.测试
编写测试类如下：
package com.tp.mysql.cluster;
import com.tp.mysql.cluster.entity.User;
import com.tp.mysql.cluster.service.IUserService;
import org.apache.commons.collections.CollectionUtils;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
class MyBootMysqlClusterApplicationTests {
@Autowired
private IUserService userService;
@Test
void masterDbTest() {
User user = new User();
user.setUsername("TP");
user.setPassword("123456");
user.setSex(1);
userService.saveUser(user);
}
@Test
void slaveDbTest() {
List<User> users = userService.getAllUsers();
System.out.println("用户信息：" + users);
System.out.println(users.size());
}
@Test
void slaveDbTest2() {
List<User> users1 = userService.showUsers();
List<User> users2 = userService.showUsers(); //测试从库轮询
System.out.println("用户信息：" + users1);
if (CollectionUtils.isNotEmpty(users1) && CollectionUtils.isNotEmpty(users2)) {
System.out.println(users1.size() == users2.size());
}
}
}
执行masterDbTest()，控制台输出：
2020-07-12 18:07:07.456 INFO 8946 --- [ main] c.tp.mysql.cluster.datasource.DbContext : 切换到master库
2020-07-12 18:07:07.597 INFO 8946 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
执行slaveDbTest()，控制台输出：
2020-07-12 18:09:50.695 INFO 8950 --- [ main] c.tp.mysql.cluster.datasource.DbContext : slave库访问线程数==>0
2020-07-12 18:09:50.698 INFO 8950 --- [ main] c.tp.mysql.cluster.datasource.DbContext : 切换到slave2库
2020-07-12 18:09:50.816 INFO 8950 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
用户信息：[User(super=BaseEntity(id=1, createTime=null, createUsername=null, updateTime=null, updateUsername=null), username=tp, password=123456, sex=1, status=1, mobile=15110222592, departmentName=总裁办, companyAccount=T_001, email=null, remark=null)]
1
执行slaveDbTest2()，控制台输出：
2020-07-12 18:11:29.456 INFO 8952 --- [ main] c.tp.mysql.cluster.datasource.DbContext : slave库访问线程数==>0
2020-07-12 18:11:29.457 INFO 8952 --- [ main] c.tp.mysql.cluster.datasource.DbContext : 切换到slave2库
2020-07-12 18:11:29.561 INFO 8952 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2020-07-12 18:11:29.947 INFO 8952 --- [ main] c.tp.mysql.cluster.datasource.DbContext : slave库访问线程数==>1
2020-07-12 18:11:29.947 INFO 8952 --- [ main] c.tp.mysql.cluster.datasource.DbContext : 切换到slave1库
2020-07-12 18:11:29.953 INFO 8952 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-2} inited
用户信息：[User(super=BaseEntity(id=1, createTime=null, createUsername=null, updateTime=null, updateUsername=null), username=tp, password=123456, sex=1, status=1, mobile=15110222592, departmentName=总裁办, companyAccount=T_001, email=null, remark=null)]
true
