Spring 数据库读写分离

2023-07-19 19:04:46

读写分离常见有俩种方式

　　1 第一种方式比较常用就是定义2个数据库连接，一个是Master,另一个是Slave。更新数据时我们取Master，查询数据时取Slave。太过简单不做介绍。

　　2 第二种方数据源式动态切换，将数据源动态织入到程序中，达到动态选择读取主库还是从库的目的。主要使用的技术是：annotation，Spring AOP ，反射。下面介绍这种方式

首先创建DatabaseConfiguration

package com.testdatasource.third.configuration.datasource;

import com.alibaba.druid.pool.DruidDataSource;

import com.baomidou.mybatisplus.MybatisConfiguration;

import com.baomidou.mybatisplus.entity.GlobalConfiguration;

import com.baomidou.mybatisplus.mapper.LogicSqlInjector;

import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;

import com.testdatasource.common.enums.DatasourceType;

import com.testdatasource.third.configuration.properties.DruidProperties;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.type.JdbcType;

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

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

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;

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

import org.springframework.context.ApplicationContext;

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

import javax.sql.DataSource;

import java.util.HashMap;

import java.util.Map;

@Configuration

@EnableAutoConfiguration

public class DatabaseConfiguration {

    @Autowired

    private ApplicationContext appContext;

    @Autowired

    private DruidProperties druidProperties;

    @Bean(name = "readDatasource")

    @Primary

    @ConfigurationProperties(prefix = "read.datasource")

    public DataSource readDatasource() {

        return getReadDruidDataSource();

    }

    /**

     * 写入数据源连接池配置

     */

    @Bean(name = "writeDatasource")

    @ConfigurationProperties(prefix = "write.datasource")

    public DataSource writeDatasource() {

        return getWriteDruidDataSource();

    }

    private DruidDataSource getReadDruidDataSource() {

        DruidDataSource dataSource = new DruidDataSource();

        druidProperties.configR(dataSource);

        return dataSource;

    }

    private DruidDataSource getWriteDruidDataSource() {

        DruidDataSource dataSource = new DruidDataSource();

        druidProperties.configW(dataSource);

        return dataSource;

    }

    //初始化数据源bean，这个bean很重要，后面事务控制也会用到

    @Bean

    public AbstractRoutingDataSource roundRobinDataSouceProxy(@Qualifier("readDatasource")DataSource read, @Qualifier("writeDatasource") DataSource write) {

        DynamicDataSource proxy = new DynamicDataSource();

        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();

        targetDataSources.put( DatasourceType.read.getName(), read);

        targetDataSources.put(DatasourceType.write.getName(), write);

        proxy.setDefaultTargetDataSource(read);

        proxy.setTargetDataSources(targetDataSources);

        return proxy;

    }

    //初始化SqlSessionFactory，将自定义的多数据源ReadWriteSplitRoutingDataSource类实例注入到工厂中

    @Bean

    public SqlSessionFactory sqlSessionFactory(@Qualifier("readDatasource")DataSource read, @Qualifier("writeDatasource") DataSource write) throws Exception {

        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();

        sqlSessionFactory. setDataSource(this.roundRobinDataSouceProxy(read, write));

        sqlSessionFactory.setMapperLocations(

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

        MybatisConfiguration configuration = new MybatisConfiguration();

        configuration.setJdbcTypeForNull(JdbcType.NULL);

        configuration.setMapUnderscoreToCamelCase(true);

        configuration.setCacheEnabled(false);

        sqlSessionFactory.setConfiguration(configuration);

        sqlSessionFactory.setGlobalConfig(globalConfiguration());

        return sqlSessionFactory.getObject();

    }

    @Bean

    public GlobalConfiguration globalConfiguration() {

        GlobalConfiguration conf = new GlobalConfiguration(new LogicSqlInjector());

        conf.setLogicDeleteValue("-1");

        conf.setLogicNotDeleteValue("1");

        conf.setIdType(0);

        conf.setDbColumnUnderline(true);

        conf.setRefresh(true);

        return conf;

    }

}

创建 DataSourceContextHolder

package com.testdatasource.third.configuration.datasource;

import com.testdatasource.common.enums.DatasourceType;

/**

 * ClassName:DataSourceContextHolder

 *

 * @Description : 当前线程数据源

 */

public class DataSourceContextHolder {

    /**

     * 默认数据源

     */

    public static final String DEFAULT_DS = DatasourceType.read.getName();

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

    // 设置数据源名

    public static void setDB(String dbType) {

        contextHolder.set(dbType);

    }

    // 获取数据源名

    public static String getDB() {

        return (contextHolder.get());

    }

    // 清除数据源名

    public static void clearDB() {

        contextHolder.remove();

    }

}

创建 DynamicDataSource

package com.testdatasource.third.configuration.datasource;

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

/**

 * ClassName:DynamicDataSource

 *

 * @Description : 动态数据源

 */

public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override

    protected Object determineCurrentLookupKey() {

        return DataSourceContextHolder.getDB();

    }

}

创建 MybatisPlusConfig

package com.testdatasource.third.configuration.datasource;

import com.baomidou.mybatisplus.plugins.PaginationInterceptor;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

/**

 * ClassName:MybatisPlusConfig

 * @Description : mybatis分页插件

 * @version

 */

@Configuration

public class MybatisPlusConfig {

    /**

     * mybatis-plus分页插件<br>

     */

    @Bean

    public PaginationInterceptor paginationInterceptor() {

        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();

        paginationInterceptor.setLocalPage(true);// 开启 PageHelper 的支持

        return paginationInterceptor;

    }

}

创建 MyDataSourceTransactionManagerAutoConfiguration

package com.testdatasource.third.configuration.datasource;

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

import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;

import org.springframework.context.ApplicationContext;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

/**

 *

 * 多数据源事物

 */

@Configuration

@EnableTransactionManagement

public class MyDataSourceTransactionManagerAutoConfiguration extends DataSourceTransactionManagerAutoConfiguration {

    @Autowired

    private ApplicationContext appContext;

    /**

     * 自定义事务

     * MyBatis自动参与到spring事务管理中，无需额外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。

     * @return

     */

    @Bean(name = "transactionManager")

    public DataSourceTransactionManager transactionManagers() {

        return new DataSourceTransactionManager((DataSource)appContext.getBean("roundRobinDataSouceProxy"));

    }

}

数据源枚举类

package com.testdatasource.common.enums;

/**

 * ClassName:DatasourceType

 *

 * @Description :数据库读写类型

 * @version

 */

public enum DatasourceType {

    write("write"), read("read");

    private String name;

    private DatasourceType(String name) {

        this.name = name();

    }

    public String getName() {

        return this.name;

    }

    public void setName(String name) {

        this.name = name;

    }

}

数据源配置类

import com.alibaba.druid.pool.DruidDataSource;

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

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

import org.springframework.stereotype.Component;

import java.sql.SQLException;

/**

 * ClassName:DruidProperties

 *

 * @Description :

 *              druid连接池配置文件属性,说明:这个类中包含了许多默认配置,若这些配置符合您的情况,您可以不用管,若不符合,建议不要修改本类,建议直接在"application.yml"中配置即可

 *              </p>

 */

@Component

@ConfigurationProperties(prefix = "spring.datasource")

public class DruidProperties {

    @Value("${spring.datasource.read_url}")

    private String rurl;

    @Value("${spring.datasource.read_username}")

    private String rusername;

    @Value("${spring.datasource.read_password}")

    private String rpassword;

    @Value("${spring.datasource.write_url}")

    private String wurl;

    @Value("${spring.datasource.write_username}")

    private String wusername;

    @Value("${spring.datasource.write_password}")

    private String wpassword;

    private String driverClassName = "com.mysql.jdbc.Driver";

    private Integer initialSize = 2;

    private Integer minIdle = 1;

    private Integer maxActive = 20;

    private Integer maxWait = 60000;

    private Integer timeBetweenEvictionRunsMillis = 60000;

    private Integer minEvictableIdleTimeMillis = 300000;

    private String validationQuery = "SELECT 1 FROM DUAL";

    private Boolean testWhileIdle = true;

    private Boolean testOnBorrow = false;

    private Boolean testOnReturn = false;

    private Boolean poolPreparedStatements = true;

    private Integer maxPoolPreparedStatementPerConnectionSize = 20;

    private String filters = "stat";

    public void configR(DruidDataSource dataSource) {

        dataSource.setUrl(rurl);

        dataSource.setUsername(rusername);

//        dataSource.setPassword(AESUtil.decrypt(rpassword, null));

        dataSource.setPassword(AESUtils.AESDecode(null,rpassword));

        dataSource.setDriverClassName(driverClassName);

        dataSource.setInitialSize(initialSize); // 定义初始连接数

        dataSource.setMinIdle(minIdle); // 最小空闲

        dataSource.setMaxActive(maxActive); // 定义最大连接数

        dataSource.setMaxWait(maxWait); // 最长等待时间

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

        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

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

        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

        dataSource.setValidationQuery(validationQuery);

        dataSource.setTestWhileIdle(testWhileIdle);

        dataSource.setTestOnBorrow(testOnBorrow);

        dataSource.setTestOnReturn(testOnReturn);

        // 打开PSCache，并且指定每个连接上PSCache的大小

        dataSource.setPoolPreparedStatements(poolPreparedStatements);

        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

        try {

            dataSource.setFilters(filters);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    public void configW(DruidDataSource dataSource) {

        dataSource.setUrl(wurl);

        dataSource.setUsername(wusername);

        dataSource.setPassword(AESUtils.AESDecode(null,wpassword));

        dataSource.setDriverClassName(driverClassName);

        dataSource.setInitialSize(initialSize); // 定义初始连接数

        dataSource.setMinIdle(minIdle); // 最小空闲

        dataSource.setMaxActive(maxActive); // 定义最大连接数

        dataSource.setMaxWait(maxWait); // 最长等待时间

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

        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

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

        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

        dataSource.setValidationQuery(validationQuery);

        dataSource.setTestWhileIdle(testWhileIdle);

        dataSource.setTestOnBorrow(testOnBorrow);

        dataSource.setTestOnReturn(testOnReturn);

        // 打开PSCache，并且指定每个连接上PSCache的大小

        dataSource.setPoolPreparedStatements(poolPreparedStatements);

        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

        try {

            dataSource.setFilters(filters);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    public String getRurl() {

        return rurl;

    }

    public void setRurl(String rurl) {

        this.rurl = rurl;

    }

    public String getRusername() {

        return rusername;

    }

    public void setRusername(String rusername) {

        this.rusername = rusername;

    }

    public String getRpassword() {

        return rpassword;

    }

    public void setRpassword(String rpassword) {

        this.rpassword = rpassword;

    }

    public String getWurl() {

        return wurl;

    }

    public void setWurl(String wurl) {

        this.wurl = wurl;

    }

    public String getWusername() {

        return wusername;

    }

    public void setWusername(String wusername) {

        this.wusername = wusername;

    }

    public String getWpassword() {

        return wpassword;

    }

    public void setWpassword(String wpassword) {

        this.wpassword = wpassword;

    }

    public String getDriverClassName() {

        return driverClassName;

    }

    public void setDriverClassName(String driverClassName) {

        this.driverClassName = driverClassName;

    }

    public Integer getInitialSize() {

        return initialSize;

    }

    public void setInitialSize(Integer initialSize) {

        this.initialSize = initialSize;

    }

    public Integer getMinIdle() {

        return minIdle;

    }

    public void setMinIdle(Integer minIdle) {

        this.minIdle = minIdle;

    }

    public Integer getMaxActive() {

        return maxActive;

    }

    public void setMaxActive(Integer maxActive) {

        this.maxActive = maxActive;

    }

    public Integer getMaxWait() {

        return maxWait;

    }

    public void setMaxWait(Integer maxWait) {

        this.maxWait = maxWait;

    }

    public Integer getTimeBetweenEvictionRunsMillis() {

        return timeBetweenEvictionRunsMillis;

    }

    public void setTimeBetweenEvictionRunsMillis(Integer timeBetweenEvictionRunsMillis) {

        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;

    }

    public Integer getMinEvictableIdleTimeMillis() {

        return minEvictableIdleTimeMillis;

    }

    public void setMinEvictableIdleTimeMillis(Integer minEvictableIdleTimeMillis) {

        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;

    }

    public String getValidationQuery() {

        return validationQuery;

    }

    public void setValidationQuery(String validationQuery) {

        this.validationQuery = validationQuery;

    }

    public Boolean getTestWhileIdle() {

        return testWhileIdle;

    }

    public void setTestWhileIdle(Boolean testWhileIdle) {

        this.testWhileIdle = testWhileIdle;

    }

    public Boolean getTestOnBorrow() {

        return testOnBorrow;

    }

    public void setTestOnBorrow(Boolean testOnBorrow) {

        this.testOnBorrow = testOnBorrow;

    }

    public Boolean getTestOnReturn() {

        return testOnReturn;

    }

    public void setTestOnReturn(Boolean testOnReturn) {

        this.testOnReturn = testOnReturn;

    }

    public Boolean getPoolPreparedStatements() {

        return poolPreparedStatements;

    }

    public void setPoolPreparedStatements(Boolean poolPreparedStatements) {

        this.poolPreparedStatements = poolPreparedStatements;

    }

    public Integer getMaxPoolPreparedStatementPerConnectionSize() {

        return maxPoolPreparedStatementPerConnectionSize;

    }

    public void setMaxPoolPreparedStatementPerConnectionSize(Integer maxPoolPreparedStatementPerConnectionSize) {

        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;

    }

    public String getFilters() {

        return filters;

    }

    public void setFilters(String filters) {

        this.filters = filters;

    }

}

annotation类作用于强制写库或者读库

package com.testdatasource.common.annotation;

import com.testdatasource.common.enums.DatasourceType;

import java.lang.annotation.ElementType;

import java.lang.annotation.Retention;

import java.lang.annotation.RetentionPolicy;

/**

 * ClassName:DS

 * @Description :自定义切换数据源注解

 * @version

 */

@Target(ElementType.METHOD)

@Retention(RetentionPolicy.RUNTIME)

public @interface DS {

    DatasourceType value() default DatasourceType.write;

}

切面类DynamicDataSourceAspect 用于辅助自定义切面注解切换数据源

package com.testdatasource.third.aspect;

import com.testdatasource.common.annotation.DS;

import com.testdatasource.third.configuration.datasource.DataSourceContextHolder;

import org.aspectj.lang.JoinPoint;

import org.aspectj.lang.annotation.After;

import org.aspectj.lang.annotation.Aspect;

import org.aspectj.lang.annotation.Before;

import org.aspectj.lang.annotation.Pointcut;

import org.aspectj.lang.reflect.MethodSignature;

import org.springframework.core.annotation.Order;

import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**

 * ClassName:DynamicDataSourceAspect

 *

 * @Description : 自定义切面注解切换数据源

 * @version

 */

@Aspect

@Component

@Order(value = 2)

public class DynamicDataSourceAspect {

    @Pointcut("@annotation(com.testdatasource.common.annotation.DS)")

    public void ds() {

    }

    @Before("ds()")

    public void beforeSwitchDS(JoinPoint point) {

        // 获得当前访问的class

        Class<?> className = point.getTarget().getClass();

        // 获得访问的方法名

        String methodName = point.getSignature().getName();

        // 得到方法的参数的类型

        Class[] argClass = ((MethodSignature) point.getSignature()).getParameterTypes();

        String dataSource = DataSourceContextHolder.DEFAULT_DS;

        try {

            // 得到访问的方法对象

            Method method = className.getMethod(methodName, argClass);

            // 判断是否存在@DS注解

            if (method.isAnnotationPresent(DS.class)) {

                DS annotation = method.getAnnotation(DS.class);

                // 取出注解中的数据源名

                dataSource = annotation.value().getName();

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

        // 切换数据源

        DataSourceContextHolder.setDB(dataSource);

    }

    @After("ds()")

    public void afterSwitchDS(JoinPoint point) {

        DataSourceContextHolder.clearDB();

    }

}

yml配置

spring:

  datasource:

    type: com.alibaba.druid.pool.xa.DruidXADataSource

    read_url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&pinGlobalTxToPhysicalConnection=true

    read_username: root

    read_password: root

    write_url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&pinGlobalTxToPhysicalConnection=true

    write_username: root

    write_password: root

好了这样配置就没问题了。

码农公寓

相关文章