java 多数据源连接 springjpa多数据源

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

这里主要使用Kotlin语言编写，在使用SpringBoot2.x进行SpringDataJpa多数据源配置之前，可以参考SpringBoot2.x 集成 SpringDataJpa 基于Kotlin，下面就开始进行SpringDataJpa多数据源配置及使用，其中SpringBoot使用的2.2.2.RELEASE版本，pom文件的编写这里就不在表述了。

一、编写配置文件

在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.sql.DataSource

/**
 * @name com.rtxtitanv.config.DataSourceConfig
 * @description 多数据源配置类
 * @author rtxtitanv
 * @date 2020/2/5 16:27
 * @version 1.0.0
 */
@Configuration
class DataSourceConfig(private val jpaProperties: JpaProperties,
                       private val hibernateProperties: HibernateProperties) {

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

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

    /**
     * 配置 组合jpaProperties和hibernateProperties配置的map对象
     * @return 组合jpaProperties和hibernateProperties配置的map
     */
    @Bean(name = ["vendorProperties"])
    fun getVendorProperties(): Map<String, Any> {
        return hibernateProperties.determineHibernateProperties(jpaProperties.properties, 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

/**
 * @name com.rtxtitanv.config.PrimaryConfig
 * @description 第一数据源配置类
 * @author rtxtitanv
 * @date 2020/2/5 17:33
 * @version 1.0.0
 */
@Configuration
@EnableTransactionManagement
// entityManagerFactoryRef：指定实体管理器工厂，transactionManagerRef：指定事务管理器
// basePackages：指定该数据源的repository所在包路径
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = ["com.rtxtitanv.repository.primary"])
class PrimaryConfig(@Resource(name = "primaryDataSource") private val primaryDataSource: DataSource,
                    @Resource(name = "vendorProperties") private val vendorProperties: Map<String, Any>) {

    /**
     * 配置第一数据源实体管理工厂的bean
     * @param builder EntityManagerFactoryBuilder
     * @return LocalContainerEntityManagerFactoryBean
     */
    @Bean(name = ["entityManagerFactoryPrimary"])
    @Primary // 标识为主数据源（主库对应的数据源）
    fun entityManagerFactoryPrimary(builder: EntityManagerFactoryBuilder): LocalContainerEntityManagerFactoryBean {
        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
    fun entityManagerPrimary(builder: EntityManagerFactoryBuilder): EntityManager {
        return entityManagerFactoryPrimary(builder).`object`!!.createEntityManager()
    }

    /**
     * 配置第一数据源事务管理器
     * @param builder EntityManagerFactoryBuilder
     * @return PlatformTransactionManager
     */
    @Bean(name = ["transactionManagerPrimary"])
    @Primary
    fun transactionManagerPrimary(builder: EntityManagerFactoryBuilder): PlatformTransactionManager {
        return JpaTransactionManager(entityManagerFactoryPrimary(builder).`object`!!)
    }
}

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

/**
 * @name com.rtxtitanv.config.SecondaryConfig
 * @description 第二数据源配置类
 * @author rtxtitanv
 * @date 2020/2/5 17:34
 * @version 1.0.0
 */
@Configuration
@EnableTransactionManagement
// entityManagerFactoryRef：指定实体管理器工厂，transactionManagerRef：指定事务管理器
// basePackages：指定该数据源的repository所在包路径
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = ["com.rtxtitanv.repository.secondary"])
class SecondaryConfig(@Resource(name = "secondaryDataSource") private val secondaryDataSource: DataSource,
                      @Resource(name = "vendorProperties") private val vendorProperties: Map<String, Any>) {

    /**
     * 配置第二数据源实体管理工厂的bean
     * @param builder EntityManagerFactoryBuilder
     * @return LocalContainerEntityManagerFactoryBean
     */
    @Bean(name = ["entityManagerFactorySecondary"])
    fun entityManagerFactorySecondary(builder: EntityManagerFactoryBuilder): LocalContainerEntityManagerFactoryBean {
        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"])
    fun entityManagerSecondary(builder: EntityManagerFactoryBuilder): EntityManager {
        return entityManagerFactorySecondary(builder).`object`!!.createEntityManager()
    }

    /**
     * 配置第二数据源事务管理器
     * @param builder EntityManagerFactoryBuilder
     * @return PlatformTransactionManager
     */
    @Bean(name = ["transactionManagerSecondary"])
    fun transactionManagerSecondary(builder: EntityManagerFactoryBuilder): PlatformTransactionManager {
        return JpaTransactionManager(entityManagerFactorySecondary(builder).`object`!!)
    }
}

三、创建实体类和Repository

1.主数据库实体类

package com.rtxtitanv.model.primary

import javax.persistence.*

/**
 * @name com.rtxtitanv.model.primary.PrimaryUser
 * @description 主数据库用户实体类
 * @author rtxtitanv
 * @date 2020/2/5 18:29
 * @version 1.0.0
 */
@Entity
@Table(name = "user")
data class PrimaryUser(@Id
                       @GeneratedValue(strategy = GenerationType.IDENTITY)
                       @Column(name = "id")
                       var id: Long? = null,
                       @Column(name = "user_name")
                       var userName: String? = null,
                       @Column(name = "pass_word")
                       var passWord: String? = null,
                       @Column(name = "nick_name")
                       var nickName: String? = null,
                       @Column(name = "age")
                       var age: Int? = null,
                       @Column(name = "email")
                       var email: String? = null,
                       @Column(name = "tel")
                       var tel: String? = null)

2.主数据库Repository

package com.rtxtitanv.repository.primary

import com.rtxtitanv.model.primary.PrimaryUser
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.JpaSpecificationExecutor

/**
 * @name com.rtxtitanv.repository.primary.PrimaryUserRepository
 * @description PrimaryUserRepository接口用于操作主库用户表
 * @author rtxtitanv
 * @date 2020/2/5 19:01
 * @version 1.0.0
 */
interface PrimaryUserRepository : JpaRepository<PrimaryUser, Long>, JpaSpecificationExecutor<PrimaryUser>

3.从数据库实体类

package com.rtxtitanv.model.secondary

import javax.persistence.*

/**
 * @name com.rtxtitanv.model.secondary.SecondaryUser
 * @description 从数据库用户实体类
 * @author rtxtitanv
 * @date 2020/2/5 18:29
 * @version 1.0.0
 */
@Entity
@Table(name = "user")
data class SecondaryUser(@Id
                         @GeneratedValue(strategy = GenerationType.IDENTITY)
                         @Column(name = "id")
                         var id: Long? = null,
                         @Column(name = "user_name")
                         var userName: String? = null,
                         @Column(name = "pass_word")
                         var passWord: String? = null,
                         @Column(name = "nick_name")
                         var nickName: String? = null,
                         @Column(name = "age")
                         var age: Int? = null,
                         @Column(name = "email")
                         var email: String? = null,
                         @Column(name = "tel")
                         var tel: String? = null)

4.从数据库Repository

package com.rtxtitanv.repository.secondary

import com.rtxtitanv.model.secondary.SecondaryUser
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.JpaSpecificationExecutor

/**
 * @name com.rtxtitanv.repository.secondary.SecondaryUserRepository
 * @description SecondaryUserRepository用于操作从库用户表
 * @author rtxtitanv
 * @date 2020/2/5 19:02
 * @version 1.0.0
 */
interface SecondaryUserRepository : 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

/**
 * @name com.rtxtitanv.JpaMultiTest
 * @description SpringDataJpa多数据源测试类
 * @author rtxtitanv
 * @date 2020/2/5 19:03
 * @version 1.0.0
 */
@RunWith(SpringRunner::class)
@SpringBootTest(classes = [JpaMultiApplication::class])
class JpaMultiTest {

    @Autowired
    private lateinit var primaryUserRepository: PrimaryUserRepository
    @Autowired
    private lateinit var secondaryUserRepository: SecondaryUserRepository
    private val logger: Logger = LoggerFactory.getLogger(JpaMultiTest::class.java)

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

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

    /**
     * 多数据源更新测试
     */
    @Test
    fun updateTest() {
        val user = primaryUserRepository.findById(1L)
        if (!user.isPresent) {
            logger.info("用户不存在")
        } else {
            user.get().userName = "ddd"
            user.get().nickName = "ddd"
            user.get().email = "[e-mail]"
            primaryUserRepository.save(user.get())
        }
        val user1 = secondaryUserRepository.findById(1L)
        if (!user1.isPresent) {
            logger.info("用户记录不存在")
        } else {
            user1.get().userName = "aaa"
            user1.get().nickName = "aaa"
            user1.get().email = "[e-mail]"
            secondaryUserRepository.save(user1.get())
        }
    }

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

主启动类启动后，test1（主库）和test2（从库）都自动创建了一张user表，暂无数据。

执行保存测试方法后两张表的结果：

执行简单的查询测试后控制台打印的日志和自动生成的sql如下：

2020-02-05 20:45:08.585  INFO 6744 --- [           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-02-05 20:45:08.746  INFO 6744 --- [           main] com.rtxtitanv.JpaMultiTest               : PrimaryUser(id=1, userName=aaa, passWord=123456, nickName=aaa, age=20, email=[e-mail], tel=13598766131)
2020-02-05 20:45:08.746  INFO 6744 --- [           main] com.rtxtitanv.JpaMultiTest               : PrimaryUser(id=2, userName=bbb, passWord=123123, nickName=bbb, age=22, email=[e-mail], tel=13659836782)
2020-02-05 20:45:08.746  INFO 6744 --- [           main] com.rtxtitanv.JpaMultiTest               : PrimaryUser(id=3, userName=ccc, passWord=111111, nickName=ccc, age=25, email=[e-mail], tel=18965233695)
2020-02-05 20:45:08.746  INFO 6744 --- [           main] com.rtxtitanv.JpaMultiTest               : 查询主库user表测试结束
2020-02-05 20:45:08.746  INFO 6744 --- [           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-02-05 20:45:08.750  INFO 6744 --- [           main] com.rtxtitanv.JpaMultiTest               : PrimaryUser(id=1, userName=aaa, passWord=123456, nickName=aaa, age=20, email=[e-mail], tel=13598766131)
2020-02-05 20:45:08.750  INFO 6744 --- [           main] com.rtxtitanv.JpaMultiTest               : PrimaryUser(id=2, userName=bbb, passWord=123123, nickName=bbb, age=22, email=[e-mail], tel=13659836782)
2020-02-05 20:45:08.750  INFO 6744 --- [           main] com.rtxtitanv.JpaMultiTest               : PrimaryUser(id=3, userName=ccc, passWord=111111, nickName=ccc, age=25, email=[e-mail], tel=18965233695)
2020-02-05 20:45:08.750  INFO 6744 --- [           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

代码示例

• Github：https://github.com/RtxTitanV/springboot-learning/tree/master/springboot2.x-learning/springboot-data-jpa-multi-kotlin