大家好，又见面了，我是你们的朋友全栈君。
springboot、mysql实现读写分离
1、首先在springcloud config中配置读写数据库
代码语言：javascript复制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、编写读库注解
代码语言：javascript复制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 ReadDataSource {
} 3、增加数据源初始化配置
代码语言：javascript复制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
* <p></p>
* @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<? extends DataSource> 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<DataSource> readDataSources(){
List<DataSource> dataSources=new ArrayList<>();
dataSources.add(readDataSourceOne());
dataSources.add(readDataSourceTwo());
return dataSources;
}
} 4、增加主从配置常量
代码语言：javascript复制/**
* name：DataSourceType
* <p></p>
* @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、事务内读写配置
由于涉及到事务处理，可能会遇到事务中同时用到读库和写库，可能会有延时造成脏读，所以增加了线程变量设置，来保证一个事务内读写都是同一个库
代码语言：javascript复制/**
* name：DataSourceContextHolder
* <p></p>
* @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<String> local = new ThreadLocal<String>();
public static ThreadLocal<String> 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层，这个得保证拦截器优先级在声明式事务前面
代码语言：javascript复制/**
* 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配置
代码语言：javascript复制@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<DataSource> 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<Object, Object> targetDataSources = new HashMap<Object, Object>();
// 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;
}
}多数据源切换
代码语言：javascript复制/**
* 多数据源切换
* name：MyAbstractRoutingDataSource
* <p></p>
* @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);
}
}事务管理配置
代码语言：javascript复制@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);
}
}发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/127034.html原文链接：https://javaforall.cn
