Spring Boot MyBatis 数据库集群访问实现
本示例主要介绍了Spring Boot程序方式实现数据库集群访问,读库轮询方式实现负载均衡,实现读写分离。阅读本示例前,建议你有AOP编程基础、mybatis基本功能会使用、数据库集群基本概念,这样你可以更快的理解和实现它。
1.MySql主从配置
关于配置请参考《MySQL主从架构方案》
2.Spring Boot实现方式
读写分离要做的事情就是对于一条SQL该选择哪个数据库去执行,至于谁来做选择数据库这件事儿,一般来讲,主要有两种实现方式,分别为:
- 1.使用中间件,比如Atlas,cobar,TDDL,mycat,heisenberg,Oceanus,vitess,OneProxy等。
- 2.使用程序自己实现,利用Spring Boot提供的路由数据源以及AOP,实现起来简单快捷(本文要介绍的方法)
3.程序代码实现
3.1.配置依赖
首先我们配置下build.gradle,项目版本管理工具是gradle,添加示例必要的依赖
plugins {
id 'java'
id 'org.springframework.boot' version '2.2.0.RELEASE'
id 'io.spring.dependency-management' version '1.0.8.RELEASE'
}
group = 'com.example'
version = '0.0.1-SNAPSHOT'
repositories {
mavenCentral(url: 'https://maven.aliyun.com/nexus/content/groups/public/')
}
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter'
implementation group: 'org.springframework.boot', name: 'spring-boot-starter-aop'
implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.2.0'
implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.31'
implementation group: 'com.alibaba', name: 'druid', version: '1.2.15'
implementation group: 'org.apache.commons', name: 'commons-lang3', version: '3.5'
implementation 'com.alibaba:fastjson:1.1.54.android'
testImplementation('org.springframework.boot:spring-boot-starter-test') {
exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
}
}
test {
useJUnitPlatform()
}
3.2.数据源路由类功能RoutingDataSource.java
基于特定的key路由到特定的数据源。它内部维护了一组目标数据源,并且做了路由key与目标数据源之间的映射,提供基于key查找数据源的方法。
a.类关系图
b.代码很简单,调用下DBContext的get方法就可以了
package com.hermes.config.dynamicDatasource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 数据库路由接口-根据枚举传入的路由key映射到具体的某个数据库-具体实现在DruidDataSource源码
*/
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DBContext.get();
}
}
数据库连接使用的Alibaba的Druid连接池。
3.3.数据源上下文类DBContext.java
package com.hermes.config.dynamicDatasource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.concurrent.atomic.AtomicInteger;
/**
* 数据源上下文类-多数据源切换
*/
public class DBContext {
private static final Logger LOGGER = LoggerFactory.getLogger(DBContext.class);
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);
LOGGER.info("切换到" + DBTypeEnum.MASTER + "库");
}
public static void slave() {
// 读库负载均衡(轮询方式)
int index = counter.getAndIncrement() % 2;
LOGGER.info("slave库访问线程数==>{}", counter.get());
if (index == 0) {
set(DBTypeEnum.SLAVE1);
LOGGER.info("切换到" + DBTypeEnum.SLAVE1 + "库");
} else {
set(DBTypeEnum.SLAVE2);
LOGGER.info("切换到" + DBTypeEnum.SLAVE2 + "库");
}
}
public static void clear(){
dbContext.remove();
}
}
3.4.数据库枚举类DBTypeEnum.java
public enum DBTypeEnum {
MASTER, SLAVE1, SLAVE2
}
这里我们配置三个库,分别是一个写库Master,2个读库slave1,slave2
3.5.数据库配置类DataSourceConfig.java
package com.hermes.config.dynamicDatasource;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
/**
* 数据库配置类-RoutingDataSource维护了路由key和数据库的映射关系
*/
@Configuration
public class DataSourceConfig {
@Bean(name = "masterDataSource")
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource() {
// 使用@ConfigurationProperties("spring.datasource.master")+DataSourceBuilder.create().build()构建DataSource为Null 没排查到问题所在 所以最后使用了ali的Druid
// DataSource build = DataSourceBuilder.create().build();
return new DruidDataSource();
}
@Bean(name = "slave1DataSource")
@ConfigurationProperties("spring.datasource.slave1")
public DataSource slave1DataSource() {
return new DruidDataSource();
}
@Bean(name = "slave2DataSource")
@ConfigurationProperties("spring.datasource.slave2")
public DataSource slave2DataSource() {
return new DruidDataSource();
}
// 项目启动,加载application.properties配置文件并构建数据库相关数据
@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;
}
}
3.6.mybatis配置类DataSourceConfig.java
package com.hermes.config.dynamicDatasource;
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;
/**
* 项目启动,mybatis配置类DataSourceConfig
*/
@EnableTransactionManagement
@Configuration
@MapperScan("com.hermes.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);
}
}
3.7.切面类DataSourceAop.java
package com.hermes.config.dynamicDatasource;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
/**
* 切面类-自动的根据方法命名切换主从数据库
*/
@Aspect
@Component
public class DataSourceAop {
// 实现方式1
// @Pointcut("@annotation(com.hermes.config.dynamicDatasource.MasterDb) " +
// "|| execution(* com.hermes.service..*.insert*(..)) " +
// "|| execution(* com.hermes.service..*.add*(..)) " +
// "|| execution(* com.hermes.service..*.update*(..)) " +
// "|| execution(* com.hermes.service..*.edit*(..)) " +
// "|| execution(* com.hermes.service..*.del*(..)) " +
// "|| execution(* com.hermes.service..*.save*(..)) " +
// "|| execution(* com.hermes.service..*.remove*(..))")
// public void writePointcut() {
//
// }
//
// @Pointcut("!@annotation(com.hermes.config.dynamicDatasource.MasterDb) " +
// "&& (execution(* com.hermes.service..*.select*(..)) " +
// "|| execution(* com.hermes.service..*.get*(..)))" +
// "|| execution(* com.hermes.service..*.find*(..)))" +
// "|| execution(* com.hermes.service..*.query*(..)))")
// public void readPointcut() {
//
// }
//
// @Before("writePointcut()")
// public void write() {
// DBContext.master();
// }
//
// @Before("readPointcut()")
// public void read() {
// DBContext.slave();
// }
// 实现方式2
// 切入点为service包下所有
@Pointcut("execution(* com.hermes.service..*.*(..))")
private void aspect() {
}
@Around("aspect()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
// 获取方法名字
String method = joinPoint.getSignature().getName();
if (method.startsWith("find") || method.startsWith("select") || method.startsWith("query") || method
.startsWith("search")) {
DBContext.slave();
} else {
// 默认操作主库数据
DBContext.master();
}
try {
// 连接具体某个数据库并执行mapper的对应的SQL操作语句-执行joinPoint.proceed()会进入到RoutingDataSource的determineCurrentLookupKey()方法,具体的Druid原理得看源码了
// 返回值proceed是方法的返回值
Object proceed = joinPoint.proceed();
return proceed;
} finally {
System.out.println("清除 datasource router...");
DBContext.clear();
}
}
}
3.8.注解类Master.java(这个注解是AOP实现方式1的补充,通过注解的方式,手动把它指到了可写库(master库))
package com.easy.mybatis.multidatasource.annotation;
/**
* 主库,可读写
*/
public @interface Master {
}
3.9.用户的xml,mapper,service类
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.hermes.mapper.DynamicDbMapper">
<update id="updateDataById">
update file_handle_t set name ='中证资本222' where id=#{id}
</update>
<select id="findCurrentDateDataByStatusGroupByBusinessTypes" resultType="integer">
select COUNT(1) as counts from file_handle_t where status=#{status} and DATE_FORMAT(NOW(),'%Y:%m:%d')=error_date
</select>
</mapper>
UserMapper.java
package com.hermes.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@Mapper
public interface DynamicDbMapper {
int findCurrentDateDataByStatusGroupByBusinessTypes(@Param("status") String status);
void updateDataById(@Param("id") Integer id);
}
UserServiceImpl.java
package com.hermes.service.impl;
import com.hermes.config.dynamicDatasource.MasterDb;
import com.hermes.mapper.DynamicDbMapper;
import com.hermes.service.IDynamicDbService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @Descrition: 根据方法名-find-updatData,测试是否能正确实现读写分离
* @ClassName: DynamicDbService
* @Author: 晴日朗
* @Date 2022年11月28日18:03
* @Version: V1.0
*/
@Service
public class DynamicDbService implements IDynamicDbService {
@Autowired
private DynamicDbMapper dynamicDbMapper;
public String find() {
int counts = dynamicDbMapper.findCurrentDateDataByStatusGroupByBusinessTypes("特法数据第二遍");
return "counts=" + counts;
}
@Override
public String updatData(Integer id) {
dynamicDbMapper.updateDataById(id);
return "success";
}
}
3.10.最后我贴上properties配置文件:application.properties
server.port=8080
// 主库-写数据库配置
spring.datasource.master.url=jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC
spring.datasource.master.username=root-master
spring.datasource.master.password=1234
// alibaba的druid连接池
spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
// 从库1-读数据库配置
spring.datasource.slave1.url=jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC
spring.datasource.slave1.username=root-slave1
spring.datasource.slave1.password=1234
spring.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
// 从库2-读数据库配置
spring.datasource.slave2.url=jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC
spring.datasource.slave1.username=root-slave2
spring.datasource.slave1.password=1234
spring.datasource.slave2.type=com.alibaba.druid.pool.DruidDataSource
该配置文件配置了三个数据源(这里我为了方便,把三个数据源指到了同个库,实际生产环境会有不同的库和读写用户)
4.测试,查看执行结果,分析数据库调用情况
4.1.简单测试
postMan访问接口:
GET localhost:8080/dynamicDb/findData
GET localhost:8080/dynamicDb/updateData?id=1
package com.hermes.controller;
import com.hermes.service.IDynamicDbService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping(value = "/dynamicDb")
public class DynamicDbController {
@Autowired
private IDynamicDbService iDynamicDbService;
@RequestMapping(value = "/findData", method = RequestMethod.GET)
public String find() {
return iDynamicDbService.find();
}
@RequestMapping(value = "/updateData", method = RequestMethod.GET)
public String updateData(@RequestParam(name = "id") Integer id) {
return iDynamicDbService.updatData(id);
}
}
4.2.查看控制台执行结果
[2022-12-05 10:22:49.374] [INFO ] [] [http-nio-8080-exec-1] com.hermes.config.dynamicDatasource.DBContext:36 >> slave库访问线程数==>0
[2022-12-05 10:22:49.375] [INFO ] [] [http-nio-8080-exec-1] com.hermes.config.dynamicDatasource.DBContext:42 >> 切换到SLAVE2库
[2022-12-05 10:22:49.477] [INFO ] [] [http-nio-8080-exec-1] com.alibaba.druid.pool.DruidDataSource:985 >> {dataSource-1} inited
清除 datasource router...
[2022-12-05 10:24:40.017] [INFO ] [] [http-nio-8080-exec-3] com.hermes.config.dynamicDatasource.DBContext:36 >> slave库访问线程数==>1
[2022-12-05 10:24:40.017] [INFO ] [] [http-nio-8080-exec-3] com.hermes.config.dynamicDatasource.DBContext:39 >> 切换到SLAVE1库
[2022-12-05 10:24:40.022] [INFO ] [] [http-nio-8080-exec-3] com.alibaba.druid.pool.DruidDataSource:985 >> {dataSource-2} inited
清除 datasource router...
[2022-12-05 10:26:06.779] [INFO ] [] [http-nio-8080-exec-6] com.hermes.config.dynamicDatasource.DBContext:30 >> 切换到MASTER库
[2022-12-05 10:26:06.783] [INFO ] [] [http-nio-8080-exec-6] com.alibaba.druid.pool.DruidDataSource:985 >> {dataSource-3} inited
清除 datasource router...
5.小结:
到此我们已经实现了数据源集群的动态切换,简单的实现了从库的负载均衡和读写分离。当然也可以使用中间件实现读写分离。
-end-