Spring Boot 应用系列 1 -- Spring Boot 2 整合Spring Data JPA和Druid，双数据源

2022-09-22 12:47:22

最近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

 server.port=

 spring.application.name=spring-data-jpa

 #Serialize JPA entity to Json string.

 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

 #Data source

 db1.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

 db1.sqlserver.url=${DB1_URL:jdbc:sqlserver://127.0.0.1:1433;DatabaseName=MyTestDb1}

 db1.sqlserver.username=${DB1_UID:tester}

 db1.sqlserver.password=${DB1_PWD:tester}

 6 db1.sqlserver.initial-size=

 db1.sqlserver.min-idle=

 db1.sqlserver.max-active=

 db1.sqlserver.max-wait=

 db1.sqlserver.time-between-eviction-runs-millis=

 db1.sqlserver.min-evictable-idle-time-millis=

 db1.sqlserver.validation-query=select

 db1.sqlserver.test-on-borrow=true

 db1.sqlserver.test-While-Idle=true

 db1.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

 db1.sqlserver.filter.stat.slow-sql-millis=

 db1.sqlserver.jpa.hibernate.dialect=org.hibernate.dialect.SQLServerDialect

 db1.sqlserver.jpa.hibernate.show_sql=true

 db1.sqlserver.jpa.hibernate.format_sql=true

 #Data source

 db2.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

 db2.mysql.username=${DB2_UID:tester}

 db2.mysql.password=${DB2_PWD:tester}

 db2.mysql.initial-size=

 db2.mysql.min-idle=

 db2.mysql.max-active=

 db2.mysql.max-wait=

 db2.mysql.time-between-eviction-runs-millis=

 db2.mysql.min-evictable-idle-time-millis=

 db2.mysql.validation-query=select

 db2.mysql.test-on-borrow=true

 db2.mysql.test-While-Idle=true

 db2.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

 db2.mysql.filter.stat.slow-sql-millis=

 db2.mysql.jpa.hibernate.dialect=org.hibernate.dialect.MySQLDialect

 db2.mysql.jpa.hibernate.show_sql=true

 db2.mysql.jpa.hibernate.format_sql=true

 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

为便于管理，每个数据源一个配置类，此处只列出一个数据源：

 import java.util.Properties;

 import javax.sql.DataSource;

 import org.springframework.boot.context.properties.ConfigurationProperties;

 import org.springframework.context.annotation.Bean;

 import org.springframework.context.annotation.Configuration;

 import org.springframework.context.annotation.Primary;

 import org.springframework.context.annotation.PropertySource;

 import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

 import org.springframework.orm.jpa.JpaTransactionManager;

 import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;

 import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

 import org.springframework.transaction.PlatformTransactionManager;

 import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;

 import devutility.internal.util.PropertiesUtils;

 @Configuration

 @PropertySource("classpath:db.properties")

 @EnableJpaRepositories(basePackages = "devutility.test.database.springdatajpa.dao.mssql", entityManagerFactoryRef = "entityManagerFactory1", transactionManagerRef = "transactionManager1")

 public class DataSource1Configuration {

     @Primary

     @Bean

     @ConfigurationProperties("db1.sqlserver")

     public DataSource dataSource1() {

         return DruidDataSourceBuilder.create().build();

     }

     @Bean

     @ConfigurationProperties("db1.sqlserver.jpa")

     public Properties jpaProperties1() {

         return new Properties();

     }

     @Primary

     @Bean

     public LocalContainerEntityManagerFactoryBean entityManagerFactory1() {

         LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();

         localContainerEntityManagerFactoryBean.setDataSource(dataSource1());

         localContainerEntityManagerFactoryBean.setPackagesToScan(new String[] { "devutility.test.database.springdatajpa.dao.mssql.entity" });

         localContainerEntityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

         localContainerEntityManagerFactoryBean.setJpaPropertyMap(PropertiesUtils.toMap(jpaProperties1()));

         return localContainerEntityManagerFactoryBean;

     }

     @Bean

     public PlatformTransactionManager transactionManager1() {

         JpaTransactionManager transactionManager = new JpaTransactionManager();

         transactionManager.setEntityManagerFactory(entityManagerFactory1().getObject());

         return transactionManager;

     }

 }

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代码

码农公寓

相关文章