说明:读写分离,指把数据库的操作分为读操作、写操作(更新、新增、删除),在多数据库实例(如主从结构)下,把读操作和写操作访问的数据库分开,以此缓解单数据库的压力。
读写分离实现的前提,需要数据库之间能同步数据,数据不一致,读写分离没有意义。数据同步可参考下面文章:
而实现读写分离,可以引入第三方框架,如dynamic-datasource,可参考下面这篇文章:
听闻mybatis-plus也可实现,没有试过。本文介绍,如何通过自定义数据源实现读写分离;
环境搭建
创建一个Spring Boot项目,pom文件如下:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.12</version>
<relativePath/>
</parent>
<groupId>com.hezy</groupId>
<artifactId>read_write_separation_demo</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.3.0</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
</dependencies>
</project>
pojo对象
import lombok.Data;
import java.io.Serializable;
@Data
public class User implements Serializable {
private Integer id;
private String username;
private String password;
}
Controller,创建两个接口,一个查主库(读写库),一个查从库(读库)
import com.hezy.pojo.User;
import com.hezy.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("master")
public List<User> getUserAllFromMaster() {
return userService.getUserAllFromMaster();
}
@GetMapping("slave")
public List<User> getUserAllFromSlave() {
return userService.getUserAllFromSlave();
}
}
重点是下面,分别创建两个数据源,两个SqlSession,创建数据源的时候读取不同的数据库配置,如下:
(主数据库)
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
import java.util.Objects;
@Configuration
public class MasterSqlSession {
@Value("${spring.datasource.driver-class-name}")
private String driverClass;
@Value("${spring.datasource.url}")
private String url;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Bean("dataSource")
@Primary
public DataSource getDataSource() {
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName(driverClass);
hikariConfig.setJdbcUrl(url);
hikariConfig.setUsername(username);
hikariConfig.setPassword(password);
return new HikariDataSource(hikariConfig);
}
@Bean("sqlSession")
@Primary
public SqlSession getSqlSession(@Qualifier("dataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setMapUnderscoreToCamelCase(true);
sessionFactory.setConfiguration(configuration);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath:com/mapper/*.xml"));
sessionFactory.setVfs(SpringBootVFS.class);
return new SqlSessionTemplate(Objects.requireNonNull(sessionFactory.getObject()));
}
}
(从数据库)
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
import java.util.Objects;
@Configuration
public class SlaveSqlSession {
@Value("${spring.datasource.readonly.driver-class-name}")
private String driverClass;
@Value("${spring.datasource.readonly.url}")
private String url;
@Value("${spring.datasource.readonly.username}")
private String username;
@Value("${spring.datasource.readonly.password}")
private String password;
@Bean("readonlyDatasource")
public DataSource getDataSource() {
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName(driverClass);
hikariConfig.setJdbcUrl(url);
hikariConfig.setUsername(username);
hikariConfig.setPassword(password);
return new HikariDataSource(hikariConfig);
}
@Bean("readonlySqlSession")
public SqlSession getSqlSession(@Qualifier("readonlyDatasource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setMapUnderscoreToCamelCase(true);
sessionFactory.setConfiguration(configuration);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath:com/mapper/*.xml"));
sessionFactory.setVfs(SpringBootVFS.class);
return new SqlSessionTemplate(Objects.requireNonNull(sessionFactory.getObject()));
}
}
然后,在配置文件里,分别配置主从库的数据库配置,当然要与上面代码里读取的配置项对应,如下:
server:
port: 9999
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://主库IP:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 123456
readonly:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://从库IP:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 123456
使用
在代码使用,就是针对不同的操作,去装配对应的SqlSession,如下:
import com.hezy.pojo.User;
import com.hezy.service.UserService;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Autowired
@Qualifier("sqlSession")
private SqlSession masterSqlSession;
@Autowired
@Qualifier("readonlySqlSession")
private SqlSession slaveSqlSession;
@Override
public List<User> getUserAllFromMaster() {
return masterSqlSession.selectList("selectAll");
}
@Override
public List<User> getUserAllFromSlave() {
return slaveSqlSession.selectList("selectAll");
}
}
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.hezy.service.impl.UserServiceImpl">
<select id="selectAll" resultType="com.hezy.pojo.User">
select * from i_users
</select>
</mapper>
需要注意:xml文件所在的路径,要与创建SqlSession设置的一致;
测试
启动项目前,先查看两个库里面对应表的数据,如下:
(master库)
(slave库)
启动项目,分别调用主从接口
(master)
(slave)
系咁(就这样),读写分离就实现了,我们可以在读写地方用Mybatis常规方式,像注解SQL、xml文件,在仅读的地方用SqlSession直接定位到xml中的Statement,便于区分。
总结
本文介绍了如何通过自定义数据源的方式实现读写分离
标签:自定义,spring,数据源,读写,springframework,import,org,com,annotation From: https://blog.csdn.net/qq_42108331/article/details/142578915