JavaEE使い方メモ（JPA その３ - JPQL）

[環境構築](http://qiita.com/opengl-8080/items/aeda0d8bad42af3113bd)
[JPA の基本的な話](http://qiita.com/opengl-8080/items/e4840aa3e33b42ae0d6b)
[マッピングの話](http://qiita.com/opengl-8080/items/265f9f66a65e966678cb)
[Criteria API の話](http://qiita.com/opengl-8080/items/6e3e03e4c90cd3d5e211)

[コード](https://github.com/opengl8080-javaee-samples/jpa)


# JPQL とは

Java Persistence Query Language の略。
JPA で使用できるクエリ言語。

SQL に似ているけど、クエリの対象はデータベースではなくエンティティ。
検索では、テーブル名ではなくエンティティ名を指定し、カラム名ではなくプロパティ名を指定する。
また、検索結果はテーブルのレコードではなく、エンティティやそのコレクションが取得される。

決して、「データベース製品間の差異を吸収した標準的な SQL」<ins>ではない</ins>。

「Java と JavaScript とは似てるようで全然別の言語」みたいな感じ。

エンティティの検索や CRUD が行える。
バージョンが上がるごとに、ちょっとずつできることが増えていっている。

# 基本

**エンティティモデル**

![javaee-jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/4e39e961-cd53-b736-79cb-e4dce97b31ea.jpeg)

**実装**

```java:Chen.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQuery;

@Entity
@NamedQuery(name = "Chen.findAll", query = "SELECT c FROM Chen c")
public class Chen {
    @Id
    private Long id;
    private String value;

    @Override
    public String toString() {
        return "Chen{" + "id=" + id + ", value=" + value + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/65aac82f-eaf2-6290-70ee-3f60ec85c23f.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/33d48345-b014-8391-f127-20ccaea9b43e.jpeg)


**動作確認**

```java:JpqlEjb.java
package sample.javaee.jpa.ejb;

import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import sample.javaee.jpa.entity.jpql.Chen;

@Stateless
public class JpqlEjb {
    
    @PersistenceContext(unitName = "SampleUnit")
    private EntityManager em;
    
    public void chen() {
        TypedQuery<Chen> query = this.em.createNamedQuery("Chen.findAll", Chen.class);
        List<Chen> list = query.getResultList();
        
        System.out.println(list);
    }
}
```

```text:GlassFishコンソール出力
情報:   [Chen{id=1, value=hoge}, Chen{id=2, value=fuga}, Chen{id=3, value=piyo}]
```

## 説明

### クエリを定義する

```java:Chen.java
@Entity
@NamedQuery(name = "Chen.findAll", query = "SELECT c FROM Chen c")
public class Chen {
```

- `@NamedQuery` でエンティティをアノテートして、クエリを定義する。
    - `name` 属性でクエリの名前を設定し、 `query` 属性にクエリを設定する。
    - これを **名前付きクエリ** と呼ぶ。
    - 名前付きクエリは静的なクエリを定義するときに使用する。

### クエリの構文

```sql:Chenエンティティのインスタンスを全て取得するクエリ
SELECT c FROM Chen c
```

```sql:構文
SELECT <取得するインスタンスやプロパティ> FROM <エンティティ名> [AS] <別名>
```

- JPQL の構文は、基本的には SQL と同じ形式になっている。
- 違うのは、 `Chen` が DB のテーブルではなく、エンティティを指しているという点。
- SQL でいうところの `*` （アスタリスク）は存在しない。
- 必ず、エンティティの別名を定義しないといけない。
    - 別名というより、通常の Java の実装のイメージで、変数を宣言していると考えたほうが分かりやすいかもしれない。
    - つまり、 `Chen c;` みたいな感じで、型と変数を宣言しているイメージ。
- JPQL では、部分ごとに大文字小文字の区別がされたりされなかったりする。
    - 区別されない。
        - 予約語。
        - エンティティの別名。
    - 区別される。
        - エンティティ名、プロパティ名。

### クエリを実行する

```java:JpqlEjb.java
    TypedQuery<Chen> query = this.em.createNamedQuery("Chen.findAll", Chen.class);
    List<Chen> list = query.getResultList();
```

- クエリを使用するおおまかな手順は以下のようになる。
    - `EntityManager` を使って `Query` インスタンスを取得する。
    - `Query` インターフェースに定義されたメソッドを使って、クエリを実行し、結果を取得する。
- 名前付きクエリを使用する場合は、 `EntityManager` の `createNamedQuery()` を使用する。
    - 引数に `@NamedQuery` の `name` で設定したクエリの名前を渡す。
    - 戻り値の型が決まっている場合は引数に `Class` オブジェクトを渡す。すると、 `Query` インターフェースを継承した型安全な `TypedQuery` のインスタンスが取得できる。
- クエリ結果が複数のオブジェクトを返す場合は、 `Query#getResultList()` を使用して `List` で結果を取得できる。

# クエリを外部ファイル（XML）で定義する

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/303f1f73-267c-e12c-ed6b-c12094f7e4c7.jpeg)

**実装**

```java:FujiwaranoMokou.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "fujiwarano_mokou")
public class FujiwaranoMokou {
    @Id
    private Long id;

    @Override
    public String toString() {
        return "FujiwaranoMokou{" + "id=" + id + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/db0ec9dc-8593-78fb-ee00-bdaf18215321.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/b83c1294-6292-9b3d-70af-a5cbc8e79852.jpeg)


## 基本

```xml:orm.xml
<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.1"
        xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm
                            http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd">
  <named-query name="FujiwaranoMokou.findAll">
    <query><![CDATA[
     SELECT f
       FROM FujiwaranoMokou f
    ]]></query>
  </named-query>
</entity-mappings>
```

```text:orm.xmlの配置場所
|-src/
:  |-conf/
:  |  |-persistence.xml
:  |  `-orm.xml
:  `-java/
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<FujiwaranoMokou> query = this.em.createNamedQuery("FujiwaranoMokou.findAll", FujiwaranoMokou.class);
    
    query.getResultList()
         .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   FujiwaranoMokou{id=1}
情報:   FujiwaranoMokou{id=2}
情報:   FujiwaranoMokou{id=3}
```

- マッピングを定義するためのファイルを作成すれば、そこに名前付きクエリを定義することができる。
    - マッピング用のファイルは、 `orm.xml` という名前で作成する。
    - `orm.xml` は、 `WEB-INF/classes/META-INF/` の下に配備されるように配置する。
    - `src/conf/` フォルダは、 GlassFish の設定で `WEB-INF/classes/META-INF/` の下に配備されるようにしている。

## 任意のファイルに定義する

```xml:my_orm.xml
<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.1"
        xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm
                            http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd">
  <named-query name="FujiwaranoMokou.findAllOrderByIdDesc">
    <query><![CDATA[
     SELECT f
       FROM FujiwaranoMokou f
   ORDER BY f.id DESC
    ]]></query>
  </named-query>
</entity-mappings>
```

```text:my_orm.xmlの配置場所
|-src/
:  |-conf/
:  |  |-persistence.xml
:  |  `-orm.xml
:  `-java/
:     |-my_orm.xml
:     :
```

```diff:persistence.xml
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence                                  http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="SampleUnit">
    <jta-data-source>jdbc/Local_MySQL_test</jta-data-source>
+   <mapping-file>my_orm.xml</mapping-file>
    <properties>
      
      <property name="eclipselink.logging.level" value="FINE" />
      <property name="eclipselink.logging.parameters" value="true"/>
    </properties>
  </persistence-unit>
</persistence>
```

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("FujiwaranoMokou.findAllOrderByIdDesc", FujiwaranoMokou.class);
    
    query.getResultList()
         .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   FujiwaranoMokou{id=3}
情報:   FujiwaranoMokou{id=2}
情報:   FujiwaranoMokou{id=1}
```

- 任意の名前のファイルに名前付きクエリを定義したい場合は、 `persistence.xml` にマッピングファイルの情報を記述する。
    - `<mapping-file>` タグを使用する。
    - `<mapping-file>` タグは複数設定可能。
- マッピングファイルは、クラスパスが通った場所に配置する。

# 動的にクエリを生成する

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/180e61be-e32d-d0ff-c994-68db0cbe8b4c.jpeg)

**実装**

```java:AkiShizuha.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="aki_shizuha")
public class AkiShizuha {
    @Id
    private Long id;
    private String value;

    @Override
    public String toString() {
        return "AkiShizuha{" + "id=" + id + ", value=" + value + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/cf7a0e6f-5c35-f63e-582e-172a21681195.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/df340547-808d-cdd7-cc65-09b8e870122d.jpeg)

**クエリ定義**

```java:JpqlEjb.java
    TypedQuery<AkiShizuha> query = this.em.createQuery("SELECT a FROM AkiShizuha a", AkiShizuha.class);
    
    query.getResultList()
         .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   AkiShizuha{id=1, value=hoge}
情報:   AkiShizuha{id=2, value=fuga}
情報:   AkiShizuha{id=3, value=piyo}
```

- `EntityManager#createQuery(String, Class)` メソッドで、クエリを動的に生成することができる。
- でも、動的にクエリを作りたいならクライテリア API を使ったほうがいいかもしれない（文字列をゴリゴリ連結させるので、読みにくくなりミスもしやすくなるので）。

# 検索結果を１件だけ取得する

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/73872dbd-56ce-1199-7f5f-b8278bd29069.jpeg)

**実装**

```java:LilyWhite.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "lily_white")
public class LilyWhite {
    @Id
    private Long id;
    private String value;

    @Override
    public String toString() {
        return "LilyWhite{" + "id=" + id + ", value=" + value + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/44e20393-5324-d4fa-93e6-c6ddd03b5e8b.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/0b5ee08d-cc7e-0645-c7e9-41459ee4e541.jpeg)


**クエリ定義**

```sql:LilyWhite.findSingle
SELECT l
  FROM LilyWhite l
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<LilyWhite> query = this.em.createNamedQuery("LilyWhite.findSingle", LilyWhite.class);
    LilyWhite lilyWhite = query.getSingleResult();
    
    System.out.println(lilyWhite);
```

```text:GlassFishコンソール出力
情報:   LilyWhite{id=1, value=hoge}
```

- 検索結果が１件だけの場合は、 `Query#getSingleResult()` を使用する。
- もしクエリを実行した結果、エンティティが複数件取得できた場合は `NonUniqueResultException` がスローされる。
- 逆に１件も取得できなかった場合は、 `NoResultException` がスローされる。

# エンティティのプロパティだけを取得する

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/b30df199-1f07-da68-259e-b457f8406f7a.jpeg)

**実装（エンティティ）**

```java:AliceMargatroid.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "alice_margatroid")
public class AliceMargatroid {
    @Id
    private Long id;
    private String name;
    private String value;

    @Override
    public String toString() {
        return "AliceMargatroid{" + "id=" + id + ", name=" + name + ", value=" + value + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/a69ca2f6-d237-07f6-d74e-5976033d4b65.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/5ec34459-1d71-84e4-c26e-05a71c19ecc0.jpeg)


**クエリ定義**

```sql:AliceMargatroid.getName
SELECT a.name
  FROM AliceMargatroid a
```

```sql:AliceMargatroid.getNameAndValue
SELECT a.name
      ,a.value
  FROM AliceMargatroid a
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<String> getNameQuery = this.em.createNamedQuery("AliceMargatroid.getName", String.class);
    List<String> names = getNameQuery.getResultList();
    
    System.out.println(names);
    
    
    Query getNameAndValueQuery = this.em.createNamedQuery("AliceMargatroid.getNameAndValue");
    List<Object[]> list = getNameAndValueQuery.getResultList();
    
    list.stream()
        .map(Arrays::toString)
        .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   [hoge, fuga, piyo]
情報:   [hoge, HOGE]
情報:   [fuga, FUGA]
情報:   [piyo, PIYO]
```

- JPQL 上でエンティティのプロパティを抽出したい場合は、ドット区切り（`.`）で記述する（`c.name, c.value`）。
- 複数のプロパティを選択した場合、 `Query` が返す結果は `Object` の配列になる。
- 複数の名前付きクエリを定義する場合は、 `@NamedQueries` を使う。

# エンティティの関連をたどる

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/cfeb4313-fdac-8dc0-d5b1-3ce58b6bdaba.jpeg)

**実装**

```java:LunasaPrismriver.java
package sample.javaee.jpa.entity.jpql;

import java.util.List;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "lunasa_prismriver")
public class LunasaPrismriver {
    @Id
    private Long id;
    
    @JoinColumn(name = "merlin_prismriver_id")
    private MerlinPrismriver merlinPrismriver;
    
    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "lunasa_prismriver_id")
    private List<LyricaPrismriver> lyricaPrismriverList;
}
```

```java:MerlinPrismriver.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "merlin_prismriver")
public class MerlinPrismriver {
    @Id
    private Long id;

    @Override
    public String toString() {
        return "MerlinPrismriver{" + "id=" + id + '}';
    }
}
```

```java:LyricaPrismriver.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "lyrica_prismriver")
public class LyricaPrismriver {
    @Id
    private Long id;

    @Override
    public String toString() {
        return "LyricaPrismriver{" + "id=" + id + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/d9c16152-030e-e333-3e66-0c7a5a2b1de3.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/2d6e5a19-84cb-ee23-b5ec-cd210d4a8f87.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/a0c7832d-bef5-017f-06cf-5c8d7f22985d.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/418b3d3a-c843-e5cb-e66f-c618b80e2416.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/94095aab-72f3-1607-f2ef-c1ebe39e9da4.jpeg)

**クエリ定義**

```sql:LunasaPrismriver.getMerlin
SELECT l.merlinPrismriver
  FROM LunasaPrismriver l
```

```sql:LunasaPrismriver.getLyrica
SELECT l.lyricaPrismriverList
  FROM LunasaPrismriver l
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<MerlinPrismriver> q1 = this.em.createNamedQuery("LunasaPrismriver.getMerlin", MerlinPrismriver.class);
    List<MerlinPrismriver> l1 = q1.getResultList();
    System.out.println(l1);
    
    TypedQuery<LyricaPrismriver> q2 = this.em.createNamedQuery("LunasaPrismriver.getLyrica", LyricaPrismriver.class);
    List<LyricaPrismriver> l2 = q2.getResultList();
    System.out.println(l2);
```

```text:GlassFishコンソール出力
情報:   [MerlinPrismriver{id=1}, MerlinPrismriver{id=2}, MerlinPrismriver{id=3}]
情報:   [LyricaPrismriver{id=1}, LyricaPrismriver{id=2}, LyricaPrismriver{id=3}, LyricaPrismriver{id=4}, LyricaPrismriver{id=5}, LyricaPrismriver{id=6}]
```

- `l.merlinPrismriver` のように、ドット区切りで関連するエンティティを取得することができる。
- 関連を辿れるのは、エンティティまたは組み込み可能クラスに限られる。
- 結果が複数件存在する場合は、単一の `List` にまとめられて結果が返される（エンティティ単位にまとめられたりはしない）。

# パラメータのバインド

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/8b9fdeda-c095-d700-4a00-4f7e63646396.jpeg)

**実装**

```java:YakumoRan.java
package sample.javaee.jpa.entity.jpql;

import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name = "yakumo_ran")
public class YakumoRan {
    @Id
    private Long id;
    @Temporal(TemporalType.DATE)
    private Date date;

    @Override
    public String toString() {
        return "YakumoRan{" + "id=" + id + ", date=" + date + '}';
    }
}
```

**データベースモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/a131712a-8339-ba52-3fa2-b53a610b34b6.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/8077cb76-e430-be04-f145-ee2f3fc934f6.jpeg)

**クエリ定義**

```sql:YakumoRan.findById
SELECT y
  FROM YakumoRan y
 WHERE y.id=:id
```

```sql:YakumoRan.findByDate
SELECT y
  FROM YakumoRan y
 WHERE y.date=:date
```

**動作確認**

```java:JpqlEjb.java
    public void yakumoRan() {
        TypedQuery<YakumoRan> query;
        
        query = this.em.createNamedQuery("YakumoRan.findById", YakumoRan.class);
        query.setParameter("id", 2L);
        System.out.println(query.getSingleResult());
        
        query = this.em.createNamedQuery("YakumoRan.findByDate", YakumoRan.class);
        query.setParameter("date", this.createDate("2014-01-01"), TemporalType.DATE);
        System.out.println(query.getSingleResult());
    }
    
    private Date createDate(String date) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        try {
            return format.parse(date);
        } catch (ParseException ex) {
            throw new RuntimeException(ex);
        }
    }
```

```text:GlassFishコンソール出力
情報:   YakumoRan{id=2, date=Thu Jan 01 00:00:00 JST 2015}
情報:   YakumoRan{id=1, date=Wed Jan 01 00:00:00 JST 2014}
```

- `:<名前>` という形式で、パラメータを定義することができる。
- パラメータに値を設定するには、 `Query` の `setParameter()` メソッドを使用する。
- 日付型の場合は、パラメータに `TemporalType` を渡す。


# WHERE 句で条件を絞る

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/03ec6c11-f225-371f-0149-bcc7b401e1b8.jpeg)

**実装**

```java:KonpakuYoumu.java
package sample.javaee.jpa.entity.jpql;

import java.util.List;
import javax.persistence.CollectionTable;
import javax.persistence.Column;
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.Table;

@Entity
@Table(name = "konpaku_youmu")
public class KonpakuYoumu {
    @Id
    private Long id;
    private int amount;
    private String value;
    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "konpaku_youmu_list", joinColumns = @JoinColumn(name = "konpaku_youmu_id"))
    @Column(name = "value")
    private List<String> list;

    @Override
    public String toString() {
        return "KonpakuYoumu{" + "id=" + id + ", amount=" + amount + ", value=" + value + ", list=" + list + '}';
    }
}
```

**データベース**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/004ae3c6-d0ba-9ab2-02ae-8bc01099b02b.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/887cd0ed-95c4-e5dd-6bf5-e0b52bd0dc55.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/434e6ed6-aad1-324a-577a-c51ffee17780.jpeg)

## 比較演算子

```sql:KonpakuYoumu.equal
SELECT k
  FROM KonpakuYoumu k
 WHERE k.amount = 10
```

```java:JpqlEjb.java
    TypedQuery<KonpakuYoumu> query;
    
    query = this.em.createNamedQuery("KonpakuYoumu.equal", KonpakuYoumu.class);
    System.out.println("Equal (=) : " + query.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   Equal (=) : KonpakuYoumu{id=1, amount=10, value=tokyo, list=[hoge, fuga, piyo]}
```

- SQL と同じ要領で比較演算子（`=`, `<>`, `<`, `<=`, `>`, `>=`）が使用できる。

## 論理演算子

```sql:KonpakuYoumu.or
SELECT k
  FROM KonpakuYoumu k
 WHERE k.amount = 10
    OR k.value = 'nagoya'
```

```java:JpqlEjb.java
    query = this.em.createNamedQuery("KonpakuYoumu.or", KonpakuYoumu.class);
    System.out.println("OR : " + query.getResultList());
```

```text:GlassFishコンソール出力
情報:   OR : [KonpakuYoumu{id=1, amount=10, value=tokyo, list=[hoge, fuga, piyo]}, KonpakuYoumu{id=3, amount=30, value=nagoya, list=[]}]
```

- SQL と同じ要領で論理演算子（`OR`, `AND`）が使用できる。

## BETWEEN

```sql:KonpakuYoumu.between
SELECT k
  FROM KonpakuYoumu k
 WHERE k.amount BETWEEN 20 AND 30
```

```java:JpqlEjb.java
    query = this.em.createNamedQuery("KonpakuYoumu.between", KonpakuYoumu.class);
    System.out.println("BETWEEN : " + query.getResultList());
```

```text:GlassFishコンソール出力
情報:   BETWEEN : [KonpakuYoumu{id=2, amount=20, value=osaka, list=[fizz, buzz]}, KonpakuYoumu{id=3, amount=30, value=nagoya, list=[]}]
```

- SQL と同じ要領で(ry 。
- 否定は `NOT BETWEEN`。

## LIKE

```sql:KonpakuYoumu.like
SELECT k
  FROM KonpakuYoumu k
 WHERE k.value LIKE '%a'
```

```java:JpqlEjb.java
    query = this.em.createNamedQuery("KonpakuYoumu.like", KonpakuYoumu.class);
    System.out.println("LIKE : " + query.getResultList());
```

```text:GlassFishコンソール出力
情報:   LIKE : [KonpakuYoumu{id=2, amount=20, value=osaka, list=[fizz, buzz]}, KonpakuYoumu{id=3, amount=30, value=nagoya, list=[]}]
```

- SQL と(ry 。
- 任意の文字列は `%`。任意の文字は `_`。
- エスケープする場合はバックスラッシュを使う `\_`, `\%`。
- 否定は `NOT LIKE` 。

## IS NULL

```sql:KonpakuYoumu.isNull
SELECT k
  FROM KonpakuYoumu k
 WHERE k.value IS NULL
```

```java:JpqlEjb.java
    query = this.em.createNamedQuery("KonpakuYoumu.isNull", KonpakuYoumu.class);
    System.out.println("IS NULL : " + query.getResultList());
```

```text:GlassFishコンソール出力
情報:   IS NULL : [KonpakuYoumu{id=4, amount=40, value=null, list=[]}]
```

- (ry 。
- 否定は `IS NOT NULL` 。

## IS EMPTY

```sql:KonpakuYoumu.isEmpty
SELECT k
  FROM KonpakuYoumu k
 WHERE k.list IS EMPTY
```

```java:JpqlEjb.java
    query = this.em.createNamedQuery("KonpakuYoumu.isEmpty", KonpakuYoumu.class);
    System.out.println("IS EMPTY : " + query.getResultList());
```

```text:GlassFishコンソール出力
情報:   IS EMPTY : [KonpakuYoumu{id=3, amount=30, value=nagoya, list=[]}, KonpakuYoumu{id=4, amount=40, value=null, list=[]}]
```

- `IS EMPTY` は、コレクション型のプロパティに対して使用できる演算子で、そのコレクションが空であることを条件にできる。
- 否定は `IS NOT EMPTY` 。

## MEMBER OF

```sql:KonpakuYoumu.memberOf
SELECT k
  FROM KonpakuYoumu k
 WHERE 'fizz' MEMBER OF k.list
```

```java:JpqlEjb.java
    query = this.em.createNamedQuery("KonpakuYoumu.memberOf", KonpakuYoumu.class);
    System.out.println("MEMBER OF : " + query.getResultList());
```

```text:GlassFishコンソール出力
情報:   MEMBER OF : [KonpakuYoumu{id=2, amount=20, value=osaka, list=[fizz, buzz]}]
```

- `MEMBER OF` は、コレクション型のプロパティが指定した要素を持つかどうかを条件にできる。
- 左辺に条件となる値を、右辺に対象のプロパティを指定する。
- 否定は `NOT MEMBER OF` 。

## EXISTS

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/f704470a-46e6-ed73-096e-566139858fcf.jpeg)

**実装**

```java:KawashiroNitori.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="kawashiro_nitori")
public class KawashiroNitori {
    @Id
    private Long id;
    private String value;

    @Override
    public String toString() {
        return "KawashiroNitori{" + "id=" + id + ", value=" + value + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/a0b33a46-ecb1-4562-a161-d25a2e7ea5da.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/ab27e760-c036-462c-c1d6-0c269725232e.jpeg)

**クエリ定義**

```sql:KawashiroNitori.exists
SELECT k1
  FROM KawashiroNitori k1
 WHERE EXISTS (
       SELECT 1
         FROM KawashiroNitori k2
        WHERE k2.value = k1.value
          AND k2.id <> k1.id
  )
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<KawashiroNitori> query = this.em.createNamedQuery("KawashiroNitori.exists", KawashiroNitori.class);
    
    query.getResultList()
         .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   KawashiroNitori{id=1, value=hoge}
情報:   KawashiroNitori{id=3, value=hoge}
情報:   KawashiroNitori{id=4, value=piyo}
情報:   KawashiroNitori{id=5, value=piyo}
```

- SQL と同じ感じで `EXISTS` が使える。
- 否定は `NOT EXISTS` 。

## IN, ANY(SOME), ALL

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/84f5580b-8101-dd2b-a15f-a330bef0c1db.jpeg)

**実装**

```java:InubashiriMomiji.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="inubashiri_momiji")
public class InubashiriMomiji {
    @Id
    private Long id;
    private int number;

    @Override
    public String toString() {
        return "InubashiriMomiji{" + "id=" + id + ", number=" + number + '}';
    }
}
```

```java:KochiyaSanae.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="kochiya_sanae")
public class KochiyaSanae {
    @Id
    private Long id;
    private int number;

    @Override
    public String toString() {
        return "KochiyaSanae{" + "id=" + id + ", number=" + number + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/6ecd047d-1980-3bd1-e271-04eee31c269c.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/f486ad7c-dfe5-56b1-4885-55d82b6df960.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/a2723bb1-de8e-4e57-f393-c210121f183f.jpeg)

### IN

**クエリ定義**

```sql:InubashiriMomiji.in
SELECT i
  FROM InubashiriMomiji i
 WHERE i.number IN (111, 333)
```

```sql:InubashiriMomiji.inSubquery
SELECT i
  FROM InubashiriMomiji i
 WHERE i.number IN (
       SELECT k.number
         FROM KochiyaSanae k
 )
```

```sql:InubashiriMomiji.inParameter
SELECT i
  FROM InubashiriMomiji i
 WHERE i.number IN :inParam
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<InubashiriMomiji> query;
    
    query = this.em.createNamedQuery("InubashiriMomiji.in", InubashiriMomiji.class);
    System.out.println("<<InubashiriMomiji.in>>");
    query.getResultList()
         .forEach(System.out::println);
    
    query = this.em.createNamedQuery("InubashiriMomiji.inSubquery", InubashiriMomiji.class);
    System.out.println("<<InubashiriMomiji.inSubquery>>");
    query.getResultList()
         .forEach(System.out::println);
    
    query = this.em.createNamedQuery("InubashiriMomiji.inParameter", InubashiriMomiji.class);
    System.out.println("<<InubashiriMomiji.inParameter>>");
    query.setParameter("inParam", Arrays.asList(222, 333));
    query.getResultList()
         .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   <<InubashiriMomiji.in>>
情報:   InubashiriMomiji{id=1, number=111}
情報:   InubashiriMomiji{id=3, number=333}

情報:   <<InubashiriMomiji.inSubquery>>
情報:   InubashiriMomiji{id=1, number=111}
情報:   InubashiriMomiji{id=2, number=222}

情報:   <<InubashiriMomiji.inParameter>>
情報:   InubashiriMomiji{id=2, number=222}
情報:   InubashiriMomiji{id=3, number=333}
```

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/f486ad7c-dfe5-56b1-4885-55d82b6df960.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/a2723bb1-de8e-4e57-f393-c210121f183f.jpeg)

- SQL と同じように IN 句が使える。
- パラメータとして `List` を渡すこともできる。

### ANY

**クエリ定義**

```sql:InubashiriMomiji.any
SELECT i
  FROM InubashiriMomiji i
 WHERE i.number < ANY (
       SELECT k.number
         FROM KochiyaSanae k
 )
```

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("InubashiriMomiji.any", InubashiriMomiji.class);
    query.getResultList()
         .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   InubashiriMomiji{id=1, number=111}
情報:   InubashiriMomiji{id=2, number=222}
```

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/f486ad7c-dfe5-56b1-4885-55d82b6df960.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/a2723bb1-de8e-4e57-f393-c210121f183f.jpeg)

- SQL と同じ感じで ANY が使える。
- ただし、条件に指定できるのはサブクエリのみ。
- ANY の変わりに SOME を使うこともできる（動きは同じ）。

### ALL

**クエリ定義**

```sql:InubashiriMomiji.all
SELECT i
  FROM InubashiriMomiji i
 WHERE i.number > ALL (
       SELECT k.number
         FROM KochiyaSanae k
 )
```

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("InubashiriMomiji.all", InubashiriMomiji.class);
    query.getResultList()
         .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   InubashiriMomiji{id=3, number=333}
情報:   InubashiriMomiji{id=4, number=444}
```

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/f486ad7c-dfe5-56b1-4885-55d82b6df960.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/a2723bb1-de8e-4e57-f393-c210121f183f.jpeg)

- ANY 同様に ALL も使える。
- こちらも、条件に指定できるのはサブクエリのみ。

# コレクションの個々のフィールドを参照する

例えば、以下のようなエンティティとテーブルが存在したとする。

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/da4b8455-0924-8bef-a024-01d4ae993c8c.jpeg)

**実装**

```java:WriggleNightbug.java
package sample.javaee.jpa.entity.jpql;

import java.util.List;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.Table;

@Entity
@Table(name = "wriggle_nightbug")
public class WriggleNightbug {
    @Id
    private Long id;
    @JoinColumn(name = "wriggle_nightbug_id")
    private List<MystiaLorelei> mystiaLoreleiList;

    @Override
    public String toString() {
        return "WriggleNightbug{" + "id=" + id + ", mystiaLoreleiList=" + mystiaLoreleiList + '}';
    }
}
```

```java:MystiaLorelei.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "mystia_lorelei")
public class MystiaLorelei {
    @Id
    private Long id;

    @Override
    public String toString() {
        return "MystiaLorelei{" + "id=" + id + '}';
    }
}
```

**データベースモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/c7d08b37-f25a-fb82-cfc1-89addb9616a4.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/108d96b3-7ae9-900d-1602-9bafb5279887.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/560b754a-7a47-1c47-79ae-bd3c5bc26fec.jpeg)

`MystiaLorelei` は、コレクションとして `WriggleNightbug` から参照されている（`mystiaLoreleiList`）。
ここで、 `WriggleNightbug` エンティティが持つ `mystiaLoreleiList` の各 `ID` だけを抽出するために、以下のような JPQL を書いたとする。

```sql:
SELECT w.mystiaLoreleiList.id
  FROM WriggleNightbug w
```

この JPQL はコンパイルエラーになり、サーバー起動時に例外がスローされる。

```text:例外のメッセージ
Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT w.mystiaLoreleiList.id FROM WriggleNightbug w]. 
[7, 29] The state field path 'w.mystiaLoreleiList.id' cannot be resolved to a valid type.
```

問題があるのは `w.mystiaLoreleiList.id` の部分。

一見うまくいきそうに見えるが、よく考えると `w.mystiaLoreleiList` の型は `List` なので、 `id` なんてプロパティは参照できない。
このため、コンパイルエラーが発生している。

正しくは、以下のように JPQL を記述する。

```sql:
SELECT mystiaLorelei.id
  FROM WriggleNightbug w
      ,IN(w.mystiaLoreleiList) mystiaLorelei
```

`IN(<コレクションのプロパティ>) <別名>` という形で、 `FROM` 句の中にコレクションの個々の要素を参照するための別名を宣言する。
これによって、宣言した別名でコレクションの各要素に対してプロパティの参照ができるようになる。

**クエリ定義**

```sql:WriggleNightbug.inQuery
SELECT mystiaLorelei.id
  FROM WriggleNightbug w
      ,IN(w.mystiaLoreleiList) mystiaLorelei
 WHERE w.id = 1
```

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("WriggleNightbug.inQuery");
    System.out.println(query.getResultList());
```

```text:GlassFishコンソール出力
情報:   [1, 2, 3]
```

## INNER JOIN で置き換える

`IN` を使ったコレクションの要素の参照は、 `INNER JOIN` を使った記述に置き換えることができる。

**クエリ定義**

```sql:WriggleNightbug.innerJoin
    SELECT mystiaLorelei.id
      FROM WriggleNightbug w
INNER JOIN w.mystiaLoreleiList mystiaLorelei
     WHERE w.id = 1
```


# 組み込みの関数を使用する

## 文字列操作用の関数

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/eb023b54-d1dc-d24d-08b7-26a494ad5b29.jpeg)

**実装**

```java:YakumoYukari.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "yakumo_yukari")
public class YakumoYukari {
    @Id
    private Long id;
    
    private String string;

    @Override
    public String toString() {
        return "YakumoYukari{" + "id=" + id + ", string=" + string + '}';
    }
}
```

**データベース**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/664b9c6a-34a6-58ed-2b77-a243316a7a23.jpeg)

### 連結する（CONCAT）

**クエリ定義**

```sql:YakumoYukari.concat
SELECT CONCAT('<<', y.string, '>>')
  FROM YakumoYukari y
 WHERE y.id=1
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/65a51079-3e58-8657-455e-1972d2aa13c2.jpeg)

**動作確認**

```java:JpqlEjb.java
    TypedQuery<String> queryStr;
    
    queryStr = this.em.createNamedQuery("YakumoYukari.concat", String.class);
    System.out.println(queryStr.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   <<hoge>>
```

- `CONCAT` 関数で、文字列を連結できる。

### 抽出する（SUBSTRING）

**クエリ定義**

```sql:YakumoYukari.substringStart
SELECT SUBSTRING(y.string, 3)
  FROM YakumoYukari y
 WHERE y.id=4
```

```sql:YakumoYukari.substringStartLength
SELECT SUBSTRING(y.string, 3, 2)
  FROM YakumoYukari y
 WHERE y.id=4
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/d9df5db9-1f9a-c34c-abd1-be551ed5d9cb.jpeg)

**動作確認**

```java:JpqlEjb.java
    queryStr = this.em.createNamedQuery("YakumoYukari.substringStart", String.class);
    System.out.println(queryStr.getSingleResult());
    
    queryStr = this.em.createNamedQuery("YakumoYukari.substringStartLength", String.class);
    System.out.println(queryStr.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   3456
情報:   34
```

- `SUBSTRING(<対象文字列>, <開始インデックス>[, <抽出文字数>])` で文字列の一部を抽出できる。

### トリムする（TRIM）

**クエリ定義**

```sql:YakumoYukari.trimDefault
SELECT TRIM(y.string)
  FROM YakumoYukari y
 WHERE y.id=2
```

```sql:YakumoYukari.trimLeading
SELECT TRIM(LEADING FROM y.string)
  FROM YakumoYukari y
 WHERE y.id=2
```

```sql:YakumoYukari.trimAsterisk
SELECT TRIM(TRAILING '*' FROM y.string)
  FROM YakumoYukari y
 WHERE y.id=3
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/c8e522d6-2b7d-1ab6-3a1b-dda576a64296.jpeg)

**動作確認**

```java:JpqlEjb.java
    queryStr = this.em.createNamedQuery("YakumoYukari.trimDefault", String.class);
    System.out.println("[" + queryStr.getSingleResult() + "]");
    
    queryStr = this.em.createNamedQuery("YakumoYukari.trimLeading", String.class);
    System.out.println("[" + queryStr.getSingleResult() + "]");
    
    queryStr = this.em.createNamedQuery("YakumoYukari.trimAsterisk", String.class);
    System.out.println("[" + queryStr.getSingleResult() + "]");
```

```text:GlassFishコンソール出力
情報:   [fuga]
情報:   [fuga  ]
情報:   [**piyo]
```

- `TRIM()` 関数で、文字のトリムができる。
- デフォルトは、左右のスペースがトリムされる。
- 前方だけをトリムする場合は、 `LEADING FROM` を付ける。
    - 後方だけをトリムする場合は、 `TRAILING FROM` を付ける。
    - 前と後ろ両方をトリムする場合は、 `BOTH FROM` を付ける。
- トリムする文字を指定する場合は、 `'<トリムする文字>' FROM` を付ける。
- 両方指定する場合は、 `TRAILING '*' FROM` のように指定する。

### 大文字・小文字にする（UPPER, LOWER）

**クエリ定義**

```sql:YakumoYukari.lower
SELECT LOWER(y.string)
  FROM YakumoYukari y
 WHERE y.id=5
```

```sql:YakumoYukari.upper
SELECT UPPER(y.string)
  FROM YakumoYukari y
 WHERE y.id=5
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/7839d48c-88a6-aa93-8984-786a33ea1899.jpeg)

**動作確認**

```java:JpqlEjb.java
    queryStr = this.em.createNamedQuery("YakumoYukari.lower", String.class);
    System.out.println(queryStr.getSingleResult());
    
    queryStr = this.em.createNamedQuery("YakumoYukari.upper", String.class);
    System.out.println(queryStr.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   fizzbuzz
情報:   FIZZBUZZ
```

- `LOWER()` で小文字に、 `UPPER()` で大文字に変換できる。

### 文字数を取得する（LENGTH）

**クエリ定義**

```sql:YakumoYukari.length
SELECT LENGTH(y.string)
  FROM YakumoYukari y
 WHERE y.id=1
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/2bdde9d6-2362-dc68-c279-266eff6e0d7e.jpeg)

**動作確認**

```java:JpqlEjb.java
    queryInt = this.em.createNamedQuery("YakumoYukari.length", Integer.class);
    System.out.println(queryInt.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   4
```

- `LENGTH()` で文字列の文字数を取得できる。

### 指定した文字が位置する場所を取得する（LOCATE）

**クエリ定義**

```sql:YakumoYukari.locate
SELECT LOCATE('cd', y.string)
  FROM YakumoYukari y
 WHERE y.id=6
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/b7ab6945-4b6e-37ee-ebe4-830f6cb7d6a3.jpeg)

**動作確認**

```java:JpqlEjb.java
    queryInt = this.em.createNamedQuery("YakumoYukari.locate", Integer.class);
    System.out.println(queryInt.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   3
```

- `LOCATE(<検索する文字列>, <検索対象の文字列>)` で、その文字の開始位置（１始まり）を取得できる（`indexOf()` 的な）。
- 第三引数に検索対象の開始位置を指定することもできる。

## 算術関数

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/ac37a17e-2902-7b8d-a89f-9b663c9f9b46.jpeg)

**実装**

```java:IbukiSuika.java
package sample.javaee.jpa.entity.jpql;

import java.util.List;
import javax.persistence.CollectionTable;
import javax.persistence.Column;
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OrderColumn;
import javax.persistence.Table;

@Entity
@Table(name = "ibuki_suika")
public class IbukiSuika {
    @Id
    private Long id;
    private double number;
    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "ibuki_suika_list", joinColumns = @JoinColumn(name = "ibuki_suika_id"))
    @Column(name = "value")
    @OrderColumn(name = "order")
    private List<String> list;

    @Override
    public String toString() {
        return "IbukiSuika{" + "id=" + id + ", number=" + number + ", list=" + list + '}';
    }
}
```

**データベースモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/73ceb4f8-b630-1b8f-f0c7-040319229cb5.jpeg)

### 絶対値を取得する（ABS）

**クエリ定義**

```sql:IbukiSuika.abs
SELECT ABS(i.number)
  FROM IbukiSuika i
 WHERE i.id=1
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/09df2d39-b1f0-e08e-4d52-171659c956ac.jpeg)

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("IbukiSuika.abs");
    System.out.println(query.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   10.2
```

- `ABS()` 関数を使うと、絶対値を取得できる。

### 平方根を取得する（SQRT）

**クエリ定義**

```sql:IbukiSuika.sqrt
SELECT SQRT(i.number)
  FROM IbukiSuika i
 WHERE i.id=2
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/0e63638c-e8f6-8300-cc14-14fc8b794855.jpeg)

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("IbukiSuika.sqrt");
    System.out.println(query.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   5.0
```

- `SQRT()` 関数を使うと、平方根を取得できる。

### 商の余りを取得する（MOD）

**クエリ定義**

```sql:IbukiSuika.mod
SELECT MOD(i.number, 5)
  FROM IbukiSuika i
 WHERE i.id=3
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/6beeb851-50de-83ec-ec15-8f79854e66a3.jpeg)

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("IbukiSuika.mod");
    System.out.println(query.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   2
```

- `MOD()` 関数で、商の余りを取得できる。

### コレクションのサイズを取得する（SIZE）

**クエリ定義**

```sql:IbukiSuika.size
SELECT SIZE(i.list)
  FROM IbukiSuika i
 WHERE i.id=4
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/61783262-ca9e-e073-2cf7-d7abab10e2d5.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/04cf20c2-2e98-7576-1968-4c8c4a9d9d92.jpeg)

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("IbukiSuika.size");
    System.out.println(query.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   3
```

- `SIZE()` 関数を使うと、コレクションのサイズを取得できる。

### リストのソート順序を条件に使用する（INDEX）

**クエリ定義**

```sql:IbukiSuika.index
SELECT l
  FROM IbukiSuika i
      ,IN(i.list) l
 WHERE i.id=4
   AND INDEX(l) = 1
```

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/04cf20c2-2e98-7576-1968-4c8c4a9d9d92.jpeg)


**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("IbukiSuika.index");
    System.out.println(query.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   fuga
```

- `INDEX()` 関数を使用すると、リストのインデックス（`@OrderColumn` で指定したカラムの値）を検索条件に使用できるようになる。

## 日付関数

**クエリ定義**

```sql:KamishirasawaKeine.currentDate
SELECT CURRENT_DATE
  FROM KamishirasawaKeine k
 WHERE k.id=1
```

```sql:KamishirasawaKeine.currentTime
SELECT CURRENT_TIME
  FROM KamishirasawaKeine k
 WHERE k.id=1
```

```sql:KamishirasawaKeine.currentTimestamp
SELECT CURRENT_TIMESTAMP
  FROM KamishirasawaKeine k
 WHERE k.id=1
```

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("KamishirasawaKeine.currentDate");
    Object result = query.getSingleResult();
    System.out.println("class = " + result.getClass() + ", value = " + result);
    
    query = this.em.createNamedQuery("KamishirasawaKeine.currentTime");
    result = query.getSingleResult();
    System.out.println("class = " + result.getClass() + ", value = " + result);
    
    query = this.em.createNamedQuery("KamishirasawaKeine.currentTimestamp");
    result = query.getSingleResult();
    System.out.println("class = " + result.getClass() + ", value = " + result);
```

```text:GlassFishコンソール出力
情報:   class = class java.sql.Date, value = 2015-03-09
情報:   class = class java.sql.Time, value = 23:24:27
情報:   class = class java.sql.Timestamp, value = 2015-03-09 23:24:27.0
```

- `CURRENT_DATE`, `CURRENT_TIME`, `CURRENT_TIMESTAMP` で、それぞれ実行時の日付・時刻・日時を取得できる。

## エンティティの型を検索の条件で使用する

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/114349be-6ee6-dba5-5fa8-a72198382cea.jpeg)

**実装**

```java:ReisenUdongeinInaba.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.Table;

@Entity
@Table(name = "reisen_udongein_inaba")
public class ReisenUdongeinInaba {
    @Id
    private Long id;
    @JoinColumn(name = "inaba_tewi_id")
    private ParentInabaTewi inabaTewi;

    @Override
    public String toString() {
        return "ReisenUdongeinInaba{" + "id=" + id + ", inabaTewi=" + inabaTewi + '}';
    }
}
```

```java:ParentInabaTewi.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "inaba_tewi")
public class ParentInabaTewi {
    @Id
    protected Long id;

    @Override
    public String toString() {
        return "ParentInabaTewi{" + "id=" + id + '}';
    }
}
```

```java:ChildInabaTewi.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;

@Entity
public class ChildInabaTewi extends ParentInabaTewi {

    @Override
    public String toString() {
        return "ChildInabaTewi{" + "id=" + id + '}';
    }
}
```

**データベースモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/1c08a7ad-c574-7fd5-63f2-ecaaab676de7.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/fd392adc-fe48-53bb-167c-d9d36697e974.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/2dbe6705-8b28-8cfa-be24-8c7284140fe1.jpeg)

**クエリ定義**

```sql:ReisenUdongeinInaba.parent
SELECT r
  FROM ReisenUdongeinInaba r
 WHERE TYPE(r.inabaTewi) = ParentInabaTewi
```

```sql:ReisenUdongeinInaba.child
SELECT r
  FROM ReisenUdongeinInaba r
 WHERE TYPE(r.inabaTewi) = ChildInabaTewi
```

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("ReisenUdongeinInaba.parent");
    System.out.println(query.getSingleResult());
    
    query = this.em.createNamedQuery("ReisenUdongeinInaba.child");
    System.out.println(query.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   ReisenUdongeinInaba{id=1, inabaTewi=ParentInabaTewi{id=1}}
情報:   ReisenUdongeinInaba{id=2, inabaTewi=ChildInabaTewi{id=2}}
```

- `TYPE()` 関数を使うことで、エンティティの型を取得し、検索条件として利用できる。

## 集約関数

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/41f580c9-bda5-cfb6-bd2a-942d5e0e722b.jpeg)

**実装**

```java:HouraisanKaguya.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "houraisan_kaguya")
public class HouraisanKaguya {
    @Id
    private Long id;
    private String string;
    private short number;
}
```

**データベースモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/761b47c6-805d-dff5-8df4-228656769e21.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/3a3294c6-f925-f9de-b33b-b99f14474d36.jpeg)

### レコード数を取得する（COUNT）

**クエリ定義**

```sql:HouraisanKaguya.count
SELECT COUNT(h)
  FROM HouraisanKaguya h
```

**動作確認**

```java:JpqlEjb.java
    public void houraisanKaguya() {
        Query query = this.em.createNamedQuery("HouraisanKaguya.count");
        this.print("count", query);
    }

    private void print(String tag, Query query) {
        Object result = query.getSingleResult();
        System.out.println(tag + " : " + result + ", class : " + result.getClass());
    }
```

```text:GlassFishコンソール出力
情報:   count : 4, class : class java.lang.Long
```

- `COUNT()` 関数で、レコードの件数を取得する。
- レコードを１件も取得できない場合は `0` が返される。
- 型は `Long`。

### 最大・最小を取得する（MAX, MIN）

**クエリ定義**

```sql:HouraisanKaguya.stringMax
SELECT MAX(h.string)
  FROM HouraisanKaguya h
```

```sql:HouraisanKaguya.numberMax
SELECT MAX(h.number)
  FROM HouraisanKaguya h
```

```sql:HouraisanKaguya.min
SELECT MIN(h.number)
  FROM HouraisanKaguya h
```

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("HouraisanKaguya.stringMax");
    this.print("stringMax", query);
    
    query = this.em.createNamedQuery("HouraisanKaguya.numberMax");
    this.print("numberMax", query);
    
    query = this.em.createNamedQuery("HouraisanKaguya.min");
    this.print("min", query);
```

```text:GlassFishコンソール出力
情報:   stringMax : ddd, class : class java.lang.String
情報:   numberMax : 40, class : class java.lang.Short
情報:   min : 10, class : class java.lang.Short
```

- `MAX()`, `MIN()` 関数で、最大値と最小値を取得できる。
- 型は、エンティティの該当フィールドに対応する型に変換される（場合によっては丸められる）。
    - `HouraisanKaguya#number` は `short` 型で宣言しているので、 DB の値が実数値であっても `java.lang.Short` に変換されている。

### 平均値を取得する（AVG）

**クエリ定義**

```sql:HouraisanKaguya.avg
SELECT AVG(h.number)
  FROM HouraisanKaguya h
```

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("HouraisanKaguya.avg");
    this.print("avg", query);
```

```text:GlassFishコンソール出力
情報:   avg : 26.0, class : class java.lang.Double
```

- `AVG()` 関数で、平均値を取得できる。
- 型は `Double`。

### 合計値を取得する（SUM）

**クエリ定義**

```sql:HouraisanKaguya.sum
SELECT SUM(h.number)
  FROM HouraisanKaguya h
```

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("HouraisanKaguya.sum");
    this.print("sum", query);
```

```text:GlassFishコンソール出力
情報:   sum : 104, class : class java.lang.Long
```

- `SUM()` 関数で、合計値を取得できる。
- 取得される値は、フィールドの型が、
    - 整数値の場合は `Long` 型に、
    - 実数値の場合は `Double` 型に、
    - `BigInteger` 型の場合は `BigInteger` に、
    - `BigDecimal` 型の場合は `BigDecimal` に変換される。

# RDB が提供する関数を利用する

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/72a57779-e7ea-26b3-4c47-a859e21420cf.jpeg)

**実装**

```java:HinanaiTenshi.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="hinanai_tenshi")
public class HinanaiTenshi {
    @Id
    private Long id;
    private int number;

    @Override
    public String toString() {
        return "HinanaiTenshi{" + "id=" + id + ", number=" + number + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/8799938b-ca78-a28b-deac-3bb1c526cd30.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/b39e20f1-e491-93b9-8c4c-2d40ad155ab2.jpeg)

**クエリ定義**

```sql:HinanaiTenshi.functions
SELECT FUNCTION('FORMAT', h.number, 3)
  FROM HinanaiTenshi h
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<String> query = this.em.createNamedQuery("HinanaiTenshi.functions", String.class);
    
    query.getResultList()
         .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   123,456.000
情報:   345.123
情報:   56,789.123
```

- `FUNCTION(<関数名> [, <引数>...])` という形式で、使用している RDB が提供する関数を使用できる。
- ビルドインの関数はもちろん、ユーザー定義の関数も利用できる。

# ORDER BY でソートする

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/b9034989-1005-a379-f3a8-db3478d89f54.jpeg)

**実装**

```java:SaigyojiYuyuko.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Embedded;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "saigyoji_yuyuko")
public class SaigyojiYuyuko {
    @Id
    private Long id;
    @Embedded
    private EmbeddedSaigyojiYuyuko embeddedSaigyojiYuyuko;

    @Override
    public String toString() {
        return "SaigyojiYuyuko{" + "id=" + id + ", embeddedSaigyojiYuyuko=" + embeddedSaigyojiYuyuko + '}';
    }
}
```

```java:EmbeddedSaigyojiYuyuko.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Embeddable;

@Embeddable
public class EmbeddedSaigyojiYuyuko {
    private int number;

    @Override
    public String toString() {
        return "{ " + this.number + " }";
    }
}
```

**データベースモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/fbd16c35-df14-4bf3-db87-d31606038731.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/e114075e-3f33-6775-3307-e970b05e343b.jpeg)

**クエリ定義**

```sql:SaigyojiYuyuko.orderByIdDesc
  SELECT s
    FROM SaigyojiYuyuko s
ORDER BY s.id DESC
```

```sql:SaigyojiYuyuko.orderByNumberDesc
  SELECT s
    FROM SaigyojiYuyuko s
ORDER BY s.embeddedSaigyojiYuyuko.number DESC
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<SaigyojiYuyuko> query;
    List<SaigyojiYuyuko> list;
    
    System.out.println("[SaigyojiYuyuko.orderByIdDesc]");
    query = this.em.createNamedQuery("SaigyojiYuyuko.orderByIdDesc", SaigyojiYuyuko.class);
    list = query.getResultList();
    list.forEach(System.out::println);
    
    System.out.println("[SaigyojiYuyuko.orderByNumberDesc]");
    query = this.em.createNamedQuery("SaigyojiYuyuko.orderByNumberDesc", SaigyojiYuyuko.class);
    list = query.getResultList();
    list.forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   [SaigyojiYuyuko.orderByIdDesc]
情報:   SaigyojiYuyuko{id=5, embeddedSaigyojiYuyuko={ 6 }}
情報:   SaigyojiYuyuko{id=4, embeddedSaigyojiYuyuko={ 7 }}
情報:   SaigyojiYuyuko{id=3, embeddedSaigyojiYuyuko={ 8 }}
情報:   SaigyojiYuyuko{id=2, embeddedSaigyojiYuyuko={ 9 }}
情報:   SaigyojiYuyuko{id=1, embeddedSaigyojiYuyuko={ 10 }}
情報:   [SaigyojiYuyuko.orderByNumberDesc]
情報:   SaigyojiYuyuko{id=1, embeddedSaigyojiYuyuko={ 10 }}
情報:   SaigyojiYuyuko{id=2, embeddedSaigyojiYuyuko={ 9 }}
情報:   SaigyojiYuyuko{id=3, embeddedSaigyojiYuyuko={ 8 }}
情報:   SaigyojiYuyuko{id=4, embeddedSaigyojiYuyuko={ 7 }}
情報:   SaigyojiYuyuko{id=5, embeddedSaigyojiYuyuko={ 6 }}
```

- SQL と同じ要領で `ORDER BY` が使える。
- `ASC` で昇順、 `DESC` で降順に並べることができる。
    - 省略時は、 `ASC` が適用される。

# GROUP BY で集約する

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/105afee5-3aed-9133-692a-eff2af5802d1.jpeg)

**実装**

```java:YagokoroEirin.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Embedded;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "yagokoro_eirin")
public class YagokoroEirin {
    @Id
    private Long id;
    private String value;
    @Embedded
    private EmbeddedValue embeddedValue;

    @Override
    public String toString() {
        return "YagokoroEirin{" + "id=" + id + ", value=" + value + '}';
    }
}
```

```java:EmbeddedValue.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Embeddable;

@Embeddable
public class EmbeddedValue {
    private int number;

    @Override
    public String toString() {
        return "EmbeddedValue{" + "number=" + number + '}';
    }
    
    @Override
    public int hashCode() {
        // 省略
    }

    @Override
    public boolean equals(Object obj) {
        // 省略
    }
}
```

**データベースモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/5dd11f53-94bc-ff08-f8b5-a76457c596e3.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/98f9cdb4-661e-1a89-3f42-f5c57599e084.jpeg)

**クエリ定義**

```sql:YagokoroEirin.groupBy
  SELECT y.value
        ,COUNT(y)
    FROM YagokoroEirin y
GROUP BY y.value
```

```sql:YagokoroEirin.groupByObject
  SELECT y.embeddedValue
        ,COUNT(y)
    FROM YagokoroEirin y
GROUP BY y.embeddedValue
```

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("YagokoroEirin.groupBy");
    
    List<Object[]> list = query.getResultList();
    
    list.stream()
        .map(Arrays::toString)
        .forEach(System.out::println);
    
    query = this.em.createNamedQuery("YagokoroEirin.groupByObject");
    
    list = query.getResultList();
    
    list.stream()
        .map(Arrays::toString)
        .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   [fuga, 2]
情報:   [hoge, 3]
情報:   [piyo, 1]
情報:   [EmbeddedValue{number=100}, 1]
情報:   [EmbeddedValue{number=200}, 2]
情報:   [EmbeddedValue{number=300}, 3]
```

- `GROUP BY` で、項目を集約することができる。

## HAVING 句

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/1424f998-d2ea-b169-6e06-7aca031e6af9.jpeg)

**実装**

```java:AkiMinoriko.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="aki_minoriko")
public class AkiMinoriko {
    @Id
    private Long id;
    private int number;

    @Override
    public String toString() {
        return "AkiMinoriko{" + "id=" + id + ", number=" + number + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/cd455e97-9da7-7524-64f4-12decbe022fe.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/61de478b-9213-5f10-8b62-638679d9cbc0.jpeg)

**クエリ定義**

```sql:AkiMinoriko.having
  SELECT a.number
        ,COUNT(a.number)
    FROM AkiMinoriko a
GROUP BY a.number
  HAVING 2 < COUNT(a.number)
```

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("AkiMinoriko.having");
    
    List<Object[]> list = query.getResultList();
    
    list.stream()
        .map(Arrays::toString)
        .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   [100, 4]
情報:   [200, 3]
```

- SQL と同じノリで `HAVING` 句が利用できる。

# DISTINCT

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/847e39c7-9f17-ba61-68df-ce802d47706d.jpeg)

**実装**

```java:KagiyamaHina.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="kagiyama_hina")
public class KagiyamaHina {
    @Id
    private Long id;
    private int number;

    @Override
    public String toString() {
        return "KagiyamaHina{" + "id=" + id + ", number=" + number + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/e36ac45c-1f15-d259-7c55-f5f6c1a2b38d.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/e31db2eb-ff42-76b3-8afa-89036a25fbfe.jpeg)

**クエリ定義**

```sql:KagiyamaHina.distinct
  SELECT 
DISTINCT k.number
    FROM KagiyamaHina k
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<Integer> query = this.em.createNamedQuery("KagiyamaHina.distinct", Integer.class);
    
    query.getResultList()
         .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   111
情報:   222
情報:   333
```

- SQL と同じように DISTINCT が使える。
- DISTINCT の対象に組み込み可能オブジェクトを指定することはできないっぽい？

> The result of DISTINCT over embeddable objects or map entry results is **undefined**.

JPA 2.1 の仕様書の 4.8

# サブクエリ

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/bd78432f-8260-794a-6f02-7fa5ca6b2392.jpeg)

**実装**

```java:ShameimaruAya.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "shameimaru_aya")
public class ShameimaruAya {
    @Id
    private Long id;
    private int number;

    @Override
    public String toString() {
        return "ShameimaruAya{" + "id=" + id + ", number=" + number + '}';
    }
}
```

**データベースモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/9ba07a44-016a-a6a3-e27a-ab12c391557f.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/521dff90-8a68-bd8d-0f64-08ec4815101d.jpeg)

**クエリ定義**

```sql:ShameimaruAya.findMax
SELECT s1
  FROM ShameimaruAya s1
 WHERE s1.number = (
         SELECT MAX(s2.number)
           FROM ShameimaruAya s2
       )
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<ShameimaruAya> query = this.em.createNamedQuery("ShameimaruAya.findMax", ShameimaruAya.class);
    System.out.println(query.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   ShameimaruAya{id=2, number=30}
```

- SQL と同じ要領で、丸括弧 `( )` でサブクエリを書くことができる。

# LEFT JOIN

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/4457c623-6266-f6a8-5e11-f520f655aa55.jpeg)

**実装**

```java:MedicineMelancholy.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "medicine_melancholy")
public class MedicineMelancholy {
    @Id
    private Long id;
    private String code;
    private String value;

    @Override
    public String toString() {
        return "MedicineMelancholy{" + "id=" + id + ", code=" + code + ", value=" + value + '}';
    }
}
```

```java:KazamiYuka.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "kazami_yuka")
public class KazamiYuka {
    @Id
    private Long id;
    private String code;
    private String value;

    @Override
    public String toString() {
        return "KazamiYuka{" + "id=" + id + ", code=" + code + ", value=" + value + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/d128a23a-90ed-35b2-2c18-3f9b049717da.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/fa8817ce-ece3-4929-9754-a0cac492f885.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/4a2719a4-93f6-df4c-bad2-53815651235c.jpeg)

**クエリ定義**

```sql:MedicineMelancholy.leftJoinOnCode
   SELECT m
         ,k
     FROM MedicineMelancholy m
LEFT JOIN KazamiYuka k
       ON k.code = m.code
```

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("MedicineMelancholy.leftJoinOnCode");
    System.out.println("leftJoinOnCode");
    
    List<Object[]> list = query.getResultList();
    list.stream()
        .map(Arrays::toString)
        .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   [MedicineMelancholy{id=1, code=AAA, value=hoge}, KazamiYuka{id=1, code=AAA, value=fizz}]
情報:   [MedicineMelancholy{id=2, code=BBB, value=fuga}, null]
情報:   [MedicineMelancholy{id=3, code=CCC, value=piyo}, KazamiYuka{id=2, code=CCC, value=buzz}]
情報:   [MedicineMelancholy{id=3, code=CCC, value=piyo}, KazamiYuka{id=3, code=CCC, value=gamma}]
```

- `LEFT JOIN` で、異なるエンティティを結合して検索できる。
- `ON` 句で結合の条件を指定できる。

# FETCH JOIN で SQL の発行回数を減らす

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/8cb029b2-b0a4-d9fc-1486-9cca7812572f.jpeg)

**実装**

```java:OnodukaKomachi.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.Table;

@Entity
@Table(name="onoduka_komachi")
public class OnodukaKomachi {
    @Id
    private Long id;
    @JoinColumn(name="shiki_eiki_id")
    private ShikiEiki shikiEiki;

    public ShikiEiki getShikiEiki() {
        return shikiEiki;
    }
    
    @Override
    public String toString() {
        return "OnodukaKomachi{" + "id=" + id + ", shikiEiki=" + shikiEiki + '}';
    }
}
```

```java:ShikiEiki.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="shiki_eiki")
public class ShikiEiki {
    @Id
    private Long id;
    private String value;

    @Override
    public String toString() {
        return "ShikiEiki{" + "id=" + id + ", value=" + value + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/94f754f7-07e9-46f1-d5f7-6437a36059a8.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/b5430452-3170-6cbc-92da-9b58749640c0.jpeg)

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/c843251b-b5ea-bad0-26f3-ee9b9b302cf5.jpeg)

**クエリ定義**

```sql:OnodukaKomachi.notFetchJoin
SELECT o
  FROM OnodukaKomachi o
```
  
```sql:OnodukaKomachi.fetchJoin
    SELECT o
      FROM OnodukaKomachi o
JOIN FETCH o.shikiEiki
```

**動作確認**

```java:JpqlEjb.java
    TypedQuery<OnodukaKomachi> query;
    
    System.out.println("<<notFetchJoin>>");
    query = this.em.createNamedQuery("OnodukaKomachi.notFetchJoin", OnodukaKomachi.class);
    query.getResultList()
         .forEach(o -> System.out.println(o.getShikiEiki()));
    
    System.out.println("<<fetchJoin>>");
    query = this.em.createNamedQuery("OnodukaKomachi.fetchJoin", OnodukaKomachi.class);
    query.getResultList()
         .forEach(o -> System.out.println(o.getShikiEiki()));
```

```text:GlassFishコンソール出力
情報:   <<notFetchJoin>>
普通:   SELECT ID, shiki_eiki_id FROM onoduka_komachi
普通:   SELECT ID, VALUE FROM shiki_eiki WHERE (ID = ?)
	bind => [1]
普通:   SELECT ID, VALUE FROM shiki_eiki WHERE (ID = ?)
	bind => [2]
普通:   SELECT ID, VALUE FROM shiki_eiki WHERE (ID = ?)
	bind => [3]
情報:   ShikiEiki{id=1, value=hoge}
情報:   ShikiEiki{id=2, value=fuga}
情報:   ShikiEiki{id=3, value=piyo}

情報:   <<fetchJoin>>
普通:   SELECT t1.ID, t1.shiki_eiki_id, t0.ID, t0.VALUE FROM shiki_eiki t0, onoduka_komachi t1 WHERE (t0.ID = t1.shiki_eiki_id)
情報:   ShikiEiki{id=1, value=hoge}
情報:   ShikiEiki{id=2, value=fuga}
情報:   ShikiEiki{id=3, value=piyo}
```

- `FETCH JOIN` を指定すると、関連するエンティティ（or 組み込み可能クラス）の情報を１回の SQL で取得するようになる。
- いわゆる、 N+1 問題を解決するための方法になる。
- ただし読み込むデータの数が多くなると、今度はメモリを大量に消費するようになるので、その辺も考慮して使うようにする。

# UPDATE, DELETE

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/e55ee233-37b0-8e7b-209f-83134200c82a.jpeg)

**実装**

```java:YasakaKanako.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="yasaka_kanako")
public class YasakaKanako {
    @Id
    private Long id;
    private String value;

    public void setValue(String value) {
        this.value = value;
    }

    @Override
    public String toString() {
        return "YasakaKanako{" + "id=" + id + ", value=" + value + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/ee166467-3c65-8fdf-dc94-b76ad435103f.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/4a025aeb-17f2-2354-4d1d-555b01f46810.jpeg)

## UPDATE

**クエリ定義**

```sql:YasakaKanako.update
UPDATE YasakaKanako y
   SET y.value = 'update'
 WHERE y.id = 2
```

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("YasakaKanako.update");
    query.executeUpdate();
```

**実行後のデータ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/e2b536c9-c19c-9a3c-dc70-5ff11e47ee53.jpeg)

- SQL と同じような感じで UPDATE 文を使える。
- UPDATE を実行するときは、 `Query#executeUpdate()` メソッドを使用する。

## DELETE

**クエリ定義**

```sql:YasakaKanako.delete
DELETE
  FROM YasakaKanako y
 WHERE y.id IN (1, 2)
```

**動作確認**

```java:JpqlEjb.java
    query = this.em.createNamedQuery("YasakaKanako.delete");
    query.executeUpdate();
```

**実行後のデータ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/14546464-4fc9-8304-82d4-44074eb83817.jpeg)

- SQL と同じような感じで DELETE 文を実行できる。

## UPDATE, DELETE を使う時の注意点

UPDATE と DELETE を JPQL で実行した場合、データベースの状態とエンティティマネージャが管理しているエンティティの状態が乖離することがある。

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/7e9e17ea-1200-518c-ed47-a6b0067a98ad.jpeg)

**動作確認**

```diff:JpqlEjb.java
+   YasakaKanako yasakaKanako = this.em.find(YasakaKanako.class, 2L);
    
    Query query = this.em.createNamedQuery("YasakaKanako.update");
    query.executeUpdate();
    
+   System.out.println(yasakaKanako);
```

```text:GlassFishコンソール出力
情報:   YasakaKanako{id=2, value=fuga}
```

**実行後のデータ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/c9f321e3-3d30-9403-bf62-576275dceb65.jpeg)

- UPDATE の実行後も、エンティティの値は更新前の `"fuga"` のままになっている。
- このように、 UPDATE, DELETE の実行は、エンティティマネージャが管理するエンティティの状態とは同期されない（同期する責務が仕様上存在しない）。
- なので、 UPDATE, DELETE を使う場合は以下のいずれかの対策をとっておくほうがいい。
    - エンティティを操作するときとは異なるトランザクションで UPDATE, DELETE を実行する。
    - エンティティを DB から取得する前に UPDATE, DELETE を実行しておく。
    - UPDATE, DELETE を実行する前に `EntityManager#flush()` と `EntityManager#clear()` を実行する。
        - `flush()` でエンティティマネージャが管理している状態を DB に反映させ、
        - `clear()` で管理対象をクリアすることで、 DB との乖離を発生させないようにしている。

# CASE 文

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/47503302-8e3c-f4e1-988b-13ec7846bfef.jpeg)

**実装**

```java:MoriyaSuwako.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="moriya_suwako")
public class MoriyaSuwako {
    @Id
    private Long id;
    private int number;

    @Override
    public String toString() {
        return "MoriyaSuwako{" + "id=" + id + ", number=" + number + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/d8d2b05d-5dd5-37e9-3473-3d5a463fe176.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/6d371fc4-a37d-3f35-4733-903b1a2283f5.jpeg)

**クエリ定義**

```sql:MoriyaSuwako.case
SELECT m.number
      ,CASE MOD(m.number, 2)
           WHEN 0 THEN 'even'
           ELSE 'odd'
       END
  FROM MoriyaSuwako m
```

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("MoriyaSuwako.case");
    
    List<Object[]> list = query.getResultList();
    
    list.stream()
        .map(Arrays::toString)
        .forEach(System.out::println);
```

```text:GlassFishコンソール出力
情報:   [111, odd]
情報:   [222, even]
情報:   [333, odd]
```

- SQL と同じ要領で CASE 文を使用することができる。

# ネイティブの SQL を実行する

## 基本

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/b3f95106-11cd-49be-21cd-7c7218ccc15b.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/84959850-43cf-cb36-0a38-9b9e474624d8.jpeg)

**クエリ定義**

```sql:native-query-simple
SELECT *
  FROM NATIVE_QUERY_TEST
```

**動作確認**

```java:JpqlEjb.java
    Query query = this.em.createNamedQuery("native-query-simple");
    
    List<Object[]> list = query.getResultList();
    
    list.stream()
        .map(Arrays::toString)
        .forEach(System.out::println);
```

- 使用している RDB ネイティブ SQL を利用することができる。
- ネイティブクエリの定義は、 JPQL と同じような要領で定義できる。
    - アノテーションで名前付きクエリを定義する場合は、 `@NamedNativeQuery` アノテーションを使用する。
    - xml で定義する場合は、 `<named-native-query>` タグを使用する。
    - `EntityManager#createNativeQuery(String)` で動的に生成することもできる。

## クエリ結果をエンティティにマッピングする

**エンティティモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/1de18c8e-b97a-413f-04e4-f167284e24a7.jpeg)

**実装**

```java:NagaeIku.java
package sample.javaee.jpa.entity.jpql;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class NagaeIku {
    @Id
    private Long id;
    private String value;

    @Override
    public String toString() {
        return "NagaeIku{" + "id=" + id + ", value=" + value + '}';
    }
}
```

**データモデル**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/c1bfa925-9ffa-a6ea-1aaf-afa020d40813.jpeg)

**データ**

![jpa.JPG](https://qiita-image-store.s3.amazonaws.com/0/28302/e48b53cc-66d5-887f-32a9-1ec4e7b773b9.jpeg)

**動作確認**

```java:JpqlEjb.java
    String sql = "SELECT ID, VALUE FROM NAGAE_IKU WHERE ID=1";
    query = this.em.createNativeQuery(sql, NagaeIku.class);
    System.out.println(query.getSingleResult());
```

```text:GlassFishコンソール出力
情報:   NagaeIku{id=1, value=hoge}
```

- `EntityManager#createNativeQuery(String, Class)` を使用することで、ネイティブクエリの結果をエンティティにマッピングすることができる。
    - `EntityManager#createNamedQuery(String, Class)` だと、実行時にエラーになった。
- ネイティブクエリの結果は、エンティティにマッピングできるように抽出しなければならない（カラム名とか）。

# ストアドプロシージャを使用する

**ストアドプロシージャ**

```sql:
CREATE PROCEDURE `sample_stored_procedure` (IN intParam INTEGER, IN stringParam VARCHAR(64), OUT outParam VARCHAR(64))
BEGIN
  SET outParam = CONCAT(intParam, ' : ', stringParam);
END
```

**名前付きストアドプロシージャ定義**

```xml:orm.xml
  <named-stored-procedure-query name="sample-stored-procedure" procedure-name="sample_stored_procedure">
    <parameter name="intParam" mode="IN" />
    <parameter name="stringParam" mode="IN" />
    <parameter name="outParam" mode="OUT" />
  </named-stored-procedure-query>
```

**動作確認**

```java:JpqlEjb.java
    StoredProcedureQuery procedure = this.em.createNamedStoredProcedureQuery("sample-stored-procedure");
    
    procedure.setParameter("intParam", 100)
             .setParameter("stringParam", "Hello Stored Procedure!!")
             .execute();
    
    String outParam = (String)procedure.getOutputParameterValue("outParam");
    System.out.println("outParam = " + outParam);
```

```text:GlassFishコンソール出力
情報:   outParam = 100 : Hello Stored Procedure!!
```

- 使用している RDB のストアドプロシージャを呼び出すことができる。
- xml で定義する場合は、 `<named-stored-procedure-query>` タグで定義する。
- アノテーションで定義する場合は、 `@NamedStoredProcedureQuery` アノテーションを使用する。

# 参考

- [JPA (Java Persistence API) - JPQLをorm.xmlに書いてみる - @lbtc_xxx lab](http://www.nailedtothex.org/articles/jpa/jpqlonorm/)
- [JSR-000338 Java Persistence 2.1 Final Release for Evaluation](http://download.oracle.com/otndocs/jcp/persistence-2_1-fr-eval-spec/index.html)
- [GlassFish勉強会で金魚本に載ってないJPQLの話をしてきた #GlassFishJP - 水まんじゅう](http://d.hatena.ne.jp/megascus/20120925/1348575449)
- [はまる！JPA（初学者向けライト版）](http://www.slideshare.net/masatoshitada7/jpa-46874399)
- [FROM clause and JOIN in JPA 2 queries (JPQL / Criteria API)](http://www.objectdb.com/java/jpa/query/jpql/from#LEFT_OUTER_INNER_JOIN_FETCH_)
- [Java Persistence/Relationships - Wikibooks, open books for an open world](http://en.wikibooks.org/wiki/Java_Persistence/Relationships#Join_Fetching)
- [MySQL - ストアドプロシージャの基本的ななにか - Qiita](http://qiita.com/setsuna82001/items/e742338eb93e3a48ba46)