方案一:基于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.slf4j.Logger;
import org.slf4j.LoggerFactory;
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();
}
}
package com.manli.api.base.datasource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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();
}
}
package com.manli.api.base.datasource;
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")