MyBatisで動的SQL文を作る【複数のワードで検索できるようにする】

やりたいこと

キーワードを

• 全角スペース→半角スペースに変換（編集①）
• 2個以上の半角スペース→1個の半角スペースに変換（編集②）
• 先頭と末尾のスペースを削除（編集③）

したうえで、カテゴリーと併せてキーワードをOR検索（スペースで区切ったいずれかのキーワードが含まれているものを検索）したい。

環境

使用OS-Windows10
使用ツール-Spring Tool Suite 4 4.6.2
テンプレートエンジン-Thymeleaf
フレームワーク-mybatis

結果（xmlファイルの記述）

<select id="findByCategoryIdAndProductName" 
resultType="対応するドメインのファイルパス">

    SELECT * FROM item_table
      WHERE cate_id = #{category}
      AND

      <foreach collection="keywords" item="keyword" open="(" close=")" index="i" separator="" >
        <choose>
            <when test="i == 0">
                (item_name LIKE '%${keyword}%')
            </when>
            <otherwise>
                OR (item_name LIKE '%${keyword}%')
            </otherwise>
        </choose>
      </foreach> 
</select>

これでふーんなるほどーってなった人は以下は必要ないと思います。
何やってるかさっぱり分からんって人は読み進めてください。私はmybatisを使うことでどんな恩恵を受けるかもよく分かってなかったのですごく基本的なことから書いていきます。

SQLとは

コンピュータ言語のひとつ（プログラミング言語ではない）。
データベースとの接続の際に使用し、以下が基本的なCRUD機能の構文。

CREATE(登録)

INSERT INTO テーブル名(カラム名, カラム名,...) values('挿入データ', '挿入データ', ...);
INSERT INTO テーブル名 VALUES('挿入データ', '挿入データ', ...),('挿入データ', '挿入データ', ...);

READ(参照)

SELECT カラム名 FROM テーブル名;

UPDATE(更新)

UPDATE テーブル名 SET カラム名 = '上書き内容';

DELETE(削除)

DELETE FROM テーブル名;

これらの文章の後ろにWHEREをつけてより詳細なデータ指定等をおこなう。
本筋からそれるのでSQLについてはここまでにします。

MyBatisとは

MyBatisの公式サイトによると

MyBatis とは？

MyBatis はカスタム SQL、ストアドプロシージャ、高度なマッピング処理に対応した優れた永続化フレームワークです。 MyBatisを使うことで、直接 JDBC を扱うコードを書いたり、クエリ引数やクエリ結果を手動で設定する必要がほとんどなくなります。 MyBatis の設定やデータベースレコードと Java オブジェクトの関連付けは、XML またはアノテーションを使って行うことができます。

ざっくり言うと複雑なSQLを書くときに力を発揮するフレームワークらしい。

本題

まずはユーザー側から入力された検索キーワードを冒頭の形に編集するためにコントローラーでメソッドを用意します。

キーワードの処理

検索キーワードはformクラスでString keywordsとして宣言しています。

話は少しそれますがformクラスは情報をまとめて送りたいときに用意されるファイル。中身としてはセッターとゲッターがあるだけ。

public String search(SampleForm f) {
  String keywords = f.getKeywords().replaceAll("　", " ").replaceAll("\\s{2,}", " ").trim();
}

replaceAllメソッドは引数を２つ取り、特定の文字列を一括置換するもの。
1つ目のメソッドreplaceAll("　", " ")では全角スペースを半角スペースに変換（編集①）し
2つ目のメソッドreplaceAll("\\s{2,}", " ")では2個以上の半角スペース→1個の半角スペースに変換（編集②）し
3つ目のメソッドtrim()で前後の空白を除去しています。

replaceAllメソッドについて

1つ目は見てわかると思いますが、2つ目については「2個以上の」という条件をつけたいのでメタ文字にしています。

• 半角スペース→\s　ただし\単体はエスケープとして扱われてしまうので \\s とする
• 直前のパターンのn回以上繰り返し（最長一致）→{n,}→今回であれば2回以上なので {2,}

参考：正規表現サンプル集

検索用SQL文について

そもそもどんなSQLを書けばいいか、という問題。

「あるキーワードを含む語をOR検索する」というのは例えばキーワード「あい」「うえ」「お」で調べると、文字列のどこかに「あい」「うえ」「お」のどれかを含む語をすべて抽出という動きのこと。

まずAという語を含む値をすべて抽出する文は

SELECT * FROM テーブル名 WHERE カラム名 LIKE '%A%';

「あ」「い」「う」という語を含む値をすべて抽出する文は

SELECT * FROM テーブル名 WHERE (カラム名 LIKE '%あい%')
                           OR (カラム名 LIKE '%うえ%')
                           OR (カラム名 LIKE '%お%');

参考：1つの列に対して複数の検索条件を指定する方法
括弧()はつけなくても成功したのでどっちでもいいかも。

ここで困った

検索キーワードはなしの場合もあるし、複数の場合もある。しかもいくつ入力されるか分からない。
これがLIKE句での検索じゃなく一致（＝）条件であればINでまとめてつなげることができそうだけど、今回は無理。

複数のものに対して同じ処理といえば繰り返し処理だけど、javaで操作できるのは%で囲んだ「あい」部分のみ。

解決策

これはフレームワークがMyBatisだからできる解決策で、SQL文内に条件をつけたりに繰り返し処理を書くことができる。
今回使うのは<foreach> <choose> <when> <otherwise>の4つ。

ちなみにほかには<if> <trim> <where> <set> というタグが用意されている。

foreach

<foreach>タグの使い方から。

まず大前提として他のプログラミング言語とも共通することですが
foreach文 は別名 拡張for文 とも呼ばれ、配列やコレクションといった複数の要素を持つものの値を順に取り出して処理をおこないます。

<foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
     <!--ここに繰り返したい処理を書く-->
</foreach>

処理を記述する前に、6つのプロパティを指定します。openとcloseについては場合によっては不要。

• item=：foreachタグの中で扱う値の名前を指定
• index=：インデックスの名前を指定
• collection=：繰り返し処理をしたいコレクションを指定
• open=：処理の始まりになにか付け足す場合はここを指定
• close=：処理の終わりになにか付け足す場合はここを指定
• separator=：コレクションに格納されているものがどんな語句で区切られているかを指定

今回はキーワードをkeywordsという変数に格納し、foreach文の中ではそれぞれの値をkeywordとして指定します。
また検索の際カテゴリーと一緒に検索をかけるので、SELECT文の大枠はこんな感じ。

SELECT * FROM item_table
　　WHERE cate_id = #{category} AND

    <foreach collection="keywords" item="keyword" open="(" close=")" index="i" separator="" >
      <!--ここに処理内容を書く--> 
    </foreach>

閑話：openとcloseは必要か

結論から言えば今回は必要。

例えばカテゴリーが1、検索キーワードで「あい」と「うえ」が指定された場合、foreach処理の全体を括弧でくくらないと

SELECT * FROM item_table 
WHERE cate_id = 1 AND カラム名 LIKE '%あい%' OR カラム名 LIKE '%うえ%'

このような処理になり、AND（かつ）とOR（または）がごっちゃになってしまってどんな条件でデータを抽出すればいいのか機械が判断できずエラーになる。

SELECT * FROM item_table 
WHERE cate_id = 1 AND (カラム名 LIKE '%あい%' OR カラム名 LIKE '%うえ%')

括弧（）できちんとくくられていればちゃんと分かる。

ちなみにもし検索キーワードになにも入っていなければ

SELECT * FROM item_table WHERE cate_id = 1 AND (カラム名 LIKE '%%')

こんな処理になる。エラーは出ないので問題なし。

choose、when、otherwise

<when>タグは if、<otherwise>タグは else です。
<if>タグとの違いはelseにあたる処理が必要かどうかなので、場合によって使い分けてください。

<when> <otherwise>を使うときは全体を<choose>タグで囲みます。
<if>タグの時は<choose>タグは使いません。

一般的なif文は if(条件式) ですが、条件式にあたる部分はtest=""で指定します。

最終的なSQL文

今回のコードを考えるため、先ほどのSELECT文をもう一度見てみます。

SELECT * FROM テーブル名 WHERE (カラム名 LIKE '%あい%')
                           OR (カラム名 LIKE '%うえ%')
                           OR (カラム名 LIKE '%お%');

こんな感じで1つ目のキーワードはそのまま％で囲み、それ以降のキーワードは先頭にORをつけたうえで％内にキーワードを入れたいです。
↑の文章をwhenの条件式に指定します。

<select id="findByCategoryIdAndProductName" 
resultType="対応するドメインのファイルパス">
  SELECT * FROM item_table
    WHERE cate_id = #{category}
    AND 
　  <!--keywordsから値を順に取り出して繰り返し処理をする-->
    <foreach collection="keywords" item="keyword" open="(" close=")" index="i" separator="" >

      <!--whenとotherwiseを使いたいのでchooseで囲む-->
      <choose>
        <!--インデックス番号0の場合の処理-->
        <when test="i == 0">
            (item_name LIKE '%${keyword}%')
        </when>
        <!--それ以外の場合-->
        <!--条件文の前にORをつけて処理をする-->  
        <otherwise>
            OR (item_name LIKE '%${keyword}%')
        </otherwise>
      </choose>
  </foreach>
</select>

Mapperファイルの設定

xmlファイルは同名のMapperファイルが存在することで記述した処理が実行できる（裏を返せば○○Mapper.xmlを記述することで○○Mapper.javaの記述内容を簡潔にできる）。

同名Mapperクラスでは以下のように記述する。

xmlファイルと同名.java

//importは省略

@Mapper
public interface 〇〇Mapper {
    List<MstProduct> findByCategoryIdAndProductName(
    @Param("category") long category,
    @Param("keywords") String[] keywords);
    //xmlでコレクションとして扱っているのでString型ではなくString[]型にする
}

FindBy~はステートメント名と言って、javaファイルで書かれているメソッドとxmlファイルのSQL文を紐づけるために命名するもの。

コントローラーで検索結果を取得する

最初に紹介したコントローラーの記述では全然足りない。まあ今やったここと言えばキーワードをちまちま編集しただけなので当然なんですが。

情報を取得する

public String search(SampleForm f) {
  //keywordsが宣言されているだけ
  String keywords = f.getKeywords().replaceAll("　", " ").replaceAll("\\s{2,}", " ").trim();
}

ここに記述を足していきます。まずは情報を取得する機能のみ。
今回はテンプレートエンジンにTymeleafを採用し、htmlで属性をth:each="item:${items}"として呼び出し各カラムの情報を出力しています。
ここら辺の話はあまりにも重量があるのでTymeleafでググってください。

//importやクラス、他のメソッドは省略

public String search(SampleForm f, Model m) {
  String keywords = f.getKeywords().replaceAll("　", " ").replaceAll("\\s{2,}", " ").trim();
  // 商品情報をリスト取得
  List<Item> items;

  //search.html内の"items"という文字列に対してitemsリストの情報を送る
  m.addAttribute("items", items);

  //まだどこにも検索機能はない
  //search.htmlへ
  return "search";
}

検索機能を実装

検索機能についての記述を追記。

//importやクラス、他のメソッドは省略

public String search(SampleForm f, Model m) {
  String keywords = f.getKeywords().replaceAll("　", " ").replaceAll("\\s{2,}", " ").trim();
  // 商品情報をリスト取得
  List<Item> items;

  //検索処理
  //つまり〇〇Mapper.javaへcategoryとkeywordsの情報を渡し
  //さらに〇〇Mapper.xmlへ情報を渡しSELECT処理、その結果をitemsリストへ代入する
  items = 〇〇Mapper.findByCategoryIdAndProductName(
          f.getCategory(),
          keywords);

  //search.html内の"items"という文字列に対してitemsリストの情報を送る
  m.addAttribute("items", items);

  //search.htmlへ
  return "search";
}

検索機能を実装しました。ただしこれで完成ではありません。

この状態ではkeywordsは検索キーワードが半角スペースでつながっただけの状態なので、foreachでの繰り返し処理ができず、また、型の不一致でコンパイルエラーがでます。

keywordsを整える

//importやクラス、他のメソッドは省略

public String search(SampleForm f, Model m) {
  String keywords = f.getKeywords().replaceAll("　", " ").replaceAll("\\s{2,}", " ").trim();
  // 商品情報をリスト取得
  List<Item> items;

  //検索処理
  //つまり〇〇Mapper.javaへkeywordsの情報を渡し
  //さらに〇〇Mapper.xmlへ情報を渡しSELECT処理、その結果をitemsリストへ代入する
  items = 〇〇Mapper.findByCategoryIdAndProductName(
          f.getCategory(),
          //splitメソッドで半角スペースで文字列を分割し、配列にして返す
          keywords.split(" "));

  //search.html内の"items"という文字列に対してitemsリストの情報を送る
  m.addAttribute("items", items);

  //search.htmlへ
  return "search";
}

これで完成です。わーい

public String search(SampleForm f, Model m) {
  String keywords = f.getKeywords().replaceAll("　", " ").replaceAll("\\s{2,}", " ").trim();
  List<Item> items;
  items = 〇〇Mapper.findByCategoryIdAndProductName(f.getCategory(), keywords.split(" "));

  m.addAttribute("items", items);

  return "search";
}