本文共 14321 字,大约阅读时间需要 47 分钟。
1、修改mybatis.properties
# 主数据源,默认的spring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.url=jdbc:mysql://127.0.0.1:3306/dbspring.datasource.username=rootspring.datasource.password=123456# 初始化大小,最小,最大spring.datasource.initialSize=5spring.datasource.minIdle=5spring.datasource.maxActive=20# 配置获取连接等待超时的时间spring.datasource.maxWait=60000# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒spring.datasource.timeBetweenEvictionRunsMillis=60000# 配置一个连接在池中最小生存的时间,单位是毫秒spring.datasource.minEvictableIdleTimeMillis=300000spring.datasource.validationQuery=SELECT 1 FROM DUALspring.datasource.testWhileIdle=truespring.datasource.testOnBorrow=falsespring.datasource.testOnReturn=false# 打开PSCache,并且指定每个连接上PSCache的大小spring.datasource.poolPreparedStatements=truespring.datasource.maxPoolPreparedStatementPerConnectionSize=20# 从数据源spring.slave.type=com.alibaba.druid.pool.DruidDataSourcespring.slave.driver-class-name=com.mysql.jdbc.Driverspring.slave.url=jdbc:mysql://127.0.0.1:3308/dbspring.slave.username=rootspring.slave.password=123456spring.slave.initialSize=5spring.slave.minIdle=5spring.slave.maxActive=20# 配置获取连接等待超时的时间spring.slave.maxWait=60000# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒spring.slave.timeBetweenEvictionRunsMillis=60000# 配置一个连接在池中最小生存的时间,单位是毫秒spring.slave.minEvictableIdleTimeMillis=300000spring.slave.validationQuery=SELECT 1 FROM DUALspring.slave.testWhileIdle=truespring.slave.testOnBorrow=falsespring.slave.testOnReturn=false# 打开PSCache,并且指定每个连接上PSCache的大小spring.slave.poolPreparedStatements=truespring.slave.maxPoolPreparedStatementPerConnectionSize=20spring.read2.type=com.alibaba.druid.pool.DruidDataSourcespring.read2.driver-class-name=com.mysql.jdbc.Driverspring.read2.url=jdbc:mysql://127.0.0.1:3309/dbspring.read2.username=rootspring.read2.password=123456spring.read2.initialSize=5spring.read2.minIdle=5spring.read2.maxActive=20# 配置获取连接等待超时的时间spring.read2.maxWait=60000# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒spring.read2.timeBetweenEvictionRunsMillis=60000# 配置一个连接在池中最小生存的时间,单位是毫秒spring.read2.minEvictableIdleTimeMillis=300000spring.read2.validationQuery=SELECT 1 FROM DUALspring.read2.testWhileIdle=truespring.read2.testOnBorrow=falsespring.read2.testOnReturn=false# 打开PSCache,并且指定每个连接上PSCache的大小spring.read2.poolPreparedStatements=truespring.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=true2、创建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@Configurationpublic class DataBaseConfiguration { @Value("${spring.datasource.type}") private Class 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 ListreadDataSources(){ List 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 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 List3、创建DataSourceType,枚举区分读写库readDataSources(){ List 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. * 本地线程全局变量 */@Slf4jpublic class DataSourceContextHolder { private static final ThreadLocal5、创建MyAbstractRoutingDataSource,多数据源切换local = new ThreadLocal (); public static ThreadLocal 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(); }}
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 dataSourceType; @Value("${datasource.readSize}") private String dataSourceSize; @Resource(name = "writeDataSource") private DataSource dataSource; @Resource(name = "readDataSources") private List7、创建DataSourceTransactionManager,自定义事物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
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@Slf4jpublic 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@Slf4jpublic 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就可以了。
转载地址:http://hmpws.baihongyu.com/