spring boot+mybatis 多数据源切换(实例讲解)

spring boot+mybatis 多数据源切换(实例讲解)

由于公司业务划分了多个数据库，开发一个项目会同事调用多个库，经过学习我们采用了注解+aop的方式实现的

1.首先定义一个注解类

@Retention(RetentionPolicy.RUNTIME)

@Target(ElementType.METHOD)

public @interface TargetDataSource {

String value();//此处接收的是数据源的名称

}

2.然后建一个配置类，这个在项目启动时会加载数据源，一开始采用了HikariCP，查资料说是最快性能最好的，然后又发现了阿里的druid，这个功能比较全面，而且性能也还可以，最主要他还有监控功能，具体实现看如下代码

package com.example.demo.datasource;

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.support.http.StatViewServlet;

import com.alibaba.druid.support.http.WebStatFilter;

import com.example.demo.datasource.DynamicDataSource;

import com.zaxxer.hikari.HikariConfig;

import com.zaxxer.hikari.HikariDataSource;

import lombok.extern.slf4j.Slf4j;

import org.mybatis.spring.annotation.MapperScan;

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

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

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.jdbc.datasource.DataSourceTransactionManager;

import org.springframework.scheduling.annotation.EnableScheduling;

import org.springframework.scheduling.annotation.Scheduled;

import org.springframework.transaction.PlatformTransactionManager;

import org.w3c.dom.NodeList;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

import org.w3c.dom.Node;

import javax.servlet.annotation.WebInitParam;

import javax.servlet.annotation.WebServlet;

import javax.sql.DataSource;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.util.HashMap;

import java.util.Map;

import java.io.File;

import com.alibaba.druid.support.http.StatViewServlet;

/**

* Author: wangchao

* Version：

* Date: 2017/9/11

* Description:数据源配置

* Modification History:

* Date Author Version Description

* --------------------------------------------------------------

* Why & What is modified:

*/

@Configuration

@EnableScheduling

public class DataSourceConfig {

/*@Autowired

private DBProperties properties;*/

@Value("${datasource.filePath}")

private String filePath;//数据源配置

@Bean(name = "dataSource")

public DataSource dataSource() {

//按照目标数据源名称和目标数据源对象的映射存放在Map中

Map

//查找xml数据连接字符串

targetDataSources=getdataMap(filePath);

//动态获取DBProperties类申明的属性

/*Field[] fields=properties.getClass().getDeclaredFields();

for(int i=0;i

{

targetDataSources.put(fields[i].getName(), getFieldValueByName(fields[i].getName(),properties));

}*/

//采用是想AbstractRoutingDataSource的对象包装多数据源

DynamicDataSource dataSource = new DynamicDataSource();

dataSource.setTargetDataSources(targetDataSources);

//设置默认的数据源，当拿不到数据源时，使用此配置

//dataSource.setDefaultTargetDataSource(properties.getUzaiTravel());

return dataSource;

}

@Bean

public PlatformTransactionManager txManager() {

return new DataSourceTransactionManager(dataSource());

}

/**

*获取数据源集合

*/

private Map

{

try {

Map

File xmlFile = new File(fiePath);

DocumentBuilderFactory builderFactory = DocumentBuilderFactory.newInstance();

DocumentBuilder builder = builderFactory.newDocumentBuilder();

Document doc = builder.parse(xmlFile);

doc.getDocumentElement().normalize();

System.out.println("Root element: " + doc.getDocumentElement().getNodeName());

NodeList nList = doc.getElementsByTagName("db");

for(int i = 0 ; i

Node node = nList.item(i);

Element ele = (Element)node;

/*HikariConfig config = new HikariConfig();

config.setDriveWnKJLirClassName(ele.getElementsByTagName("driver-class").item(0).getTextContent());

config.setJdbcUrl(ele.getElementsByTagName("jdbc-url").item(0).getTextContent());

config.setUsername(ele.getElementsByTagName("username").item(0).getTextContent());

config.setPassword(ele.getElementsByTagName("password").item(0).getTextContent());

//config.addDataSourceProperty("password", ele.getElementsByTagName("password").item(0).getTextContent());

HikariDataSource dataSource = new HikariDataSource(config);*/

DruidDataSource dataSource = new DruidDataSource();

dataSource.setDriverClassName(ele.getElementsByTagName("driver-class").item(0).getTextContent());

http:// dataSource.setUsername(ele.getElementsByTagName("username").item(0).getTextContent());

dataSource.setPassword(ele.getElementsByTagName("password").item(0).getTextContent());

dataSource.setUrl(ele.getElementsByTagName("jdbc-url").item(0).getTextContent());

dataSource.setInitialSize(5);

dataSource.setMinIdle(1);

dataSource.setMaxActive(10);// 启用监控统计功能

dataSource.setFilters("stat");//设置是否显示sql语句

targetDataSources.put(ele.getElementsByTagName("databasename").item(0).getTextContent(), dataSource);

}

return targetDataSources;

}

catch (Exception ex)

{

return null;

}

}

//访问的ip

@Value("${druid.IP}")

private String IP;

//登录名

@Value("${druid.druidLgoinName}")

private String druidLgoinName;

//密码

@Value("${druid.druidLgoinPassword}")

private String druidLgoinPassword;

@Bean

public ServletRegistrationBean DruidStatViewServle() {

//org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.

ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

//添加初始化参数：initParams

//白名单：

servletRegistrationBean.addInitParameter("allow",IP);

//IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.

// servletRegistrationBean.addInitParameter("deny", "192.168.1.73");

//登录查看信息的账号密码.

servletRegistrationBean.addInitParameter("loginUsername",druidLgoinName);

servletRegistrationBean.addInitParameter("loginPassword",druidLgoinPassword);

//是否能够重置数据.

servletRegistrationBean.addInitParameter("resetEnable","false");

return servletRegistrationBean;

}

/**

* 注册一个：filterRegistrationBean

* @return

*/

@Bean

public FilterRegistrationBean druidStatFilter2(){

FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());

//添加过滤规则.

filterRegistrationBean.addUrlPatterns("/*");

//添加不需要忽略的格式信息.

filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");

return filterRegistrationBean;

}

}

3.动态数据源，从之前已加载的数据源中选取，DynamicDataSource和DynamicDataSourceHolder配合使用

public class DynamicDataSource extends AbstractRoutingDataSource{

//数据源路由，此方用于产生要选取的数据源逻辑名称

@Override

protected Object determineCurrentLookupKey() {

//从共享线程中获取数据源名称

return DynamicDataSourceHolder.getDataSource();

}

}

public class DynamicDataSourceHolder {

/**

* 本地线程共享对象

*/

private static final ThreadLocal THREAD_LOCAL = new ThreadLocal<>();

public static void putDataSource(String name) {

THREAD_LOCAL.set(name);

}

public static String getDataSource() {

return THREAD_LOCAL.get();

}

public static void removeDataSource() {

THREAD_LOCAL.remove();

}

}

4.就是使用aop，在dao层切换数据源

@Component

@Aspect

public class DataSourceAspect {

//切换放在mapper接口的方法上，所以这里要配置AOP切面的切入点

@Pointcut("execution( * com.example.demo.dao.*.*(..))")

public void dataSourcePointCut() {

}

@Before("dataSourcePointCut()")

public void before(JoinPoint joinPoint) {

Object target = joinPoint.getTarget();

String method = joinPoint.getSignature().getName();

Class>[] clazz = target.getClass().getInterfaces();

Class>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();

try {

Method m = clazz[0].getMethod(method, parameterTypes);

//如果方法上存在切换数据源的注解，则根据注解内容进行数据源切换

if (m != null && m.isAnnotationPresent(TargetDataSource.class)) {

TargetDataSource data = m.getAnnotation(TargetDataSource.class);

String dataSourceName = data.value();

DynamicDataSourceHolder.putDataSource(dataSourceName);

} else {

}

} catch (Exception e) {

}

}

//执行完切面后，将线程共享中的数据源名称清空

@After("dataSourcePointCut()")

public void after(JoinPoint joinPoint){

DynamicDataSourceHolder.removeDataSource();

}

}

xml路径在配置文件里面配置，这样适用读写分离和多个不同的数据源，而且多个项目可以共用这一个配置

最后引用注解，需要注意的是注解的数据库名称和xml里面databasename节点是一一对应的，可以随便自定义，比如读写是一个数据库名字，这时候就可以定义成pringtest_r表示读库

至此多数据源就配置完成，至于阿里的druid下次再分享，代码都贴出来，如果大家感觉还有哪些不足的地方，欢迎指正。

{

targetDataSources.put(fields[i].getName(), getFieldValueByName(fields[i].getName(),properties));

}*/

//采用是想AbstractRoutingDataSource的对象包装多数据源

DynamicDataSource dataSource = new DynamicDataSource();

dataSource.setTargetDataSources(targetDataSources);

//设置默认的数据源，当拿不到数据源时，使用此配置

//dataSource.setDefaultTargetDataSource(properties.getUzaiTravel());

return dataSource;

}

@Bean

public PlatformTransactionManager txManager() {

return new DataSourceTransactionManager(dataSource());

}

/**

*获取数据源集合

*/

private Map

{

try {

Map

File xmlFile = new File(fiePath);

DocumentBuilderFactory builderFactory = DocumentBuilderFactory.newInstance();

DocumentBuilder builder = builderFactory.newDocumentBuilder();

Document doc = builder.parse(xmlFile);

doc.getDocumentElement().normalize();

System.out.println("Root element: " + doc.getDocumentElement().getNodeName());

NodeList nList = doc.getElementsByTagName("db");

for(int i = 0 ; i

Node node = nList.item(i);

Element ele = (Element)node;

/*HikariConfig config = new HikariConfig();

config.setDriveWnKJLirClassName(ele.getElementsByTagName("driver-class").item(0).getTextContent());

config.setJdbcUrl(ele.getElementsByTagName("jdbc-url").item(0).getTextContent());

config.setUsername(ele.getElementsByTagName("username").item(0).getTextContent());

config.setPassword(ele.getElementsByTagName("password").item(0).getTextContent());

//config.addDataSourceProperty("password", ele.getElementsByTagName("password").item(0).getTextContent());

HikariDataSource dataSource = new HikariDataSource(config);*/

DruidDataSource dataSource = new DruidDataSource();

dataSource.setDriverClassName(ele.getElementsByTagName("driver-class").item(0).getTextContent());

http:// dataSource.setUsername(ele.getElementsByTagName("username").item(0).getTextContent());

dataSource.setPassword(ele.getElementsByTagName("password").item(0).getTextContent());

dataSource.setUrl(ele.getElementsByTagName("jdbc-url").item(0).getTextContent());

dataSource.setInitialSize(5);

dataSource.setMinIdle(1);

dataSource.setMaxActive(10);// 启用监控统计功能

dataSource.setFilters("stat");//设置是否显示sql语句

targetDataSources.put(ele.getElementsByTagName("databasename").item(0).getTextContent(), dataSource);

}

return targetDataSources;

}

catch (Exception ex)

{

return null;

}

}

//访问的ip

@Value("${druid.IP}")

private String IP;

//登录名

@Value("${druid.druidLgoinName}")

private String druidLgoinName;

//密码

@Value("${druid.druidLgoinPassword}")

private String druidLgoinPassword;

@Bean

public ServletRegistrationBean DruidStatViewServle() {

//org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.

ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

//添加初始化参数：initParams

//白名单：

servletRegistrationBean.addInitParameter("allow",IP);

//IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.

// servletRegistrationBean.addInitParameter("deny", "192.168.1.73");

//登录查看信息的账号密码.

servletRegistrationBean.addInitParameter("loginUsername",druidLgoinName);

servletRegistrationBean.addInitParameter("loginPassword",druidLgoinPassword);

//是否能够重置数据.

servletRegistrationBean.addInitParameter("resetEnable","false");

return servletRegistrationBean;

}

/**

* 注册一个：filterRegistrationBean

* @return

*/

@Bean

public FilterRegistrationBean druidStatFilter2(){

FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());

//添加过滤规则.

filterRegistrationBean.addUrlPatterns("/*");

//添加不需要忽略的格式信息.

filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");

return filterRegistrationBean;

}

}

3.动态数据源，从之前已加载的数据源中选取，DynamicDataSource和DynamicDataSourceHolder配合使用

public class DynamicDataSource extends AbstractRoutingDataSource{

//数据源路由，此方用于产生要选取的数据源逻辑名称

@Override

protected Object determineCurrentLookupKey() {

//从共享线程中获取数据源名称

return DynamicDataSourceHolder.getDataSource();

}

}

public class DynamicDataSourceHolder {

/**

* 本地线程共享对象

*/

private static final ThreadLocal THREAD_LOCAL = new ThreadLocal<>();

public static void putDataSource(String name) {

THREAD_LOCAL.set(name);

}

public static String getDataSource() {

return THREAD_LOCAL.get();

}

public static void removeDataSource() {

THREAD_LOCAL.remove();

}

}

4.就是使用aop，在dao层切换数据源

@Component

@Aspect

public class DataSourceAspect {

//切换放在mapper接口的方法上，所以这里要配置AOP切面的切入点

@Pointcut("execution( * com.example.demo.dao.*.*(..))")

public void dataSourcePointCut() {

}

@Before("dataSourcePointCut()")

public void before(JoinPoint joinPoint) {

Object target = joinPoint.getTarget();

String method = joinPoint.getSignature().getName();

Class>[] clazz = target.getClass().getInterfaces();

Class>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();

try {

Method m = clazz[0].getMethod(method, parameterTypes);

//如果方法上存在切换数据源的注解，则根据注解内容进行数据源切换

if (m != null && m.isAnnotationPresent(TargetDataSource.class)) {

TargetDataSource data = m.getAnnotation(TargetDataSource.class);

String dataSourceName = data.value();

DynamicDataSourceHolder.putDataSource(dataSourceName);

} else {

}

} catch (Exception e) {

}

}

//执行完切面后，将线程共享中的数据源名称清空

@After("dataSourcePointCut()")

public void after(JoinPoint joinPoint){

DynamicDataSourceHolder.removeDataSource();

}

}

xml路径在配置文件里面配置，这样适用读写分离和多个不同的数据源，而且多个项目可以共用这一个配置

最后引用注解，需要注意的是注解的数据库名称和xml里面databasename节点是一一对应的，可以随便自定义，比如读写是一个数据库名字，这时候就可以定义成pringtest_r表示读库

至此多数据源就配置完成，至于阿里的druid下次再分享，代码都贴出来，如果大家感觉还有哪些不足的地方，欢迎指正。

Node node = nList.item(i);

Element ele = (Element)node;

/*HikariConfig config = new HikariConfig();

config.setDriveWnKJLirClassName(ele.getElementsByTagName("driver-class").item(0).getTextContent());

config.setJdbcUrl(ele.getElementsByTagName("jdbc-url").item(0).getTextContent());

config.setUsername(ele.getElementsByTagName("username").item(0).getTextContent());

config.setPassword(ele.getElementsByTagName("password").item(0).getTextContent());

//config.addDataSourceProperty("password", ele.getElementsByTagName("password").item(0).getTextContent());

HikariDataSource dataSource = new HikariDataSource(config);*/

DruidDataSource dataSource = new DruidDataSource();

dataSource.setDriverClassName(ele.getElementsByTagName("driver-class").item(0).getTextContent());

http:// dataSource.setUsername(ele.getElementsByTagName("username").item(0).getTextContent());

dataSource.setPassword(ele.getElementsByTagName("password").item(0).getTextContent());

dataSource.setUrl(ele.getElementsByTagName("jdbc-url").item(0).getTextContent());

dataSource.setInitialSize(5);

dataSource.setMinIdle(1);

dataSource.setMaxActive(10);// 启用监控统计功能

dataSource.setFilters("stat");//设置是否显示sql语句

targetDataSources.put(ele.getElementsByTagName("databasename").item(0).getTextContent(), dataSource);

}

return targetDataSources;

}

catch (Exception ex)

{

return null;

}

}

//访问的ip

@Value("${druid.IP}")

private String IP;

//登录名

@Value("${druid.druidLgoinName}")

private String druidLgoinName;

//密码

@Value("${druid.druidLgoinPassword}")

private String druidLgoinPassword;

@Bean

public ServletRegistrationBean DruidStatViewServle() {

//org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.

ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

//添加初始化参数：initParams

//白名单：

servletRegistrationBean.addInitParameter("allow",IP);

//IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.

// servletRegistrationBean.addInitParameter("deny", "192.168.1.73");

//登录查看信息的账号密码.

servletRegistrationBean.addInitParameter("loginUsername",druidLgoinName);

servletRegistrationBean.addInitParameter("loginPassword",druidLgoinPassword);

//是否能够重置数据.

servletRegistrationBean.addInitParameter("resetEnable","false");

return servletRegistrationBean;

}

/**

* 注册一个：filterRegistrationBean

* @return

*/

@Bean

public FilterRegistrationBean druidStatFilter2(){

FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());

//添加过滤规则.

filterRegistrationBean.addUrlPatterns("/*");

//添加不需要忽略的格式信息.

filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");

return filterRegistrationBean;

}

}

3.动态数据源，从之前已加载的数据源中选取，DynamicDataSource和DynamicDataSourceHolder配合使用

public class DynamicDataSource extends AbstractRoutingDataSource{

//数据源路由，此方用于产生要选取的数据源逻辑名称

@Override

protected Object determineCurrentLookupKey() {

//从共享线程中获取数据源名称

return DynamicDataSourceHolder.getDataSource();

}

}

public class DynamicDataSourceHolder {

/**

* 本地线程共享对象

*/

private static final ThreadLocal THREAD_LOCAL = new ThreadLocal<>();

public static void putDataSource(String name) {

THREAD_LOCAL.set(name);

}

public static String getDataSource() {

return THREAD_LOCAL.get();

}

public static void removeDataSource() {

THREAD_LOCAL.remove();

}

}

4.就是使用aop，在dao层切换数据源

@Component

@Aspect

public class DataSourceAspect {

//切换放在mapper接口的方法上，所以这里要配置AOP切面的切入点

@Pointcut("execution( * com.example.demo.dao.*.*(..))")

public void dataSourcePointCut() {

}

@Before("dataSourcePointCut()")

public void before(JoinPoint joinPoint) {

Object target = joinPoint.getTarget();

String method = joinPoint.getSignature().getName();

Class>[] clazz = target.getClass().getInterfaces();

Class>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();

try {

Method m = clazz[0].getMethod(method, parameterTypes);

//如果方法上存在切换数据源的注解，则根据注解内容进行数据源切换

if (m != null && m.isAnnotationPresent(TargetDataSource.class)) {

TargetDataSource data = m.getAnnotation(TargetDataSource.class);

String dataSourceName = data.value();

DynamicDataSourceHolder.putDataSource(dataSourceName);

} else {

}

} catch (Exception e) {

}

}

//执行完切面后，将线程共享中的数据源名称清空

@After("dataSourcePointCut()")

public void after(JoinPoint joinPoint){

DynamicDataSourceHolder.removeDataSource();

}

}

xml路径在配置文件里面配置，这样适用读写分离和多个不同的数据源，而且多个项目可以共用这一个配置

最后引用注解，需要注意的是注解的数据库名称和xml里面databasename节点是一一对应的，可以随便自定义，比如读写是一个数据库名字，这时候就可以定义成pringtest_r表示读库

至此多数据源就配置完成，至于阿里的druid下次再分享，代码都贴出来，如果大家感觉还有哪些不足的地方，欢迎指正。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。