mysql 读写分离 事务6_读写分离注解(示例代码)

以前写过读写分离，今天完善成文档。

一：概述

1.结构文档

2.思路

组装好各个数据源，然后通过注解选择使用读或者写的数据源，将其使用AbstractRoutingDataSource中的方法determineCurrentLookuoKey进行选择datasource的key。

然后，通过key，就找到了要使用的数据源。

在数据源的这里，最后配置上连接池。

3.说明

本配置直接使用一个公共的连接池配置，如果需要，自己进行配置

二：程序说明

1.连接池配置

package com.jun.webpro.common.config.dataSource.properties;

import lombok.Data;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.stereotype.Component;

import java.util.List;

import java.util.Map;

/**

* 数据库连接池配置

*/

@ConfigurationProperties(prefix = "spring.druid")

@Component

@Data

public class DruidProperties {

/**

* 初始化大小

*/

private int initialSize;

/**

* 最小

*/

private int minIdle;

/**

* 最大

*/

private int maxActive;

/**

* 获取连接等待超时的时间

*/

private int maxWait;

/**

* 间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒

*/

private int timeBetweenEvictionRunsMillis;

/**

* 一个连接在池中最小生存的时间，单位是毫秒

*/

private int minEvictableIdleTimeMillis;

private String validationQuery;

private boolean testWhileIdle;

private boolean testOnBorrow;

private boolean testOnReturn;

/**

* 打开PSCache

*/

private boolean poolPreparedStatements;

/**

* 并且指定每个连接上PSCache的大小

*/

private int maxPoolPreparedStatementPerConnectionSize;

/**

* 监控统计拦截的filters，去掉后监控界面sql无法统计，‘wall‘用于防火墙

*/

private String filters;

/**

* 物理连接初始化的时候执行的sql

*/

private List connectionInitSqls;

/**

* connectProperties属性来打开mergeSql功能；慢SQL记录

*/

private Map connectionProperties;

}

2.主从库的配置项接口

因为通过这个接口，进行下面的注入

package com.jun.webpro.common.config.dataSource.properties;

public interface DataSourceProperties {

String getDriverClassName();

String getUrl();

String getUsername();

String getPassword();

}

3.从库配置

package com.jun.webpro.common.config.dataSource.properties;

import lombok.Data;

import lombok.Getter;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.stereotype.Component;

@Component

@Data

@Getter

@ConfigurationProperties("mysql.datasource.slave")

public class SlaveDataSourceProperties implements DataSourceProperties{

/**

* url

*/

private String url;

/**

* driverClassName

*/

private String driverClassName;

/**

* username

*/

private String username;

/**

* password

*/

private String password;

}

4.主库配置

package com.jun.webpro.common.config.dataSource.properties;

import lombok.Data;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.stereotype.Component;

@Component

@Data

@ConfigurationProperties("mysql.datasource.master")

public class MasterDataSourceProperties implements DataSourceProperties{

/**

* url

*/

private String url;

/**

* driverClassName

*/

private String driverClassName;

/**

* username

*/

private String username;

/**

* password

*/

private String password;

}

5.数据池配置项

package com.jun.webpro.common.config.dataSource.config;

import com.alibaba.druid.pool.DruidDataSource;

import com.jun.webpro.common.config.dataSource.properties.DataSourceProperties;

import com.jun.webpro.common.config.dataSource.properties.DruidProperties;

import org.springframework.context.annotation.Configuration;

import javax.annotation.Resource;

import java.sql.SQLException;

import java.util.Properties;

@Configuration

public class DataSourceConfig {

// 导入数据库连接池配置

@Resource

private DruidProperties druidProperties;

/**

* @param dataSourceProperties 其他地方传参

*/

DruidDataSource initDruidDataSource(DataSourceProperties dataSourceProperties) throws SQLException {

try (DruidDataSource dataSource = new DruidDataSource()) {

dataSource.setInitialSize(druidProperties.getInitialSize());

dataSource.setMinIdle(druidProperties.getMinIdle());

dataSource.setMaxActive(druidProperties.getMaxActive());

dataSource.setMaxWait(druidProperties.getMaxWait());

dataSource.setTimeBetweenEvictionRunsMillis(druidProperties.getTimeBetweenEvictionRunsMillis());

dataSource.setMinEvictableIdleTimeMillis(druidProperties.getMinEvictableIdleTimeMillis());

dataSource.setValidationQuery(druidProperties.getValidationQuery());

dataSource.setTestWhileIdle(druidProperties.isTestWhileIdle());

dataSource.setTestOnBorrow(druidProperties.isTestOnBorrow());

dataSource.setTestOnReturn(druidProperties.isTestOnReturn());

dataSource.setPoolPreparedStatements(druidProperties.isPoolPreparedStatements());

dataSource.setMaxPoolPreparedStatementPerConnectionSize(druidProperties.getMaxPoolPreparedStatementPerConnectionSize());

dataSource.setConnectionInitSqls(druidProperties.getConnectionInitSqls());

dataSource.setFilters(druidProperties.getFilters());

Properties properties = new Properties();

for (String key : druidProperties.getConnectionProperties().keySet()) {

properties.setProperty(key, druidProperties.getConnectionProperties().get(key));

}

dataSource.setConnectProperties(properties);

dataSource.setDriverClassName(dataSourceProperties.getDriverClassName());

dataSource.setUrl(dataSourceProperties.getUrl());

dataSource.setUsername(dataSourceProperties.getUsername());

dataSource.setPassword(dataSourceProperties.getPassword());

return dataSource;

}

}

}

6.主数据库配置项

package com.jun.webpro.common.config.dataSource.config;

import com.jun.webpro.common.config.dataSource.properties.MasterDataSourceProperties;

import lombok.extern.slf4j.Slf4j;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import javax.annotation.Resource;

import javax.sql.DataSource;

import java.sql.SQLException;

@Slf4j

@Configuration

public class MasterDataSourceConfig extends DataSourceConfig{

@Resource

private MasterDataSourceProperties dataSourceProperties;

/**

* 数据源

*/

@Bean(name = "masterDataSource")

@Primary

public DataSource masterDataSource() throws SQLException {

return initDruidDataSource(dataSourceProperties);

}

}

7.从数据库配置

package com.jun.webpro.common.config.dataSource.config;

import com.jun.webpro.common.config.dataSource.properties.MasterDataSourceProperties;

import com.jun.webpro.common.config.dataSource.properties.SlaveDataSourceProperties;

import lombok.extern.slf4j.Slf4j;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.annotation.Resource;

import javax.sql.DataSource;

import java.sql.SQLException;

@Slf4j

@Configuration

public class SlaveDataSourceConfig extends DataSourceConfig{

@Resource

private SlaveDataSourceProperties dataSourceProperties;

/**

* 数据源

*/

@Bean(name = "slaveDataSource")

public DataSource masterDataSource() throws SQLException {

return initDruidDataSource(dataSourceProperties);

}

}

8.mybatis配置项

这里用于mysql事务

package com.jun.webpro.common.config.dataSource.config;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

@Configuration

public class MybatisConfig {

@Bean("mybatis-config")

@ConfigurationProperties(prefix = "mybatis.configuration")

public org.apache.ibatis.session.Configuration globalConfiguration() {

return new org.apache.ibatis.session.Configuration();

}

}

9.将数据源的key设置进ThreadLocal中

这里主要是注解中，将值添加进来，然后后面使用。

package com.jun.webpro.common.config.dataSource.route;

import lombok.extern.slf4j.Slf4j;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

* Description 这里切换读/写模式

* 原理是利用ThreadLocal保存当前线程是否处于读模式(通过开始READ_ONLY注解在开始操作前设置模式为读模式，

* 操作结束后清除该数据，避免内存泄漏，同时也为了后续在该线程进行写操作时任然为读模式

*/

@Slf4j

public class DbContextHolder {

public static final String MASTER = "masterDataSource";

public static final String SLAVE = "slaveDataSource";

private static ThreadLocal contextHolder= new ThreadLocal<>();

public static void setDbType(String dbType) {

if (dbType == null) {

log.error("dbType为空");

throw new NullPointerException();

}

log.info("设置dbType为：{}",dbType);

contextHolder.set(dbType);

}

public static String getDbType() {

return contextHolder.get();

}

public static void clearDbType() {

contextHolder.remove();

}

}

10.去ThreadLocal中获取荣国注解加入的key

这个key可以决定走哪个库。

package com.jun.webpro.common.config.dataSource.route;

import com.jun.webpro.common.config.dataSource.route.DbContextHolder;

import com.jun.webpro.common.units.NumberUtils;

import lombok.extern.slf4j.Slf4j;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import java.util.Objects;

/**

* Description

*

*/

@Slf4j

public class RoutingDataSource extends AbstractRoutingDataSource {

@Value("${mysql.datasource.num}")

private int num;

@Override

protected Object determineCurrentLookupKey() {

String typeKey = DbContextHolder.getDbType();

if (Objects.equals(DbContextHolder.MASTER, typeKey)) {

log.info("使用了写库");

return typeKey;

}else {

int sum = NumberUtils.getRandom(1, num);

log.info("使用了读库{}", sum);

return DbContextHolder.SLAVE + sum;

}

}

}

11.读写配置，主要点是重写routingDataSource

package com.jun.webpro.common.config.dataSource.route;

import com.jun.webpro.common.config.dataSource.config.DataSourceConfig;

import com.jun.webpro.common.config.dataSource.route.DbContextHolder;

import com.jun.webpro.common.config.dataSource.route.RoutingDataSource;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.beans.factory.annotation.Qualifier;

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.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.annotation.Resource;

import javax.sql.DataSource;

import java.util.HashMap;

import java.util.Map;

/**

* Description

*

*/

@Configuration

@MapperScan(basePackages = "com.jun.webpro.common.domain.mapper", sqlSessionFactoryRef = "sqlSessionFactory")

public class WriteOrReadDatabaseConfig extends DataSourceConfig {

@Resource

private DataSource masterDataSource;

@Resource

private DataSource slaveDataSource;

/**

* 事务

*/

@Bean

public DataSourceTransactionManager masterTransactionManager() {

return new DataSourceTransactionManager(routingDataSource());

}

@Bean

public SqlSessionFactory sqlSessionFactory(@Qualifier("mybatis-config") org.apache.ibatis.session.Configuration configuration) throws Exception {

final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

sessionFactory.setDataSource(routingDataSource());

sessionFactory.setConfiguration(configuration);

sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));

return sessionFactory.getObject();

}

/**

* 设置数据源路由，通过该类中的determineCurrentLookupKey决定使用哪个数据源

* 工厂模式

*/

@Bean

public AbstractRoutingDataSource routingDataSource() {

RoutingDataSource proxy = new RoutingDataSource();

Map targetDataSources = new HashMap<>(2);

targetDataSources.put(DbContextHolder.MASTER, masterDataSource);

targetDataSources.put(DbContextHolder.SLAVE+"1", slaveDataSource);

proxy.setDefaultTargetDataSource(slaveDataSource);

proxy.setTargetDataSources(targetDataSources);

return proxy;

}

}

12.注解

package com.jun.webpro.common.aspect;

import java.lang.annotation.ElementType;

import java.lang.annotation.Retention;

import java.lang.annotation.RetentionPolicy;

import java.lang.annotation.Target;

/**

* Description 通过该接口注释的service使用读模式，其他使用写模式

*

* 接口注释只是一种办法，如果项目已经有代码了，通过注释可以不修改任何业务代码加持读写分离

* 也可以通过切面根据方法开头来设置读写模式，例如getXXX()使用读模式，其他使用写模式

*

*/

@Target({ElementType.METHOD,ElementType.TYPE})

@Retention(RetentionPolicy.RUNTIME)

public @interface ReadOnly {

}

13，注解实现

将要使用的库写进去

package com.jun.webpro.common.aspect.impl;

import com.jun.webpro.common.aspect.ReadOnly;

import com.jun.webpro.common.config.dataSource.route.DbContextHolder;

import org.aspectj.lang.ProceedingJoinPoint;

import org.aspectj.lang.annotation.Around;

import org.aspectj.lang.annotation.Aspect;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.core.Ordered;

import org.springframework.stereotype.Component;

/**

* Description

*

*/

@Aspect

@Component

public class ReadOnlyInterceptor implements Ordered {

private static final Logger log= LoggerFactory.getLogger(ReadOnlyInterceptor.class);

@Around("@annotation(readOnly)")

public Object setRead(ProceedingJoinPoint joinPoint, ReadOnly readOnly) throws Throwable{

try{

// 通过注解，将值注册进去

DbContextHolder.setDbType(DbContextHolder.SLAVE);

return joinPoint.proceed();

}finally {

DbContextHolder.clearDbType();

log.info("清除threadLocal");

}

}

@Override

public int getOrder() {

return 0;

}

}

三：配置项

1.

mysql:

datasource:

#读库数目

num: 1

master:

url: jdbc:mysql://47.103.25.1:3306/center?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC

username: root

password: 123456

driver-class-name: com.mysql.cj.jdbc.Driver

slave:

url: jdbc:mysql://47.103.25.1:3306/center?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC

username: root

password: 123456

driver-class-name: com.mysql.cj.jdbc.Driver

spring:

# Redis配置, 使用了连接池

redis:

database: 0

host: 106.14.25.1

port: 6379

password:

jedis:

pool:

max-active: 20

max-wait: -1

max-idle: 20

min-idle: 10

timeout: 1000

#druid数据库连接池配置

druid:

initialSize: 5

minIdle: 5

maxActive: 8

maxWait: 60000

timeBetweenEvictionRunsMillis: 60000

minEvictableIdleTimeMillis: 300000

validationQuery: SELECT 1

testWhileIdle: true

testOnBorrow: false

testOnReturn: false

poolPreparedStatements: true

maxPoolPreparedStatementPerConnectionSize: 20

connectionInitSqls: set names utf8mb4

filters: stat

connectionProperties:

druid:

stat:

mergeSql: true

slowSqlMillis: 1000

四：关于事务

1.DataSourceTranceManager

AbstractRoutingDataSource 只支持单库事务，也就是说切换数据源要在开启事务之前执行。

spring DataSourceTransactionManager进行事务管理，开启事务，会将数据源缓存到DataSourceTransactionObject对象中进行后续的commit rollback等事务操作。

将事务管理在数据持久 (Dao层) 开启，切换数据源的操作放在业务层进行操作，就可在事务开启之前顺利进行数据源切换，不会再出现切换失败了。

五：缺点

1.不能动态的增加数据源

2.主从延迟问题

如果往主库增加数据，马上到从库读取，一般没有问题

但是数据量大的时候，因为有延迟，可能去查询的时候，查询不到