Spring Boot 應用系列 1 -- Spring Boot 2 整合Spring Data JPA和Druid，雙資料源

最近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=9006
2 spring.application.name=spring-data-jpa
3 
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=60000
10 db1.sqlserver.time-between-eviction-runs-millis=60000
11 db1.sqlserver.min-evictable-idle-time-millis=300000
12 db1.sqlserver.validation-query=select 1
13 db1.sqlserver.test-on-borrow=true
14 db1.sqlserver.test-While-Idle=true
15 db1.sqlserver.test-on-return=false
16 db1.sqlserver.pool-prepared-statements=false
17 db1.sqlserver.max-pool-prepared-statement-per-connection-size=20
18 
19 db1.sqlserver.filter.stat.enabled=true
20 db1.sqlserver.filter.stat.db-type=mssql
21 db1.sqlserver.filter.stat.log-slow-sql=true
22 db1.sqlserver.filter.stat.slow-sql-millis=2000
23 
24 db1.sqlserver.jpa.hibernate.dialect=org.hibernate.dialect.SQLServerDialect
25 db1.sqlserver.jpa.hibernate.show_sql=true
26 db1.sqlserver.jpa.hibernate.format_sql=true
27 
28 #Data source 2
29 db2.mysql.driver-class-name=com.mysql.jdbc.Driver
30 db2.mysql.url=${DB2_URL:jdbc:mysql://127.0.0.1:3306/Test}?useUnicode=true&useSSL=false
31 db2.mysql.username=${DB2_UID:tester}
32 db2.mysql.password=${DB2_PWD:tester}
33 db2.mysql.initial-size=1
34 db2.mysql.min-idle=1
35 db2.mysql.max-active=20
36 db2.mysql.max-wait=60000
37 db2.mysql.time-between-eviction-runs-millis=60000
38 db2.mysql.min-evictable-idle-time-millis=300000
39 db2.mysql.validation-query=select 1
40 db2.mysql.test-on-borrow=true
41 db2.mysql.test-While-Idle=true
42 db2.mysql.test-on-return=false
43 db2.mysql.pool-prepared-statements=false
44 db2.mysql.max-pool-prepared-statement-per-connection-size=20
45 
46 db2.mysql.filter.stat.enabled=true
47 db2.mysql.filter.stat.db-type=mysql
48 db2.mysql.filter.stat.log-slow-sql=true
49 db2.mysql.filter.stat.slow-sql-millis=2000
50 
51 db2.mysql.jpa.hibernate.dialect=org.hibernate.dialect.MySQLDialect
52 db2.mysql.jpa.hibernate.show_sql=true
53 db2.mysql.jpa.hibernate.format_sql=true
54 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 @Configuration
21 @PropertySource("classpath:db.properties")
22 @EnableJpaRepositories(basePackages = "devutility.test.database.springdatajpa.dao.mssql", entityManagerFactoryRef = "entityManagerFactory1", transactionManagerRef = "transactionManager1")
23 public class DataSource1Configuration {
24     @Primary
25     @Bean
26     @ConfigurationProperties("db1.sqlserver")
27     public DataSource dataSource1() {
28         return DruidDataSourceBuilder.create().build();
29     }
30 
31     @Bean
32     @ConfigurationProperties("db1.sqlserver.jpa")
33     public Properties jpaProperties1() {
34         return new Properties();
35     }
36 
37     @Primary
38     @Bean
39     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     @Bean
49     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;

@Configuration
public 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 druid
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin

5. 應用

配置好之後就該實作CRUD的基本功能了：

(1) 定義一個實體類Customer

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "Customer")
public class Customer extends BaseEntity {
    @Id
    private long id;

    @Column(name = "Name1")
    private String name;

    @Column(name = "Address1")
    private String address;

    private String city;
    private int state;
    private int zip;
    private String phone;
    private String email;

這裡需要注意以下幾點：

a. 所有JPA的實體類都需要有@Entity的注解；

b. @Table注解可選，如果不設定則表名=類名，如果表名和類名不一緻則需要配置；

c. @Column注解可選，用于表中字段名和實體類的屬性不一緻的情況；

d: 可在擁有@Id字段上添加@GeneratedValue注解用于生成主鍵。

(2) Dao層

a. 對于每一個表，隻需要定義一個簡單的接口并繼承JpaRepository<T, ID>即可實作基本的CRUD還有分頁操作：

package devutility.test.database.springdatajpa.dao.mysql;

import org.springframework.data.jpa.repository.JpaRepository;

import devutility.test.database.springdatajpa.dao.mysql.entity.Customer;

public interface CustomerRepository extends JpaRepository<Customer, Long> {

}

b. 假設你的實體類是通過聯表查詢得到的，或者對于一個單表來說基本的CRUD無法滿足你的需求，你可以通過使用@Query注解來手寫SQL語句實作，下面我們來示範一下這種情況：

首先定義一個實體類SimpleCustomer，該實體類隻包含Customer的部分字段。

package devutility.test.database.springdatajpa.dao.mysql.entity;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "Customer")
public class SimpleCustomer {
    @Id
    private long id;

    private String name;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

然後我們再定義SimpleCustomer對應的Repository：

package devutility.test.database.springdatajpa.dao.mysql;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import devutility.test.database.springdatajpa.dao.mysql.entity.SimpleCustomer;

public interface SimpleCustomerRepository extends JpaRepository<SimpleCustomer, Long> {
    @Query(value = "select ID, Name1 Name, Address1 Address, Created from Customer where Created > ?1 and Name1 is not null order by Created desc limit ?2, ?3", nativeQuery = true)
    List<SimpleCustomer> paging(Date startDate, int skip, int pageSize);
}

在SimpleCustomerRepository中，我們定義了一個接口paging，用來進行分頁查詢。注意，一定要有nativeQuery = true，否則報錯。

(3) 應用層

接下來就是怎樣使用上面定義的Repository了:

import java.text.ParseException;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import devutility.internal.models.OperationResult;
import devutility.internal.text.format.DateFormatUtils;
import devutility.test.database.springdatajpa.dao.mysql.CustomerRepository;
import devutility.test.database.springdatajpa.dao.mysql.SimpleCustomerRepository;
import devutility.test.database.springdatajpa.dao.mysql.entity.Customer;
import devutility.test.database.springdatajpa.dao.mysql.entity.SimpleCustomer;

@RestController
@RequestMapping("/mysql")
public class MySqlController {
    private int pageSize = 10;

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private SimpleCustomerRepository simpleCustomerRepository;

    @RequestMapping("/customer")
    public Customer findCustomer(String id) {
        return customerRepository.getOne(id);
    }

    @RequestMapping("/update-customer")
    public OperationResult updateCustomer(String id) {
        OperationResult result = new OperationResult();
        Customer customer = customerRepository.getOne(id);

        if (customer == null) {
            result.setErrorMessage(String.format("Customer with id %d not found!", id));
            return result;
        }

        customer.setName("Test-Customer");
        Customer updatedCustomer = customerRepository.save(customer);
        result.setData(updatedCustomer);
        return result;
    }

    @RequestMapping("/paging-customers")
    public List<Customer> pagingCustomers(int page) {
        Pageable pageable = PageRequest.of(page, pageSize, Sort.by(Direction.DESC, "Created"));
        Page<Customer> customerPage = customerRepository.findAll(pageable);
        System.out.println(String.format("TotalElements: %d", customerPage.getTotalElements()));
        System.out.println(String.format("TotalPages: %d", customerPage.getTotalPages()));
        return customerPage.getContent();
    }

    @RequestMapping("/paging-simple-customers")
    public List<SimpleCustomer> pagingSimpleCustomers(int page) throws ParseException {
        Date startDate = DateFormatUtils.parse("2018-01-01", "yyyy-MM-dd");
        return simpleCustomerRepository.paging(startDate, (page - 1) * pageSize, pageSize);
    }
}

除此之外，save方法也用于新增，delete方法用于删除，不再贅述。

Demo代碼

Spring Boot 應用系列 1 -- Spring Boot 2 整合Spring Data JPA和Druid，雙資料源

繼續閱讀

Springboot整合Mybatis增删查改、連接配接MYSQL資料庫及配置druid連接配接池

spring data jpa 和 jdbcTemplate混用

spring boot 和 mybatis 整合

mybatis-plus多資料源使用

Spring data jpa怎麼配置一個實體類映射兩張資料庫表

Spring Boot + Spring Cloud 內建 Druid 資料源Spring Boot + Spring Cloud 內建 Druid 資料源

SpringBoot 之 Spring Boot 內建 Druid

Druid 之 java.sql.SQLException: sql injection violation

Druid報錯：com.alibaba.druid.parser.ParserException:sysntax error,except RPAREN,actual EXCEPT EXCEPT

解決: com.alibaba.druid.filter.FilterManager : load filter error, filter not found : sl4j問題描述問題分析問題解決

springboot-雙資料源配置

Spring Data JPA更新和删除報錯

Spring Boot內建Spring Data

springbatch 結合springboot 的一個demo

傳回Json時多了一對中括号，關于可變參數和簡單問題思考

SpringBoot內建Mybatis,Redis，druid實作多資料源開發