Spring Boot + MyBatis + MySQL 實現(xiàn)讀寫分離！
作者：狂亂的貴公子
鏈接：https://www.cnblogs.com/cjsblog/p/9712457.html
1、引言
讀寫分離要做的事情就是對于一條SQL該選擇哪個數(shù)據(jù)庫去執(zhí)行，至于誰來做選擇數(shù)據(jù)庫這件事兒，無非兩個，要么中間件幫我們做，要么程序自己做。
因此，一般來講，讀寫分離有兩種實現(xiàn)方式。第一種是依靠中間件（比如：MyCat），也就是說應用程序連接到中間件，中間件幫我們做SQL分離；第二種是應用程序自己去做分離。這里我們選擇程序自己來做，主要是利用Spring提供的路由數(shù)據(jù)源，以及AOP
然而，應用程序層面去做讀寫分離最大的弱點（不足之處）在于無法動態(tài)增加數(shù)據(jù)庫節(jié)點，因為數(shù)據(jù)源配置都是寫在配置中的，新增數(shù)據(jù)庫意味著新加一個數(shù)據(jù)源，必然改配置，并重啟應用。當然，好處就是相對簡單。
2、AbstractRoutingDataSource
基于特定的查找key路由到特定的數(shù)據(jù)源。它內部維護了一組目標數(shù)據(jù)源，并且做了路由key與目標數(shù)據(jù)源之間的映射，提供基于key查找數(shù)據(jù)源的方法。
3、實踐
3.1. maven依賴
"1.0"?encoding="UTF-8"?>
"http://maven.apache.org/POM/4.0.0"?xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
????xsi:schemaLocation="http://maven.apache.org/POM/4.0.0?http://maven.apache.org/xsd/maven-4.0.0.xsd">
????4.0.0
????com.cjs.example
????cjs-datasource-demo
????0.0.1-SNAPSHOT
????jar
????cjs-datasource-demo
????
????
????????org.springframework.boot
????????spring-boot-starter-parent
????????2.0.5.RELEASE
????????
????
????
????????UTF-8
????????UTF-8
????????1.8
????
????
????????
????????????org.springframework.boot
????????????spring-boot-starter-aop
????????
????????
????????????org.springframework.boot
????????????spring-boot-starter-jdbc
????????
????????
????????????org.springframework.boot
????????????spring-boot-starter-web
????????
????????
????????????org.mybatis.spring.boot
????????????mybatis-spring-boot-starter
????????????1.3.2
????????
????????
????????????org.apache.commons
????????????commons-lang3
????????????3.8
????????
????????
????????????mysql
????????????mysql-connector-java
????????????runtime
????????
????????
????????????org.springframework.boot
????????????spring-boot-starter-test
????????????test
????????
????
????
????????
????????????
????????????????org.springframework.boot
????????????????spring-boot-maven-plugin
????????????
????????????
????????
????
3.2. 數(shù)據(jù)源配置
application.yml
spring:
??datasource:
????master:
??????jdbc-url:?jdbc:mysql://192.168.102.31:3306/test
??????username:?root
??????password:?123456
??????driver-class-name:?com.mysql.jdbc.Driver
????slave1:
??????jdbc-url:?jdbc:mysql://192.168.102.56:3306/test
??????username:?pig???#?只讀賬戶
??????password:?123456
??????driver-class-name:?com.mysql.jdbc.Driver
????slave2:
??????jdbc-url:?jdbc:mysql://192.168.102.36:3306/test
??????username:?pig???#?只讀賬戶
??????password:?123456
??????driver-class-name:?com.mysql.jdbc.Driver
多數(shù)據(jù)源配置
package?com.cjs.example.config;
import?com.cjs.example.bean.MyRoutingDataSource;
import?com.cjs.example.enums.DBTypeEnum;
import?org.springframework.beans.factory.annotation.Qualifier;
import?org.springframework.boot.context.properties.ConfigurationProperties;
import?org.springframework.boot.jdbc.DataSourceBuilder;
import?org.springframework.context.annotation.Bean;
import?org.springframework.context.annotation.Configuration;
import?javax.sql.DataSource;
import?java.util.HashMap;
import?java.util.Map;
/**
?*?關于數(shù)據(jù)源配置，參考SpringBoot官方文檔第79章《Data?Access》
?*?79.?Data?Access
?*?79.1?Configure?a?Custom?DataSource
?*?79.2?Configure?Two?DataSources
?*/
@Configuration
public?class?DataSourceConfig?{
????@Bean
????@ConfigurationProperties("spring.datasource.master")
????public?DataSource?masterDataSource()?{
????????return?DataSourceBuilder.create().build();
????}
????@Bean
????@ConfigurationProperties("spring.datasource.slave1")
????public?DataSource?slave1DataSource()?{
????????return?DataSourceBuilder.create().build();
????}
????@Bean
????@ConfigurationProperties("spring.datasource.slave2")
????public?DataSource?slave2DataSource()?{
????????return?DataSourceBuilder.create().build();
????}
????@Bean
????public?DataSource?myRoutingDataSource(@Qualifier("masterDataSource")?DataSource?masterDataSource,
??????????????????????????????????????????@Qualifier("slave1DataSource")?DataSource?slave1DataSource,
??????????????????????????????????????????@Qualifier("slave2DataSource")?DataSource?slave2DataSource)?{
????????Map?targetDataSources?=?new?HashMap<>();
????????targetDataSources.put(DBTypeEnum.MASTER,?masterDataSource);
????????targetDataSources.put(DBTypeEnum.SLAVE1,?slave1DataSource);
????????targetDataSources.put(DBTypeEnum.SLAVE2,?slave2DataSource);
????????MyRoutingDataSource?myRoutingDataSource?=?new?MyRoutingDataSource();
????????myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
????????myRoutingDataSource.setTargetDataSources(targetDataSources);
????????return?myRoutingDataSource;
????}
}
package?com.cjs.example.config;
import?org.apache.ibatis.session.SqlSessionFactory;
import?org.mybatis.spring.SqlSessionFactoryBean;
import?org.springframework.context.annotation.Bean;
import?org.springframework.context.annotation.Configuration;
import?org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import?org.springframework.jdbc.datasource.DataSourceTransactionManager;
import?org.springframework.transaction.PlatformTransactionManager;
import?org.springframework.transaction.annotation.EnableTransactionManagement;
import?javax.annotation.Resource;
import?javax.sql.DataSource;
@EnableTransactionManagement
@Configuration
public?class?MyBatisConfig?{
????@Resource(name?=?"myRoutingDataSource")
????private?DataSource?myRoutingDataSource;
????@Bean
????public?SqlSessionFactory?sqlSessionFactory()?throws?Exception?{
????????SqlSessionFactoryBean?sqlSessionFactoryBean?=?new?SqlSessionFactoryBean();
????????sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
????????sqlSessionFactoryBean.setMapperLocations(new?PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
????????return?sqlSessionFactoryBean.getObject();
????}
????@Bean
????public?PlatformTransactionManager?platformTransactionManager()?{
????????return?new?DataSourceTransactionManager(myRoutingDataSource);
????}
}
由于Spring容器中現(xiàn)在有4個數(shù)據(jù)源，所以我們需要為事務管理器和MyBatis手動指定一個明確的數(shù)據(jù)源。
3.3. 設置路由key / 查找數(shù)據(jù)源
目標數(shù)據(jù)源就是那前3個這個我們是知道的，但是使用的時候是如果查找數(shù)據(jù)源的呢？
首先，我們定義一個枚舉來代表這三個數(shù)據(jù)源
package?com.cjs.example.enums;
public?enum?DBTypeEnum?{
????MASTER,?SLAVE1,?SLAVE2;
}
接下來，通過ThreadLocal將數(shù)據(jù)源設置到每個線程上下文中
package?com.cjs.example.bean;
import?com.cjs.example.enums.DBTypeEnum;
import?java.util.concurrent.atomic.AtomicInteger;
public?class?DBContextHolder?{
????private?static?final?ThreadLocal?contextHolder?=?new?ThreadLocal<>();
????private?static?final?AtomicInteger?counter?=?new?AtomicInteger(-1);
????public?static?void?set(DBTypeEnum?dbType)?{
????????contextHolder.set(dbType);
????}
????public?static?DBTypeEnum?get()?{
????????return?contextHolder.get();
????}
????public?static?void?master()?{
????????set(DBTypeEnum.MASTER);
????????System.out.println("切換到master");
????}
????public?static?void?slave()?{
????????//??輪詢
????????int?index?=?counter.getAndIncrement()?%?2;
????????if?(counter.get()?>?9999)?{
????????????counter.set(-1);
????????}
????????if?(index?==?0)?{
????????????set(DBTypeEnum.SLAVE1);
????????????System.out.println("切換到slave1");
????????}else?{
????????????set(DBTypeEnum.SLAVE2);
????????????System.out.println("切換到slave2");
????????}
????}
}
獲取路由key
package?com.cjs.example.bean;
import?org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import?org.springframework.lang.Nullable;
public?class?MyRoutingDataSource?extends?AbstractRoutingDataSource?{
????@Nullable
????@Override
????protected?Object?determineCurrentLookupKey()?{
????????return?DBContextHolder.get();
????}
}
設置路由key
默認情況下，所有的查詢都走從庫，插入/修改/刪除走主庫。我們通過方法名來區(qū)分操作類型（CRUD）
package?com.cjs.example.aop;
import?com.cjs.example.bean.DBContextHolder;
import?org.apache.commons.lang3.StringUtils;
import?org.aspectj.lang.JoinPoint;
import?org.aspectj.lang.annotation.Aspect;
import?org.aspectj.lang.annotation.Before;
import?org.aspectj.lang.annotation.Pointcut;
import?org.springframework.stereotype.Component;
@Aspect
@Component
public?class?DataSourceAop?{
????@Pointcut("!@annotation(com.cjs.example.annotation.Master)?"?+
????????????"&&?(execution(*?com.cjs.example.service..*.select*(..))?"?+
????????????"||?execution(*?com.cjs.example.service..*.get*(..)))")
????public?void?readPointcut()?{
????}
????@Pointcut("@annotation(com.cjs.example.annotation.Master)?"?+
????????????"||?execution(*?com.cjs.example.service..*.insert*(..))?"?+
????????????"||?execution(*?com.cjs.example.service..*.add*(..))?"?+
????????????"||?execution(*?com.cjs.example.service..*.update*(..))?"?+
????????????"||?execution(*?com.cjs.example.service..*.edit*(..))?"?+
????????????"||?execution(*?com.cjs.example.service..*.delete*(..))?"?+
????????????"||?execution(*?com.cjs.example.service..*.remove*(..))")
????public?void?writePointcut()?{
????}
????@Before("readPointcut()")
????public?void?read()?{
????????DBContextHolder.slave();
????}
????@Before("writePointcut()")
????public?void?write()?{
????????DBContextHolder.master();
????}
????/**
?????*?另一種寫法：if...else... ?判斷哪些需要讀從數(shù)據(jù)庫，其余的走主數(shù)據(jù)庫
?????*/
//????@Before("execution(*?com.cjs.example.service.impl.*.*(..))")
//????public?void?before(JoinPoint?jp)?{
//????????String?methodName?=?jp.getSignature().getName();
//
//????????if?(StringUtils.startsWithAny(methodName,?"get",?"select",?"find"))?{
//????????????DBContextHolder.slave();
//????????}else?{
//????????????DBContextHolder.master();
//????????}
//????}
}
有一般情況就有特殊情況，特殊情況是某些情況下我們需要強制讀主庫，針對這種情況，我們定義一個主鍵，用該注解標注的就讀主庫
package?com.cjs.example.annotation;
public?@interface?Master?{
}
例如，假設我們有一張表member
package?com.cjs.example.service.impl;
import?com.cjs.example.annotation.Master;
import?com.cjs.example.entity.Member;
import?com.cjs.example.entity.MemberExample;
import?com.cjs.example.mapper.MemberMapper;
import?com.cjs.example.service.MemberService;
import?org.springframework.beans.factory.annotation.Autowired;
import?org.springframework.stereotype.Service;
import?org.springframework.transaction.annotation.Transactional;
import?java.util.List;
@Service
public?class?MemberServiceImpl?implements?MemberService?{
????@Autowired
????private?MemberMapper?memberMapper;
????@Transactional
????@Override
????public?int?insert(Member?member)?{
????????return?memberMapper.insert(member);
????}
????@Master
????@Override
????public?int?save(Member?member)?{
????????return?memberMapper.insert(member);
????}
????@Override
????public?List?selectAll()?{
????????return?memberMapper.selectByExample(new?MemberExample());
????}
????@Master
????@Override
????public?String?getToken(String?appId)?{
????????//??有些讀操作必須讀主數(shù)據(jù)庫
????????//??比如，獲取微信access_token，因為高峰時期主從同步可能延遲
????????//??這種情況下就必須強制從主數(shù)據(jù)讀
????????return?null;
????}
}
4、測試
package?com.cjs.example;
import?com.cjs.example.entity.Member;
import?com.cjs.example.service.MemberService;
import?org.junit.Test;
import?org.junit.runner.RunWith;
import?org.springframework.beans.factory.annotation.Autowired;
import?org.springframework.boot.test.context.SpringBootTest;
import?org.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest
public?class?CjsDatasourceDemoApplicationTests?{
????@Autowired
????private?MemberService?memberService;
????@Test
????public?void?testWrite()?{
????????Member?member?=?new?Member();
????????member.setName("zhangsan");
????????memberService.insert(member);
????}
????@Test
????public?void?testRead()?{
????????for?(int?i?=?0;?i?????????????memberService.selectAll();
????????}
????}
????@Test
????public?void?testSave()?{
????????Member?member?=?new?Member();
????????member.setName("wangwu");
????????memberService.save(member);
????}
????@Test
????public?void?testReadFromMaster()?{
????????memberService.getToken("1234");
????}
}
查看控制臺
5、工程結構
參考：
https://www.jianshu.com/p/f2f4256a2310
http://www.cnblogs.com/gl-developer/p/6170423.html
https://www.cnblogs.com/huangjuncong/p/8576935.html
https://blog.csdn.net/liu976180578/article/details/77684583
關注公眾號【Java技術江湖】后回復“PDF”即可領取200+頁的《Java工程師面試指南》
強烈推薦，幾乎涵蓋所有Java工程師必知必會的知識點，不管是復習還是面試，都很實用。
評論
圖片
表情