springboot、mysql实现读写分离
1、首先在springcloud config中配置读写数据库
mysql: datasource: readSize: 1 #读库个数 type: com.alibaba.druid.pool.DruidDataSource write: url: jdbc:mysql://200.200.4.34:3306/quote?characterEncoding=utf8&useSSL=false username: root password: 123123 driver-class-name: com.mysql.cj.jdbc.Driver minIdle: 5 maxActive: 100 initialSize: 10 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: select 'x' testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 50 removeAbandoned: true filters: stat read01: url: jdbc:mysql://200.200.4.34:3306/quote?characterEncoding=utf8&useSSL=false username: root password: 123123 driver-class-name: com.mysql.cj.jdbc.Driver minIdle: 5 maxActive: 100 initialSize: 10 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: select 'x' testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 50 removeAbandoned: true filters: stat read02: url: jdbc:mysql://200.200.4.34:3306/quote?characterEncoding=utf8&useSSL=false username: root password: 123123 driver-class-name: com.mysql.cj.jdbc.Driver minIdle: 5 maxActive: 100 initialSize: 10 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: select 'x' testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 50 removeAbandoned: true filters: stat
2、编写读库注解
import java.lang.annotation.Documented; import java.lang.annotation.ElementType; import java.lang.annotation.Inherited; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Target({ElementType.METHOD, ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) @Inherited @Documented public [@interface](https://my.oschina.net/u/996807) ReadDataSource { } 3、增加数据源初始化配置
import java.util.ArrayList;import java.util.List;import javax.sql.DataSource;import org.slf4j.Logger;import org.slf4j.LoggerFactory;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;/** * name：DataSourceConfiguration * * @author：lipeng * @data：2018年6月27日 下午5:55:35 * @version 1.0 */@Configuration public class DataSourceConfiguration { private static Logger log = LoggerFactory.getLogger(DataSourceConfiguration.class); @Value("${mysql.datasource.type}") private Class dataSourceType; /** * 写库 数据源配置 * @return */ @Bean(name = "writeDataSource") @Primary @ConfigurationProperties(prefix = "mysql.datasource.write") public DataSource writeDataSource() { log.info("-------------------- writeDataSource init ---------------------"); return DataSourceBuilder.create().type(dataSourceType).build(); } /** * 有多少个从库就要配置多少个 * @return */ @Bean(name = "readDataSource01") @ConfigurationProperties(prefix = "mysql.datasource.read01") public DataSource readDataSourceOne() { log.info("-------------------- read01 DataSourceOne init ---------------------"); return DataSourceBuilder.create().type(dataSourceType).build(); } @Bean(name = "readDataSource02") @ConfigurationProperties(prefix = "mysql.datasource.read02") public DataSource readDataSourceTwo() { log.info("-------------------- read01 DataSourceOne init ---------------------"); return DataSourceBuilder.create().type(dataSourceType).build(); } @Bean("readDataSources") public List readDataSources(){ List dataSources=new ArrayList<>(); dataSources.add(readDataSourceOne()); dataSources.add(readDataSourceTwo()); return dataSources; }} 4、增加主从配置常量
/** * name：DataSourceType * * @author：lipeng * @data：2018年6月28日 上午9:25:44 * @version 1.0 */public enum DataSourceType { read("read", "从库"), write("write", "主库"); private String type; private String name; DataSourceType(String type, String name) { this.type = type; this.name = name; } public String getType() { return type; } public void setType(String type) { this.type = type; } public String getName() { return name; } public void setName(String name) { this.name = name; } } 5、事务内读写配置
由于涉及到事务处理，可能会遇到事务中同时用到读库和写库，可能会有延时造成脏读，所以增加了线程变量设置，来保证一个事务内读写都是同一个库
/** * name：DataSourceContextHolder * * @author：lipeng * @data：2018年6月27日 下午5:57:39 * @version 1.0 */public class DataSourceContextHolder { private static Logger log = LoggerFactory.getLogger(DataSourceContextHolder.class); //线程本地环境 private static final ThreadLocal local = new ThreadLocal (); public static ThreadLocal getLocal() { return local; } /** * 读库 */ public static void setRead() { local.set(DataSourceType.read.getType()); log.info("数据库切换到读库..."); } /** * 写库 */ public static void setWrite() { local.set(DataSourceType.write.getType()); log.info("数据库切换到写库..."); } public static String getReadOrWrite() { return local.get(); } public static void clear(){ local.remove(); } } 如果在注解在service层并且声明式事务也在service层，这个得保证拦截器优先级在声明式事务前面
/** * name：DataSourceAopInService * 在service层觉得数据源 * 必须在事务AOP之前执行，所以实现Ordered,order的值越小，越先执行 * 如果一旦开始切换到写库，则之后的读都会走写库 * * @author：lipeng * @data：2018年6月27日 下午5:59:17 * @version 1.0 */@Aspect @EnableAspectJAutoProxy(exposeProxy=true,proxyTargetClass=true) @Component public class DataSourceAopInService implements PriorityOrdered{ private static Logger log = LoggerFactory.getLogger(DataSourceAopInService.class); @Before("@annotation(com.sangfor.quote.datasource.annotation.ReadDataSource) ") public void setReadDataSourceType() { //如果已经开启写事务了，那之后的所有读都从写库读 if(!DataSourceType.write.getType().equals(DataSourceContextHolder.getReadOrWrite())){ DataSourceContextHolder.setRead(); } } @Before("@annotation(com.sangfor.quote.datasource.annotation.WriteDataSource) ") public void setWriteDataSourceType() { DataSourceContextHolder.setWrite(); } @Override public int getOrder() { /** * 值越小，越优先执行 * 要优于事务的执行 * 在启动类中加上了@EnableTransactionManagement(order = 10) */ return 1; } } 并且在启动类或者配置类中增加注解order配置 @EnableTransactionManagement(order = 10)
6、增加mybatis相关配置类
mybatis配置
@Configuration@AutoConfigureAfter(DataSourceConfiguration.class)@MapperScan(basePackages = "com.sangfor.springboot")public class MybatisConfiguration { private static Logger log = LoggerFactory.getLogger(MybatisConfiguration.class); @Value("${mysql.datasource.readSize}") private String readDataSourceSize; @Autowired @Qualifier("writeDataSource") private DataSource writeDataSource; @Autowired @Qualifier("readDataSources") private List readDataSources; @Bean @ConditionalOnMissingBean public SqlSessionFactory sqlSessionFactory() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy()); sqlSessionFactoryBean.setTypeAliasesPackage("com.sangfor.quote.model"); //设置mapper.xml文件所在位置 Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"); sqlSessionFactoryBean.setMapperLocations(resources); sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true); return sqlSessionFactoryBean.getObject(); } /** * 有多少个数据源就要配置多少个bean * * @return */ @Bean public AbstractRoutingDataSource roundRobinDataSouceProxy() { int size = Integer.parseInt(readDataSourceSize); MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size); Map targetDataSources = new HashMap (); // DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource"); // 写 targetDataSources.put(DataSourceType.write.getType(), writeDataSource); // targetDataSources.put(DataSourceType.read.getType(),readDataSource); // 多个读数据库时 for (int i = 0; i < size; i++) { targetDataSources.put(i, readDataSources.get(i)); } proxy.setDefaultTargetDataSource(writeDataSource); proxy.setTargetDataSources(targetDataSources); return proxy; }} 多数据源切换
/** * 多数据源切换 * name：MyAbstractRoutingDataSource * * @author：lipeng * @data：2018年6月27日 下午6:57:34 * @version 1.0 */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.getReadOrWrite(); if(StringUtils.isBlank(typeKey)||typeKey.equals(DataSourceType.write.getType())) { return DataSourceType.write.getType(); } // 读 简单负载均衡 int number = count.getAndAdd(1); int lookupKey = number % dataSourceNumber; return new Integer(lookupKey); }} 事务管理配置
@Configuration@EnableTransactionManagement(order = 10)@Slf4j@AutoConfigureAfter({ MybatisConfiguration.class })public class TransactionConfiguration extends DataSourceTransactionManagerAutoConfiguration { @Bean @Autowired public DataSourceTransactionManager transactionManager(MyAbstractRoutingDataSource roundRobinDataSouceProxy) { log.info("事物配置"); return new DataSourceTransactionManager(roundRobinDataSouceProxy); }}