Spring Boot 2.0.4整合Spring Data JPA和Druid，雙資料來源

標籤：test   database   控制台   官網   str   reg   檔案中   對象   相關   

最近Team開始嘗試使用Spring Boot + Spring Data JPA作為資料層的解決方案，在網上逛了幾圈之後發現大家並不待見JPA，理由是(1)MyBatis簡單直觀夠用，(2)以Hibernate為底層的Spring Data JPA複雜且效能一般。

但是當我們來到Spring Boot的世界後發現，相較於Spring Data JPA，MyBatis對Spring Boot的支援有限，Spring Data JPA與Spring Boot結合可以讓dao變得非常簡單，比如(1)JPA內建分頁對象，無需設定外掛程式；(2)一個空介面搞定所有基本CRUD。

本著虛心學習的態度，我決定將Spring Boot、Spring Data JPA和Druid三者整合在一起，並分別對SQL Server和MySQL進行支援，希望本文能夠協助到需要相關技術的同學。

1. 程式和版本

Spring Boot 2.0.4

mssql-jdbc 6.2.2.jre8

mysql-connector-java 5.1.46

druid-spring-boot-starter 1.1.10

2. properties設定檔

我們把主程式設定檔application.properties和資料庫設定檔分開，這樣可使application.properties不至於臃腫。

(1) application.properties

1 server.port=90062 spring.application.name=spring-data-jpa3 4 #Serialize JPA entity to Json string.5 spring.jackson.serialization.fail-on-empty-beans=false

第5行的作用是避免com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer and no properties discovered to create BeanSerializer，該配置只對MSSQL資料來源有效。

(2) db.properties

 1 #Data source 1 2 db1.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver 3 db1.sqlserver.url=${DB1_URL:jdbc:sqlserver://127.0.0.1:1433;DatabaseName=MyTestDb1} 4 db1.sqlserver.username=${DB1_UID:tester} 5 db1.sqlserver.password=${DB1_PWD:tester} 
 6 db1.sqlserver.initial-size=1 7 db1.sqlserver.min-idle=1 8 db1.sqlserver.max-active=20 9 db1.sqlserver.max-wait=6000010 db1.sqlserver.time-between-eviction-runs-millis=6000011 db1.sqlserver.min-evictable-idle-time-millis=30000012 db1.sqlserver.validation-query=select 113 db1.sqlserver.test-on-borrow=true14 db1.sqlserver.test-While-Idle=true15 db1.sqlserver.test-on-return=false16 db1.sqlserver.pool-prepared-statements=false17 db1.sqlserver.max-pool-prepared-statement-per-connection-size=2018 19 db1.sqlserver.filter.stat.enabled=true20 db1.sqlserver.filter.stat.db-type=mssql21 db1.sqlserver.filter.stat.log-slow-sql=true22 db1.sqlserver.filter.stat.slow-sql-millis=200023 24 db1.sqlserver.jpa.hibernate.dialect=org.hibernate.dialect.SQLServerDialect25 db1.sqlserver.jpa.hibernate.show_sql=true26 db1.sqlserver.jpa.hibernate.format_sql=true27 28 #Data source 229 db2.mysql.driver-class-name=com.mysql.jdbc.Driver30 db2.mysql.url=${DB2_URL:jdbc:mysql://127.0.0.1:3306/Test}?useUnicode=true&useSSL=false31 db2.mysql.username=${DB2_UID:tester}32 db2.mysql.password=${DB2_PWD:tester}33 db2.mysql.initial-size=134 db2.mysql.min-idle=135 db2.mysql.max-active=2036 db2.mysql.max-wait=6000037 db2.mysql.time-between-eviction-runs-millis=6000038 db2.mysql.min-evictable-idle-time-millis=30000039 db2.mysql.validation-query=select 140 db2.mysql.test-on-borrow=true41 db2.mysql.test-While-Idle=true42 db2.mysql.test-on-return=false43 db2.mysql.pool-prepared-statements=false44 db2.mysql.max-pool-prepared-statement-per-connection-size=2045 46 db2.mysql.filter.stat.enabled=true47 db2.mysql.filter.stat.db-type=mysql48 db2.mysql.filter.stat.log-slow-sql=true49 db2.mysql.filter.stat.slow-sql-millis=200050 51 db2.mysql.jpa.hibernate.dialect=org.hibernate.dialect.MySQLDialect52 db2.mysql.jpa.hibernate.show_sql=true53 db2.mysql.jpa.hibernate.format_sql=true54 db2.mysql.jpa.hibernate.enable_lazy_load_no_trans=true

該設定檔可分為三部分：一是JPA的資料來源基本資料配置（行5之前）；二是JPA的資料庫連接池配置（行6-行17）；三是Druid串連池的特殊配置（行19-行22）；四是自訂配置（行24-行26）。

需要注意行54的配置，加這一行是為瞭解決由Hibernate懶載入引起的異常org.hibernate.LazyInitializationException: could not initialize proxy [devutility.test.database.springdatajpa.dao.mysql.entity.Customer#100000123] - no Session

但是讓enable_lazy_load_no_trans=true會帶來一定的效能問題，具體參考https://vladmihalcea.com/the-hibernate-enable_lazy_load_no_trans-anti-pattern/

此外，解決org.hibernate.LazyInitializationException異常還有另外一種方法，在每個Entity類型上添加@Proxy(lazy = false)註解，經測試有效。

3. Java Config

為便於管理，每個資料來源一個配置類，此處只列出一個資料來源：

 1 import java.util.Properties; 2  3 import javax.sql.DataSource; 4  5 import org.springframework.boot.context.properties.ConfigurationProperties; 6 import org.springframework.context.annotation.Bean; 7 import org.springframework.context.annotation.Configuration; 8 import org.springframework.context.annotation.Primary; 9 import org.springframework.context.annotation.PropertySource;10 import org.springframework.data.jpa.repository.config.EnableJpaRepositories;11 import org.springframework.orm.jpa.JpaTransactionManager;12 import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;13 import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;14 import org.springframework.transaction.PlatformTransactionManager;15 16 import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;17 18 import devutility.internal.util.PropertiesUtils;19 20 @Configuration21 @PropertySource("classpath:db.properties")22 @EnableJpaRepositories(basePackages = "devutility.test.database.springdatajpa.dao.mssql", entityManagerFactoryRef = "entityManagerFactory1", transactionManagerRef = "transactionManager1")23 public class DataSource1Configuration {24     @Primary25     @Bean26     @ConfigurationProperties("db1.sqlserver")27     public DataSource dataSource1() {28         return DruidDataSourceBuilder.create().build();29     }30 31     @Bean32     @ConfigurationProperties("db1.sqlserver.jpa")33     public Properties jpaProperties1() {34         return new Properties();35     }36 37     @Primary38     @Bean39     public LocalContainerEntityManagerFactoryBean entityManagerFactory1() {40         LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();41         localContainerEntityManagerFactoryBean.setDataSource(dataSource1());42         localContainerEntityManagerFactoryBean.setPackagesToScan(new String[] { "devutility.test.database.springdatajpa.dao.mssql.entity" });43         localContainerEntityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());44         localContainerEntityManagerFactoryBean.setJpaPropertyMap(PropertiesUtils.toMap(jpaProperties1()));45         return localContainerEntityManagerFactoryBean;46     }47 48     @Bean49     public PlatformTransactionManager transactionManager1() {50         JpaTransactionManager transactionManager = new JpaTransactionManager();51         transactionManager.setEntityManagerFactory(entityManagerFactory1().getObject());52         return transactionManager;53     }54 }

4. Druid控制台頁面配置

Druid的詳細配置見Druid官網

如果你不想對Druid控制台的訪問加以限制可以忽略此節，如果你希望通過使用者名稱和密碼訪問Druid控制台，有如下兩種配置方式：

(1)Java Config

import org.springframework.boot.web.servlet.FilterRegistrationBean;import org.springframework.boot.web.servlet.ServletRegistrationBean;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import com.alibaba.druid.support.http.StatViewServlet;import com.alibaba.druid.support.http.WebStatFilter;@Configurationpublic class DruidConfiguration {    @Bean    public ServletRegistrationBean<StatViewServlet> druidStatViewServlet() {        ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");        servletRegistrationBean.addInitParameter("loginUsername", "admin");        servletRegistrationBean.addInitParameter("loginPassword", "admin");        servletRegistrationBean.addInitParameter("resetEnable", "false");        return servletRegistrationBean;    }    @Bean    public FilterRegistrationBean<WebStatFilter> druidStatFilter() {        FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>(new WebStatFilter());        filterRegistrationBean.setName("DruidWebStatFilter");        filterRegistrationBean.addUrlPatterns("/*");        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");        return filterRegistrationBean;    }}

(2). 在application.properties檔案中添加

#Configuration for druidspring.datasource.druid.stat-view-servlet.enabled=truespring.datasource.druid.stat-view-servlet.url-pattern=/druid/*spring.datasource.druid.stat-view-servlet.login-username=adminspring.datasource.druid.stat-view-servlet.login-password=admin

Demo代碼

Spring Boot 2.0.4整合Spring Data JPA和Druid，雙資料來源