1 介绍

  • 使用JPA的配置两个数据库，配置不同的数据库。

    2 示例

    2.1 依赖引入

    注意：pom不要去改，引入其他druid会报错的！

    1. <parent>
    2. <groupId>org.springframework.boot</groupId>
    3. <artifactId>spring-boot-starter-parent</artifactId>
    4. <version>2.1.3.RELEASE</version>
    5. <relativePath/> <!-- lookup parent from repository -->
    6. </parent>
    7. <dependencies>
    8. <dependency>
    9.   <groupId>org.springframework.boot</groupId>
    10.   <artifactId>spring-boot-starter-web</artifactId>
    11. </dependency>
    13. <dependency>
    14.   <groupId>org.springframework.boot</groupId>
    15.   <artifactId>spring-boot-starter-data-jpa</artifactId>
    16. </dependency>
    18. <dependency>
    19.   <groupId>org.springframework.boot</groupId>
    20.   <artifactId>spring-boot-starter-actuator</artifactId>
    21. </dependency>
    23. <dependency>
    24.   <groupId>mysql</groupId>
    25.   <artifactId>mysql-connector-java</artifactId>
    26. </dependency>
    28. <dependency>
    29.   <groupId>org.projectlombok</groupId>
    30.   <artifactId>lombok</artifactId>
    31.   <scope>provided</scope>
    32. </dependency>
    34. <dependency>
    35.   <groupId>org.springframework.boot</groupId>
    36.   <artifactId>spring-boot-starter-test</artifactId>
    37.   <scope>test</scope>
    38. </dependency>
    39. <dependency>
    40.   <groupId>com.alibaba</groupId>
    41.   <artifactId>druid</artifactId>
    42.   <version>1.1.9</version>
    43. </dependency>
    44. <dependency>
    45.   <groupId>log4j</groupId>
    46.   <artifactId>log4j</artifactId>
    47.   <version>1.2.17</version>
    48. </dependency>
    49. </dependencies>

    2.2 自定义配置两个数据库

    application.yml

    1. spring:
    2. datasource:
    3.   primary:
    4.     url: jdbc:mysql://localhost:3306/primary?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    5.     username: root
    6.     password: 123456
    7.     driver-class-name: com.mysql.cj.jdbc.Driver
    8.   secondary:
    9.     url: jdbc:mysql://localhost:3306/second?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    10.     username: root
    11.     password: 123456
    12.     driver-class-name: com.mysql.cj.jdbc.Driver
    13. jpa:
    14.   database: mysql
    15.   generate-ddl: true
    16.   show-sql: true
    17.   hibernate:
    18.     ddl-auto: update
    19.     naming:
    20.       physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

    2.3 配置默认的数据库源

    ```java package com.hikktn.config;

import com.alibaba.druid.pool.DruidDataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; 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.stereotype.Component;

import javax.sql.DataSource; import java.sql.SQLException;

@Component @Configuration public class DataSourceConfig {

  1. private Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);
  3. @Value("${spring.datasource.primary.url}")
  4. private String dbUrl1;
  6. @Value("${spring.datasource.primary.username}")
  7. private String username1;
  9. @Value("${spring.datasource.primary.password}")
  10. private String password1;
  12. @Value("${spring.datasource.secondary.username}")
  13. private String username2;
  15. @Value("${spring.datasource.secondary.password}")
  16. private String password2;
  18. @Value("${spring.datasource.secondary.url}")
  19. private String dbUrl2;
  21. @Value("${spring.datasource.secondary.driver-class-name}")
  22. private String driverClassName;
  24. @Value("5")
  25. private int initialSize;
  27. @Value("5")
  28. private int minIdle;
  30. @Value("20")
  31. private int maxActive;
  33. @Value("60000")
  34. private int maxWait;
  36. /**
  37.  * 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
  38.  */
  39. @Value("60000")
  40. private int timeBetweenEvictionRunsMillis;
  41. /**
  42.  * 配置一个连接在池中最小生存的时间，单位是毫秒
  43.  */
  44. @Value("300000")
  45. private int minEvictableIdleTimeMillis;
  47. @Value("SELECT 1 FROM DUAL")
  48. private String validationQuery;
  50. @Value("true")
  51. private boolean testWhileIdle;
  53. @Value("false")
  54. private boolean testOnBorrow;
  56. @Value("false")
  57. private boolean testOnReturn;
  59. /**
  60.  * 打开PSCache，并且指定每个连接上PSCache的大小
  61.  */
  62. @Value("true")
  63. private boolean poolPreparedStatements;
  65. @Value("20")
  66. private int maxPoolPreparedStatementPerConnectionSize;
  67. /**
  68.  * 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
  69.  */
  70. @Value("stat,wall,log4j")
  71. private String filters;
  72. /**
  73.  * 通过connectProperties属性来打开mergeSql功能；慢SQL记录
  74.  */
  75. @Value("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500")
  76. private String connectionProperties;
  78. @Bean(name = "primaryDataSource")
  79. @Qualifier("primaryDataSource")
  80. public DataSource dataSource() {
  81.     return getDruidDataSource(username1, password1, dbUrl1);
  82. }
  84. @Bean(name = "secondDataSource")
  85. @Qualifier("secondDataSource")
  86. @Primary
  87. public DataSource secondaryDataSource() {
  88.     return getDruidDataSource(username2, password2, dbUrl2);
  89. }
  91. private DruidDataSource getDruidDataSource(String username, String password, String url) {
  92.     DruidDataSource datasource = new DruidDataSource();
  94.     datasource.setUrl(url);
  95.     datasource.setUsername(username);
  96.     datasource.setPassword(password);
  97.     datasource.setDriverClassName(driverClassName);
  99.     //configuration
  100.     datasource.setInitialSize(initialSize);
  101.     datasource.setMinIdle(minIdle);
  102.     datasource.setMaxActive(maxActive);
  103.     datasource.setMaxWait(maxWait);
  104.     datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
  105.     datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
  106.     datasource.setValidationQuery(validationQuery);
  107.     datasource.setTestWhileIdle(testWhileIdle);
  108.     datasource.setTestOnBorrow(testOnBorrow);
  109.     datasource.setTestOnReturn(testOnReturn);
  110.     datasource.setPoolPreparedStatements(poolPreparedStatements);
  111.     datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
  112.     try {
  113.         datasource.setFilters(filters);
  114.     } catch (SQLException e) {
  115.         logger.error("druid configuration initialization filter : {0}", e);
  116.     }
  117.     datasource.setConnectionProperties(connectionProperties);
  119.     return datasource;
  120. }

}

  1. <a name="pIgz2"></a>
  2. ## 2.4 JPA配置主数据库
  3. ```java
  4. package com.hikktn.config;
  6. import org.springframework.beans.factory.annotation.Autowired;
  7. import org.springframework.beans.factory.annotation.Qualifier;
  8. import org.springframework.boot.autoconfigure.domain.EntityScan;
  9. import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
  10. import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
  11. import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
  12. import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
  13. import org.springframework.context.annotation.Bean;
  14. import org.springframework.context.annotation.Configuration;
  15. import org.springframework.context.annotation.Primary;
  16. import org.springframework.core.env.Environment;
  17. import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
  18. import org.springframework.orm.jpa.JpaTransactionManager;
  19. import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
  20. import org.springframework.transaction.PlatformTransactionManager;
  21. import org.springframework.transaction.annotation.EnableTransactionManagement;
  23. import javax.persistence.EntityManager;
  24. import javax.sql.DataSource;
  25. import java.util.Map;
  27. @Configuration
  28. @EnableTransactionManagement
  29. @EntityScan(basePackages = "com.hikktn.domain.primary")
  30. @EnableJpaRepositories(
  31.         entityManagerFactoryRef = "entityManagerFactoryPrimary",
  32.         transactionManagerRef = "transactionManagerPrimary",
  33.         basePackages = {"com.hikktn.dao.primary"})
  34. public class PrimaryConfig {
  36.     @Autowired
  37.     @Qualifier("primaryDataSource")
  38.     private DataSource primaryDataSource;
  40.     @Autowired
  41.     private JpaProperties jpaProperties;
  42.     @Autowired
  43.     private HibernateProperties hibernateProperties;
  45.     private Map<String, Object> getVendorProperties() {
  46.         return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
  47.     }
  49.     @Primary
  50.     @Bean(name = "entityManagerPrimary")
  51.     public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
  52.         return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
  53.     }
  55.     @Primary
  56.     @Bean(name = "entityManagerFactoryPrimary")
  57.     public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
  58.         return builder
  59.                 .dataSource(primaryDataSource)
  60.                 .properties(getVendorProperties())
  61.                 .packages("com.hikktn.domain.primary")
  62.                 .persistenceUnit("primaryPersistenceUnit")
  63.                 .build();
  64.     }
  66.     @Primary
  67.     @Bean(name = "transactionManagerPrimary")
  68.     public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
  69.         return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
  70.     }
  71. }

2.5 JPA配置副数据库

  1. package com.hikktn.config;
  3. import org.springframework.beans.factory.annotation.Autowired;
  4. import org.springframework.beans.factory.annotation.Qualifier;
  5. import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
  6. import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
  7. import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
  8. import org.springframework.context.annotation.Bean;
  9. import org.springframework.context.annotation.Configuration;
  10. import org.springframework.core.env.Environment;
  11. import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
  12. import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
  13. import org.springframework.orm.jpa.JpaTransactionManager;
  14. import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
  15. import org.springframework.transaction.PlatformTransactionManager;
  16. import org.springframework.transaction.annotation.EnableTransactionManagement;
  18. import javax.annotation.Resource;
  19. import javax.persistence.EntityManager;
  20. import javax.sql.DataSource;
  21. import java.util.HashMap;
  22. import java.util.Map;
  24. @Configuration
  25. @EnableTransactionManagement
  26. @EnableJpaRepositories(
  27.         //实体管理
  28.         entityManagerFactoryRef="entityManagerFactorySecond",
  29.         //事务管理
  30.         transactionManagerRef="transactionManagerSecond",
  31.         //实体扫描,设置Repository所在位置
  32.         basePackages= { "com.hikktn.dao.second" })
  33. public class SecondConfig {
  35.     @Autowired
  36.     @Qualifier("secondDataSource")
  37.     private DataSource secondDataSource;
  39.     @Autowired
  40.     private JpaProperties jpaProperties;
  41.     @Autowired
  42.     private HibernateProperties hibernateProperties;
  44.     private Map<String, Object> getVendorProperties() {
  45.         return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
  46.     }
  48.     @Bean(name = "entityManagerSecond")
  49.     public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
  50.         return entityManagerFactorySecond(builder).getObject().createEntityManager();
  51.     }
  53.     @Bean(name = "entityManagerFactorySecond")
  54.     public LocalContainerEntityManagerFactoryBean entityManagerFactorySecond (EntityManagerFactoryBuilder builder) {
  55.         return builder
  56.                 .dataSource(secondDataSource)
  57.                 .properties(getVendorProperties())
  58.                 .packages("com.hikktn.domain.second")
  59.                 .persistenceUnit("secondPersistenceUnit")
  60.                 .build();
  61.     }
  63.     @Bean(name = "transactionManagerSecond")
  64.     PlatformTransactionManager transactionManagerSecond(EntityManagerFactoryBuilder builder) {
  65.         return new JpaTransactionManager(entityManagerFactorySecond(builder).getObject());
  66.     }
  67. }

2.6 主数据库Bean

  1. package com.hikktn.domain.primary;
  3. import lombok.AllArgsConstructor;
  4. import lombok.Data;
  5. import lombok.NoArgsConstructor;
  7. import javax.persistence.*;
  8. import java.io.Serializable;
  10. @Data
  11. @Entity
  12. @NoArgsConstructor
  13. @AllArgsConstructor
  14. @Table(name = "t_user")
  15. public class UserPrimaryEntity implements Serializable {
  17.     @Id
  18.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  19.     Long id;
  21.     /**
  22.      * 名称
  23.      */
  24.     String name;
  26.     /**
  27.      * 性别
  28.      */
  29.     String sex;
  30. }

2.7 副数据库Bean

  1. package com.hikktn.domain.second;
  3. import lombok.AllArgsConstructor;
  4. import lombok.Data;
  5. import lombok.NoArgsConstructor;
  7. import javax.persistence.*;
  8. import java.io.Serializable;
  10. @Data
  11. @Entity
  12. @NoArgsConstructor
  13. @AllArgsConstructor
  14. @Table(name = "t_user")
  15. public class UserSecondEntity implements Serializable {
  17.     @Id
  18.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  19.     Long id;
  21.     /**
  22.      * 名称
  23.      */
  24.     String name;
  26.     /**
  27.      * 性别
  28.      */
  29.     String sex;
  30. }

2.8 DAO层

  1. package com.hikktn.dao.primary;
  3. import com.hikktn.domain.primary.UserPrimaryEntity;
  4. import org.springframework.data.jpa.repository.JpaRepository;
  5. import org.springframework.stereotype.Repository;
  7. @Repository
  8. public interface UserPrimaryRepository extends JpaRepository<UserPrimaryEntity, Long> {
  10. }
  1. package com.hikktn.dao.second;
  3. import com.hikktn.domain.second.UserSecondEntity;
  4. import org.springframework.data.jpa.repository.JpaRepository;
  5. import org.springframework.stereotype.Repository;
  7. @Repository
  8. public interface UserSecondRepository extends JpaRepository<UserSecondEntity, Long> {
  10. }

2.9 启动类

  1. package com.hikktn;
  3. import org.springframework.boot.SpringApplication;
  4. import org.springframework.boot.autoconfigure.SpringBootApplication;
  6. @SpringBootApplication
  7. public class Application {
  9.     public static void main(String[] args) {
  10.         SpringApplication.run(Application.class, args);
  11.     }
  13. }

2.10 测试

  1. import com.hikktn.Application;
  2. import com.hikktn.dao.primary.UserPrimaryRepository;
  3. import com.hikktn.dao.second.UserSecondRepository;
  4. import org.springframework.beans.factory.annotation.Autowired;
  5. import org.springframework.boot.test.context.SpringBootTest;
  6. import org.junit.Test;
  7. import org.junit.runner.RunWith;
  8. import org.springframework.test.context.junit4.SpringRunner;
  10. @RunWith(SpringRunner.class)
  11. @SpringBootTest(classes = Application.class)
  12. public class SpringbootJapDatasourceApplicationTests {
  14.     @Autowired
  15.     private UserPrimaryRepository userPrimaryRepository;
  16.     @Autowired
  17.     private UserSecondRepository userSecondRepository;
  19.     @Test
  20.     public void contextLoads() {
  21.         System.out.println(userPrimaryRepository.findAll());
  22.         System.out.println(userSecondRepository.findAll());
  23.     }
  25. }
  • 数据库里的数据

image.png

  • 结果
    1. Hibernate: select userprimar0_.id as id1_0_, userprimar0_.name as name2_0_, userprimar0_.sex as sex3_0_ from t_user userprimar0_
    2. [UserPrimaryEntity(id=1, name=zhansan, sex=男), UserPrimaryEntity(id=2, name=tom, sex=男)]
    3. 2021-08-31 23:44:44.991  INFO 16516 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
    4. Hibernate: select usersecond0_.id as id1_0_, usersecond0_.name as name2_0_, usersecond0_.sex as sex3_0_ from t_user usersecond0_
    5. [UserSecondEntity(id=1, name=kitty, sex=女)]