赞
踩
spring: datasource: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver druid: master: url: jdbc:mysql://localhost:3306/my_test?characterEncoding=utf-8&serverTimezone=Hongkong&useSSL=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true username: root password: root slave: url: jdbc:mysql://localhost:3306/my_util?characterEncoding=utf-8&serverTimezone=Hongkong&useSSL=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true username: root password: root # 初始连接数 initialSize: 5 # 最小连接池数量 minIdle: 10 # 最大连接池数量 maxActive: 20 # 配置获取连接等待超时的时间 maxWait: 60000 # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 timeBetweenEvictionRunsMillis: 60000 # 配置一个连接在池中最小生存的时间,单位是毫秒 minEvictableIdleTimeMillis: 300000 # 配置一个连接在池中最大生存的时间,单位是毫秒 maxEvictableIdleTimeMillis: 900000 # 配置检测连接是否有效 validationQuery: SELECT 1 testWhileIdle: true testOnBorrow: false testOnReturn: false webStatFilter: enabled: true statViewServlet: enabled: true allow: # 访问地址http://localhost:7991/druid/index.html url-pattern: /druid/* login-username: admin login-password: 123456 filter: stat: enabled: true log-slow-sql: true slow-sql-millis: 1000 merge-sql: true wall: config: multi-statement-allow: true commons-log: connection-logger-name: stat,wall,log4j
public enum DataSourceType {
MASTER,
SLAVE,
}
@Component public class DataSourceProperties { @Value("${spring.datasource.druid.initialSize}") private int initialSize; @Value("${spring.datasource.druid.minIdle}") private int minIdle; @Value("${spring.datasource.druid.maxActive}") private int maxActive; @Value("${spring.datasource.druid.maxWait}") private int maxWait; @Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}") private int timeBetweenEvictionRunsMillis; @Value("${spring.datasource.druid.minEvictableIdleTimeMillis}") private int minEvictableIdleTimeMillis; @Value("${spring.datasource.druid.maxEvictableIdleTimeMillis}") private int maxEvictableIdleTimeMillis; @Value("${spring.datasource.druid.validationQuery}") private String validationQuery; @Value("${spring.datasource.druid.testWhileIdle}") private boolean testWhileIdle; @Value("${spring.datasource.druid.testOnBorrow}") private boolean testOnBorrow; @Value("${spring.datasource.druid.testOnReturn}") private boolean testOnReturn; public DruidDataSource dataSource(DruidDataSource datasource) { /* 配置初始化大小、最小、最大 */ datasource.setInitialSize(initialSize); datasource.setMaxActive(maxActive); datasource.setMinIdle(minIdle); /* 配置获取连接等待超时的时间 */ datasource.setMaxWait(maxWait); /* 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 */ datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); /* 配置一个连接在池中最小、最大生存的时间,单位是毫秒 */ datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); datasource.setMaxEvictableIdleTimeMillis(maxEvictableIdleTimeMillis); /* * 用来检测连接是否有效的sql,要求是一个查询语句,常用select 'x'。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会起作用。 */ datasource.setValidationQuery(validationQuery); /* 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。 */ datasource.setTestWhileIdle(testWhileIdle); /* 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 */ datasource.setTestOnBorrow(testOnBorrow); /* 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 */ datasource.setTestOnReturn(testOnReturn); return datasource; } }
@Slf4j public class DataSourceContextHolder { private static final ThreadLocal<DataSourceType> CONTEXT_HOLDER = new ThreadLocal<>(); /** * 修改当前数据源 */ public static void setDataSourceType(DataSourceType dsType) { log.info("切换到{}数据源", dsType); CONTEXT_HOLDER.set(dsType); } /** * 获取当前数据源 */ public static DataSourceType getDataSourceType() { return CONTEXT_HOLDER.get(); } /** * 清空数据源变量 */ public static void clearDataSourceType() { CONTEXT_HOLDER.remove(); } }
/**
* AbstractRoutingDataSource 根据用户定义的规则选择当前的数据源,
* 这样我们可以在执行查询之前,设置使用的数据源。
*
* 实现可动态路由的数据源,在每次数据库查询操作前执行。
* 它的抽象方法 determineCurrentLookupKey() 决定使用哪个数据源。
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceType();
}
}
@Slf4j @Configuration @MapperScan(basePackages = {"com.it.mapper"}, sqlSessionTemplateRef = "sqlSessionTemplate") public class DataSourceConfig { @Autowired private DataSourceProperties dataSourceProperties; @Autowired private Environment environment; @Bean @ConfigurationProperties("spring.datasource.druid.master") public DataSource master() { log.info("初始化{}数据库", DataSourceType.MASTER); DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); dataSource.setName(DataSourceType.MASTER.name()); return dataSourceProperties.dataSource(dataSource); } @Bean @ConfigurationProperties("spring.datasource.druid.slave") public DataSource slave() { log.info("初始化{}数据库", DataSourceType.SLAVE); DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); dataSource.setName(DataSourceType.SLAVE.name()); return dataSourceProperties.dataSource(dataSource); } /** * Spring 容器里实际上有2个数据源,分别是 MASTER、SLAVE * 自动注入dataSource时,spring就会疑惑到底注入哪一个bean * 注解 @Primary 就是为了解决这个问题,加了这个注解就表示优先注入这个bean * * @return DynamicDataSource */ @Bean @Primary public DynamicDataSource dynamicDataSource() { DynamicDataSource dynamicDataSource = new DynamicDataSource(); // 默认数据源 dynamicDataSource.setDefaultTargetDataSource(master()); // 配置多数据源 Map<Object, Object> dsMap = new HashMap<>(); dsMap.put(DataSourceType.MASTER, master()); dsMap.put(DataSourceType.SLAVE, slave()); dynamicDataSource.setTargetDataSources(dsMap); return dynamicDataSource; } /** * 配置SqlSessionFactory * * @return SqlSessionFactory * @throws Exception Exception */ @Bean public SqlSessionFactory sqlSessionFactory(DynamicDataSource dynamicDataSource) throws Exception { SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean(); factoryBean.setDataSource(dynamicDataSource); String locationPattern = environment.getProperty("mybatis.mapper-locations"); Assert.notNull(locationPattern, "[mybatis.mapper-locations] is null"); factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(locationPattern)); // 驼峰配置 org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration(); configuration.setMapUnderscoreToCamelCase(true); factoryBean.setConfiguration(configuration); return factoryBean.getObject(); } /** * 配置sqlSessionTemplate * * @return SqlSessionTemplate * @throws Exception Exception */ @Bean public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } /** * 配置@Transactional注解事务管理 * * @return PlatformTransactionManager */ @Bean public PlatformTransactionManager transactionManager(DynamicDataSource dynamicDataSource) { return new DataSourceTransactionManager(dynamicDataSource); } }
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {
DataSourceType value() default DataSourceType.MASTER;
}
@Aspect @Component @Order(0) public class DynamicDataSourceAspect { @Before("@annotation(com.it.annotation.DataSource)") public void before(JoinPoint point) { Class<?> className = point.getTarget().getClass(); String methodName = point.getSignature().getName(); Class<?>[] argClass = ((MethodSignature) point.getSignature()).getParameterTypes(); DataSourceType dataSource = DataSourceType.MASTER; try { Method method = className.getMethod(methodName, argClass); // 判断是否存在@DataSource注解,并获取值 if (method.isAnnotationPresent(DataSource.class)) { DataSource annotation = method.getAnnotation(DataSource.class); dataSource = annotation.value(); } } catch (Exception e) { e.printStackTrace(); } // 切换数据源 DataSourceContextHolder.setDataSourceType(dataSource); } @After("@annotation(com.it.annotation.DataSource)") public void after(JoinPoint point) { DataSourceContextHolder.clearDataSourceType(); } }
/**
* 方法执行前,自动切换到配置的数据源,
* 方法执行结束后,默认回到主数据源。
*
* 缺点:只能切换一次,多次切换数据源需要手动切换
*/
@DataSource(DataSourceType.SLAVE)
public List<Student> getAllStudent() {
return studentMapper.selectAll();
}
public List<Student> getAllStudent() {
// 手动切换数据源
DataSourceContextHolder.setDataSourceType(DataSourceType.SLAVE);
List<Student> list = studentMapper.selectAll();
// 注意:使用完不会切回主数据源,注意踩坑
DataSourceContextHolder.setDataSourceType(DataSourceType.MASTER);
return list;
}
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。