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:
-
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.
-
CrudRepository: It is a sub-interface of Repository and provides CRUD functions.
-
PagingAndSortingRepository: is a sub-interface of CrudRepository, adding paging and sorting functions
-
JpaRepository: It is a sub-interface of PagingAndSortingRepository, adding some practical functions, such as batch operations, etc.
-
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
- 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 |
- 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:
- UPDATE and DELETE operations can be accomplished via custom JPQL. Note: JPQL does not support the use of INSERT (new operation)
- Write JPQL statements in the @Query annotation. If it is an UPDATE or DELETE operation, it must be decorated with @Modifying
- 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.
- 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
- 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
- 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
- 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
- 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
- The dao layer integrates JpaSpecificationExecutor
public interface UserJpaSpecificationExecutor extends JpaRepository<User,Long>, JpaSpecificationExecutor<User> {<!-- --> }
- 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
- 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()
- 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