欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SpringBoot2.x SpringDataJpa多数据源配置及使用

程序员文章站 2022-04-13 15:59:17
...

SpringDataJpa使用单数据源时的SpringBoot配置很简单,但是随着业务量发展,单个数据库可能不能满足我们的业务需求,这时候一般会对数据库进行拆分或引入其他数据库,此时单数据源就不能满足我们的需求,需要配置多个数据源。

在使用SpringBoot2.x进行SpringDataJpa多数据源配置之前,对SpringBoot2.x集成SpringDataJpa还不熟悉的朋友,可以先参考SpringBoot2.x 集成 SpringDataJpa。下面就开始进行SpringDataJpa多数据源配置及使用,其中SpringBoot使用的2.2.2.RELEASE版本,添加依赖在这里就不再表述了。

一.编写配置文件

application.yml里进行如下配置:

spring:
  #多数据源数据库连接配置
  datasource:
    #第一数据源配置
    primary:
      jdbc-url: jdbc:mysql://127.0.0.1:3306/test1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: root
    #第二数据源配置
    secondary:
      jdbc-url: jdbc:mysql://127.0.0.1:3306/test2?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: root
  #jpa相关配置
  jpa:
    database: mysql
    show-sql: true
    generate-ddl: true
    hibernate:
      ddl-auto: update
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect

二.编写多数据源的配置类

1.多数据源配置类

package com.rtxtitanv.config;

import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
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 org.springframework.context.annotation.Primary;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;

/**
 * @author rtxtitanv
 * @version v1.0.0
 * @name com.rtxtitanv.config.DataSourceConfig
 * @description 多数据源配置类
 * @date 2020/1/7 19:19
 */
@Configuration
public class DataSourceConfig {

    @Resource
    private JpaProperties jpaProperties;
    @Resource
    private HibernateProperties hibernateProperties;

    /**
     * 配置第一数据源
     * @return 数据源
     */
    @Bean(name = "primaryDataSource")
    @Primary //标识为主数据源
    //prefix:指定yml配置文件中配置项的前缀
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        //这种方式默认只满足spring的配置方式,如果使用其他数据库连接池,需独立获取配置
        return DataSourceBuilder.create().build();
    }

    /**
     * 配置第二数据源
     * @return 数据源
     */
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 配置 组合jpaProperties和hibernateProperties配置的map对象
     * @return 组合jpaProperties和hibernateProperties配置的map
     */
    @Bean(name = "vendorProperties")
    public Map<String, Object> getVendorProperties() {
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }
}

2.第一数据源配置类

package com.rtxtitanv.config;

import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

/**
 * @author rtxtitanv
 * @version v1.0.0
 * @name com.rtxtitanv.config.PrimaryConfig
 * @description 第一数据源配置类
 * @date 2020/1/7 19:21
 */
@Configuration
@EnableTransactionManagement
//entityManagerFactoryRef:指定实体管理器工厂,transactionManagerRef:指定事务管理器
//basePackages:指定该数据源的repository所在包路径
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"com.rtxtitanv.repository.primary"})
public class PrimaryConfig {

    @Resource(name = "primaryDataSource")
    private DataSource primaryDataSource;
    @Resource(name = "vendorProperties")
    private Map<String, Object> vendorProperties;

    /**
     * 配置第一数据源实体管理工厂的bean
     * @param builder EntityManagerFactoryBuilder
     * @return LocalContainerEntityManagerFactoryBean
     */
    @Bean(name = "entityManagerFactoryPrimary")
    @Primary //标识为主数据源(主库对应的数据源)
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(primaryDataSource)
                //指定组合jpaProperties和hibernateProperties配置的map对象
                .properties(vendorProperties)
                //指定该数据源的实体类所在包路径
                .packages("com.rtxtitanv.model.primary")
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }

    /**
     * 配置第一数据源实体管理器
     * @param builder EntityManagerFactoryBuilder
     * @return EntityManager
     */
    @Bean(name = "entityManagerPrimary")
    @Primary
    public EntityManager entityManagerPrimary(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    /**
     * 配置第一数据源事务管理器
     * @param builder EntityManagerFactoryBuilder
     * @return PlatformTransactionManager
     */
    @Bean(name = "transactionManagerPrimary")
    @Primary
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

3.第二数据源配置类

package com.rtxtitanv.config;

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.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

/**
 * @author rtxtitanv
 * @version v1.0.0
 * @name com.rtxtitanv.config.SecondaryConfig
 * @description 第二数据源配置类
 * @date 2020/1/7 19:21
 */
@Configuration
@EnableTransactionManagement
//entityManagerFactoryRef:指定实体管理器工厂,transactionManagerRef:指定事务管理器
//basePackages:指定该数据源的repository所在包路径
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = {"com.rtxtitanv.repository.secondary"})
public class SecondaryConfig {

    @Resource(name = "secondaryDataSource")
    private DataSource secondaryDataSource;
    @Resource(name = "vendorProperties")
    private Map<String, Object> vendorProperties;

    /**
     * 配置第二数据源实体管理工厂的bean
     * @param builder EntityManagerFactoryBuilder
     * @return LocalContainerEntityManagerFactoryBean
     */
    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(secondaryDataSource)
                //指定组合jpaProperties和hibernateProperties配置的map对象
                .properties(vendorProperties)
                //指定该数据源的实体类所在包路径
                .packages("com.rtxtitanv.model.secondary")
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }

    /**
     * 配置第二数据源实体管理器
     * @param builder EntityManagerFactoryBuilder
     * @return EntityManager
     */
    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManagerSecondary(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }

    /**
     * 配置第二数据源事务管理器
     * @param builder EntityManagerFactoryBuilder
     * @return PlatformTransactionManager
     */
    @Bean(name = "transactionManagerSecondary")
    public PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
}

三.创建实体类和Repository

1.主数据库实体类和Repository

主数据库实体类

package com.rtxtitanv.model.primary;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import javax.persistence.*;

/**
 * @author rtxtitanv
 * @version v1.0.0
 * @name com.rtxtitanv.model.primary.PrimaryUser
 * @description 主数据库用户实体类
 * @date 2020/1/7 19:31
 */
@Accessors(chain = true)
@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name = "user")
public class PrimaryUser {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    @Column(name = "user_name")
    private String userName;
    @Column(name = "pass_word")
    private String passWord;
    @Column(name = "nick_name")
    private String nickName;
    @Column(name = "age")
    private Integer age;
    @Column(name = "email")
    private String email;
    @Column(name = "tel")
    private String tel;
}

主数据库Repository

/**
 * @author rtxtitanv
 * @version v1.0.0
 * @name com.rtxtitanv.repository.primary.PrimaryUserRepository
 * @description PrimaryUserRepository接口用于操作主库用户表
 * @date 2020/1/7 19:34
 */
public interface PrimaryUserRepository extends JpaRepository<PrimaryUser, Long>, JpaSpecificationExecutor<PrimaryUser> {
}

2.从数据库实体类和Repository

从数据库实体类

package com.rtxtitanv.model.secondary;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import javax.persistence.*;

/**
 * @author rtxtitanv
 * @version v1.0.0
 * @name com.rtxtitanv.model.secondary.SecondaryUser
 * @description 从数据库用户实体类
 * @date 2020/1/7 19:32
 */
@Accessors(chain = true)
@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name = "user")
public class SecondaryUser {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    @Column(name = "user_name")
    private String userName;
    @Column(name = "pass_word")
    private String passWord;
    @Column(name = "nick_name")
    private String nickName;
    @Column(name = "age")
    private Integer age;
    @Column(name = "email")
    private String email;
    @Column(name = "tel")
    private String tel;
}

从数据库Repository

/**
 * @author rtxtitanv
 * @version v1.0.0
 * @name com.rtxtitanv.repository.secondary.SecondaryUserRepository
 * @description SecondaryUserRepository用于操作从库用户表
 * @date 2020/1/7 19:37
 */
public interface SecondaryUserRepository extends JpaRepository<SecondaryUser, Long>, JpaSpecificationExecutor<SecondaryUser> {
}

四.使用测试

package com.rtxtitanv;

import com.rtxtitanv.model.primary.PrimaryUser;
import com.rtxtitanv.model.secondary.SecondaryUser;
import com.rtxtitanv.repository.primary.PrimaryUserRepository;
import com.rtxtitanv.repository.secondary.SecondaryUserRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import java.util.Optional;

/**
 * @author rtxtitanv
 * @version v1.0.0
 * @name com.rtxtitanv.JpaMultiTest
 * @description SpringDataJpa多数据源测试类
 * @date 2020/1/7 18:22
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = JpaMultiApplication.class)
public class JpaMultiTest {

    @Autowired
    private PrimaryUserRepository primaryUserRepository;
    @Autowired
    private SecondaryUserRepository secondaryUserRepository;
    private static Logger logger = LoggerFactory.getLogger(JpaMultiTest.class);

    /**
     * 多数据源保存测试
     */
    @Test
    public void saveTest() {
        primaryUserRepository.save(new PrimaryUser(null, "aaa", "123456", "aaa", 20, "[email protected]", "13598766131"));
        primaryUserRepository.save(new PrimaryUser(null, "bbb", "123123", "bbb", 22, "[email protected]", "13659836782"));
        primaryUserRepository.save(new PrimaryUser(null, "ccc", "111111", "ccc", 25, "[email protected]", "18965233695"));
        secondaryUserRepository.save(new SecondaryUser(null, "ddd", "2356890", "ccc", 18, "[email protected]", "13678922986"));
        secondaryUserRepository.save(new SecondaryUser(null, "eee", "124678", "bbb", 25, "[email protected]", "13669876321"));
        secondaryUserRepository.save(new SecondaryUser(null, "fff", "112233567", "aaa", 22, "[email protected]", "19862398732"));
    }

    /**
     * 多数据源查询测试
     */
    @Test
    public void findTest() {
        logger.info("查询主库user表测试开始");
        List<PrimaryUser> primaryUsers = primaryUserRepository.findAll();
        if (primaryUsers.isEmpty()) {
            logger.info("主库user表不存在数据");
        } else {
            primaryUsers.forEach(primaryUser -> logger.info(primaryUser.toString()));
        }
        logger.info("查询主库user表测试结束");
        logger.info("查询从库user表测试开始");
        List<SecondaryUser> secondaryUsers = secondaryUserRepository.findAll();
        if (secondaryUsers.isEmpty()) {
            logger.info("从库user表不存在数据");
        } else {
            secondaryUsers.forEach(secondaryUser -> logger.info(secondaryUser.toString()));
        }
        logger.info("查询从库user表测试结束");
    }

    /**
     * 多数据源更新测试
     */
    @Test
    public void updateTest() {
        Optional<PrimaryUser> user = primaryUserRepository.findById(1L);
        if (!user.isPresent()) {
            logger.info("用户不存在");
        } else {
            PrimaryUser primaryUser = user.get().setUserName("ddd").setNickName("ddd").setEmail("[email protected]");
            primaryUserRepository.save(primaryUser);
        }
        Optional<SecondaryUser> user1 = secondaryUserRepository.findById(1L);
        if (!user1.isPresent()) {
            logger.info("用户记录不存在");
        } else {
            SecondaryUser secondaryUser = user1.get().setUserName("aaa").setNickName("aaa").setEmail("[email protected]");
            secondaryUserRepository.save(secondaryUser);
        }
    }

    /**
     * 多数据源删除测试
     */
    @Test
    public void deleteTest() {
        //primaryUserRepository.deleteById(1L);
        //secondaryUserRepository.deleteById(3L);
        primaryUserRepository.deleteAllInBatch();
        secondaryUserRepository.deleteAllInBatch();
    }
 }

主启动类启动后,test1(主库)和test2(从库)都自动创建了一张user表,暂无数据。
执行保存测试方法后两张表的结果:
SpringBoot2.x SpringDataJpa多数据源配置及使用
SpringBoot2.x SpringDataJpa多数据源配置及使用
执行简单的查询测试后控制台打印的日志和自动生成的sql如下:

2020-01-08 16:38:14.342  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : 查询主库user表测试开始
Hibernate: select primaryuse0_.id as id1_0_, primaryuse0_.age as age2_0_, primaryuse0_.email as email3_0_, primaryuse0_.nick_name as nick_nam4_0_, primaryuse0_.pass_word as pass_wor5_0_, primaryuse0_.tel as tel6_0_, primaryuse0_.user_name as user_nam7_0_ from user primaryuse0_
2020-01-08 16:38:14.487  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : PrimaryUser(id=1, userName=aaa, passWord=123456, nickName=aaa, age=20, [email protected], tel=13598766131)
2020-01-08 16:38:14.487  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : PrimaryUser(id=2, userName=bbb, passWord=123123, nickName=bbb, age=22, [email protected], tel=13659836782)
2020-01-08 16:38:14.487  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : PrimaryUser(id=3, userName=ccc, passWord=111111, nickName=ccc, age=25, [email protected], tel=18965233695)
2020-01-08 16:38:14.487  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : 查询主库user表测试结束
2020-01-08 16:38:14.487  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : 查询从库user表测试开始
Hibernate: select secondaryu0_.id as id1_0_, secondaryu0_.age as age2_0_, secondaryu0_.email as email3_0_, secondaryu0_.nick_name as nick_nam4_0_, secondaryu0_.pass_word as pass_wor5_0_, secondaryu0_.tel as tel6_0_, secondaryu0_.user_name as user_nam7_0_ from user secondaryu0_
2020-01-08 16:38:14.492  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : SecondaryUser(id=1, userName=ddd, passWord=2356890, nickName=ccc, age=18, [email protected], tel=13678922986)
2020-01-08 16:38:14.492  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : SecondaryUser(id=2, userName=eee, passWord=124678, nickName=bbb, age=25, [email protected], tel=13669876321)
2020-01-08 16:38:14.492  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : SecondaryUser(id=3, userName=fff, passWord=112233567, nickName=aaa, age=22, [email protected], tel=19862398732)
2020-01-08 16:38:14.493  INFO 10720 --- [           main] com.rtxtitanv.JpaMultiTest               : 查询从库user表测试结束

执行更新测试后控制台打印的sql语句如下,刷新两张表后查看更新成功。

Hibernate: select primaryuse0_.id as id1_0_0_, primaryuse0_.age as age2_0_0_, primaryuse0_.email as email3_0_0_, primaryuse0_.nick_name as nick_nam4_0_0_, primaryuse0_.pass_word as pass_wor5_0_0_, primaryuse0_.tel as tel6_0_0_, primaryuse0_.user_name as user_nam7_0_0_ from user primaryuse0_ where primaryuse0_.id=?
Hibernate: select primaryuse0_.id as id1_0_0_, primaryuse0_.age as age2_0_0_, primaryuse0_.email as email3_0_0_, primaryuse0_.nick_name as nick_nam4_0_0_, primaryuse0_.pass_word as pass_wor5_0_0_, primaryuse0_.tel as tel6_0_0_, primaryuse0_.user_name as user_nam7_0_0_ from user primaryuse0_ where primaryuse0_.id=?
Hibernate: update user set age=?, email=?, nick_name=?, pass_word=?, tel=?, user_name=? where id=?
Hibernate: select secondaryu0_.id as id1_0_0_, secondaryu0_.age as age2_0_0_, secondaryu0_.email as email3_0_0_, secondaryu0_.nick_name as nick_nam4_0_0_, secondaryu0_.pass_word as pass_wor5_0_0_, secondaryu0_.tel as tel6_0_0_, secondaryu0_.user_name as user_nam7_0_0_ from user secondaryu0_ where secondaryu0_.id=?
Hibernate: select secondaryu0_.id as id1_0_0_, secondaryu0_.age as age2_0_0_, secondaryu0_.email as email3_0_0_, secondaryu0_.nick_name as nick_nam4_0_0_, secondaryu0_.pass_word as pass_wor5_0_0_, secondaryu0_.tel as tel6_0_0_, secondaryu0_.user_name as user_nam7_0_0_ from user secondaryu0_ where secondaryu0_.id=?
Hibernate: update user set age=?, email=?, nick_name=?, pass_word=?, tel=?, user_name=? where id=?

执行删除测试后控制台打印的sql语句如下,刷新两张表查看删除成功。
按id删除:

Hibernate: select primaryuse0_.id as id1_0_0_, primaryuse0_.age as age2_0_0_, primaryuse0_.email as email3_0_0_, primaryuse0_.nick_name as nick_nam4_0_0_, primaryuse0_.pass_word as pass_wor5_0_0_, primaryuse0_.tel as tel6_0_0_, primaryuse0_.user_name as user_nam7_0_0_ from user primaryuse0_ where primaryuse0_.id=?
Hibernate: delete from user where id=?
Hibernate: select secondaryu0_.id as id1_0_0_, secondaryu0_.age as age2_0_0_, secondaryu0_.email as email3_0_0_, secondaryu0_.nick_name as nick_nam4_0_0_, secondaryu0_.pass_word as pass_wor5_0_0_, secondaryu0_.tel as tel6_0_0_, secondaryu0_.user_name as user_nam7_0_0_ from user secondaryu0_ where secondaryu0_.id=?
Hibernate: delete from user where id=?

删除所有:

Hibernate: delete from user
Hibernate: delete from user

代码示例