SpringData JPA JpaRepository Query Creation Keywords - Derived Query

Giriş
Metod söz dizimi açıklaması şöyle.

A derived query method name has two main components separated by the first By keyword:

1. The introducer clause like find, read, query, count, or get which tells Spring Data JPA what you want to do with the method. This clause can contain further expressions, such as Distinct to set a distinct flag on the query to be created.

2. The criteria clause that starts after the first By keyword. The first By acts as a delimiter to indicate the start of the actual query criteria. The criteria clause is where you define conditions on entity properties and concatenate them with And and Or keywords.

Introducers + Araya Eklenen Kelimeler + Limiting Operations + By + Criteria + Order By

Introducers

Bunlar 5 tane. liste şöyle

find

read

query

count

get

Reactive Return Types

Açıklaması şöyle

Reactive support is also there since (spring-data 2.0) depending on your configuration

Örnek

Şöyle yaparız

CompletableFuture<WebAuthnUser> findByAddToken(byte[] token);

userRepository.findByAddToken(token)
  .thenAccept( u-> log.info("found {}",u);
  .get();

userRepository.findByUsername("junit")
  .ifPresentOrElse(u -> log.info("found user: {}", user),
                   () -> fail("user not found"));


userRepository.findByAddTokenAndRegistrationAddStartAfter(token, 
  LocalDateTime.now().minusMinutes(10))
  .ifPresentOrElse(u -> log.info("found user: {}", user),
                   () -> fail("user not found"));

Araya Eklenen Kelimeler

Açıklaması şöyle. Yani Limiting Operation kelimeleri hariç, By kelimesine kadar etklenen şeyler etkisiz eleman

Spring Data’s method parsing uses prefix keywords like find, exists, count, and delete and a terminating By keyword. Everything you put in between find and By makes your method name more expressive and does not affect query derivation.

Örnek
Şu 3 metod aynı şeyi yaparlar.

List<SomeEntity> findBySomeCondition();

veya

List<SomeEntity> findAllBySomeCondition();

veya

List<SomeEntity> findAnythingYouWantToPutHereBySomeCondition();

Keyword Listesi
Metod isimlerindeki keyword'lerin listesi 5.3.2. Query Creation başlığı altında.
Bunlar "Logical Keywords". Her "Logical Keyword" başlığı altında "Keyword Expressions" var. "Keyword Expressions" Appendix C: Repository query keywords bölümünde görülebilir.

IgnoreCase Keywords
Açıklaması şöyle.

The IgnoreCase keyword is used to enable case-insensitive search for a specific property:
List<User> findByNameIgnoreCase(String name);

To enable case-insensitive search for all suitable properties, you should use the AllIgnoreCase keyword:
List<User> findByNameOrEmailAllIgnoreCase(String name, String email);

Sorting Derived Query Results
Derived Query - OrderBy yazısına taşıdım

Limiting Derived Query Results
Limiting Derived Query Results yazısına taşıdım

Paginate Derived Query Results
Açıklaması şöyle.

Spring Data JPA provides another special parameter Pageable to apply pagination to query results. You just need to add this special parameter to your query method definition and change the return type to Page<User>:

Page<User> findByActive(boolean active, Pageable pageable);
The Pageable interface makes it very easy to dynamically add paging to your statically defined query. A Page knows about the total number of elements and pages available.

You just define the page number you want to retrieve and how many records should be on a page. That’s it. Spring Data JPA will automatically create an appropriate paging query:

Pageable pageable = PageRequest.of(0, 10);
Page<User> userPage = userRepository.findByActive(true, pageable)
You can even add dynamic sorting to derived query through the Pageable instance:

Pageable pageable = PageRequest.of(0, 10, Sort.by("name").descending());
Page<User> userPage = userRepository.findByActive(true, pageable);

Derived Delete Queries
Açıklaması şöyle.

Spring Data JPA also supports derived delete queries:

// delete all users by name
void deleteByName(String name);

// delete all active or inactive users
void deleteAllByActive(boolean active);

1. AND
Bu "Logical Keyword" altında sadece "Keyword Expression" olarak yine sadece And var. Açıklaması şöyle. Eğer metod ismi çok uzun oluyorsa @Query anotasyonunu kullanmak daha iyi olur.

Spring Data JPA does not explicitly limit the number of expressions you can combine in a single derived query. However, you shouldn't go insane. Too long derived queries are hard to read and maintain. For complex use cases, you should rather use custom queries.

Örnek
Elimizde şöyle bir sınıf olsun.

@Entity 
public class Person {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Integer id;
  private String name;
  private Integer age;
  private String address;
}

Tek alana göre sorgulamak için şöyle yaparız.

List<Person> findByAddress(String address);

Birden fazla alana göre sorgulamak için şöyle yaparız.

Person findByNameAndAddress(String name, String address);

2. OR
Örnek ver

3. AFTER
Örnek ver

4. BEFORE
Örnek ver

5. CONTAINING
Bu "Logical Keyword" altında "Keyword Expression" olarak Containing, IsContaining, Contains var. Şöyle yaparız findByFirstnameContaining
Açıklaması şöyle.

Similarly, for users whose names contain a specific value, there exists a Containing keyword which is equivalent to WHERE name LIKE '%infix%':

Örnek
Şöyle yaparız.

List<customer> findTop5ByNameContains(String name);

Örnek

Şöyle yaparız.

Page<RoomDetail> findByRoomNumberOrBedTypeOrRoomCategoryContains(String search,
  Pageable pageable);

6. BETWEEN
Şöyle yaparız

@Entity
@Table
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@ToString
public class User implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private String lastName;
    private Integer age;

}

import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {

  public List<User> findAllByOrderByIdAsc();

  public List<User> findByAge(Integer age);

  public List<User> findByFirstNameStartingWith(String character);

  public List<User> findByAgeLessThan(Integer age);

  public List<User> findByFirstNameStartingWithAndAge(String character, Integer age);

  public List<User> findByAgeBetween(Integer age1, Integer age2);

  public User findFirstByOrderByAgeDesc();
}

7. ENDING_WITH
Açıklaması şöyle.

Spring Data JPA will translate this to WHERE name LIKE '%suffix' query.

8. EXISTS

Örnek ver

9. FALSE

Örnek ver

10. GREATER_THAN
Örnek ver

11. GREATER_THAN_EQUALS
Bu "Logical Keyword" altında "Keyword Expression" olarak GreaterThan, IsGreaterThan var. Şöyle yaparız findByAgeGreaterThanEqual
Örnek
Şöyle yaparız.

List<Customer> findTop1000BySubscriptionDateGreaterThanEqualAndIndividual
  (Date subsDate, int indOrComp);

12. IN

Bu "Logical Keyword" altında "Keyword Expression" olarak In, IsIn var. Şöyle yaparız

findByAgeIn(Collection<Age> ages)




Örnek
IN içine verilen parametre sayısı çok fazla ise geçici bir tablo oluşturmak daha iyi.

Örnek
Şöyle bir sorgu isteyelim

student0_.name in (? , ?);

Şöyle yaparız.

List<Student> findByGrpAndNameIn(String grp, Set<String> name);


13. IS
Örnek ver

14. IS_EMPTY
Örnek ver

15. IS_NOT_EMPTY
Örnek ver

16. IS_NULL
Örnek ver

17. IS_NOT_NULL
Örnek ver

18. LESS_THAN
Örnek ver

19. LESS_THAN_EQUAL
Örnek ver

20. LIKE
Açıklaması şöyle.

All the above three condition keywords automatically append the % operator to the parameter value. It is good enough for simple use cases.

For complex matching operation which includes several % operators, you should use the Like keyword instead:

The Like (or NotLike) keyword does not append the % operator to the argument. You have to explicitly define the matching pattern like below:

21. NEAR
Örnek ver

22. NOT
Derived Query Not yazısına taşıdım

23. NOT_IN
Örnek ver

24. NOT_LIKE
Örnek ver

25. REGEX
Örnek ver

26. STARTING_WITH

Bu "Logical Keyword" altında "Keyword Expression" olarak StartingWith, IsStartingWith, StartsWith var. Şöyle yaparız findByFirstnameStartingWith. 
Açıklaması şöyle.

Spring Data JPA will translate this to WHERE name LIKE 'prefix%' query.

Örnek
Şöyle yaparız.

List<customer> findTop5ByNameStartingWith(String name);

27. TRUE
Örnek ver

28. WITHIN
Örnek ver