解决:使用Spring AOP 实现多数据源的切换和读写分离功能

- 日理万妓 2023-08-17 15:46 16阅读 0赞

**关键词：**Spring AOP、多数据源、读写分离、读写按照权重分离、ThreadLocal

### 1、数据源选取+读写分离+权重判定 ###

1）选定数据源

2）判定是否是查询方法，并按权重进行读写分离

import org.apache.commons.collections.CollectionUtils;
    import org.aspectj.lang.ProceedingJoinPoint;
    import org.springframework.util.PatternMatchUtils;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Random;
    /**
     * * @author Administrator
     * @date 2019/6/10
     */
    public class DataSourceSelector {
        private static final Random random = new Random();
        private List<String> readMethods = new ArrayList<String>();
        private List<String> slaveDataSourceKeys = new ArrayList<>();
        private String masterDataSourceKey;
        private boolean readOnly;
        private int weight;
    
        public Object chooseDataSource(ProceedingJoinPoint pjp) throws Throwable {
            Object retVal = null;
            String masterName = getMasterDataSourceKey();
            boolean isReadOnly = isReadOnly();
            String methodName = pjp.getSignature().getName();
            if (isReadOnly && isChooseReadDB(methodName)) {
                int weight = getWeight();
                if (weight == 0) {
                    DataSourceHolder.setDataSource(chooseSlave());
                } else if (weight > 0) {
                    int randomVal = random.nextInt(100);
                    //随机, 如果随机数大于配置权重则去从库查询
                    if (randomVal < weight) {
                        DataSourceHolder.setDataSource(chooseSlave());
                    } else {
                        DataSourceHolder.setDataSource(masterName);
                    }
                } else {
                    DataSourceHolder.setDataSource(masterName);
                }
            } else {
                DataSourceHolder.setDataSource(masterName);
            }
    
            retVal = pjp.proceed();
            DataSourceHolder.clear();
            return retVal;
        }
    
    
        private String chooseSlave(){
            if(CollectionUtils.isEmpty(slaveDataSourceKeys)){
                return masterDataSourceKey;
            }
           int index = random.nextInt(slaveDataSourceKeys.size());
            return slaveDataSourceKeys.get(index);
        }
    
        private boolean isChooseReadDB(String methodName) {
            for (String mappedName : this.readMethods) {
                if (isMatch(methodName, mappedName)) {
                    return true;
                }
            }
            return false;
        }
    
        private boolean isMatch(String methodName, String mappedName) {
            return PatternMatchUtils.simpleMatch(mappedName, methodName);
        }
    
        public List<String> getReadMethods() {
            return readMethods;
        }
    
        public void setReadMethods(List<String> readMethods) {
            this.readMethods = readMethods;
        }
    
    
    
        public String getMasterDataSourceKey() {
            return masterDataSourceKey;
        }
    
        public void setMasterDataSourceKey(String masterDataSourceKey) {
            this.masterDataSourceKey = masterDataSourceKey;
        }
    
        public boolean isReadOnly() {
            return readOnly;
        }
    
        public void setReadOnly(boolean readOnly) {
            this.readOnly = readOnly;
        }
    
        public int getWeight() {
            return weight;
        }
    
        public void setWeight(int weight) {
            this.weight = weight;
        }
    
        public List<String> getSlaveDataSourceKeys() {
            return slaveDataSourceKeys;
        }
    
        public void setSlaveDataSourceKeys(List<String> slaveDataSourceKeys) {
            this.slaveDataSourceKeys = slaveDataSourceKeys;
        }
    }

### 2、针对每个线程数据源的存储和获取 ###

`DataSourceHolder使用到ThreadLocal：存放信息针对每个线程单独享有。`

/**
     * Created by Administrator on 2019/6/10.
     */
    public class DataSourceHolder {
        //线程本地环境
        private static final ThreadLocal<String> dataSources = new ThreadLocal<String>();
    
        //设置数据源
        public static void setDataSource(String customerType) {
            dataSources.set(customerType);
        }
        //获取数据源
        public static String getDataSource() {
            return (String) dataSources.get();
        }
        //清除数据源
        public static void clear() {
            dataSources.remove();
        }
    }

### 3、数据源路由获取数据源名称 ###

从线程独有的`ThreadLocal直接取出`

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    /**
     * 获取数据源（依赖于spring）
     * @author l
     */
    public class RoutingDataSource extends AbstractRoutingDataSource {
        @Override
        protected Object determineCurrentLookupKey() {
            String das = DataSourceHolder.getDataSource();
            return das;
        }
    }

### 4、数据源配置文件 ###

test.properties

#DB配置
    #数据库通用配置
    db.readOnly=false
    db.connectionTimeout=30000
    db.idleTimeout=600000
    db.maxLifetime=1800000
    db.maximumPoolSize=50
    db.minimumIdle=50
    db.driver=com.mysql.jdbc.Driver
    db.showSql=false
    #业务数据库配置
    insurance.db.master.driver=${db.driver}
    insurance.db.master.url=jdbc:mysql://192.168.115.116:3306/orderdb?autoReconnect=true&useUnicode=true&characterEncoding=${default.charset}&serverTimezone=GMT%2b8
    insurance.db.master.username=root
    insurance.db.master.password=password*909
    
    insurance.db.slave.driver=${db.driver}
    insurance.db.slave.url=jdbc:mysql://192.168.115.116:3306/orderdb?autoReconnect=true&useUnicode=true&characterEncoding=${default.charset}&serverTimezone=GMT%2b8
    insurance.db.slave.username=root
    insurance.db.slave.password=password*909
    insurance.db.slave.readOnly=true
    #订单数据库配置
    school.db.master.driver=${db.driver}
    school.db.master.url=jdbc:mysql://192.168.115.116:3306/cloudschool?autoReconnect=true&useUnicode=true&characterEncoding=${default.charset}&serverTimezone=GMT%2b8
    school.db.master.username=root
    school.db.master.password=password*909
    
    school.db.slave.driver=${db.driver}
    school.db.slave.url=jdbc:mysql://192.168.115.116:3306/cloudschool?autoReconnect=true&useUnicode=true&characterEncoding=${default.charset}&serverTimezone=GMT%2b8
    school.db.slave.username=root
    school.db.slave.password=password*909
    school.db.slave.readOnly=true
    #数据库配置
    unvi.db.master.driver=${db.driver}
    unvi.db.master.url=jdbc:mysql://192.168.115.116:3306/orderdb-unvi?autoReconnect=true&useUnicode=true&characterEncoding=${default.charset}&serverTimezone=GMT%2b8
    unvi.db.master.username=root
    unvi.db.master.password=password*909
    
    unvi.db.slave.driver=${db.driver}
    unvi.db.slave.url=jdbc:mysql://192.168.115.116:3306/orderdb-unvi?autoReconnect=true&useUnicode=true&characterEncoding=${default.charset}&serverTimezone=GMT%2b8
    unvi.db.slave.username=root
    unvi.db.slave.password=password*909
    unvi.db.slave.readOnly=true
    #数据库配置
    li.db.master.driver=${db.driver}
    li.db.master.url=jdbc:mysql://192.168.115.116:3306/orderdb?autoReconnect=true&useUnicode=true&characterEncoding=${default.charset}&serverTimezone=GMT%2b8
    li.db.master.username=root
    li.db.master.password=password*909
    
    li.db.slave.driver=${db.driver}
    li.db.slave.url=jdbc:mysql://192.168.115.116:3306/orderdb?autoReconnect=true&useUnicode=true&characterEncoding=${default.charset}&serverTimezone=GMT%2b8
    li.db.slave.username=root
    li.db.slave.password=password*909
    li.db.slave.readOnly=true
    
    #数据源名称
    datasource.school.master.key=school
    datasource.school.slave.key=school_read
    
    datasource.insurance.master.key=insurance
    datasource.insurance.slave.key=insurance_read
    
    datasource.unvi.master.key=ddyunf-unvi
    datasource.unvi.slave.key=ddyunf-unvi-read
    
    datasource.li.master.key=ddyunf-li
    datasource.li.slave.key=ddyunf-li-read

### 5、数据源+AOP配置 ###

applicationContext-jpa.xml

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xmlns:tx="http://www.springframework.org/schema/tx"
           xmlns:jpa="http://www.springframework.org/schema/data/jpa"
           xsi:schemaLocation="http://www.springframework.org/schema/beans
                               http://www.springframework.org/schema/beans/spring-beans.xsd
                              http://www.springframework.org/schema/aop
                               http://www.springframework.org/schema/aop/spring-aop.xsd
              				   http://www.springframework.org/schema/tx
              				   http://www.springframework.org/schema/tx/spring-tx.xsd
              				   http://www.springframework.org/schema/data/jpa
              				   http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">
    
        <bean id="dbConfig" abstract="true">
            <property name="driverClassName" value="${db.driver}"/>
            <property name="connectionTimeout" value="${db.connectionTimeout}"/>
            <property name="idleTimeout" value="${db.idleTimeout}"/>
            <property name="maxLifetime" value="${db.maxLifetime}"/>
            <property name="maximumPoolSize" value="${db.maximumPoolSize}"/>
            <property name="minimumIdle" value="${db.minimumIdle}"/>
            <property name="readOnly" value="${db.readOnly}"/>
        </bean>
        <bean id="${datasource.insurance.master.key}" parent="dbConfig" class="com.zaxxer.hikari.HikariDataSource"
              destroy-method="close">
            <property name="jdbcUrl" value="${insurance.db.master.url}"/>
            <property name="username" value="${insurance.db.master.username}"/>
            <property name="password" value="${insurance.db.master.password}"/>
        </bean>
        <bean id="${datasource.insurance.slave.key}" parent="dbConfig" class="com.zaxxer.hikari.HikariDataSource"
              destroy-method="close">
            <property name="jdbcUrl" value="${insurance.db.slave.url}"/>
            <property name="username" value="${insurance.db.slave.username}"/>
            <property name="password" value="${insurance.db.slave.password}"/>
            <property name="readOnly" value="${insurance.db.slave.readOnly}"/>
        </bean>
    
        <bean id="${datasource.school.master.key}" parent="dbConfig" class="com.zaxxer.hikari.HikariDataSource"
              destroy-method="close">
            <property name="jdbcUrl" value="${school.db.master.url}"/>
            <property name="username" value="${school.db.master.username}"/>
            <property name="password" value="${school.db.master.password}"/>
        </bean>
        <bean id="${datasource.school.slave.key}" parent="dbConfig" class="com.zaxxer.hikari.HikariDataSource"
              destroy-method="close">
            <property name="jdbcUrl" value="${school.db.slave.url}"/>
            <property name="username" value="${school.db.slave.username}"/>
            <property name="password" value="${school.db.slave.password}"/>
            <property name="readOnly" value="${school.db.slave.readOnly}"/>
        </bean>
    
        <bean id="${datasource.unvi.master.key}" parent="dbConfig" class="com.zaxxer.hikari.HikariDataSource"
              destroy-method="close">
            <property name="jdbcUrl" value="${unvi.db.master.url}"/>
            <property name="username" value="${unvi.db.master.username}"/>
            <property name="password" value="${unvi.db.master.password}"/>
        </bean>
    
        <bean id="${datasource.unvi.slave.key}" parent="dbConfig" class="com.zaxxer.hikari.HikariDataSource"
              destroy-method="close">
            <property name="jdbcUrl" value="${unvi.db.slave.url}"/>
            <property name="username" value="${unvi.db.slave.username}"/>
            <property name="password" value="${unvi.db.slave.password}"/>
            <property name="readOnly" value="${unvi.db.slave.readOnly}"/>
        </bean>
    
        <bean id="${datasource.li.master.key}" parent="dbConfig" class="com.zaxxer.hikari.HikariDataSource"
              destroy-method="close">
            <property name="jdbcUrl" value="${li.db.master.url}"/>
            <property name="username" value="${li.db.master.username}"/>
            <property name="password" value="${li.db.master.password}"/>
        </bean>
    
        <bean id="${datasource.li.slave.key}" parent="dbConfig" class="com.zaxxer.hikari.HikariDataSource"
              destroy-method="close">
            <property name="jdbcUrl" value="${li.db.slave.url}"/>
            <property name="username" value="${li.db.slave.username}"/>
            <property name="password" value="${li.db.slave.password}"/>
            <property name="readOnly" value="${li.db.slave.readOnly}"/>
        </bean>
    
        
        <bean id="routingDataSource" class="com.ddyunf.cloud.common.datasource.RoutingDataSource">
            <property name="targetDataSources">
                <map key-type="java.lang.String">
                    <entry value-ref="${datasource.insurance.master.key}" key="${datasource.insurance.master.key}"></entry>
                    <entry value-ref="${datasource.insurance.slave.key}" key="${datasource.insurance.slave.key}"></entry>
    
                    <entry value-ref="${datasource.school.master.key}" key="${datasource.school.master.key}"></entry>
                    <entry value-ref="${datasource.school.slave.key}" key="${datasource.school.slave.key}"></entry>
    
                    <entry value-ref="${datasource.unvi.master.key}" key="${datasource.unvi.master.key}"></entry>
                    <entry value-ref="${datasource.unvi.slave.key}" key="${datasource.unvi.slave.key}"></entry>
    
                    <entry value-ref="${datasource.li.master.key}" key="${datasource.li.master.key}"></entry>
                    <entry value-ref="${datasource.li.slave.key}" key="${datasource.li.slave.key}"></entry>
                </map>
            </property>
            
            <property name="defaultTargetDataSource" ref="${datasource.unvi.master.key}">
            </property>
        </bean>
    
    
        
        <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
            <property name="dataSource" ref="routingDataSource"/>
            <property name="packagesToScan" value="com.ddyunf.**.model"/>
            <property name="jpaVendorAdapter">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                    <property name="generateDdl" value="false"/>
                    <property name="showSql" value="${db.showSql}"/>
                    <property name="databasePlatform" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
                </bean>
            </property>
            <property name="jpaDialect">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"/>
            </property>
            <property name="jpaPropertyMap">
                <map>
                    <entry key="hibernate.query.substitutions" value="true 1, false 0"/>
                    <entry key="hibernate.default_batch_fetch_size" value="16"/>
                    <entry key="hibernate.max_fetch_depth" value="2"/>
                    <entry key="hibernate.generate_statistics" value="true"/>
                    <entry key="hibernate.bytecode.use_reflection_optimizer" value="true"/>
                    <entry key="hibernate.cache.use_second_level_cache" value="false"/>
                    <entry key="hibernate.cache.use_query_cache" value="false"/>
                </map>
            </property>
        </bean>
        
        <jpa:repositories base-package="com.ddyunf.**.dao"
                          query-lookup-strategy="create-if-not-found" repository-impl-postfix="ExtImpl"
                          entity-manager-factory-ref="entityManagerFactory"
                          transaction-manager-ref="transactionManager">
        </jpa:repositories>
        
        <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
            <property name="entityManagerFactory" ref="entityManagerFactory"/>
        </bean>
        
        <tx:annotation-driven transaction-manager="transactionManager"/>
    
        <bean id="dataSourceSelector4Insurance" class="com.ddyunf.cloud.common.datasource.DataSourceSelector">
            <property name="masterDataSourceKey" value="${datasource.insurance.master.key}"/>
            <property name="slaveDataSourceKeys">
                <list>
                    <value>${datasource.insurance.slave.key}</value>
                </list>
            </property>
            <property name="readOnly" value="true"/>
            <property name="weight" value="60"/>
            <property name="readMethods">
                <list>
                    <value>query*</value>
                    <value>count*</value>
                    <value>find*</value>
                    <value>list*</value>
                    <value>search*</value>
                </list>
            </property>
        </bean>
        <bean id="dataSourceSelector4School" class="com.ddyunf.cloud.common.datasource.DataSourceSelector">
            <property name="masterDataSourceKey" value="${datasource.school.master.key}"/>
            <property name="slaveDataSourceKeys">
                <list>
                    <value>${datasource.school.slave.key}</value>
                </list>
            </property>
            <property name="readOnly" value="true"/>
            <property name="weight" value="60"/>
            <property name="readMethods">
                <list>
                    <value>query*</value>
                    <value>count*</value>
                    <value>find*</value>
                    <value>list*</value>
                    <value>search*</value>
                </list>
            </property>
        </bean>
    
        <bean id="dataSourceSelector4unvi" class="com.ddyunf.cloud.common.datasource.DataSourceSelector">
            <property name="masterDataSourceKey" value="${datasource.unvi.master.key}"/>
            <property name="slaveDataSourceKeys">
                <list>
                    <value>${datasource.unvi.slave.key}</value>
                </list>
            </property>
            <property name="readOnly" value="true"/>
            <property name="weight" value="60"/>
            <property name="readMethods">
                <list>
                    <value>query*</value>
                    <value>count*</value>
                    <value>find*</value>
                    <value>list*</value>
                    <value>search*</value>
                </list>
            </property>
        </bean>
    
        <bean id="dataSourceSelector4Li" class="com.ddyunf.cloud.common.datasource.DataSourceSelector">
            <property name="masterDataSourceKey" value="${datasource.li.master.key}"/>
            <property name="slaveDataSourceKeys">
                <list>
                    <value>${datasource.li.slave.key}</value>
                </list>
            </property>
            <property name="readOnly" value="true"/>
            <property name="weight" value="60"/>
            <property name="readMethods">
                <list>
                    <value>query*</value>
                    <value>count*</value>
                    <value>find*</value>
                    <value>list*</value>
                    <value>search*</value>
                </list>
            </property>
        </bean>
    
        
        <aop:config expose-proxy="true">
            <aop:aspect ref="dataSourceSelector4Insurance" order="1">
                
                <aop:pointcut id="txPointcut4Insurance" expression="execution(* com.ddyunf.cloud.vi.*..*(..))
                || execution(* com.ddyunf.cloud.base.*..*(..))
                || execution(* com.ddyunf.cloud.oldCustomer.*..*(..))
                || execution(* com.ddyunf.cloud.message.*..*(..))
                || execution(* com.ddyunf.cloud.user.*..*(..))
                || execution(* com.ddyunf.cloud.settlement.*..*(..))
                || execution(* com.ddyunf.cloud.gift.*..*(..))
                || execution(* com.ddyunf.cloud.express.*..*(..))
                || execution(* com.ddyunf.cloud.teletraffic.*..*(..))
                || execution(* com.ddyunf.cloud.commodity.*..*(..))
                || execution(* com.ddyunf.cloud.credit.*..*(..))
                || execution(* com.ddyunf.cloud.log.*..*(..))"/>
    
                <aop:around pointcut-ref="txPointcut4Insurance" method="chooseDataSource"/>
            </aop:aspect>
            <aop:aspect ref="dataSourceSelector4School" order="1">
                
                <aop:pointcut id="txPointcut4School" expression="execution(* com.ddyunf.cloud.school.*..*(..))"/>
                <aop:around pointcut-ref="txPointcut4School" method="chooseDataSource"/>
            </aop:aspect>
            <aop:aspect ref="dataSourceSelector4unvi" order="1">
                
                <aop:pointcut id="txPointcut4unvi"
                              expression="execution(* com.ddyunf.cloud.unvi.*..*(..)) || execution(* com.ddyunf.cloud.product.*..*(..)) || execution(* com.ddyunf.cloud.customer.*..*(..))"/>
                <aop:around pointcut-ref="txPointcut4unvi" method="chooseDataSource"/>
            </aop:aspect>
            <aop:aspect ref="dataSourceSelector4Li" order="1">
                
                <aop:pointcut id="txPointcut4Li" expression="execution(* com.ddyunf.cloud.li.*..*(..))"/>
                <aop:around pointcut-ref="txPointcut4Li" method="chooseDataSource"/>
            </aop:aspect>
        </aop:config>
    </beans>

## ##