spring boot多数据源动态切换及druid-sql监控

最新推荐文章于 2024-05-10 19:47:22 发布

Nestor_

最新推荐文章于 2024-05-10 19:47:22 发布

阅读量457

点赞数

分类专栏：知识总结文章标签： springboot druid

本文链接：https://blog.csdn.net/weixin_43122585/article/details/98480819

版权

知识总结专栏收录该内容

8 篇文章 0 订阅

订阅专栏

spring boot多数据源动态切换及druid-sql监控

首先来讲一下我的设计思路：
1.数据库配置依然在yml文件中，但是由于要实现多数据源切换我就把sql监控的公用配置放在了代码层面
2.通过注解的形式实现数据库切换

一、yml多数据源配置

dynamic:
  datasource:
    #新增
    db-001:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost01:3306/rc-ict?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: 123456
    #修改
    db-002:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost02:3306/rc-ict?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: 123456
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    #查询
    druid:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost03:3306/rc-ict?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: 123456

这里我们配置了默认的查询数据库和用于编辑可切换的数据源

二、java多数据源配置

我们把druid的一些公用配置放在这里每一个数据源都用到了相同配置，这样就不用在yml文件中重复配置

import java.util.LinkedList;
import java.util.List;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;

import lombok.Data;

/**
 * 数据库连接参数及druid配置
 *
 * @since 1.0.0
 */
@Data
public class DataSourceProperties {
	/**
	 * 从配置文件中获取
	 */
	private String driverClassName;
	private String url;
	private String username;
	private String password;

	/**
	 * druid默认参数
	 */
	private int initialSize = 2;
	private int maxActive = 10;
	private int minIdle = -1;
	private long maxWait = 60 * 1000L;
	private long timeBetweenEvictionRunsMillis = 60 * 1000L;
	private long minEvictableIdleTimeMillis = 1000L * 60L * 30L;
	private long maxEvictableIdleTimeMillis = 1000L * 60L * 60L * 7;
	private String validationQuery = "select 1";
	private int validationQueryTimeout = -1;
	private boolean testOnBorrow = false;
	private boolean testOnReturn = false;
	private boolean testWhileIdle = true;
	private boolean poolPreparedStatements = false;
	private int maxOpenPreparedStatements = -1;
	private boolean sharePreparedStatements = false;
	private String filters = "stat,wall";

	/**
	 * durid过滤器
	 */
	public List<Filter> duridFilters() {
		StatFilter statFilter = new StatFilter();
		statFilter.setLogSlowSql(true);
		statFilter.setSlowSqlMillis(1000);
		statFilter.setMergeSql(false);

		WallFilter wallFilter = new WallFilter();
		WallConfig wallConfig = new WallConfig();
		wallConfig.setMultiStatementAllow(true);
		wallFilter.setConfig(wallConfig);

		List<Filter> filters = new LinkedList<>();
		filters.add(statFilter);
		filters.add(wallFilter);

		return filters;
	}
}

多数据源配置类：

import java.util.Map;

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

import lombok.Data;

/**
 * 多数据源参数配置
 *
 * @since 1.0.0
 */
@Data
@ConfigurationProperties(prefix = "dynamic")
public class DynamicDataSourceProperties {
	private Map<String, DataSourceProperties> datasource;
}

三、java多数据源切换核心代码

获取当前事务的线程

import java.util.ArrayDeque;
import java.util.Deque;

/**
 * 多数据源上下文
 */
public class DynamicContextHolder {

	/**
	 * 当在方法或者类上使用DataSource注解时，CONTEXT_HOLDER为当前事务的线程
	 */
	private static final ThreadLocal<Deque<String>> CONTEXT_HOLDER = ThreadLocal.withInitial(ArrayDeque::new);

	/**
	 * 获得当前线程数据源
	 *
	 * @return 数据源名称
	 */
	public static String peek() {
		return CONTEXT_HOLDER.get().peek();
	}

	/**
	 * 设置当前线程数据源
	 *
	 * @param dataSource 数据源名称
	 */
	public static void push(String dataSource) {
		CONTEXT_HOLDER.get().push(dataSource);
	}

	/**
	 * 清空当前线程数据源
	 */
	public static void poll() {
		Deque<String> deque = CONTEXT_HOLDER.get();
		deque.poll();
		if (deque.isEmpty()) {
			CONTEXT_HOLDER.remove();
		}
	}
}

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

/**
 * 由于AbstractRoutingDataSource继承了{@link javax.sql.DataSource}
 * <p>
 * 可以把DynamicDataSource当作配置类来配置
 *
 * @since 1.0.0
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

	/**
	 * 确定当前查找键。通常会实现这一点以检查线程绑定的事务上下文。
	 * <p>
	 * 允许任意键。返回的密钥需要匹配存储的查找密钥类型，由{@link #resolveSpecifiedLookupKey}方法解析
	 */
	@Override
	protected Object determineCurrentLookupKey() {
		return DynamicContextHolder.peek();
	}
}

设置数据库及druid参数

import java.util.HashMap;
import java.util.Map;

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 com.alibaba.druid.pool.DruidDataSource;
import com.study.commons.dynamic.datasource.properties.DataSourceProperties;
import com.study.commons.dynamic.datasource.properties.DynamicDataSourceProperties;

/**
 * 配置多数据源
 *
 * @since 1.0.0
 */
@Configuration
@EnableConfigurationProperties(DynamicDataSourceProperties.class)
public class DynamicDataSourceConfig {

	@Autowired
	private DynamicDataSourceProperties properties;

	@Bean
	@ConfigurationProperties(prefix = "spring.datasource.druid")
	public DataSourceProperties dataSourceProperties() {
		return new DataSourceProperties();
	}

	@Bean
	public DynamicDataSource dynamicDataSource(DataSourceProperties dataSourceProperties) {
		DynamicDataSource dynamicDataSource = new DynamicDataSource();

		// 默认数据源即不适用DataSource注解时使用该数据库配置
		DruidDataSource defaultDataSource = DynamicDataSourceFactory.buildDruidDataSource(dataSourceProperties);
		dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);

		// 动态数据源即使用DataSource注解时使用该数据库配置
		dynamicDataSource.setTargetDataSources(getDynamicDataSource());

		return dynamicDataSource;
	}

	/**
	 * 生成多数据源参数
	 * 
	 * @return 多数据源map
	 */
	private Map<Object, Object> getDynamicDataSource() {
		Map<String, DataSourceProperties> dataSourcePropertiesMap = properties.getDatasource();
		Map<Object, Object> targetDataSources = new HashMap<>(dataSourcePropertiesMap.size());
		dataSourcePropertiesMap.forEach((k, v) -> {
			DruidDataSource druidDataSource = DynamicDataSourceFactory.buildDruidDataSource(v);
			targetDataSources.put(k, druidDataSource);
		});

		return targetDataSources;
	}
}

参数设置

import java.sql.SQLException;

import com.alibaba.druid.pool.DruidDataSource;
import com.study.commons.dynamic.datasource.properties.DataSourceProperties;

/**
 * DruidDataSource
 *
 * @since 1.0.0
 */
public class DynamicDataSourceFactory {

	public static DruidDataSource buildDruidDataSource(DataSourceProperties properties) {
		DruidDataSource druidDataSource = new DruidDataSource();
		druidDataSource.setDriverClassName(properties.getDriverClassName());
		druidDataSource.setUrl(properties.getUrl());
		druidDataSource.setUsername(properties.getUsername());
		druidDataSource.setPassword(properties.getPassword());

		druidDataSource.setInitialSize(properties.getInitialSize());
		druidDataSource.setMaxActive(properties.getMaxActive());
		druidDataSource.setMinIdle(properties.getMinIdle());
		druidDataSource.setMaxWait(properties.getMaxWait());
		druidDataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
		druidDataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
		druidDataSource.setMaxEvictableIdleTimeMillis(properties.getMaxEvictableIdleTimeMillis());
		druidDataSource.setValidationQuery(properties.getValidationQuery());
		druidDataSource.setValidationQueryTimeout(properties.getValidationQueryTimeout());
		druidDataSource.setTestOnBorrow(properties.isTestOnBorrow());
		druidDataSource.setTestOnReturn(properties.isTestOnReturn());
		druidDataSource.setTestWhileIdle(properties.isTestWhileIdle());
		druidDataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements());
		druidDataSource.setMaxOpenPreparedStatements(properties.getMaxOpenPreparedStatements());
		druidDataSource.setSharePreparedStatements(properties.isSharePreparedStatements());

		try {
			druidDataSource.setFilters(properties.getFilters());
			druidDataSource.setProxyFilters(properties.duridFilters());
			druidDataSource.init();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return druidDataSource;
	}
}

四、java多数据源AOP

import java.lang.reflect.Method;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import com.study.commons.dynamic.datasource.annotation.DataSource;
import com.study.commons.dynamic.datasource.config.DynamicContextHolder;

/**
 * 多数据源，切面处理类
 *
 * @since 1.0.0
 */
@Aspect
@Component
@Order(Ordered.HIGHEST_PRECEDENCE) // 设置bean加载的优先级
public class DataSourceAspect {
	protected Logger logger = LoggerFactory.getLogger(getClass());

	/**
	 * 设置注解切入点
	 */
	@Pointcut("@annotation(com.study.commons.dynamic.datasource.annotation.DataSource) " +
			"|| @within(com.study.commons.dynamic.datasource.annotation.DataSource)")
	public void dataSourcePointCut() {}

	@Around("dataSourcePointCut()")
	public Object around(ProceedingJoinPoint point) throws Throwable {
		MethodSignature signature = (MethodSignature) point.getSignature();
		Class targetClass = point.getTarget().getClass();
		Method method = signature.getMethod();

		DataSource targetDataSource = (DataSource) targetClass.getAnnotation(DataSource.class);
		DataSource methodDataSource = method.getAnnotation(DataSource.class);
		if (targetDataSource != null || methodDataSource != null) {
			String value;
			if (methodDataSource != null) {
				value = methodDataSource.value();
			} else {
				value = targetDataSource.value();
			}

			DynamicContextHolder.push(value);
			logger.debug("set datasource is {}", value);
		}

		try {
			return point.proceed();
		} finally {
			DynamicContextHolder.poll();
			logger.debug("clean datasource");
		}
	}
}

设置注解

import java.lang.annotation.*;

/**
 * 多数据源注解
 * 
 * @since 1.0.0
 */

// 注解的作用目标
@Target({ElementType.METHOD, ElementType.TYPE}) // 注解的作用域
// 指明修饰的注解的生存周期，即会保留到哪个阶段
@Retention(RetentionPolicy.RUNTIME)
// 指明修饰的注解，可以被例如javadoc此类的工具文档化，只负责标记，没有成员取值
@Documented
// 允许子类继承父类中的注解
@Inherited
public @interface DataSource {
	String value() default "";
}

五、效果展示

注解@Target已经可以看出注解的作用域

	@Override
	@DataSource("db-002")
	public void updateVersion(SysVersionEntity version) {
		this.updateById(version);
	}

2019-08-05 11:41:34.222 DEBUG 14156 --- [io-8080-exec-17] c.s.i.c.d.d.aspect.DataSourceAspect      : set datasource is db-002
2019-08-05 11:41:34.223 DEBUG 14156 --- [io-8080-exec-17] c.s.i.m.sys.SysVersionDao.updateById     : ==>  Preparing: UPDATE sys_version SET VERSION_CODE=?, VERSION_NAME=?, VERSION_TYPE=?, VERSION_STATE=?, FILE_PATH=?, UPGRADE_=? WHERE VERSION_ID=? 
2019-08-05 11:41:34.224 DEBUG 14156 --- [io-8080-exec-17] c.s.i.m.sys.SysVersionDao.updateById     : ==> Parameters: 测试数据库切换(String), 123(String), 0(String), 0(String), /data(String), 0(String), 55(Integer)
2019-08-05 11:41:34.264 DEBUG 14156 --- [io-8080-exec-17] c.s.i.m.sys.SysVersionDao.updateById     : <==    Updates: 1
2019-08-05 11:41:34.264 DEBUG 14156 --- [io-8080-exec-17] c.s.i.c.d.d.aspect.DataSourceAspect      : clean datasource

五、SQL监控

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

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

/**
 * 设置sql监控过滤器
 * 
 * @version V1.0
 */
@WebFilter(
    filterName = "druidWebStatFilter",
    urlPatterns = "/*",
    initParams = {
        @WebInitParam(name = "exclusions", value = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源
    }
)
public class DruidWebStatFilter extends WebStatFilter {}

import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;

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

/**
 * @author wusj
 * @version V1.0
 */
@WebServlet(
	urlPatterns = "/druid/*",
	initParams = {
			@WebInitParam(name = "allow", value = ""),// IP白名单(没有配置或者为空，则允许所有访问)
			@WebInitParam(name = "deny", value = ""),// IP黑名单 (存在共同时，deny优先于allow)
			@WebInitParam(name = "loginUsername", value = "admin"),// 用户名
			@WebInitParam(name = "loginPassword", value = "admin"),// 密码
			@WebInitParam(name = "resetEnable", value = "false")// 禁用HTML页面上的“Reset All”功能
	}
)
public class DruidStatViewServlet extends StatViewServlet {}

在启动类中添加@ServletComponentScan注解是sql监控生效

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;

/*
 * 在Spring Boot启动类上使用@ServletComponentScan注解后<p>
 * 使用@WebServlet、@WebFilter、@WebListener标记的Servlet、Filter、Listener就可以自动注册到Servlet容器中
 */
@ServletComponentScan
@SpringBootApplication
public class DemoApplication {
	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
	}
}

Nestor_

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
spring boot多数据源动态切换及druid-sql监控

spring boot多数据源动态切换及druid-sql监控首先来讲一下我的设计思路：1.数据库配置依然在yml文件中，但是由于要实现多数据源切换我就把sql监控的公用配置放在了代码层面2.通过注解的形式实现数据库切换一、yml多数据源配置dynamic: datasource: #新增 db-001: driver-class-name: com.my...
复制链接

扫一扫