【JPA徹底入門】JPQLによる検索処理
JPQL (Java Persistence Query Language) は JPA で利用できる SQL ライクなクエリ言語です。JPA を採用したプロジェクトでの検索処理は
- Spring Data JPA の Specification
- Query DSL
- Criteria API
などを利用することも多いですが、これらのライブラリ・API の動作を深く理解するには JPQL の理解が必須です。JPQL では Update / Delete を行うこともできますが、この記事では 利用頻度の高い検索処理に限って解説していきたいと思います。
前提
この記事で解説するコードとサンプルデータは GitHub に掲載してあります。
サンプルコード
- サンプルコードは、Spring Boot のテストコードとして Java11 で実装しています。パッケージは こちら
- JPA の実装は Hibernate を利用しています。 EclipeLink など他の JPA プロバイダーでは動作が異なることがあります。
- データベースは H2 を利用しています。Flyway で実行している DDL とサンプルデータ投入 SQL は こちら
テストデータ
spring-petclinic のデータを一部カスタマイズしたものを利用します。
単一エンティティに対するクエリ
まずは 1 テーブルで完結するシンプルなクエリを題材にし、JPQL の基本構文と EntityManager を利用した JPQL の実行方法について解説します。
この節で利用する PETS テーブルについて、データとエンティティを確認しましょう。
PETS テーブルと Pet エンティティ
| ID | NAME | TYPE_ID | OWNER_ID |
|---|---|---|---|
| 1 | Leo | 1 | 1 |
| 2 | Basil | 6 | 2 |
| 3 | Rosy | 2 | 3 |
| 4 | Jewel | 2 | 3 |
| 5 | Iggy | 3 | 4 |
| 6 | George | 4 | 5 |
| 7 | Samantha | 1 | 6 |
| 8 | Max | 1 | 6 |
| 9 | Lucky | 5 | 7 |
| 10 | Mulligan | 2 | 8 |
| 11 | Freddy | 5 | 9 |
| 12 | Lucky | 2 | 10 |
| 13 | Sly | 1 | 10 |
// getter と setter は Lombok で生成しています。
@Getter
@Setter
@Entity
@Table(name = "pets")
public class Pet {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "type_id")
private Type type;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "owner_id")
private Owner owner;
@Override
public String toString() {
return "Pet{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
JPQL の基本構文
「PETS テーブルからデータを全件取得し、結果を name の昇順でソートする」というクエリは以下のように実装できます。
String jpql = "SELECT p FROM Pet p ORDER BY p.name ASC"; // <1>
List<Pet> pets = em.createQuery(jpql, Pet.class).getResultList(); // <2>
for (Pet pet : pets) {
System.out.println("pet = " + pet);
}
- JPQL を文字列として定義します。基本構文は、
SELECT エイリアス FROM エンティティ名 エイリアス [WHERE ...] [ORDER BY ...]
という形式です。SELECT 句では取得対象とするプロパティを個別に指定することもできますが、まずは基本のこの形を覚えましょう。
-
EntityManagerのインスタンスemに対してcreateQuery(JPQL, エンティティのクラスオブジェクト)を実行してクエリ（javax.persistence.TypedQueryのインスタンス）を初期化します。getResultListメソッドを実行すると結果が List で返ってきます。
実際に実行された SQL が標準出力されているので確認してみましょう。
select
pet0_.id as id1_2_,
pet0_.name as name2_2_,
pet0_.owner_id as owner_id3_2_,
pet0_.type_id as type_id4_2_
from
pets pet0_
order by
pet0_.name ASC
pet = Pet{id=2, name='Basil'}
pet = Pet{id=11, name='Freddy'}
pet = Pet{id=6, name='George'}
...
元の JPQL との対応関係をもうちょっと分かりやすく書くとこんな感じでしょうか。
-- JPQL
SELECT p FROM Pet p ORDER BY p.name ASC
-- SQL
SELECT p.* FROM PETS p ORDER BY p.name ASC
- FROM 句について
- SQL ではテーブル名 (PETS) を指定
- JPQL ではエンティティ名 (Pet) を指定
- SELECT 句について
- SQL では
p.*(PETS テーブルの全項目) を指定 - JPQL では Pet エンティティのエイリアスを指定
- SQL では
という対応関係が成立していますね。
ORDER BY の形はこのサンプルでは同じですが、これはテーブルのカラム名とエンティティのプロパティ名（変数名）が一致しているためです。テーブルのカラム名とエンティティのプロパティ名が異なる場合、JPQL ではプロパティ名を指定する必要があるので注意しましょう。[1]
WHERE 句
次に WHERE 句ありのパターンを考えます。以下の例は「name が "L" で始まる Pet を全件取得する」クエリです。
String jpql = "SELECT p FROM Pet p WHERE p.name LIKE :name"; // <1>
List<Pet> pets = em.createQuery(jpql, Pet.class)
.setParameter("name", "L%") // <2>
.getResultList();
for (Pet pet : pets) {
System.out.println("pet = " + pet);
}
標準出力
select
pet0_.id as id1_2_,
pet0_.name as name2_2_,
pet0_.owner_id as owner_id3_2_,
pet0_.type_id as type_id4_2_
from
pets pet0_
where
pet0_.name like ?
binding parameter [1] as [VARCHAR] - [L%]
pet = Pet{id=1, name='Leo'}
pet = Pet{id=9, name='Lucky'}
pet = Pet{id=12, name='Lucky'}
-
:変数名という形式でプレースホルダを定義すると、後から値を代入することができます。 -
setParameterメソッドを利用してプレースホルダに実際の値を代入しています。
比較演算子は =, <>, <, >, <=, >=, LIKE, BETWEEN, IN など SQL でよく使うものをそのまま書くことができます。[2]
また、JPQL では以下の関数がサポートされています。[3]
- 文字列 CONCAT, SUBSTRING, UPPER, LOWER, TRIM, LENGTH, LOCATE
- 算術 ABS, MOD, SQRT
- 時刻 CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
大文字小文字を無視して検索を行うなら、JPQL を
SELECT p FROM Pet p WHERE LOWER(p.name) LIKE :name
として、バインディングを setParameter("name", "l%") と変更すれば OK です。
getSingleResult によるデータの取得
以下に示すのは、「name が "Leo" の Pet を取得する」というクエリです。結果をリストで受ける場合は getResultList メソッドを利用しましたが、結果をスカラーとして取得したい場合は getSingleResult メソッドを利用します。
String jpql = "SELECT p FROM Pet p WHERE p.name = :name";
Pet pet = em.createQuery(jpql, Pet.class)
.setParameter("name", "Leo")
.getSingleResult();
System.out.println("pet = " + pet);
標準出力
select
pet0_.id as id1_2_,
pet0_.name as name2_2_,
pet0_.owner_id as owner_id3_2_,
pet0_.type_id as type_id4_2_
from
pets pet0_
where
pet0_.name=?
binding parameter [1] as [VARCHAR] - [Leo]
pet = Pet{id=1, name='Leo'}
結果が複数件のクエリに対して getSingleResult を実行すると、NonUniqueResultException がスローされます。先頭の 1 件を取得したければ setMaxResults(1) とすれば OK です。
String jpql = "SELECT p FROM Pet p WHERE p.name = :name";
Pet pet = em.createQuery(jpql, Pet.class)
.setParameter("name", "Lucky")
.setMaxResults(1)
.getSingleResult();
System.out.println("pet = " + pet);
標準出力
select
pet0_.id as id1_2_,
pet0_.name as name2_2_,
pet0_.owner_id as owner_id3_2_,
pet0_.type_id as type_id4_2_
from
pets pet0_
where
pet0_.name=? limit ?
binding parameter [1] as [VARCHAR] - [Lucky]
pet = Pet{id=9, name='Lucky'}
標準出力を確認すると SQL に limit が追加されていることが分かります。ログには出ていませんが、limit には 1 が代入されて結果が 1 件になります。実際のアプリケーションでは ORDER BY を指定して、意図したレコードが取得されるようにコントロールするよう注意してください。
まとめ
- JPQL の基本構文は
SELECT エイリアス FROM エンティティ名 エイリアス [WHERE ...] [ORDER BY ...] - 結果が複数件の場合は
getResultListメソッドを利用する。 - 結果が 1 件の場合は
getSingleResultメソッドを利用する。 -
getSingleResultは結果が複数件の場合にNonUniqueResultExceptionをスローする。 -
getSingleResultは結果が存在しない場合にNoResultExceptionをスローする。
ManyToOne の関連を伴うクエリ
続いて、結合が発生するクエリについて見ていきましょう。まず、ORM (Object Relational Mapper) を利用する際に避けて通れない「N+1 問題」について説明し、続いて JPA における JOIN の 3 構文 (JOIN FETCH, JOIN, JOIN ON) を解説します。
まず、Pet の飼い主を表す OWNERS テーブルを導入します。
OWNERS テーブルと Owner エンティティ
| ID | FIRST_NAME | LAST_NAME | ADDRESS | CITY | TELEPHONE |
|---|---|---|---|---|---|
| 1 | George | Franklin | 110 W. Liberty St. | Madison | 6085551023 |
| 2 | Betty | Davis | 638 Cardinal Ave. | Sun Prairie | 6085551749 |
| 3 | Eduardo | Rodriquez | 2693 Commerce St. | McFarland | 6085558763 |
| 4 | Harold | Davis | 563 Friendly St. | Windsor | 6085553198 |
| 5 | Peter | McTavish | 2387 S. Fair Way | Madison | 6085552765 |
| 6 | Jean | Coleman | 105 N. Lake St. | Monona | 6085552654 |
| 7 | Jeff | Black | 1450 Oak Blvd. | Monona | 6085555387 |
| 8 | Maria | Escobito | 345 Maple St. | Madison | 6085557683 |
| 9 | David | Schroeder | 2749 Blackhawk Trail | Madison | 6085559435 |
| 10 | Carlos | Estaban | 2335 Independence La. | Waunakee | 6085555487 |
@Getter
@Setter
@Entity
@Table(name = "owners")
public class Owner {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
private String address;
private String city;
private String telephone;
@OneToMany(mappedBy = "owner", cascade = CascadeType.ALL, orphanRemoval = true)
private List<Pet> pets = new ArrayList<>();
@Override
public String toString() {
return "Owner{" +
"id=" + id +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
'}';
}
}
Owner 1 レコードに対して Pet が複数レコード紐づく @OneToMany のリレーションが成立しています（Pet 側から見ると @ManyToOne）。このテーブルを利用して、「Pet の情報 + Owner の情報を同時に取得する」、「Owner の情報を条件にして Pet を検索する」といったクエリを実装していきます。
N+1 問題
JPA に限らず、ORM を利用する際に避けて通れないのが「N+1 問題」です。まずはこの問題を例示するために、以下のコードを実行してみます。
List<Pet> pets = em.createQuery("SELECT p FROM Pet p", Pet.class) // <1>
.getResultList();
System.out.println("=== getResultList が実行された ===");
for (Pet pet : pets) {
System.out.println("pet = " + pet + ", owner = " + pet.getOwner()); // <2>
}
-
SELECT p FROM Pet pで Pet を全件取得し、 - for ループの中で
pet.getOwnerを実行して@ManyToOneの関連エンティティである Owner を取得しています。
どのような SQL が発行されるのか、標準出力を確認してみましょう。
select * from pets -- <1>
=== getResultList が実行された ===
select * from owners where owners.id = 1 -- <2>
pet = Pet{id=1, name='Leo'}, owner = Owner{id=1, firstName='George', lastName='Franklin'}
select * from owners where owners.id = 2
pet = Pet{id=2, name='Basil'}, owner = Owner{id=2, firstName='Betty', lastName='Davis'}
select * from owners where owners.id = 3
pet = Pet{id=3, name='Rosy'}, owner = Owner{id=3, firstName='Eduardo', lastName='Rodriquez'}
...
実際の標準出力は非常に長いので、SQL を簡略化して示しています（これ以降の例でも同様の簡略化を行います）。
-
select * from petsで Pet を全件取得した後、 -
pet.getOwnerの実行時にselect * from owners where owners.id = ?が都度実行され、Owner の情報が個別に取得されていることが分かります。
このように、関連エンティティへのアクセサ (この例では pet.getOwner) が実行された時に別途 SQL を発行してデータを取得することを Lazy フェッチ、Lazy ロードなどと呼びます。 Lazy フェッチは、JPA に限らず Rails の Active Record など様々な ORM で実装されている機構です。[4]
さて、元の例に戻ると
- Pet を取得する SQL を 1 回実行（結果を N 件とする）
- Owner を取得する SQL を N 回実行
と合計で N+1 回の SQL が発行されています。「N+1 問題」とは、このように関連エンティティの取得が意図せぬ Lazy フェッチで非効率に行われ、パフォーマンスに影響を及ぼすという問題です。
JOIN FETCH
N+1 問題を回避するには、Pet 取得に Owner を結合して 1 本の SQL で取得するというアプローチが考えられます。JPQL では JOIN FETCH 結合したいプロパティ名 という構文によりこれを実現可能です。SQL と違って結合条件 (ON) は指定不要です。[5]
String jpql = "SELECT p FROM Pet p INNER JOIN FETCH p.owner";
List<Pet> pets = em.createQuery(, Pet.class).getResultList();
for (Pet pet : pets) {
System.out.println("pet = " + pet + ", owner=" + pet.getOwner());
}
select
pet0_.id as id1_2_0_,
owner1_.id as id1_0_1_,
pet0_.name as name2_2_0_,
pet0_.owner_id as owner_id3_2_0_,
pet0_.type_id as type_id4_2_0_,
owner1_.address as address2_0_1_,
owner1_.city as city3_0_1_,
owner1_.first_name as first_na4_0_1_,
owner1_.last_name as last_nam5_0_1_,
owner1_.telephone as telephon6_0_1_
from
pets pet0_
inner join
owners owner1_
on pet0_.owner_id=owner1_.id
pet = Pet{id=1, name='Leo'}, owner=Owner{id=1, firstName='George', lastName='Franklin'}
pet = Pet{id=2, name='Basil'}, owner=Owner{id=2, firstName='Betty', lastName='Davis'}
pet = Pet{id=3, name='Rosy'}, owner=Owner{id=3, firstName='Eduardo', lastName='Rodriquez'}
...
1 つの SQL で Pet と Owner の項目がまとめて取得され、追加の SQL は発行されていません。今回の例は内部結合なので INNER JOIN FETCH としましたが、外部結合の場合は LEFT JOIN FETCH とします。
応用ケースとして、ネストした JOIN FETCH を考えます。Pet エンティティに対して @ManyToOne の関連を持つ Visit エンティティを定義します。
Visit エンティティ
@Getter
@Setter
@Entity
@Table(name = "visits")
public class Visit {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "pet_id")
private Pet pet;
@Column(name = "visit_date")
private LocalDate visitDate;
private String description;
@Override
public String toString() {
return "Visit{" +
"id=" + id +
", pet=" + pet +
", visitDate=" + visitDate +
", description='" + description + '\'' +
'}';
}
}
Visit エンティティを軸にして、
- Visit -> Pet を JOIN FETCH
- Pet -> Owner を JOIN FETCH
と JOIN FETCH をネストさせ、Visit, Pet, Owner の 3 エンティティを同時に取得します。このように ネストした JOIN FETCH は、中間エンティティである Pet に対して INNER JOIN FETCH v.pet p として p というエイリアスを与え、そのエイリアスを参照して INNER JOIN FETCH p.owner とすることによって表現することができます。
String jpql = "SELECT v FROM Visit v INNER JOIN FETCH v.pet p INNER JOIN FETCH p.owner";
List<Visit> visits = em.createQuery(jpql, Visit.class).getResultList();
for (Visit visit : visits) {
System.out.println(
"visit = " + visit + ", pet = " + visit.getPet() + ", owner = " + visit.getPet().getOwner());
}
標準出力（イメージ）
select
visit0_.*,
pet1_.*,
owner2_.*
from
visits visit0_
inner join
pets pet1_
on visit0_.pet_id=pet1_.id
inner join
owners owner2_
on pet1_.owner_id=owner2_.id
visit = Visit{id=1, pet=Pet{id=7, name='Samantha'}, visitDate=2013-01-01, description='rabies shot'}, pet = Pet{id=7, name='Samantha'}, owner = Owner{id=6, firstName='Jean', lastName='Coleman'}
visit = Visit{id=2, pet=Pet{id=8, name='Max'}, visitDate=2013-01-02, description='rabies shot'}, pet = Pet{id=8, name='Max'}, owner = Owner{id=6, firstName='Jean', lastName='Coleman'}
visit = Visit{id=3, pet=Pet{id=8, name='Max'}, visitDate=2013-01-03, description='neutered'}, pet = Pet{id=8, name='Max'}, owner = Owner{id=6, firstName='Jean', lastName='Coleman'}
visit = Visit{id=4, pet=Pet{id=7, name='Samantha'}, visitDate=2013-01-04, description='spayed'}, pet = Pet{id=7, name='Samantha'}, owner = Owner{id=6, firstName='Jean', lastName='Coleman'}
JOIN
JPQL には JOIN FETCH とよく似た JOIN という構文もあります。JOIN FETCH の節で説明した SELECT p FROM Pet p INNER JOIN FETCH p.owner という JPQL から、FETCH キーワードを除外したものを実行してみましょう。
String jpql = "SELECT p FROM Pet p INNER JOIN p.owner";
List<Pet> pets = em.createQuery(jpql), Pet.class).getResultList();
for (Pet pet : pets) {
System.out.println("pet = " + pet + ", owner=" + pet.getOwner());
}
select
pet0_.id as id1_2_,
pet0_.name as name2_2_,
pet0_.owner_id as owner_id3_2_,
pet0_.type_id as type_id4_2_
from
pets pet0_
inner join
owners owner1_
on pet0_.owner_id=owner1_.id
select * from owners where owners.id = 1
pet = Pet{id=1, name='Leo'}, owner = Owner{id=1, firstName='George', lastName='Franklin'}
select * from owners where owners.id = 2
pet = Pet{id=2, name='Basil'}, owner = Owner{id=2, firstName='Betty', lastName='Davis'}
select * from owners where owners.id = 3
pet = Pet{id=3, name='Rosy'}, owner = Owner{id=3, firstName='Eduardo', lastName='Rodriquez'}
OWNERS テーブルの結合は行われていますが、select 句に OWNERS テーブルの項目が含まれていません。そのため、このコードは pet.getOwner を実行したタイミングで LAZY フェッチが行われています。N+1 問題で例示したクエリと同じ挙動ですね。
JPQL の JOIN は、WHERE 句では利用するが、SELECT 句に含める必要がないエンティティを結合するという用途で利用するのが基本です。以下は「Owner の firstName が "George" である Pet を取得する。ただし、Owner の情報は不要」というクエリです。
String jpql = "SELECT p FROM Pet p INNER JOIN p.owner WHERE p.owner.firstName = :firstName";
List<Pet> pets = em.createQuery(jpql, Pet.class)
.setParameter("firstName", "George")
.getResultList();
for (Pet pet : pets) {
System.out.println("pet = " + pet);
}
標準出力
select
pet0_.id as id1_2_,
pet0_.name as name2_2_,
pet0_.owner_id as owner_id3_2_,
pet0_.type_id as type_id4_2_
from
pets pet0_
inner join
owners owner1_
on pet0_.owner_id=owner1_.id
where
owner1_.first_name=?
binding parameter [1] as [VARCHAR] - [George]
pet = Pet{id=1, name='Leo'}
SQL と似ているのでうっかり区別をせずにこちらを使ってしまう人も多いのですが、JOIN FETCH と比べると利用シーンは少ないので注意しましょう。
JOIN ON
続いて紹介するのが JOIN ON です。JOIN FETCH や JOIN と異なり、JOIN ON は結合条件を指定するので、リレーションが定義されていないエンティティを結合することができます。
Pet エンティティから @ManyToOne アノテーションを削除し、単に外部キー (petId) をプロパティとして持つようにした PetNoRelation エンティティを作ります。
PetNoRelation エンティティ
@Getter
@Setter
@Entity
@Table(name = "pets")
public class PetNoRelation {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
@Column(name = "type_id")
private Integer typeId;
@Column(name = "owner_id")
private Integer ownerId;
@Override
public String toString() {
return "Pet{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
PetNoRelation エンティティに対して、Owner を結合する JPQL を実行してみましょう。JOIN エイリアス ON 結合条件 という構文を利用します。
String jpql = "SELECT p, o FROM PetNoRelation p INNER JOIN Owner o ON o.id = p.ownerId";
List<Tuple> tuples = em.createQuery(jpql, Tuple.class).getResultList();
for (Tuple tuple : tuples) {
System.out.println("pet = " + tuple.get(0, PetNoRelation.class) + ", owner = " + tuple.get(1, Owner.class));
}
標準出力
select
petnorelat0_.id as id1_2_0_,
owner1_.id as id1_0_1_,
petnorelat0_.name as name2_2_0_,
petnorelat0_.owner_id as owner_id3_2_0_,
petnorelat0_.type_id as type_id4_2_0_,
owner1_.address as address2_0_1_,
owner1_.city as city3_0_1_,
owner1_.first_name as first_na4_0_1_,
owner1_.last_name as last_nam5_0_1_,
owner1_.telephone as telephon6_0_1_
from
pets petnorelat0_
inner join
owners owner1_
on (
owner1_.id=petnorelat0_.owner_id
)
pet = Pet{id=1, name='Leo'}, owner = Owner{id=1, firstName='George', lastName='Franklin'}
pet = Pet{id=2, name='Basil'}, owner = Owner{id=2, firstName='Betty', lastName='Davis'}
pet = Pet{id=3, name='Rosy'}, owner = Owner{id=3, firstName='Eduardo', lastName='Rodriquez'}
...
ON o.id = p.ownerId のところが結合条件ですが、SQL と全く同じ表記ですね。SELECT 句で p, o と 2 つのエンティティを指定しているため、これまでの例とは異なり結果を javax.persistence.Tuple のリストとして取得しています。Tuple から値を取得するには、get(SELECT句上のインデックス, クラス) とします。Tuple は任意の形をした SELECT 句をもつクエリの結果をマッピングできる便利なインターフェースですが、値を取り出すのにインデックス（あるいはエイリアス）を指定するため、型安全性が失われてしまうというデメリットがあります。
まとめ
「JOIN 3 兄弟」をそれぞれ使い分けよう！
| 構文 | 用途 |
|---|---|
| JOIN FETCH | 関連エンティティを結合して一括で取得する（利用頻度が一番高い）。 |
| JOIN | WHERE 句のみで参照するエンティティを結合する。 |
| JOIN ON | 関連（@ManyToOne など）が定義されていないエンティティを結合する。 |
OneToMany / ManyToMany の関連を伴うクエリ
ToMany (@OneToMany / @ManyToMany) の関連を伴うクエリでは、新たな JOIN の構文が必要とされることはありませんが、JOIN によりレコード数が増幅する点が特徴です。また、関連エンティティを検索条件のみに利用したいのであれば、EXISTS や IN が有効です。
OneToMany に対する JOIN FETCH
@ManyToOne の例では、「Pet エンティティを駆動表にして Owner エンティティを結合するクエリ」を紹介しましたが、ここでは逆に「Owner エンティティを駆動表にして Pet エンティティを結合するクエリ」を紹介します。構文として特に真新しいものはないので、標準出力の最後に注目して下さい。
String jpql = "SELECT o FROM Owner o INNER JOIN FETCH o.pets";
List<Owner> owners = em.createQuery(jpql, Owner.class).getResultList();
for (Owner owner : owners) {
System.out.println("owner = " + owner + " pets = " + owner.getPets());
}
select
owner0_.id as id1_0_0_,
pets1_.id as id1_2_1_,
owner0_.address as address2_0_0_,
owner0_.city as city3_0_0_,
owner0_.first_name as first_na4_0_0_,
owner0_.last_name as last_nam5_0_0_,
owner0_.telephone as telephon6_0_0_,
pets1_.name as name2_2_1_,
pets1_.owner_id as owner_id3_2_1_,
pets1_.type_id as type_id4_2_1_,
pets1_.owner_id as owner_id3_2_0__,
pets1_.id as id1_2_0__
from
owners owner0_
inner join
pets pets1_
on owner0_.id=pets1_.owner_id
owner = Owner{id=1, firstName='George', lastName='Franklin'} pets = [Pet{id=1, name='Leo'}]
owner = Owner{id=2, firstName='Betty', lastName='Davis'} pets = [Pet{id=2, name='Basil'}]
-- id=3のOwnerが2件取得されている
owner = Owner{id=3, firstName='Eduardo', lastName='Rodriquez'} pets = [Pet{id=3, name='Rosy'}, Pet{id=4, name='Jewel'}]
owner = Owner{id=3, firstName='Eduardo', lastName='Rodriquez'} pets = [Pet{id=3, name='Rosy'}, Pet{id=4, name='Jewel'}]
...
Pet を 2 匹飼っている、id=3 の Owner が 2 レコード重複して取得されていますね。このように、OneToMany の関連にあるエンティティを JOIN すると、子側の多重度の分だけ親側のレコードが重複します。
実際のアプリケーションを開発する際は、このように重複が起こっていると不都合なケースが多いでしょう。SELECT 句に DISTINCT キーワードを付与するとこの重複を除外することができます。
String jpql = "SELECT DISTINCT o FROM Owner o INNER JOIN FETCH o.pets";
List<Owner> owners = em.createQuery(jpql, Owner.class).getResultList();
for (Owner owner : owners) {
System.out.println("owner = " + owner + " pets = " + owner.getPets());
}
select
distinct owner0_.id as id1_0_0_,
pets1_.id as id1_2_1_,
owner0_.address as address2_0_0_,
owner0_.city as city3_0_0_,
owner0_.first_name as first_na4_0_0_,
owner0_.last_name as last_nam5_0_0_,
owner0_.telephone as telephon6_0_0_,
pets1_.name as name2_2_1_,
pets1_.owner_id as owner_id3_2_1_,
pets1_.type_id as type_id4_2_1_,
pets1_.owner_id as owner_id3_2_0__,
pets1_.id as id1_2_0__
from
owners owner0_
inner join
pets pets1_
on owner0_.id=pets1_.owner_id
owner = Owner{id=1, firstName='George', lastName='Franklin'} pets = [Pet{id=1, name='Leo'}]
owner = Owner{id=2, firstName='Betty', lastName='Davis'} pets = [Pet{id=2, name='Basil'}]
owner = Owner{id=3, firstName='Eduardo', lastName='Rodriquez'} pets = [Pet{id=3, name='Rosy'}, Pet{id=4, name='Jewel'}]
owner = Owner{id=4, firstName='Harold', lastName='Davis'} pets = [Pet{id=5, name='Iggy'}]
...
id=3 の Owner が 1 件になりましたね。@OneToMany の JOIN には注意点が 2 つあります。
具体的には、以下のようなクエリです。
String jpql = "SELECT DISTINCT o FROM Owner o INNER JOIN FETCH o.pets";
List<Owner> owners = em.createQuery(jpql, Owner.class)
.setMaxResults(5) // Ownerを5件取得したい
.getResultList();
これは、結合によって関連エンティティの多重度だけ結果セットの数が増えるので、全件取得してみないと駆動表のレコード数が何件になるか分からないためです。このコードを実行すると、 WARN o.h.h.i.ast.QueryTranslatorImpl - HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! という警告ログが出力され、limit 指定なしの SQL を実行してデータを全件取得した後、メモリー上で先頭 5 件に絞り込みが行われます。結果が多いクエリで意図せずこの状況が発生すると、メモリー不足を引き起こす恐れがあります。
例えば、Owner エンティティに対して Family エンティティが @OneToMany で関連していて、
SELECT DISTINCT o FROM Owner o
INNER JOIN FETCH o.pets
INNER JOIN FETCH o.families
としたケースがこれに当たります。この JPQL はデカルト積を生むため、Hibernate では org.hibernate.loader.MultipleBagFetchException がスローされます。コレクションの型を List から Set に変更するとこの例外を回避することはできるのですが、以下の記事で回答されている通り、無理せず関連エンティティの取得処理を別クエリで行うのがオススメです。
OneToMany の関連エンティティを検索条件にする
@OneToMany の関連エンティティを検索条件にする場合、JOIN よりも EXISTS や IN を利用した方が効率的です。「Pet としてネコ（typeId=1）を飼っている Owner を取得する」クエリを、まずは EXISTS を使って実装してみます。
String jpql = "SELECT o FROM Owner o WHERE EXISTS (SELECT 1 FROM Pet p WHERE p.owner = o AND p.type.id = :typeId)";
List<Owner> owners = em.createQuery(jpql, Owner.class).setParameter("typeId", 1).getResultList();
for (Owner owner : owners) {
System.out.println("owner = " + owner);
}
標準出力
select
owner0_.id as id1_0_,
owner0_.address as address2_0_,
owner0_.city as city3_0_,
owner0_.first_name as first_na4_0_,
owner0_.last_name as last_nam5_0_,
owner0_.telephone as telephon6_0_
from
owners owner0_
where
exists (
select
1
from
pets pet1_
where
pet1_.owner_id=owner0_.id
and pet1_.type_id=?
)
binding parameter [1] as [INTEGER] - [1]
owner = Owner{id=1, firstName='George', lastName='Franklin'}
owner = Owner{id=6, firstName='Jean', lastName='Coleman'}
owner = Owner{id=10, firstName='Carlos', lastName='Estaban'}
WHERE p.owner = o のところが SQL と違ってちょっとクセがありますが、Owner の id をキーにして駆動表とサブクエリを結合しています（相関サブクエリ）。
IN を利用しても同じクエリを実装可能です。
String jpql = "SELECT o FROM Owner o WHERE o.id IN (SELECT p.owner.id FROM Pet p WHERE p.type.id = :typeId)";
List<Owner> owners = em.createQuery(jpql, Owner.class).setParameter("typeId", 1).getResultList();
for (Owner owner : owners) {
System.out.println("owner = " + owner);
}
標準出力
select
owner0_.id as id1_0_,
owner0_.address as address2_0_,
owner0_.city as city3_0_,
owner0_.first_name as first_na4_0_,
owner0_.last_name as last_nam5_0_,
owner0_.telephone as telephon6_0_
from
owners owner0_
where
owner0_.id in (
select
pet1_.owner_id
from
pets pet1_
where
pet1_.type_id=?
)
binding parameter [1] as [INTEGER] - [1]
owner = Owner{id=1, firstName='George', lastName='Franklin'}
owner = Owner{id=6, firstName='Jean', lastName='Coleman'}
owner = Owner{id=10, firstName='Carlos', lastName='Estaban'}
EXISTS と IN の使い分けですが、個人的には「関連エンティティの存在有無を条件にして絞り込みを行なっている」という意図がはっきりするので EXISTS の方がオススメです。
ManyToMany の関連エンティティを検索条件にする
最後に @ManyToMany について、関連エンティティを検索条件にする JPQL を見ていきましょう。
- ユーザー:ロールが N:N で定義されていて、「管理者」ロールを持つユーザーを検索する
- 記事:タグが N:N で定義されていて、「Java」タグを持つ記事を検索する
など、実際のアプリケーションで非常に利用シーンの多い検索処理です。PetClinic のサンプルデータでは、Vet (獣医) と Specialty (専門) が N:N で関連しています。
Vet エンティティと Specialty エンティティ
@Getter
@Setter
@Entity
@Table(name = "vets")
public class Vet {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@ManyToMany
@JoinTable(name = "vet_specialties",
joinColumns = @JoinColumn(name = "vet_id", referencedColumnName = "id"),
inverseJoinColumns = @JoinColumn(name = "specialty_id", referencedColumnName = "id")
)
private List<Specialty> specialities;
@Override
public String toString() {
return "Vet{" +
"id=" + id +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
'}';
}
}
@Getter
@Setter
@Entity
@Table(name = "specialties")
public class Specialty {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
@ManyToMany
@JoinTable(name = "vet_specialties",
joinColumns = @JoinColumn(name = "specialty_id", referencedColumnName = "id"),
inverseJoinColumns = @JoinColumn(name = "vet_id", referencedColumnName = "id")
)
private List<Vet> vets;
@Override
public String toString() {
return "Specialty{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
「id = 2 の Specialty (専門) を持つ Vet (獣医) を検索する」処理は EXISTS 句を利用して以下のように実装することができます。
String jpql = "SELECT v FROM Vet v WHERE EXISTS (SELECT 1 FROM Specialty s WHERE s MEMBER OF v.specialities AND s.id = :id)";
List<Vet> vets = em.createQuery(jpql, Vet.class).setParameter("id", 2).getResultList();
for (Vet vet : vets) {
System.out.println("vet = " + vet);
}
標準出力
select
vet0_.id as id1_6_,
vet0_.first_name as first_na2_6_,
vet0_.last_name as last_nam3_6_
from
vets vet0_
where
exists (
select
1
from
specialties specialty1_
where
(
specialty1_.id in (
select
specialiti2_.specialty_id
from
vet_specialties specialiti2_
where
vet0_.id=specialiti2_.vet_id
)
)
and specialty1_.id=?
)
binding parameter [1] as [INTEGER] - [2]
vet = Vet{id=3, firstName='Linda', lastName='Douglas'}
vet = Vet{id=4, firstName='Rafael', lastName='Ortega'}
WHERE s MEMBER OF v.specialities AND s.id = :id としている箇所が JPQL 特有の構文で、「条件に一致する @ManyToMany の関連エンティティが存在するか」という条件を表しています。
まとめ
- ToMany (
@OneToMany/@ManyToMany) の関連エンティティを JOIN すると子側のレコードの数だけ結果が増幅するので DISTINCT する。 - ただし、ToMany の JOIN は「
setParameterを指定した際の件数絞り込みがインメモリーで行われる」「複数回の JOIN が不可能」などの制約があるので使い過ぎに注意！ - ToMany の関連エンティティを検索条件とする場合、JOIN ではなく EXISTS または IN を利用しよう。
集計を伴うクエリ
最後に、集計を伴うクエリについて簡単に説明していきます。集計を行うと結果がエンティティで受け取れなくなってしまうので、集計は JPA が苦手とする操作の一つです。実際のプロジェクトでは Native Query (生の SQL) として実行することも多いです。
GROUP BY
これまで見てきた Owner エンティティについて、「Owner の情報に加え、その Owner が飼っている Pet の件数を同時に取得する」というクエリを実装してみます。
String jpql = "SELECT o, COUNT(o) FROM Owner o LEFT JOIN Pet p ON p.owner.id = o.id GROUP BY o";
List<Tuple> results = em.createQuery(jpql, Tuple.class).getResultList();
for (Tuple result : results) {
System.out.println("owner = " + result.get(0, Owner.class) + ", count = " + result.get(1, Long.class));
}
標準出力
select
owner0_.id as col_0_0_,
count(owner0_.id) as col_1_0_,
owner0_.id as id1_0_,
owner0_.address as address2_0_,
owner0_.city as city3_0_,
owner0_.first_name as first_na4_0_,
owner0_.last_name as last_nam5_0_,
owner0_.telephone as telephon6_0_
from
owners owner0_
left outer join
pets pet1_
on (
pet1_.owner_id=owner0_.id
)
group by
owner0_.id
owner = Owner{id=1, firstName='George', lastName='Franklin'}, count = 1
owner = Owner{id=2, firstName='Betty', lastName='Davis'}, count = 1
owner = Owner{id=3, firstName='Eduardo', lastName='Rodriquez'}, count = 2
...
GROUP BY o のところがちょっと気持ち悪いかもしれませんが、SQL を確認すると Owner の id（PK）でグルーピングが行われることが分かります。なお、JPA では集計関数として COUNT, MIN, MAX, SUM, AVG の 5 つがサポートされています。[6]
コンストラクタ式
先ほどの例では、結果を Tuple で受け取っていましたが、Tuple は値を取り出すのにインデックス（あるいはエイリアス）を指定するため、型安全性が失われてしまうというデメリットがあります。以下のように DTO (Data Transfer Object) を用意して、DTO に結果を詰め替えることを考えます。
OwnerAndCountDTO
public class OwnerAndCountDTO {
private final Owner owner;
private final Long count;
public OwnerAndCountDTO(Owner owner, Long count) {
this.owner = owner;
this.count = count;
}
public Owner getOwner() {
return owner;
}
public Long getCount() {
return count;
}
@Override
public String toString() {
return "OwnerAndCountDTO{" +
"owner=" + owner +
", count=" + count +
'}';
}
}
GROUP BY の例で SELECT 句で 取得している owner と count をフィールドとして持ち、その両方を受け取るコンストラクタを定義しているのがポイントです。この DTO へ値の詰め込むには、JPQL の SELECT 句で SELECT new DTO(...) として、定義したコンストラクタを呼び出します。このような書き方を、コンストラクタ式と呼びます。
String jpql = "SELECT new com.github.sooogle.jpademo.entitysub.OwnerAndCountDTO(o, COUNT(o)) " +
"FROM Owner o LEFT JOIN Pet p ON p.owner.id = o.id GROUP BY o";
List<OwnerAndCountDTO> dtos = em.createQuery(jpql, OwnerAndCountDTO.class).getResultList();
for (OwnerAndCountDTO dto : dtos) {
System.out.println("owner = " + dto.getOwner() + ", count = " + dto.getCount());
}
パッケージ名付きの完全修飾クラス名を指定する必要があるのでちょっと冗長ですね。
まとめ
- JPQL では、集計関数 COUNT, MIN, MAX, SUM, AVG を利用した集計が可能。
- 集計を行った場合の結果は、コンストラクタ式を利用して DTO に詰め替えるのがオススメ。
-
@Columnアノテーションを利用してマッピングを行なっている場合に注意が必要です。 ↩︎ -
詳しくは、Hibernate 公式ドキュメントの Relational comparisons を参照して下さい。 ↩︎
-
詳しくは、Hibernate 公式ドキュメントの JPQL standardized functions を参照して下さい。 ↩︎
-
Martin Fowler 著「Patterns of Enterprise Application Architecture」では Lazy Load パターンとして紹介されています。 ↩︎
-
エンティティの
@JoinColumnアノテーションで結合条件が指定されているためです。 ↩︎ -
詳しくは、 Hibernate 公式ドキュメントの Aggregate functions を参照して下さい。 ↩︎
Discussion