Spring Boot 2.0.4 integrates spring data JPA and druid, dual source

Recently, team started to try Spring Boot + Spring data JPA as a solution to the data tier, and after a few laps on the internet, it was found that everyone was not in the JPA, for (1) MyBatis simple and intuitive enough, (2) The spring Data JPA at the bottom of hibernate is complex and performance-based.

But when we came to the spring boot world, we found that spring data JPA combined with spring boot could make DAO very simple, compared to spring data Jpa,mybatis's limited support for spring boot, such as (1) JPA comes with paging objects without setting up plugins; (2) An empty interface takes care of all the basic crud.

In an open-minded manner, I decided to integrate Spring Boot, Spring Data JPA and druid, and support SQL Server and MySQL, respectively, in the hope that this article will help the students who need the relevant technology.

1. Programs and versions

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 configuration file

We separate the main program configuration file application.properties from the database configuration file, so that the application.properties is not bloated.

(1) application.properties

1 server.port=90062 spring.application.name=spring-data-JPA34  string. 5 spring.jackson.serialization.fail-on-empty-beans=false

The function of line 5th is to avoid com.fasterxml.jackson.databind.exc.InvalidDefinitionException:No serializer found for class Org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer and no properties discovered to create Beanserializer, This configuration is valid only for MSSQL data sources.

(2) Db.properties

1#Data Source12Db1.sqlserver.driver-class-name=Com.microsoft.sqlserver.jdbc.SQLServerDriver3Db1.sqlserver.url=${db1_url:jdbc:sqlserver://127.0.0.1:1433;DATABASENAME=MYTESTDB1}4Db1.sqlserver.username=${db1_uid:tester}5db1.sqlserver.password=${db1_pwd:tester}
6Db1.sqlserver.initial-size=17db1.sqlserver.min-idle=18Db1.sqlserver.max-active= -9db1.sqlserver.max-wait=60000TenDb1.sqlserver. Time-between-eviction-runs-millis=60000 Onedb1.sqlserver.min-evictable-idle- Time-millis=300000 Adb1.sqlserver.validation-query=Select 1 -db1.sqlserver.test-on-borrow=true -db1.sqlserver.test-while-idle=true thedb1.sqlserver.test-on-return=false -db1.sqlserver.pool-prepared-statements=false -Db1.sqlserver.max-pool-prepared-statement-per-connection-size= - -  +Db1.sqlserver.filter.Stat. enabled=true -Db1.sqlserver.filter.Stat. db-type=MSSQL +Db1.sqlserver.filter.Stat. log-slow-sql=true ADb1.sqlserver.filter.Stat. slow-sql-millis= - at  -db1.sqlserver.jpa.hibernate.dialect=Org.hibernate.dialect.SQLServerDialect -Db1.sqlserver.jpa.hibernate.show_sql=true -Db1.sqlserver.jpa.hibernate.format_sql=true -  -#Data Source2 inDb2.mysql.driver-class-name=Com.mysql.jdbc.Driver -Db2.mysql.url=${db2_url:jdbc:mysql://127.0.0.1:3306/test}?useunicode=true&usessl=false toDb2.mysql.username=${db2_uid:tester} +db2.mysql.password=${db2_pwd:tester} -Db2.mysql.initial-size=1 thedb2.mysql.min-idle=1 *Db2.mysql.max-active= - $db2.mysql.max-wait=60000Panax NotoginsengDb2.mysql. Time-between-eviction-runs-millis=60000 -db2.mysql.min-evictable-idle- Time-millis=300000 thedb2.mysql.validation-query=Select 1 +db2.mysql.test-on-borrow=true Adb2.mysql.test-while-idle=true thedb2.mysql.test-on-return=false +db2.mysql.pool-prepared-statements=false -Db2.mysql.max-pool-prepared-statement-per-connection-size= - $  $Db2.mysql.filter.Stat. enabled=true -Db2.mysql.filter.Stat. db-type=MySQL -Db2.mysql.filter.Stat. log-slow-sql=true theDb2.mysql.filter.Stat. slow-sql-millis= - - Wuyidb2.mysql.jpa.hibernate.dialect=Org.hibernate.dialect.MySQLDialect theDb2.mysql.jpa.hibernate.show_sql=true -Db2.mysql.jpa.hibernate.format_sql=true Wudb2.mysql.jpa.hibernate.enable_lazy_load_no_trans=true

The configuration file can be divided into three parts: one is the JPA data source configuration (before line 5), the second is the JPA database connection pool configuration (line 6-line 17), the third is the special configuration of the Druid Connection pool (line 19-line 22), and the four is the custom configuration (line 24-line 26).

Note that line 54 is configured to add this line to address exceptions caused by hibernate lazy loading org.hibernate.LazyInitializationException:could not initialize proxy [ DEVUTILITY.TEST.DATABASE.SPRINGDATAJPA.DAO.MYSQL.ENTITY.CUSTOMER#100000123]-No Session

But let Enable_lazy_load_no_trans=true will bring some performance problems, specific reference https://vladmihalcea.com/the-hibernate-enable_lazy_load_no_ trans-anti-pattern/

In addition, there is another way to resolve org.hibernate.LazyInitializationException exceptions by adding @proxy (lazy = false) annotations on each entity type, which is tested for validity.

3. Java Config

For ease of administration, each data source is a configuration class, where only one data source is listed:

1 Importjava.util.Properties;2 3 ImportJavax.sql.DataSource;4 5 Importorg.springframework.boot.context.properties.ConfigurationProperties;6 ImportOrg.springframework.context.annotation.Bean;7 Importorg.springframework.context.annotation.Configuration;8 Importorg.springframework.context.annotation.Primary;9 ImportOrg.springframework.context.annotation.PropertySource;Ten Importorg.springframework.data.jpa.repository.config.EnableJpaRepositories; One ImportOrg.springframework.orm.jpa.JpaTransactionManager; A ImportOrg.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; - ImportOrg.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter; - ImportOrg.springframework.transaction.PlatformTransactionManager; the  - ImportCom.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder; -  - Importdevutility.internal.util.PropertiesUtils; +  - @Configuration +@PropertySource ("Classpath:db.properties") A@EnableJpaRepositories (basepackages = "Devutility.test.database.springdatajpa.dao.mssql", Entitymanagerfactoryref = "EntityManagerFactory1", Transactionmanagerref = "TransactionManager1") at  Public classdatasource1configuration { - @Primary - @Bean -@ConfigurationProperties ("Db1.sqlserver") -      PublicDataSource DataSource1 () { -         returndruiddatasourcebuilder.create (). build (); in     } -  to @Bean +@ConfigurationProperties ("DB1.SQLSERVER.JPA") -      PublicProperties jpaProperties1 () { the         return NewProperties (); *     } $ Panax Notoginseng @Primary - @Bean the      PublicLocalcontainerentitymanagerfactorybean EntityManagerFactory1 () { +Localcontainerentitymanagerfactorybean Localcontainerentitymanagerfactorybean =NewLocalcontainerentitymanagerfactorybean (); A Localcontainerentitymanagerfactorybean.setdatasource (DataSource1 ()); theLocalcontainerentitymanagerfactorybean.setpackagestoscan (NewString[] {"Devutility.test.database.springdatajpa.dao.mssql.entity" }); +Localcontainerentitymanagerfactorybean.setjpavendoradapter (NewHibernatejpavendoradapter ()); - Localcontainerentitymanagerfactorybean.setjpapropertymap (Propertiesutils.tomap (JpaProperties1 ())); $         returnLocalcontainerentitymanagerfactorybean; $     } -  - @Bean the      PublicPlatformtransactionmanager TransactionManager1 () { -Jpatransactionmanager TransactionManager =NewJpatransactionmanager ();Wuyi transactionmanager.setentitymanagerfactory (EntityManagerFactory1 (). GetObject ()); the         returnTransactionManager; -     } Wu}

4. Druid Console page Configuration

Detailed configuration of Druid See Druid official website

If you do not want to restrict access to the Druid console, you can ignore this section if you want to access the Druid console with a user name and password, as in the following two configurations:

(1) Java Config

ImportOrg.springframework.boot.web.servlet.FilterRegistrationBean;ImportOrg.springframework.boot.web.servlet.ServletRegistrationBean;ImportOrg.springframework.context.annotation.Bean;Importorg.springframework.context.annotation.Configuration;ImportCom.alibaba.druid.support.http.StatViewServlet;ImportCom.alibaba.druid.support.http.WebStatFilter; @Configuration Public classdruidconfiguration {@Bean PublicServletregistrationbean<statviewservlet>Druidstatviewservlet () {Servletregistrationbean<StatViewServlet> Servletregistrationbean =NewServletregistrationbean<> (NewStatviewservlet (), "/druid/*"); Servletregistrationbean.addinitparameter ("Loginusername", "admin"); Servletregistrationbean.addinitparameter ("Loginpassword", "admin"); Servletregistrationbean.addinitparameter ("Resetenable", "false"); returnServletregistrationbean; } @Bean PublicFilterregistrationbean<webstatfilter>Druidstatfilter () {Filterregistrationbean<WebStatFilter> Filterregistrationbean =NewFilterregistrationbean<> (NewWebstatfilter ()); Filterregistrationbean.setname ("Druidwebstatfilter"); Filterregistrationbean.addurlpatterns ("/*"); Filterregistrationbean.addinitparameter ("Exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); returnFilterregistrationbean; }}

(2). Add in the Application.properties file

 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 Code

Spring Boot 2.0.4 integrates spring data JPA and druid, dual source