Spring data JPA: addition, deletion, modification and query of JpaRepository

Keywords: jpa

Jpa query

1. Simple query of jparepository

There are also two basic queries. One is that spring data has been implemented by default, and the other is that it is automatically parsed into SQL according to the query method.

Pre generation method

spring data jpa generates some basic CURD methods in advance by default, such as adding, deleting, modifying, etc

Inherit JpaRepository

   
     
      
     
     
      
       public 
       interface UserRepository extends JpaRepository<User, Long> {
      
     

     
      
     
     
      
       }

Use default method

   
     
      
     
     
      
       @Test
      
     

     
      
     
     
      
       public void testBaseQuery() throws Exception {
      
     

     
      
     
     
      
           User user=
       new User();
      
     

     
      
     
     
      
           userRepository.findAll();
      
     

     
      
     
     
      
           userRepository.findOne(
       1l);
      
     

     
      
     
     
      
           userRepository.save(user);
      
     

     
      
     
     
      
           userRepository.delete(user);
      
     

     
      
     
     
      
           userRepository.count();
      
     

     
      
     
     
      
           userRepository.exists(
       1l);
      
     

     
      
     
     
          
       // ...
      
     

     
      
     
     
      
       }

The custom simple query is to automatically generate SQL according to the method name. The main syntax is findxxby, readaxxby, queryxxby and countxxby, getXXBy followed by property name:
The specific keywords, usage and production into SQL are shown in the following table

Keyword	Sample	JPQL snippet
And	findByLastnameAndFirstname	... where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	... where x.lastname = ?1 or x.firstname = ?2
Is,Equals	findByFirstnameIs,findByFirstnameEquals	... where x.firstname = ?1
Between	findByStartDateBetween	... where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	... where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	... where x.age ⇐ ?1
GreaterThan	findByAgeGreaterThan	... where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	... where x.age >= ?1
After	findByStartDateAfter	... where x.startDate > ?1
Before	findByStartDateBefore	... where x.startDate < ?1
IsNull	findByAgeIsNull	... where x.age is null
IsNotNull,NotNull	findByAge(Is)NotNull	... where x.age not null
Like	findByFirstnameLike	... where x.firstname like ?1
NotLike	findByFirstnameNotLike	... where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	... where x.firstname like ?1 (parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	... where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	... where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	... where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	... where x.lastname <> ?1
In	findByAgeIn(Collection ages)	... where x.age in ?1
NotIn	findByAgeNotIn(Collection age)	... where x.age not in ?1
TRUE	findByActiveTrue()	... where x.active = true
FALSE	findByActiveFalse()	... where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	... where UPPER(x.firstame) = UPPER(?1)

According to the specification of Spring Data, the query method starts with find | read | get. When the query condition is involved, the attributes of the condition are connected with the condition keyword,

Note that condition attributes are capitalized.

Example:

For example: define an Entity class:

   
     
      
     
     
      
       class People{
      
     

     
      
     
     
             
       private String firstName;
      
     

     
      
     
     
             
       private String lastName;
      
     

     
      
     
     
      
       }

When using and criteria above to query, it should be written as follows:

findByLastNameAndFirstName(String lastName,String firstName);

Note: the attribute name and number of conditions should correspond to the position and number of parameters one by one

2.JpaRepository query method parsing process

JPA method name resolution process

The Spring Data JPA framework intercepts the redundant prefix of the method name when parsing the method name
For example, find, findBy, read, readBy, get, getBy, and then parse the rest.
Suppose the following query is created: findByUserDepUuid(), when the framework parses this method, it first removes findBy, and then parses the remaining attributes. It is assumed that the query entity is Doc.

--1. First judge userDepUuid (according to POJO (Plain Ordinary Java Object, simple java object, actually ordinary java bean) specification, the initial letter becomes lowercase.) Is it a property of the query entity,

If you query based on this attribute; If you do not have this attribute, continue with step 2.

--2. Intercept the string beginning with the first uppercase letter (UUID here) from right to left, and then check whether the remaining string is an attribute of the query entity,

If yes, it means to query according to this attribute; If there is no such attribute, repeat step 2 and continue to intercept from right to left; Finally, assume that user is an attribute of the query entity.

--3. Next, process the remaining part (depuuid), and first judge whether the type corresponding to user has depuuid attribute,

If yes, it means that the method is finally queried according to the value of "Doc.user.depUuid";

Otherwise, continue to intercept from right to left according to the rules in step 2. Finally, it means to query according to the value of "Doc.user.dep.uuid".

--4. There may be a special case. For example, Doc contains a user attribute and a userDep attribute. There will be confusion at this time.

You can explicitly add "" between attributes Express the intention explicitly, such as "findbyuser"_ Depuuid() "or" findbyuserdep "_ uuid()".

Special parameters (paging or sorting):

You can also directly add paging or sorting parameters to the parameters of the method, such as:

   
     
      
     
     
      
       Page<UserModel> findByName(
       String 
       name, Pageable pageable);
      
     

     
      
     
     
      
       List<UserModel> findByName(
       String 
       name, Sort sort);

Pageable It is a paging implementation class encapsulated by spring. When using it, you need to pass in the number of pages, the number of entries per page and sorting rules

   
     
      
     
     
      
       @Test
      
     

     
      
     
     
      
       public void testPageQuery() throws Exception {
      
     

     
      
     
     
          
       int page=
       1,size=
       10;
      
     

     
      
     
     
      
           Sort sort = 
       new Sort(Direction.DESC, 
       "id");
      
     

     
      
     
     
      
           Pageable pageable = 
       new PageRequest(page, size, sort);
      
     

     
      
     
     
      
           userRepository.findALL(pageable);
      
     

     
      
     
     
      
           userRepository.findByUserName(
       "testName", pageable);
      
     

     
      
     
     
      
       }

NamedQueries using JPA

The method is as follows:

1: Use @ NamedQuery on the entity class, for example:

@NamedQuery(name = "UserModel.findByAge",query = "select o from UserModel o where o.age >= ?1")

2: Define a method with the same name in the Repository interface of the DAO implemented by yourself. An example is as follows:

public List<UserModel> findByAge(int age);

3: Then you can use it. Spring will first find out whether there is a NamedQuery with the same name. If so, it will not be resolved according to the method defined by the interface.

Use @ Query to specify a local Query

Just set nativeQuery to true

For example:

   
     
      
     
     
      
       @Query(value="select * from tbl_user where name like %?1" ,nativeQuery=true)
      
     

     
      
     
     
      
       public List<UserModel> findByUuidOrAge(String name);

Note: the current version of local query does not support page turning and dynamic sorting

Use named parameters

Use @ Param

For example:

   
     
      
     
     
      
       @Query(value="select o from UserModel o where o.name like %:nn")
      
     

     
      
     
     
      
       public List<UserModel> findByUuidOrAge(@Param("nn") String name);

The order in which queries are created

When Spring Data JPA creates proxy objects for interfaces, if it finds that multiple of the above situations are available at the same time, which strategy should it take first?

< JPA: repositories > provides the query lookup strategy attribute to specify the search order. It has the following three values:

1: create-if-not-found:

If the method specifies a Query statement through @ Query, the statement is used to implement the Query;

If not, check whether a qualified named query is defined. If found, use the named query;

If neither is found, the query is created by resolving the method name.

This is the default value of the querylookup strategy property

2: create: creates a query by resolving the method name.

Even if there is a qualified named Query, or the Query statement specified by the method through @ Query, it will be ignored

3: use-declared-query:

If the method specifies a Query statement through @ Query, the statement is used to implement the Query;

If not, find out whether a qualified named query is defined. If found, use the query

Named query; If neither is found, an exception is thrown

3.JpaRepository restriction query

Sometimes we only need to query the first N elements or withdraw the previous entity.

   
     
      
     
     
      
       User findFirstByOrderByLastnameAsc();
      
     

     
      
     
     
      
       User findTopByOrderByAgeDesc();
      
     

     
      
     
     
      
       Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);
      
     

     
      
     
     
      
       List<User> findFirst10ByLastname(String lastname, Sort sort);
      
     

     
      
     
     
      
       List<User> findTop10ByLastname(String lastname, Pageable pageable);

4.JpaRepository multi table query

There are two ways to implement multi table query in spring data jpa. The first is to use the cascade query of hibernate, and the second is to create an interface of result set to receive the results of connected table query. Here is the second way.
First, you need to define an interface class for the result set.

   
     
      
     
     
      
       public 
       interface HotelSummary {
      
     

     
      
     
     
          
       City getCity();
      
     

     
      
     
     
          
       String getName();
      
     

     
      
     
     
          
       Double getAverageRating();
      
     

     
      
     
     
          
       default Integer getAverageRatingRounded() {
      
     

     
      
     
     
              
       return getAverageRating() == 
       null ? 
       null : (
       int) Math.round(getAverageRating());
      
     

     
      
     
     
      
           }
      
     

     
      
     
     
      
       }

The return type of the query method is set to the newly created interface

   
     
      
     
     
      
       @Query("select h.city as city, h.name as name, avg(r.rating) as averageRating "
      
     

     
      
     
     
      
               + "from Hotel h left outer join h.reviews r where h.city = ?1 group by h")
      
     

     
      
     
     
      
       Page<HotelSummary> findByCity(City city, Pageable pageable);
      
     

     
      
     
     
       
      
     

     
      
     
     
       
      
     

     
      
     
     
      
       @Query("select h.name as name, avg(r.rating) as averageRating "
      
     

     
      
     
     
      
               + "from Hotel h left outer join h.reviews r  group by h")
      
     

     
      
     
     
      
       Page<HotelSummary> findByCity(Pageable pageable);

   
     
      
     
     
      
       Page<HotelSummary> hotels = 
       this.hotelRepository.findByCity(
       new PageRequest(
       0, 
       10, Direction.ASC, 
       "name"));
      
     

     
      
     
     
      
       for(HotelSummary summay:hotels){
      
     

     
      
     
     
      
               System.out.println(
       "Name" +summay.getName());
      
     

     
      
     
     
      
           }

During operation, Spring will automatically produce a proxy class for the interface (HotelSummary) to receive the returned results, and the code summary is obtained in the form of getXX

JPA update

Query statements that support updating classes

Add @ Modifying

For example:

   
     
      
     
     
      
       @Modifying
      
     

     
      
     
     
      
       @Query(value="update UserModel o set o.name=:newName where o.name like %:nn")
      
     

     
      
     
     
      
       public int findByUuidOrAge(@Param("nn") String name,@Param("newName") String newName);

be careful:

1: The return value of the method should be int, indicating the number of rows affected by the update statement

2: A transaction must be added to the calling place. It cannot be executed normally without a transaction

JPA delete

SQL mode - delete

   
     
      
     
     
      
       @Query(value = "delete from r_upa where user_id= ?1 and point_indecs_id in (?2)", nativeQuery = true)
      
     

     
      
     
     
      
       @Modifying
      
     

     
      
     
     
      
       void deleteByUserAndPointIndecs(Long uid, List<Long> hids);

be careful:

Like the delete and update statements, the @ Modifying annotation needs to be added. When using, the @ Transactional annotation is required in the Repository or at a higher level.

Function (delete) mode - delete

You can directly use delete(id) to delete a piece of data according to the id
You can also use deleteByName(String name). You need to add @ Transactional annotation before using it
The deleteByXXXX of Spring Data JPA is to select first and then delete after the whole Transaction is completed

JpaRepository

   
     
      
     
     
      
       @NoRepositoryBean
      
     

     
      
     
     
      
       public 
       interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
      
     

     
      
     
     
          
      
     

     
      
     
     
          
       /**
      
     

     
      
     
     
      
       	 * Deletes the given entities in a batch which means it will create a single {@link Query}. Assume that we will clear
      
     

     
      
     
     
      
       	 * the {@link javax.persistence.EntityManager} after the call.
      
     

     
      
     
     
      
       	 *
      
     

     
      
     
     
      
       	 * @param entities
      
     

     
      
     
     
      
            * Unbinding multiple in batch has the advantage that only one SQL statement will be formed
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      	
       void deleteInBatch(Iterable<T> entities);
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
       /**
      
     

     
      
     
     
      
       	 * Deletes all entities in a batch call.
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      	
       void deleteAllInBatch();
      
     

     
      
     
     
       
      
     

     
      
     
     
         
      
     

     
      
     
     
      
       }

CrudRepository

   
     
      
     
     
      
       @NoRepositoryBean
      
     

     
      
     
     
      
       public 
       interface CrudRepository<T, ID> extends Repository<T, ID> {
      
     

     
      
     
     
         
      
     

     
      
     
     
          
       /**
      
     

     
      
     
     
      
       	 * Deletes the entity with the given id.
      
     

     
      
     
     
      
       	 *
      
     

     
      
     
     
      
       	 * @param id must not be {@literal null}.
      
     

     
      
     
     
      
       	 * @throws IllegalArgumentException in case the given {@code id} is {@literal null}
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      	
       void deleteById(ID id);
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
       /**
      
     

     
      
     
     
      
       	 * Deletes a given entity.
      
     

     
      
     
     
      
       	 *
      
     

     
      
     
     
      
       	 * @param entity
      
     

     
      
     
     
      
       	 * @throws IllegalArgumentException in case the given entity is {@literal null}.
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      	
       void delete(T entity);
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
       /**
      
     

     
      
     
     
      
       	 * Deletes the given entities.
      
     

     
      
     
     
      
       	 *
      
     

     
      
     
     
      
       	 * @param entities
      
     

     
      
     
     
      
       	 * @throws IllegalArgumentException in case the given {@link Iterable} is {@literal null}.
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      	
       void deleteAll(Iterable<? extends T> entities);
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
       /**
      
     

     
      
     
     
      
       	 * Deletes all entities managed by the repository.
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      	
       void deleteAll();
      
     

     
      
     
     
      
       }

JPA add

Leverage the in JpaRepository and CrudRepository save operation

JpaRepository

   
     
      
     
     
      
       @NoRepositoryBean
      
     

     
      
     
     
      
       public 
       interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
      
     

     
      
     
     
      	
       /*
      
     

     
      
     
     
      
       	 * (non-Javadoc)
      
     

     
      
     
     
      
       	 * @see org.springframework.data.repository.CrudRepository#save(java.lang.Iterable)
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      
       	<S extends T> 
       List<S> saveAll(Iterable<S> entities);
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
       /**
      
     

     
      
     
     
      
       	 * Flushes all pending changes to the database.
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      	
       void flush();
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
       /**
      
     

     
      
     
     
      
       	 * Saves an entity and flushes changes instantly.
      
     

     
      
     
     
      
       	 *
      
     

     
      
     
     
      
       	 * @param entity
      
     

     
      
     
     
      
       	 * @return the saved entity
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      
       	<S extends T> 
       S saveAndFlush(S entity);
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
      
     

     
      
     
     
      
       }

CrudRepository

   
     
      
     
     
      
       @NoRepositoryBean
      
     

     
      
     
     
      
       public 
       interface CrudRepository<T, ID> extends Repository<T, ID> {
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
       /**
      
     

     
      
     
     
      
       	 * Saves a given entity. Use the returned instance for further operations as the save operation might have changed the
      
     

     
      
     
     
      
       	 * entity instance completely.
      
     

     
      
     
     
      
       	 *
      
     

     
      
     
     
      
       	 * @param entity must not be {@literal null}.
      
     

     
      
     
     
      
       	 * @return the saved entity will never be {@literal null}.
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      
       	<S extends T> 
       S save(S entity);
      
     

     
      
     
     
       
      
     

     
      
     
     
      	
       /**
      
     

     
      
     
     
      
       	 * Saves all given entities.
      
     

     
      
     
     
      
       	 *
      
     

     
      
     
     
      
       	 * @param entities must not be {@literal null}.
      
     

     
      
     
     
      
       	 * @return the saved entities will never be {@literal null}.
      
     

     
      
     
     
      
       	 * @throws IllegalArgumentException in case the given entity is {@literal null}.
      
     

     
      
     
     
      
       	 */
      
     

     
      
     
     
      
       	<S extends T> 
       Iterable<S> saveAll(Iterable<S> entities);
      
     

     
      
     
     
      
       }

The difference between JpaRepository and CrudRepository

Implementation source code of save method in JpaRepository:

   
     
      
     
     
        
       @Transactional
      
     

     
      
     
     
          
       public <S extends T> 
       List<S> save(Iterable<S> entities) {
      
     

     
      
     
     
      
               List<S> result = 
       new ArrayList<S>();
      
     

     
      
     
     
              
       if (entities == 
       null) {
      
     

     
      
     
     
                  
       return result;
      
     

     
      
     
     
      
               }
      
     

     
      
     
     
              
       for (S entity : entities) {
      
     

     
      
     
     
      
                   result.add(save(entity));
      
     

     
      
     
     
      
               }
      
     

     
      
     
     
              
       return result;
      
     

     
      
     
     
      
           }

Source code of save method in CrudRepository

   
     
      
     
     
      
       @Transactional
      
     

     
      
     
     
          
       public <S extends T> 
       S save(S entity) {
      
     

     
      
     
     
              
       if (entityInformation.isNew(entity)) {
      
     

     
      
     
     
      
                   em.persist(entity);
       //If it's new, insert it
      
     

     
      
     
     
                  
       return entity;
      
     

     
      
     
     
      
               } 
       else {
      
     

     
      
     
     
                  
       return em.merge(entity); 
       //Not a new merge
      
     

     
      
     
     
      
               }
      
     

     
      
     
     
      
           }

It can be seen from the source code that the save method in CrudRepository is equivalent to merge+save. It will first judge whether the record exists, update it if it exists, and insert it if it does not exist

Reference source: http://www.ityouknow.com/springboot/2016/08/20/springboot(%E4%BA%94)-spring-data-jpa%E7%9A%84%E4%BD%BF%E7%94%A8.html

Posted by nickcwj on Mon, 06 Sep 2021 23:14:14 -0700

Programmer Group

Spring data JPA: addition, deletion, modification and query of JpaRepository

Jpa query

1. Simple query of jparepository

Pre generation method

Use default method

2.JpaRepository query method parsing process

JPA method name resolution process

Special parameters (paging or sorting):

NamedQueries using JPA

Use @ Query to specify a local Query

Use named parameters

The order in which queries are created

3.JpaRepository restriction query

4.JpaRepository multi table query

JPA update

Query statements that support updating classes

JPA delete

SQL mode - delete

Function (delete) mode - delete

JpaRepository

CrudRepository

JPA add

JpaRepository

CrudRepository

The difference between JpaRepository and CrudRepository

Hot Keywords