springboot整合druid连接池、mybatis实现多数据源动态切换
demo环境：
JDK 1.8 ，Spring boot 1.5.14
一 整合durid
1.添加druid连接池maven依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.29</version> </dependency>
2.配置多数据源Druid
- 2.1 application.yml关于数据源配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
master:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/db_test?characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false
username: root
password: root
# 连接池初始化大小
initialSize: 5
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
minEvictableIdleTimeMillis: 30000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache，并且指定每个连接上PSCache的大小
poolPreparedStatements: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
useGlobalDataSourceStat: true
filters: stat,wall,log4j
slave:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/db_slave?characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false
username: root
password: root
# 连接池初始化大小
initialSize: 5
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
minEvictableIdleTimeMillis: 30000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache，并且指定每个连接上PSCache的大小
poolPreparedStatements: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
useGlobalDataSourceStat: true
filters: stat,wall,log4
PS:若不配置filters在druid的SQL监控无法正常打印(如果选择的maven是直接继承springboot的druid-spring-boot-starter就不需要配置)
- 2.2 多数据源Bean的配置以及动态数据源的实现
@Configuration
@EnableTransactionManagement
public class DataSourceConfig {
@Value("${spring.datasource.type}")
private Class<? extends DataSource> dataSourceType;
@Bean(name = "dbMasterDataSource")
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource dbTestDataSource(){
return DataSourceBuilder.create().type(dataSourceType).build();
}
@Bean(name = "dbSlaveDataSource")
@ConfigurationProperties(prefix = "spring.datasource.slave")
public DataSource dbSlaveDataSource(){
return DataSourceBuilder.create().type(dataSourceType).build();
}
@Bean(name = "dataSource")
@Primary
public AbstractRoutingDataSource dataSource(){
MasterSlaveRoutingDataSource masterSlaveRoutingDataSource = new MasterSlaveRoutingDataSource();
Map<Object, Object> targetDataResources = new HashMap<>();
// targetDataResources.put(DbContextHolder.DbType.MASTER, dbTestDataSource());
// targetDataResources.put(DbContextHolder.DbType.SLAVE, dbSlaveDataSource());
targetDataResources.put(DbEnum.MASTER, dbTestDataSource());
targetDataResources.put(DbEnum.SLAVE, dbSlaveDataSource());
masterSlaveRoutingDataSource.setDefaultTargetDataSource(dbSlaveDataSource());
masterSlaveRoutingDataSource.setTargetDataSources(targetDataResources);
masterSlaveRoutingDataSource.afterPropertiesSet();
return masterSlaveRoutingDataSource;
}
}
@Bean(name = "***")配置了两个数据源，将AbstractRoutingDataSource永@Primary注解标注，表示这个动态数据源是首选数据源
- 2.3 druid页面监控台配置
@WebFilter(filterName = "druidWebStatFilter", urlPatterns = "/*", initParams = {@WebInitParam(name = "exclusions", value = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")})
public class DruidStatFilter extends WebStatFilter {
}
3.整合mybatis
- 3.1 mybatis配置文件
mybatis: mapper-locations: classpath*:/mapper/*.xml check-config-location: true type-aliases-package: com.springboot.datasource.entity config-location: classpath:mybatis-config.xml pagehelper: auto-dialect: true close-conn: false reasonable: true helperDialect: mysql supportMethodsArguments: true params: count=countSql
4.切面注解配置
@Target({ElementType.TYPE,ElementType.METHOD}) @Retention(RetentionPolicy.RUNTIME) public @interface DynamicDb { String value() default DbEnum.MASTER; }
@Aspect @Component public class DbAspect implements Ordered { private static final Logger logger = LoggerFactory.getLogger(DbAspect.class); @Before("@annotation(masterDb)") public void beforeSwitch(JoinPoint joinPoint, MasterDb masterDb){ System.out.println("进入之前"); } @Around("@annotation(masterDb)") public Object proceed(ProceedingJoinPoint proceedingJoinPoint, MasterDb masterDb) throws Throwable{ try { logger.info("set database connection to db_test only"); DbContextHolder.setDbType(DbContextHolder.DbType.MASTER); Object result = proceedingJoinPoint.proceed(); return result; }finally { DbContextHolder.clearDbType(); logger.info("restore database connection"); } } @Around("@annotation(slaveDb)") public Object proceed(ProceedingJoinPoint proceedingJoinPoint, SlaveDb slaveDb) throws Throwable{ try { logger.info("set database connection to db_test only"); DbContextHolder.setDbType(DbContextHolder.DbType.SLAVE); Object result = proceedingJoinPoint.proceed(); return result; }finally { DbContextHolder.clearDbType(); logger.info("restore database connection"); } } @Around("@annotation(dynamicDb)") public Object proceed(ProceedingJoinPoint proceedingJoinPoint, DynamicDb dynamicDb) throws Throwable{ try { logger.info("set database connection to {} only",dynamicDb.value()); DbContextHolder.setDb(dynamicDb.value()); Object result = proceedingJoinPoint.proceed(); return result; }finally { DbContextHolder.clearDb(); logger.info("restore database connection"); } } @Override public int getOrder() { return 0; } }
5. 启动主程序
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@ServletComponentScan
@EnableAspectJAutoProxy
public class BootDatasourceApplication {
public static void main(String[] args) {
SpringApplication.run(BootDatasourceApplication.class, args);
}
}
按网上的需要将@MapperScan 加入到BootDatasourceApplication 类用来扫描mapper，demo中在Dao层用@Repository 注解，这里没添加@MapperScan 也没有报错。
