SpringBoot+MyBatis+MySQL讀寫分離（實(shí)例）
作者:狂亂的貴公子
來源:cnblogs.com/cjsblog/p/9712457.html
1. 引言
讀寫分離要做的事情就是對于一條SQL該選擇哪個(gè)數(shù)據(jù)庫去執(zhí)行，至于誰來做選擇數(shù)據(jù)庫這件事兒，無非兩個(gè)，要么中間件幫我們做，要么程序自己做。因此，一般來講，讀寫分離有兩種實(shí)現(xiàn)方式。第一種是依靠中間件（比如：MyCat），也就是說應(yīng)用程序連接到中間件，中間件幫我們做SQL分離；第二種是應(yīng)用程序自己去做分離。這里我們選擇程序自己來做，主要是利用Spring提供的路由數(shù)據(jù)源，以及AOP。
然而，應(yīng)用程序?qū)用嫒プ鲎x寫分離最大的弱點(diǎn)（不足之處）在于無法動態(tài)增加數(shù)據(jù)庫節(jié)點(diǎn)，因?yàn)閿?shù)據(jù)源配置都是寫在配置中的，新增數(shù)據(jù)庫意味著新加一個(gè)數(shù)據(jù)源，必然改配置，并重啟應(yīng)用。當(dāng)然，好處就是相對簡單。
2. AbstractRoutingDataSource
基于特定的查找key路由到特定的數(shù)據(jù)源。它內(nèi)部維護(hù)了一組目標(biāo)數(shù)據(jù)源，并且做了路由key與目標(biāo)數(shù)據(jù)源之間的映射，提供基于key查找數(shù)據(jù)源的方法。
3. 實(shí)踐
關(guān)于配置請參考：
https://www.cnblogs.com/cjsblog/p/9706370.html
3.1. maven依賴
<project?xmlns="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">
????<modelVersion>4.0.0modelVersion>
????<groupId>com.cjs.examplegroupId>
????<artifactId>cjs-datasource-demoartifactId>
????<version>0.0.1-SNAPSHOTversion>
????<packaging>jarpackaging>
????<name>cjs-datasource-demoname>
????<description>description>
????<parent>
????????<groupId>org.springframework.bootgroupId>
????????<artifactId>spring-boot-starter-parentartifactId>
????????<version>2.0.5.RELEASEversion>
????????<relativePath/>?
????parent>
????<properties>
????????<project.build.sourceEncoding>UTF-8project.build.sourceEncoding>
????????<project.reporting.outputEncoding>UTF-8project.reporting.outputEncoding>
????????<java.version>1.8java.version>
????properties>
????<dependencies>
????????<dependency>
????????????<groupId>org.springframework.bootgroupId>
????????????<artifactId>spring-boot-starter-aopartifactId>
????????dependency>
????????<dependency>
????????????<groupId>org.springframework.bootgroupId>
????????????<artifactId>spring-boot-starter-jdbcartifactId>
????????dependency>
????????<dependency>
????????????<groupId>org.springframework.bootgroupId>
????????????<artifactId>spring-boot-starter-webartifactId>
????????dependency>
????????<dependency>
????????????<groupId>org.mybatis.spring.bootgroupId>
????????????<artifactId>mybatis-spring-boot-starterartifactId>
????????????<version>1.3.2version>
????????dependency>
????????<dependency>
????????????<groupId>org.apache.commonsgroupId>
????????????<artifactId>commons-lang3artifactId>
????????????<version>3.8version>
????????dependency>
????????<dependency>
????????????<groupId>mysqlgroupId>
????????????<artifactId>mysql-connector-javaartifactId>
????????????<scope>runtimescope>
????????dependency>
????????<dependency>
????????????<groupId>org.springframework.bootgroupId>
????????????<artifactId>spring-boot-starter-testartifactId>
????????????<scope>testscope>
????????dependency>
????dependencies>
????<build>
????????<plugins>
????????????<plugin>
????????????????<groupId>org.springframework.bootgroupId>
????????????????<artifactId>spring-boot-maven-pluginartifactId>
????????????plugin>
????????????
????????plugins>
????build>
project>
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;
/**
?*?關(guān)于數(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;
????}
}
這里，我們配置了4個(gè)數(shù)據(jù)源，1個(gè)master，2兩個(gè)slave，1個(gè)路由數(shù)據(jù)源。前3個(gè)數(shù)據(jù)源都是為了生成第4個(gè)數(shù)據(jù)源，而且后續(xù)我們只用這最后一個(gè)路由數(shù)據(jù)源。
MyBatis配置
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個(gè)數(shù)據(jù)源，所以我們需要為事務(wù)管理器和MyBatis手動指定一個(gè)明確的數(shù)據(jù)源。
3.3. 設(shè)置路由key / 查找數(shù)據(jù)源
目標(biāo)數(shù)據(jù)源就是那前3個(gè)這個(gè)我們是知道的，但是使用的時(shí)候是如果查找數(shù)據(jù)源的呢？
首先，我們定義一個(gè)枚舉來代表這三個(gè)數(shù)據(jù)源
package?com.cjs.example.enums;
public?enum?DBTypeEnum?{
????MASTER,?SLAVE1,?SLAVE2;
}
接下來，通過ThreadLocal將數(shù)據(jù)源設(shè)置到每個(gè)線程上下文中
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();
????}
}
設(shè)置路由key
默認(rèn)情況下，所有的查詢都走從庫，插入/修改/刪除走主庫。我們通過方法名來區(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();
//????????}
//????}
}
有一般情況就有特殊情況，特殊情況是某些情況下我們需要強(qiáng)制讀主庫，針對這種情況，我們定義一個(gè)主鍵，用該注解標(biāo)注的就讀主庫
package?com.cjs.example.annotation;
public?@interface?Master?{
}
例如，假設(shè)我們有一張表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，因?yàn)楦叻鍟r(shí)期主從同步可能延遲
????????//??這種情況下就必須強(qiáng)制從主數(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?4;?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. 工程結(jié)構(gòu)
題外話: 目前小哈正在個(gè)人博客（新搭建的網(wǎng)站，域名就是犬小哈的拼音）?www.quanxiaoha.com?上更新《Go語言教程》，畢竟Go自帶天然的并發(fā)優(yōu)勢，后端的同學(xué)還是要學(xué)一下的，這個(gè)教程系列小哈會一直更新下去，目前已經(jīng)更新到 Go語言的基礎(chǔ)語法了，歡迎小伙伴們訪問哦~ END
有熱門推薦?
2.?高可用解決方案：同城雙活？異地雙活？異地多活？怎么實(shí)現(xiàn)？
最近面試BAT，整理一份面試資料《Java面試BATJ通關(guān)手冊》，覆蓋了Java核心技術(shù)、JVM、Java并發(fā)、SSM、微服務(wù)、數(shù)據(jù)庫、數(shù)據(jù)結(jié)構(gòu)等等。
獲取方式：點(diǎn)“在看”，關(guān)注公眾號并回復(fù)?Java?領(lǐng)取，更多內(nèi)容陸續(xù)奉上。
文章有幫助的話，在看，轉(zhuǎn)發(fā)吧。
謝謝支持喲 (*^__^*)
評論
圖片
表情