Help us understand the problem. What is going on with this article?

【Spring Data JPA】自動実装されるメソッドの命名ルール

Spring Data JPAには、Repositoryインターフェースに宣言されたメソッドを、その名前からクエリを生成して自動的に生成してくれるお便利機能があります。どんな命名規則があるのか分からなかったのでメモ。

基本的にはマニュアルの要約です。

環境

  • Java ・・・ jdk1.8
  • Spring Boot ・・・ 1.3.5-RELEASE
  • DB ・・・ MySQL 5.7

構文

以下の3つの要素を規則に従って組み合わせたメソッド名をRepositoryインターフェースに宣言することで、自動実装が利用可能になります。

  • プレフィックス(find...By read...By query...By count...By get...By
  • キーワード
  • フィールド名

以下のエンティティを例に使います。

Employee.java
@Entity
public class Employee implements Serializable {

    private static final long serialVersionUID = 3453583737318640866L;
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    private String firstname;
    private String lastname;
    private int age;
    @Temporal(TemporalType.DATE)
    private Date hiredAt;
    private Boolean active;
    @ManyToOne
    @JoinTable(
            name="department_employee",
            joinColumns=@JoinColumn(name="employee_id"),
            inverseJoinColumns=@JoinColumn(name="department_code")
        )
    private Department department;

    //以下Getter/Setter...
Department.java
@Entity
public class Department implements Serializable {

    private static final long serialVersionUID = -6771704436232452390L;
    @Id
    private String code;
    private String name;
    private boolean flg;
    @OneToMany(fetch=FetchType.LAZY, mappedBy="department")
    private transient List<Employee> employeeList;

    //以下Getter/Setter...

使用可能キーワード

キーワード サンプル JPQL表現
And findByLastnameAndFirstname … where e.lastname = ?1 and e.firstname = ?2
Or findByLastnameOrFirstname … where e.lastname = ?1 or e.firstname = ?2
Is,Equals findByFirstname,findByFirstnameIs,findByFirstnameEquals … where e.firstname = ?1
Between findByHiredAtBetween … where e.hiredAt between ?1 and ?2
LessThan findByAgeLessThan … where e.age < ?1
LessThanEqual findByAgeLessThanEqual … where e.age <= ?1
GreaterThan findByAgeGreaterThan … where e.age > ?1
GreaterThanEqual findByAgeGreaterThanEqual … where e.age >= ?1
After findByHiredAtAfter … where e.hiredAt > ?1
Before findByStartDateBefore … where e.hiredAt < ?1
IsNull findByAgeIsNull … where x.age is null
IsNotNull,NotNull findByAge(Is)NotNull … where e.age not null
Like findByFirstnameLike … where e.firstname like ?1
NotLike findByFirstnameNotLike … where e.firstname not like ?1
StartingWith findByFirstnameStartingWith … where e.firstname like ?1
EndingWith findByFirstnameEndingWith … where e.firstname like ?1
Containing findByFirstnameContaining … where e.firstname like ?1
OrderBy findByAgeOrderByLastnameDesc … where e.age = ?1 order by e.lastname desc
Not findByLastnameNot … where e.lastname <> ?1
In findByAgeIn(Collection ages) … where e.age in ?1
NotIn findByAgeNotIn(Collection age) … where e.age not in ?1
True findByActiveTrue() … where e.active = true
False findByActiveFalse() … where e.active = false
IgnoreCase findByFirstnameIgnoreCase … where UPPER(e.firstame) = UPPER(?1)

サンプル

Is / Equals / Not

EmployeeRepository.java
    // SELECT e FROM Employee e
    Employee findById(Long id);

    // SELECT e FROM Employee e WHERE e.firstname = ?1 
    List<Employee> findByFirstnameEquals(String firstname);

    // SELECT e FROM Employee e WHERE e.age = ?1
    List<Employee> findByAgeIs(int age);

    // SELECT e FROM Employee e WHERE e.lastname != ?1
    List<Employee> findByLastnameNot(String lastname);

    // SELECT e FROM Employee e WHERE e.department = ?1
    List<Employee> findByDepartment(Department department);

フィールドのあとにEquals/Isをつけると完全一致、Notをつけると不一致のレコードを探索します。
大文字小文字が区別されるかは、データベースの照合順序などに依存します。

LessThan / GreaterThan

使用するキーワードはLessThan GreaterThanです。

EmployeeRepository.java
    // SELECT e FROM Employee e WHERE e.age < ?1
    List<Employee> findByAgeLessThan(int age);

    // SELECT e FROM Employee e WHERE e.age > ?1
    List<Employee> findByAgeGreaterThan(String firstname);

    // SELECT e FROM Employee e WHERE e.hiredAt > ?1
    List<Employee> findByHiredAtGreaterThan(Date date);

境界値を含む場合は上述のキーワードに「Equal」を足して GreaterThanEqual LessThanEqualとします。
また、 Between キーワードで境界値を含んだ範囲検索が可能です。

EmployeeRepository.java
    // SELECT e FROM Employee WHERE e.age <= ?1
    List<Employee> findByAgeLessThanEqual(int age);

    // SELECT e FROM Employee WHERE e.age >= ?1
    List<Employee> findByAgeGreaterThanEqual(int age);

    // SELECT e FROM Employee WHERE e.hiredAt BETWEEN ?1 AND ?2
    List<Employee> findByHiredAtBetween(Date since, Date until);

ちなみに、findByFirstnameLessThan(String name) のように、String型のフィールドにLessThan, GreaterThanをつけることも可能です。結果はDBMS依存になると思いますが。。

Like / NotLike / StartingWith / EndingWith / Containing

ワイルドカードを使用した部分一致検索は、Like NotLike StartingWith EndingWith Containing を使用します。

EmployeeRepository.java
    // SELECT e FROM Employee WHERE e.firstname LIKE ?1
    List<Employee> findByFirstnameLike(int age);

    // SELECT e FROM Employee WHERE e.firstname NOT LIKE ?1
    List<Employee> findByFirstnameNotLike(String firstname);

    // SELECT e FROM Employee WHERE e.lastname LIKE ?1 (前方一致)
    List<Employee> findByLastnameStartingWith(String lastname);

    // SELECT e FROM Employee WHERE e.lastname LIKE ?1 (後方一致)
    List<Employee> findByLastnameEndingWith(String lastname);

    // SELECT e FROM Employee WHERE e.lastname LIKE ?1 (部分一致)
    List<Employee> findByLastnameContaining(String lastname);

このうち、StartingWith EndingWith Containing については、それぞれ順番に、前方一致、後方一致、部分一致となります。
任意の位置にワイルドカードを挿入した場合はLike NotLike を使用してください。

Between

EmployeeRepository.java
    // SELECT e FROM Employee e WHERE e.age BETWEEN ?1 AND ?2
    List<Employee> findByAgeBetween(int olderThan, int youngerThan);

    // SELECT e FROM Employee e WHERE e.hiredAt BETWEEN ?1 AND ?2
    List<Employee> findByHiredAtBetween(Date since, Date until);

IsNull / (Is)NotNull

EmployeeRepository.java
    // SELECT e FROM Employee WHERE e.hiredAt IS NULL
    List<Employee> findByHiredAtIsNull();

    // SELECT e FROM Employee WHERE e.hiredAt IS NOT NULL
    List<Employee> findByHiredAtIsNotNull();

    // SELECT e FROM Employee WHERE e.hiredAt IS NOT NULL
    List<Employee> findByHiredAtNotNull();

True / False

EmployeeRepository.java
    // SELECT e FROM Employee WHERE e.active = true
    List<Employee> findByActiveTrue();

    // SELECT e FROM Employee WHERE e.active = false
    List<Employee> findByActiveFalse();

IN

複数の候補値で検索する場合はSQLと同じくInを使います。

EmployeeRepository.java
    // SELECT e FROM Employee WHERE e.lastname in ?1
    List<Employee> findByLastnameIn(List<String> lastname);

After / Before

日付の比較にはLessThan(Equal) GreaterThan(Equal)が使えますが、After Beforeでも表現可能です。

EmployeeRepository.java
    // SELECT e FROM Employee WHERE e.lastname > ?1
    List<Employee> findByHiredAtAfter(Date date);

    // SELECT e FROM Employee WHERE e.lastname < ?1
    List<Employee> findByHiredAtBefore(Date date);

OrderBy

抽出結果をソートしたい場合はOrderByを使用します。フィールド名はOrderByのうしろに置きます。昇順降順はさらにフィールド名のうしろに置くので、OrderBy+フィールド名+Asc(Desc)となります。
ただし、By句で条件の指定がないとOrderByは使えないようです。(マニュアルに書いてないので、単に自分が知らないだけかもしれません)

EmployeeRepository.java
    // SELECT e FROM Employee e WHERE e.lastname = ?1 ORDER BY e.age
    List<Employee> findByLastnameOrderByAge(String lastname);

    // SELECT e FROM Employee e WHERE e.firstname = ?1 ORDER BY e.firstname ASC
    List<Employee> findByFirstnameOrderByHiredAtAsc(String firstname);

複数のフィールドでソートする場合は、Asc Descを指定して、下記のようにつなげればOKです。

EmployeeRepository.java
    // SELECT e FROM Employee e WHERE e.lastname = ?1 ORDER BY e.age ASC, e.firstname DESC
    List<Employee> findByLastnameOrderByAgeAscFirstnameDesc(String lastname);

Top / First

findの直後にFirstTopをつけることで件数を絞ることができます。

EmployeeRepository.java
    Employee findFirstByLastname(String lastname);

    Employee findTopByLastname(String lastname);

    List<Employee> findFirst3ByActiveTrueOrderByAgeDesc();

    List<Employee> findTop5ByHiredAtIs(Date date);

キーワードを組み合わせる

And Orで条件の組み合わせができます。

EmployeeRepository.java
    // SELECT e FROM Employee e WHERE e.age = ?1, e.active = true
    List<Employee> findByAgeIsAndActiveTrue(int age);

    // SELECT e FROM Employee e WHERE e.lastname LIKE ?1 OR e.lastname LIKE ?2
    List<Employee> findByLastnameStartingWithOrFirstnameEndingWith(String lastname, String firstname);

    // SELECT e FROM Employee e WHERE e.age BETWEEN ?1 AND ?2 AND e.department = ?3
    List<Employee> findByAgeBetweenAndDepartmentIs(int startAge, int endAge, Department department);

他にも・・・

Distinct read...By query...By count...By get...By とか色々あるみたいですが、力尽きたので気が向いたら書き足します。。

参考

Spring Data JPA - Reference Documentation
Spring Data JPA でのクエリー実装方法まとめ

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした