Spring Boot多数据源配置(mybatis+druid+aop)
本文将介绍Spring Boot如何整合mybatis与druid配置多数据源,以及实现自定义注解,利用aop动态切换数据源的方法。
一、主要的maven依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.4.RELEASE</version>
<relativePath/>
</parent>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<!--druid连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<!--spring aop-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
<version>2.1.4.RELEASE</version>
</dependency>
二、yml文件相关配置
配置连接池与数据源信息
spring:
#此配置让项目一启动就加载dispatcherServlet
mvc:
servlet:
load-on-startup: 1
main:
allow-bean-definition-overriding: false
datasource:
type: com.alibaba.druid.pool.DruidDataSource
#配置数据源
clickhouse-db:
url: url
username: username
password: password
driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
# 初始连接数
initial-size: 10
# 最小连接池数量
min-idle: 10
# 最大连接池数量
max-active: 100
# 配置获取连接等待超时的时间
max-wait: 6000
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
# 配置检测连接是否有效
validationQuery: SELECT 1
#申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
keepAlive: true
orcale-db:
url: url
username: username
password: password
driver-class-name: oracle.jdbc.OracleDriver
# 初始连接数
initial-size: 10
# 最小连接池数量
min-idle: 10
# 最大连接池数量
max-active: 100
# 配置获取连接等待超时的时间
max-wait: 6000
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
# 配置检测连接是否有效
validationQuery: SELECT 1 FROM DUAL
#申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
keepAlive: true
#监控信息配置
monitor:
username: 账号
password: 密码
三、启动类
在启动类上使用@EnableAutoConfiguration注解排除DataSource自动配置
@SpringBootApplication
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
@EnableCaching
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
四、自定义注解与数据源枚举
自定义数据源切换注解DataSource
package xxx.config.dataSource;
/**
* @author cf
* @Date 2021/12/7
*/
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 自定义多数据源切换注解
*
* @author cf
*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource
{
/**
* 切换数据源名称
*/
DataSourceType value() default DataSourceType.CLICKHOUSEDB;
}
自定义数据源枚举DataSourceType
package xxx.config.dataSource;
/**
* @author cf
* @Date 2021/12/7
*/
public enum DataSourceType {
/**
* ORCALE
* */
ORCALEDB,
/**
* CLICKHOUSE
* */
CLICKHOUSEDB;
/**
* 数据源
*/
private String dbType;
}
五、DatabaseContextHolder
DatabaseContextHolder是一个线程安全的DatabaseType容器,他提供设置和获取DatabaseType的方法
package xxx.config.dataSource;
/**
* @author cf
* @Date 2021/12/7
* 数据源动态切换处理
*/
public class DynamicDataSourceContextHolder {
/**
* 使用ThreadLocal维护变量,ThreadLocal为每个使用该变量的线程提供独立的变量副本,
* 所以每一个线程都可以独立地改变自己的副本,而不会影响其它线程所对应的副本。
*/
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
/**
* 设置数据源的变量
*/
public static void setDateSourceType(String dsType) {
CONTEXT_HOLDER.set(dsType);
}
/**
* 获得数据源的变量
*/
public static String getDateSourceType() {
return CONTEXT_HOLDER.get();
}
/**
* 清空数据源变量
*/
public static void clearDateSourceType() {
CONTEXT_HOLDER.remove();
}
}
六、DynamicDataSource
package xxx.config.dataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* @author cf
* @Date 2021/12/7
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
this.setDefaultTargetDataSource(defaultTargetDataSource);
this.setTargetDataSources(targetDataSources);
this.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDateSourceType();
}
}
七、DruidConfig
本文件将指定mapper.xml文件所在包,获取配置文件中多数据源信息,通过动态数据源构造SqlSessionFactory和事务管理器,以及配置druid连接池监控信息
package xxx.config.dataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
/**
* @author cf
* @Date 2021/12/7
*/
@Configuration
@MapperScan(value = "xxx.mapper")
public class DruidConfig {
/**
* 配置别名
*/
@Value("${mybatis.type-aliases-package}")
private String typeAliasesPackage;
/**
* 配置mapper的扫描,找到所有的mapper.xml映射文件
*/
@Value("${mybatis.mapper-locations}")
private String mapperLocations;
/**
* 获取druid监控信息
* */
@Value("${spring.datasource.monitor.username}")
private String username;
@Value("${spring.datasource.monitor.password}")
private String password;
@Bean
@ConfigurationProperties("spring.datasource.clickhouse-db")
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.orcale-db")
public DataSource slaveDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "dynamicDataSource")
@Primary
public DynamicDataSource dataSource() {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceType.CLICKHOUSEDB.name(), masterDataSource());
targetDataSources.put(DataSourceType.ORCALEDB.name(), slaveDataSource());
return new DynamicDataSource(masterDataSource(), targetDataSources);
}
/**
* SqlSessionFactory 配置并放入容器中
*/
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
sqlSessionFactoryBean.setTypeAliasesPackage(typeAliasesPackage);
return sqlSessionFactoryBean.getObject();
}
/**
* 事务
*/
@Bean
public PlatformTransactionManager transactionManager(@Qualifier("dynamicDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* 连接池监控
* */
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
servletRegistrationBean.addInitParameter("loginUsername", username);
servletRegistrationBean.addInitParameter("loginPassword", password);
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean druidFilter() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
八、定义aop切面
package xxx.config.dataSource;
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.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* @author cf
* @Date 2021/12/7
* 定义数据源切面
*/
@Aspect
@Order(1)
@Component
public class DataSourceAspect {
@Pointcut("@annotation(xxx.config.dataSource.DataSource)")
public void dsPointCut() {
}
@Around("dsPointCut()")
public Object around(ProceedingJoinPoint point) throws Throwable {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
DataSource dataSource = method.getAnnotation(DataSource.class);
if (null!=dataSource) {
DynamicDataSourceContextHolder.setDateSourceType(dataSource.value().name());
}
try {
return point.proceed();
} finally {
// 销毁数据源 在执行方法之后
DynamicDataSourceContextHolder.clearDateSourceType();
}
}
}
九、使用
package xxx.mapper;
import xxx.config.dataSource.DataSource;
import xxx.config.dataSource.DataSourceType;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* @author cf
* @Date 2021/12/7
*/
@Mapper
public interface testMapper {
//在需要切换数据源的地方使用自定义注解@DataSource(数据源名称)
@DataSource(DataSourceType.ORCALEDB)
List<String> testfind();
//不使用注解切换数据源将使用默认数据源(主数据源)
List<String> findAllName();
}
druld监控
URL:http://项目路径加端口/项目名称/druid/login.html
输入网址后会到监控页面的登录页属于用户名密码即可
username:yml文件中配置的账号
password:yml文件中配置的密码