小編的世界優質文選資料

SpringBoot + MyBatis + MySQL讀寫分離實踐！

2020年12月11日 - 資料小編

酷扯兒

《酷扯兒》官方帳號

2. AbstractRoutingDataSource

基於特定的查找key路由到特定的數據源。它內部維護了一組目標數據源，並且做了路由key與目標數據源之間的映射，提供基於key查找數據源的方法。

3. 實踐

3.1. 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">

<groupId>com.cjs.example</groupId>

<artifactId>cjs-datasource-demo</artifactId>

<version>0.0.1-SNAPSHOT</version>

<name>cjs-datasource-demo</name>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>2.0.5.RELEASE</version>

</parent>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

<java.version>1.8</java.version>

</properties>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-aop</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<groupId>org.mybatis.spring.boot</groupId>

<artifactId>mybatis-spring-boot-starter</artifactId>

</dependency>

<groupId>org.apache.commons</groupId>

<artifactId>commons-lang3</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<scope>runtime</scope>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-test</artifactId>

</dependency>

</dependencies>

<build>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-maven-plugin</artifactId>

</plugin>

<!--<plugin>

<groupId>org.mybatis.generator</groupId>

<artifactId>mybatis-generator-maven-plugin</artifactId>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

</dependencies>

<configurationFile>${basedir}/src/main/resources/myBatisGeneratorConfig.xml</configurationFile>

</configuration>

<id>Generate MyBatis Artifacts</id>

<goals>

<goal>generate</goal>

</goals>

</execution>

</executions>

</plugin>-->

</plugins>

</build>

</project>

3.2. 數據源配置

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

多數據源配置

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;

/**

* 關於數據源配置，參考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個數據源，1個master，2兩個slave，1個路由數據源。前3個數據源都是為了生成第4個數據源，而且後續我們只用這最後一個路由數據源。

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容器中現在有4個數據源，所以我們需要為事務管理器和MyBatis手動指定一個明確的數據源。

3.3 設置路由key / 查找數據源

目標數據源就是那前3個這個我們是知道的，但是使用的時候是如果查找數據源的呢？

首先，我們定義一個枚舉來代表這三個數據源

package com.cjs.example.enums;

public enum DBTypeEnum {

MASTER, SLAVE1, SLAVE2;

}

接下來，通過ThreadLocal將數據源設置到每個線程上下文中

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

默認情況下，所有的查詢都走從庫，插入/修改/刪除走主庫。我們通過方法名來區分操作類型（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();

}

/**

* 另一種寫法：ifelse 判斷哪些需要讀從數據庫，其餘的走主數據庫

// @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) {

// 有些讀操作必須讀主數據庫

// 比如，獲取微信access_token，因為高峰時期主從同步可能延遲

// 這種情況下就必須強制從主數據讀

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. 工程結構

6. 參考

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

大家在看