数据库读写分离程序实现--AOP方式（SpringMVC+Mybatis+Mysql5.6）

本文链接：https://blog.youkuaiyun.com/BuFanQi_Info/article/details/80801705

简介

笔者上篇文章写到数据库主从配置–<数据库主从配置入门级>，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力
笔者在<数据库读写分离程序实现–ReplicationDriver方式>介绍了通过ReplicationDriver+Transactional注解的方式实现读写分离
本文笔者将使用AOP+多数据源动态切换的方式实现读写分离
在目前项目开发中，由于用户流量的逐渐增加、业务模块的增加，应用单数据库已无法满足业务需要，从而衍生了数据库读写分离、业务数据分库存储、数据库集群等数据库操作模式来提高访问效率、数据归类、减轻数据数据库压力。

本图为引用借鉴

单一数据源

如上图第一部分，一般中小型项目普遍使用单一数据源的模式

多数据源

图第二部分

图二部分可以看到，配置了两个sessionfactory分别对应两个datasoure，在做数据层操作时，可在到层接口上指定数据源，从而达到多库访问。（淡然可配置多个datasoure+sessionfactory）

图第三部分

图三部分可以看到，配置了两个datasoure，使用AbstractRoutingDataSource的实现类通过AOP或者手动处理实现动态的使用我们的数据源，这样的入侵性较低。通过AOP或者手动编程设置当前的DataSource，不用修改我们编写的数据访问层接口

本文使用AbstractRoutingDataSource动态切换数据源的形式实现数据库读写分离

db.properties

##DB1
db.c3p0.driverClassName=com.mysql.jdbc.ReplicationDriver   
## 使用的是jdbc:mysql:replication://  
db.c3p0.url=jdbc:mysql:replication://XXXX:XXX,XXXX:XXX/smart_db_study?characterEncoding=utf8
db.c3p0.username=XXX
db.c3p0.password=XXX
##DB2
db1.domain=log
db1.c3p0.driverClassName=com.mysql.jdbc.Driver
db1.c3p0.url=jdbc:mysql://XXX:XXX/smart_db_study?useUnicode=true&characterEncoding=utf8
db1.c3p0.username=${db.c3p0.username}
db1.c3p0.password=${db.c3p0.password}

db.mybatis.aliases=com.lswd.mapper
db.mybatis.mapperloc=classpath:com/**/mapper/**/*Mapper.xml

MultipleC3p0DataSource数据源配置

package com.lswd.db.config;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.util.StringUtils;

import com.lswd.db.dynamic.DynamicDataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

@Configuration
@PropertySource("classpath:db.properties") 
public class MultipleC3p0DataSource {

    @Autowired
    private Environment environment;

    private DataSource getDataSource(int index){
        try {

            String i = index > 0 ? String.format("%d", index) : "";
            //读取配置文件数据源配置信息，通过index区分
            String url = environment.getProperty("db"+i+".c3p0.url");
            if (StringUtils.isEmpty(url)){
                return null;
            }

            String prefix = StringUtils.isEmpty(i) ? "" : "db"+i;

             ComboPooledDataSource dataSource = new ComboPooledDataSource();
             String dbclass = environment.getProperty("db"+i+".c3p0.driverClassName", String.class, "com.mysql.jdbc.Driver");
             dataSource.setDriverClass(dbclass);
             dataSource.setJdbcUrl(url);
             dataSource.setUser(environment.getProperty("db"+i+".c3p0.username"));
             dataSource.setPassword(environment.getProperty("db"+i+".c3p0.password"));
             dataSource.setAcquireIncrement(environment.getProperty(prefix+"c3p0.acquireIncrement", int.class, 5));
             dataSource.setInitialPoolSize(environment.getProperty(prefix+"c3p0.initialPoolSize", int.class, 5));
             dataSource.setMaxPoolSize(environment.getProperty(prefix+"c3p0.maxPoolSize", int.class, 200));
             dataSource.setMinPoolSize(environment.getProperty(prefix+"c3p0.minPoolSize", int.class, 5));
             dataSource.setMaxIdleTime(environment.getProperty(prefix+"c3p0.maxIdleSize", int.class, 1800));
             dataSource.setMaxIdleTimeExcessConnections(environment.getProperty(prefix+"c3p0.maxIdleTimeExcessConnections", int.class, 1200));
             dataSource.setMaxConnectionAge(environment.getProperty(prefix+"c3p0.maxConnectionAge", int.class, 1000));
             dataSource.setPreferredTestQuery(environment.getProperty(prefix+"c3p0.preferredTestQuery", "select 1 from dual"));
             dataSource.setIdleConnectionTestPeriod(environment.getProperty(prefix+"c3p0.idleConnectionTestPeriod", int.class, 120));
             dataSource.setAutoCommitOnClose(false);

            return dataSource;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    @Bean
    @Primary
    //创建数据源
    public DynamicDataSource dataSource() {
        DynamicDataSource dataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        int i = 0;
        DataSource ds = getDataSource(i);
        while (ds != null) {
            String key = environment.getProperty("db" + i + ".domain");
            if (StringUtils.isEmpty(key)){
                key = "mapper";
                dataSource.setDefaultTargetDataSource(ds);// 默认的datasource设置为mapper
            }
            targetDataSources.put(key, ds);
            i ++;
            ds = getDataSource(i);
        }

        dataSource.setTargetDataSources(targetDataSources);
        return dataSource;
    }

    // use mybatis
    @Bean
    public SqlSessionFactory sqlSessionFactory(DynamicDataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setTypeAliasesPackage(environment.getProperty("db.mybatis.aliases")));
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(environment.getProperty("db.mybatis.mapperloc"))) )); 
        return sessionFactory.getObject();
    }

    @Bean(value="txManager")
    public DataSourceTransactionManager dataSourceTransactionManager(DynamicDataSource dataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }

}

DatabaseContextHolder–当前线程使用的数据源名

package com.lswd.db.dynamic;

import java.util.ArrayList;
import java.util.List;

public class DatabaseContextHolder {
    //当前线程使用的数据源名
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
     // 设置当前线程使用的数据源名
    public static void setDatabaseType(String type){
        contextHolder.set(type);
    }
    // 获取前线程使用的数据源名
    public static String getDatabaseType(){
        return contextHolder.get();
    }
    // 清除前线程使用的数据源名
    public static void clearDataSourceType() {  
        contextHolder.remove();  
    } 
}

DynamicDataSource–多数据源的选择

package com.lswd.db.dynamic;

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

public class DynamicDataSource extends AbstractRoutingDataSource {
    //根据Key获取数据源的信息，上层抽象函数的钩子
    @Override
    protected Object determineCurrentLookupKey() {
       // 获取前线程使用的数据源名
        String key = DatabaseContextHolder.getDatabaseType();
        return key;
    }
}

通过AOP切面的方式拦截查询方法，将数据源切换至从库

package com.lswd.db.dynamic;

import java.lang.reflect.Method;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

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;
import org.aspectj.lang.reflect.MethodSignature;
@Aspect
@Component
public class MultipleDataSourceSelectorAspect {

    /**
     * 使用空方法定义切点表达式
     */
    @Pointcut("execution(public * com.lswd.mapper..*Mapper..*(..))")
    public void declareJointPointExpression() {
    }

    private static Pattern p = Pattern.compile("\\.([^\\.]+)\\.[^\\.]+$");

    /**
     * 使用定义切点表达式的方法进行切点表达式的引入
     */
    @Around("declareJointPointExpression()")
    public Object setDataSourceKey(ProceedingJoinPoint point) throws Throwable {
        Object result = null;
        try {
            MethodSignature ms= (MethodSignature)point.getSignature();
            Method mt = ms.getMethod();
            if(mt.getName().contains("query")){
                System.out.println(mt.getName() + ": set datasource to query");
                DatabaseContextHolder.setDatabaseType("log");
            }else{
                DatabaseContextHolder.setDatabaseType("mapper");
            }       
            result = point.proceed();
        } finally {
            DatabaseContextHolder.clearDataSourceType();
        }
        return result;
    }
}

测试

    //@Transactional(value = "txManager",propagation=Propagation.REQUIRED,rollbackFor=Exception.class,readOnly=true)
   Integer selectTalCount();
      Integer queryTalCount();

@Test
    public  void pushMeun() throws SQLException{
        System.out.println("条数为frist1："+manageImageMapper.queryTalCount());
        System.out.println("条数为frist2："+manageImageMapper.selectTalCount());

    }