SpringBoot3+JPA+MySQL实现多数据源的读写分离(基于EntityManagerFactory)_jpa 读写分离

1、简介

在Spring Boot中配置多个数据源并实现自动切换EntityManager，这里我编写了一个RoutingEntityManagerFactory和AOP（面向切面编程）的方式来实现。

这里我配置了两个数据源：primary和secondary，其中primary主数据源用来写入数据，secondary从数据源用来读取数据。

注意1： 使用Springboot3的读写分离，首先要保证主库和从库已经配置好了 数据同步，否则会导致数据不一致。
当然如果仅仅是测试的话，不同步就不影响了

注意2： SpringBoot3的JDK不能低于17
我使用的JDK版本

openjdk version "20.0.2" 2023-07-18
OpenJDK Runtime Environment (build 20.0.2+9-78)
OpenJDK 64-Bit Server VM (build 20.0.2+9-78, mixed mode, sharing)
1
2
3

2、数据库说明

这里我使用了本机的同一个mysql上的两个不同的数据库，在实际环境中这两个库应该是分别处于不同的服务器上，同时应该已经配置好了主从复制或主备，保证了数据的一致性，不然读写分离就没有意义了。

提示：虽然这里我使用的是MySQL数据库，但是在实际的开发过程中，可以替换为Postgresql或oracle等其他关系型数据库，只需要做很小的改动就可以使用了

3、准备工作

3.1、添加依赖

在你的项目里添加如下依赖

<!-- Spring Web -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<!-- AOP -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>

<!-- JPA -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- MySQL -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>


<!-- druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.22</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>
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

完整的pom.xml文件

下面是我在编写代码时候的完整的pom.xml文件内容

<?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>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.ts</groupId>
    <artifactId>springboot3-jpa-read-write-separation-mysql2</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot3-jpa-read-write-separation-mysql2</name>
    <description>springBoot3 + JPA + MySQL 实现读写分离</description>
    <properties>
        <java.version>17</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Spring Boot Starter AOP for @Transactional annotations -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <!-- MySQL Database -->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.3.0</version>
        </dependency>


        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.22</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

3.2、准备的SQL

-- 用户表
CREATE TABLE userinfo (
  id int AUTO_INCREMENT PRIMARY KEY,
  name varchar(50),
  age smallint,
  gender varchar(3),
  entry_date timestamp
);

-- 测试数据
INSERT INTO userinfo(name,age,gender,entry_date) VALUES
('刘峰',24,'男','2024-02-28 12:01:01'),
('舒航',25,'女','2024-02-28 12:01:01'),
('张明',26,'男','2024-02-29 12:01:01'),
('徐媛',28,'女','2024-02-29 12:01:01'),
('舒莱',29,'女','2023-07-30 12:01:01'),
('唐力',30,'男','2023-08-05 12:01:01'),
('唐莉',29,'女','2023-06-05 12:01:01'),
('王乐',27,'男','2023-07-01 12:01:01'),
('张萌',32,'女','2023-07-02 12:01:01'),
('程媛',25,'女','2023-08-02 12:01:01'),
('嫪玉',35,'女','2023-08-01 10:00:00'),
('贾茹',26,'女','2023-10-03 12:01:01'),
('胡安',25,'男','2023-11-09 12:01:01'),
('刘伟',27,'男','2023-07-09 12:01:01');
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

3.3、application.yml中定义多数据源配置

application.yml中定义每个数据源的连接信息。

spring:
  jpa:
    database: mysql
    show-sql: true

  datasource:
    #主数据源 , 写入数据库
    primary:
      url: jdbc:mysql://localhost:3306/primary_db?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: root

    # 次数据源 , 读取数据库
    secondary:
      url: jdbc:mysql://localhost:3306/secondary_db?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: root


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

4、创建动态数据源

4.1、主数据源配置

package com.ts.config;


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 *  主数据源配置
 * @author zhouq
 * @since 15:26 2024/03/21
 **/
@Configuration(proxyBeanMethods = false)
@EnableJpaRepositories(
        basePackages = "com.ts.service",
        entityManagerFactoryRef = "primaryEntityManagerFactory"
)
public class PrimaryDataSourceConfig {


    @Autowired
    private JpaProperties jpaProperties;



    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        //return DataSourceBuilder.create().build();
        return new DruidDataSource();
    }

    //LocalContainerEntityManagerFactoryBean
    @Bean
    public Object primaryEntityManagerFactory(DataSource primaryDataSource,
                                                                            JpaProperties primaryJpaProperties) {
        EntityManagerFactoryBuilder builder = createEntityManagerFactoryBuilder(primaryJpaProperties);
        return builder.dataSource(primaryDataSource).packages("com.ts.model")
                .persistenceUnit("primaryDataSource").build();
    }

    private EntityManagerFactoryBuilder createEntityManagerFactoryBuilder(JpaProperties jpaProperties) {
        JpaVendorAdapter jpaVendorAdapter = createJpaVendorAdapter(jpaProperties);
        return new EntityManagerFactoryBuilder(jpaVendorAdapter, jpaProperties.getProperties(), null);
    }

    private JpaVendorAdapter createJpaVendorAdapter(JpaProperties jpaProperties) {
        // ... map JPA properties as needed
        return new HibernateJpaVendorAdapter();
    }


}

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
60
61
62
63
64
65
66

4.2、次要数据源配置

package com.ts.config;


import com.alibaba.druid.pool.DruidDataSource;
import jakarta.persistence.EntityManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * 次要数据源配置
 * @author zhouq
 * @since 15:25 2024/03/21
 **/
@Configuration
@EnableJpaRepositories(
        basePackages = "com.ts.service",
        entityManagerFactoryRef = "secondaryEntityManagerFactory"
)
public class SecondaryDataSourceConfig {

    @Autowired
    private JpaProperties jpaProperties;


    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        //return DataSourceBuilder.create().build();
        return new DruidDataSource();
    }

    // LocalContainerEntityManagerFactoryBean
    @Bean
    public Object secondaryEntityManagerFactory(DataSource secondaryDataSource,
                                                                              JpaProperties secondaryJpaProperties) {
        EntityManagerFactoryBuilder builder = createEntityManagerFactoryBuilder(secondaryJpaProperties);
        return builder.dataSource(secondaryDataSource).packages("com.ts.model")
                .persistenceUnit("secondaryDataSource").build();
    }

    private EntityManagerFactoryBuilder createEntityManagerFactoryBuilder(JpaProperties jpaProperties) {
        JpaVendorAdapter jpaVendorAdapter = createJpaVendorAdapter(jpaProperties);
        return new EntityManagerFactoryBuilder(jpaVendorAdapter, jpaProperties.getProperties(), null);
    }

    private JpaVendorAdapter createJpaVendorAdapter(JpaProperties jpaProperties) {
        // ... map JPA properties as needed
        return new HibernateJpaVendorAdapter();
    }
    
}

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
60
61
62
63
64
65

5、全局EntityManager配置

5.1、创建EntityManager线程工具类

EntityManagerContextHolder是一个用来保存当前线程的EntityManager名称的工具类。

package com.ts.config;


/**
 * 用来保存当前线程的EntityManagerFactory名称 的工具类
 * @author zhouq
 * @since 14:29 2024/03/20
 **/
public class EntityManagerContextHolder {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public static void setEntityManagerFactoryType(String entityManagerFactoryType) {
        contextHolder.set(entityManagerFactoryType);
    }

    public static String getEntityManagerFactoryType() {
        return contextHolder.get();
    }

    public static void clearEntityManagerFactoryType() {
        contextHolder.remove();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

5.2、创建数RoutingEntityManagerFactory路由(管理多个EntityManager)

package com.ts.config;

import jakarta.persistence.*;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.metamodel.Metamodel;
import org.springframework.lang.Nullable;
import org.springframework.util.Assert;

import java.util.Map;


/**
 *  动态切换EntityManagerFactory
 *  EntityManagerFactory路由类
 * @author zhouq
 * @since 14:54 2024/03/21
 **/
public class RoutingEntityManagerFactory implements EntityManagerFactory{
    @Nullable
    private Map<String, Object> targetEntityManagerFactorys;
    @Nullable
    private Object defaultTargetEntityManagerFactory;

    @Nullable
    public Map<String, Object> getTargetEntityManagerFactorys() {
        return targetEntityManagerFactorys;
    }

    public void setTargetEntityManagerFactorys(@Nullable Map<String, Object> targetEntityManagerFactorys) {
        this.targetEntityManagerFactorys = targetEntityManagerFactorys;
    }

    @Nullable
    public Object getDefaultTargetEntityManagerFactory() {
        return defaultTargetEntityManagerFactory;
    }

    public void setDefaultTargetEntityManagerFactory(@Nullable Object defaultTargetEntityManagerFactory) {
        this.defaultTargetEntityManagerFactory = defaultTargetEntityManagerFactory;
    }

    public String determineCurrentLookupKey() {
        // 如果不使用读写分离,这里也可以做一个简单的负载均衡策略
        String entityManagerFactoryType = EntityManagerContextHolder.getEntityManagerFactoryType();
        //System.out.println("当前使用的数据源是：" + entityManagerFactoryType);
        return entityManagerFactoryType;
    }

    public EntityManagerFactory determineTargetEntityManagerFactory() {
        Assert.notNull(this.targetEntityManagerFactorys, "targetEntityManagerFactory router not initialized");
        Object lookupKey = this.determineCurrentLookupKey();
        EntityManagerFactory entityManagerFactory = (EntityManagerFactory)this.targetEntityManagerFactorys.get(lookupKey);
        if (entityManagerFactory == null && lookupKey == null) {
            entityManagerFactory = (EntityManagerFactory)this.defaultTargetEntityManagerFactory;
        }

        if (entityManagerFactory == null) {
            throw new IllegalStateException("Cannot determine target EntityManagerFactory for lookup key [" + lookupKey + "]");
        } else {
            return entityManagerFactory;
        }
    }

    //---------------------下面的方法都是EntityManagerFactory接口的实现------------------------------------
    @Override
    public EntityManager createEntityManager() {
        return this.determineTargetEntityManagerFactory().createEntityManager();
    }

    @Override
    public EntityManager createEntityManager(Map map) {
        return this.determineTargetEntityManagerFactory().createEntityManager(map);
    }

    @Override
    public EntityManager createEntityManager(SynchronizationType synchronizationType) {
        return this.determineTargetEntityManagerFactory().createEntityManager(synchronizationType);
    }

    @Override
    public EntityManager createEntityManager(SynchronizationType synchronizationType, Map map) {
        return this.determineTargetEntityManagerFactory().createEntityManager(synchronizationType,map);
    }

    @Override
    public CriteriaBuilder getCriteriaBuilder() {
        return this.determineTargetEntityManagerFactory().getCriteriaBuilder();
    }

    @Override
    public Metamodel getMetamodel() {
        return this.determineTargetEntityManagerFactory().getMetamodel();
    }

    @Override
    public boolean isOpen() {
        return this.determineTargetEntityManagerFactory().isOpen();
    }

    @Override
    public void close() {
        this.determineTargetEntityManagerFactory().close();
    }

    @Override
    public Map<String, Object> getProperties() {
        return this.determineTargetEntityManagerFactory().getProperties();
    }

    @Override
    public Cache getCache() {
        return this.determineTargetEntityManagerFactory().getCache();
    }

    @Override
    public PersistenceUnitUtil getPersistenceUnitUtil() {
        return this.determineTargetEntityManagerFactory().getPersistenceUnitUtil();
    }

    @Override
    public void addNamedQuery(String s, Query query) {
        this.determineTargetEntityManagerFactory().addNamedQuery(s,query);
    }

    @Override
    public <T> T unwrap(Class<T> aClass) {
        return this.determineTargetEntityManagerFactory().unwrap(aClass);
    }

    @Override
    public <T> void addNamedEntityGraph(String s, EntityGraph<T> entityGraph) {
        this.determineTargetEntityManagerFactory().addNamedEntityGraph(s,entityGraph);
    }
}

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

5.3、创建EntityManagerFactory全局配置类

package com.ts.config;

import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 *  全局配置实体管理工厂 EntityManagerFactory
 *  配置EntityManagerFactory，确定使用的EntityManagerFactory
 * @author zhouq
 * @since 10:59 2024/03/21
 **/
@Configuration
public class EntityManagerFactoryConfig {

    @Autowired
    @Qualifier("primaryEntityManagerFactory")
    private Object primaryEntityManagerFactory;

    @Autowired
    @Qualifier("secondaryEntityManagerFactory")
    private Object secondaryEntityManagerFactory;


    @Bean
    public EntityManagerFactory entityManager(){
        String dataSourceType = EntityManagerContextHolder.getEntityManagerFactoryType();

        RoutingEntityManagerFactory routingEntityManagerFactory = new RoutingEntityManagerFactory();

        Map<String, Object> targetEntityManagerFactorys = new HashMap<String, Object>();

        targetEntityManagerFactorys.put("primary", primaryEntityManagerFactory);  //主实体管理工厂
        targetEntityManagerFactorys.put("secondary", secondaryEntityManagerFactory); //次要实体管理工厂

        routingEntityManagerFactory.setTargetEntityManagerFactorys(targetEntityManagerFactorys);// 配置实体管理工厂
        routingEntityManagerFactory.setDefaultTargetEntityManagerFactory(primaryEntityManagerFactory);// 设置默认实体管理工厂

        return routingEntityManagerFactory;
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager tm = new JpaTransactionManager();
        tm.setEntityManagerFactory(entityManager());
        return tm;
    }

}

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
60
61
62
63
64

6、使用AOP实现EntityManager自动切换

6.1、创建AOP切面注入EntityManager类型

通过AOP在方法执行前设置EntityManagerFactory，并在方法执行后清除。

package com.ts.config;

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;

/**
 * 使用AOP注入EntityManager类型
 * 通过AOP在方法执行前设置EntityManager，并在方法执行后清除。
 * @author zhouq
 * @since 14:25 2024/03/20
 **/
@Aspect
@Component
public class EntityManagerFactoryAspect {

    @Before("@annotation(dataSource)")
    public void switchDataSource(JoinPoint point, DataSourceSwitch dataSource) {
        //log.info("使用的数据源是:" + dataSource.value());
        System.out.println("使用的数据源是:" + dataSource.value());
        EntityManagerContextHolder.setEntityManagerFactoryType(dataSource.value());
    }

    @After("@annotation(dataSource)")
    public void restoreDataSource(JoinPoint point, DataSourceSwitch dataSource) {
        EntityManagerContextHolder.clearEntityManagerFactoryType();
    }
}
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

6.2、创建自定义注解用于标注所使用的的

DataSourceSwitch是一个自定义注解，用来标识需要切换数据源的方法。

package com.ts.config;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 自定义注解，用来标识需要切换数据源的方法
 * @author zhouq
 * @since 14:31 2024/03/20
 **/
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSourceSwitch {
    String value() default "primary";
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

7、创建实体类

package com.ts.model;

import jakarta.persistence.*;
import lombok.*;
import lombok.experimental.Accessors;
import java.util.Date;


@Setter
@Getter
@Accessors(chain = true)
@AllArgsConstructor // 全参构造方法
@NoArgsConstructor // 无参构造方法
//@RequiredArgsConstructor
@ToString
@Entity
@Table(name = "userinfo")
public class Userinfo implements java.io.Serializable{

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    private String name;
    private int age;
    private String gender;
    private Date entry_date;

}
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

8、创建Service

创建UserService接口

package com.ts.service;

import com.ts.model.Userinfo;
import java.util.List;


public interface UserService {
    /**
     *  获取所有
     * @author zhouq
     * @since 13:33 2024/03/20
     * @return java.util.List<com.ts.model.Userinfo>
     **/
    List<Userinfo> getAll();

    /**
     * 查找包含姓名的用户
     * @author zhouq
     * @since 13:34 2024/03/20
     * @param name 姓名
     * @return java.util.List<com.ts.model.Userinfo>
     **/
    List<Userinfo> queryWithName(String name);


    /**
     *  添加用户
     * @author zhouq
     * @since 13:39 2024/03/20
     * @return java.util.List<com.ts.model.Userinfo>
     **/
    void addUser(Userinfo userinfo);

    /**
     * 按照id删除
     * @author zhouq
     * @since 13:55 2024/03/20
     **/
    void deleteUser(int id);

}

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

创建UserService接口实现类

package com.ts.service.impl;

import com.ts.config.DataSourceSwitch;
import com.ts.model.Userinfo;
import com.ts.service.UserService;
import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 *  基于EntityManager的实现
 *  这里使用 primary主数据源写入数据，secondary从数据源读取数据
 * @author zhouq
 * @since 11:08 2024/03/21
 **/
@Service
public class UserServiceEntityManagerImpl implements UserService {

    @Autowired
    private EntityManager entityManager;


    /**
     * 获取所有用户
     * 这里使用   @DataSourceSwitch("secondary")  指定 读取的数据源
     * @author zhouq
     * @since 11:21 2024/03/21
     * @return java.util.List<com.ts.model.Userinfo>
     **/
    @Override
    @DataSourceSwitch("secondary")
    public List<Userinfo> getAll() {
        return  entityManager.createQuery("select u from Userinfo u").getResultList();
    }

    /**
     * 查询用户，模糊查询
     * @author zhouq
     * @since 11:20 2024/03/21
     * @return java.util.List<com.ts.model.Userinfo>
     **/
    @Override
    @DataSourceSwitch("secondary")
    public List<Userinfo> queryWithName(String name) {
        Query query = entityManager.createQuery("select u from Userinfo u where u.name like :name");
        query.setParameter("name", "%" + name +"%");

        return query.getResultList();
    }


    /**
     * 添加用户
     * 这里使用   @DataSourceSwitch("primary")  指定写入的数据源
     * @author zhouq
     * @since 11:20 2024/03/21
     **/
    @Override
    @DataSourceSwitch("primary")  //或者 @DataSourceSwitch
    @Modifying
    @Transactional
    public void addUser(Userinfo userinfo) {

        //使用本地原生SQL查询
        Query nativeQuery = entityManager.createNativeQuery("insert into Userinfo(id,name,age,gender) values(:id,:name,:age,:gender)");
        nativeQuery.setParameter("id",userinfo.getId());
        nativeQuery.setParameter("name",userinfo.getName());
        nativeQuery.setParameter("age",userinfo.getAge());
        nativeQuery.setParameter("gender",userinfo.getGender());

        //执行查询
        int i = nativeQuery.executeUpdate();
        if ( i<= 0 ) {
            throw new RuntimeException("添加用户失败!");
        }
    }

    /**
     * 删除用户
     * 这里使用   @DataSourceSwitch("primary")  指定写入的数据源
     * @author zhouq
     * @since 11:19 2024/03/21
     **/
    @Override
    @DataSourceSwitch("primary")  //或者 @DataSourceSwitch
    @Modifying
    @Transactional
    public void deleteUser(int id) {
        //使用本地原生SQL查询
        Query nativeQuery = entityManager.createNativeQuery("delete from Userinfo where id = :id");
        nativeQuery.setParameter("id",id);
        //执行
        int i = nativeQuery.executeUpdate();
        if ( i<= 0 ) {
            throw new RuntimeException("删除用户失败!");
        }
    }

}

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

9、编写测试类

package com.ts;

import com.ts.model.Userinfo;
import com.ts.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Date;

@SpringBootTest
public class UserServiceTest {

    @Autowired
    private UserService service;



    // 测试使用从库读取  查询用户
    @Test
    public void testGetAll() {
        service.getAll().forEach(System.out::println);
        //System.out.println(service);
    }
    // 测试使用从库读取  查询用户
    @Test
    public void testQuery() {
        service.queryWithName("张").forEach(System.out::println);
    }

    // 测试使用主库写入  添加用户
    @Test
    public void testAddUser() {
        service.addUser(new Userinfo(100,"测试用户",25,"男",new Date()));
        System.out.println("添加用户完成");
    }

    // 测试使用主库写入  删除用户
    @Test
    public void testDelete() {
        service.deleteUser(100);
        System.out.println("删除用户完成");
    }

}

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

小结

关于读写分离网上的示例很多，但是都比较杂乱，而且很多的方法都是基于SpringBoot2或者SpringBoot1的，我这个是基于SpringBoot3.2.3版本实现了，

上面的代码所有的都亲自测试通过，有什么疑问可以留言评论。

几个截图：
1、查询所有
可以看到 使用的数据源是:secondary 使用的是 读 数据源

2、模糊查询

3、添加用户
看到 使用的数据源是:primary 使用的是 写 数据源

4、删除用户