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

環境構築
JPA の基本的な話
マッピングの話
Criteria API の話

コード

JPQL とは

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

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

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

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

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

基本

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

動作確認

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);
    }
}

GlassFishコンソール出力

情報:   [Chen{id=1, value=hoge}, Chen{id=2, value=fuga}, Chen{id=3, value=piyo}]

説明

クエリを定義する

Chen.java

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

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

クエリの構文

Chenエンティティのインスタンスを全て取得するクエリ

SELECT c FROM Chen c

構文

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

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

クエリを実行する

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）で定義する

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

基本

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>

orm.xmlの配置場所

|-src/
:  |-conf/
:  |  |-persistence.xml
:  |  `-orm.xml
:  `-java/

動作確認

JpqlEjb.java

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

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/ の下に配備されるようにしている。

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

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>

my_orm.xmlの配置場所

|-src/
:  |-conf/
:  |  |-persistence.xml
:  |  `-orm.xml
:  `-java/
:     |-my_orm.xml
:     :

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>

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   FujiwaranoMokou{id=3}
情報:   FujiwaranoMokou{id=2}
情報:   FujiwaranoMokou{id=1}

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

動的にクエリを生成する

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

クエリ定義

JpqlEjb.java

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

GlassFishコンソール出力

情報:   AkiShizuha{id=1, value=hoge}
情報:   AkiShizuha{id=2, value=fuga}
情報:   AkiShizuha{id=3, value=piyo}

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

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

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

クエリ定義

LilyWhite.findSingle

SELECT l
  FROM LilyWhite l

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   LilyWhite{id=1, value=hoge}

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

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

エンティティモデル

実装（エンティティ）

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 + '}';
    }
}

データモデル

データ

クエリ定義

AliceMargatroid.getName

SELECT a.name
  FROM AliceMargatroid a

AliceMargatroid.getNameAndValue

SELECT a.name
      ,a.value
  FROM AliceMargatroid a

動作確認

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);

GlassFishコンソール出力

情報:   [hoge, fuga, piyo]
情報:   [hoge, HOGE]
情報:   [fuga, FUGA]
情報:   [piyo, PIYO]

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

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

エンティティモデル

実装

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;
}

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 + '}';
    }
}

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 + '}';
    }
}

データモデル

データ

クエリ定義

LunasaPrismriver.getMerlin

SELECT l.merlinPrismriver
  FROM LunasaPrismriver l

LunasaPrismriver.getLyrica

SELECT l.lyricaPrismriverList
  FROM LunasaPrismriver l

動作確認

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);

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 にまとめられて結果が返される（エンティティ単位にまとめられたりはしない）。

パラメータのバインド

エンティティモデル

実装

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 + '}';
    }
}

データベースモデル

データ

クエリ定義

YakumoRan.findById

SELECT y
  FROM YakumoRan y
 WHERE y.id=:id

YakumoRan.findByDate

SELECT y
  FROM YakumoRan y
 WHERE y.date=:date

動作確認

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);
        }
    }

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 句で条件を絞る

エンティティモデル

実装

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 + '}';
    }
}

データベース

比較演算子

KonpakuYoumu.equal

SELECT k
  FROM KonpakuYoumu k
 WHERE k.amount = 10

JpqlEjb.java

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

GlassFishコンソール出力

情報:   Equal (=) : KonpakuYoumu{id=1, amount=10, value=tokyo, list=[hoge, fuga, piyo]}

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

論理演算子

KonpakuYoumu.or

SELECT k
  FROM KonpakuYoumu k
 WHERE k.amount = 10
    OR k.value = 'nagoya'

JpqlEjb.java

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

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

KonpakuYoumu.between

SELECT k
  FROM KonpakuYoumu k
 WHERE k.amount BETWEEN 20 AND 30

JpqlEjb.java

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

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

KonpakuYoumu.like

SELECT k
  FROM KonpakuYoumu k
 WHERE k.value LIKE '%a'

JpqlEjb.java

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

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

KonpakuYoumu.isNull

SELECT k
  FROM KonpakuYoumu k
 WHERE k.value IS NULL

JpqlEjb.java

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

GlassFishコンソール出力

情報:   IS NULL : [KonpakuYoumu{id=4, amount=40, value=null, list=[]}]

• (ry 。
• 否定は IS NOT NULL 。

IS EMPTY

KonpakuYoumu.isEmpty

SELECT k
  FROM KonpakuYoumu k
 WHERE k.list IS EMPTY

JpqlEjb.java

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

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

KonpakuYoumu.memberOf

SELECT k
  FROM KonpakuYoumu k
 WHERE 'fizz' MEMBER OF k.list

JpqlEjb.java

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

GlassFishコンソール出力

情報:   MEMBER OF : [KonpakuYoumu{id=2, amount=20, value=osaka, list=[fizz, buzz]}]

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

EXISTS

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

クエリ定義

KawashiroNitori.exists

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

動作確認

JpqlEjb.java

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

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

エンティティモデル

実装

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 + '}';
    }
}

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 + '}';
    }
}

データモデル

データ

IN

クエリ定義

InubashiriMomiji.in

SELECT i
  FROM InubashiriMomiji i
 WHERE i.number IN (111, 333)

InubashiriMomiji.inSubquery

SELECT i
  FROM InubashiriMomiji i
 WHERE i.number IN (
       SELECT k.number
         FROM KochiyaSanae k
 )

InubashiriMomiji.inParameter

SELECT i
  FROM InubashiriMomiji i
 WHERE i.number IN :inParam

動作確認

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);

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}

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

ANY

クエリ定義

InubashiriMomiji.any

SELECT i
  FROM InubashiriMomiji i
 WHERE i.number < ANY (
       SELECT k.number
         FROM KochiyaSanae k
 )

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   InubashiriMomiji{id=1, number=111}
情報:   InubashiriMomiji{id=2, number=222}

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

ALL

クエリ定義

InubashiriMomiji.all

SELECT i
  FROM InubashiriMomiji i
 WHERE i.number > ALL (
       SELECT k.number
         FROM KochiyaSanae k
 )

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   InubashiriMomiji{id=3, number=333}
情報:   InubashiriMomiji{id=4, number=444}

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

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

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

エンティティモデル

実装

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 + '}';
    }
}

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 + '}';
    }
}

データベースモデル

データ

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

SELECT w.mystiaLoreleiList.id
  FROM WriggleNightbug w

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

例外のメッセージ

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 を記述する。

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

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

クエリ定義

WriggleNightbug.inQuery

SELECT mystiaLorelei.id
  FROM WriggleNightbug w
      ,IN(w.mystiaLoreleiList) mystiaLorelei
 WHERE w.id = 1

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   [1, 2, 3]

INNER JOIN で置き換える

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

クエリ定義

WriggleNightbug.innerJoin

    SELECT mystiaLorelei.id
      FROM WriggleNightbug w
INNER JOIN w.mystiaLoreleiList mystiaLorelei
     WHERE w.id = 1

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

文字列操作用の関数

エンティティモデル

実装

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 + '}';
    }
}

データベース

連結する（CONCAT）

クエリ定義

YakumoYukari.concat

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

データ

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   <<hoge>>

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

抽出する（SUBSTRING）

クエリ定義

YakumoYukari.substringStart

SELECT SUBSTRING(y.string, 3)
  FROM YakumoYukari y
 WHERE y.id=4

YakumoYukari.substringStartLength

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

データ

動作確認

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());

GlassFishコンソール出力

情報:   3456
情報:   34

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

トリムする（TRIM）

クエリ定義

YakumoYukari.trimDefault

SELECT TRIM(y.string)
  FROM YakumoYukari y
 WHERE y.id=2

YakumoYukari.trimLeading

SELECT TRIM(LEADING FROM y.string)
  FROM YakumoYukari y
 WHERE y.id=2

YakumoYukari.trimAsterisk

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

データ

動作確認

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() + "]");

GlassFishコンソール出力

情報:   [fuga]
情報:   [fuga  ]
情報:   [**piyo]

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

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

クエリ定義

YakumoYukari.lower

SELECT LOWER(y.string)
  FROM YakumoYukari y
 WHERE y.id=5

YakumoYukari.upper

SELECT UPPER(y.string)
  FROM YakumoYukari y
 WHERE y.id=5

データ

動作確認

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());

GlassFishコンソール出力

情報:   fizzbuzz
情報:   FIZZBUZZ

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

文字数を取得する（LENGTH）

クエリ定義

YakumoYukari.length

SELECT LENGTH(y.string)
  FROM YakumoYukari y
 WHERE y.id=1

データ

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   4

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

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

クエリ定義

YakumoYukari.locate

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

データ

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   3

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

算術関数

エンティティモデル

実装

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 + '}';
    }
}

データベースモデル

絶対値を取得する（ABS）

クエリ定義

IbukiSuika.abs

SELECT ABS(i.number)
  FROM IbukiSuika i
 WHERE i.id=1

データ

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   10.2

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

平方根を取得する（SQRT）

クエリ定義

IbukiSuika.sqrt

SELECT SQRT(i.number)
  FROM IbukiSuika i
 WHERE i.id=2

データ

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   5.0

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

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

クエリ定義

IbukiSuika.mod

SELECT MOD(i.number, 5)
  FROM IbukiSuika i
 WHERE i.id=3

データ

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   2

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

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

クエリ定義

IbukiSuika.size

SELECT SIZE(i.list)
  FROM IbukiSuika i
 WHERE i.id=4

データ

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   3

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

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

クエリ定義

IbukiSuika.index

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

データ

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   fuga

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

日付関数

クエリ定義

KamishirasawaKeine.currentDate

SELECT CURRENT_DATE
  FROM KamishirasawaKeine k
 WHERE k.id=1

KamishirasawaKeine.currentTime

SELECT CURRENT_TIME
  FROM KamishirasawaKeine k
 WHERE k.id=1

KamishirasawaKeine.currentTimestamp

SELECT CURRENT_TIMESTAMP
  FROM KamishirasawaKeine k
 WHERE k.id=1

動作確認

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);

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 で、それぞれ実行時の日付・時刻・日時を取得できる。

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

エンティティモデル

実装

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 + '}';
    }
}

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 + '}';
    }
}

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 + '}';
    }
}

データベースモデル

データ

クエリ定義

ReisenUdongeinInaba.parent

SELECT r
  FROM ReisenUdongeinInaba r
 WHERE TYPE(r.inabaTewi) = ParentInabaTewi

ReisenUdongeinInaba.child

SELECT r
  FROM ReisenUdongeinInaba r
 WHERE TYPE(r.inabaTewi) = ChildInabaTewi

動作確認

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());

GlassFishコンソール出力

情報:   ReisenUdongeinInaba{id=1, inabaTewi=ParentInabaTewi{id=1}}
情報:   ReisenUdongeinInaba{id=2, inabaTewi=ChildInabaTewi{id=2}}

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

集約関数

エンティティモデル

実装

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;
}

データベースモデル

データ

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

クエリ定義

HouraisanKaguya.count

SELECT COUNT(h)
  FROM HouraisanKaguya h

動作確認

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());
    }

GlassFishコンソール出力

情報:   count : 4, class : class java.lang.Long

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

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

クエリ定義

HouraisanKaguya.stringMax

SELECT MAX(h.string)
  FROM HouraisanKaguya h

HouraisanKaguya.numberMax

SELECT MAX(h.number)
  FROM HouraisanKaguya h

HouraisanKaguya.min

SELECT MIN(h.number)
  FROM HouraisanKaguya h

動作確認

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);

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）

クエリ定義

HouraisanKaguya.avg

SELECT AVG(h.number)
  FROM HouraisanKaguya h

動作確認

JpqlEjb.java

    query = this.em.createNamedQuery("HouraisanKaguya.avg");
    this.print("avg", query);

GlassFishコンソール出力

情報:   avg : 26.0, class : class java.lang.Double

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

合計値を取得する（SUM）

クエリ定義

HouraisanKaguya.sum

SELECT SUM(h.number)
  FROM HouraisanKaguya h

動作確認

JpqlEjb.java

    query = this.em.createNamedQuery("HouraisanKaguya.sum");
    this.print("sum", query);

GlassFishコンソール出力

情報:   sum : 104, class : class java.lang.Long

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

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

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

クエリ定義

HinanaiTenshi.functions

SELECT FUNCTION('FORMAT', h.number, 3)
  FROM HinanaiTenshi h

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   123,456.000
情報:   345.123
情報:   56,789.123

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

ORDER BY でソートする

エンティティモデル

実装

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 + '}';
    }
}

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 + " }";
    }
}

データベースモデル

データ

クエリ定義

SaigyojiYuyuko.orderByIdDesc

  SELECT s
    FROM SaigyojiYuyuko s
ORDER BY s.id DESC

SaigyojiYuyuko.orderByNumberDesc

  SELECT s
    FROM SaigyojiYuyuko s
ORDER BY s.embeddedSaigyojiYuyuko.number DESC

動作確認

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);

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 で集約する

エンティティモデル

実装

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 + '}';
    }
}

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) {
        // 省略
    }
}

データベースモデル

データ

クエリ定義

YagokoroEirin.groupBy

  SELECT y.value
        ,COUNT(y)
    FROM YagokoroEirin y
GROUP BY y.value

YagokoroEirin.groupByObject

  SELECT y.embeddedValue
        ,COUNT(y)
    FROM YagokoroEirin y
GROUP BY y.embeddedValue

動作確認

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);

GlassFishコンソール出力

情報:   [fuga, 2]
情報:   [hoge, 3]
情報:   [piyo, 1]
情報:   [EmbeddedValue{number=100}, 1]
情報:   [EmbeddedValue{number=200}, 2]
情報:   [EmbeddedValue{number=300}, 3]

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

HAVING 句

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

クエリ定義

AkiMinoriko.having

  SELECT a.number
        ,COUNT(a.number)
    FROM AkiMinoriko a
GROUP BY a.number
  HAVING 2 < COUNT(a.number)

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   [100, 4]
情報:   [200, 3]

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

DISTINCT

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

クエリ定義

KagiyamaHina.distinct

  SELECT 
DISTINCT k.number
    FROM KagiyamaHina k

動作確認

JpqlEjb.java

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

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

サブクエリ

エンティティモデル

実装

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 + '}';
    }
}

データベースモデル

データ

クエリ定義

ShameimaruAya.findMax

SELECT s1
  FROM ShameimaruAya s1
 WHERE s1.number = (
         SELECT MAX(s2.number)
           FROM ShameimaruAya s2
       )

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   ShameimaruAya{id=2, number=30}

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

LEFT JOIN

エンティティモデル

実装

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 + '}';
    }
}

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 + '}';
    }
}

データモデル

データ

クエリ定義

MedicineMelancholy.leftJoinOnCode

   SELECT m
         ,k
     FROM MedicineMelancholy m
LEFT JOIN KazamiYuka k
       ON k.code = m.code

動作確認

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);

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 の発行回数を減らす

エンティティモデル

実装

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 + '}';
    }
}

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 + '}';
    }
}

データモデル

データ

クエリ定義

OnodukaKomachi.notFetchJoin

SELECT o
  FROM OnodukaKomachi o

OnodukaKomachi.fetchJoin

    SELECT o
      FROM OnodukaKomachi o
JOIN FETCH o.shikiEiki

動作確認

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()));

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

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

UPDATE

クエリ定義

YasakaKanako.update

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

動作確認

JpqlEjb.java

    Query query = this.em.createNamedQuery("YasakaKanako.update");
    query.executeUpdate();

実行後のデータ

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

DELETE

クエリ定義

YasakaKanako.delete

DELETE
  FROM YasakaKanako y
 WHERE y.id IN (1, 2)

動作確認

JpqlEjb.java

    query = this.em.createNamedQuery("YasakaKanako.delete");
    query.executeUpdate();

実行後のデータ

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

UPDATE, DELETE を使う時の注意点

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

データ

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   YasakaKanako{id=2, value=fuga}

実行後のデータ

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

CASE 文

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

クエリ定義

MoriyaSuwako.case

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

動作確認

JpqlEjb.java

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

GlassFishコンソール出力

情報:   [111, odd]
情報:   [222, even]
情報:   [333, odd]

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

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

基本

データモデル

データ

クエリ定義

native-query-simple

SELECT *
  FROM NATIVE_QUERY_TEST

動作確認

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) で動的に生成することもできる。

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

エンティティモデル

実装

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 + '}';
    }
}

データモデル

データ

動作確認

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());

GlassFishコンソール出力

情報:   NagaeIku{id=1, value=hoge}

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

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

ストアドプロシージャ

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

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

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>

動作確認

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);

GlassFishコンソール出力

情報:   outParam = 100 : Hello Stored Procedure!!

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

参考

• JPA (Java Persistence API) - JPQLをorm.xmlに書いてみる - @lbtc_xxx lab
• JSR-000338 Java Persistence 2.1 Final Release for Evaluation
• GlassFish勉強会で金魚本に載ってないJPQLの話をしてきた #GlassFishJP - 水まんじゅう
• はまる！JPA（初学者向けライト版）
• FROM clause and JOIN in JPA 2 queries (JPQL / Criteria API)
• Java Persistence/Relationships - Wikibooks, open books for an open world
• MySQL - ストアドプロシージャの基本的ななにか - Qiita