少し複雑な関連のデモ（JPA 2.1のおさらいメモの補足）

# 概要

この記事は[Java Persistence API 2.1のおさらいメモ] (https://qiita.com/rubytomato@github/items/6781e91bcf7ab8a68c40)の補足です。
少し複雑なモデルを使って最適な(N+1問題を回避する)検索処理の実装方法を確認します。

最適化の目標は下記の3点です。

* 関連のFetchTypeは可能な限りLAZYとする。
* open-in-viewは無効(false)にする。
* 発行されるSQLの数を抑える。

**環境**

* Windows 10 Professional
* Java 9.0.4
* Spring Boot 2.0.0
* Spring Data Jpa 2.0.5
    * Hibernate 5.2.14
* MySQL 5.7.19

# デモ環境

## モデル関連図

```text
  (parent) <----------------> (child) 
                              (parent) <---------------> (child)
 +--------+                  +--------+                 +--------------+                  +---------+
 |        |---[OneToMany]--->|        |---[OneToOne]--->|              +---[ManyToOne]--->|         |
 | Person |   (Cascade)      | Phone  |   (Cascade)     | PhoneDetails |                  | Provider|
 |        |                  |        |                 |              |                  |         |
 |        |<---[ManyToOne]---+        |<---[OneToOne]---+              |                  |         |
 +--------+                  +--------+                 +--------------+                  +---------+

```

## Entityクラス

* 記載するDDLはHibernateがEntityクラスから生成したものです。

### Person

```java:Person
@Entity(name = "Person")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @OneToMany(mappedBy = "person", cascade = CascadeType.ALL, orphanRemoval = true,
        fetch = FetchType.LAZY) // default LAZY
    //@OnDelete(action = OnDeleteAction.CASCADE)
    @JsonManagedReference("Person")
    private List<Phone> phones = new ArrayList<>();

    public void addPhone(Phone phone) {
        phones.add(phone);
        phone.setPerson(this);
    }

    public void removePhone(Phone phone) {
        phones.remove(phone);
        phone.setPerson(null);
    }

}
```

```sql:person
create table person (
    id bigint not null auto_increment
  , name varchar(255) not null
  , primary key (id)
) engine=InnoDB
```

### Phone

```java:Phone
@Entity(name = "Phone")
public class Phone {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "`number`", nullable = false)
    private String number;

    @ManyToOne(fetch = FetchType.LAZY) // default EAGER
    @JoinColumn(name = "person_id", nullable = false)
    @JsonBackReference("Person")
    private Person person;

    @OneToOne(mappedBy = "phone", cascade = CascadeType.ALL, orphanRemoval = true,
        fetch = FetchType.LAZY) // default EAGER
    @JsonManagedReference("Phone")
    private PhoneDetails details;

    public void addDetails(PhoneDetails details) {
        this.details = details;
        details.setPhone(this);
    }

    public void removeDetails() {
        if (this.details != null) {
            this.details.setPhone(null);
            this.details = null;
        }
    }

    public static Phone of(String number) {
        Phone phone = new Phone();
        phone.setNumber(number);
        return phone;
    }

    public static Phone of(String number, PhoneDetails details) {
        Phone phone = new Phone();
        phone.setNumber(number);
        phone.setDetails(details);
        details.setPhone(phone);
        return phone;
    }

}
```

```sql:phone
create table phone (
    id bigint not null auto_increment
  , `number` varchar(255) not null
  , person_id bigint not null
  , primary key (id)
) engine=InnoDB
```

### PhoneDetails

```java
@Entity(name = "PhoneDetails")
public class PhoneDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToOne(fetch = FetchType.LAZY) //default EAGER
    @JoinColumn(name = "phone_id", nullable = false)
    //@OnDelete(action= OnDeleteAction.CASCADE)
    @JsonBackReference("Phone")
    private Phone phone;

    @ManyToOne(fetch = FetchType.LAZY) //default EAGER
    @JoinColumn(name = "provider_id", nullable = false)
    private Provider provider;

    private String technology;

    public static PhoneDetails of(String technology, Provider provider) {
        PhoneDetails details = new PhoneDetails();
        details.setTechnology(technology);
        details.setProvider(provider);
        return details;
    }
}
```

```sql:phone_details
create table phone_details (
    id bigint not null auto_increment
  , technology varchar(255)
  , phone_id bigint not null
  , provider_id bigint not null
  , primary key (id)
) engine=InnoDB
```

### Provider

```java
@Entity(name = "Provider")
public class Provider {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    public static Provider of(String name) {
        Provider provider = new Provider();
        provider.setName(name);
        return provider;
    }
}
```

```sql:provider
create table provider (
    id bigint not null auto_increment
  , name varchar(255)
  , primary key (id)
) engine=InnoDB
```

### 外部キー制約

```sql
alter table phone add constraint FKkk6uij3j6wikpnqlj9dymobs9
  foreign key (person_id)
  references person (id)

alter table phone_details add constraint FK4vs8ep213cifmt8opragp1gkw
  foreign key (phone_id)
  references phone (id)

alter table phone_details add constraint FKrco5hsjio6faa9f4s7wc1jd3c
  foreign key (provider_id)
  references provider (id)
```

### デモデータ

```sql
select * from provider;
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Verizon Wireless |
|  2 | T-Mobile         |
|  3 | AT&T             |
+----+------------------+
3 rows in set (0.00 sec)
```

```sql
select * from person;
+----+--------+
| id | name   |
+----+--------+
|  1 | robb   |
|  2 | bran   |
|  3 | jon    |
|  4 | rickon |
+----+--------+
4 rows in set (0.00 sec)
```

```sql
select person.id as person_id
       , person.name
       , phone.id as phone_id
       , phone.number
       , phone_details.technology
       , provider.name as provider_name
    from person left outer join phone on person.id = phone.person_id
                left outer join phone_details on phone.id = phone_details.phone_id
                left outer join provider on provider.id = phone_details.provider_id
   order by
         person.id
       , phone.id;
+-----------+--------+----------+----------------+------------+------------------+
| person_id | name   | phone_id | number         | technology | provider_name    |
+-----------+--------+----------+----------------+------------+------------------+
|         1 | robb   |        1 | p1-123-456-789 | GSM        | Verizon Wireless |
|         1 | robb   |        2 | p1-456-789-123 | NULL       | NULL             |
|         1 | robb   |        3 | p1-789-123-456 | GSM        | T-Mobile         |
|         2 | bran   |        4 | p2-123-456-789 | GSM        | AT&T             |
|         3 | jon    |        5 | p3-123-456-789 | GSM        | Verizon Wireless |
|         3 | jon    |        6 | p3-456-789-123 | NULL       | NULL             |
|         4 | rickon |        7 | p4-123-456-789 | GSM        | Verizon Wireless |
+-----------+--------+----------+----------------+------------+------------------+
7 rows in set (0.00 sec)
```

# 検索処理

検索したエンティティのデータをjsonへ変換してクライアントへ返すというRest APIを想定しています。
実装する検索処理は、personのidを指定して1件取得する処理と、personを全件取得する処理の2つです。

**1件検索時のjsonのイメージ**

person idを指定した検索結果のjsonです。全件検索はこれが配列になります。

```json
{
  "id": 1,
  "name": "robb",
  "phones": [
    {
      "id": 1,
      "number": "p1-123-456-789",
      "details": {
        "id": 1,
        "provider": {
          "id": 1,
          "name": "Verizon Wireless"
        },
        "technology": "GSM"
      }
    },
    {
      "id": 2,
      "number": "p1-456-789-123",
      "details": null
    },
    {
      "id": 3,
      "number": "p1-789-123-456",
      "details": {
        "id": 2,
        "provider": {
          "id": 2,
          "name": "T-Mobile"
        },
        "technology": "GSM"
      }
    }
  ]
}
```

## シンプルな検索

比較対象としてjoin fetchやEntityGraphを使わない方法で検索処理を実装します。
ただし、関連のFetchTypeがLAZYでopen-in-viewがfalseだとjson変換時に下記のエラーが発生してしまうので、この例ではopen-in-viewをtrueにします。

```text
Failed to write HTTP message: org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: failed to lazily initialize a collection of role: com.example.demofeaturelayer.ext.entity.Person.phones, could not initialize proxy - no Session; nested exception is com.fasterxml.jackson.databind.JsonMappingException: failed to lazily initialize a collection of role: com.example.demofeaturelayer.ext.entity.Person.phones, could not initialize proxy - no Session (through reference chain: com.example.demofeaturelayer.ext.entity.Person["phones"])
```

### 1件検索する処理

```java
String jpql = "SELECT p FROM Person p WHERE p.id = :id";
Person person = entityManager.createQuery(jpql, Person.class)
                    .setParameter("id", id)
                    .getSingleResult();
```

**発行されるSQL**

関連するエンティティにアクセスするたびにselect文が発行されてしまう非効率な検索処理です。

```sql
select person0_.id as id1_6_, person0_.name as name2_6_
  from person person0_
 where person0_.id=1

/* 以降はトランザクション外で発行される */
select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
  from phone phones0_
 where phones0_.person_id=1

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
 where phonedetai0_.phone_id=1

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
 where phonedetai0_.phone_id=2

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
 where phonedetai0_.phone_id=3

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=1

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=2
```

### 全件検索する処理

```java
String jpql = "SELECT p FROM Person p";
List<Person> personList = entityManager.createQuery(jpql, Person.class)
                    .getResultList();
```

**発行されるSQL**

```sql
select person0_.id as id1_6_, person0_.name as name2_6_
  from person person0_

/* 以降はトランザクション外で発行される */
select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
  from phone phones0_
 where phones0_.person_id=1

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
  where phonedetai0_.phone_id=1

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
 where phonedetai0_.phone_id=2

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
  where phonedetai0_.phone_id=3

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=1

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=2

select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
  from phone phones0_
 where phones0_.person_id=2

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
 where phonedetai0_.phone_id=4

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=3

select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
  from phone phones0_
 where phones0_.person_id=3

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
 where phonedetai0_.phone_id=5

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
 where phonedetai0_.phone_id=6

select phones0_.person_id as person_i3_7_0_, phones0_.id as id1_7_0_, phones0_.id as id1_7_1_, phones0_.`number` as number2_7_1_, phones0_.person_id as person_i3_7_1_
  from phone phones0_
 where phones0_.person_id=4

select phonedetai0_.id as id1_8_0_, phonedetai0_.phone_id as phone_id3_8_0_, phonedetai0_.provider_id as provider4_8_0_, phonedetai0_.technology as technolo2_8_0_
  from phone_details phonedetai0_
 where phonedetai0_.phone_id=7
```


## JOIN FETCHを使用する

1つのSQLで必要なデータをすべて取得するので、関連をすべてLAZYにすることができます。

### 1件検索する処理

```java
String jpql = "SELECT p FROM Person p LEFT OUTER JOIN FETCH p.phones ph " +
                                    " LEFT OUTER JOIN FETCH ph.details dt " +
                                    " LEFT OUTER JOIN FETCH dt.provider " +
                     " WHERE p.id = :id";
Person person = entityManager.createQuery(jpql, Person.class)
                    .setParameter("id", id)
                    .getSingleResult();
```

**発行されるSQL**

```sql
select person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, provider3_.id as id1_9_3_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_, provider3_.name as name2_9_3_
  from person person0_
  left outer join phone phones1_ on person0_.id=phones1_.person_id
  left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
  left outer join provider provider3_ on phonedetai2_.provider_id=provider3_.id
 where person0_.id=1
```

### 全件検索する処理

```java
String jpql = "SELECT DISTINCT p FROM Person p LEFT OUTER JOIN FETCH p.phones ph " +
                                             " LEFT OUTER JOIN FETCH ph.details dt " +
                                             " LEFT OUTER JOIN FETCH dt.provider";
List<Person> personList = entityManager.createQuery(jpql, Person.class)
                    .getResultList();
```

**発行されるSQL**

```sql
select distinct person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, provider3_.id as id1_9_3_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_, provider3_.name as name2_9_3_
  from person person0_
  left outer join phone phones1_ on person0_.id=phones1_.person_id
  left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
  left outer join provider provider3_ on phonedetai2_.provider_id=provider3_.id
```

## JOIN FETCHを使用する (少し改良版)

データ件数の少ないマスター系のエンティティなどはJOIN FETCHせずに、敢えて別個にselectさせて1次キャッシュにのせておいたほうが検索処理のトータルのコストが下がる場合があります。
この例では、providerエンティティをjoin fetchから外して1次キャッシュからヒットさせるようにしています。

open-in-viewは無効にしたいので、providerが即時検索されるようにPhoneDetailsとProviderの関連をEAGERにします。

```java
@Entity(name = "PhoneDetails")
public class PhoneDetails {

    @ManyToOne(fetch = FetchType.EAGER) //default EAGER
    @JoinColumn(name = "provider_id", nullable = false)
    private Provider provider;

}
```

### 1件検索する処理

```java
String jpql = "SELECT p FROM Person p LEFT OUTER JOIN FETCH p.phones ph " +
                                    " LEFT OUTER JOIN FETCH ph.details dt " +
                     " WHERE p.id = :id";
Person person = entityManager.createQuery(jpql, Person.class)
                    .setParameter("id", id)
                    .getSingleResult();
```

**発行されるSQL**

1件検索のときは、別個に検索するメリットは薄いのでjoin fetchでもいいかもしれません。

```sql
select person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_
  from person person0_
  left outer join phone phones1_ on person0_.id=phones1_.person_id
  left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
 where person0_.id=1

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=1

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=2
```

### 全件検索する処理

```java
String jpql = "SELECT DISTINCT p FROM Person p LEFT OUTER JOIN FETCH p.phones ph " +
                                             " LEFT OUTER JOIN FETCH ph.details dt ";
List<Person> personList = entityManager.createQuery(jpql, Person.class)
                    .getResultList();
```

**発行されるSQL**

providerへのselectが3回発生していますが、1度キャッシュにのったデータは再びselectされないのでpersonのデータ件数が多いほど効果が期待できます。

```sql
select distinct person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_
  from person person0_
  left outer join phone phones1_ on person0_.id=phones1_.person_id
  left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=1

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=2

select provider0_.id as id1_9_0_, provider0_.name as name2_9_0_
  from provider provider0_
 where provider0_.id=3
```

## EntityGraphを使用する

EntityGraphをエンティティクラスに定義します。
NamedAttributeNodeアノテーションのvalueにFetchTypeをEAGERにしたいフィールドを指定していきます。

```java
@Entity(name = "Person")
@NamedEntityGraph(name = "Person.graph",
    attributeNodes = {
        @NamedAttributeNode(value = "phones", subgraph = "Phone.graph")
    },
    subgraphs = {
        @NamedSubgraph(name = "Phone.graph",
            attributeNodes = {
                @NamedAttributeNode(value = "details", subgraph = "PhoneDetails.graph")
            }
        ),
        @NamedSubgraph(name = "PhoneDetails.graph",
            attributeNodes = {
                @NamedAttributeNode(value = "provider")
            }
        )
    }
)
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @OneToMany(mappedBy = "person", cascade = CascadeType.ALL, orphanRemoval = true,
        fetch = FetchType.LAZY) // default LAZY
    @JsonManagedReference("Person")
    private List<Phone> phones = new ArrayList<>();

    public void addPhone(Phone phone) {
        phones.add(phone);
        phone.setPerson(this);
    }

    public void removePhone(Phone phone) {
        phones.remove(phone);
        phone.setPerson(null);
    }
}
```

EntityGraphではJPQLを記述する必要はなくリポジトリを使用することができます。

```java
public interface PersonRepository extends JpaRepository<Person, Long> {

    // 1件検索処理
    @EntityGraph(value = "Person.graph", type = EntityGraph.EntityGraphType.LOAD)
    @Override
    Optional<Person> findById(Long id);

    // 全件検索処理
    @EntityGraph(value = "Person.graph", type = EntityGraph.EntityGraphType.LOAD)
    @Override
    List<Person> findAll();

}
```

### 1件検索する処理

```java
Person person = personRepository.findById(id).orElseThrow(RuntimeException::new);
```

**発行されるSQL**

```sql
select person0_.id as id1_6_0_, person0_.name as name2_6_0_, phones1_.person_id as person_i3_7_1_, phones1_.id as id1_7_1_, phones1_.id as id1_7_2_, phones1_.`number` as number2_7_2_, phones1_.person_id as person_i3_7_2_, phonedetai2_.id as id1_8_3_, phonedetai2_.phone_id as phone_id3_8_3_, phonedetai2_.provider_id as provider4_8_3_, phonedetai2_.technology as technolo2_8_3_, provider3_.id as id1_9_4_, provider3_.name as name2_9_4_
  from person person0_
  left outer join phone phones1_ on person0_.id=phones1_.person_id
  left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
  left outer join provider provider3_ on phonedetai2_.provider_id=provider3_.id
 where person0_.id=1
```

### 全件検索する処理

```java
List<Person> personList = personRepository.findAll();
```

**発行されるSQL**

```sql
select person0_.id as id1_6_0_, phones1_.id as id1_7_1_, phonedetai2_.id as id1_8_2_, provider3_.id as id1_9_3_, person0_.name as name2_6_0_, phones1_.`number` as number2_7_1_, phones1_.person_id as person_i3_7_1_, phones1_.person_id as person_i3_7_0__, phones1_.id as id1_7_0__, phonedetai2_.phone_id as phone_id3_8_2_, phonedetai2_.provider_id as provider4_8_2_, phonedetai2_.technology as technolo2_8_2_, provider3_.name as name2_9_3_
  from person person0_
  left outer join phone phones1_ on person0_.id=phones1_.person_id
  left outer join phone_details phonedetai2_ on phones1_.id=phonedetai2_.phone_id
  left outer join provider provider3_ on phonedetai2_.provider_id=provider3_.id
```