读写分离
经典模式
思路：
1.准备多个数据源
2.把所有的数据源存入AbstractRoutingDataSource中
3.mybatis设置SqlSessionFactory时，SqlSessionFactory中设置使用的数据源为AbstractRoutingDataSource（使用JPA时则为EntityManagerFactory设置数据源）
4.使用时通过自定义注解指定sql使用哪一个数据源
1.准备多个数据源
application.yml
spring:
main:
allow-bean-definition-overriding: true
datasource:
druid:
type: com.alibaba.druid.pool.DruidDataSource
write:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.1.105:33065/test_db?characterEncoding=utf-8&serverTimezone=GMT%2B8
username: root
password: root
read:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.1.105:33066/test_db?characterEncoding=utf-8&serverTimezone=GMT%2B8
username: root
password: root
加载数据源
@Configuration
public class DruidDataSourceConfig {
/**
* DataSource 配置
* @return
*/
@ConfigurationProperties(prefix = "spring.datasource.druid.read")
@Bean(name = DataSourceConst.READ_DATASOURCE_KEY)
public DataSource readDruidDataSource() {
return new DruidDataSource();
}
/**
* DataSource 配置
* @return
*/
@ConfigurationProperties(prefix = "spring.datasource.druid.write")
@Bean(name = DataSourceConst.WRITE_DATASOURCE_KEY)
@Primary
public DataSource writeDruidDataSource() {
return new DruidDataSource();
}
}
2.把所有的数据源存入AbstractRoutingDataSource中
自定义AbstractRoutingDataSource实现类，determineCurrentLookupKey方法可用于实现获取那一个数据源
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
String lookupKey = DynamicDataSourceHolder.getDataSource();
return lookupKey;
}
}
@Configuration
public class DataSourceConfig {
/**
* 注入AbstractRoutingDataSource
* @param readDruidDataSource
* @param writeDruidDataSource
* @return
* @throws Exception
*/
@Bean
public AbstractRoutingDataSource routingDataSource(
@Qualifier(DataSourceConst.READ_DATASOURCE_KEY) DataSource readDruidDataSource,
@Qualifier(DataSourceConst.WRITE_DATASOURCE_KEY) DataSource writeDruidDataSource
) throws Exception {
DynamicDataSource dataSource = new DynamicDataSource();
Map<Object, Object> targetDataSources = new HashMap();
targetDataSources.put(DataSourceConst.WRITE_DATASOURCE_KEY, writeDruidDataSource);
targetDataSources.put(DataSourceConst.READ_DATASOURCE_KEY, readDruidDataSource);
/*设置所有的数据源*/
dataSource.setTargetDataSources(targetDataSources);
/*指定默认的数据源*/
dataSource.setDefaultTargetDataSource(writeDruidDataSource);
return dataSource;
}
}
3.mybatis设置SqlSessionFactory
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {
@Resource
private AbstractRoutingDataSource myRoutingDataSource;
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
return sqlSessionFactoryBean.getObject();
}
@Bean
public PlatformTransactionManager platformTransactionManager() {
return new DataSourceTransactionManager(myRoutingDataSource);
}
}
4.选择数据源
@Aspect
@Component
public class DynamicDataSourceAspect {
@Around("@annotation(com.example.annotation.TargetDataSource)")
public Object around(ProceedingJoinPoint pjp) throws Throwable {
MethodSignature methodSignature = (MethodSignature) pjp.getSignature();
Method targetMethod = methodSignature.getMethod();
if (targetMethod.isAnnotationPresent(TargetDataSource.class)) {
String targetDataSource = targetMethod.getAnnotation(TargetDataSource.class).value();
/*设置线程与当前数据源绑定*/
DynamicDataSourceHolder.setDataSource(targetDataSource);
}
Object result = pjp.proceed();//执行方法
DynamicDataSourceHolder.clearDataSource();
return result;
}
}
5.使用
@RestController
@RequestMapping("/user")
public class UserController {
@Resource
private UserMapper userMapper;
@GetMapping("/save")
public String insert() {
User user = new User();
user.setNickname("");
user.setPassword("123456");
user.setSex(1);
user.setBirthday("1990-10-01");
userMapper.addUser(user);
return "success";
}
@GetMapping("/getByRead")
@TargetDataSource(value = DataSourceConst.READ_DATASOURCE_KEY)
public List<User> listUserByReadDataSource() {
return userMapper.findUsers();
}
@GetMapping("/getByWrite")
@TargetDataSource(value = DataSourceConst.WRITE_DATASOURCE_KEY)
public List<User> listUserByWriteDataSource() {
return userMapper.findUsers();
}
}
上述过程参考至：https://github.com/Lengchuan/SpringBoot-Study/tree/master/SpringBoot-Druid，上述代码实现：https://gitee.com/ChaiYe/sharding-sphere-demo/tree/master/classic-reading-writing
sharding-jdbc的实现
问题：sharding-jdbc已经实现了数据源准备，AbstractRoutingDataSource准备以及SqlSessionFactory的处理了，唯一的问题是如何主动指定使用那一个数据源，因为sharding-jdbc读的时候默认走从库，写的时候默认走主库，这时会遇到一个问题，就是有时候从库无法及时从主库中同步数据，无法达一致性，我们某些sql需要强制指定从主库中读取
解决思路：
官方给的解决方案是：https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/usage/hint/#基于暗示-hint-的强制主库路由
1.shardingsphere配置
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
props:
sql:
show: true
datasource:
names: ds1,ds2
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.1.101:33065/ksd_sharding_db?characterEncoding=utf-8&serverTimezone=GMT%2B8
username: root
password: root
maxPoolSize: 100
minPoolSize: 5
ds2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.1.101:33066/ksd_sharding_db?characterEncoding=utf-8&serverTimezone=GMT%2B8
username: root
password: root
maxPoolSize: 100
minPoolSize: 5
sharding:
#默认数据源
default-data-source-name: ds1
masterslave:
name: ms
master-data-source-name: ds1
slave-data-source-names: ds2
load-balance-algorithm-type: round_robin
2.指定选择主数据源
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface MasterRoute {
}
@Aspect
@Component
public class MasterRouteAspect {
@Around("@annotation(com.example.annotation.MasterRoute)")
public Object around(ProceedingJoinPoint pjp) throws Throwable {
Object result = null;
try (HintManager hintManager = HintManager.getInstance()) {
hintManager.setMasterRouteOnly();
result = pjp.proceed();//执行方法
}
return result;
}
}
3.使用
@GetMapping("/getByWrite")
@MasterRoute
public List<User> listUserByWriteDataSource() {
return userMapper.findUsers();
}
上述代码实现：https://gitee.com/ChaiYe/sharding-sphere-demo/tree/master/separation-reading-writing
原创文章，转载请注明出处
