SpringBoot 專案優雅實現讀寫分離
來源:京東雲開發者
1. 配置主資料庫和從資料庫的連線資訊
# 主庫配置
spring.datasource.master.jdbc-url=jdbc:mysql://ip:port/master?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.datasource.master.username=master
spring.datasource.master.password=123456
spring.datasource.master.driver-class-name=com.mysql.jdbc.Driver
# 從庫配置
spring.datasource.slave.jdbc-url=jdbc:mysql://ip:port/slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.datasource.slave.username=slave
spring.datasource.slave.password=123456
spring.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
2. 建立主資料庫和從資料庫的資料來源配置類
@Configuration@ConditionalOnProperty("spring.datasource.master.jdbc-url")public class MasterDataSourceConfiguration { @Bean("masterDataSource") @ConfigurationProperties(prefix = "spring.datasource.master") public DataSource masterDataSource() { return DataSourceBuilder.create().build(); }}
@Configuration@ConditionalOnProperty("spring.datasource.slave.jdbc-url")public class SlaveDataSourceConfiguration { @Bean("slaveDataSource") @ConfigurationProperties(prefix = "spring.datasource.slave") public DataSource slaveDataSource() { return DataSourceBuilder.create().build(); }}
3. 建立主從資料來源列舉
public enum DataSourceTypeEnum {
/**
* 主庫
*/
MASTER,
/**
* 從庫
*/
SLAVE,
;
}
4. 建立動態路由資料來源
4j
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
"${DB_RW_SEPARATE_SWITCH:false}") (
private boolean dbRwSeparateSwitch;
protected Object determineCurrentLookupKey() {
if(dbRwSeparateSwitch && DataSourceTypeEnum.SLAVE.equals(DataSourceContextHolder.getDataSourceType())) {
log.info("DynamicRoutingDataSource 切換資料來源到從庫");
return DataSourceTypeEnum.SLAVE;
}
log.info("DynamicRoutingDataSource 切換資料來源到主庫");
// 根據需要指定當前使用的資料來源,這裡可以使用ThreadLocal或其他方式來決定使用主庫還是從庫
return DataSourceTypeEnum.MASTER;
}
}
5. 建立動態資料來源配置類
"spring.datasource.master.jdbc-url") (
public class DynamicDataSourceConfiguration {
"dataSource") (
public DataSource dynamicDataSource(DataSource masterDataSource, DataSource slaveDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceTypeEnum.MASTER, masterDataSource);
targetDataSources.put(DataSourceTypeEnum.SLAVE, slaveDataSource);
DynamicRoutingDataSource dynamicDataSource = new DynamicRoutingDataSource();
dynamicDataSource.setTargetDataSources(targetDataSources);
dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
return dynamicDataSource;
}
}
6. 建立DatasourceContextHolder類使用ThreadLocal儲存當前執行緒的資料來源型別
public class DataSourceContextHolder {
private static final ThreadLocal<DataSourceTypeEnum> contextHolder = new ThreadLocal<>();
public static void setDataSourceType(DataSourceTypeEnum dataSourceType) {
contextHolder.set(dataSourceType);
}
public static DataSourceTypeEnum getDataSourceType() {
return contextHolder.get();
}
public static void clearDataSourceType() {
contextHolder.remove();
}
}
7. 建立自定義註解,用於標記主和從資料來源
@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.METHOD)public @interface MasterDataSource {}@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.METHOD)public @interface SlaveDataSource {}
8. 建立切面類,攔截資料庫操作,並根據註解設定切換資料來源引數
public class DataSourceAspect {
"@annotation(xxx.MasterDataSource)") (
public void setMasterDataSource(JoinPoint joinPoint) {
DataSourceContextHolder.setDataSourceType(DataSourceTypeEnum.MASTER);
}
"@annotation(xxx.SlaveDataSource)") (
public void setSlaveDataSource(JoinPoint joinPoint) {
DataSourceContextHolder.setDataSourceType(DataSourceTypeEnum.SLAVE);
}
"@annotation(xxx.MasterDataSource) || @annotation(xxx.SlaveDataSource)") (
public void clearDataSource(JoinPoint joinPoint) {
DataSourceContextHolder.clearDataSourceType();
}
}
9. 在Service層的方法上使用自定義註解標記查詢資料來源
public class TestService {
private TestDao testDao;
public Test test() {
return testDao.queryByPrimaryKey(11L);
}
}
10. 排除掉資料來源自動配置類
SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
1. 使用連結池,以Hikari為例
# 主庫配置
spring.datasource.master.jdbc-url=jdbc:mysql://ip:port/master?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.datasource.master.username=master
spring.datasource.master.password=123456
spring.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.master.hikari.name=master
spring.datasource.master.hikari.minimum-idle=5
spring.datasource.master.hikari.idle-timeout=30
spring.datasource.master.hikari.maximum-pool-size=10
spring.datasource.master.hikari.auto-commit=true
spring.datasource.master.hikari.pool-name=DatebookHikariCP
spring.datasource.master.hikari.max-lifetime=1800000
spring.datasource.master.hikari.connection-timeout=30000
spring.datasource.master.hikari.connection-test-query=SELECT 1
# 從庫配置
spring.datasource.slave.jdbc-url=jdbc:mysql://ip:port/slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.datasource.slave.username=root
spring.datasource.slave.password=123456
spring.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.slave.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.slave.hikari.name=master
spring.datasource.slave.hikari.minimum-idle=5
spring.datasource.slave.hikari.idle-timeout=30
spring.datasource.slave.hikari.maximum-pool-size=10
spring.datasource.slave.hikari.auto-commit=true
spring.datasource.slave.hikari.pool-name=DatebookHikariCP
spring.datasource.slave.hikari.max-lifetime=1800000
spring.datasource.slave.hikari.connection-timeout=30000
spring.datasource.slave.hikari.connection-test-query=SELECT 1
2. 整合 mybatis 並在寫入時強制切換到主庫
@Intercepts({ @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),})@Componentpublic class WriteInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { // 獲取 SQL 型別 DataSourceTypeEnum dataSourceType = DataSourceContextHolder.getDataSourceType(); if(DataSourceTypeEnum.SLAVE.equals(dataSourceType)) { DataSourceContextHolder.setDataSourceType(DataSourceTypeEnum.MASTER); } try { // 執行 SQL return invocation.proceed(); } finally { // 恢復資料來源 考慮到寫入後可能會反查,後續都走主庫 // DataSourceContextHolder.setDataSourceType(dataSourceType); } }}
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/70024420/viewspace-2996449/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- springboot實現讀寫分離Spring Boot
- Amoeba實現讀寫分離
- ProxySQL實現MySQL讀寫分離MySql
- Amoeba 實現 MySQL 讀寫分離MySql
- CQRS如何實現讀寫分離
- ShardingSphere + Mysql,實現分庫分表、讀寫分離,並整合 SpringBootMySqlSpring Boot
- 分庫分表(6)--- SpringBoot+ShardingSphere實現分表+ 讀寫分離Spring Boot
- MySQL-SpringBoot整合JPA實現資料讀寫分離MySqlSpring Boot
- Amoeba+Mysql 實現讀寫分離MySql
- 【Mongo】Mongo讀寫分離的實現Go
- mysql-proxy實現讀寫分離MySql
- ShardingSphere(七) 讀寫分離配置,實現分庫讀寫操作
- Sharding-JDBC基本使用,整合Springboot實現分庫分表,讀寫分離JDBCSpring Boot
- mysql優化之讀寫分離MySql優化
- 位元組面試:什麼是讀寫分離?讀寫分離的底層如何實現?面試
- PostgreSQL+Pgpool實現HA讀寫分離SQL
- docker+atlas+mysql實現讀寫分離DockerMySql
- Docker實現Mariadb分庫分表、讀寫分離Docker
- springboot+mybatis+druid實現mysql主從讀寫分離(五)Spring BootMyBatisUIMySql
- 手把手教你利用 SpringBoot + Mybatis 實現一個讀寫分庫專案Spring BootMyBatis
- 搭建MySQL主從實現Django讀寫分離MySqlDjango
- MHA+ProxySQL實現讀寫分離高可用SQL
- ProxySQL實現Mysql讀寫分離 - 部署手冊MySql
- MySQL 中介軟體Atlas 實現讀寫分離MySql
- Spring實現資料庫讀寫分離Spring資料庫
- 讀寫分離很難嗎?springboot結合aop簡單就實現了Spring Boot
- StoneDB 讀寫分離實踐方案
- Mycat讀寫分離配置實踐
- redis客戶端實現高可用讀寫分離Redis客戶端
- Spring Aop實現資料庫讀寫分離Spring資料庫
- Mycat實現mysql的負載均衡讀寫分離MySql負載
- Spring配置RoutingDataSource實現mysql讀寫分離SpringMySql
- mysql proxy 安裝及配置實現讀寫分離MySql
- Kubernetes 中實現 MySQL 的讀寫分離MySql
- 基於Sharding-Jdbc 實現的讀寫分離實現JDBC
- SpringBoot使用Sharding-JDBC讀寫分離Spring BootJDBC
- 搭建基於springmvc,ibatis的工程實現讀寫分離,配置分離SpringMVCBAT
- springboot多資料來源配合docker部署mysql主從實現讀寫分離Spring BootDockerMySql