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

  1. public interface UserRepository extends JpaRepository<User, Long> {
  2. }
  • Use default method

  1. @Test
  2. public void testBaseQuery() throws Exception {
  3. User user= new User();
  4. userRepository.findAll();
  5. userRepository.findOne( 1l);
  6. userRepository.save(user);
  7. userRepository.delete(user);
  8. userRepository.count();
  9. userRepository.exists( 1l);
  10. // ...
  11. }
  • 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
KeywordSampleJPQL snippet
AndfindByLastnameAndFirstname... where x.lastname = ?1 and x.firstname = ?2
OrfindByLastnameOrFirstname... where x.lastname = ?1 or x.firstname = ?2
Is,EqualsfindByFirstnameIs,findByFirstnameEquals... where x.firstname = ?1
BetweenfindByStartDateBetween... where x.startDate between ?1 and ?2
LessThanfindByAgeLessThan... where x.age < ?1
LessThanEqualfindByAgeLessThanEqual... where x.age ⇐ ?1
GreaterThanfindByAgeGreaterThan... where x.age > ?1
GreaterThanEqualfindByAgeGreaterThanEqual... where x.age >= ?1
AfterfindByStartDateAfter... where x.startDate > ?1
BeforefindByStartDateBefore... where x.startDate < ?1
IsNullfindByAgeIsNull... where x.age is null
IsNotNull,NotNullfindByAge(Is)NotNull... where x.age not null
LikefindByFirstnameLike... where x.firstname like ?1
NotLikefindByFirstnameNotLike... where x.firstname not like ?1
StartingWithfindByFirstnameStartingWith... where x.firstname like ?1 (parameter bound with appended %)
EndingWithfindByFirstnameEndingWith... where x.firstname like ?1 (parameter bound with prepended %)
ContainingfindByFirstnameContaining... where x.firstname like ?1 (parameter bound wrapped in %)
OrderByfindByAgeOrderByLastnameDesc... where x.age = ?1 order by x.lastname desc
NotfindByLastnameNot... where x.lastname <> ?1
InfindByAgeIn(Collection ages)... where x.age in ?1
NotInfindByAgeNotIn(Collection age)... where x.age not in ?1
TRUEfindByActiveTrue()... where x.active = true
FALSEfindByActiveFalse()... where x.active = false
IgnoreCasefindByFirstnameIgnoreCase... 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:

  1. class People{
  2. private String firstName;
  3. private String lastName;
  4. }

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:
  1. Page<UserModel> findByName( String name, Pageable pageable);
  2. 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
  1. @Test
  2. public void testPageQuery() throws Exception {
  3. int page= 1,size= 10;
  4. Sort sort = new Sort(Direction.DESC, "id");
  5. Pageable pageable = new PageRequest(page, size, sort);
  6. userRepository.findALL(pageable);
  7. userRepository.findByUserName( "testName", pageable);
  8. }

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:
  1. @Query(value="select * from tbl_user where name like %?1" ,nativeQuery=true)
  2. 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:

  1. @Query(value="select o from UserModel o where o.name like %:nn")
  2. 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.
  1. User findFirstByOrderByLastnameAsc();
  2. User findTopByOrderByAgeDesc();
  3. Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);
  4. List<User> findFirst10ByLastname(String lastname, Sort sort);
  5. 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.
  1. public interface HotelSummary {
  2. City getCity();
  3. String getName();
  4. Double getAverageRating();
  5. default Integer getAverageRatingRounded() {
  6. return getAverageRating() == null ? null : ( int) Math.round(getAverageRating());
  7. }
  8. }
  • The return type of the query method is set to the newly created interface
  1. @Query("select h.city as city, h.name as name, avg(r.rating) as averageRating "
  2. + "from Hotel h left outer join h.reviews r where h.city = ?1 group by h")
  3. Page<HotelSummary> findByCity(City city, Pageable pageable);
  4. @Query("select h.name as name, avg(r.rating) as averageRating "
  5. + "from Hotel h left outer join h.reviews r group by h")
  6. Page<HotelSummary> findByCity(Pageable pageable);
  • use
  1. Page<HotelSummary> hotels = this.hotelRepository.findByCity( new PageRequest( 0, 10, Direction.ASC, "name"));
  2. for(HotelSummary summay:hotels){
  3. System.out.println( "Name" +summay.getName());
  4. }

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:
  1. @Modifying
  2. @Query(value="update UserModel o set o.name=:newName where o.name like %:nn")
  3. 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

  1. @Query(value = "delete from r_upa where user_id= ?1 and point_indecs_id in (?2)", nativeQuery = true)
  2. @Modifying
  3. 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


  1. @NoRepositoryBean
  2. public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
  3. /**
  4. * Deletes the given entities in a batch which means it will create a single {@link Query}. Assume that we will clear
  5. * the {@link javax.persistence.EntityManager} after the call.
  6. *
  7. * @param entities
  8. * Unbinding multiple in batch has the advantage that only one SQL statement will be formed
  9. */
  10. void deleteInBatch(Iterable<T> entities);
  11. /**
  12. * Deletes all entities in a batch call.
  13. */
  14. void deleteAllInBatch();
  15. }


  1. @NoRepositoryBean
  2. public interface CrudRepository<T, ID> extends Repository<T, ID> {
  3. /**
  4. * Deletes the entity with the given id.
  5. *
  6. * @param id must not be {@literal null}.
  7. * @throws IllegalArgumentException in case the given {@code id} is {@literal null}
  8. */
  9. void deleteById(ID id);
  10. /**
  11. * Deletes a given entity.
  12. *
  13. * @param entity
  14. * @throws IllegalArgumentException in case the given entity is {@literal null}.
  15. */
  16. void delete(T entity);
  17. /**
  18. * Deletes the given entities.
  19. *
  20. * @param entities
  21. * @throws IllegalArgumentException in case the given {@link Iterable} is {@literal null}.
  22. */
  23. void deleteAll(Iterable<? extends T> entities);
  24. /**
  25. * Deletes all entities managed by the repository.
  26. */
  27. void deleteAll();
  28. }


JPA add

Leverage the in JpaRepository and CrudRepository   save operation


  1. @NoRepositoryBean
  2. public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
  3. /*
  4. * (non-Javadoc)
  5. * @see org.springframework.data.repository.CrudRepository#save(java.lang.Iterable)
  6. */
  7. <S extends T> List<S> saveAll(Iterable<S> entities);
  8. /**
  9. * Flushes all pending changes to the database.
  10. */
  11. void flush();
  12. /**
  13. * Saves an entity and flushes changes instantly.
  14. *
  15. * @param entity
  16. * @return the saved entity
  17. */
  18. <S extends T> S saveAndFlush(S entity);
  19. }


  1. @NoRepositoryBean
  2. public interface CrudRepository<T, ID> extends Repository<T, ID> {
  3. /**
  4. * Saves a given entity. Use the returned instance for further operations as the save operation might have changed the
  5. * entity instance completely.
  6. *
  7. * @param entity must not be {@literal null}.
  8. * @return the saved entity will never be {@literal null}.
  9. */
  10. <S extends T> S save(S entity);
  11. /**
  12. * Saves all given entities.
  13. *
  14. * @param entities must not be {@literal null}.
  15. * @return the saved entities will never be {@literal null}.
  16. * @throws IllegalArgumentException in case the given entity is {@literal null}.
  17. */
  18. <S extends T> Iterable<S> saveAll(Iterable<S> entities);
  19. }

The difference between JpaRepository and CrudRepository

  • Implementation source code of save method in JpaRepository:
  1. @Transactional
  2. public <S extends T> List<S> save(Iterable<S> entities) {
  3. List<S> result = new ArrayList<S>();
  4. if (entities == null) {
  5. return result;
  6. }
  7. for (S entity : entities) {
  8. result.add(save(entity));
  9. }
  10. return result;
  11. }
  • Source code of save method in CrudRepository
  1. @Transactional
  2. public <S extends T> S save(S entity) {
  3. if (entityInformation.isNew(entity)) {
  4. em.persist(entity); //If it's new, insert it
  5. return entity;
  6. } else {
  7. return em.merge(entity); //Not a new merge
  8. }
  9. }

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