目录
需求
有主从两个数据库(结构一致),期望在SpringBoot程序层面实现读写分离。
具体需求:SpringBoot程序的写操作均进入主库;SpringBoot程序的读操作均访问从库。
(这里为便于区分主从库,二者初始数据不一致,但结构完全一致)
环境
开发环境:win7 x64+IDEA2018
java version :"1.8.0_111"
mysql数据库版本5.7.10
mysql主库:192.168.7.161:3306
mysql从库:192.168.7.162:3306
SpringBoot版本2.3.7
数据库表字段
数据库名:dbdemo
数据表:categories和products;
mysql> use dbdemo;
Database changed
mysql> show tables;
+------------------+
| Tables_in_dbdemo |
+------------------+
| categories |
| products |
+------------------+
2 rows in set (0.02 sec)
mysql> show create table categories;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------+
| categories | CREATE TABLE `categories` (
`cat_id` int(11) NOT NULL AUTO_INCREMENT,
`cat_name` varchar(255) NOT NULL,
`cat_description` text,
`cat_date` datetime DEFAULT NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------+
1 row in set (0.23 sec)
mysql> show create table products;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------+
| products | CREATE TABLE `products` (
`prd_id` int(11) NOT NULL AUTO_INCREMENT,
`prd_name` varchar(355) NOT NULL,
`prd_price` decimal(10,0) DEFAULT NULL,
`prd_date` datetime DEFAULT NULL,
`cat_id` int(11) NOT NULL,
PRIMARY KEY (`prd_id`),
KEY `fk_cat` (`cat_id`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`cat_id`) REFERENCES `categories` (`cat_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
mysql>
操作笔记
IDEA新建SpringBoot应用程序,添加了mybatis-generator-maven-plugin,jdbc,aop等依赖。
基于切面和自定义注解实现了读写分离。
项目结构如下
完整pom.xm如下:
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.wdh</groupId>
<artifactId>springboot-read-write-split-mysql</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-read-write-split-mysql</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.7.RELEASE</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--commons-lang3是Apache的Jakarta commons工程下具有一系列公共基础类。涉及到数组工具类,字符串工具类,字符工具类,数学方面,时间日期工具类,异常,事件等工具类-->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<!--引入面向切面编程的依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.3.7.RELEASE</version>
<configuration>
<mainClass>com.wdh.springbootreadwritesplitmysql.SpringbootReadWriteSplitMysqlApplication
</mainClass>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
<!--mybatis generator自动生成代码插件-->
<!--Idea中使用此插件方法生成代码的方法:配置完generatorConfig.xml后,在idea右侧Maven面板-本项目-Plugins-刷新-找到mybatis-generate-点击右键Run Maven Build即可生成代码-->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.1</version>
<configuration>
<configurationFile>src\main\resources\mybatis-generator\generatorConfig.xml</configurationFile>
<overwrite>false</overwrite>
<verbose>true</verbose>
</configuration>
</plugin>
</plugins>
</build>
</project>
application.properties内容如下,配置了mysql主库和从库的连接信息
# 应用名称
spring.application.name=springboot-read-write-split-mysql
# 应用服务 WEB 访问端口
server.port=8080
#这是自定义的mysql主数据库的访问配置
spring.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
#以下是配合默认的spring.datasource.hikari的jdbcUrl
spring.datasource.master.jdbcUrl=jdbc:mysql://192.168.7.161:3306/dbdemo?serverTimezone=UTC
spring.datasource.master.username=root
spring.datasource.master.[CSDN不让发]password[CSDN不让发]=XX你的密码XX
#这是自定义的mysql从数据库的访问配置
spring.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.slave.jdbcUrl=jdbc:mysql://192.168.7.162:3306/dbdemo?serverTimezone=UTC
spring.datasource.slave.username=root
spring.datasource.slave.[CSDN不让发]password[CSDN不让发]=XX你的密码XX
MyBatis自动生成mapper、xml的配置
在src\main\resources下新建mybatis-generator目录及目录下generatorConfig.xml。
src\main\resources\mybatis-generator\generatorConfig.xml
前述pom.xml中的以下内容是指定使用此文件进行mybatis自动生成
<!--mybatis generator自动生成代码插件-->
<!--Idea中使用此插件方法生成代码的方法:配置完generatorConfig.xml后,在idea右侧Maven面板-本项目-Plugins-刷新-找到mybatis-generate-点击右键Run Maven Build即可生成代码-->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.1</version>
<configuration>
<configurationFile>src\main\resources\mybatis-generator\generatorConfig.xml</configurationFile>
<overwrite>false</overwrite>
<verbose>true</verbose>
</configuration>
</plugin>
generatorConfig.xml完整内容如下。(说明见注释)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<!--参考http://mybatis.org/generator/configreference/xmlconfig.html-->
<generatorConfiguration>
<!-- 数据库驱动:选择你的本地硬盘上面的数据库驱动包-->
<!--<classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" />-->
<!--注意mysql驱动的文件要与pom.xml中引用的mysql的包的版本一致-->
<classPathEntry location="D:\JavaDevEnv\maven_repository\Mysql\mysql-connector-java\8.0.22\mysql-connector-java-8.0.22.jar"></classPathEntry>
<context id="DB2Tables" targetRuntime="MyBatis3">
<commentGenerator>
<property name="suppressDate" value="false"></property>
<!-- 是否去除自动生成的注释 true:是 : false:否 -->
<property name="suppressAllComments" value="false"></property>
</commentGenerator>
<!--数据库连接驱动类,URL,用户名、密码 -->
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/dbdemo?serverTimezone=UTC"
userId="root"
[CSDN不让发]password[CSDN不让发]="XX你的密码XX">
</jdbcConnection>
<javaTypeResolver >
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!-- 生成(实体)模型的包名和位置-->
<javaModelGenerator targetPackage="com.wdh.springbootreadwritesplitmysql.entity" targetProject="src\main\java">
<property name="enableSubPackages" value="true" />
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!--方式一 生成XML映射文件的包名和位置,这里sqlMapGenerator与javaClientGenerator配置为相同的targetPackage值,目的是让Mapper自动找到xml文件。
,无需application.properties中指定 mybatis.mapper-locations
-->
<!--<sqlMapGenerator targetPackage="com.wdh.mybatistest.mapper" targetProject="src\main\java">-->
<!--<property name="enableSubPackages" value="true" />-->
<!--</sqlMapGenerator>-->
<!--方式二 xml文件生成到resources目录com.wdh.mybatistest.mapper下保证与XXMapper.class相同目录,
resources目录自动会将XXMapper.xml生成输出到target目录,无需在pom.xml中配置resource节指定src-main-java下的XXMapper.xml文件输出到target目录
,无需application.properties中指定 mybatis.mapper-locations-->
<!--<sqlMapGenerator targetPackage="main.resources.com.wdh.mybatistest.mapper" targetProject="src">-->
<!--<property name="enableSubPackages" value="true"/>-->
<!--</sqlMapGenerator>-->
<!--方式三 xml文件生成到resources目录mapper下,不与XXMapper.class相同目录;
然后必须在application.properties中指定 mybatis.mapper-locations=classpath:wdhmappers/*.xml
mybatis.mapper-locations的值与targetPackage要对应,
例如targetPackage="main.resources.wdhmappers"即是生成到[src\main\resources\wdhmappers],对应需设置mybatis.mapper-locations=classpath:wdhmappers/*.xml
-->
<sqlMapGenerator targetPackage="main.resources.mappers" targetProject="src">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!-- 生成DAO接口的包名和位置-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.wdh.springbootreadwritesplitmysql.mapper" targetProject="src\main\java">
<property name="enableSubPackages" value="true" />
</javaClientGenerator>
<!-- 要生成的表 tableName是数据库中的表名或视图名 domainObjectName是实体类名-->
<!--<table schema="DB2ADMIN" tableName="ALLTYPES" domainObjectName="Customer" >-->
<!--<property name="useActualColumnNames" value="true"/>-->
<!--<generatedKey column="ID" sqlStatement="DB2" identity="true" />-->
<!--<columnOverride column="DATE_FIELD" property="startDate" />-->
<!--<ignoreColumn column="FRED" />-->
<!--<columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" />-->
<!--</table>-->
<table schema="dbdemo" tableName="categories"
enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"
></table>
<table schema="dbdemo" tableName="products"
enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"
></table>
</context>
</generatorConfiguration>
注意,因主库和从库结构一致,上述配置文件中定义了按主库配置连接信息,访问并生成mapper和xml.
配置完generatorConfig.xml后,在idea右侧Maven面板-本项目-Plugins-刷新-找到mybatis-generate-点击右键Run Maven Build即可生成代码。生成过程及日志见idea的控制台输出。
(pom.xml中配置mybatis-generator后需要手动在idea的maven面板左上角点击刷新按钮才会在Plugins下显示mybatis-generator)
执行之后将自动在【src\main\java\com\wdh\springbootreadwritesplitmysql\mapper】目录下生成【xx表名xxMapper.java】
自动在【src\main\resources\mappers】目录下生成【xx表名xxMapper.xml】文件。
读写分离切换的代码实现
定义主从库枚举类型
package com.wdh.springbootreadwritesplitmysql.enums;
/**
* @author WangDH
* @create 2022-11-10 13:51
*
* 数据源类型枚举
*/
public enum DBTypeEnum {
//数据库主库
MASTER,
//数据库从库
SLAVE
}
定义DBContextHolder用于访问枚举和枚举变量记录切换,DBContextHolder.java代码如下
package com.wdh.springbootreadwritesplitmysql.bean;
import com.wdh.springbootreadwritesplitmysql.enums.DBTypeEnum;
import java.util.concurrent.atomic.AtomicInteger;
/**
* @author WangDH
* @create 2022-11-10 13:59
*/
public class DBContextHolder {
private static final ThreadLocal<DBTypeEnum> contextHolder=new ThreadLocal<>();
private static final AtomicInteger counter=new AtomicInteger(-1);
public static void set(DBTypeEnum dbTypeEnum){
contextHolder.set(dbTypeEnum);
}
public static DBTypeEnum get(){
return contextHolder.get();
}
public static void master(){
set(DBTypeEnum.MASTER);
System.out.println("切换到master");
}
public static void slave(){
set(DBTypeEnum.SLAVE);
System.out.println("切换到slave");
}
}
自定义MyRoutingDataSource实现AbstractRoutingDataSource,AbstractRoutingDataSource提供了程序运行时动态切换数据源的方法。MyRoutingDataSource.java代码如下。
package com.wdh.springbootreadwritesplitmysql.bean;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;
/**
* @author WangDH
* @create 2022-11-10 13:57
*/
public class MyRoutingDataSource extends AbstractRoutingDataSource {
@Nullable
@Override
protected Object determineCurrentLookupKey() {
Object obj= DBContextHolder.get();
return obj;
}
}
定义DataSourceConfig数据源配置类初始化两个数据源和获取动态数据源。DataSourceConfig.java代码如下
package com.wdh.springbootreadwritesplitmysql.config;
import com.wdh.springbootreadwritesplitmysql.bean.MyRoutingDataSource;
import com.wdh.springbootreadwritesplitmysql.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;
/**
* @author WangDH
* @create 2022-11-10 11:42
*
*
* 自定义两个数据源,主库数据源,从库数据源
*/
@Configuration
public class DataSourceConfig {
//定义Mysql主库数据源
@Bean
@ConfigurationProperties("spring.datasource.master")//从配置文件读取spring.datasource.master开头的相关配置进行数据源初始化
public DataSource DataSourceMaster(){
return DataSourceBuilder.create().build();
}
//定义Mysql从库数据源
@Bean
@ConfigurationProperties("spring.datasource.slave")
public DataSource DataSourceSlave(){
return DataSourceBuilder.create().build();
}
@Bean
public DataSource myRoutingDataSource(
//@Qualifier 类似于@Autowired注解实现Spring依赖注入,但@Qualifier用来指定应该注入特定类型的bean,防止依赖注入冲突
@Qualifier("DataSourceMaster") DataSource dataSourceMaster,
@Qualifier("DataSourceSlave") DataSource dataSourceSlave)
{
Map<Object,Object> targetDataSource=new HashMap<>(2);
targetDataSource.put(DBTypeEnum.MASTER,dataSourceMaster);
targetDataSource.put(DBTypeEnum.SLAVE,dataSourceSlave);
MyRoutingDataSource routingDataSource=new MyRoutingDataSource();
routingDataSource.setDefaultTargetDataSource(dataSourceMaster);
routingDataSource.setTargetDataSources(targetDataSource);
return routingDataSource;
}
}
创建MyBatisConfig.java,其中构建SqlSessionFactory 对象访问动态数据源,以便这获取到mybatis操作数据库的 SqlSession 对象,进而通过mybatis操作数据库。
MyBatisConfig.java代码如下。(这里有个坑,报错是【FileNotFoundException: class path resource [mappers/*.xml] cannot be opened because it does not exist】,原因是,要写成getResources 不要误写成getResource,见注释)
package com.wdh.springbootreadwritesplitmysql.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;
/**
* @author WangDH
* @create 2022-11-10 14:26
*/
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {
@Resource(name="myRoutingDataSource") //@Resource默认按byName自动注入
private DataSource myRoutingDataSource;
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception{
///org.springframework.core.io.Resource resourceMappersXml=new PathMatchingResourcePatternResolver().getResource("classpath:mappers/*.xml");
//上述代码报错:Factory method 'sqlSessionFactory' threw exception; nested exception is org.springframework.core.NestedIOException: Failed to parse mapping resource: 'class path resource [mappers/*.xml]'; nested exception is java.io.FileNotFoundException: class path resource [mappers/*.xml] cannot be opened because it does not exist
org.springframework.core.io.Resource[] resourceMappersXml=new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml");
//注意这里是getResources 不要误写成getResource
//参考https://www.cnblogs.com/jev-0987/p/12839193.html
//getResource(): //1.从类的根路径下获取文件
//getResources(): //1.获取所有类路径下的指定文件
SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
sqlSessionFactoryBean.setMapperLocations(
resourceMappersXml
);
SqlSessionFactory sqlSessionFactory= sqlSessionFactoryBean.getObject();
return sqlSessionFactory;
}
@Bean
public PlatformTransactionManager platformTransactionManager(){
return new DataSourceTransactionManager(myRoutingDataSource);
}
}
编码两个自定义注解,用于标记方法是操作主库还是从库。
Master.java代码
package com.wdh.springbootreadwritesplitmysql.annotation;
/**
* @author WangDH
* @create 2022-11-10 14:03
*
* 定义一种注解,加此注解的方法查询主库(主数据库)
*/
public @interface Master {
}
Slave.java代码
package com.wdh.springbootreadwritesplitmysql.annotation;
/**
* @author WangDH
* @create 2022-11-11 11:25
* 定义一种注解,加此注解的方法查询从库(从数据库)
*/
public @interface Slave {
}
关键部分来了,切面编程,aop。
DataSourceAop.java代码如下
package com.wdh.springbootreadwritesplitmysql.aop;
import com.wdh.springbootreadwritesplitmysql.bean.DBContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
/**
* @author WangDH
* @create 2022-11-10 14:53
*/
@Aspect
@Component
public class DataSourceAop {
// //读切面(条件:没有Master注解)
// @Pointcut(
// "!@annotation(com.wdh.springbootreadwritesplitmysql.annotation.Master)" //如果使用!@annotation,则必须加&&(execution指定包范围,否则会导致所有方法都被切面捕获而导致启动失败
// +"&&(execution(* com.wdh.springbootreadwritesplitmysql.service.*.*(..)))"
// )
//切面切入点(条件:有Slave注解)的方法使用从库数据源
@Pointcut(
"@annotation(com.wdh.springbootreadwritesplitmysql.annotation.Slave)"
)
public void readPointcut(){}
//切面切入点,(条件:有Master注解)的方法使用主库数据源
@Pointcut(
"@annotation(com.wdh.springbootreadwritesplitmysql.annotation.Master)"
//+"&&(execution(* com.wdh.springbootreadwritesplitmysql.service.*.*(..)))"
)
public void writePointcut(){}
//Before方法,读切面前设置ThreadLocal里变量为slave
@Before("readPointcut()")
public void read(){
DBContextHolder.slave();
}
//Before方法,写切面前设置ThreadLocal里变量为master
@Before("writePointcut()")
public void write(){
DBContextHolder.master();
}
}
入口主类代码
SpringbootReadWriteSplitMysqlApplication.java
注意,增加了@MapperScan扫描指定包,以便mybatis扫描到XXXMapper.java进行自动注入。
package com.wdh.springbootreadwritesplitmysql;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.wdh.springbootreadwritesplitmysql.mapper")
public class SpringbootReadWriteSplitMysqlApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootReadWriteSplitMysqlApplication.class, args);
//参考https://blog.csdn.net/qq_37171353/article/details/111999618
//参考https://github.com/cbeann/Demooo/tree/master/springboot-readwrite-separation-deno/src
//前提:需要主从两个数据库
//启动springboot后浏览器访问【http://localhost:8080/product/hello】测试是否正常
//浏览器访问【http://localhost:8080/product/allm】读取主库
//浏览器访问【http://localhost:8080/product/alls】读取从库
//浏览器访问【http://localhost:8080/product/insert/M1336】写入主库。(如mysql做了主从复制,则自动复制到从库)
}
}
创建WebAPI实现读主库、读从库、写主库
Controller层 ProductController.java代码
package com.wdh.springbootreadwritesplitmysql.controller;
import com.wdh.springbootreadwritesplitmysql.entity.Products;
import com.wdh.springbootreadwritesplitmysql.service.ProductsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
import java.util.List;
/**
* @author WangDH
* @create 2022-11-10 16:54
*/
@RestController
@RequestMapping("/product")
public class ProductController {
@Autowired
private ProductsService productsService;
@GetMapping("/hello")
public String hello(){
return "ok,this is ProductController.";
}
@GetMapping("/allm")
public List<Products> allM(){
List<Products> list=productsService.getAllMaster();
return list;
}
@GetMapping("/alls")
public List<Products> allS(){
List<Products> list=productsService.getAllSlave();
return list;
}
@GetMapping("/insert/{name}")
public String insert(@PathVariable("name") String prdName){
Date date=new Date();
String prdNameJoin="SpringRW_"+prdName+"_"+date.toString();
Products prd=new Products();
prd.setPrdName(prdNameJoin);
prd.setPrdPrice((long)1110);
prd.setPrdDate(date);
prd.setCatId(2);
int retIns=productsService.insert(prd);//进主库,可以测试关掉从库测试能否写成功。
return "retIns="+retIns+","+(retIns>0?"ok":"fail");
}
}
Service层代码,ProductsService.java
特别注意,
Service层写主库的方法上增加了@Master注解,例如insert方法;
Service层读主库的方法上增加了@Master注解,例如getAllMaster();
Service层读从库的方法上增加了@Slave注解,例如getAllSlave();
通过添加不同注解的方式标记哪个方法操作主库,哪个方法操作从库。当然也有博友在aop中通过表达式方式匹配不同方法名,也可。DataSourceAop.java的注释中也有。
package com.wdh.springbootreadwritesplitmysql.service;
import com.wdh.springbootreadwritesplitmysql.annotation.Master;
import com.wdh.springbootreadwritesplitmysql.annotation.Slave;
import com.wdh.springbootreadwritesplitmysql.entity.Products;
import com.wdh.springbootreadwritesplitmysql.mapper.ProductsMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author WangDH
* @create 2022-11-10 16:32
*/
@Service
public class ProductsService {
//注意,需要所有Service层访问数据库的方法必须增加@Slave或@Master注解,以标明是操作从库还是操作主库
@Autowired
private ProductsMapper productsMapper;
@Slave
public List<Products> getAllSlave(){
List<Products> listSlave= productsMapper.selectAll();
return listSlave;
}
//标记为由主库读
@Master
public List<Products> getAllMaster(){
List<Products> list= productsMapper.selectAll();
return list;
}
//标记为进入主库写
@Master
public int insert(Products record){
return productsMapper.insert(record);
}
}
Mapper层修改,ProductsMapper.java是自动生成的,由于没有查询全表的方法,这里手动增加一个方法
List<Products> selectAll();
,同步需要修改ProductsMapper.xml。
修改部分如下
<select id="selectAll" resultMap="BaseResultMap" >
select
<include refid="Base_Column_List" />
from products
</select>
ProductsMapper.java完整代码如下;
package com.wdh.springbootreadwritesplitmysql.mapper;
import com.wdh.springbootreadwritesplitmysql.entity.Products;
import java.util.List;
public interface ProductsMapper {
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table products
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
int deleteByPrimaryKey(Integer prdId);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table products
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
int insert(Products record);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table products
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
int insertSelective(Products record);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table products
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
Products selectByPrimaryKey(Integer prdId);
//wdh自定义增加的查询函数,需要ProductsMapper.xml有配置selectAll查询sql
List<Products> selectAll();
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table products
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
int updateByPrimaryKeySelective(Products record);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table products
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
int updateByPrimaryKey(Products record);
}
ProductsMapper.xml全文
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.wdh.springbootreadwritesplitmysql.mapper.ProductsMapper" >
<resultMap id="BaseResultMap" type="com.wdh.springbootreadwritesplitmysql.entity.Products" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Thu Nov 10 16:13:23 CST 2022.
-->
<id column="prd_id" property="prdId" jdbcType="INTEGER" />
<result column="prd_name" property="prdName" jdbcType="VARCHAR" />
<result column="prd_price" property="prdPrice" jdbcType="DECIMAL" />
<result column="prd_date" property="prdDate" jdbcType="TIMESTAMP" />
<result column="cat_id" property="catId" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Thu Nov 10 16:13:23 CST 2022.
-->
prd_id, prd_name, prd_price, prd_date, cat_id
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Thu Nov 10 16:13:23 CST 2022.
-->
select
<include refid="Base_Column_List" />
from products
where prd_id = #{prdId,jdbcType=INTEGER}
</select>
<!--wdh 自定义查询全表数据-->
<select id="selectAll" resultMap="BaseResultMap" >
select
<include refid="Base_Column_List" />
from products
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Thu Nov 10 16:13:23 CST 2022.
-->
delete from products
where prd_id = #{prdId,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.wdh.springbootreadwritesplitmysql.entity.Products" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Thu Nov 10 16:13:23 CST 2022.
-->
insert into products (prd_id, prd_name, prd_price,
prd_date, cat_id)
values (#{prdId,jdbcType=INTEGER}, #{prdName,jdbcType=VARCHAR}, #{prdPrice,jdbcType=DECIMAL},
#{prdDate,jdbcType=TIMESTAMP}, #{catId,jdbcType=INTEGER})
</insert>
<insert id="insertSelective" parameterType="com.wdh.springbootreadwritesplitmysql.entity.Products" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Thu Nov 10 16:13:23 CST 2022.
-->
insert into products
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="prdId != null" >
prd_id,
</if>
<if test="prdName != null" >
prd_name,
</if>
<if test="prdPrice != null" >
prd_price,
</if>
<if test="prdDate != null" >
prd_date,
</if>
<if test="catId != null" >
cat_id,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="prdId != null" >
#{prdId,jdbcType=INTEGER},
</if>
<if test="prdName != null" >
#{prdName,jdbcType=VARCHAR},
</if>
<if test="prdPrice != null" >
#{prdPrice,jdbcType=DECIMAL},
</if>
<if test="prdDate != null" >
#{prdDate,jdbcType=TIMESTAMP},
</if>
<if test="catId != null" >
#{catId,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.wdh.springbootreadwritesplitmysql.entity.Products" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Thu Nov 10 16:13:23 CST 2022.
-->
update products
<set >
<if test="prdName != null" >
prd_name = #{prdName,jdbcType=VARCHAR},
</if>
<if test="prdPrice != null" >
prd_price = #{prdPrice,jdbcType=DECIMAL},
</if>
<if test="prdDate != null" >
prd_date = #{prdDate,jdbcType=TIMESTAMP},
</if>
<if test="catId != null" >
cat_id = #{catId,jdbcType=INTEGER},
</if>
</set>
where prd_id = #{prdId,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.wdh.springbootreadwritesplitmysql.entity.Products" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Thu Nov 10 16:13:23 CST 2022.
-->
update products
set prd_name = #{prdName,jdbcType=VARCHAR},
prd_price = #{prdPrice,jdbcType=DECIMAL},
prd_date = #{prdDate,jdbcType=TIMESTAMP},
cat_id = #{catId,jdbcType=INTEGER}
where prd_id = #{prdId,jdbcType=INTEGER}
</update>
</mapper>
entity层的Products.java代码是自动生成的,代码未变,也附上。
package com.wdh.springbootreadwritesplitmysql.entity;
import lombok.Data;
import java.util.Date;
@Data
public class Products {
/**
* This field was generated by MyBatis Generator.
* This field corresponds to the database column products.prd_id
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
private Integer prdId;
/**
* This field was generated by MyBatis Generator.
* This field corresponds to the database column products.prd_name
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
private String prdName;
/**
* This field was generated by MyBatis Generator.
* This field corresponds to the database column products.prd_price
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
private Long prdPrice;
/**
* This field was generated by MyBatis Generator.
* This field corresponds to the database column products.prd_date
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
private Date prdDate;
/**
* This field was generated by MyBatis Generator.
* This field corresponds to the database column products.cat_id
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
private Integer catId;
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column products.prd_id
*
* @return the value of products.prd_id
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public Integer getPrdId() {
return prdId;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column products.prd_id
*
* @param prdId the value for products.prd_id
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public void setPrdId(Integer prdId) {
this.prdId = prdId;
}
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column products.prd_name
*
* @return the value of products.prd_name
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public String getPrdName() {
return prdName;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column products.prd_name
*
* @param prdName the value for products.prd_name
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public void setPrdName(String prdName) {
this.prdName = prdName == null ? null : prdName.trim();
}
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column products.prd_price
*
* @return the value of products.prd_price
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public Long getPrdPrice() {
return prdPrice;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column products.prd_price
*
* @param prdPrice the value for products.prd_price
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public void setPrdPrice(Long prdPrice) {
this.prdPrice = prdPrice;
}
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column products.prd_date
*
* @return the value of products.prd_date
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public Date getPrdDate() {
return prdDate;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column products.prd_date
*
* @param prdDate the value for products.prd_date
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public void setPrdDate(Date prdDate) {
this.prdDate = prdDate;
}
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column products.cat_id
*
* @return the value of products.cat_id
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public Integer getCatId() {
return catId;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column products.cat_id
*
* @param catId the value for products.cat_id
*
* @mbggenerated Thu Nov 10 16:13:23 CST 2022
*/
public void setCatId(Integer catId) {
this.catId = catId;
}
}
验证读写分离
背景,主库和从库中Products表的数据行数是不一样的。主库此表内记录多,从库此表内记录少。
如果执行方法可以根据@Master或@Slave实现读写分离,目的即为达成。
验证前准备:主从两个数据库已启动且可访问。
启动springboot,IDEA控制台打印的成功日志如下
"C:\Program Files\Java\jdk1.8.0_111\bin\java.exe" -XX:TieredStopAtLevel=1 -noverify -Dspring.output.ansi.enabled=always -Dcom.sun.management.jmxremote -Dspring.liveBeansView.mbeanDomain -Dspring.application.admin.enabled=true "-javaagent:D:\JavaDevEnv\JetBrains\IntelliJ IDEA 2018.3.6\lib\idea_rt.jar=62267:D:\JavaDevEnv\JetBrains\IntelliJ IDEA 2018.3.6\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_111\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\rt.jar;D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql\target\classes;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-web\2.3.7.RELEASE\spring-boot-starter-web-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter\2.3.7.RELEASE\spring-boot-starter-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot\2.3.7.RELEASE\spring-boot-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-autoconfigure\2.3.7.RELEASE\spring-boot-autoconfigure-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-logging\2.3.7.RELEASE\spring-boot-starter-logging-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\ch\qos\logback\logback-classic\1.2.3\logback-classic-1.2.3.jar;D:\JavaDevEnv\maven_repository\ch\qos\logback\logback-core\1.2.3\logback-core-1.2.3.jar;D:\JavaDevEnv\maven_repository\org\apache\logging\log4j\log4j-to-slf4j\2.13.3\log4j-to-slf4j-2.13.3.jar;D:\JavaDevEnv\maven_repository\org\apache\logging\log4j\log4j-api\2.13.3\log4j-api-2.13.3.jar;D:\JavaDevEnv\maven_repository\org\slf4j\jul-to-slf4j\1.7.30\jul-to-slf4j-1.7.30.jar;D:\JavaDevEnv\maven_repository\jakarta\annotation\jakarta.annotation-api\1.3.5\jakarta.annotation-api-1.3.5.jar;D:\JavaDevEnv\maven_repository\org\yaml\snakeyaml\1.26\snakeyaml-1.26.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-json\2.3.7.RELEASE\spring-boot-starter-json-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-databind\2.11.3\jackson-databind-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-annotations\2.11.3\jackson-annotations-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-core\2.11.3\jackson-core-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.11.3\jackson-datatype-jdk8-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.11.3\jackson-datatype-jsr310-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\module\jackson-module-parameter-names\2.11.3\jackson-module-parameter-names-2.11.3.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-tomcat\2.3.7.RELEASE\spring-boot-starter-tomcat-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\apache\tomcat\embed\tomcat-embed-core\9.0.41\tomcat-embed-core-9.0.41.jar;D:\JavaDevEnv\maven_repository\org\glassfish\jakarta.el\3.0.3\jakarta.el-3.0.3.jar;D:\JavaDevEnv\maven_repository\org\apache\tomcat\embed\tomcat-embed-websocket\9.0.41\tomcat-embed-websocket-9.0.41.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-web\5.2.12.RELEASE\spring-web-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-beans\5.2.12.RELEASE\spring-beans-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-webmvc\5.2.12.RELEASE\spring-webmvc-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-context\5.2.12.RELEASE\spring-context-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-expression\5.2.12.RELEASE\spring-expression-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\projectlombok\lombok\1.18.16\lombok-1.18.16.jar;D:\JavaDevEnv\maven_repository\org\apache\commons\commons-lang3\3.10\commons-lang3-3.10.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-aop\2.3.7.RELEASE\spring-boot-starter-aop-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-aop\5.2.12.RELEASE\spring-aop-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\aspectj\aspectjweaver\1.9.6\aspectjweaver-1.9.6.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-data-jdbc\2.3.7.RELEASE\spring-boot-starter-data-jdbc-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-jdbc\2.0.6.RELEASE\spring-data-jdbc-2.0.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-relational\2.0.6.RELEASE\spring-data-relational-2.0.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-commons\2.3.6.RELEASE\spring-data-commons-2.3.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-tx\5.2.12.RELEASE\spring-tx-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\slf4j\slf4j-api\1.7.30\slf4j-api-1.7.30.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-jdbc\2.3.7.RELEASE\spring-boot-starter-jdbc-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\com\zaxxer\HikariCP\3.4.5\HikariCP-3.4.5.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-jdbc\5.2.12.RELEASE\spring-jdbc-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\mybatis\spring\boot\mybatis-spring-boot-starter\2.1.4\mybatis-spring-boot-starter-2.1.4.jar;D:\JavaDevEnv\maven_repository\org\mybatis\spring\boot\mybatis-spring-boot-autoconfigure\2.1.4\mybatis-spring-boot-autoconfigure-2.1.4.jar;D:\JavaDevEnv\maven_repository\org\mybatis\mybatis\3.5.6\mybatis-3.5.6.jar;D:\JavaDevEnv\maven_repository\org\mybatis\mybatis-spring\2.0.6\mybatis-spring-2.0.6.jar;D:\JavaDevEnv\maven_repository\Mysql\mysql-connector-java\8.0.22\mysql-connector-java-8.0.22.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-core\5.2.12.RELEASE\spring-core-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-jcl\5.2.12.RELEASE\spring-jcl-5.2.12.RELEASE.jar" com.wdh.springbootreadwritesplitmysql.SpringbootReadWriteSplitMysqlApplication
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.3.7.RELEASE)
2022-11-11 16:43:26.762 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : Starting SpringbootReadWriteSplitMysqlApplication on 14JPYI7CBESDNFK with PID 2452 (D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql\target\classes started by Administrator in D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql)
2022-11-11 16:43:26.762 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : No active profile set, falling back to default profiles: default
2022-11-11 16:43:28.525 INFO 2452 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2022-11-11 16:43:28.541 INFO 2452 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2022-11-11 16:43:28.541 INFO 2452 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.41]
2022-11-11 16:43:28.606 INFO 2452 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2022-11-11 16:43:28.606 INFO 2452 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1781 ms
2022-11-11 16:43:30.028 INFO 2452 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2022-11-11 16:43:30.511 INFO 2452 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2022-11-11 16:43:30.511 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : Started SpringbootReadWriteSplitMysqlApplication in 4.093 seconds (JVM running for 4.693)
浏览器访问【http://localhost:8080/product/hello】测试未访问数据库时,仅rest服务是否正常,以下是正常截图。
浏览器访问【http://localhost:8080/product/allm】读取主库,成功图示如下
读取主库,IDEA控制台日志如下。可以看到在访问数据库时DBContextHolder.java打印出”切换到master“的日志。
"C:\Program Files\Java\jdk1.8.0_111\bin\java.exe" -XX:TieredStopAtLevel=1 -noverify -Dspring.output.ansi.enabled=always -Dcom.sun.management.jmxremote -Dspring.liveBeansView.mbeanDomain -Dspring.application.admin.enabled=true "-javaagent:D:\JavaDevEnv\JetBrains\IntelliJ IDEA 2018.3.6\lib\idea_rt.jar=62267:D:\JavaDevEnv\JetBrains\IntelliJ IDEA 2018.3.6\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_111\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\rt.jar;D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql\target\classes;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-web\2.3.7.RELEASE\spring-boot-starter-web-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter\2.3.7.RELEASE\spring-boot-starter-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot\2.3.7.RELEASE\spring-boot-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-autoconfigure\2.3.7.RELEASE\spring-boot-autoconfigure-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-logging\2.3.7.RELEASE\spring-boot-starter-logging-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\ch\qos\logback\logback-classic\1.2.3\logback-classic-1.2.3.jar;D:\JavaDevEnv\maven_repository\ch\qos\logback\logback-core\1.2.3\logback-core-1.2.3.jar;D:\JavaDevEnv\maven_repository\org\apache\logging\log4j\log4j-to-slf4j\2.13.3\log4j-to-slf4j-2.13.3.jar;D:\JavaDevEnv\maven_repository\org\apache\logging\log4j\log4j-api\2.13.3\log4j-api-2.13.3.jar;D:\JavaDevEnv\maven_repository\org\slf4j\jul-to-slf4j\1.7.30\jul-to-slf4j-1.7.30.jar;D:\JavaDevEnv\maven_repository\jakarta\annotation\jakarta.annotation-api\1.3.5\jakarta.annotation-api-1.3.5.jar;D:\JavaDevEnv\maven_repository\org\yaml\snakeyaml\1.26\snakeyaml-1.26.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-json\2.3.7.RELEASE\spring-boot-starter-json-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-databind\2.11.3\jackson-databind-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-annotations\2.11.3\jackson-annotations-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-core\2.11.3\jackson-core-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.11.3\jackson-datatype-jdk8-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.11.3\jackson-datatype-jsr310-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\module\jackson-module-parameter-names\2.11.3\jackson-module-parameter-names-2.11.3.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-tomcat\2.3.7.RELEASE\spring-boot-starter-tomcat-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\apache\tomcat\embed\tomcat-embed-core\9.0.41\tomcat-embed-core-9.0.41.jar;D:\JavaDevEnv\maven_repository\org\glassfish\jakarta.el\3.0.3\jakarta.el-3.0.3.jar;D:\JavaDevEnv\maven_repository\org\apache\tomcat\embed\tomcat-embed-websocket\9.0.41\tomcat-embed-websocket-9.0.41.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-web\5.2.12.RELEASE\spring-web-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-beans\5.2.12.RELEASE\spring-beans-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-webmvc\5.2.12.RELEASE\spring-webmvc-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-context\5.2.12.RELEASE\spring-context-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-expression\5.2.12.RELEASE\spring-expression-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\projectlombok\lombok\1.18.16\lombok-1.18.16.jar;D:\JavaDevEnv\maven_repository\org\apache\commons\commons-lang3\3.10\commons-lang3-3.10.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-aop\2.3.7.RELEASE\spring-boot-starter-aop-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-aop\5.2.12.RELEASE\spring-aop-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\aspectj\aspectjweaver\1.9.6\aspectjweaver-1.9.6.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-data-jdbc\2.3.7.RELEASE\spring-boot-starter-data-jdbc-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-jdbc\2.0.6.RELEASE\spring-data-jdbc-2.0.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-relational\2.0.6.RELEASE\spring-data-relational-2.0.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-commons\2.3.6.RELEASE\spring-data-commons-2.3.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-tx\5.2.12.RELEASE\spring-tx-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\slf4j\slf4j-api\1.7.30\slf4j-api-1.7.30.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-jdbc\2.3.7.RELEASE\spring-boot-starter-jdbc-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\com\zaxxer\HikariCP\3.4.5\HikariCP-3.4.5.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-jdbc\5.2.12.RELEASE\spring-jdbc-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\mybatis\spring\boot\mybatis-spring-boot-starter\2.1.4\mybatis-spring-boot-starter-2.1.4.jar;D:\JavaDevEnv\maven_repository\org\mybatis\spring\boot\mybatis-spring-boot-autoconfigure\2.1.4\mybatis-spring-boot-autoconfigure-2.1.4.jar;D:\JavaDevEnv\maven_repository\org\mybatis\mybatis\3.5.6\mybatis-3.5.6.jar;D:\JavaDevEnv\maven_repository\org\mybatis\mybatis-spring\2.0.6\mybatis-spring-2.0.6.jar;D:\JavaDevEnv\maven_repository\Mysql\mysql-connector-java\8.0.22\mysql-connector-java-8.0.22.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-core\5.2.12.RELEASE\spring-core-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-jcl\5.2.12.RELEASE\spring-jcl-5.2.12.RELEASE.jar" com.wdh.springbootreadwritesplitmysql.SpringbootReadWriteSplitMysqlApplication
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.3.7.RELEASE)
2022-11-11 16:43:26.762 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : Starting SpringbootReadWriteSplitMysqlApplication on 14JPYI7CBESDNFK with PID 2452 (D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql\target\classes started by Administrator in D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql)
2022-11-11 16:43:26.762 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : No active profile set, falling back to default profiles: default
2022-11-11 16:43:28.525 INFO 2452 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2022-11-11 16:43:28.541 INFO 2452 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2022-11-11 16:43:28.541 INFO 2452 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.41]
2022-11-11 16:43:28.606 INFO 2452 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2022-11-11 16:43:28.606 INFO 2452 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1781 ms
2022-11-11 16:43:30.028 INFO 2452 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2022-11-11 16:43:30.511 INFO 2452 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2022-11-11 16:43:30.511 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : Started SpringbootReadWriteSplitMysqlApplication in 4.093 seconds (JVM running for 4.693)
2022-11-11 16:44:12.596 INFO 2452 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2022-11-11 16:44:12.596 INFO 2452 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2022-11-11 16:44:12.610 INFO 2452 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 14 ms
切换到master
2022-11-11 16:46:07.003 INFO 2452 --- [nio-8080-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-11-11 16:46:07.146 INFO 2452 --- [nio-8080-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
浏览器访问【http://localhost:8080/product/alls】读取从库。成功图示如下。
读取从库,IDEA控制台日志如下。可以看到在访问数据库时DBContextHolder.java打印出”切换到slave“的日志。
"C:\Program Files\Java\jdk1.8.0_111\bin\java.exe" -XX:TieredStopAtLevel=1 -noverify -Dspring.output.ansi.enabled=always -Dcom.sun.management.jmxremote -Dspring.liveBeansView.mbeanDomain -Dspring.application.admin.enabled=true "-javaagent:D:\JavaDevEnv\JetBrains\IntelliJ IDEA 2018.3.6\lib\idea_rt.jar=62267:D:\JavaDevEnv\JetBrains\IntelliJ IDEA 2018.3.6\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_111\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\rt.jar;D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql\target\classes;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-web\2.3.7.RELEASE\spring-boot-starter-web-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter\2.3.7.RELEASE\spring-boot-starter-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot\2.3.7.RELEASE\spring-boot-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-autoconfigure\2.3.7.RELEASE\spring-boot-autoconfigure-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-logging\2.3.7.RELEASE\spring-boot-starter-logging-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\ch\qos\logback\logback-classic\1.2.3\logback-classic-1.2.3.jar;D:\JavaDevEnv\maven_repository\ch\qos\logback\logback-core\1.2.3\logback-core-1.2.3.jar;D:\JavaDevEnv\maven_repository\org\apache\logging\log4j\log4j-to-slf4j\2.13.3\log4j-to-slf4j-2.13.3.jar;D:\JavaDevEnv\maven_repository\org\apache\logging\log4j\log4j-api\2.13.3\log4j-api-2.13.3.jar;D:\JavaDevEnv\maven_repository\org\slf4j\jul-to-slf4j\1.7.30\jul-to-slf4j-1.7.30.jar;D:\JavaDevEnv\maven_repository\jakarta\annotation\jakarta.annotation-api\1.3.5\jakarta.annotation-api-1.3.5.jar;D:\JavaDevEnv\maven_repository\org\yaml\snakeyaml\1.26\snakeyaml-1.26.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-json\2.3.7.RELEASE\spring-boot-starter-json-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-databind\2.11.3\jackson-databind-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-annotations\2.11.3\jackson-annotations-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-core\2.11.3\jackson-core-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.11.3\jackson-datatype-jdk8-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.11.3\jackson-datatype-jsr310-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\module\jackson-module-parameter-names\2.11.3\jackson-module-parameter-names-2.11.3.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-tomcat\2.3.7.RELEASE\spring-boot-starter-tomcat-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\apache\tomcat\embed\tomcat-embed-core\9.0.41\tomcat-embed-core-9.0.41.jar;D:\JavaDevEnv\maven_repository\org\glassfish\jakarta.el\3.0.3\jakarta.el-3.0.3.jar;D:\JavaDevEnv\maven_repository\org\apache\tomcat\embed\tomcat-embed-websocket\9.0.41\tomcat-embed-websocket-9.0.41.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-web\5.2.12.RELEASE\spring-web-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-beans\5.2.12.RELEASE\spring-beans-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-webmvc\5.2.12.RELEASE\spring-webmvc-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-context\5.2.12.RELEASE\spring-context-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-expression\5.2.12.RELEASE\spring-expression-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\projectlombok\lombok\1.18.16\lombok-1.18.16.jar;D:\JavaDevEnv\maven_repository\org\apache\commons\commons-lang3\3.10\commons-lang3-3.10.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-aop\2.3.7.RELEASE\spring-boot-starter-aop-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-aop\5.2.12.RELEASE\spring-aop-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\aspectj\aspectjweaver\1.9.6\aspectjweaver-1.9.6.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-data-jdbc\2.3.7.RELEASE\spring-boot-starter-data-jdbc-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-jdbc\2.0.6.RELEASE\spring-data-jdbc-2.0.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-relational\2.0.6.RELEASE\spring-data-relational-2.0.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-commons\2.3.6.RELEASE\spring-data-commons-2.3.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-tx\5.2.12.RELEASE\spring-tx-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\slf4j\slf4j-api\1.7.30\slf4j-api-1.7.30.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-jdbc\2.3.7.RELEASE\spring-boot-starter-jdbc-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\com\zaxxer\HikariCP\3.4.5\HikariCP-3.4.5.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-jdbc\5.2.12.RELEASE\spring-jdbc-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\mybatis\spring\boot\mybatis-spring-boot-starter\2.1.4\mybatis-spring-boot-starter-2.1.4.jar;D:\JavaDevEnv\maven_repository\org\mybatis\spring\boot\mybatis-spring-boot-autoconfigure\2.1.4\mybatis-spring-boot-autoconfigure-2.1.4.jar;D:\JavaDevEnv\maven_repository\org\mybatis\mybatis\3.5.6\mybatis-3.5.6.jar;D:\JavaDevEnv\maven_repository\org\mybatis\mybatis-spring\2.0.6\mybatis-spring-2.0.6.jar;D:\JavaDevEnv\maven_repository\Mysql\mysql-connector-java\8.0.22\mysql-connector-java-8.0.22.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-core\5.2.12.RELEASE\spring-core-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-jcl\5.2.12.RELEASE\spring-jcl-5.2.12.RELEASE.jar" com.wdh.springbootreadwritesplitmysql.SpringbootReadWriteSplitMysqlApplication
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.3.7.RELEASE)
2022-11-11 16:43:26.762 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : Starting SpringbootReadWriteSplitMysqlApplication on 14JPYI7CBESDNFK with PID 2452 (D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql\target\classes started by Administrator in D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql)
2022-11-11 16:43:26.762 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : No active profile set, falling back to default profiles: default
2022-11-11 16:43:28.525 INFO 2452 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2022-11-11 16:43:28.541 INFO 2452 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2022-11-11 16:43:28.541 INFO 2452 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.41]
2022-11-11 16:43:28.606 INFO 2452 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2022-11-11 16:43:28.606 INFO 2452 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1781 ms
2022-11-11 16:43:30.028 INFO 2452 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2022-11-11 16:43:30.511 INFO 2452 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2022-11-11 16:43:30.511 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : Started SpringbootReadWriteSplitMysqlApplication in 4.093 seconds (JVM running for 4.693)
2022-11-11 16:44:12.596 INFO 2452 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2022-11-11 16:44:12.596 INFO 2452 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2022-11-11 16:44:12.610 INFO 2452 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 14 ms
切换到master
2022-11-11 16:46:07.003 INFO 2452 --- [nio-8080-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-11-11 16:46:07.146 INFO 2452 --- [nio-8080-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
切换到slave
2022-11-11 16:49:04.559 INFO 2452 --- [nio-8080-exec-7] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting...
2022-11-11 16:49:04.660 INFO 2452 --- [nio-8080-exec-7] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed.
浏览器访问【http://localhost:8080/product/insert/M1651】写入主库。(因本示例mysql做了主从复制,所以主库的变更自动复制到从库了)
日志如下,可以看到在调用写操作时自动切换到主库了。
"C:\Program Files\Java\jdk1.8.0_111\bin\java.exe" -XX:TieredStopAtLevel=1 -noverify -Dspring.output.ansi.enabled=always -Dcom.sun.management.jmxremote -Dspring.liveBeansView.mbeanDomain -Dspring.application.admin.enabled=true "-javaagent:D:\JavaDevEnv\JetBrains\IntelliJ IDEA 2018.3.6\lib\idea_rt.jar=62267:D:\JavaDevEnv\JetBrains\IntelliJ IDEA 2018.3.6\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_111\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_111\jre\lib\rt.jar;D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql\target\classes;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-web\2.3.7.RELEASE\spring-boot-starter-web-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter\2.3.7.RELEASE\spring-boot-starter-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot\2.3.7.RELEASE\spring-boot-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-autoconfigure\2.3.7.RELEASE\spring-boot-autoconfigure-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-logging\2.3.7.RELEASE\spring-boot-starter-logging-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\ch\qos\logback\logback-classic\1.2.3\logback-classic-1.2.3.jar;D:\JavaDevEnv\maven_repository\ch\qos\logback\logback-core\1.2.3\logback-core-1.2.3.jar;D:\JavaDevEnv\maven_repository\org\apache\logging\log4j\log4j-to-slf4j\2.13.3\log4j-to-slf4j-2.13.3.jar;D:\JavaDevEnv\maven_repository\org\apache\logging\log4j\log4j-api\2.13.3\log4j-api-2.13.3.jar;D:\JavaDevEnv\maven_repository\org\slf4j\jul-to-slf4j\1.7.30\jul-to-slf4j-1.7.30.jar;D:\JavaDevEnv\maven_repository\jakarta\annotation\jakarta.annotation-api\1.3.5\jakarta.annotation-api-1.3.5.jar;D:\JavaDevEnv\maven_repository\org\yaml\snakeyaml\1.26\snakeyaml-1.26.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-json\2.3.7.RELEASE\spring-boot-starter-json-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-databind\2.11.3\jackson-databind-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-annotations\2.11.3\jackson-annotations-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\core\jackson-core\2.11.3\jackson-core-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.11.3\jackson-datatype-jdk8-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.11.3\jackson-datatype-jsr310-2.11.3.jar;D:\JavaDevEnv\maven_repository\com\fasterxml\jackson\module\jackson-module-parameter-names\2.11.3\jackson-module-parameter-names-2.11.3.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-tomcat\2.3.7.RELEASE\spring-boot-starter-tomcat-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\apache\tomcat\embed\tomcat-embed-core\9.0.41\tomcat-embed-core-9.0.41.jar;D:\JavaDevEnv\maven_repository\org\glassfish\jakarta.el\3.0.3\jakarta.el-3.0.3.jar;D:\JavaDevEnv\maven_repository\org\apache\tomcat\embed\tomcat-embed-websocket\9.0.41\tomcat-embed-websocket-9.0.41.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-web\5.2.12.RELEASE\spring-web-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-beans\5.2.12.RELEASE\spring-beans-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-webmvc\5.2.12.RELEASE\spring-webmvc-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-context\5.2.12.RELEASE\spring-context-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-expression\5.2.12.RELEASE\spring-expression-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\projectlombok\lombok\1.18.16\lombok-1.18.16.jar;D:\JavaDevEnv\maven_repository\org\apache\commons\commons-lang3\3.10\commons-lang3-3.10.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-aop\2.3.7.RELEASE\spring-boot-starter-aop-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-aop\5.2.12.RELEASE\spring-aop-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\aspectj\aspectjweaver\1.9.6\aspectjweaver-1.9.6.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-data-jdbc\2.3.7.RELEASE\spring-boot-starter-data-jdbc-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-jdbc\2.0.6.RELEASE\spring-data-jdbc-2.0.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-relational\2.0.6.RELEASE\spring-data-relational-2.0.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\data\spring-data-commons\2.3.6.RELEASE\spring-data-commons-2.3.6.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-tx\5.2.12.RELEASE\spring-tx-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\slf4j\slf4j-api\1.7.30\slf4j-api-1.7.30.jar;D:\JavaDevEnv\maven_repository\org\springframework\boot\spring-boot-starter-jdbc\2.3.7.RELEASE\spring-boot-starter-jdbc-2.3.7.RELEASE.jar;D:\JavaDevEnv\maven_repository\com\zaxxer\HikariCP\3.4.5\HikariCP-3.4.5.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-jdbc\5.2.12.RELEASE\spring-jdbc-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\mybatis\spring\boot\mybatis-spring-boot-starter\2.1.4\mybatis-spring-boot-starter-2.1.4.jar;D:\JavaDevEnv\maven_repository\org\mybatis\spring\boot\mybatis-spring-boot-autoconfigure\2.1.4\mybatis-spring-boot-autoconfigure-2.1.4.jar;D:\JavaDevEnv\maven_repository\org\mybatis\mybatis\3.5.6\mybatis-3.5.6.jar;D:\JavaDevEnv\maven_repository\org\mybatis\mybatis-spring\2.0.6\mybatis-spring-2.0.6.jar;D:\JavaDevEnv\maven_repository\Mysql\mysql-connector-java\8.0.22\mysql-connector-java-8.0.22.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-core\5.2.12.RELEASE\spring-core-5.2.12.RELEASE.jar;D:\JavaDevEnv\maven_repository\org\springframework\spring-jcl\5.2.12.RELEASE\spring-jcl-5.2.12.RELEASE.jar" com.wdh.springbootreadwritesplitmysql.SpringbootReadWriteSplitMysqlApplication
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.3.7.RELEASE)
2022-11-11 16:43:26.762 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : Starting SpringbootReadWriteSplitMysqlApplication on 14JPYI7CBESDNFK with PID 2452 (D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql\target\classes started by Administrator in D:\JavaWorkspace\IdeaProjects\SpringInAction\springboot-read-write-split-mysql)
2022-11-11 16:43:26.762 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : No active profile set, falling back to default profiles: default
2022-11-11 16:43:28.525 INFO 2452 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2022-11-11 16:43:28.541 INFO 2452 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2022-11-11 16:43:28.541 INFO 2452 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.41]
2022-11-11 16:43:28.606 INFO 2452 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2022-11-11 16:43:28.606 INFO 2452 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1781 ms
2022-11-11 16:43:30.028 INFO 2452 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2022-11-11 16:43:30.511 INFO 2452 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2022-11-11 16:43:30.511 INFO 2452 --- [ main] SpringbootReadWriteSplitMysqlApplication : Started SpringbootReadWriteSplitMysqlApplication in 4.093 seconds (JVM running for 4.693)
2022-11-11 16:44:12.596 INFO 2452 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2022-11-11 16:44:12.596 INFO 2452 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2022-11-11 16:44:12.610 INFO 2452 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 14 ms
切换到master
2022-11-11 16:46:07.003 INFO 2452 --- [nio-8080-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-11-11 16:46:07.146 INFO 2452 --- [nio-8080-exec-4] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
切换到slave
2022-11-11 16:49:04.559 INFO 2452 --- [nio-8080-exec-7] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting...
2022-11-11 16:49:04.660 INFO 2452 --- [nio-8080-exec-7] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed.
切换到master
再查询主库,ok,新增的数据已可查出。
再查询从库(在Mysql实现了主从复制的场景下,主库新增的数据已经自动同步到从库了),ok
拓展读写分离(写一个库,读多个库)
思路:
1.Mysql先实现一主多从,
2.然后用Nginx为多个Mysql服务器建立反向代理在多个mysql上轮询实现负载均衡。例如Nginx将IP:3333端口暴露为多个Mysql负载均衡后对外的统一端口。
3.将Nginx暴露出的对外数据访问端口IP:3333配置到springboot的application.propertities中读数据库的连接[spring.datasource.slave.jdbcUrl]中即可。
4.上述仅改个springboot配置文件即可实现【mysql一主一从+springboot读写分离】或【mysql一主多从+springboot读写分离】,简单易用。
mysql主从复制及Nginx读写分离可参考本人其他博文
MySql 主从复制 双机热备 笔记_既择远方-风雨兼程的博客-CSDN博客
Nginx 负载均衡 初步配置&验证 笔记_既择远方-风雨兼程的博客-CSDN博客
Nginx Mysql负载均衡 初步配置及验证 笔记_既择远方-风雨兼程的博客-CSDN博客
读写分离参考资料