springboot+druid+mybatis plus读写分
2019-07-08 本文已影响11人
依然慢节奏
读写分离可以通过多个数据源来实现，读数据从从库中读，写数据写入到主库中；思路：
1、yml中配置多个数据源；
2、通过AOP匹配key切换到不同的数据源；
一、AbstractRoutingDataSource
AbstractRoutingDataSource基于特定的key值到特定的数据源，内部维护了一组目标数据源，做了路由key和目标数据源之间的映射，我们可以基于指定的key来切换数据源；
image.png
public abstract class AbstractRoutingDataSource{
/**determineCurrentLookupKey的返回值指定了key*/
protected abstract Object determineCurrentLookupKey(){
return key;
}
}
二、pom.xml
maven的pom.xml文件，只写了必要的依赖，其它依赖可以自己添加；
<?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 http://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.0.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<mybatisplus-spring-boot-starter.version>1.0.5</mybatisplus-spring-boot-starter.version>
<mybatisplus.version>2.1.4</mybatisplus.version>
<druid.version>1.1.10</druid.version>
<skip>true</skip>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-cache</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
<exclusions>
<exclusion>
<artifactId>mybatis</artifactId>
<groupId>org.mybatis</groupId>
</exclusion>
<exclusion>
<artifactId>mybatis-spring</artifactId>
<groupId>org.mybatis</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatisplus-spring-boot-starter</artifactId>
<version>${mybatisplus-spring-boot-starter.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/**</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/**</include>
</includes>
</resource>
</resources>
</build>
</project>
三、application.yml数据源配置
#生产环境配置
server:
#端口
port: 8070
spring:
#redis配置
redis:
database: 0
host: 127.0.0.1
port: 6379
password: # 密码（默认为空）
timeout: 6000ms # 连接超时时长（毫秒）
jedis:
pool:
max-active: 200
max-idle: 1000 # 连接池中的最大空闲连接
max-wait: -1s # 连接池最大阻塞等待时间（使用负值表示没有限制）
min-idle: 5 # 连接池中的最小空闲连接
# 数据源配置
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
stat-view-servlet:
loginUsername: admin
loginPassword: 123456
validationQuery: SELECT 1
initialSize: 10
minIdle: 10
maxActive: 200
minEvictableIdleTimeMillis: 180000
testOnBorrow: false
testWhileIdle: true
removeAbandoned: true
removeAbandonedTimeout: 1800
logAbandoned: true
poolPreparedStatements: true
maxOpenPreparedStatements: 100
dynamic:
datasource:
master:
username: root
password: xxxxx
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://xxx.xx.x.xx:3306/oneclick?useUnicode=true&characterEncoding=utf8&useSSL=false&tinyInt1isBit=true
slave1:
username: root
password: xxxxx
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://xxx.xx.x.xx:3306/oneclick?useUnicode=true&characterEncoding=utf8&useSSL=false&tinyInt1isBit=true
#日志
logging:
config: classpath:log/logback.xml
path: /log/oneclick/yjs-client-gateway.log/prod/
上面配置了两个数据数据源，主库master，从库slave1；主库负责写数据，从库负责读数据；
四、mybatis-plus和druid配置
4.1、mybatis-plus配置和数据源配置
package com.unnet.yjs.config.db;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.MybatisConfiguration;
import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.plugins.PerformanceInterceptor;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import com.baomidou.mybatisplus.toolkit.GlobalConfigUtils;
import com.unnet.yjs.config.SysMetaObjectHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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 org.springframework.context.annotation.Profile;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* Email: love1208tt@foxmail.com
* Copyright (c) 2019. missbe
* @author lyg 19-7-8 下午2:04
*
* MybatisPlus的数据源和插件配置
**/
@Configuration
public class MybatisPlusConfig {
private static final Logger LOGGER = LoggerFactory.getLogger(MybatisPlusConfig.class);
@Resource
private SysMetaObjectHandler sysMetaObjectHandler;
@Resource
private MybatisProperties mybatisProperties;
/***
* mybatis-plus的性能优化-设置最大执行时长和格式化sql输出
*/
@Bean
@Profile({"dev","test"})
public PerformanceInterceptor performanceInterceptor() {
LOGGER.info("dev和test环境下加载PerformanceInterceptor性能分析插件.");
PerformanceInterceptor performanceInterceptor=new PerformanceInterceptor();
/*<!-- SQL 执行性能分析，开发环境使用，线上不推荐。 maxTime 指的是 sql 最大执行时长 -->*/
performanceInterceptor.setMaxTime(1000);
/*<!--SQL是否格式化 默认false-->*/
performanceInterceptor.setFormat(true);
return performanceInterceptor;
}
/**
* mybatis-plus分页插件-设置分页类型为mysql和本地分页
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
LOGGER.info("加载PaginationInterceptor本地分页插件.");
PaginationInterceptor page = new PaginationInterceptor();
page.setLocalPage(true);
page.setDialectType("mysql");
return page;
}
/**
* 数据库主库
*/
@Bean
@ConfigurationProperties("spring.datasource.dynamic.datasource.master")
public DataSource masterDataSource(){
LOGGER.info("加载主数据源masterDataSource.");
return DruidDataSourceBuilder.create().build();
}
/**
* 数据库从库
*/
@Bean
@ConfigurationProperties("spring.datasource.dynamic.datasource.slave1")
public DataSource slave1DataSource(){
LOGGER.info("加载从数据源slave1DataSource.");
return DruidDataSourceBuilder.create().build();
}
/**
* 动态数据源
*/
@Bean
public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slave1DataSource") DataSource slave1DataSource) {
LOGGER.info("加载[masterDataSource-slave1DataSource]设置为动态数据源DynamicDataSource.");
Map<Object, Object> targetDataSources = new HashMap<>(2);
targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
dynamicDataSource.setTargetDataSources(targetDataSources);
return dynamicDataSource;
}
/**
* 配置mybatis-plus的SqlSessionFactory
*/
@Bean
public MybatisSqlSessionFactoryBean sqlSessionFactory(@Qualifier("masterDataSource") DataSource master,
@Qualifier("slave1DataSource") DataSource slave) throws Exception {
LOGGER.info("自定义配置mybatis-plus的SqlSessionFactory.");
MybatisSqlSessionFactoryBean mybatisPlus = new MybatisSqlSessionFactoryBean();
mybatisPlus.setDataSource(myRoutingDataSource(master, slave));
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
///自定义配置
mybatisPlus.setConfiguration(configuration);
GlobalConfiguration globalConfiguration = GlobalConfigUtils.defaults();
/////自定义填充策略接口实现
globalConfiguration.setMetaObjectHandler(sysMetaObjectHandler);
////主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
globalConfiguration.setIdType(0);
/////字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
globalConfiguration.setFieldStrategy(2);
////驼峰下划线转换
globalConfiguration.setDbColumnUnderline(true);
///自定义全局配置
mybatisPlus.setGlobalConfig(globalConfiguration);
if (StringUtils.hasLength(this.mybatisProperties.getTypeAliasesPackage())) {
mybatisPlus.setTypeAliasesPackage(this.mybatisProperties.getTypeAliasesPackage());
}
if (StringUtils.hasLength(this.mybatisProperties.getTypeHandlersPackage())) {
mybatisPlus.setTypeHandlersPackage(this.mybatisProperties.getTypeHandlersPackage());
}
if (!ObjectUtils.isEmpty(this.mybatisProperties.resolveMapperLocations())) {
mybatisPlus.setMapperLocations(this.mybatisProperties.resolveMapperLocations());
}
//// 设置mapper.xml文件的路径
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
org.springframework.core.io.Resource[] resource = resolver.getResources("classpath:mapper/*.xml");
mybatisPlus.setMapperLocations(resource);
//添加插件到SqlSessionFactory才能生效
mybatisPlus.setPlugins(new Interceptor[]{paginationInterceptor(),performanceInterceptor()});
return mybatisPlus;
}
}
4.2、数据源枚举
package com.unnet.yjs.config.db;
/**
* Email: love1208tt@foxmail.com
* Copyright (c) 2019. missbe
* @author lyg 19-7-8 下午2:05
*
* 数据源类型
**/
public enum DBTypeEnum {
/**主库*/
MASTER,
/**从库1*/
SLAVE1,
/**从库2*/
SLAVE2;
}
4.3、数据源线程安全设置key
package com.unnet.yjs.config.db;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.concurrent.atomic.AtomicInteger;
/**
* Email: love1208tt@foxmail.com
* Copyright (c) 2019. missbe
* @author lyg 19-7-8 下午1:00
*
* 线程安全的数据源切换
**/
public class DbContextHolder {
private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceApp.class);
private static final ThreadLocal<DBTypeEnum> CONTEXT_HOLDER = new ThreadLocal<>();
private static final AtomicInteger COUNTER = new AtomicInteger(-1);
public static void set(DBTypeEnum dbType) {
CONTEXT_HOLDER.set(dbType);
}
public static DBTypeEnum get() {
return CONTEXT_HOLDER.get();
}
static void master() {
set(DBTypeEnum.MASTER);
System.out.println("切换到master");
}
static void slave() {
// 轮询
int index = COUNTER.getAndIncrement() % 2;
if (COUNTER.get() > 9999) {
COUNTER.set(-1);
}
set(DBTypeEnum.SLAVE1);
LOGGER.info(index +"->切换到slave1");
}
/**
* 清除上下文数据
*/
static void clearDbType() {
CONTEXT_HOLDER.remove();
}
}
4.4、AOP切面配置key切换
package com.unnet.yjs.config.db;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
/**
* Email: love1208tt@foxmail.com
* Copyright (c) 2019. missbe
* @author lyg 19-7-8 下午2:04
*
* 配置数据源切换的AOP切面
* com.baomidou.mybatisplus.service的select开头的方法走从库，insert、update、delete开头的方法走主库
* com.unnet.yjs.service的select开头的方法走从库，insert、update、delete开头的方法走主库
**/
@Aspect
@Component
public class DataSourceAop {
private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceAop.class);
@Pointcut("!@annotation(com.unnet.yjs.annotation.Master) && " +
"(execution(* com.unnet.yjs.service..*.select*(..)) || " +
"execution(* com.unnet.yjs.service..*.get*(..)) || " +
"execution(* com.baomidou.mybatisplus.service..*.select*(..)) || " +
"execution(* com.unnet.yjs.service..*.find*(..)))")
public void readPointCut(){ }
@Pointcut("@annotation(com.unnet.yjs.annotation.Master) || execution(* com.unnet.yjs.service..*.insert*(..)) " +
"|| execution(* com.unnet.yjs.service..*.update*(..)) || execution(* com.baomidou.mybatisplus.service..*.update*(..)) " +
"|| execution(* com.unnet.yjs.service..*.delete*(..)) || execution(* com.baomidou.mybatisplus.service..*.delete*(..)) " +
"|| execution(* com.baomidou.mybatisplus.service..*.insert*(..))")
public void writePointcut() { }
@Before("readPointCut()")
public void read(){
LOGGER.info("------switch read-------");
DbContextHolder.slave();
}
@Before("writePointcut()")
public void write(){
LOGGER.info("------switch write-------");
DbContextHolder.master();
}
}
4.5、数据key获取策略设置
package com.unnet.yjs.config.db;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.annotation.Nullable;
/**
* Email: love1208tt@foxmail.com
* Copyright (c) 2019. missbe
* @author lyg 19-7-8 下午2:11
*
*
**/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Nullable
@Override
protected Object determineCurrentLookupKey() {
return DbContextHolder.get();
}
}
4.6、Master注解
package com.unnet.yjs.annotation;
/**
* Email: love1208tt@foxmail.com
* Copyright (c) 2019. missbe
* @author lyg 7/7/19 9:06 PM
*
* 强制读主库切换
**/
public @interface Master {
}
主要用于需要自己强制切换到主库的时候；
五、测试
-
swagger测试这个手机号是否存在；
image.png
-
日志如下：
切换到master
Time：11 ms - ID：com.unnet.yjs.dao.AgentDao.selectList
Execute SQL：
SELECT
id,
create_by AS createId,
create_date AS createDate,
update_by AS updateId,
update_date AS updateDate,
del_flag AS delFlag,
remarks
FROM
t_agent
WHERE
(
phone_number = 'xxxxxxxxxxx'
)
2019-07-08 14:51:27.557 INFO : ------switch read-------
2019-07-08 14:51:27.557 INFO : -1->切换到slave1
2019-07-08 14:51:27.561 INFO : {dataSource-3} inited
Time：39 ms - ID：com.unnet.yjs.dao.TerminatorDao.selectList
Execute SQL：
SELECT
id,
create_by AS createId,
create_date AS createDate,
update_by AS updateId,
update_date AS updateDate,
del_flag AS delFlag,
remarks
FROM
t_terminator
WHERE
(
terminator_machine_code = 'xx-xx-xx-xxx-xx'
)
2019-07-08 14:51:28.005 INFO : 15310663435:14102d47-8d22-42c0-ac79-20e047037a0c-j5s7yk->该帐号对应机器码为首次登录,返回营业厅数据信息.
2019-07-08 14:51:28.044 INFO : ------switch read-------
2019-07-08 14:51:28.044 INFO : 0->切换到slave1
Time：35 ms - ID：com.unnet.yjs.dao.HallDao.selectList
Execute SQL：
SELECT
id,
create_by AS createId,
create_date AS createDate,
update_by AS updateId,
update_date AS updateDate,
del_flag AS delFlag,
remarks
FROM
t_hall
WHERE
(
agent_id = 'xx'
)
