網頁

▼

2021/3/17

Spring Data JPA 方法名稱查詢排序複合主鍵參數 query method findby orderby @EmbeddedId parameters

Spring Data JPA Query Method可以透過定義方法名稱來作為查詢的條件。當查詢或排序條件為@EmbeddedId複合主鍵(compound key)中的屬性時的命名方式如下。

例如EMPLOYEE資料表的主鍵為由本身的EMPLOYEE_ID及外鍵REGION_ID，DEPARTMENT_ID組成，則JPA的entiy類Employee設計如下。

Employee

@Entity
@Table(name="EMPLOYEE")
public class Employee {
    
    @EmbeddedId
    private EmployeePk id;
    
    private String name;
    private int age;
    
    // getter and setter ommitted 
}

EMPLOYEE的複合主鍵使用@EmbeddedId及一個@Embeddable類別EmployeePk來設定。

@Embeddable
public class EmployeePk implements Serializable {
    
    private long employeeId;
    private long departmentId;
    private long regionId;
    
    // getter and setter ommitted
}

在EmployeeRepository定義方法名稱時若查詢或排序條件包含了EmployeePk中的欄位，則命名方式如下。

public interface EmployeeRepository extends JpaRepository<Employee, EmployeePk> {

    /**
     * from 
     *     EMPLOYEE
     * where
     *     EMPLOYEE_ID = ?
     *     and DEPARTMENT_ID = ?
     *     and REGION_ID = ?
     */
    List<Employee> findByIdAndAge(EmployeePk id, int age);

    /**
     * from 
     *     EMPLOYEE
     * where
     *     EMPLOYEE_ID = ?
     *     and NAME = ?
     * order by
     *     DEPARTMENT_ID asc
     */
    List<Employee> findByIdEmployeeIdAndNameOrderByIdDepartmentId(long employeeId, String name);

    /**
     * from 
     *     EMPLOYEE
     * where
     *     REGION_ID = ?
     *     AGE = ?
     * order by
     *     REGION_ID asc,
     *     DEPARTMENT_ID asc,
     *     AGE desc
     */
    List<Employee> findByIdRegionIdAndAgeOrderByIdRegionIdAscIdDepartmentIdAscAgeDesc(long regionId, int age);
}


沒有留言:

張貼留言