Spring Data JPA learning

Posted by DanAuito on Thu, 02 Jan 2020 03:24:15 +0100

1, Spring Data JPA

1, introduction

(1) Official website address:
　　https://spring.io/projects/spring-data-jpa
Reference documents:
　　https://docs.spring.io/spring-data/jpa/docs/2.2.3.RELEASE/reference/html/#preface

(2) Basic introduction:
Spring Data JPA is a set of JPA framework based on ORM framework and JPA specification encapsulation of spring. It enables developers to access and operate the database through simple code.
Note:
ORM framework: refers to Object Relational Mapping, that is, Object Relational Mapping. Metadata is used to describe the details of object and relationship mapping.
Metadata: generally in the form of XML file. Common ORM frameworks include mybatis and Hibernate.
JPA: refers to the Java Persistence API, which is the Java persistence layer API. The runtime entity objects are persisted to the database through xml or annotation mapping.

2. Used in the spinning boot project

(1) Introducing dependency in pom.xml file

[pom.xml]

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

(2) Configure in application.properties

[application.properties]

# jpa configuration
# Configure the database as mysql
spring.jpa.database=mysql
# Print sql statements in the console
spring.jpa.show-sql=true
# Every time you run the program, if there is no table, a new table will be created. If there is data in the table, it will not be emptied and will only be updated
spring.jpa.hibernate.ddl-auto=update
# Every time the program is run, a new table will be created if there is no table, and the data in the table will be cleared
#spring.jpa.hibernate.ddl-auto=create

2, Basic notes

1,@Entity

@Entity is written on a class to indicate that a class corresponds to a database table.
    Properties:
        Name, optional, table name used for custom mapping. If not, the class name is the table name by default.

[example 1: the default class name is table name]
import javax.persistence.Entity;

@Entity
public class Blog {
}

[example 2: user defined table name]
import javax.persistence.Entity;

@Entity(name="t_blog")
public class Blog {
}

2,@Table

@Table is written on the class, and is generally used with @ Entity to specify the related information of the data table.
    Properties:
        Name, corresponding to the data table name.
        Catalog, optional, corresponds to the catalog in the relational database.
        Schema, optional, corresponds to the schema in the relational database.

[example]
import javax.persistence.Entity;
import javax.persistence.Table;

@Entity(name = "blog")
@Table(name = "t_blog")
public class Blog {
}

Note: if @ Entity and @ Table define the name attribute at the same time, it is mainly @ Table.

3,@Id,@GeneratedValue

@Id is written on the variable in the class to specify the current variable as the primary key Id. Generally used with @ GeneratedValue.

@GeneratedValue is used with @ Id to set the primary key generation policy (auto increase primary key, database dependent).
Note:
    @The growth mode of the generated value (strategy = generationtype. Auto) primary key is automatically selected by the database
 When the library selects AUTO mode, the hibernate sequence table will be generated automatically.
    
    @GeneratedValue(strategy = GenerationType.IDENTITY) requires the database to select auto increment mode, oracle does not
 This method is supported by mysql.
    
    @Generated value (strategy = generationtype. Sequence) is generated by the sequence mechanism provided by the database
 Become primary key, not supported by mysql.


[example]
package com.lyh.blog.bean;

import lombok.Data;

import javax.persistence.*;

@Entity
@Table(name = "t_blog")
@Data
public class Blog {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
}

4,@Column

@Column is written on the variable of the class, which is used to specify the attributes (column name, uniqueness, nulls allowed, updates allowed, etc.) of the current variable mapped to the column in the data table.
Properties:
    Name: column name. 
    Unique: unique or not 
    nullable: whether null is allowed 
    insertable: whether to allow insertion 
    updatable: whether updates are allowed 
    Length: define length
    
[example]
import lombok.Data;

import javax.persistence.*;

@Entity
@Table(name = "t_blog")
@Data
public class Blog {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 36, unique = false, nullable = false, insertable = true, updatable = true)
    private String name;
}

5,@Temporal

@Temporal Will be used java.util The time date type conversion under coexists in the database (date, time, timestamp).
//Properties:
    TemporalType.DATE       java.sql.Date Date type, accurate to MM DD YY, for example“2019-12-17"
    TemporalType.TIME       java.sql.Time Time type, accurate to time, minute and second, for example“2019-12-17 00:00:00"
    TemporalType.TIMESTAMP  java.sql.Timestamp Time stamp, accurate to nanoseconds, e.g“2019-12-17 00:00:00.000000001"

[Example:
package com.lyh.blog.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "t_blog")
@Data
public class Blog {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 36, unique = false, nullable = false, insertable = true, updatable = true)
    private String name;

    @Temporal(TemporalType.TIMESTAMP)
    private Date createTime;

    @Temporal(TemporalType.DATE)
    private Date updateTime;
}

6. cascade

about @OneToOne,@ManyToMany,@OneToMany And other mapping relationships, involving cascading operations.
    CascadeType[] cascade() default {};
    //Defines the permissions that cascade is used to manipulate another associated entity for the currently set entity.
    
[Type of cascade:]
package javax.persistence;
public enum CascadeType {
    ALL,
    PERSIST,
    MERGE,
    REMOVE,
    REFRESH,
    DETACH;

    private CascadeType() {
    }
}

CascadeType.ALL         Has permissions for all cascading operations.
CascadeType.PERSIST     When the current entity class is saved, its associated entities are also saved.
CascadeType.MERGE       When the current entity data is merged, its associated entities are affected.
CascadeType.REMOVE      Delete the current entity, and the entity associated with it will also be deleted.
CascadeType.REFRESH     Refresh the current entity, and the entities associated with it will also be refreshed.
CascadeType.DETACH      Remove foreign key Association. When deleting an entity, there are foreign keys that cannot be deleted. Use this cascade to remove foreign keys.

7,mappedby

 mappedBy attribute is only available on @ OneToOne, @OneToMany, @ManyToMany, @ ManyToOne does not exist.
 
 The function of this attribute:
     Set the association relationship. The one-way association relationship does not need to be set, and the two-way relationship must be set to avoid the establishment of foreign key fields by both parties.
     
For a one to many relationship, the foreign key is always based on the multiple side (using @ JoinColumn), while mappedBy has the opposite side.
For example:
    department and Employee
    A Department corresponds to multiple employees. An employee belongs to a department.
    That is, the relationship between departments and employees is one to many.
[example]
public class Department {
    @OneToMany(mappedBy = "bookCategory", cascade = CascadeType.ALL)
    private List<Employee> employee;
}

public class Employee {
    @ManyToOne
    private Department department;
}

8,@OneToOne

@One to one is used to describe the one-to-one association between two data tables.
[property:]
    cascade, used to define cascading properties
    fetch, which is used to define LAZY load (LAZY, do not load without query), hot load (EAGER, default)
    mappedBy, used to define the maintained table (associated table)
    optional, which defines whether the object is allowed to be null.

3, Implementation of CRUD by JPA (taking a single entity class as an example)

1. Build environment (take Spring Boot 2.0 as an example)

(1) add dependency information

[pom.xml]

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>2.2.2.RELEASE</version>
      <relativePath/> <!-- lookup parent from repository -->
   </parent>
   <groupId>com.lyh.demo</groupId>
   <artifactId>jpa</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>jpa</name>
   <description>JPA Demo project for Spring Boot</description>

   <properties>
      <java.version>1.8</java.version>
   </properties>

   <dependencies>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-data-jpa</artifactId>
      </dependency>

      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-devtools</artifactId>
         <scope>runtime</scope>
         <optional>true</optional>
      </dependency>
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.18</version>
      </dependency>
      <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
         <optional>true</optional>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
         <exclusions>
            <exclusion>
               <groupId>org.junit.vintage</groupId>
               <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
         </exclusions>
      </dependency>
   </dependencies>

   <build>
      <plugins>
         <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
         </plugin>
      </plugins>
   </build>

</project>

(2) Configure connections

[application.properties]

# Database connection configuration
spring.datasource.url=jdbc:mysql://localhost:3306/lyh?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# jpa configuration
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

2. Write entity classes and mapping relationships

[com.lyh.demo.jpa.bean.Employee]
package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "emp")
@Data
@Proxy(lazy = false)
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 32)
    private String name;

    @Column(name = "age")
    private Integer age;

    @Temporal(TemporalType.TIMESTAMP)
    private Date createDate;
}

3. Write Dao layer

There is no need to write implementation classes. Only two interfaces (JpaRepository, jpaspecification executor) need to be inherited.

package com.lyh.demo.jpa.dao;

import com.lyh.demo.jpa.bean.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Component;

/**
 * JpaRepository<The entity type of the operation, the type > of the primary key in the entity class, encapsulates the CRUD basic operation.
 * JpaSpecificationExecutor<The entity type of operation > encapsulates complex operations, such as paging.
 */
@Component
public interface EmployeeDao extends JpaRepository<Employee, Integer>, JpaSpecificationExecutor<Employee> {
}

4. Write test class

[com.lyh.demo.jpa.JpaApplicationTests]

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Date;

@SpringBootTest
class JpaApplicationTests {
   @Autowired
   private EmployeeDao employeeDao;

   /**
    * When using the save method, if there is no id, add it directly.
    */
   @Test
   void testSave() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);
      employee.setCreateDate(new Date());
      employeeDao.save(employee);
   }

   /**
    * Using the save method, if the id exists, a query operation will be performed first. If the data exists, the data will be updated. Otherwise, the data will be saved.
    */
   @Test
   void testUpdate() {
      Employee employee = new Employee();
      employee.setId(10);
      employee.setName("tom");
      employee.setAge((int)(Math.random() * 100 + 1));
      employee.setCreateDate(new Date());
      employeeDao.save(employee);
   }

   /**
    * Query a piece of data according to id
    */
   @Test
   void testFindOne() {
      System.out.println(employeeDao.getOne(1));
   }

   /**
    * Query all data
    */
   @Test
   void testFindAll() {
      System.out.println(employeeDao.findAll());
   }

   /**
    * Delete data by id
    */
   @Test
   void testDelete() {
      employeeDao.deleteById(1);
   }
}

Test save insert

Test the save update.

Test query.

Test delete.

5. Pit encountered

(1) Error reported when executing test (getOne()):
　　org.hibernate.LazyInitializationException: could not initialize proxy [com.lyh.demo.jpa.bean.Employee#1] - no Session

Reason:

getOne() is executed by lazy loading internally. When to use it and when to trigger to get the value.

Solution 1:
Add @ Proxy(lazy = false) before entity class to cancel lazy loading

[Namely
package com.lyh.demo.jpa.bean;

import lombok.Data;
import org.hibernate.annotations.Proxy;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "emp")
@Data
@Proxy(lazy = false)
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 32)
    private String name;

    @Column(name = "age")
    private Integer age;

    @Temporal(TemporalType.TIMESTAMP)
    private Date createDate;
}

Solution 2:
Add @ Transactional before method execution.

[Namely

/**
 * Query a piece of data according to id
 */
@Test
@Transactional
void testFindOne() {
   System.out.println(employeeDao.getOne(2));
}

4, JPA writes sql statement -- jpql

1, introduction

Java Persistence Query Language, which can be understood as the sql statement used by JPA, is used to operate entity classes and attributes of entity classes.

2, use

(1) Define related methods in Dao interface and define sql statement through @ Query annotation.
When you need to update data, you need to use @ Modifying annotation. When testing, you need to use @ Transactional annotation.
If the method parameter is an entity class object, it can be obtained by: {{entity class name. Entity class property name}. And method parameters need to be declared with @ Param.

[com.lyh.demo.jpa.dao.EmployeeDao]

package com.lyh.demo.jpa.dao;

import com.lyh.demo.jpa.bean.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * JpaRepository<The entity type of the operation, the type > of the primary key in the entity class, encapsulates the CRUD basic operation.
 * JpaSpecificationExecutor<The entity type of operation > encapsulates complex operations, such as paging.
 * Among them, the method naming rule writing method is used.
 */
@Component
public interface EmployeeDao extends JpaRepository<Employee, Integer>, JpaSpecificationExecutor<Employee> {

    public List<Employee> getEmployeeByAge(Integer age);

    @Query("from Employee where age = ?1")
    public List<Employee> getEmployeeByAge1(Integer age);

    public List<Employee> getEmployeeByAgeAndName(Integer age, String name);

    @Query("from Employee where name = ?2 and age = ?1")
    public List<Employee> getEmployeeByAgeAndName1(Integer age, String name);

    @Query("update Employee set age = :#{#employee.age} where name = :#{#employee.name}")
    @Modifying
    public void updateEmpAgeByName(@Param("employee") Employee employee);
}

(2) Testing

[com.lyh.demo.jpa.JpaApplicationTestJSQLs]

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;

import javax.transaction.Transactional;

@SpringBootTest
class JpaApplicationTestJSQLs {
   @Autowired
   private EmployeeDao employeeDao;

   @Test
   void testGetEmployeeByAge() {
      System.out.println(employeeDao.getEmployeeByAge(40));
   }

   @Test
   void testGetEmployeeByAge1() {
      System.out.println(employeeDao.getEmployeeByAge1(40));
   }

   @Test
   void testGetEmployeeByAgeAndName() {
      System.out.println(employeeDao.getEmployeeByAgeAndName(40, "tom"));
   }

   @Test
   void testGetEmployeeByAgeAndName1() {
      System.out.println(employeeDao.getEmployeeByAgeAndName1(41, "tom"));
   }

   @Test
   @Transactional
   void testUpdateEmpAgeByName() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(11);
      employeeDao.updateEmpAgeByName(employee);
   }
}

Test getEmployeeByAge

The difference between testing getEmployeeByAge1 and getEmployeeByAge is that getEmployeeByAge1 is a custom query method.

Test getEmployeeByAgeAndName

The test getEmployeeByAgeAndName1 is also a custom query method.

Test the updateemapagebyname, using the method of object parameter passing. The @ Modifying annotation is required for update operation.

3. Pit encountered

(1) Error reporting: (JDBC style parameters (?) are not supported for JPA queries.)
　　org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'employeeDao': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: JDBC style parameters (?) are not supported for JPA queries.

Resolution: specify the matching parameter location on the placeholder (starting with 1)

[com.lyh.demo.jpa.dao.EmployeeDao]

@Query("from Employee where age = ?1")
public List<Employee> getEmployeeByAge1(Integer age);

(2) Use entity class object as parameter to query jpql to get a parameter error of entity class.
Solution: use: {} employee.age} to get the parameters.

@Query("update Employee set age = :#{#employee.age} where name = :#{#employee.name}")
@Modifying
public void updateEmpAgeByName(@Param("employee") Employee employee);

(3) For update and delete operations, @ Transactional and @ Modifying annotations are required, otherwise an error will be reported.

5, JPA writes sql statement -- sql, method rule name query

1. sql syntax rules

The writing method is similar to jpql. You need to use @ Query annotation, but you need to use nativeQuery = true.
If nativeQuery = false, jpql is used.
If nativeQuery = true, sql is used.

(1) Configuration

[application.properties]

# Database connection configuration
spring.datasource.url=jdbc:mysql://localhost:3306/lyh?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# jpa configuration
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

(2) dao layer

[com/lyh/demo/jpa/dao/EmployeeDao.java]

package com.lyh.demo.jpa.dao;

import com.lyh.demo.jpa.bean.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface EmployeeDao extends JpaRepository<Employee, Integer>, JpaSpecificationExecutor<Employee> {

    @Query(value = "select * from emp", nativeQuery = true)
    public List<Employee> getEmployee();
}

(3)bean
Entity class.

[com/lyh/demo/jpa/bean/Employee.java]

package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "emp")
@Data
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 32)
    private String name;

    @Column(name = "age")
    private Integer age;

    @Temporal(TemporalType.TIMESTAMP)
    private Date createTime;
}

(4)test

[com/lyh/demo/jpa/JpaApplicationTests.java]

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

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

@SpringBootTest
class JpaApplicationTests {

    @Autowired
    private EmployeeDao employeeDao;

    @Test
    void testSave() {
        Employee employee = new Employee();
        employee.setName("tom");
        employee.setAge(22);
        employee.setCreateTime(new Date());
        employeeDao.save(employee);
    }

    @Test
    void testGetEmployee() {
        List<Employee> employeeList = employeeDao.getEmployee();
        for (Employee employee: employeeList) {
            System.out.println(employee);
        }
    }
}

Test screenshot:
Execute the testSave() method twice to add several pieces of test data.

Test testGetEmployee() method and sql statement.

2. Method naming rule query

It is a further encapsulation of jpql. You only need to define the method name according to the method name rules provided by SpringDataJPA, so you don't need to configure the jpql statement, and it will automatically resolve to the sql statement according to the method name.
(1) Key definition:
　　https://docs.spring.io/spring-data/jpa/docs/2.2.3.RELEASE/reference/html/#repository-query-keywords
Detailed documents:
　　https://blog.csdn.net/qq_32448349/article/details/89445216

(2) For example:

findEmployeesByAgeAndName is equivalent to select * from EMP where age =? And name =?
Query by property name.

findEmployeesByNameLike is equivalent to select * from emp where name like?
Fuzzy query based on attributes

(3) Test:

[com/lyh/demo/jpa/dao/EmployeeDao.java]

package com.lyh.demo.jpa.dao;

import com.lyh.demo.jpa.bean.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface EmployeeDao extends JpaRepository<Employee, Integer>, JpaSpecificationExecutor<Employee> {

    @Query(value = "select * from emp", nativeQuery = true)
    public List<Employee> getEmployee();

    public List<Employee> findEmployeesByAgeAndName(Integer age, String name);

    public List<Employee> findEmployeesByNameLike(String name);
}



[com/lyh/demo/jpa/JpaApplicationTests.java]

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

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

@SpringBootTest
class JpaApplicationTests {

    @Autowired
    private EmployeeDao employeeDao;

    @Test
    void testSave() {
        Employee employee = new Employee();
        employee.setName("tom");
        employee.setAge(22);
        employee.setCreateTime(new Date());
        employeeDao.save(employee);
    }

    @Test
    void testGetEmployee() {
        List<Employee> employeeList = employeeDao.getEmployee();
        for (Employee employee: employeeList) {
            System.out.println(employee);
        }
    }

    @Test
    void testFindEmployeesByAgeAndName() {
        List<Employee> employeeList = employeeDao.findEmployeesByAgeAndName(22, "tom");
        for (Employee employee: employeeList) {
            System.out.println(employee);
        }
    }

    @Test
    void testFindEmployeesByNameLike() {
        List<Employee> employeeList = employeeDao.findEmployeesByNameLike("t%");
        for (Employee employee: employeeList) {
            System.out.println(employee);
        }
    }
}

Basic query:

Fuzzy query:

6, Dynamic query (jpaspecification executor, Specification)

1,JpaSpecificationExecutor

The jpaspecification executor is an interface. The query statements are defined in the Specification.

package org.springframework.data.jpa.repository;

import java.util.List;
import java.util.Optional;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.lang.Nullable;

public interface JpaSpecificationExecutor<T> {
    // Query single object
  Optional<T> findOne(@Nullable Specification<T> var1);

    // Query object list
  List<T> findAll(@Nullable Specification<T> var1);

    // Query object list and return paging data
  Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);

    // Query object list and sort
  List<T> findAll(@Nullable Specification<T> var1, Sort var2);

    // Results of statistical query
  long count(@Nullable Specification<T> var1);
}

2,Specification

Define sql statements. It is also an interface, which requires a custom implementation class. You need to override the toPredicate() method.

// Root Refers to the root object of the query, which can get any properties.
// CriteriaQuery Standard query, you can customize the query method (generally not used)
// CriteriaBuilder It refers to the constructor of query and encapsulates many query conditions
Predicate toPredicate(Root<T> var1, CriteriaQuery<?> var2, CriteriaBuilder var3);

package org.springframework.data.jpa.domain;

import java.io.Serializable;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.lang.Nullable;

public interface Specification<T> extends Serializable {
  long serialVersionUID = 1L;

  static <T> Specification<T> not(@Nullable Specification<T> spec) {
    return spec == null ? (root, query, builder) -> {
      return null;
    } : (root, query, builder) -> {
      return builder.not(spec.toPredicate(root, query, builder));
    };
  }

  @Nullable
  static <T> Specification<T> where(@Nullable Specification<T> spec) {
    return spec == null ? (root, query, builder) -> {
      return null;
    } : spec;
  }

  @Nullable
  default Specification<T> and(@Nullable Specification<T> other) {
    return SpecificationComposition.composed(this, other, (builder, left, rhs) -> {
      return builder.and(left, rhs);
    });
  }

  @Nullable
  default Specification<T> or(@Nullable Specification<T> other) {
    return SpecificationComposition.composed(this, other, (builder, left, rhs) -> {
      return builder.or(left, rhs);
    });
  }

  @Nullable
  Predicate toPredicate(Root<T> var1, CriteriaQuery<?> var2, CriteriaBuilder var3);
}

3. Basic use

(1) Step:
Step 1: implement the Specification interface (define generics, which is the object type of the query), and override the topredicte() method.
Step 2: define criteria builder query criteria.

(2) General query

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;
import java.util.Date;
import java.util.List;

@SpringBootTest
class JpaApplicationTests {

   @Autowired
   private EmployeeDao employeeDao;

   @Test
   void testSave() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);
      employee.setCreateTime(new Date());
      employeeDao.save(employee);
   }


   @Test
   void testSpecification() {
      // Define inner class and generically query object
      Specification<Employee> specification = new

         Specification<Employee>() {

            @Override
            public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
               // Get properties for comparison
               Path<Object> name = root.get("name");
               // Build query criteria, select * from emp where name = "tom";
               Predicate predicate = criteriaBuilder.equal(name, "tom");
               return predicate;
            }


         };
      List<Employee> employeeList = employeeDao.findAll(specification);
      for (Employee employee : employeeList) {
         System.out.println(employee);
      }
   }
}

(3) Multi condition splicing and fuzzy query

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;
import java.util.Date;
import java.util.List;

@SpringBootTest
class JpaApplicationTests {

   @Autowired
   private EmployeeDao employeeDao;

   @Test
   void testSave() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);
      employee.setCreateTime(new Date());
      employeeDao.save(employee);
   }


   @Test
   void testSpecification() {
      // Define inner class and generically query object
      Specification<Employee> specification = new
         Specification<Employee>() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
               // Get properties for comparison
               Path<String> name = root.get("name");
               Path<Integer> age = root.get("age");

               // Build query criteria, select * from emp where name like "to%" and age >= 22;
               Predicate predicate1 = criteriaBuilder.like(name, "to%");
               Predicate predicate2 = criteriaBuilder.ge(age, 22);
               Predicate predicate = criteriaBuilder.and(predicate1, predicate2);
               return predicate;
            }
         };
      List<Employee> employeeList = employeeDao.findAll(specification);
      for (Employee employee : employeeList) {
         System.out.println(employee);
      }
   }
}

(4) Sort
On the basis of the above example multi condition splicing code, add sorting to make the data output in the descending order of id.

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;
import java.util.Date;
import java.util.List;

@SpringBootTest
class JpaApplicationTests {

   @Autowired
   private EmployeeDao employeeDao;

   @Test
   void testSave() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);
      employee.setCreateTime(new Date());
      employeeDao.save(employee);
   }


   @Test
   void testSpecification() {
      // Define inner class and generically query object
      Specification<Employee> specification = new

         Specification<Employee>() {

            @Override
            public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
               // Get properties for comparison
               Path<String> name = root.get("name");
               Path<Integer> age = root.get("age");

               // Build query criteria, select * from emp where name like "to%" and age >= 22;
               Predicate predicate1 = criteriaBuilder.like(name, "to%");
               Predicate predicate2 = criteriaBuilder.ge(age, 22);
               Predicate predicate = criteriaBuilder.and(predicate1, predicate2);
               return predicate;
            }


         };
      // Definition sort(Sort.Direction.DESC，Descending order; Sort.Direction.ASC，Ascending order)
      Sort sort = Sort.by(Sort.Direction.DESC, "id");
      List<Employee> employeeList = employeeDao.findAll(specification, sort);
      for (Employee employee : employeeList) {
         System.out.println(employee);
      }
   }
}

(5) Pagination
Add paging based on the above example of multi conditional splicing code. As shown in the following example, page 2 data is obtained by paging one piece of data on each page.

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;
import java.util.Date;

@SpringBootTest
class JpaApplicationTests {

   @Autowired
   private EmployeeDao employeeDao;

   @Test
   void testSave() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);
      employee.setCreateTime(new Date());
      employeeDao.save(employee);
   }


   @Test
   void testSpecification() {
      // Define inner class and generically query object
      Specification<Employee> specification = new

         Specification<Employee>() {

            @Override
            public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
               // Get properties for comparison
               Path<String> name = root.get("name");
               Path<Integer> age = root.get("age");

               // Build query criteria, select * from emp where name like "to%" and age >= 22;
               Predicate predicate1 = criteriaBuilder.like(name, "to%");
               Predicate predicate2 = criteriaBuilder.ge(age, 22);
               Predicate predicate = criteriaBuilder.and(predicate1, predicate2);
               return predicate;
            }


         };
      // Define paging, where the first parameter refers to the number of pages of the current query (starting from 0), and the second parameter refers to the number of pages per page
      Pageable pageable = PageRequest.of(1, 1);
      Page<Employee> page = employeeDao.findAll(specification, pageable);
      // Get the collection of current query data
      System.out.println(page.getContent());
      // Get the total number
      System.out.println(page.getTotalElements());
      // Get total pages
      System.out.println(page.getTotalPages());
   }
}

7, Multi table operation

1. One to one (@ OneToOne)

A piece of data in a table corresponds to a piece of data in another table.

2. One to many (@ OneToMany, @ ManyToOne)

(1) Basic concepts:
One piece of data in a table corresponds to multiple pieces of data in another table.
The Party of "one" is called: main table.
The "many" side is called: slave table.
Usually, the foreign key is placed on the slave table, that is, a column is added as the foreign key from the table, and depends on a column of the main table.

(2) sql statement table building

[Example:
//The relationship between employees and departments.
//A department can have multiple employees, and an employee belongs to a department. At this time, there is a one to many relationship between departments and employees.
//The Department table is the main table and the employee table is the slave table. The foreign key is based on the employee table (slave table).

CREATE TABLE dept (
    deptId int primary key auto_increment,
    deptName varchar(20)
);

CREATE TABLE emp (
    id int primary key auto_increment,
    name varchar(32),
    age int,
    deptId int,
    foreign key(deptId) references dept(deptId)
);

(3) jpa table building

[step:]
Step 1: clarify the relationship between the two tables
 Step 2: determine the relationship between tables, one to many (foreign key) or many to many (intermediate table).
Step 3: write entity class and establish table relation (declare corresponding attribute) in entity class.
Step 4: configure the mapping relationship

Step1,Step2:
There is a one to many relationship between the Department table and the employee table, so you need to establish a foreign key on the employee table.

[com/lyh/demo/jpa/bean/Employee.java]
package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "emp")
@Data
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 32)
    private String name;

    @Column(name = "age")
    private Integer age;
}

[com/lyh/demo/jpa/bean/Department.java]
package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;

@Entity
@Table(name = "dept")
@Data
public class Department {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private int deptId;
   private String deptName;
}

Step3,Step4:

Establish relationships between entity classes and add mapping relationships.

[com/lyh/demo/jpa/bean/Employee.java]
package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "emp")
@Data
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 32)
    private String name;

    @Column(name = "age")
    private Integer age;

    /**
     * The relationship between employee table and department table is many to one. Therefore, a common attribute should be defined in Employee class to save department information.
     * And use @ ManyToOne to define the mapping relationship (many to one)
     * Use @ JoinColumn to define the foreign key (defined on the slave table, name refers to the foreign key name, and referencedColumnName refers to the primary key of the dependent primary table).
     */
    @ManyToOne(targetEntity = Department.class)
    @JoinColumn(name = "deptId", referencedColumnName = "deptId")
    private Department department;
}



[com/lyh/demo/jpa/bean/Department.java]
package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "dept")
@Data
public class Department {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private int deptId;
   private String deptName;

   /**
    * Department table and employee table are one to many relationships, so set should be defined in department entity class to save employee information.
    * And use @ onetoman to specify the mapping relationship (one to many).
    * You can use @ JoinColumn to create a foreign key. At this time, you can maintain the foreign key (one party). If you assign a value to this foreign key, there will be an update compared with the other party.
    * If you discard foreign key maintenance, you can use mapperBy to specify an association relationship whose value is the property name maintained by the corresponding class.
    */
// @OneToMany(targetEntity = Employee.class)
// @JoinColumn(name = "id", referencedColumnName = "deptId")
   @OneToMany(mappedBy = "department")
   private Set<Employee> employees = new HashSet<Employee>();
}

(4) Testing

The file structure is as follows:

Code:

[application.properties]

# Database connection configuration
spring.datasource.url=jdbc:mysql://localhost:3306/lyh?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# jpa configuration
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect


[com/lyh/demo/jpa/bean/Department.java]

package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "dept")
@Data
public class Department {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private int deptId;
   private String deptName;

   /**
    * Department table and employee table are one to many relationships, so set should be defined in department entity class to save employee information.
    * And use @ onetoman to specify the mapping relationship (one to many).
    * You can use @ JoinColumn to create a foreign key. At this time, you can maintain the foreign key (one party). If you assign a value to this foreign key, there will be an update compared with the other party.
    * If you discard foreign key maintenance, you can use mapperBy to specify an association relationship whose value is the property name maintained by the corresponding class.
    */
// @OneToMany(targetEntity = Employee.class)
// @JoinColumn(name = "id", referencedColumnName = "deptId")
   @OneToMany(mappedBy = "department")
   private Set<Employee> employees = new HashSet<Employee>();
}


[com/lyh/demo/jpa/bean/Employee.java]

package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "emp")
@Data
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 32)
    private String name;

    @Column(name = "age")
    private Integer age;

    /**
     * The relationship between employee table and department table is many to one. Therefore, a common attribute should be defined in Employee class to save department information.
     * And use @ ManyToOne to define the mapping relationship (many to one)
     * Use @ JoinColumn to define the foreign key (defined on the slave table, name refers to the foreign key name, and referencedColumnName refers to the primary key of the dependent primary table).
     */
    @ManyToOne(targetEntity = Department.class)
    @JoinColumn(name = "deptId", referencedColumnName = "deptId")
    private Department department;
}


[com/lyh/demo/jpa/dao/DepartmentDao.java]

package com.lyh.demo.jpa.dao;

import com.lyh.demo.jpa.bean.Department;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Component;

@Component
public interface DepartmentDao extends JpaRepository<Department, Integer>, JpaSpecificationExecutor<Department> {
}


[com/lyh/demo/jpa/dao/EmployeeDao.java]

package com.lyh.demo.jpa.dao;

import com.lyh.demo.jpa.bean.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface EmployeeDao extends JpaRepository<Employee, Integer>, JpaSpecificationExecutor<Employee> {
}


[com/lyh/demo/jpa/JpaApplicationTests.java]

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Department;
import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.DepartmentDao;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.criteria.*;
import java.util.Date;

@SpringBootTest
class JpaApplicationTests {

   @Autowired
   private EmployeeDao employeeDao;

   @Autowired
   private DepartmentDao departmentDao;

   @Test
   void testSave1() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);

      Department department = new Department();
      department.setDeptId(1);
      department.setDeptName("Development");

      employeeDao.save(employee);
      departmentDao.save(department);
   }

   @Test
   void testSave2() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);

      Department department = new Department();
      department.setDeptId(1);
      department.setDeptName("Development");

      // Maintain foreign key, i.e. add value (execution order here may cause error)
      employee.setDepartment(department);
      // departmentDao.save(department);
      employeeDao.save(employee);
      departmentDao.save(department);
   }
}

Test screenshot:

Test testSave1(), the foreign key is null because it is not maintained.

Test testSave2() to maintain the foreign key. The foreign key has a value.

(5) Cascade operation

Note that in the above example, each table needs to be operated once, which can be tedious sometimes.

In this case, cascading can be used to operate one entity class and another entity class associated with it.

The possible problem of testSave2() in the above example: when the data is empty, the employee Dao. Save (employee) is executed first;

Then execute departmentDao.save(department); at this time, because the main table has no data, adding foreign keys from the table will make an error.

Solution 1:

Exchange the order in which sql is executed.

Solution 2:

Take the cascading attribute (cascade = CascadeType.ALL).

Modify the above example code.

[com/lyh/demo/jpa/bean/Department.java]
package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "dept")
public class Department {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private int deptId;
   private String deptName;

   /**
    * Department table and employee table are one to many relationships, so set should be defined in department entity class to save employee information.
    * And use @ onetoman to specify the mapping relationship (one to many).
    * You can use @ JoinColumn to create a foreign key. At this time, you can maintain the foreign key (one party). If you assign a value to this foreign key, there will be an update compared with the other party.
    * If you discard foreign key maintenance, you can use mapperBy to specify an association relationship whose value is the property name maintained by the corresponding class.
    * Use cascade to define cascading properties.
    */
// @OneToMany(targetEntity = Employee.class)
// @JoinColumn(name = "id", referencedColumnName = "deptId")
   @OneToMany(mappedBy = "department", cascade = CascadeType.ALL)
   private Set<Employee> employees = new HashSet<Employee>();

   public int getDeptId() {
      return deptId;
   }

   public void setDeptId(int deptId) {
      this.deptId = deptId;
   }

   public String getDeptName() {
      return deptName;
   }

   public void setDeptName(String deptName) {
      this.deptName = deptName;
   }

   public Set<Employee> getEmployees() {
      return employees;
   }

   public void setEmployees(Set<Employee> employees) {
      this.employees = employees;
   }
}



[com/lyh/demo/jpa/JpaApplicationTests.java]

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Department;
import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.DepartmentDao;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.criteria.*;
import java.util.Date;

@SpringBootTest
class JpaApplicationTests {

   @Autowired
   private EmployeeDao employeeDao;

   @Autowired
   private DepartmentDao departmentDao;

   @Test
   void testSave1() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);

      Department department = new Department();
      department.setDeptId(1);
      department.setDeptName("Development");

      employeeDao.save(employee);
      departmentDao.save(department);
   }

   @Test
   void testSave2() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);

      Department department = new Department();
      department.setDeptId(1);
      department.setDeptName("Development");

      // Maintain foreign key, i.e. add value
      employee.setDepartment(department);
      department.getEmployees().add(employee);
      departmentDao.save(department);
   }
}

Note:

Use the pit encountered by the cascade (stack overflow java.lang.StackOverflowError). Remove @ Data, manually getter and setter (I don't know why, but this is the solution).

(6) Object navigation query

By querying an object, you can query its associated objects.

For one to many relationships, if you want to query multiple objects from one object, the default mode is delayed loading.

If one object is queried from multiple objects, it will be loaded immediately by default.

Modify the above code.
[com/lyh/demo/jpa/bean/Department.java]

package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "dept")
public class Department {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private int deptId;
   private String deptName;

   /**
    * Department table and employee table are one to many relationships, so set should be defined in department entity class to save employee information.
    * And use @ onetoman to specify the mapping relationship (one to many).
    * You can use @ JoinColumn to create a foreign key. At this time, you can maintain the foreign key (one party). If you assign a value to this foreign key, there will be an update compared with the other party.
    * If you discard foreign key maintenance, you can use mapperBy to specify an association relationship whose value is the property name maintained by the corresponding class.
    * Use cascade to define cascading properties.
    */
// @OneToMany(targetEntity = Employee.class)
// @JoinColumn(name = "id", referencedColumnName = "deptId")
   @OneToMany(mappedBy = "department", cascade = CascadeType.ALL)
   private Set<Employee> employees = new HashSet<Employee>();

   public int getDeptId() {
      return deptId;
   }

   public void setDeptId(int deptId) {
      this.deptId = deptId;
   }

   public String getDeptName() {
      return deptName;
   }

   public void setDeptName(String deptName) {
      this.deptName = deptName;
   }

   public Set<Employee> getEmployees() {
      return employees;
   }

   public void setEmployees(Set<Employee> employees) {
      this.employees = employees;
   }

   @Override
   public String toString() {
      return "Department{" +
         "deptId=" + deptId +
         ", deptName='" + deptName +
         '}';
   }
}


[com/lyh/demo/jpa/JpaApplicationTests.java]
package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Department;
import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.dao.DepartmentDao;
import com.lyh.demo.jpa.dao.EmployeeDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;

@SpringBootTest
class JpaApplicationTests {

   @Autowired
   private EmployeeDao employeeDao;
   @Autowired
   private DepartmentDao departmentDao;

   /**
    * Test cascade add data
    */
   @Test
   void testSave() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);

      Department department = new Department();
      department.setDeptId(1);
      department.setDeptName("Development");

      // Maintain foreign key, i.e. add value
      employee.setDepartment(department);
      department.getEmployees().add(employee);
      departmentDao.save(department);
   }


   /**
    * Test object query (gets the object of more than one party and its associated object. Its default loading method is load now.)
    */
   @Test
   @Transactional
   void testObjectQueryOneFromMany() {
      Employee employee = employeeDao.getOne(1);
      System.out.println(employee.getDepartment());
   }


   /**
    * Test object query (gets the object of one party and its associated object. The default loading method is delayed loading.)
    */
   @Test
   @Transactional
   void testObjectQueryManyFromOne() {
      Department department = departmentDao.getOne(1);
      System.out.println(department.getEmployees());
   }
}

Test testObjectQueryOneToMany().

Test testObjectQueryManyFromOne().

3. Many to many (@ ManyToMany)

(1) Basic concepts:

Two tables are one to many relationships.

The intermediate table is used to maintain the relationship between the two tables. The intermediate table is composed of at least two fields, which, as foreign keys, point to the primary keys of the two tables, forming a joint primary key.

(2) sql table creation

Similar to a one to many relationship.

[Example:
    //Employee table and role table.
    //An employee can correspond to multiple roles, and a role can correspond to multiple employees. There is a many to many relationship between employees and roles.
    //Intermediate tables need to be created.
    
drop table emp_and_role;
drop table emp;
drop table role;
CREATE TABLE role (
    roleId int primary key auto_increment,
    roleName varchar(32) 
);

CREATE TABLE emp (
    id int primary key auto_increment,
    name varchar(32),
    age int
);

CREATE TABLE emp_and_role (
    emp_id int,
    role_id int,
    primary key(emp_id, role_id),
    foreign key(emp_id) references emp(id),
    foreign key(role_id) references role(roleId)
);

(3) jpa table building

[step:]
Step 1: clarify the relationship between the two tables
 Step 2: determine the relationship between tables, one to many (foreign key) or many to many (intermediate table).
Step 3: write entity class and establish table relation (declare corresponding attribute) in entity class.
Step 4: configure the mapping relationship

Step1,Step2:

The employee table and role table are many to many relationships, so intermediate tables need to be established.

[com/lyh/demo/jpa/bean/Employee.java]
package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "emp")
@Data
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 32)
    private String name;

    @Column(name = "age")
    private Integer age;
}


[com/lyh/demo/jpa/bean/Role.java]

package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;

@Entity
@Table(name = "role")
@Data
public class Role {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Integer roleId;

   @Column(length = 32)
   private String roleName;
}


[com/lyh/demo/jpa/dao/EmployeeDao.java]

package com.lyh.demo.jpa.dao;

import com.lyh.demo.jpa.bean.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Component;

@Component
public interface EmployeeDao extends JpaRepository<Employee, Integer>, JpaSpecificationExecutor<Employee> {
}


[com/lyh/demo/jpa/dao/RoleDao.java]

package com.lyh.demo.jpa.dao;

import com.lyh.demo.jpa.bean.Role;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Component;

@Component
public interface RoleDao extends JpaRepository<Role, Integer>, JpaSpecificationExecutor<Role> {
}

Step3,Step4:

Configure the mapping relationship.

[com/lyh/demo/jpa/bean/Employee.java]

package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "emp")
@Data
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 32)
    private String name;

    @Column(name = "age")
    private Integer age;

    /**
     * Configure many to many relationships.
     * @JoinTable Configure intermediate tables for.
     * Among them:
     *  name: Middle table name.
     *  joinColumns: Define a foreign key and associate it with the primary key of the current class.
     *  inverseJoinColumns: Define a foreign key and associate it with the primary key of another class.
     */
    @ManyToMany(targetEntity = Role.class)
    @JoinTable(name = "emp_and_role",
    joinColumns = {@JoinColumn(name = "emp_id", referencedColumnName = "id")},
    inverseJoinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "roleId")})
    private Set<Role> roleSet = new HashSet<>();
}


[com/lyh/demo/jpa/bean/Role.java]

package com.lyh.demo.jpa.bean;

import lombok.Data;

import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "role")
public class Role {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Integer roleId;

   @Column(length = 32)
   private String roleName;

   /**
    * Waiver of foreign key maintenance rights.
    * And define cascading properties.
    */
   @ManyToMany(mappedBy = "roleSet", cascade = CascadeType.ALL)
   private Set<Employee> employeeSet = new HashSet<>();

   public Integer getRoleId() {
      return roleId;
   }

   public void setRoleId(Integer roleId) {
      this.roleId = roleId;
   }

   public String getRoleName() {
      return roleName;
   }

   public void setRoleName(String roleName) {
      this.roleName = roleName;
   }

   public Set<Employee> getEmployeeSet() {
      return employeeSet;
   }

   public void setEmployeeSet(Set<Employee> employeeSet) {
      this.employeeSet = employeeSet;
   }

   @Override
   public String toString() {
      return "Role{" +
         "roleId=" + roleId +
         ", roleName='" + roleName + '\'' +
         ", employeeSet=" + employeeSet +
         '}';
   }
}

(4) Test (using cascade assignments)

[com/lyh/demo/jpa/JpaApplicationTests.java]

package com.lyh.demo.jpa;

import com.lyh.demo.jpa.bean.Employee;
import com.lyh.demo.jpa.bean.Role;
import com.lyh.demo.jpa.dao.EmployeeDao;
import com.lyh.demo.jpa.dao.RoleDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class JpaApplicationTests {

   @Autowired
   private EmployeeDao employeeDao;

   @Autowired
   private RoleDao roleDao;

   @Test
   void testSave1() {
      Employee employee = new Employee();
      employee.setName("tom");
      employee.setAge(22);

      Role role = new Role();
      role.setRoleName("manager");

      // Maintaining foreign keys
      employee.getRoleSet().add(role);
      role.getEmployeeSet().add(employee);
      // Use cascade assignment
      roleDao.save(role);
   }
}

Topics: Java Spring SQL Lombok

Programmer Think

Spring Data JPA learning

1, Spring Data JPA

1, introduction

2. Used in the spinning boot project

2, Basic notes

1,@Entity

2,@Table

3,@Id,@GeneratedValue

4,@Column

5,@Temporal

6. cascade

7,mappedby

8,@OneToOne

3, Implementation of CRUD by JPA (taking a single entity class as an example)

1. Build environment (take Spring Boot 2.0 as an example)

2. Write entity classes and mapping relationships

3. Write Dao layer

4. Write test class

5. Pit encountered

4, JPA writes sql statement -- jpql

1, introduction

2, use

3. Pit encountered

5, JPA writes sql statement -- sql, method rule name query

1. sql syntax rules

2. Method naming rule query

6, Dynamic query (jpaspecification executor, Specification)

1,JpaSpecificationExecutor

2,Specification

3. Basic use

7, Multi table operation

1. One to one (@ OneToOne)

2. One to many (@ OneToMany, @ ManyToOne)

3. Many to many (@ ManyToMany)

Hot Topics