網頁

▼

2020/4/10

Spring Data JPA Query Method 方法名稱查詢範例

Spring Data JPA Query Method的用法範例如下。

使用Spring Data JPA的Repository查詢時，可以直接以方法名稱作為查詢的條件，Spring Data JPA會自動將其轉對應的SQL，這樣就不用另外在寫JPQL或原生的SQL(Native SQL)語法了。

下面是用來對實體Employee進行資料庫操作的EmployeeRepository介面，裡面的各種查詢方法都是以方法名稱來表示要查詢的各種條件。例如findById方法名稱實際上就相當於JPQL

"select e from Employee e where e.id = ?1"

關於方法名稱的定義請參考官方文件Supported keywords inside method names。

EmployeeRepository

package com.abc.demo.repository;

import com.abc.dmeo.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import java.util.List;
import java.util.Optional;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    Optional<Employee> findById(long id); // where x.id = ?1
    Optional<Employee> findByIdIs(Long id); // where x.id = ?1
    Optional<Employee> findByIdEqual(Long id); // where x.id = ?1

    List<Employee> findByNameAndEmail(String name); // where x.name = ?1 and x.email = ?2
    List<Employee> findByNameOrEmail(String name); // where x.name = ?1 or x.email = ?2

    List<Employee> findByAgeLessThan(int age); // where x.age < ?1
    List<Employee> findByAgeLessThanEqual(int age); // where x.age <= ?1

    List<Employee> findByAgeGreaterThan(int age); // where x.age > ?1
    List<Employee> findByAgeGreaterThanEqual(int age); // where x.age >= ?1

    List<Employee> findByNameLike(String name); // where x.name like ?1
    List<Employee> findByNameNotLike(String name); // where x.name not like ?1

    List<Employee> findByAgeOrderByNameDesc(int age); // where x.age = ?1 order by x.name desc

    List<Employee> findByNameNot(String name); // where x.name <> ?1

    List<Employee> findByAgeIn(Collection<Integer> ages); // where x age in ?1
    List<Employee> findByAgeNotIn(Collection<Integer> ages); // where x age not in ?1

    List<Employee> findByDeletedTrue(); // where x.deleted  = true
    List<Employee> findByDeletedFalse(); // where x.deleted = false

    List<Employee> findByNameIsNotNullAndAgeLessThan(int age); // where x.name is not null and x.age < ?
    List<Employee> findByNameIsNullAndAgeGreaterThan(int age); // where x.name is null and x.age > ?

    Optional<Employee> findTopByAgeGreaterThan(int age); // 取得第一筆
    Optional<Employee> findFirstByAgeGreaterThan(int age); // 取得第一筆
    
    List<Employee> findTop10ByAgeGreaterThan(int age); // 取得前10筆
    List<Employee> findFirst10ByAgeGreaterThan(int age); // 取得前10筆

    long countByDeletedTrue(); // select count(*) from employee e where e.deleted == true
    long countByAgeGreaterThan(Integer age); // select count(*) from employee e where e.age > ?1

    boolean existsByName(String name);
    boolean existsById(Long id);

}

Employee為entity類，映射資料表EMPLOYEE。

Employee

package com.abc.demo.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "Employee")
public class Employee {

    @Id
    @Column(name = "ID")
    private long id;

    @Column(name = "NAME")
    private String name;

    @Column(name = "AGE")
    private int age;

    @Column(name = "EMAIL")
    private String email;

    @Column(name = "DELETED")
    private boolean deleted;

    // getters and setters

}

查詢IS NULL條件也可將參數null。

不過複雜的語法例如JOIN資料表或子查詢等還是Native SQL比較方便。




沒有留言:

張貼留言