Specifications, multi table query basis

One 、Specifications A dynamic query

When we query an entity , Given conditions are not fixed , At this point, you need to dynamically build the corresponding query statements , stay Spring Data JPA Through JpaSpecificationExecutor Interface query . comparison JPQL, Its advantage is type safety , More object-oriented .

JpaSpecificationExecutor Method list

	T findOne(Specification<T> spec);  // Query single object 

	List<T> findAll(Specification<T> spec);  // Query list 

	// Query all , Pagination 
	//pageable： Paging parameters 
	// Return value ： Pagination pageBean（page： yes springdatajpa Provided ）
	Page<T> findAll(Specification<T> spec, Pageable pageable);

	// Query list 
	//Sort： Sort parameters 
	List<T> findAll(Specification<T> spec, Sort sort);

	long count(Specification<T> spec);// Statistics query 
	

about JpaSpecificationExecutor, This interface basically revolves around Specification Interface to define . We can simply understand it as ,Specification What we construct is a query condition .

Specification ： Query criteria

Customize our own Specification Implementation class

 Realization 
		//root： The root object of the query （ Any property of the query can be obtained from the root object ）
		//CriteriaQuery： Top level query object , Custom query method （ understand ： Generally do not use ）
		//CriteriaBuilder： Query builder , Encapsulates many query conditions 
		Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb); // Encapsulate query criteria 

Dynamically query a single object

Case realization

 @Test
    /** *  User defined query criteria  * 1、 Realization Specification Interface （ Provide generics , The object type of the query ） * 2、 Realization toPredicate Method （ Construct query conditions ） * 3、 You need to use two parameters in the method parameters （ * root： Get the object properties to query  * CriteriaBuilder： Construct query conditions , Many query conditions are encapsulated inside （ Fuzzy matching , Exactly match ） * ） *  Case study ： According to the name of the customer , Query customers named Internet customers  *  Query criteria  * 1、 A query  criteriaBuilder object  * 2、 The name of the attribute being compared  root object  * */
    public void testSpecifications(){
    
        Specification<Customer>  spec=new Specification<Customer>() {
    
            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    
                //1. Get the properties of the comparison 
                Path<Object> custName = root.get("custName");
                //2, Structural conditions ：select * from cst_customer where cust_name="xxx"
                /** *  The first parameter ： Attributes to compare （path object ） *  The second parameter ： The value to be compared at present  */
                Predicate predicate = criteriaBuilder.equal(custName, " Internet ");// Make accurate matching （ Properties of comparison , The value of the attribute being compared ）
                return predicate;
            }
        };
        Customer customer = customerDao.findOne(spec);
        System.out.println(customer);
    }

Dynamic query completes multi condition splicing

Case study

/** *  Dynamic multi condition splicing query  *  Case study ： Query the customer named gold miner , The industry is the customer of Education  */
    @Test
    public void testConditions(){
    
       Specification<Customer> spec=new Specification<Customer>() {
    
           public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    
               // Get the properties of the comparison 
               Path<Object> custName = root.get("custName");
               Path<Object> custIndustry = root.get("custIndustry");
               // Structural conditions 
               Predicate p1 = criteriaBuilder.equal(custName, " Golden Miner ");
               Predicate p2 = criteriaBuilder.equal(custIndustry, " education ");

               // Conditional mosaicing 
               Predicate predicate = criteriaBuilder.and(p1, p2);
               return predicate;
           }
       };
        Customer customer = customerDao.findOne(spec);
        System.out.println(customer);
    }

Fuzzy matching query list

Case study ：

/** *  Fuzzy query matching  *  Case study ： The name of the query customer is Huang xx The customer  */
    @Test
    public void testLike(){
    
        Specification<Customer> spec=new Specification<Customer>() {
    
            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    
                // Get the properties of the comparison 
                Path<Object> custName = root.get("custName");

                // Structural conditions 
                Predicate predicate = criteriaBuilder.like(custName.as(String.class), " yellow %");
                
                return predicate;
            }
        };
        List<Customer> list = customerDao.findAll(spec);
    for (Customer customer : list) {
    
      System.out.println(customer);
    }
    }

Because the customer name is a string , therefore path.as(String.class)

️ Be careful ：

1、equals： Directly obtained path Object properties , Then compare .

2、gt、lt、ge、le、like： obtain path object , according to path Specify the parameter type to compare , Then compare

​ Specify parameter type ,path.as( Bytecode object of type )

Sort

Create sort object , You need to call the constructor instantiation sort object . The first parameter specifies the sort order （ Forward and reverse order ）、 The second parameter sorts the attribute name

Example ：

/** *  Fuzzy query matching  *  Case study ： The name of the query customer is Huang xx The customer  */
    @Test
    public void testLike(){
    
        Specification<Customer> spec=new Specification<Customer>() {
    
            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    
                // Get the properties of the comparison 
                Path<Object> custName = root.get("custName");

                // Structural conditions 
                Predicate predicate = criteriaBuilder.like(custName.as(String.class), " yellow %");

                return predicate;
            }
        };
        Sort sort=new Sort(Sort.Direction.DESC,"custId");

        List<Customer> list = customerDao.findAll(spec,sort);
    for (Customer customer : list) {
    
      System.out.println(customer);
    }
    }

Paging query

Case study

There are two messages on each page , Find the first 0 page

// Paging query 
        Pageable pageable=new PageRequest(0,2);
        Page<Customer> customers = customerDao.findAll(spec, pageable);
        System.out.println(customers.getContent());// result set 
        System.out.println(customers.getTotalElements());// Total number of articles 
        System.out.println(customers.getTotalPages());// Total number of pages 
        for (Customer customer : customers) {
    
            System.out.println(customer);
        }

summary

1、 Get the compared attribute according to the attribute name

2、 Construct query conditions

Two 、 The relationship between multiple tables and the operation steps of operating multiple tables

i. One to many operation 
		 Case study ： The case of customers and contacts （ One-to-many relation ）
			 Customer ： A company 
			 Contacts ： The employees of this company 
		 A customer can have multiple contacts 
		 A contact belongs to a company 
		
	 Analysis steps 
		1. Clarify the table relationship 
			 One-to-many relation 
		2. Determine table relationships （ describe   Foreign keys | In the middle of table ）
			 Main table ： Customer list 
			 From the table ： Contact list 
				*  Then add foreign keys from the table 
		3. Write entity class , Then describe the table relationship in the entity class （ Inclusion relation ）
			 Customer ： The entity class of the customer contains a collection of contacts 
			 Contacts ： Include a customer object in the entity class of the contact 
		4. Configure mapping relationships 
			*  Use jpa Annotation configures a one to many mapping relationship 

Contact entity class

package cn.itcast.domain;

import lombok.Data;

import javax.persistence.*;

@Entity
@Table(name = "cst_linkman")
@Data
public class LinkMan {
    

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "lkm_id")
    private Long lkmId; // Contact number ( Primary key )
    @Column(name = "lkm_name")
    private String lkmName;// Contact name 
    @Column(name = "lkm_gender")
    private String lkmGender;// Contact gender 
    @Column(name = "lkm_phone")
    private String lkmPhone;// Office phone number of contact person 
    @Column(name = "lkm_mobile")
    private String lkmMobile;// Contact phone 
    @Column(name = "lkm_email")
    private String lkmEmail;// Contact email 
    @Column(name = "lkm_position")
    private String lkmPosition;// Position of contact person 
    @Column(name = "lkm_memo")
    private String lkmMemo;// Contact notes 

    /** *  Configure the many to one relationship between contact and customer  *  Configure many to one relationships in the form of annotations  * 1. Configuration table relationship  * @ManyToOne :  Configure many to one relationships  * targetEntity： The other party's entity class bytecode  * 2. Configure foreign key （ In the middle of table ） * name: The name of the foreign key  * referencedColumnName： The name of the referenced primary key  * * *  The process of configuring foreign keys , Configured to more than one party , Will maintain foreign keys on the more side  * */
    @ManyToOne(targetEntity = Customer.class,fetch = FetchType.LAZY)
    @JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
    private Customer customer;


}

Customer entity class

package cn.itcast.domain;

import lombok.Data;

import javax.persistence.*;

/** *  Mapping relationship between entity class and table  * @Entity * @Table */
@Entity
@Table(name = "cst_customer")
@Data// Provide a class of get,set,equals,toString,hashCode Method 
public class Customer {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cust_id")
    private Long custId;

    @Column(name ="cust_address")
    private String custAddress;

    @Column(name ="cust_industry")
    private String custIndustry;

    @Column(name ="cust_level")
    private String custLevel;

    @Column(name ="cust_name")
    private String custName;

    @Column(name ="cust_phone")
    private String custPhone;

    @Column(name ="cust_source")
    private String custSource;

    // Configure foreign key 
    @OneToMany(targetEntity = Customer.class )
    @JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
    private LinkMan linkMan;
}

Configuration of configuration file

<!-- Inject jpa Configuration information   load jpa Basic configuration information and jpa How to implement （hibernate） Configuration information  hibernate.hbm2ddl.auto： Automatically create database tables  create： The database table is recreated every time  update： There are tables that will not be recreated , No tables will be recreated  -->
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.hbm2ddl.auto">create</prop>
            </props>
        </property>

stay springDataJPA in , Adding a transaction will rollback by default . So after testing and adding transaction annotations , Also add @Rollback annotation , Set up value=false

3、 ... and 、 Complete multi table query

One to many operation

One to many relationship

Settings between entities

package cn.itcast.domain;

import lombok.Data;

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

/** *  Mapping relationship between entity class and table  * @Entity * @Table */
@Entity
@Table(name = "cst_customer")
@Data// Provide a class of get,set,equals,toString,hashCode Method 
public class Customer {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cust_id")
    private Long custId;

    @Column(name ="cust_address")
    private String custAddress;

    @Column(name ="cust_industry")
    private String custIndustry;

    @Column(name ="cust_level")
    private String custLevel;

    @Column(name ="cust_name")
    private String custName;

    @Column(name ="cust_phone")
    private String custPhone;

    @Column(name ="cust_source")
    private String custSource;

    // Configure the relationship between customers and contacts （ One-to-many relation ）
    /** *  Configure multi table relationships in the form of annotations  * 1. Declare the relationship  * @OneToMany :  Configure one to many relationships  * targetEntity ： Bytecode of opposite object  * 2. Configure foreign key （ In the middle of table ） * @JoinColumn :  Configure foreign key  * name： Foreign key field name  * referencedColumnName： The primary key field name of the referenced primary table  * * *  On the customer entity class （ One side ） Added foreign key configuration , So for customers , It also has the function of maintaining foreign keys  * */

// @OneToMany(targetEntity = LinkMan.class)
// @JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
    /** *  Waiving foreign key maintenance rights  * mappedBy： The attribute name of the opposite configuration relationship \ * cascade :  Configure cascading （ It can be configured on the annotation for setting the mapping relationship of multiple tables ） * CascadeType.all :  all  * MERGE ： to update  * PERSIST ： preservation  * REMOVE ： Delete  * * fetch :  Configure the loading method of associated objects  * EAGER ： Immediately load  * LAZY ： Delay loading  */
    @OneToMany(mappedBy = "customer",cascade = CascadeType.ALL)
    private Set<LinkMan> linkMans = new HashSet<LinkMan>();

}

package cn.itcast.domain;

import lombok.Data;

import javax.persistence.*;

@Entity
@Table(name = "cst_linkman")
@Data
public class LinkMan {
    

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "lkm_id")
    private Long lkmId; // Contact number ( Primary key )
    @Column(name = "lkm_name")
    private String lkmName;// Contact name 
    @Column(name = "lkm_gender")
    private String lkmGender;// Contact gender 
    @Column(name = "lkm_phone")
    private String lkmPhone;// Office phone number of contact person 
    @Column(name = "lkm_mobile")
    private String lkmMobile;// Contact phone 
    @Column(name = "lkm_email")
    private String lkmEmail;// Contact email 
    @Column(name = "lkm_position")
    private String lkmPosition;// Position of contact person 
    @Column(name = "lkm_memo")
    private String lkmMemo;// Contact notes 

    /** *  Configure the many to one relationship between contact and customer  *  Configure many to one relationships in the form of annotations  * 1. Configuration table relationship  * @ManyToOne :  Configure many to one relationships  * targetEntity： The other party's entity class bytecode  * 2. Configure foreign key （ In the middle of table ） * name: The name of the foreign key  * referencedColumnName： The name of the referenced primary key  * * *  The process of configuring foreign keys , Configured to more than one party , Will maintain foreign keys on the more side  * */
    @ManyToOne(targetEntity = Customer.class,fetch = FetchType.LAZY)
    @JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
    private Customer customer;


}

Delete data from table ： It can be deleted at any time .

Delete main table data ：

• There is data from the table

1、 By default , It will set the foreign key field to null, Then delete the main table data . If in the table structure of the database , Foreign key field has non NULL constraint , An error will be reported by default .

2、 If it is configured to give up the right to maintain Association , You can't delete it （ And whether the foreign key field is allowed to be null, It doesn't matter. ） Because when deleting , It won't update the foreign key field of the slave table at all .

3、 If you still want to delete , Use cascading to delete references

• No reference from table data ： Delete casually

cascade ：

​ Operate an object while operating its associated object

		 Cascade operation ：
			1. It is necessary to distinguish the operation subject 
			2. It needs to be on the entity class of the operation body , Add cascading properties （ It needs to be added to the annotation of multi table mapping relationship ）
			3.cascade（ Configure cascading ）
		
		 Cascade add ,
			 Case study ： When I save a customer, I also save contacts 
		 cascading deletion 
			 Case study ： When I delete a customer, I delete all contacts of this customer 

Be sure to distinguish the operation subject , Just operate on this topic , From then on, we will make corresponding changes

cascade :  Configure cascading （ It can be configured on the annotation for setting the mapping relationship of multiple tables ）
      CascadeType.all         :  all 
                  MERGE       ： to update 
                  PERSIST     ： preservation 
                  REMOVE      ： Delete 

     fetch :  Configure the loading method of associated objects 
          EAGER   ： Immediately load 
          LAZY    ： Delay loading 

Case study

// Cascade add 
    @Test
    @Transactional
    @Rollback(false)
    public void testAdd(){
    
        Customer customer=new Customer();
        customer.setCustName(" aston 2");

        LinkMan linkMan=new LinkMan();
        linkMan.setLkmName(" Zhao Min 1");

        linkMan.setCustomer(customer);
        customer.setLinkMans(Arrays.asList(linkMan));

        customerDao.save(customer);

    }

// cascading deletion 
    @Test
    @Transactional
    @Rollback(false)
    public void testDelete(){
    
        customerDao.delete(14l);

    }

Many to many operation

Case study ： Users and roles （ Many to many relationship ）
user ：
role ：

	 Analysis steps 
		1. Clarify the table relationship 
			 Many to many relationship 
		2. Determine table relationships （ describe   Foreign keys | In the middle of table ）
			 Intermediate table 
		3. Write entity class , Then describe the table relationship in the entity class （ Inclusion relation ）
			 user ： A collection containing characters 
			 role ： A collection containing users 
		4. Configure mapping relationships 

Set up three tables

Create entity class

User

package cn.itcast.domain;

import lombok.Data;

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

@Entity
@Table(name = "sys_user")
@Data
public class User {
    

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="user_id")
    private Long userId;
    @Column(name="user_name")
    private String userName;
    @Column(name="age")
    private Integer age;

    /** *  Configure user to role many to many relationships  *  Configure many to many mapping  * 1. Declare the configuration of table relationships  * @ManyToMany(targetEntity = Role.class) // Many to many  * targetEntity： Entity class bytecode representing the other party  * 2. Configure the middle table （ Contains two foreign keys ） * @JoinTable * name :  The name of the middle table  * joinColumns： Configure the foreign key of the current object in the intermediate table  * @JoinColumn Array of  * name： Foreign key name  * referencedColumnName： The primary key name of the referenced primary table  * inverseJoinColumns： Configure the foreign key of the opposite object in the intermediate table  */
    @ManyToMany(targetEntity = Role.class,cascade = CascadeType.ALL)
    @JoinTable(name = "sys_user_role",
            //joinColumns, The foreign key of the current object in the intermediate table 
            joinColumns = {
    @JoinColumn(name = "sys_user_id",referencedColumnName = "user_id")},
            //inverseJoinColumns, The foreign key of the opposite object in the middle table 
            inverseJoinColumns = {
    @JoinColumn(name = "sys_role_id",referencedColumnName = "role_id")}
    )
    private Set<Role> roles = new HashSet<Role>();


}

Role

package cn.itcast.domain;

import lombok.Data;

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

@Entity
@Table(name = "sys_role")
@Data
public class Role {
    

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "role_id")
    private Long roleId;
    @Column(name = "role_name")
    private String roleName;

    // Configure many to many 
    @ManyToMany(mappedBy = "roles")  // Configure multi table relationships 
    private Set<User> users = new HashSet<User>();


}

Create the corresponding DAO

RoleDao and UserDao

public interface RoleDao extends JpaRepository<Role,Long >,JpaSpecificationExecutor<Role> {
    
}

Will configure the update Change it to create, Create... Every time , Test whether there is data in the intermediate table

Case study

@Test
    public void testAdd(){
    
        User user=new User();
        user.setUserName(" petty thief ");

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

        // Configure user to role relationships 
        user.getRoles().add(role);

        userDao.save(user);
    }


Hibernate: insert into sys_user (age, user_name) values (?, ?)
Hibernate: insert into sys_role (role_name) values (?)
Hibernate: insert into sys_user_role (sys_user_id, sys_role_id) values (?, ?)

Cascade add

 @Test
    public void testAdd(){
    
        User user=new User();
        user.setUserName(" petty thief ");

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

        // Configure user to role relationships 
        user.getRoles().add(role);

        userDao.save(user);
    }

When testing cascade deletion , Configuration required create Change it to update

// cascading deletion 
    @Test
    @Transactional
    @Rollback(value = false)
    public void testCasCadeDelete(){
    
        User user=userDao.findOne(1l);
    //System.out.println(user);
        userDao.delete(user);
    }

The meaning of each annotation

Multi table query

1. Object navigation query
While querying an object , Query its associated objects through this object

		 Case study ： Customers and contacts 
		
		 Query multiple parties from one party 
			*  Default ： Using delayed loading （****）
			
		 Query one party from multiple parties 
			*  Default ： Use immediate load 

Use one to many cases

Build a data

Check with a customer , Get all contacts under the customer

	@Autowired
	private CustomerDao customerDao;
	
	@Test
	// Because it is in java Test in code , In order to solve no session problem , Configure operations to the same transaction 
	@Transactional 
	public void testFind() {
    
		Customer customer = customerDao.findOne(5l);
		Set<LinkMan> linkMans = customer.getLinkMans();// Object navigation query 
		for(LinkMan linkMan : linkMans) {
    
  			System.out.println(linkMan);
		}
	}

Check a contact , Get all customers for this contact

	@Autowired
	private LinkManDao linkManDao;
	
	
	@Test
	public void testFind() {
    
		LinkMan linkMan = linkManDao.findOne(4l);
		Customer customer = linkMan.getCustomer(); // Object navigation query 
		System.out.println(customer);
	}

problem 1： When we look up customers , Do you want to find out the contact person ？

analysis ： If we don't check , When you use it, you have to write your own code , Call method to query . If we find out , When it is not used, it will waste the server memory in vain .

solve ： Using the idea of delayed loading . Set it by configuration when we need to use it , Launch a real query .

Configuration mode ：

	/** *  In the customer object of @OneToMany Add in comments fetch attribute  * FetchType.EAGER ： Immediately load  * FetchType.LAZY ： Delay loading  */
	@OneToMany(mappedBy="customer",fetch=FetchType.EAGER)
	private Set<LinkMan> linkMans = new HashSet<>(0);

problem 2： When we look up contacts , Do you want to find out the customers ？

analysis ： for example ： When checking contact details , I'm sure I'll take a look at the customer of this contact . If we don't check , When you use it, you have to write your own code , Call method to query . If we find out , An object does not consume too much memory . And most of the time we use .

solve ： Use the idea of immediate loading . Set by configuration , Just query from table entities , Just look up the main table entity object at the same time

Configuration mode

	/** *  In the contact person's @ManyToOne Add in comments fetch attribute  * FetchType.EAGER ： Immediately load  * FetchType.LAZY ： Delay loading  */
	@ManyToOne(targetEntity=Customer.class,fetch=FetchType.EAGER)
	@JoinColumn(name="cst_lkm_id",referencedColumnName="cust_id")
	private Customer customer;

@OneToMany(mappedBy = "customer",cascade = CascadeType.ALL)
/**  Waiving foreign key rights , mappedBy： The attribute name of the opposite configuration relationship  cascade： Configure cascading （ It can be configured to set the annotation of the mapping relationship of multiple tables ） CascadeType.ALL ： all  PERSIST, preservation  MERGE, to update  REMOVE, Delete  fetch： Configure the loading method of associated objects  EAGER： Immediately load  LAZY： Delay loading  */

Immediately load ： When we inquire about customers , All the contacts below him have been found , Whether we use it or not , Therefore, immediate loading is not recommended .

Query one party from multiple parties , The default is to load immediately , To change to delayed loading , It depends on the operation body , In the entity class of the contact on the operation body, it is used for the customer Make the relevant configuration .

That is, query the customer from the contact

/** *  Configure the many to one relationship between contact and customer  *  Configure many to one relationships in the form of annotations  * 1. Configuration table relationship  * @ManyToOne :  Configure many to one relationships  * targetEntity： The other party's entity class bytecode  * 2. Configure foreign key （ In the middle of table ） * name: The name of the foreign key  * referencedColumnName： The name of the primary key  * * *  The process of configuring foreign keys , Configured to more than one party , Will maintain foreign keys on the more side  * */
@ManyToOne(targetEntity = Customer.class,fetch = FetchType.LAZY)
@JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
private Customer customer;

// Test object navigation query （ Find customers according to contacts ）
    @Test
    @Transactional// solve could not initialize proxy - no Session
    public void testQuery2(){
    
        LinkMan linkMan = linkManDao.findOne(1l);
        Customer customer = linkMan.getCustomer();
        System.out.println(customer);
    }

When to use and when to send sql sentence . At the beginning, I just checked the users , It hasn't been used yet , When printing users , Will send select Of sql sentence , This is delayed loading

summary

1. Object navigation query
While querying an object , Query its associated objects through this object

​ Case study ： Customers and contacts

Query multiple parties from one party

• Default ： Using delayed loading

Query one party from multiple parties

• Default ： Use immediate load