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比較方便。
沒有留言:
張貼留言