Java实现多数据源配置(并包括通过AOP实现数据源切换)_validationquery 多数据源

Java实现多数据源配置

1.排除自动配置类

首先在启动类中将DataSourceAutoConfiguration.class自动配置类排除

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
1

2.在配置文件填写数据源信息

datasource.druid.type=com.alibaba.druid.pool.DruidDataSource
datasource.druid.initialSize= 50
datasource.druid.minIdle= 100
datasource.druid.maxActive= 2000
datasource.druid.maxWait=60000
datasource.druid.timeBetweenEvictionRunsMillis=60000
datasource.druid.minEvictableIdleTimeMillis=30000
datasource.druid.testWhileIdle=true
datasource.druid.testOnBorrow=false
datasource.druid.testOnReturn=false
datasource.druid.poolPreparedStatements= true
datasource.druid.maxPoolPreparedStatementPerConnectionSize=20

datasource.druid.filters=stat,wall,slf4j

datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

datasource.mysql.url=jdbc:mysql://localhost:3306/data_1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
datasource.mysql.username=root
datasource.mysql.password=123456
datasource.mysql.driverClassName=com.mysql.jdbc.Driver
datasource.mysql.validationQuery=select 'x'

datasource.mysql_1.url=jdbc:mysql://localhost:3306/data_2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
datasource.mysql_1.username=root
datasource.mysql_1.password=123456
datasource.mysql_1.driverClassName=com.mysql.jdbc.Driver
datasource.mysql_1.validationQuery=select 'x'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

3.编写一个枚举类

/**
 * @ ClassName: DataSourceType
 * @ Description: 数据源类型
 * @ Auther: lipengbo
 */

public enum DataSourceType {
    Mysql("mysql"),
    Mysql_1("mysql_1"),
    Oracle("oracle");

    private String name;

    DataSourceType(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4.编写数据源上下文JdbcContextHolder(ThreadLocal)

/**
 * @ ClassName: JdbcContextHolder
 * @ Description: 动态数据源持有者，负责利用ThreadLocal存取数据源名称
 * @author lipengbo
 */

public class JdbcContextHolder {

    /** 本地线程共享对象（保证在同一线程下切换后不要被其他线程修改） */
    private final static ThreadLocal<String> local = new ThreadLocal<>();

    public static void putDataSource(String name){
        local.set(name);
    }

    public static String getDataSource(){
        return local.get();
    }

    public static void removeDataSource(){
        local.remove();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

5.使用AbstractRoutingDataSource实现动态数据源切换

Spring boot提供了AbstractRoutingDataSource 根据用户定义的规则选择当前的数据源，这样我们可以在执行查询之前，设置使用的数据源。实现可动态路由的数据源，在每次数据库查询操作前执行。它的抽象方法 determineCurrentLookupKey() 决定使用哪个数据源。

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

/**
  * @author lipengbo
  * @Description: 动态数据源实现类
  * @return
  * @throws
  */
public class DynamicDataSource extends AbstractRoutingDataSource {

    /** 数据源路由，此方法用于产生要选取的数据源逻辑名称 */
    @Override
    protected Object determineCurrentLookupKey() {
        //从共享线程中获取数据源名称
        return JdbcContextHolder.getDataSource();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

6.编写数据源配置类

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.example.constants.DataSourceType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
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.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
  * @author lipengbo
  * @Description: dataSource参数配置类
  * @return
  * @throws
  */

@Configuration
public class DataSourceConfig {

	private Logger logger = LoggerFactory.getLogger(this.getClass());

	// -----------------------------------------mysql config-------------------------------------

	@Value("${datasource.mysql.url}")
	private String dbUrl;

	@Value("${datasource.mysql.username}")
	private String username;

	@Value("${datasource.mysql.password}")
	private String password;

	@Value("${datasource.mysql.driverClassName}")
	private String driverClassName;

	@Value("${datasource.mysql.validationQuery}")
	private String validationQuery;

	@Bean(name="dataSourceMysql")
	public DataSource dataSourceMysql(){
		System.out.println("----------------主配:" + dbUrl);
		return initDruidDataSource(dbUrl,username,password,driverClassName,validationQuery);
	}
	// -----------------------------------------mysql_1 config-------------------------------------

	@Value("${datasource.mysql_1.url}")
	private String mysqlUrl;

	@Value("${datasource.mysql_1.username}")
	private String mysqlUsername;

	@Value("${datasource.mysql_1.password}")
	private String mysqlPassword;

	@Value("${datasource.mysql_1.driverClassName}")
	private String mysqlDriverClassName;

	@Value("${datasource.mysql_1.validationQuery}")
	private String mysqlValidationQuery;

	@Bean(name="dataSourceMysql_1")
	public DataSource dataSourceMysql_1(){
		System.out.println("----------------2:" + mysqlUrl);
		return initDruidDataSource(mysqlUrl,mysqlUsername,mysqlPassword,mysqlDriverClassName,mysqlValidationQuery);
	}




	// -----------------------------------------oracle config-------------------------------------

//	@Value("${datasource.oracle.url}")
//	private String oracleUrl;
//
//	@Value("${datasource.oracle.username}")
//	private String oracleUsername;
//
//	@Value("${datasource.oracle.password}")
//	private String oraclePassword;
//
//	@Value("${datasource.oracle.driverClassName}")
//	private String oracleDriverClassName;
//
//	@Value("${datasource.oracle.validationQuery}")
//	private String oracleValidationQuery;
//
//	@Bean(name="dataSourceOracle")
//	public DataSource dataSourceOracle(){
//		System.out.println("----------------次配1:" + oracleUrl);
//		return initDruidDataSource(oracleUrl,oracleUsername,oraclePassword,oracleDriverClassName,oracleValidationQuery);
//	}

	private DruidDataSource initDruidDataSource(String url,String username,String password,String driverName,String validationQuery){
		DruidDataSource datasource = new DruidDataSource();
		datasource.setUrl(url);
		datasource.setUsername(username);
		datasource.setPassword(password);
		datasource.setDriverClassName(driverName);
		datasource.setValidationQuery(validationQuery);
		setDruidOptions(datasource); // 设置druid数据源的属性
		return datasource;
	}

	// -----------------------------------------druid config-------------------------------------

	@Value("${datasource.druid.initialSize}")
	private int initialSize;

	@Value("${datasource.druid.minIdle}")
	private int minIdle;

	@Value("${datasource.druid.maxActive}")
	private int maxActive;

	@Value("${datasource.druid.maxWait}")
	private int maxWait;

	@Value("${datasource.druid.timeBetweenEvictionRunsMillis}")
	private int timeBetweenEvictionRunsMillis;

	@Value("${datasource.druid.minEvictableIdleTimeMillis}")
	private int minEvictableIdleTimeMillis;

	@Value("${datasource.druid.testWhileIdle}")
	private boolean testWhileIdle;

	@Value("${datasource.druid.testOnBorrow}")
	private boolean testOnBorrow;

	@Value("${datasource.druid.testOnReturn}")
	private boolean testOnReturn;

	@Value("${datasource.druid.poolPreparedStatements}")
	private boolean poolPreparedStatements;

	@Value("${datasource.druid.maxPoolPreparedStatementPerConnectionSize}")
	private int maxPoolPreparedStatementPerConnectionSize;

	@Value("${datasource.druid.filters}")
	private String filters;

	@Value("{datasource.druid.connectionProperties}")
	private String connectionProperties;

	private void setDruidOptions(DruidDataSource datasource){
		datasource.setInitialSize(initialSize);
		datasource.setMinIdle(minIdle);
		datasource.setMaxActive(maxActive);
		datasource.setMaxWait(maxWait);
		datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
		datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
		datasource.setTestWhileIdle(testWhileIdle);
		datasource.setTestOnBorrow(testOnBorrow);
		datasource.setTestOnReturn(testOnReturn);
		datasource.setPoolPreparedStatements(poolPreparedStatements);
		datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
		try {
			datasource.setFilters(filters);
		} catch (SQLException e) {
			logger.error("druid configuration initialization filter Exception", e);
		}
		datasource.setConnectionProperties(connectionProperties);
	}


	@Bean(name = "dynamicDataSource")
	@Primary  // 优先使用，多数据源
	public DataSource dataSource(){

		DynamicDataSource dynamicDataSource = new DynamicDataSource();
		DataSource mysql = dataSourceMysql();
		DataSource mysql_1 = dataSourceMysql_1();
//		DataSource oracle = dataSourceOracle();

		//设置默认数据源
		dynamicDataSource.setDefaultTargetDataSource(mysql);

		//配置多个数据源
		Map<Object,Object> map = new HashMap<>();
		map.put(DataSourceType.Mysql.getName(),mysql);
		map.put(DataSourceType.Mysql_1.getName(),mysql_1);
//		map.put(DataSourceType.Oracle.getName(),oracle);
		dynamicDataSource.setTargetDataSources(map);

		return dynamicDataSource;
	}

	@Bean // 事务管理
	public PlatformTransactionManager txManager() {
		return new DataSourceTransactionManager(dataSource());
	}


	@Bean(name="druidServlet")
	public ServletRegistrationBean druidServlet() {
		ServletRegistrationBean reg = new ServletRegistrationBean();
		reg.setServlet(new StatViewServlet());
		reg.addUrlMappings("/druid/*");
		reg.addInitParameter("allow", ""); // 白名单
		return reg;
	}
 
	@Bean(name = "filterRegistrationBean")
	public FilterRegistrationBean filterRegistrationBean() {
		FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
		filterRegistrationBean.setFilter(new WebStatFilter());
		filterRegistrationBean.addUrlPatterns("/*");
		filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
		filterRegistrationBean.addInitParameter("profileEnable", "true");
		filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
		filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");
		filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");
		return filterRegistrationBean;
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226

附带POM文件

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.1.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>
        <dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>
        
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

至此数据源切换基本配置就已完成

通过AOP技术实现多数据源切换

1.首先写一个注解类

import com.example.constants.DataSourceType;

import java.lang.annotation.*;

/**
 * @ PackageName: com.example.config
 * @ ClassName: MyDataSource
 * @ Description: 自定义注解TargetDataSource
 * @ Auther: lipengbo
 */

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface TargetDataSource {

    DataSourceType value() default DataSourceType.Mysql;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.编写数据源切换AOP

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.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**
 * @ PackageName: com.sjyw.sj.config
 * @ ClassName: DataSourceAspect
 * @ Description: AOP数据源切换
 *@author lipengbo
 */

@Aspect
@Order(2)
@Component
public class DataSourceAspect {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    // 切入点：service类的方法上(这个包的子包及所有包的里面的以Service结尾的类的任意方法名任意参数的方法，都讲被切到)
    @Pointcut("execution(* com.sjyw.sj..*Service..*(..))")
    public void dataSourcePointCut(){
        System.out.println("dataSourcePointCut service");
    }

    @Before("dataSourcePointCut()")
    private void before(JoinPoint joinPoint){
        Object target = joinPoint.getTarget();
        String method = joinPoint.getSignature().getName();
        Class<?> classz = target.getClass();
        Class<?>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();
        try {
            Method m = classz.getMethod(method,parameterTypes);

            // 如果 m 上存在切换数据源的注解，则根据注解内容进行数据源切换
            if (m != null && m.isAnnotationPresent(TargetDataSource.class)){
                TargetDataSource data = m.getAnnotation(TargetDataSource.class);
                JdbcContextHolder.putDataSource(data.value().getName());
                logger.debug("》》》》》》》 current thread " + Thread.currentThread().getName() + " add 【 " + data.value().getName() + " 】 to ThreadLocal");
            } else { // 如果不存在，则使用默认数据源
                logger.debug("》》》》》》》 use default datasource");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    // 执行完切面后，将线程共享中的数据源名称清空
    @After("dataSourcePointCut()")
    public void after(JoinPoint joinPoint){
        JdbcContextHolder.removeDataSource();
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63