散文網(wǎng) » 生活 »日常 » SpringBoot + MyBatis + MySQL 實現(xiàn)讀寫分離！

SpringBoot + MyBatis + MySQL 實現(xiàn)讀寫分離！

2022-07-26 10:04 作者:網(wǎng)星軟件 0人讀過 | 我要投稿

引言

讀寫分離要做的事情就是對于一條SQL該選擇哪個數(shù)s據(jù)庫去執(zhí)行，至于誰來做選擇數(shù)據(jù)庫這件事兒，無非兩個，要么中間件幫我們做，要么程序自己做。因此，一般來講，讀寫分離有兩種實現(xiàn)方式。第一種是依靠中間件（比如：MyCat），也就是說應用程序連接到中間件，中間件幫我們做SQL分離；第二種是應用程序自己去做分離。這里我們選擇程序自己來做，主要是利用Spring提供的路由數(shù)據(jù)源，以及AOP

然而，應用程序?qū)用嫒プ鲎x寫分離最大的弱點（不足之處）在于無法動態(tài)增加數(shù)據(jù)庫節(jié)點，因為數(shù)據(jù)源配置都是寫在配置中的，新增數(shù)據(jù)庫意味著新加一個數(shù)據(jù)源，必然改配置，并重啟應用。當然，好處就是相對簡單。

AbstractRoutingDataSource

基于特定的查找key路由到特定的數(shù)據(jù)源。它內(nèi)部維護了一組目標數(shù)據(jù)源，并且做了路由key與目標數(shù)據(jù)源之間的映射，提供基于key查找數(shù)據(jù)源的方法。

實踐

maven依賴

<?xml?version="1.0"?encoding="UTF-8"?> <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.0</modelVersion> ????<groupId>com.cjs.example</groupId> ????<artifactId>cjs-datasource-demo</artifactId> ????<version>0.0.1-SNAPSHOT</version> ????<packaging>jar</packaging> ????<name>cjs-datasource-demo</name> ????<description></description> ????<parent> ????????<groupId>org.springframework.boot</groupId> ????????<artifactId>spring-boot-starter-parent</artifactId> ????????<version>2.0.5.RELEASE</version> ????????<relativePath/>? ????</parent> ????<properties> ????????<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> ????????<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> ????????<java.version>1.8</java.version> ????</properties> ????<dependencies> ????????<dependency> ????????????<groupId>org.springframework.boot</groupId> ????????????<artifactId>spring-boot-starter-aop</artifactId> ????????</dependency> ????????<dependency> ????????????<groupId>org.springframework.boot</groupId> ????????????<artifactId>spring-boot-starter-jdbc</artifactId> ????????</dependency> ????????<dependency> ????????????<groupId>org.springframework.boot</groupId> ????????????<artifactId>spring-boot-starter-web</artifactId> ????????</dependency> ????????<dependency> ????????????<groupId>org.mybatis.spring.boot</groupId> ????????????<artifactId>mybatis-spring-boot-starter</artifactId> ????????????<version>1.3.2</version> ????????</dependency> ????????<dependency> ????????????<groupId>org.apache.commons</groupId> ????????????<artifactId>commons-lang3</artifactId> ????????????<version>3.8</version> ????????</dependency> ????????<dependency> ????????????<groupId>mysql</groupId> ????????????<artifactId>mysql-connector-java</artifactId> ????????????<scope>runtime</scope> ????????</dependency> ????????<dependency> ????????????<groupId>org.springframework.boot</groupId> ????????????<artifactId>spring-boot-starter-test</artifactId> ????????????<scope>test</scope> ????????</dependency> ????</dependencies> ????<build> ????????<plugins> ????????????<plugin> ????????????????<groupId>org.springframework.boot</groupId> ????????????????<artifactId>spring-boot-maven-plugin</artifactId> ????????????</plugin> ???????????? ????????</plugins> ????</build> </project>

數(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<Object,?Object>?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個數(shù)據(jù)源，1個master，2兩個slave，1個路由數(shù)據(jù)源。前3個數(shù)據(jù)源都是為了生成第4個數(shù)據(jù)源，而且后續(xù)我們只用這最后一個路由數(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個數(shù)據(jù)源，所以我們需要為事務管理器和MyBatis手動指定一個明確的數(shù)據(jù)源。

設置路由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<DBTypeEnum>?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<Member>?selectAll()?{ ????????return?memberMapper.selectByExample(new?MemberExample()); ????} ????@Master ????@Override ????public?String?getToken(String?appId)?{ ????????//??有些讀操作必須讀主數(shù)據(jù)庫 ????????//??比如，獲取微信access_token，因為高峰時期主從同步可能延遲 ????????//??這種情況下就必須強制從主數(shù)據(jù)讀 ????????return?null; ????} }

測試

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"); ????} }

查看控制臺

工程結(jié)構(gòu)

標簽：