Development environment construction based on SpringBoot framework: use of SpringData JPA

The previous article has described how to build a Springboot environment and integrate mysql database

Development environment construction based on SpringBoot framework: project creation + integrated database

3. Query operation of SpringData JPA

SpringData JPA has 5 core interfaces:

  1. Repository: It is the top-level interface. It is an empty interface. The purpose is to unify all Repository types and allow components to be automatically recognized when scanning.

  2. CrudRepository: It is a sub-interface of Repository and provides CRUD functions.

  3. PagingAndSortingRepository: is a sub-interface of CrudRepository, adding paging and sorting functions

  4. JpaRepository: It is a sub-interface of PagingAndSortingRepository, adding some practical functions, such as batch operations, etc.

  5. JpaSpecificationExecutor: interface used to make complex queries

Repository->CrudRepository->PagingAndSortingRepository->JpaRepository is a hierarchical inheritance relationship, and JpaRepository has the most complete functions. JpaSpecificationExecutor needs to be paired with Repository to implement querying responsible for conditions.

Query

  1. Based on method name naming rules
    In the previous article, we added a new interface to the UserRepository interface
 User findByUserName(String username);

We know that the userName field in the user’s Entity is mapped to the user_name field in the database. We add this interface to the UserRepository and then execute the select * from user where user_name=xxx’ sql statement when it is actually called.

Rule: findBy(keyword) + attribute name (the first letter of the attribute name is capitalized) + query condition (the first letter is capitalized)

Common rules:

Keywords SQL symbols Method naming sql where clause
And and findByNameAndPwd where name= ? and pwd =?
Or or findByNameOrSex where name= ? or sex=?
Is, Equals = findById, findByIdEquals where id = ?
Between between xx and xx findByIdBetween where id between ? and ?
LessThan < findByIdLessThan where id < ?
LessThanEqual < = findByIdLessThanEqual where id <= ?
GreaterThan > findByIdGreaterThan where id > ?
GreaterThanEqual >= findByIdGreaterThanEqual where id > = ?
After > findByIdAfter where id > ?
Before < findByIdBefore where id < ?
IsNull is null findByNameIsNull where name is null
isNotNull, NotNull is not null findByNameNotNull where name is not null
Like like findByNameLike where name like ?
NotLike not like findByNameNotLike where name not like ?
StartingWith like ‘xx%’ findByNameStartingWith where name like ‘?%’
EndingWith like ‘%xx’ findByNameEndingWith where name like ‘%?’
Containing like ‘%xx%’ findByNameContaining where name like ‘%?%’
OrderBy order by findByIdOrderByXDesc where id=? order by x desc
Not <>、!= findByNameNot where name <> ?
In in() findByIdIn(Collection c) where id in (?)
NotIn not in() findByNameNot where name <> ?
True =true findByAaaTue where aaa = true
False =false findByAaaFalse where aaa = false
IgnoreCase upper(x)=upper(y) findByNameIgnoreCase where UPPER(name)=UPPER(? )
top top, rownum<=x findTop10 top 10 / where ROWNUM < =10
  1. Query and update using @Query
public interface UserRepository extends JpaRepository<User,Long> {<!-- -->
    User findByUserName(String username);

    @Query("from User where userName= ?1")
    User findByUserNameJPQL(String username);

@Query("from User where userName like :username")
    List<User> queryUserByLikeUsernameUseJPQL(@Param("username")String username);

//Execute queries through native sql
@Query(value = "select * from user where user_name = ?1",nativeQuery=true)
    List<User> queryUserByUsernameUseSQL(String username);

@Query(value = "update user set user_name = ? where id = ?",nativeQuery=true)
    @Modifying // An update operation needs to be performed
    Integer updateUsernameById(String username,Long id);
}

Summarize:

  1. UPDATE and DELETE operations can be accomplished via custom JPQL. Note: JPQL does not support the use of INSERT (new operation)
  2. Write JPQL statements in the @Query annotation. If it is an UPDATE or DELETE operation, it must be decorated with @Modifying
  3. UPDATE or DELETE operations require the use of transactions. At this time, you need to add transaction operations on the methods of the Service layer.
  4. By default, there is a transaction on each method of SpringData, but it is a read-only transaction. They can’t complete the edit operation

Sort

  1. Based on method name naming rules
 /**
     * If you need all the data without querying according to conditions, you should write it like this:
     * findBy/getBy/queryBy.. + OrderBy + sorting field + sorting method
     * Sort by username in positive order by default
     * ..order by username
     */
    List<User> findByOrderByUsername();

    /**
     * Query all data: first sort by age field in reverse order and then by id field in forward order.
     * ..order by age desc, id asc
     */
    List<User> findByOrderByAgeDescIdAsc();

    /**
     * The query name result set is sorted in positive order:
     * ...where username = ? order by age asc
     */
    List<User> findByUsernameOrderByAgeAsc(String username);

    /**
     * The query name result set is sorted in reverse order:
     * ...where username = ? order by id desc
     */
    List<User> findByUsernameOrderByIdDesc(String username);

demo:

/**
     * Sorting operation:
     * 1. Sort a single column
     * 2. Sorting processing of multiple columns
     */
    @Test
    public void test2(){<!-- -->
        /**
         * Sort: This object encapsulates the sorting rules and the specified sorting field (represented by the properties of the object)
         * direction: sorting rule (Sort.Direction.DESC)
         * properties: Specify the attribute (id) for sorting
         */
        // 1. Sort the single column and sort the id in descending order.
        Sort sortOne = Sort.by(Sort.Direction.DESC,"id");
        Iterable iterable = this.userPagingAndSortingRepository.findAll(sortOne);
        iterable.forEach(System.out::println);

        System.err.println("--------------Gorgeous dividing line------------------");

        /**
         * You can get Order in the following two ways:
         * 1.new Sort.Order()
         * 2.Sort.Order.desc()/asc()/by()
         */
        // 2. For sorting processing of multiple columns, first perform descending order processing on the age column, and then perform ascending order processing on the id field.
        Sort.Order age = Sort.Order.desc("age");
        Sort.Order id = new Sort.Order(Sort.Direction.ASC,"id");
        Sort sortMany = Sort.by(age,id);
        this.userPagingAndSortingRepository.findAll(sortMany).forEach(System.out::println);
    }

Pagination

  1. Based on method name naming rules
 /**
     * Description: Sort all the data in the table according to age in Asc (ascending order) and then select the first data to return
     * SQL: ...order by age asc limit 1
     * Note: findFirst3ByOrderByAgeAsc()/findTop3ByOrderByAgeAsc() returns 3 items each time limit 3
     */
    User findFirstByOrderByAgeAsc();
    User findTopByOrderByAgeAsc();

    /**
     * Note: First perform data query and sort by Sort, then filter the first 2 rows of data in the data list and return limit 2
     * SQL: where username = ? order by field asc/desc
     */
    List<User> findFirst2ByUsername(String username, Sort sort);
    List<User> findTop2ByUsername(String username,Sort sort);

    /**
     * Description: First perform data query, query all specified usernames and then perform paging. After paging, perform data control.
     *Control instructions:
     * Regarding the control with paging, it is assumed that the data id queried after paging is 3, 4, 5. After querying these three pieces of data, perform data control.
     * In this case, the control is 2, and the returned ID data is two records, 3 and 4. The one with ID 5 will be discarded.
     * Then if the data control is 5 (Top5), then 3, 4, 5 will be printed and 6, 7 will be added to supplement the data length.
     */
    Page<User> queryFirst2ByUsername(String username, Pageable pageable);
    List<User> queryTop2ByUsername(String username, Pageable pageable);

demo:

/**
     * paging operation
     */
    @Test
    public void test1(){<!-- -->
        /**
         * Pageable: Encapsulates the paging parameters, the current page, and the number of items displayed on each page. Note: The current page starts from 0
         * PageRequest(page,size): page represents the current page, size represents how many items are displayed on each page
         */
        Pageable pageable = PageRequest.of(0, 2);
        Page<User> page = this.userPagingAndSortingRepository.findAll(pageable);
        System.out.println("Total number of records: " + page.getTotalElements());
        System.out.println("The current page: " + (page.getNumber() + 1));
        System.out.println("Total number of pages: " + page.getTotalPages());
        System.out.println("List of the current page: " + page.getContent());
        System.out.println("Number of records on the current page: " + page.getNumberOfElements());
        page.getContent().forEach(System.out::println);
    }
    /**
     * Paging + sorting processing
     *
     */
    @Test
    public void test3(){<!-- -->
        // Sorting operation object
        Sort.Order age = Sort.Order.desc("age");
        Sort.Order id = Sort.Order.asc("id");
        Sort sort = Sort.by(age,id);
        // The sorting operation remains unchanged, you just need to add an additional Sort parameter in PageRequest.of
        Pageable pageable = PageRequest.of(0, 2,sort);
        Page<User> page = this.userPagingAndSortingRepository.findAll(pageable);
        System.out.println("Total number of records: " + page.getTotalElements());
        System.out.println("The current page: " + (page.getNumber() + 1));
        System.out.println("Total number of pages: " + page.getTotalPages());
        System.out.println("Number of records on the current page: " + page.getNumberOfElements());
        System.out.println("List of the current page: " + page.getContent());
        System.err.println("--------------Gorgeous dividing line------------------");
        page.getContent().forEach(System.out::println);
    }

Count

  1. Based on method name naming rules
 /**
     * count returns the total
     * countBy() is equivalent to countAllBy()
     *SQL:
     * select count(id) from sys_user where username = ?
     * select count(id) from sys_user
     */
    Long countByUsername(String username);
    Long countAllBy();

Delete

  1. Based on method name naming rules
 /**
     * Note: You must add transactions and rollbacks, so that you can delete as many as you find based on the conditions.
     * Corresponding SQL statement:
     * Hibernate: select * from sys_user where username = ?
     * Hibernate: delete from sys_user where id=?
     * Hibernate: delete from sys_user where id=?
     * ....
     * Regarding the difference between removeBy and deleteBy methods:
     * They are the same. Which one to choose depends on your personal preference.
     */
    void deleteByUsername(String username);
    void removeByUsername(String username);

Interface methods

CrudRepository interface:

@NoRepositoryBean
public interface CrudRepository<T, ID extends Serializable> extends Repository<T, ID> {<!-- --> // T is the entity class to be operated, and ID is the type of the primary key of the entity class

    <S extends T> S save(S entity); // save
    <S extends T> Iterable<S> saveAll(Iterable<S> entities); // Batch save
    
    Optional<T> findById(ID id); // Query an Optional based on id
    boolean exists(ID id); // Determine whether the object exists based on id
    Iterable<T> findAll(); // Query all objects
    Iterable<T> findAllById(Iterable<ID> ids); // Query all objects based on the id list
 
    long count(); // Calculate the total number of objects

    void delete(ID id); //Delete based on id
    void delete(T entity); // Delete an object
    void delete(Iterable<? extends T> entities); // Batch deletion, collection objects (deletion one by one during background execution)
    void deleteAll(); // Delete all (deletion one by one during background execution)
}

PagingAndSortingRepository interface:

@NoRepositoryBean
public interface PagingAndSortingRepository<T, ID extends Serializable> extends CrudRepository<T, ID> {<!-- -->
    Iterable<T> findAll(Sort sort); // Sort only
    Page<T> findAll(Pageable pageable); // Paging and sorting
}

JpaRepository interface:

@NoRepositoryBean
public interface JpaRepository<T, ID extends Serializable>
        extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {<!-- -->
    
    @Override
    List<T> findAll(); // Query all objects and return List
    @Override
    List<T> findAll(Sort sort); // Query all objects, sort them, and return List
    @Override
    List<T> findAllById(Iterable<ID> ids); // Query all objects based on the id list and return List

    @Override
    <S extends T> List<S> saveAll(Iterable<S> entities); // Save in batches and return object List
    <S extends T> S saveAndFlush(S entity); // Save and force database synchronization
    void flush(); // Force cache to synchronize with database

    void deleteInBatch(Iterable<T> entities); // Batch delete collection objects (when executing in the background, a statement is generated for execution, using multiple or conditions)
    void deleteAllInBatch(); // Delete all (execute a statement, such as: delete from user)

    T getOne(ID id); // Query the object based on id and return the reference of the object (different from findOne). When the object does not exist, the returned reference is not null, but each attribute value is null.
    
    @Override
    <S extends T> List<S> findAll(Example<S> example); // Query based on examples
    @Override
    <S extends T> List<S> findAll(Example<S> example, Sort sort);// Query and sort based on examples
}

JpaSpecificationExecutor

Interface definition:

/**
 * JpaSpecificationExecutor: interface used for dynamic query
 * Specification: It is a query specification provided by SpringDataJPA. To make complex queries, you only need to set query conditions around this specification.
 * There are a total of 5 interface methods under the JpaSpecificationExecutor interface
 */
public interface JpaSpecificationExecutor<T> {<!-- -->
T findOne(Specification<T> spec);//Query a single
List<T> findAll(Specification<T> spec);// Query all
Page<T> findAll(Specification<T> spec, Pageable pageable);//Query all【Paging】
List<T> findAll(Specification<T> spec, Sort sort); // Query all [sort]
long count(Specification<T> spec);//Count the total number
}

Instructions

  1. The dao layer integrates JpaSpecificationExecutor
public interface UserJpaSpecificationExecutor extends JpaRepository<User,Long>, JpaSpecificationExecutor<User> {<!-- -->
}
  1. service layer
 /**
     * Conditional query - single condition
     * Predicate filter conditions
     * jpql: from User where username = 'Little King Next Door'
     * sql: select * from sys_user where username = '王小王'
     */
    @Test
    public void test1(){<!-- -->
        Specification<User> spec = new Specification<User>() {<!-- -->
            /**
             * @param root root object. An object that encapsulates query conditions
             * @param criteriaQuery defines a basic query. Generally used less
             * @param criteriaBuilder creates a query condition
             * @return Predicate: defines the query conditions
             */
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {<!-- -->
                Path<Object> username = root.get("username");
                Predicate predicate = criteriaBuilder.equal(username, "王小王");
                return predicate;
            }
        };
        List<User> userList = userJpaSpecificationExecutor.findAll(spec);
        userList.forEach(System.out::println);
    }
/**
     *Multi-condition query 2
     * jpql: from User where username like '%小王' and password = 'password' and age < 30
     * sql: select * from sys_user where username like '%小王' and password = 'password' and age < 30
     */
    @Test
    public void test3(){<!-- -->
        Specification<User> spec = (root, criteriaQuery, criteriaBuilder) -> {<!-- -->
            Predicate username = criteriaBuilder.like(root.get("username"), "%小王");
            Predicate password = criteriaBuilder.equal(root.get("password"), "password");
            Predicate age = criteriaBuilder.lt(root.get("age"), 30);
            Predicate predicate = criteriaBuilder.and(username, password);
            predicate = criteriaBuilder.and(predicate, age);
            return predicate;
        };
        List<User> userList = userJpaSpecificationExecutor.findAll(spec);
        userList.forEach(System.out::println);
    }

tips

  1. Comparison: Differences between deleteAll() and deleteAllInBatch():
    • deleteAll() deletes all, find ALL first, then delete one by one, and finally commit the transaction
    • deleteAllInBatch() deletes all, one sql. Obviously: deleteAllInBatch() method is more efficient
    • When performing batch deletion operations, try to use JpaRepository’s own batch deletion methods deleteInBatch() and deleteAllInBatch()
  2. Summary and differences between getOne() and findById() methods:
    • 1. getOne() is lazy loading. findById() is loaded immediately
    • 2. getOne() returns a reference to an entity. If there is no result, an exception will be thrown. findById() returns an Optional object
    • 3. getOne() belongs to JpaRepository. The bottom layer calls the getReference() method. In lazy loading mode, SQL is sent only when the object is called.
    • 4. findById() belongs to CrudRepository. The bottom layer calls the find() method. When this method is called, SQL is sent directly.

This article draws on part of the code and copywriting of the CSDN blogger “Roadside Shaomai”, and I would like to express my gratitude to the blogger.
Original link: https://blog.csdn.net/qq_36259143/article/details/120207737