JPAで前方一致検索する正しい方法 - サラリーマン技術者の調査レポート

JPAで前方一致検索(LIKE検索)する際、JPQLの書き方やプログラムからのパラメータ設定方法などいろいろと悩ましい問題がありますが、「現時点ではこれがベスト」というやり方を取り急ぎ書いてみます。

前提事項

動作確認に利用したJPA実装
- Hibernate、EclipseLink

動作確認に利用したデーターベース製品
- Oracle Database 11g R2, SQL Server 2012

前方一致検索の「前方」の文字列は、外部から任意の文字列として与えられるものとします。

CriteriaじゃなくてJPQLを使用します。
- とりあえずわかりやすいほうを採用しました。

JPA実装やデータベース製品の組み合わせによらずなるべく汎用的なコードのほうが好ましいものとします。

実装

顧客情報をあらわすCustomerクラスを用意します。

@Entity(name = "Customer")
@Table(name = "CUSTOMER_TBL")
public class Customer {
    @Id
    @Column(name = "CUST_CODE")
    private String custCode;
    ...
}

EntityManagerを使って前方一致検索(JPQLでLIKE検索)する箇所の実装は次のようになります。

private static final char ESC_CHAR = '\\';
@PersistenceContext(name = "ds")
private EntityManager em;
public List<Customer> findByCustCodePrefix(String custCode) {
    return em.createNamedQuery("Customer.findByCustCodePrefix", Customer.class)
            .setParameter("custCode", escapeSqlLikeParam(custCode, ESC_CHAR))
            .setParameter("escChar", ESC_CHAR)
            .getResultList();
}

エスケープ文字はJavaプログラムのほうで一元的に設定できるようにしました。
ただし、ここでの課題はescapeSqlLikeParamの実装です。
使用するデータベース製品によって切り替える必要がありそうです(未検証)。

orm.xmlにCustomer.findByCustCodePrefixを定義します。

<entity-mappings>
  <named-query name="Customer.findByCustCodePrefix">
    <query>SELECT c FROM Customer c WHERE c.custCode LIKE CONCAT(:custCode, '%') ESCAPE :escChar</query>
  </named-query>
  ...
</entity-mappings>

JPQLの部分だけ抜粋すると次のようになります。

SELECT
  c
FROM
  Customer c
WHERE
  c.custCode LIKE CONCAT(:custCode, '%') ESCAPE :escChar

上にも書きましたが、エスケープ文字はJavaプログラムからパラメータとして渡すようにしています。
前方一致検索の「前方」はJavaプログラムではなくJPQLで指定するようにしました。具体的には、パラメータのcustCodeの後ろにCONCAT()で"%"を結合しています。理由は考え中、、(いまのところは「なんとなく」です)
また、Eclipseのエディタだと下図のようにCONCAT()の部分がJPQLの構文エラーとなってしまいますが、エラーを無視してそのまま動かすと実際はきちんと動作します。
(Eclipseの構文解析が間違っているのか、それともJPA実装が間違って動いているのかは未確認です)
f:id:k_aruga:20131118155253p:plain

気づいたこと

JPA実装が生成するSQL文はHibernateよりもEclipseLinkのほうが圧倒的にキレイで見やすい
LIKEパラメータのエスケープ処理はデータベース製品によってまちまちみたい

終わりに

未確認・未検証事項など、ここで出た課題は今後検証していきたいと思います。