多資料源管理以及資料的讀寫分離

方案一：基于AbstractDataSource實作主從資料庫切換

package com.manli.api.base.aop;

import com.manli.api.base.datasource.DynmicDataSourceContextHolder;

import com.manli.api.util.LogAopUtil;

import org.aspectj.lang.JoinPoint;

import org.aspectj.lang.annotation.*;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.context.annotation.EnableAspectJAutoProxy;

import org.springframework.core.annotation.Order;

import org.springframework.stereotype.Component;

/**

• 切面到service 通過方法名指定資料源
• @author Shensg
• 2018年5月31日

*/

@Aspect

@Order(-1)

@Component

@EnableAspectJAutoProxy(proxyTargetClass = true)

public class DataSourceAspect {

private Logger log = LoggerFactory.getLogger(DataSourceAspect.class);   
   
ThreadLocal<Long> startTime = new ThreadLocal<>();   

 
@Pointcut("execution(* com.manli.api.service..*Service.*(..))")
public void webLog(){}   

@Before("webLog()")   
public void doBefore(JoinPoint joinPoint) throws Throwable {   
    startTime.set(System.currentTimeMillis());   
    String method = joinPoint.getSignature().getDeclaringTypeName() + "." + joinPoint.getSignature().getName();
    Object[] args = joinPoint.getArgs();
    String classType = joinPoint.getTarget().getClass().getName();
    Class<?> clazz = Class.forName(classType);
    String clazzName = clazz.getName();
    String methodName = joinPoint.getSignature().getName(); // 擷取方法名稱
     
     
    /*log.info("DataSourceAspect.doBefore 切面service");
    log.info("===============請求内容===============");
    log.info("切入點: " + joinPoint.getTarget().getClass().getName() + "類中"
            + method + "方法");
    // 擷取參數名稱和值
     StringBuffer sb = LogAopUtil.getNameAndArgs(this.getClass(), clazzName, methodName, args);
     log.info("請求類方法參數名稱和值：" + sb);
     log.info("===============請求内容===============");*/
    DynmicDataSourceContextHolder.clear();
    if (joinPoint.getTarget().getClass().getName().contains("Primary")) {
        if (method.startsWith("add")
                || method.startsWith("create")
                || method.startsWith("save")
                || method.startsWith("edit")
                || method.startsWith("update")
                || method.startsWith("delete")
                || method.startsWith("remove")
                || method.startsWith("grant")
                || method.startsWith("get")
                ) {
            log.info("切換到: primaryDataSource");
            DynmicDataSourceContextHolder.setDataSourceKey("primaryDataSource");  
            return;
        } else {
            log.info("切換到: primaryDataSource");
            DynmicDataSourceContextHolder.setDataSourceKey("primaryDataSource");
            return;
        }
    }
    if (joinPoint.getTarget().getClass().getName().contains("Second")) {
        log.info("切換到: secondDataSource");
        DynmicDataSourceContextHolder.setDataSourceKey("secondDataSource");
        return;
    }
    log.warn("沒有分庫操作,切換到預設庫：primaryDataSource");
    DynmicDataSourceContextHolder.setDataSourceKey("primaryDataSource");
}   

@AfterReturning(returning = "ret", pointcut = "webLog()")   
public void doAfterReturning(Object ret) throws Throwable {   
    // 處理完請求，傳回内容  
    /*log.info("===============響應内容===============");
    log.info("DataSourceAspect.doAfterReturning 切面service");
    log.info("RESPONSE=" + ret);
    log.info("SPEND TIME=" + (System.currentTimeMillis() - startTime.get()));
    log.info("===============響應内容===============");*/
    DynmicDataSourceContextHolder.clear();
}   
// 抛出Exception之後被調用
@AfterThrowing(throwing = "ex", pointcut = "webLog()")  
 public void afterThrowing(Exception ex) throws Throwable {
    DynmicDataSourceContextHolder.setDataSourceKey("primaryDataSource");
    log.error("DataSourceAspect.afterThrowing 切面service");
     log.error("資料操作出現異常,切換到: readParkingData"+ex.getMessage());
 }           

}

package com.manli.api.base.datasource;

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

public class DynmicDataSource extends AbstractRoutingDataSource {

private Logger log = LoggerFactory.getLogger(DynmicDataSource.class);  
/** 
 * 傳回的内容是targetDataSources 的Key 
 */   
@Override   
protected Object determineCurrentLookupKey() {   
    return DynmicDataSourceContextHolder.getDataSourceKey();   
}   
           

}

import org.springframework.util.Assert;

//資料源通過key 設定和擷取

public class DynmicDataSourceContextHolder {

private static Logger log = LoggerFactory.getLogger(DynmicDataSourceContextHolder.class);  
public static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();   
 
public static String getDataSourceKey() {   
    return contextHolder.get();   
}   

public static void setDataSourceKey(String dataSourcekey) {   
    Assert.notNull(dataSourcekey, "dataSource cannot be null");
    contextHolder.set(dataSourcekey);   
}   
 
public static void clear() {   
    contextHolder.remove();   
}           

import com.alibaba.druid.filter.Filter;

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.support.spring.stat.DruidStatInterceptor;

import com.alibaba.druid.wall.WallConfig;

import com.alibaba.druid.wall.WallFilter;

import org.springframework.aop.Advisor;

import org.springframework.aop.support.DefaultPointcutAdvisor;

import org.springframework.aop.support.JdkRegexpMethodPointcut;

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

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

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

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Properties;

// 定義資料源并設定到動态配置設定進行管理

@Configuration

@EnableConfigurationProperties(DruidSettings.class)

public class DruidDataSourceConfig {

@Autowired
private DruidSettings druidSettings;
@Bean(name="primaryDataSource",initMethod = "init", destroyMethod = "close")
@Primary
//設定為主要的，當同一個類型存在多個Bean的時候，spring 會預設注入以@Primary注解的bean 
public DataSource primaryDataSource() throws Exception{
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setDriverClassName(druidSettings.getDriverClassName());
    dataSource.setUrl(druidSettings.getUrl());
    dataSource.setUsername(druidSettings.getUsername());
    dataSource.setPassword(druidSettings.getPassword());
    dataSource.setInitialSize(druidSettings.getInitialSize());
    dataSource.setMinIdle(druidSettings.getMinIdle());
    dataSource.setMaxActive(druidSettings.getMaxActive());
    dataSource.setMaxWait(druidSettings.getMaxWait());
    dataSource.setTimeBetweenEvictionRunsMillis(druidSettings.getTimeBetweenEvictionRunsMillis());
    dataSource.setMinEvictableIdleTimeMillis(druidSettings.getMinEvictableIdleTimeMillis());
    String validationQuery = druidSettings.getValidationQuery();
    if (validationQuery != null && !"".equals(validationQuery)) {
        dataSource.setValidationQuery(validationQuery);
    }
    dataSource.setTestWhileIdle(druidSettings.isTestWhileIdle());
    dataSource.setTestOnBorrow(druidSettings.isTestOnBorrow());
    dataSource.setTestOnReturn(druidSettings.isTestOnReturn());
    if(druidSettings.isPoolPreparedStatements()){
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(druidSettings.getMaxPoolPreparedStatementPerConnectionSize());
    }
    dataSource.setFilters(druidSettings.getFilters());//這是最關鍵的,否則SQL監控無法生效
    String connectionPropertiesStr = druidSettings.getConnectionProperties();
    if(connectionPropertiesStr != null && !"".equals(connectionPropertiesStr)){
        Properties connectProperties = new Properties();
        String[] propertiesList = connectionPropertiesStr.split(";");
        for(String propertiesTmp:propertiesList){
            String[] obj = propertiesTmp.split("=");
            String key = obj[0];
            String value = obj[1];
            connectProperties.put(key,value);
        }
        dataSource.setConnectProperties(connectProperties);
    }
    dataSource.setUseGlobalDataSourceStat(druidSettings.isUseGlobalDataSourceStat());
    /**
     * add ProxyFilters
     */
    List<Filter> filters = new ArrayList<>();
    filters.add(wallFilter());
    filters.add(new MyDruidFilter());
    dataSource.setProxyFilters(filters);
    return dataSource;
}
 
@Bean(name="secondDataSource",initMethod = "init", destroyMethod = "close")
public DataSource secondDataSource() throws Exception{
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setDriverClassName(druidSettings.getDriverClassNameTwo());
    dataSource.setUrl(druidSettings.getUrlTwo());
    dataSource.setUsername(druidSettings.getUsernameTwo());
    dataSource.setPassword(druidSettings.getPasswordTwo());
    dataSource.setInitialSize(druidSettings.getInitialSize());
    dataSource.setMinIdle(druidSettings.getMinIdle());
    dataSource.setMaxActive(druidSettings.getMaxActive());
    dataSource.setMaxWait(druidSettings.getMaxWait());
    dataSource.setTimeBetweenEvictionRunsMillis(druidSettings.getTimeBetweenEvictionRunsMillis());
    dataSource.setMinEvictableIdleTimeMillis(druidSettings.getMinEvictableIdleTimeMillis());
    String validationQuery = druidSettings.getValidationQuery();
    if (validationQuery != null && !"".equals(validationQuery)) {
        dataSource.setValidationQuery(validationQuery);
    }
    dataSource.setTestWhileIdle(druidSettings.isTestWhileIdle());
    dataSource.setTestOnBorrow(druidSettings.isTestOnBorrow());
    dataSource.setTestOnReturn(druidSettings.isTestOnReturn());
    if(druidSettings.isPoolPreparedStatements()){
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(druidSettings.getMaxPoolPreparedStatementPerConnectionSize());
    }
    dataSource.setFilters(druidSettings.getFilters());//這是最關鍵的,否則SQL監控無法生效
    String connectionPropertiesStr = druidSettings.getConnectionProperties();
    if(connectionPropertiesStr != null && !"".equals(connectionPropertiesStr)){
        Properties connectProperties = new Properties();
        String[] propertiesList = connectionPropertiesStr.split(";");
        for(String propertiesTmp:propertiesList){
            String[] obj = propertiesTmp.split("=");
            String key = obj[0];
            String value = obj[1];
            connectProperties.put(key,value);
        }
        dataSource.setConnectProperties(connectProperties);
    }
    dataSource.setUseGlobalDataSourceStat(druidSettings.isUseGlobalDataSourceStat());
    /**
     * add ProxyFilters
     */
    List<Filter> filters = new ArrayList<>();
    filters.add(wallFilter());
    filters.add(new MyDruidFilter());
    dataSource.setProxyFilters(filters);
    return dataSource;
}
 
@Bean(name="dataSource")  
public DynmicDataSource dynmicDataSource() throws Exception {   
    DynmicDataSource dynmicDataSource = new DynmicDataSource();   
     
    Map<Object, Object> targetDataSources = new HashMap<>();   
    targetDataSources.put("primaryDataSource", primaryDataSource());   
    targetDataSources.put("secondDataSource", secondDataSource());   
    dynmicDataSource.setTargetDataSources(targetDataSources);   
    dynmicDataSource.setDefaultTargetDataSource(primaryDataSource());   
     
    return dynmicDataSource;   
}
 
/**
 * 監聽Spring
 *  1.定義攔截器
 *  2.定義切入點
 *  3.定義通知類
 * @return
 */
@Bean
public DruidStatInterceptor druidStatInterceptor(){
    return new DruidStatInterceptor();
}
@Bean
public JdkRegexpMethodPointcut druidStatPointcut(){
    JdkRegexpMethodPointcut druidStatPointcut = new JdkRegexpMethodPointcut();
    String patterns = "com.manli.api.controller.*";
    String patterns2 = "com.manli.api.service.*";
    druidStatPointcut.setPatterns(patterns,patterns2);
    return druidStatPointcut;
}
@Bean
public Advisor druidStatAdvisor() {
    return new DefaultPointcutAdvisor(druidStatPointcut(), druidStatInterceptor());
}
   
@Bean
@ConfigurationProperties("spring.datasource.druid.filter.wall.config")
public WallConfig wallConfig(){
    return new WallConfig();
}
@Bean
public WallFilter wallFilter(){
    WallFilter filter = new WallFilter();
    WallConfig wallConfig = wallConfig();
    filter.setConfig(wallConfig);
    filter.setDbType("mysql");
    return filter;
}           

方案二：整合mybatisplus ， 可以使用@Ds("datasouceName")