Springboot+Mybatis+Druid基于AOP的多数据源切换和监控

        最近看技术博客的感受是“天下文章一大抄”。“大佬们”，真为看你们博客和接了你们班的人感到不幸。博客写的bug一堆一堆，不知道你是故意，总是把最关键的东西漏掉，又加些莫名其妙的东西。真心累！！！

1、配置文件

#数据源1
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=root
#数据源2
spring.datasource.driver-class-name2=com.mysql.jdbc.Driver
spring.datasource.url2=jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.username2=root
spring.datasource.password2=root
#druid连接池
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
#配置监控统计拦截的filters.去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.useGlobalDataSourceStat=true
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

2、连接池配置

package com.example.demo.config;

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

@Configuration
@ConfigurationProperties(prefix = "spring.datasource", ignoreNestedProperties = false)
public class DruidSettings {
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private String driverClassName2;
    private String url2;
    private String username2;
    private String password2;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private long maxWait;
    private long timeBetweenEvictionRunsMillis;
    private long minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private String filters;
    private int maxPoolPreparedStatementPerConnectionSize;
    private boolean useGlobalDataSourceStat;
    private String connectionProperties;
	/**
     * getter、setter忽略
     */
}

3、数据源配置

package com.example.demo.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfig {

    @Autowired
    private DruidSettings druidSettings;

    /**
     * 数据源1
     *
     * @return
     */
    @Bean(name = "dataSource1")
    public DataSource dataSource1() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(druidSettings.getDriverClassName());
        druidDataSource.setUrl(druidSettings.getUrl());
        druidDataSource.setUsername(druidSettings.getUsername());
        druidDataSource.setPassword(druidSettings.getPassword());
        druidDataSource.setInitialSize(druidSettings.getInitialSize());
        druidDataSource.setMinIdle(druidSettings.getMinIdle());
        druidDataSource.setMaxActive(druidSettings.getMaxActive());
        druidDataSource.setMaxWait(druidSettings.getMaxWait());
        druidDataSource.setTimeBetweenEvictionRunsMillis(druidSettings.getTimeBetweenEvictionRunsMillis());
        druidDataSource.setMinEvictableIdleTimeMillis(druidSettings.getMinEvictableIdleTimeMillis());
        druidDataSource.setValidationQuery(druidSettings.getValidationQuery());
        druidDataSource.setTestWhileIdle(druidSettings.isTestWhileIdle());
        druidDataSource.setTestOnBorrow(druidSettings.isTestOnBorrow());
        druidDataSource.setTestOnReturn(druidSettings.isTestOnReturn());
        druidDataSource.setPoolPreparedStatements(druidSettings.isPoolPreparedStatements());
        try {
            druidDataSource.setFilters(druidSettings.getFilters());
        } catch (Exception e) {
            e.printStackTrace();
        }
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(druidSettings.getMaxPoolPreparedStatementPerConnectionSize());
        druidDataSource.setUseGlobalDataSourceStat(druidSettings.isUseGlobalDataSourceStat());
        druidDataSource.setConnectionProperties(druidSettings.getConnectionProperties());
        return druidDataSource;
    }

    /**
     * 数据源2
     *
     * @return
     */
    @Bean(name = "dataSource2")
    public DataSource dataSource2() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(druidSettings.getDriverClassName2());
        druidDataSource.setUrl(druidSettings.getUrl2());
        druidDataSource.setUsername(druidSettings.getUsername2());
        druidDataSource.setPassword(druidSettings.getPassword2());
        druidDataSource.setInitialSize(druidSettings.getInitialSize());
        druidDataSource.setMinIdle(druidSettings.getMinIdle());
        druidDataSource.setMaxActive(druidSettings.getMaxActive());
        druidDataSource.setMaxWait(druidSettings.getMaxWait());
        druidDataSource.setTimeBetweenEvictionRunsMillis(druidSettings.getTimeBetweenEvictionRunsMillis());
        druidDataSource.setMinEvictableIdleTimeMillis(druidSettings.getMinEvictableIdleTimeMillis());
        druidDataSource.setValidationQuery(druidSettings.getValidationQuery());
        druidDataSource.setTestWhileIdle(druidSettings.isTestWhileIdle());
        druidDataSource.setTestOnBorrow(druidSettings.isTestOnBorrow());
        druidDataSource.setTestOnReturn(druidSettings.isTestOnReturn());
        druidDataSource.setPoolPreparedStatements(druidSettings.isPoolPreparedStatements());
        try {
            druidDataSource.setFilters(druidSettings.getFilters());
        } catch (Exception e) {
            e.printStackTrace();
        }
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(druidSettings.getMaxPoolPreparedStatementPerConnectionSize());
        druidDataSource.setUseGlobalDataSourceStat(druidSettings.isUseGlobalDataSourceStat());
        druidDataSource.setConnectionProperties(druidSettings.getConnectionProperties());
        return druidDataSource;
    }

    /**
     * 动态数据源
     * @Primary：在众多相同的bean中，优先选择用@Primary注解的bean
     * @return
     */
    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        //设置默认数据源
        dynamicDataSource.setDefaultTargetDataSource(dataSource1());
        //配置多数据源
        Map<Object, Object> dbMap = new HashMap<>(16);
        dbMap.put(DataSourceType.db1, dataSource1());
        dbMap.put(DataSourceType.db2, dataSource2());
        dynamicDataSource.setTargetDataSources(dbMap);
        return dynamicDataSource;
    }

    /**
     * 事务管理
     *
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }

    /**
     * Druid Servlet 配置
     *
     * @return
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        servletRegistrationBean.addInitParameter("deny", "192.168.31.10");
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "admin");
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    /**
     * Druid Filter 配置
     *
     * @return
     */
    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.jpg,*.png,*.gif,*.ico,*.css,/druid/*");
        return filterRegistrationBean;
    }
}

4、动态数据源

package com.example.demo.config;

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

public class DynamicDataSource extends AbstractRoutingDataSource {

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

    @Override
    protected Object determineCurrentLookupKey() {
        System.out.println("数据源：" + getDataSource());
        return getDataSource();
    }

    public static void setDataSource(DataSourceType dataSourceType) {
        contextHolder.set(dataSourceType);
    }

    private DataSourceType getDataSource() {
        return contextHolder.get();
    }

    public static void clearDataSource() {
        contextHolder.remove();
    }
}

AbstractRoutingDataSource源码：

package org.springframework.jdbc.datasource.lookup;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import javax.sql.DataSource;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.util.Assert;

public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
    private Map<Object, Object> targetDataSources;
    private Object defaultTargetDataSource;
    private boolean lenientFallback = true;
    private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
    private Map<Object, DataSource> resolvedDataSources;
    private DataSource resolvedDefaultDataSource;

    public AbstractRoutingDataSource() {
    }

    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        this.targetDataSources = targetDataSources;
    }

    public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
        this.defaultTargetDataSource = defaultTargetDataSource;
    }

    public void setLenientFallback(boolean lenientFallback) {
        this.lenientFallback = lenientFallback;
    }

    public void setDataSourceLookup(DataSourceLookup dataSourceLookup) {
        this.dataSourceLookup = (DataSourceLookup)(dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
    }
	 /**
     * 将targetDataSources（这里保存我们数据源配置的多个数据源）的数据源保存到resolvedDataSources
     *
     * @return
     */
    public void afterPropertiesSet() {
        if (this.targetDataSources == null) {
            throw new IllegalArgumentException("Property 'targetDataSources' is required");
        } else {
            this.resolvedDataSources = new HashMap(this.targetDataSources.size());
            Iterator var1 = this.targetDataSources.entrySet().iterator();

            while(var1.hasNext()) {
                Entry<Object, Object> entry = (Entry)var1.next();
                Object lookupKey = this.resolveSpecifiedLookupKey(entry.getKey());
                DataSource dataSource = this.resolveSpecifiedDataSource(entry.getValue());
                this.resolvedDataSources.put(lookupKey, dataSource);
            }

            if (this.defaultTargetDataSource != null) {
                this.resolvedDefaultDataSource = this.resolveSpecifiedDataSource(this.defaultTargetDataSource);
            }

        }
    }

    protected Object resolveSpecifiedLookupKey(Object lookupKey) {
        return lookupKey;
    }

    protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
        if (dataSource instanceof DataSource) {
            return (DataSource)dataSource;
        } else if (dataSource instanceof String) {
            return this.dataSourceLookup.getDataSource((String)dataSource);
        } else {
            throw new IllegalArgumentException("Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
        }
    }

    public Connection getConnection() throws SQLException {
        return this.determineTargetDataSource().getConnection();
    }

    public Connection getConnection(String username, String password) throws SQLException {
        return this.determineTargetDataSource().getConnection(username, password);
    }

    public <T> T unwrap(Class<T> iface) throws SQLException {
        return iface.isInstance(this) ? this : this.determineTargetDataSource().unwrap(iface);
    }

    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return iface.isInstance(this) || this.determineTargetDataSource().isWrapperFor(iface);
    }
	 /**
     * 获取数据源
     *
     * @return
     */
    protected DataSource determineTargetDataSource() {
        Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
        //从子类重写的determineCurrentLookupKey方法获取数据源map的key
        Object lookupKey = this.determineCurrentLookupKey();
        DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);
        if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
            dataSource = this.resolvedDefaultDataSource;
        }

        if (dataSource == null) {
            throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
        } else {
            return dataSource;
        }
    }

    protected abstract Object determineCurrentLookupKey();
}

5、AOP编写

package com.example.demo.config;

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.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;

/**
 * @Order(1)：设置该类的加载顺序，
 *
 */
@Aspect
@Order(1)
@Component
public class DynamicDataSourceAspect {

    /**
     * 前置通知：设置数据源
     *
     * @param point
     */
    @Before("@annotation(DataSource)")
    public void beforeSwitchDataSource(JoinPoint point) {
        //获取当前访问的class
        Class<?> clazz = point.getTarget().getClass();
        //获取当前访问的方法名
        String methodName = point.getSignature().getName();
        //获取当前访问方法的参数类型
        Class[] argClass = ((MethodSignature) point.getSignature()).getParameterTypes();
        //数据源类型
        DataSourceType dataSourceType = DataSourceType.db1;
        try {
            //获取访问方法对象
            Method method = clazz.getMethod(methodName, argClass);
            //判断是否存在注解
            if (method.isAnnotationPresent(DataSource.class)) {
                //获取注解对象
                DataSource dataSource = method.getAnnotation(DataSource.class);
                dataSourceType = dataSource.value();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DynamicDataSource.setDataSource(dataSourceType);
    }

    /**
     * 后置通知：清除数据源
     *
     * @param point
     */
    @After("@annotation(DataSource)")
    public void afterSwitchDataSource(JoinPoint point) {
        DynamicDataSource.clearDataSource();
    }
}

6、数据源注解

package com.example.demo.config;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {
    DataSourceType value() default DataSourceType.db1;
}

7、数据源枚举

package com.example.demo.config;

public enum DataSourceType {
    db1,db2
}

8、DAO

package com.example.demo.dao;

import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {

    List<User> select();

    int insert(List<User> list);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.demo.dao.UserMapper" >
  <resultMap id="BaseResultMap" type="com.example.demo.entity.User" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="INTEGER" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
  </resultMap>
  
  <sql id="Base_Column_List" >
    id, name, age, create_time
  </sql>

  <select id="select" resultMap="BaseResultMap" >
    select 
    <include refid="Base_Column_List" />
    from user
  </select>

  <insert id="insert" parameterType="java.util.List" >
    insert into user (
      name, age, create_time
    )
    values
    <foreach collection="list" item="item" separator=",">
      (
      #{item.name,jdbcType=VARCHAR},
      #{item.age,jdbcType=INTEGER},
      #{item.createTime,jdbcType=TIMESTAMP}
      )
    </foreach>
  </insert>

</mapper>

9、Entity

package com.example.demo.entity;

import java.util.Date;

public class User {
    private Integer id;

    private String name;

    private Integer age;

    private Date createTime;

    /**
     * getter、setter忽略
     */
}

10、Service

package com.example.demo.service;

import com.example.demo.config.DataSource;
import com.example.demo.config.DataSourceType;
import com.example.demo.dao.UserMapper;
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;
/**
 * @EnableTransactionManagement：开启事务管理
 * @Transactional：加入事务管理
 * 以上两个注解配合使用才有效。
 */
@Service
@EnableTransactionManagement
public class UserService {

    @Autowired
    private UserMapper userMapper;

    @DataSource(DataSourceType.db1)
    public List<User> queryUsers() {
        List<User> userList = userMapper.select();
        return userList;
    }

    @Transactional(rollbackFor = Exception.class)
    @DataSource(DataSourceType.db2)
    public int saveUsers(List<User> user) {
        return userMapper.insert(user);
    }
}

11、Controller

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping("/backUp")
    public int backUp() {
       List<User> userList = userService.queryUsers();
       return userService.saveUsers(userList);
    }
}

12、启动类

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
/**
 * @EnableAspectJAutoProxy：开启AOP自动代理。
 * exclude = {DataSourceAutoConfiguration.class}：禁用数据源自动配置
 */
@EnableAspectJAutoProxy
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class App {

    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }

}

13、依赖

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.3</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.13</version>
        </dependency>
  </dependencies>