1、修改mybatis.properties
- # 主数据源,默认的
- spring.datasource.driver-class-name=com.mysql.jdbc.Driver
- spring.datasource.url=jdbc:mysql://127.0.0.1:3306/db
- spring.datasource.username=root
- spring.datasource.password=123456
- # 初始化大小,最小,最大
- spring.datasource.initialSize=5
- spring.datasource.minIdle=5
- spring.datasource.maxActive=20
- # 配置获取连接等待超时的时间
- spring.datasource.maxWait=60000
- # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
- spring.datasource.timeBetweenEvictionRunsMillis=60000
- # 配置一个连接在池中最小生存的时间,单位是毫秒
- spring.datasource.minEvictableIdleTimeMillis=300000
- spring.datasource.validationQuery=SELECT 1 FROM DUAL
- spring.datasource.testWhileIdle=true
- spring.datasource.testOnBorrow=false
- spring.datasource.testOnReturn=false
- # 打开PSCache,并且指定每个连接上PSCache的大小
- spring.datasource.poolPreparedStatements=true
- spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
- # 从数据源
- spring.slave.type=com.alibaba.druid.pool.DruidDataSource
- spring.slave.driver-class-name=com.mysql.jdbc.Driver
- spring.slave.url=jdbc:mysql://127.0.0.1:3308/db
- spring.slave.username=root
- spring.slave.password=123456
- spring.slave.initialSize=5
- spring.slave.minIdle=5
- spring.slave.maxActive=20
- # 配置获取连接等待超时的时间
- spring.slave.maxWait=60000
- # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
- spring.slave.timeBetweenEvictionRunsMillis=60000
- # 配置一个连接在池中最小生存的时间,单位是毫秒
- spring.slave.minEvictableIdleTimeMillis=300000
- spring.slave.validationQuery=SELECT 1 FROM DUAL
- spring.slave.testWhileIdle=true
- spring.slave.testOnBorrow=false
- spring.slave.testOnReturn=false
- # 打开PSCache,并且指定每个连接上PSCache的大小
- spring.slave.poolPreparedStatements=true
- spring.slave.maxPoolPreparedStatementPerConnectionSize=20
- spring.read2.type=com.alibaba.druid.pool.DruidDataSource
- spring.read2.driver-class-name=com.mysql.jdbc.Driver
- spring.read2.url=jdbc:mysql://127.0.0.1:3309/db
- spring.read2.username=root
- spring.read2.password=123456
- spring.read2.initialSize=5
- spring.read2.minIdle=5
- spring.read2.maxActive=20
- # 配置获取连接等待超时的时间
- spring.read2.maxWait=60000
- # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
- spring.read2.timeBetweenEvictionRunsMillis=60000
- # 配置一个连接在池中最小生存的时间,单位是毫秒
- spring.read2.minEvictableIdleTimeMillis=300000
- spring.read2.validationQuery=SELECT 1 FROM DUAL
- spring.read2.testWhileIdle=true
- spring.read2.testOnBorrow=false
- spring.read2.testOnReturn=false
- # 打开PSCache,并且指定每个连接上PSCache的大小
- spring.read2.poolPreparedStatements=true
- spring.read2.maxPoolPreparedStatementPerConnectionSize=20
- # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
- spring.datasource.filters=stat,wall,logback
- # 通过connectProperties属性来打开mergeSql功能;慢SQL记录
- spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
- # 合并多个DruidDataSource的监控数据
- spring.datasource.useGlobalDataSourceStat=true
- # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
- spring.slave.filters=stat,wall,logback
- # 通过connectProperties属性来打开mergeSql功能;慢SQL记录
- spring.slave.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
- # 合并多个DruidDataSource的监控数据
- spring.slave.useGlobalDataSourceStat=true
- # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
- spring.read2.filters=stat,wall,logback
- # 通过connectProperties属性来打开mergeSql功能;慢SQL记录
- spring.read2.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
- # 合并多个DruidDataSource的监控数据
- spring.read2.useGlobalDataSourceStat=true
2、创建DataBaseConfiguration读取mybatis.properties配置文件
- package com.demo.mybatis;
- import lombok.extern.slf4j.Slf4j;
- import org.springframework.beans.factory.annotation.Value;
- import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
- import org.springframework.boot.context.properties.ConfigurationProperties;
- import org.springframework.context.annotation.Bean;
- import org.springframework.context.annotation.Configuration;
- import org.springframework.context.annotation.Primary;
- import javax.sql.DataSource;
- import java.util.ArrayList;
- import java.util.List;
- /**
- * Created by huguoju on 2016/12/29.
- * 数据库配置:解析properties文件
- */
- @Slf4j
- @Configuration
- public class DataBaseConfiguration {
- @Value("${spring.datasource.type}")
- private Class<? extends DataSource> dataSourceType;
- @Bean(name="writeDataSource", destroyMethod = "close", initMethod="init")
- @Primary
- @ConfigurationProperties(prefix = "spring.datasource",locations = "classpath:mybatis/mybatis.properties")
- public DataSource writeDataSource() {
- log.info("-------------------- writeDataSource init ---------------------");
- return DataSourceBuilder.create().type(dataSourceType).build();
- }
- /**
- * 有多少个从库就要配置多少个
- * @return
- */
- @Bean(name = "readDataSource1")
- @ConfigurationProperties(prefix = "spring.slave",locations = "classpath:mybatis/mybatis.properties")
- public DataSource readDataSourceOne(){
- log.info("-------------------- readDataSourceOne init ---------------------");
- return DataSourceBuilder.create().type(dataSourceType).build();
- }
- @Bean(name = "readDataSource2")
- @ConfigurationProperties(prefix = "spring.read2",locations = "classpath:mybatis/mybatis.properties")
- public DataSource readDataSourceTwo() {
- log.info("-------------------- readDataSourceTwo init ---------------------");
- return DataSourceBuilder.create().type(dataSourceType).build();
- }
- @Bean("readDataSources")
- public List<DataSource> readDataSources(){
- List<DataSource> dataSources=new ArrayList<>();
- dataSources.add(readDataSourceOne());
- dataSources.add(readDataSourceTwo());
- return dataSources;
- }
- }
最新版本的springboot的@ConfigurationProperties去掉了locations属性,改用@PropertySource指定其他的配置文件,@PropertySource不能作用在方法上,要写在类上
添加引用:
import org.springframework.context.annotation.PropertySource;
- package com.demo.mybatis;
- import lombok.extern.slf4j.Slf4j;
- import org.springframework.beans.factory.annotation.Value;
- import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
- import org.springframework.boot.context.properties.ConfigurationProperties;
- import org.springframework.context.annotation.Bean;
- import org.springframework.context.annotation.Configuration;
- import org.springframework.context.annotation.Primary;
- import org.springframework.context.annotation.PropertySource;
- import javax.sql.DataSource;
- import java.util.ArrayList;
- import java.util.List;
- /**
- * Created by huguoju on 2016/12/29.
- * 数据库配置:解析properties文件
- */
- @Slf4j
- @Configuration
- @PropertySource("classpath:mybatis/mybatis.properties")
- public class DataBaseConfiguration {
- @Value("${spring.datasource.type}")
- private Class<? extends DataSource> dataSourceType;
- @Bean(name="writeDataSource", destroyMethod = "close", initMethod="init")
- @Primary
- @ConfigurationProperties(prefix = "spring.datasource")
- public DataSource writeDataSource() {
- log.info("-------------------- writeDataSource init ---------------------");
- return DataSourceBuilder.create().type(dataSourceType).build();
- }
- /**
- * 有多少个从库就要配置多少个
- * @return
- */
- @Bean(name = "readDataSource1")
- @ConfigurationProperties(prefix = "spring.slave")
- public DataSource readDataSourceOne(){
- log.info("-------------------- readDataSourceOne init ---------------------");
- return DataSourceBuilder.create().type(dataSourceType).build();
- }
- @Bean(name = "readDataSource2")
- @ConfigurationProperties(prefix = "spring.read2")
- public DataSource readDataSourceTwo() {
- log.info("-------------------- readDataSourceTwo init ---------------------");
- return DataSourceBuilder.create().type(dataSourceType).build();
- }
- @Bean("readDataSources")
- public List<DataSource> readDataSources(){
- List<DataSource> dataSources=new ArrayList<>();
- dataSources.add(readDataSourceOne());
- dataSources.add(readDataSourceTwo());
- return dataSources;
- }
- }
- package com.demo.mybatis;
- import lombok.Getter;
- /**
- * Created by huguoju on 2016/12/29.
- */
- public enum DataSourceType {
- read("read", "从库"),
- write("write", "主库");
- @Getter
- private String type;
- @Getter
- private String name;
- DataSourceType(String type, String name) {
- this.type = type;
- this.name = name;
- }
- }
4、创建DataSourceContextHolder,本地线程全局变量
- package com.demo.mybatis;
- import lombok.extern.slf4j.Slf4j;
- /**
- * Created by huguoju on 2016/12/29.
- * 本地线程全局变量
- */
- @Slf4j
- public class DataSourceContextHolder {
- private static final ThreadLocal<String> local = new ThreadLocal<String>();
- public static ThreadLocal<String> getLocal() {
- return local;
- }
- /**
- * 读可能是多个库
- */
- public static void read() {
- local.set(DataSourceType.read.getType());
- }
- /**
- * 写只有一个库
- */
- public static void write() {
- log.debug("writewritewrite");
- local.set(DataSourceType.write.getType());
- }
- public static String getJdbcType() {
- return local.get();
- }
- }
5、创建MyAbstractRoutingDataSource,多数据源切换
- package com.demo.mybatis;
- import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
- import java.util.concurrent.atomic.AtomicInteger;
- /**
- * Created by huguoju on 2016/12/29.
- * 多数据源切换
- */
- public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
- private final int dataSourceNumber;
- private AtomicInteger count = new AtomicInteger(0);
- public MyAbstractRoutingDataSource(int dataSourceNumber) {
- this.dataSourceNumber = dataSourceNumber;
- }
- @Override
- protected Object determineCurrentLookupKey() {
- String typeKey = DataSourceContextHolder.getJdbcType();
- if (typeKey.equals(DataSourceType.write.getType()))
- return DataSourceType.write.getType();
- // 读 简单负载均衡
- int number = count.getAndAdd(1);
- int lookupKey = number % dataSourceNumber;
- return new Integer(lookupKey);
- }
- }
6、创建MybatisConfiguration或者将MyBatisConfig修改,配置mybatis
- package com.demo.mybatis;
- import lombok.extern.slf4j.Slf4j;
- import org.apache.ibatis.session.SqlSessionFactory;
- import org.mybatis.spring.SqlSessionFactoryBean;
- import org.mybatis.spring.annotation.MapperScan;
- import org.springframework.beans.factory.annotation.Value;
- import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
- import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
- import org.springframework.context.annotation.*;
- import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
- import org.springframework.transaction.annotation.EnableTransactionManagement;
- import javax.annotation.Resource;
- import javax.sql.DataSource;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- /**
- * Created by huguoju on 2016/12/28.
- * 配置mybatis
- */
- @Slf4j
- @Configuration
- @ConditionalOnClass({EnableTransactionManagement.class})
- @Import({ DataBaseConfiguration.class})
- @MapperScan(basePackages={"com.demo.mapper"})
- public class MybatisConfiguration {
- @Value("${spring.datasource.type}")
- private Class<? extends DataSource> dataSourceType;
- @Value("${datasource.readSize}")
- private String dataSourceSize;
- @Resource(name = "writeDataSource")
- private DataSource dataSource;
- @Resource(name = "readDataSources")
- private List<DataSource> readDataSources;
- @Bean
- @ConditionalOnMissingBean
- public SqlSessionFactory sqlSessionFactory() throws Exception {
- SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
- sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
- sqlSessionFactoryBean.setTypeAliasesPackage("com.demo.model");
- sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
- return sqlSessionFactoryBean.getObject();
- }
- /**
- * 有多少个数据源就要配置多少个bean
- * @return
- */
- @Bean
- public AbstractRoutingDataSource roundRobinDataSouceProxy() {
- int size = Integer.parseInt(dataSourceSize);
- MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size);
- Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
- // DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
- // 写
- targetDataSources.put(DataSourceType.write.getType(),dataSource);
- // targetDataSources.put(DataSourceType.read.getType(),readDataSource);
- //多个读数据库时
- for (int i = 0; i < size; i++) {
- targetDataSources.put(i, readDataSources.get(i));
- }
- proxy.setDefaultTargetDataSource(dataSource);
- proxy.setTargetDataSources(targetDataSources);
- return proxy;
- }
- }
7、创建DataSourceTransactionManager,自定义事物
- package com.demo.mybatis;
- import lombok.extern.slf4j.Slf4j;
- import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
- import org.springframework.context.annotation.Bean;
- import org.springframework.context.annotation.Configuration;
- import org.springframework.transaction.annotation.EnableTransactionManagement;
- import javax.annotation.Resource;
- import javax.sql.DataSource;
- /**
- * 自定义事务
- * Created by huguoju on 2016/12/29.
- */
- @Configuration
- @EnableTransactionManagement
- @Slf4j
- public class DataSourceTransactionManager extends DataSourceTransactionManagerAutoConfiguration {
- /**
- * 自定义事务
- * MyBatis自动参与到spring事务管理中,无需额外配置,只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可,否则事务管理会不起作用。
- * @return
- */
- @Resource(name = "writeDataSource")
- private DataSource dataSource;
- @Bean(name = "transactionManager")
- public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManagers() {
- log.info("-------------------- transactionManager init ---------------------");
- return new org.springframework.jdbc.datasource.DataSourceTransactionManager(dataSource);
- }
- }
8、创建DataSourceAop,切换数据源
- package com.demo.mybatis;
- import lombok.extern.slf4j.Slf4j;
- import org.aspectj.lang.annotation.Aspect;
- import org.aspectj.lang.annotation.Before;
- import org.springframework.stereotype.Component;
- /**
- * Created by huguoju on 2016/12/29.
- * 拦截设置本地线程变量
- */
- @Aspect
- @Component
- @Slf4j
- public class DataSourceAop {
- @Before("execution(* com.demo.mapper..*.select*(..)) || execution(* com.demo.mapper..*.get*(..))")
- public void setReadDataSourceType() {
- DataSourceContextHolder.read();
- log.info("dataSource切换到:Read");
- }
- @Before("execution(* com.demo.mapper..*.insert*(..)) || execution(* com.demo.mapper..*.update*(..))")
- public void setWriteDataSourceType() {
- DataSourceContextHolder.write();
- log.info("dataSource切换到:write");
- }
- }
以上就完成了,测试类省略了,测试类只要一个select和insert的sql语句就可以,在DataSourceAop的切换方法里打断点,就可以看出来切换数据了,
druid监控全部库, 只要设置useGlobalDataSourceStat=true就可以了。