Environment creation and simple query, see the previous article

https://blog.csdn.net/Insist___/article/details/104206437

1Specifications dynamic query

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: pageBean (page: provided by springdatajpa)
	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);//Statistical query
	
* Specification : query criteria
	//Customize our own Specification implementation class
		//Realization
			//Root: the root object of the query (any attribute of the query can be obtained from the root object)
			//CriteriaQuery: top level query object, user-defined query method (Understanding: generally not used)
			//Criteria Builder: the builder of query, encapsulating many query conditions
			Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb); //Encapsulate query criteria

1.1 query single object according to conditions

 /**
     * Query a single object based on criteria
     *
     */
    @Test
    public void testSpec() {
        //Anonymous Inner Class 
        /**
         * Custom query criteria
         *      1.Implement the Specification interface (provide generics: object type of query)
         *      2.Implement the method of topredicte (construct query conditions)
         *      3.Two parameters of method parameters are needed(
         *          root: Get the object properties to query
         *          CriteriaBuilder: For constructing query conditions, many query conditions (fuzzy matching, precise matching) are encapsulated internally
         *       )
         *  Case: according to the customer name query, query the customer with the name of wisdom Podcast
         *          query criteria
         *              1.Query mode
         *                  cb object
         *              2.Property name for comparison
         *                  root object
         *
         */
        Specification<Customer> spec = new Specification<Customer>() {

            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                //1. Get the properties of the comparison
                Path<Object> custName = root.get("custName");
                //2. Construct query criteria: select * from CST ﹣ customer where custom ﹣ name = 'meow sauce'
                /**
                 * First parameter: attribute to compare (path object)
                 * Second parameter: current value to be compared
                 */
                Predicate predicate = cb.equal(custName, "Meow meow sauce");//Carry out accurate matching (Comparative attribute, value of comparative attribute)
                return predicate;
            }
        };
        Customer customer = customerDao.findOne(spec);
        System.out.println(customer);
    }

1.2 multi criteria query

/**
     * Multi criteria query
     *      Case: query according to customer name (meow sauce) and customer industry (it Education)
     *
     */
    @Test
    public void testSpec1() {
        /**
         *  root:get attribute
         *      Customer name
         *      Industry
         *  cb: Structure query
         *      1.Construct accurate matching query of customer name
         *      2.Construct accurate matching query of the industry
         *      3.Connect the above two queries
         */
        Specification<Customer> spec = new Specification<Customer>() {

            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path<Object> custName = root.get("custName");//Customer name
                Path<Object> custIndustry = root.get("custIndustry");//Industry

                //Structure query
                //1. Construct accurate matching query of customer name
                Predicate p1 = cb.equal(custName, "Meow meow sauce");//First parameter, path (attribute), second parameter, attribute value
                //2. Construct accurate matching query of the industry
                Predicate p2 = cb.equal(custIndustry, "it education");
                //3. Combine multiple query conditions together: combine (meet condition 1 and condition 2: and relationship, and meet condition 1 or condition 2: or relationship)
                Predicate and = cb.and(p1, p2);//Splicing multiple query conditions in the form of and
                // cb.or(); / / splices multiple query conditions in the form of or
                return and;
            }
        };
        Customer customer = customerDao.findOne(spec);
        System.out.println(customer);
    }

1.3 fuzzy matching, sorting

/**
     * Case: complete fuzzy matching according to customer name and return to customer list
     *      Customer name begins with 'meow sauce'
     *
     * equal : Go directly to the path object (property) and compare it
     * gt，lt,ge,le,like : Get the path object, specify the parameter type of comparison according to path, and then compare
     *      Specifies the parameter type: path.as (bytecode object of type)
     */
    @Test
    public void testSpec3() {
        //Construct query criteria
        Specification<Customer> spec = new Specification<Customer>() {

            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                //Query attribute: customer name
                Path<Object> custName = root.get("custName");
                //Query method: fuzzy matching
                Predicate like = cb.like(custName.as(String.class), "Meow meow sauce%");
                return like;
            }
        };
//        List<Customer> list = customerDao.findAll(spec);
//        for (Customer customer : list) {
//            System.out.println(customer);
//        }
        //Add sort
        //To create a sort object, you need to call the constructor to instantiate the sort object
        //First parameter: sort order (reverse order, positive order)
        //   Sort.Direction.DESC: reverse
        //   Sort.Direction.ASC: ascending
        //Second parameter: attribute name of sorting
        Sort sort = new Sort(Sort.Direction.DESC,"custId");
        List<Customer> list = customerDao.findAll(spec, sort);
        for (Customer customer : list) {
            System.out.println(customer);
        }
    }

1.4 paging query

/**
     * Paging query
     *      Specification: query criteria
     *      Pageable: Paging parameters
     *          Paging parameter: page number of queries, number of queries per page
     *          findAll(Specification,Pageable): Conditional paging
     *          findAll(Pageable): Unconditional paging
     *  Return: Page (the pageBean object encapsulated by springDataJpa, data list, total number)
     */
    @Test
    public void testSpec4() {

        Specification spec = null;
        //PageRequest object is the implementation class of Pageable interface
        /**
         * In the process of creating PageRequest, you need to call its constructor to pass in two parameters
         *      First parameter: number of pages of the current query (starting from 0)
         *      Second parameter: number of queries per page
         */
        Pageable pageable = new PageRequest(0,2);
        //Paging query
        Page<Customer> page = customerDao.findAll(null, pageable);
        System.out.println(page.getContent()); //Get data set list
        System.out.println(page.getTotalElements());//Get the total number
        System.out.println(page.getTotalPages());//Get the total number of pages
    }

Upload code:
https://download.csdn.net/download/Insist___/12142040

2. Relationship between multiple tables and operation steps of multiple tables

Table relation
One-on-one
One to many:
One party: main table
Multiple party: from table
Foreign key: you need to create a new column from the table as a foreign key. Its value comes from the primary key of the main table
Many to many:
Intermediate table: the intermediate table should be composed of at least two fields. These two fields, as foreign keys, point to the primary keys of two tables and form the joint primary keys

Lecturer to student: one to many relationship
		
Relationships in entity classes
	Include relation: table relation can be described by include relation in entity class
	Inheritance relationship

Analysis steps
	1. Clear table relationship
	2. Determine table relationship (describe foreign key | middle table)
	3. Write entity class, and then describe table relation (including relation) in entity class
	4. Configure the mapping relationship

3. Complete multi table operation

3.1 one to many

i. One to many operation
	Case: case of customer and contact (one to many relationship)
		Client: a company
		Contact: employees of this company
	
		One customer can have multiple contacts
		A contact is subordinate to a company
		
	Analysis steps
		1. Clear table relationship
			One to many relationship
		2. Determine table relationship (describe foreign key | middle table)
			Main table: customer table
			From table: contact table
				*Add foreign key from the table
		3. Write entity class, and then describe table relation (including relation) in entity class
			Customer: a collection of contacts in the entity class of a second customer
			Contact: contains a customer's object in the contact's entity class
		4. Configure the mapping relationship
			*Using jpa annotation to configure one to many mapping relationship

	Cascade:
		Operate an object while operating its associated object
		
		Cascade operation:
			1. Need to distinguish operators
			2. Need to add cascading attribute on entity class of operation principal (need to add to annotation of multi table mapping relationship)
			3.cascade (configuration cascade)
		
		Cascade add,
			Case: when I save a customer, I also save the contact person
		cascading deletion
			Case: when I delete a customer, I also delete all contacts of this customer

Customer:
@OneToMany(mappedBy = "customer",cascade = CascadeType.ALL)
private Set linkMans = new HashSet();

LinkMan:
@ManyToOne(targetEntity = Customer.class,fetch = FetchType.LAZY)
@JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
private Customer customer;

3.1.1 customer object

package cn.itcast.domain;

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

/**
 * 1.Mapping relationship between entity class and table
 *      @Eitity
 *      @Table
 * 2.Mapping between properties in class and fields in table
 *      @Id
 *      @GeneratedValue
 *      @Column
 */
@Entity
@Table(name="cst_customer")
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 customer and contact (one to many relationship)
    /**
     * Using annotations to configure multi table relationships
     *      1.Declaring relationship
     *          @OneToMany : Configure one to many relationships
     *              targetEntity : Bytecode object of the opposite object
     *      2.Configure foreign key (middle table)
     *              @JoinColumn : Configure foreign keys
     *                  name: Foreign key field name
     *                  referencedColumnName: Primary key field name of the referenced primary table
     *
     *  * The configuration of foreign key is added to the customer entity class (one party), so for customers, it also has the function of maintaining foreign key
     *
     */

//    @OneToMany(targetEntity = LinkMan.class)
//    @JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
    /**
     * Waiver of foreign key maintenance rights
     *      mappedBy: Property name of the opposite configuration relationship\
     * cascade : Configuration cascading (can be configured to set the annotation of mapping relationship of multiple tables)
     *      CascadeType.all         : All
     *                  MERGE       : To update
     *                  PERSIST     : Preservation
     *                  REMOVE      : delete
     *
     * fetch : Configure how associated objects are loaded
     *          EAGER   : Immediate loading
     *          LAZY    : Delayed loading

      */
    @OneToMany(mappedBy = "customer",cascade = CascadeType.ALL)
    private Set<LinkMan> linkMans = new HashSet<LinkMan>();

   
}

1> Give up the maintenance right of external building, or an update statement will be added
// @OneToMany(targetEntity = LinkMan.class)
// @JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
That is:
@OneToMany(mappedBy = "customer")

2> To achieve cascading operation, cascade is needed
Namely:
@OneToMany(mappedBy = "customer",cascade = CascadeType.ALL)

3.1.2LinkMan object

package cn.itcast.domain;

import javax.persistence.*;

@Entity
@Table(name = "cst_linkman")
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;//Cell Phone 
    @Column(name = "lkm_email")
    private String lkmEmail;//Contact email
    @Column(name = "lkm_position")
    private String lkmPosition;//Contact Title 
    @Column(name = "lkm_memo")
    private String lkmMemo;//Contact notes

    /**
     * Configure many to one relationship from contact to customer
     *     Using annotation to configure many to one relationship
     *      1.Configure table relationships
     *          @ManyToOne : Configure many to one relationships
     *              targetEntity: Opposite entity class bytecode
     *      2.Configure foreign key (middle table)
     *
     * * In the process of configuring a foreign key, if you configure it to more than one party, you will maintain the foreign key in more than one party
     *
     */
    @ManyToOne(targetEntity = Customer.class,fetch = FetchType.LAZY)
    @JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
    private Customer customer;

   
}

3.1.3 test

 /**
     * Cascade add: save all contacts of a customer while saving a customer
     *      You need to configure the cascade property on the entity class of the operation principal
     */
    @Test
    @Transactional //Configuration transaction
    @Rollback(false) //Do not roll back automatically
    public void testCascadeAdd() {
        Customer customer = new Customer();
        customer.setCustName("Baidu 1");

        LinkMan linkMan = new LinkMan();
        linkMan.setLkmName("Xiao Li 1");

        linkMan.setCustomer(customer);
        customer.getLinkMans().add(linkMan);

        customerDao.save(customer);
    }


    /**
     * Cascade delete:
     *      Delete all contacts of customer 1 while deleting customer 1
     */
    @Test
    @Transactional //Configuration transaction
    @Rollback(false) //Do not roll back automatically
    public void testCascadeRemove() {
        //1. Query customer 1
        Customer customer = customerDao.findOne(1l);
        //2. Delete customer 1
        customerDao.delete(customer);
    }

Code uploaded:

3.2 more to many

ii. Many to many operation
Case: user and role (many to many relationship)
User:
Role:

	Analysis steps
		1. Clear table relationship
			Many to many relationship
		2. Determine table relationship (describe foreign key | middle table)
			Intermediate table
		3. Write entity class, and then describe table relation (including relation) in entity class
			Users: collections containing roles
			Roles: collections containing users
		4. Configure the mapping relationship

user:
@ManyToMany(targetEntity = Role.class,cascade = CascadeType.ALL)
@JoinTable(name = "sys_user_role",
//joinColumns, the foreign key of the current object in the middle 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 roles = new HashSet();

role:
@ManyToMany(mappedBy = "roles") / / configure multi table relationship
private Set users = new HashSet();

3.2.1 user table

package cn.itcast.domain;

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

@Entity
@Table(name = "sys_user")
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 intermediate table (including two foreign keys)
     *                @JoinTable
     *                  name : Middle table name
     *                  joinColumns: Configure the foreign key of the current object in the middle table
     *                      @JoinColumn Array
     *                          name: Foreign key name
     *                          referencedColumnName: Primary key name of the referenced primary table
     *                  inverseJoinColumns: Configure the foreign key of the opposite object in the middle table
     */
    @ManyToMany(targetEntity = Role.class,cascade = CascadeType.ALL)
    @JoinTable(name = "sys_user_role",
            //joinColumns, the foreign key of the current object in the middle 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>();

   
}

3.2.2 role table

package cn.itcast.domain;

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

@Entity
@Table(name = "sys_role")
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>();

   }

3.2.3 test

package cn.itcast.test;

import cn.itcast.dao.RoleDao;
import cn.itcast.dao.UserDao;
import cn.itcast.domain.Role;
import cn.itcast.domain.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class ManyToManyTest {

    @Autowired
    private UserDao userDao;
    @Autowired
    private RoleDao roleDao;

    /**
     * Save a user, save a role
     *
     *  Many to many waiver of right to maintain: the passive party gives up
     */
    @Test
    @Transactional
    @Rollback(false)
    public void  testAdd() {
        User user = new User();
        user.setUserName("petty thief");

        Role role = new Role();
        role.setRoleName("java Programmer");

        //To configure the user role relationship, you can maintain the data in the intermediate Table 1-1
        user.getRoles().add(role);

        //To configure the relationship between roles and users, you can maintain the data in the intermediate Table 1-1
        role.getUsers().add(user);

        userDao.save(user);
        roleDao.save(role);
    }


    //Test cascade add (save a user and the associated role of the user)
    @Test
    @Transactional
    @Rollback(false)
    public void  testCasCadeAdd() {
        User user = new User();
        user.setUserName("petty thief");

        Role role = new Role();
        role.setRoleName("java Programmer");

        //To configure the user role relationship, you can maintain the data in the intermediate Table 1-1
        user.getRoles().add(role);

        //To configure the relationship between roles and users, you can maintain the data in the intermediate Table 1-1
        role.getUsers().add(user);

        userDao.save(user);
    }

    /**
     * Case: delete the user with id 1 and its associated object at the same time
     */
    @Test
    @Transactional
    @Rollback(false)
    public void  testCasCadeRemove() {
        //Query user 1
        User user = userDao.findOne(1l);
        //Delete user 1
        userDao.delete(user);

    }
}

3.3 object navigation query

iii. multi table query
1. Object navigation query
When querying an object, query its associated objects through this object

		Case: customer and contact
		
		Query multiple parties from one party
			*Default: use delay load (* * *)
			
		Query one party from multiple parties
			*Default: use load now

3.3.1

package cn.itcast.test;

import cn.itcast.dao.CustomerDao;
import cn.itcast.dao.LinkManDao;
import cn.itcast.domain.Customer;
import cn.itcast.domain.LinkMan;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import java.util.Set;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class ObjectQueryTest {
    @Autowired
    private CustomerDao customerDao;

    @Autowired
    private LinkManDao linkManDao;

    //could not initialize proxy - no Session
    //Test object navigation query (when querying an object, query all associated objects through this object)
    @Test
    @Transactional // Solve the problem of no session in java code
    public void  testQuery1() {
        //Query customer with id 1
        Customer customer = customerDao.getOne(1l);
        //Object navigation query, all contacts under this customer
        Set<LinkMan> linkMans = customer.getLinkMans();

        for (LinkMan linkMan : linkMans) {
            System.out.println(linkMan);
        }
    }

    /**
     * Object navigation query:
     *      The default is to query in the form of delayed loading
     *          Calling the get method does not immediately send the query, but only sends out the harmony message when using the associated object
     *      Delay loading!
     * Modify configuration, change delay load to immediate load
     *      fetch，Need to be configured on annotation of multi table mapping relationship
     *
     */

    @Test
    @Transactional // Solve the problem of no session in java code
    public void  testQuery2() {
        //Query customer with id 1
        Customer customer = customerDao.findOne(1l);
        //Object navigation query, all contacts under this customer
        Set<LinkMan> linkMans = customer.getLinkMans();

        System.out.println(linkMans.size());
    }

    /**
     * Query his customers from the contact object navigation
     *      * Default: load now
     *  Delayed loading:
     *
     */
    @Test
    @Transactional // Solve the problem of no session in java code
    public void  testQuery3() {
        LinkMan linkMan = linkManDao.findOne(2l);
        //Object navigation query belongs to customers
        Customer customer = linkMan.getCustomer();
        System.out.println(customer);
    }

}