数据库使用主从复制模式，后端项目中使用多数据源主从读写分离，主要有以下优点：

  • 提升数据库性能。读请求分配到从数据库节点上，减少主数据库的压力，使主数据库专注于写操作，从而提升整个数据库集群的吞吐量和响应速度。
  • 高可用性和故障恢复。
  • 提高扩展性。可以根据业务读写比例增加从库节点。实现横向扩展。

后来发现 mybatis-plus 的组织 baomidou 还有一个开源项目 dynamic-datasource，现在写一下该多数据源工具的示例。

一、多数据源使用 mybatis-plus

1、POM 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<dependency>  
    <groupId>com.baomidou</groupId>  
    <artifactId>mybatis-plus-spring-boot3-starter</artifactId>  
</dependency>

<dependency>  
    <groupId>com.baomidou</groupId>  
    <artifactId>dynamic-datasource-spring-boot3-starter</artifactId>  
</dependency>  
  
<dependency>  
    <groupId>org.postgresql</groupId>  
    <artifactId>postgresql</artifactId>  
</dependency>

<dependency>  
    <groupId>org.springframework.boot</groupId>  
    <artifactId>spring-boot-starter</artifactId>  
    <exclusions>  
        <exclusion>  
            <groupId>org.springframework.boot</groupId>  
            <artifactId>spring-boot-starter-logging</artifactId>  
        </exclusion>  
    </exclusions>  
</dependency>  
  
<dependency>  
    <groupId>org.springframework.boot</groupId>  
    <artifactId>spring-boot-starter-test</artifactId>  
    <scope>test</scope>  
    <exclusions>  
        <exclusion>  
            <groupId>org.springframework.boot</groupId>  
            <artifactId>spring-boot-starter-logging</artifactId>  
        </exclusion>  
    </exclusions>  
</dependency>  
  
<dependency>  
    <groupId>org.springframework.boot</groupId>  
    <artifactId>spring-boot-starter-log4j2</artifactId>  
</dependency>

2、应用属性文件

2.1 数据源配置

应用为 Spring Boot 应用，下面的 properties 中的配置遵循 dynamic-datasource 中的配置。该配置中的 spring.datasource.dynamic.datasource 属性下可以定义数据源，下面的配置中使用了 master 和 slave 两个数据源。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
spring.datasource.dynamic.primary=master  
spring.datasource.dynamic.strict=false  
spring.datasource.dynamic.grace-destroy=false  

spring.datasource.dynamic.datasource.master.url=jdbc:postgresql://localhost:5400/demo?currentSchema=public  
spring.datasource.dynamic.datasource.master.username=z2huo  
spring.datasource.dynamic.datasource.master.password=yourpassword
spring.datasource.dynamic.datasource.master.driver-class-name=org.postgresql.Driver  
spring.datasource.dynamic.datasource.slave.url=jdbc:postgresql://localhost:5401/demo?currentSchema=public  
spring.datasource.dynamic.datasource.slave.username=z2huo  
spring.datasource.dynamic.datasource.slave.password=yourpassword
spring.datasource.dynamic.datasource.slave.driver-class-name=org.postgresql.Driver  

spring.datasource.dynamic.hikari.minimum-idle=5  
spring.datasource.dynamic.hikari.connection-test-query=SELECT 1  
spring.datasource.dynamic.hikari.maximum-pool-size=20  
spring.datasource.dynamic.hikari.is-auto-commit=true  
spring.datasource.dynamic.hikari.idle-timeout=30000  
spring.datasource.dynamic.hikari.max-lifetime=60000  
spring.datasource.dynamic.hikari.connection-timeout=30000

2.2 mybatis-plus 配置

1
2
3
4
5
6
7
8
# 主键类型  
# auto=数据库自增主键 none=未设置主键类型 assign_id=自动填充（雪花算法） assign_uuid=自动填充（UUID）  
mybatis-plus.global-config.db-config.id-type=ASSIGN_ID  
# 驼峰下划线转换  
mybatis-plus.global-config.db-config.table-underline=true  
mybatis-plus.configuration.map-underscore-to-camel-case=true  
mybatis-plus.configuration.cache-enabled=true  
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

3、Java 代码

3.1 数据源常量类

1
2
3
4
5
6
7
8
9
public final class DataSourceConstant {  
  
    public static final String MASTER = "master";  
  
    public static final String SLAVE = "slave";  
  
    private DataSourceConstant(){  
    }  
}

3.2 Mybatis-plus

1
2
3
@Mapper  
public interface UserMapper extends BaseMapper<UserDO> {  
}

3.2 实体类对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
@Data
@NoArgsConstructor
@TableName("z2huo_user")
public class UserDO implements Serializable {

    @Serial
    private static final long serialVersionUID = 222111140283658200L;

    /**
     * 
     */
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;

    /**
     * 用户代码
     */
    private String userCode;

    /**
     * 用户名称
     */
    private String userName;

    /**
     * 
     */
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;

    /**
     * 
     */
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime operateTime;

    /**
     * 
     */
    @TableField(fill = FieldFill.INSERT)
    private String createByCode;

    /**
     * 
     */
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private String operateByCode;
}

3.3 DAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Repository  
public class UserDAO {  
  
    @Autowired  
    private UserMapper userMapper;  
  
    @Nullable  
    public UserDO getUserByUserCode(String userCode) {  
        if (StringUtils.isBlank(userCode)) {  
            return null;  
        }  
        QueryWrapper<UserDO> queryWrapper = Wrappers.query(UserDO.class);  
        queryWrapper.eq("user_code", userCode);  
        return userMapper.selectOne(queryWrapper);  
    }  
  
}

3.4 Service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Service  
public class UserServiceImpl {  
  
    @Autowired  
    private UserDAO userDAO;  
  
    @Autowired  
    private UserMapper userMapper;  
  
    @Nullable  
    @DS(DataSourceConstant.SLAVE)  
    public UserDO getUserByUserCode(String userCode) {  
        return userDAO.getUserByUserCode(userCode);  
    }  
  
    @DS(DataSourceConstant.MASTER)  
    public void insert(UserDO userDO) {  
        userMapper.insert(userDO);  
    }    
}

在 Service 中使用 @DS 注解，该注解源码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
 * The core Annotation to switch datasource. It can be annotated at class or method.
 */
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DS {

    /**
     * groupName or specific database name or spring SPEL name.
     *
     * @return the database you want to switch
     */
    String value();
}

value 属性使用的是 application.properties 中声明的数据源名称，即 master 和 slave。

上面的方法中，getUserByUserCode 方法使用数据库的从库，insert 方法使用数据库的主库。

3.5 测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Slf4j
@Disabled
@SpringBootTest(classes = MBPMultiDataSourceApplication.class)
class UserServiceImplTest {

    @Autowired
    private UserServiceImpl userService;

    @Test
    void getUserByUserCode() {

        String userCode = "18182024";

        UserDO userDO = userService.getUserByUserCode(userCode);
        log.info("userDO = {}", userDO);
        Assertions.assertNotNull(userDO);
        Assertions.assertEquals(userCode, userDO.getUserCode());
    }

    @Test
    void insert() {

        UserDO userDO = new UserDO();
        userDO.setUserName("insertUserTest");
        userDO.setUserCode("11111111");
        userService.insert(userDO);
    }
}

getUserByUserCode 方法的日志：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2024-10-30T01:22:25.508+08:00  INFO 36293 --- [           main] c.z.h.HikariDataSource                   : master - Starting...
2024-10-30T01:22:25.656+08:00  INFO 36293 --- [           main] c.z.h.p.HikariPool                       : master - Added connection org.postgresql.jdbc.PgConnection@4cfa83f9
2024-10-30T01:22:25.657+08:00  INFO 36293 --- [           main] c.z.h.HikariDataSource                   : master - Start completed.
2024-10-30T01:22:25.658+08:00  INFO 36293 --- [           main] c.z.h.HikariDataSource                   : slave - Starting...
2024-10-30T01:22:25.704+08:00  INFO 36293 --- [           main] c.z.h.p.HikariPool                       : slave - Added connection org.postgresql.jdbc.PgConnection@270d43a7
2024-10-30T01:22:25.704+08:00  INFO 36293 --- [           main] c.z.h.HikariDataSource                   : slave - Start completed.
2024-10-30T01:22:25.704+08:00  INFO 36293 --- [           main] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource - add a datasource named [slave] success
2024-10-30T01:22:25.704+08:00  INFO 36293 --- [           main] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource - add a datasource named [master] success
2024-10-30T01:22:25.705+08:00  INFO 36293 --- [           main] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource initial loaded [2] datasource,primary datasource named [master]

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@13a268cd] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1370822209 wrapping org.postgresql.jdbc.PgConnection@270d43a7] will not be managed by Spring
==>  Preparing: SELECT id,user_code,user_name,valid_date,invalid_date,valid_flag,delete_flag,company_code,department_code,create_time,operate_time,create_by_code,operate_by_code FROM z2huo_user WHERE (user_code = ?)
==> Parameters: 18182024(String)
<==    Columns: id, user_code, user_name, valid_date, invalid_date, valid_flag, delete_flag, company_code, department_code, create_time, operate_time, create_by_code, operate_by_code
<==        Row: 3, 18182024, 张二爷, 2020-01-01, 2077-01-01, 1, 0, null, null, 2024-04-10 01:23:17, 2024-04-10 01:23:17, null, null
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@13a268cd]

从上面的日志中可以看到两个数据源连接的打印日志：

  • master - Added connection org.postgresql.jdbc.PgConnection@4cfa83f9
  • slave - Added connection org.postgresql.jdbc.PgConnection@270d43a7
  • JDBC Connection [HikariProxyConnection@1370822209 wrapping org.postgresql.jdbc.PgConnection@270d43a7] will not be managed by Spring

可以看到，使用的是从库的数据库连接。

insert 方法的日志：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2024-10-30T01:25:41.482+08:00  INFO 37410 --- [           main] c.z.h.HikariDataSource                   : master - Starting...
2024-10-30T01:25:41.596+08:00  INFO 37410 --- [           main] c.z.h.p.HikariPool                       : master - Added connection org.postgresql.jdbc.PgConnection@4cfa83f9
2024-10-30T01:25:41.596+08:00  INFO 37410 --- [           main] c.z.h.HikariDataSource                   : master - Start completed.
2024-10-30T01:25:41.598+08:00  INFO 37410 --- [           main] c.z.h.HikariDataSource                   : slave - Starting...
2024-10-30T01:25:41.639+08:00  INFO 37410 --- [           main] c.z.h.p.HikariPool                       : slave - Added connection org.postgresql.jdbc.PgConnection@270d43a7
2024-10-30T01:25:41.639+08:00  INFO 37410 --- [           main] c.z.h.HikariDataSource                   : slave - Start completed.
2024-10-30T01:25:41.639+08:00  INFO 37410 --- [           main] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource - add a datasource named [slave] success
2024-10-30T01:25:41.639+08:00  INFO 37410 --- [           main] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource - add a datasource named [master] success
2024-10-30T01:25:41.639+08:00  INFO 37410 --- [           main] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource initial loaded [2] datasource,primary datasource named [master]

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47fa3671] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1891984436 wrapping org.postgresql.jdbc.PgConnection@4cfa83f9] will not be managed by Spring
==>  Preparing: INSERT INTO z2huo_user ( id, user_code, user_name, create_time, operate_time, create_by_code, operate_by_code ) VALUES ( ?, ?, ?, ?, ?, ?, ? )
==> Parameters: 1851314479528370177(Long), 11111111(String), insertUserTest(String), null, null, null, null
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47fa3671]

从上面的日志中可以看到两个数据源连接的打印日志：

  • master - Added connection org.postgresql.jdbc.PgConnection@4cfa83f9
  • slave - Added connection org.postgresql.jdbc.PgConnection@270d43a7
  • JDBC Connection [HikariProxyConnection@1891984436 wrapping org.postgresql.jdbc.PgConnection@4cfa83f9] will not be managed by Spring

可以看到，使用的是主库的数据库连接。

如果使 insert 方法使用从库连接，将报错如下，可以看到从库的连接是只读的：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: cannot execute INSERT in a read-only transaction
### The error may exist in cn/z2huo/demo/mapper/user/UserMapper.java (best guess)
### The error may involve cn.z2huo.demo.mapper.user.UserMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO z2huo_user  ( id, user_code, user_name,       create_time, operate_time, create_by_code, operate_by_code )  VALUES (  ?, ?, ?,       ?, ?, ?, ?  )
### Cause: org.postgresql.util.PSQLException: ERROR: cannot execute INSERT in a read-only transaction
; uncategorized SQLException; SQL state [25006]; error code [0]; ERROR: cannot execute INSERT in a read-only transaction

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:93)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
	at jdk.proxy2/jdk.proxy2.$Proxy75.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:59)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:149)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:90)
	at jdk.proxy2/jdk.proxy2.$Proxy79.insert(Unknown Source)
	at cn.z2huo.demo.multidatasource.mybatisplus.service.impl.UserServiceImpl.insert(UserServiceImpl.java:34)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:355)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:57)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720)
	at cn.z2huo.demo.multidatasource.mybatisplus.service.impl.UserServiceImpl$$SpringCGLIB$$0.insert(<generated>)
	at cn.z2huo.demo.multidatasource.mybatisplus.service.impl.UserServiceImplTest.insert(UserServiceImplTest.java:42)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute INSERT in a read-only transaction
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58)
	at jdk.proxy3/jdk.proxy3.$Proxy140.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:48)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:75)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:425)
	... 22 more

相关链接

dynamic datasource | baomidou

Docker 部署主从复制 PostgreSQL | z2huo.io

[[Docker 部署主从复制 PostgreSQL]]

OB tags

#SpringBoot #MyBatis #后端