SpringBoot + MyBatis + MySQL 實現(xiàn)讀寫分離！
源?/?cnblog? ? ? ?文/ 廢物大師兄
引言
讀寫分離要做的事情就是對于一條SQL該選擇哪個數(shù)據庫去執(zhí)行，至于誰來做選擇數(shù)據庫這件事兒，無非兩個，要么中間件幫我們做，要么程序自己做。因此，一般來講，讀寫分離有兩種實現(xiàn)方式。第一種是依靠中間件（比如：MyCat），也就是說應用程序連接到中間件，中間件幫我們做SQL分離；第二種是應用程序自己去做分離。這里我們選擇程序自己來做，主要是利用Spring提供的路由數(shù)據源，以及AOP
然而，應用程序層面去做讀寫分離最大的弱點（不足之處）在于無法動態(tài)增加數(shù)據庫節(jié)點，因為數(shù)據源配置都是寫在配置中的，新增數(shù)據庫意味著新加一個數(shù)據源，必然改配置，并重啟應用。當然，好處就是相對簡單。
AbstractRoutingDataSource
基于特定的查找key路由到特定的數(shù)據源。它內部維護了一組目標數(shù)據源，并且做了路由key與目標數(shù)據源之間的映射，提供基于key查找數(shù)據源的方法。
實踐
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>
數(shù)據源配置
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ù)據源配置
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ù)據源配置，參考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個數(shù)據源，1個master，2兩個slave，1個路由數(shù)據源。前3個數(shù)據源都是為了生成第4個數(shù)據源，而且后續(xù)我們只用這最后一個路由數(shù)據源。
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ù)據源，所以我們需要為事務管理器和MyBatis手動指定一個明確的數(shù)據源。
設置路由key / 查找數(shù)據源
目標數(shù)據源就是那前3個這個我們是知道的，但是使用的時候是如果查找數(shù)據源的呢？
首先，我們定義一個枚舉來代表這三個數(shù)據源
package?com.cjs.example.enums;
public?enum?DBTypeEnum?{
????MASTER,?SLAVE1,?SLAVE2;
}
接下來，通過ThreadLocal將數(shù)據源設置到每個線程上下文中
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ù)據庫，其余的走主數(shù)據庫
?????*/
//????@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ù)據庫
????????//??比如，獲取微信access_token，因為高峰時期主從同步可能延遲
????????//??這種情況下就必須強制從主數(shù)據讀
????????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");
????}
}
查看控制臺
工程結構
END
頂級程序員：topcoding
做最好的程序員社區(qū)：Java后端開發(fā)、Python、大數(shù)據、AI
一鍵三連「分享」、「點贊」和「在看」
評論
圖片
表情