工具篇-Spring boot JPA多数据源

写这篇博文是因为这个东西坑太多，首先说明下边实现的多数据源不是动态切换的，应该算是静态的。另外，如果对Spring JPA不熟悉的话，可以参见：SpringBoot 中 JPA 的使用

坑一、pom文件

pom中spring boot以及mysql connector的版本一定要注意。

  1  <parent>
  2          <groupId>org.springframework.boot</groupId>
  3          <artifactId>spring-boot-starter-parent</artifactId>
  4          <version>1.5.8.RELEASE</version>
  5          <relativePath/> 
  6      </parent>
  7  
  8      <properties>
  9          <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 10          <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
 11          <springboot.version>1.5.8.RELEASE</springboot.version>
 12          <java.version>1.8</java.version>
 13          <spring.boot.mainclass>xxxx.data.xxxx.Application</spring.boot.mainclass>
 14      </properties>
 15  
 16      <dependencies>
 17          <dependency>
 18              <groupId>io.springfox</groupId>
 19              <artifactId>springfox-swagger2</artifactId>
 20              <exclusions>
 21                  <exclusion>
 22                      <artifactId>spring-aop</artifactId>
 23                      <groupId>org.springframework</groupId>
 24                  </exclusion>
 25                  <exclusion>
 26                      <artifactId>spring-beans</artifactId>
 27                      <groupId>org.springframework</groupId>
 28                  </exclusion>
 29                  <exclusion>
 30                      <artifactId>spring-context</artifactId>
 31                      <groupId>org.springframework</groupId>
 32                  </exclusion>
 33                  <exclusion>
 34                      <artifactId>spring-core</artifactId>
 35                      <groupId>org.springframework</groupId>
 36                  </exclusion>
 37                  <exclusion>
 38                      <artifactId>spring-expression</artifactId>
 39                      <groupId>org.springframework</groupId>
 40                  </exclusion>
 41                  <exclusion>
 42                      <artifactId>jackson-annotations</artifactId>
 43                      <groupId>com.fasterxml.jackson.core</groupId>
 44                  </exclusion>
 45              </exclusions>
 46          </dependency>
 47          <dependency>
 48              <groupId>io.springfox</groupId>
 49              <artifactId>springfox-swagger-ui</artifactId>
 50          </dependency>
 51          <dependency>
 52              <groupId>org.springframework.boot</groupId>
 53              <artifactId>spring-boot-starter</artifactId>
 54              <version>${springboot.version}</version>
 55          </dependency>
 56          <dependency>
 57              <groupId>org.springframework.boot</groupId>
 58              <artifactId>spring-boot-starter-web</artifactId>
 59              <version>${springboot.version}</version>
 60          </dependency>
 61          <dependency>
 62              <groupId>org.springframework.boot</groupId>
 63              <artifactId>spring-boot-starter-data-jpa</artifactId>
 64              <version>${springboot.version}</version>
 65          </dependency>
 66          <dependency>
 67              <groupId>org.springframework.boot</groupId>
 68              <artifactId>spring-boot-starter-test</artifactId>
 69              <version>${springboot.version}</version>
 70              <scope>test</scope>
 71          </dependency>
 72      
 73          <dependency>
 74              <groupId>mysql</groupId>
 75              <artifactId>mysql-connector-java</artifactId>
 76              <version>5.1.36</version>
 77          </dependency>
 78          <dependency>
 79              <groupId>junit</groupId>
 80              <artifactId>junit</artifactId>
 81              <version>4.12</version>
 82          </dependency>
 83          <dependency>
 84              <groupId>org.springframework.boot</groupId>
 85              <artifactId>spring-boot-autoconfigure</artifactId>
 86              <version>1.5.8.RELEASE</version>
 87          </dependency>
 88      </dependencies>
 89  
 90      <build>
 91          <plugins>
 92              <plugin>
 93                  <groupId>org.springframework.boot</groupId>
 94                  <artifactId>spring-boot-maven-plugin</artifactId>
 95                  <version>${springboot.version}</version>
 96                  <configuration>
 97                      <!--<fork>true</fork>
 98                      <mainClass>${spring.boot.mainclass}</mainClass>-->
 99                      <layout>ZIP</layout>
100                  </configuration>
101                  <executions>
102                      <execution>
103                          <goals>
104                              <goal>repackage</goal>
105                          </goals>
106                      </execution>
107                  </executions>
108              </plugin>
109          </plugins>
110      </build>

坑二、config文件

有DataSourceConfig、PrimaryConfig、SecondaryConfig三个配置文件，路径大概是这样的：

首先DataSourceConfig：

 1 /**
 2  * Multi datasource profile.
 3  * @date 2019/04/26 10:58
 4  */
 5 
 6 @Configuration
 7 public class DataSourceConfig {
 8     @Bean(name = "primaryDataSource")
 9     @Qualifier("primaryDataSource")
10     @Primary
11     @ConfigurationProperties(prefix = "spring.datasource.primary")
12     public DataSource primaryDataSource() {
13         return DataSourceBuilder.create().build();
14     }
15 
16     @Bean(name = "secondaryDataSource")
17     @Qualifier("secondaryDataSource")
18     @ConfigurationProperties(prefix = "spring.datasource.secondary")
19     public DataSource secondaryDataSource() {
20         return DataSourceBuilder.create().build();
21     }
22 }

PrimaryConfig：

 1 @Configuration
 2 @EnableTransactionManagement
 3 @EnableJpaRepositories(
 4         entityManagerFactoryRef="entityManagerFactoryPrimary",
 5         transactionManagerRef="transactionManagerPrimary",
 6         basePackages = {"xxxx.data.xxxx.dao.primary.*"})
 7 public class PrimaryConfig {
 8 
 9     @Autowired
10     @Qualifier("primaryDataSource")
11     private DataSource primaryDataSource;
12 
13     @Autowired(required = false)
14     private JpaProperties jpaProperties;
15 
16     /**
17      * EntityManager profile.
18       */
19     @Primary
20     @Bean(name = "entityManagerPrimary")
21     public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
22         return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
23     }
24 
25     /**
26      * EntityManagerFactory profile.
27      */
28     @Primary
29     @Bean(name = "entityManagerFactoryPrimary")
30     public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
31         return builder.dataSource(primaryDataSource)
32                 .properties(getVendorProperties(primaryDataSource))
33                 .packages("xxxx.data.xxxx.entity.primary.*")
34                 .persistenceUnit("primaryPersistenceUnit")
35                 .build();
36     }
37 
38     /**
39      * Jpa profile.
40      */
41     private Map<String, String> getVendorProperties(DataSource dataSource) {
42         return jpaProperties.getHibernateProperties(dataSource);
43     }
44 
45     /**
46      * Transaction profile.
47      */
48     @Primary
49     @Bean(name = "transactionManagerPrimary")
50     public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
51         return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
52     }
53 }

SecondaryConfig:

 1 @Configuration
 2 @EnableTransactionManagement
 3 @EnableJpaRepositories(
 4         entityManagerFactoryRef="entityManagerFactorySecondary",
 5         transactionManagerRef="transactionManagerSecondary",
 6         basePackages = {"xxxx.data.xxxx.dao.secondary.*"})
 7 public class SecondaryConfig {
 8     @Autowired
 9     @Qualifier("secondaryDataSource")
10     private DataSource secondaryDataSource;
11 
12     @Autowired(required = false)
13     private JpaProperties jpaProperties;
14 
15     /**
16      * EntityManager profile.
17      */
18     @Bean(name = "entityManagerSecondary")
19     public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
20         return entityManagerFactorySecondary(builder).getObject().createEntityManager();
21     }
22 
23     /**
24      * EntityManagerFactory profile.
25      */
26     @Bean(name = "entityManagerFactorySecondary")
27     public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary (EntityManagerFactoryBuilder builder) {
28         return builder
29                 .dataSource(secondaryDataSource)
30                 .properties(getVendorProperties(secondaryDataSource))
31                 .packages("xxxx.data.xxxx.entity.secondary.*")
32                 .persistenceUnit("secondaryPersistenceUnit")
33                 .build();
34     }
35 
36     /**
37      * Jpa profile.
38      */
39     private Map<String, String> getVendorProperties(DataSource dataSource) {
40         return jpaProperties.getHibernateProperties(dataSource);
41     }
42 
43     /**
44      * Transaction profile.
45      */
46     @Bean(name = "transactionManagerSecondary")
47     public PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
48         return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
49     }
50 
51 }

上边这些配不好，就会报一些烂七八糟的错误：

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'operationLogAspect': Unsatisfied dependency expressed through field 'jobOperationLogDao'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'xxxxxxDao': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Not a managed type: class xxxxxx
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:588) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:366) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1264) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867) ~[spring-context-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543) ~[spring-context-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122) ~[spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:303) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1118) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1107) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
    at yidian.data.bear.Application.main(Application.java:18) [classes/:na]
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'xxxxxxxDao': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Not a managed type: class ......
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.
    ... 19 common frames omitted
Caused by: java.lang.IllegalArgumentException: Not a managed type: class

当然，如果你使用Springboot2.x的话，getHibernateProperties方法会报错的，对于这种情况，可以参考：https://blog.csdn.net/MrCoderStack/article/details/88658069，附上上边链接中的一张图片。

坑三、数据库配置文件

application-test.properties中配置了两个数据源，注意.url不好使的话，换成.jdbc.url试试：

 1 #########################################################
 2 ### Primary DataSource -- DataSource 1 configuration  ###
 3 #########################################################
 4 spring.datasource.primary.url=jdbc:mysql://ip:3307/数据库名
 5 spring.datasource.primary.username=用户名
 6 spring.datasource.primary.password=密码
 7 spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver
 8 
 9 #########################################################
10 ### Secondary DataSource -- DataSource 2 configuration ##
11 #########################################################
12 spring.datasource.secondary.url=jdbc:mysql://ip:3309/数据库名
13 spring.datasource.secondary.username=用户名
14 spring.datasource.secondary.password=密码
15 spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver
16 
17 #########################################################
18 ### Java Persistence Api --  Spring jpa configuration ###
19 #########################################################
20 # Specify the DBMS
21 spring.jpa.database=mysql
22 spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
23 # Hibernate ddl auto (create, create-drop, update, validate)
24 spring.jpa.hibernate.ddl-auto=validate
25 # Naming strategy
26 spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
27 # stripped before adding them to the entity manager
28 #spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

坑四、Entity映射无自增Id数据库表

JPA entity映射数据库表时需要一个唯一Id，而有的数据库表没有id怎么办，但是如果存在联合主键的话你可以采取IdClass注解的方式，这样的好处是写dao层代码方便，首先把联合主键封装成一个类，如下所示：

 1 public class ScPK implements Serializable {
 2     @Column(name = "student_name")
 3     private String studentName;
 4     @Column(name = "course_name")
 5     private String courseName;
 6     private String score;
 7 
 8     public String getStudentName() {
 9         return studentName;
10     }
11 
12     public void setStudentName(String studentName) {
13         this.studentName = studentName;
14     }
15 
16     public String getCourseName() {
17         return courseName;
18     }
19 
20     public void setCourseName(String courseName) {
21         this.courseName = courseName;
22     }
23 
24     public String getScore() {
25         return score;
26     }
27 
28     public void setScore(String score) {
29         this.score = score;
30     }
31 }

然后是实体类：

 1 @Entity
 2 @Table(name = "test")
 3 @IdClass(ScPK.class)
 4 public class ScEntity {
 5     @Id
 6     @Column(name = "student_name")
 7     private String studentName;
 8     @Id
 9     @Column(name = "course_name")
10     private String courseName;
11     
12     private String score;
13 
14     public String getStudentName() {
15         return studentName;
16     }
17 
18     public void setStudentName(String studentName) {
19         this.studentName = studentName;
20     }
21 
22     public String getCourseName() {
23         return courseName;
24     }
25 
26     public void setCourseName(String courseName) {
27         this.courseName = courseName;
28     }
29 
30     public String getScore() {
31         return score;
32     }
33 
34     public void setScore(String score) {
35         this.score = score;
36     }
37 }

也附上dao层代码吧，这里studentName、courseName与实体类中属性保持一致，与数据库表无关：

1 @Repository
2 public interface ScDao extends JpaRepository<ScEntity, Long> {
3 
4     @Query("select l from ScEntity l where l.studentName = :studentName and l.courseName = :courseName")
5     ScEntity findAllByStudentNameAndCourseName(@Param("studentName") String studentName, @Param("courseName") String courseName);
6 }

坑五、SpringBoot项目改多数据源后debug启动不起来

我就纳闷怎么改了多数据源，debug启动服务就起不来了，用postman也访问不了。有位大哥or大姐（https://www.cnblogs.com/fightingting/p/9683811.html）说自己几个月一直启动很慢，我是直接起不来了偶偶，幸亏ta提醒：idea左下角出现一行小字，Method breakpoints may dramatically slow down debugging，启动Springboot项目之前把断点去掉就解决了。

坑刘、Hibernate整合SpringBoot配置多数据源后8小时无操作数据库连接失效

问题：应用8小时不访问数据库，数据库连接自动失效访问失败，报错如下：

解决办法：在上面的properties中增加如下配置：

1 spring.datasource.primary.test-while-idle=true
2 spring.datasource.secondary.test-while-idle=true

posted @ 2019-04-28 13:45 akia开凯阅读(831) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

akia开凯