iBatis奮闘記-0005 (動的SQL(Dynamic Mapped Statement))

今回は、動的SQLについてです。
iBatisには、動的SQLを簡単に作成する方法が用意されています。
JDBCを使用して文字列を必死になって連結よりも楽にできるようになります。

動的SQLがよく使用される場面は、検索画面などです。
これらの画面などでは、複数の項目があったりなかったりした場合に応じて
検索条件を可変で切り替えないといけません。

方法は、今までと変わらずSQLマッピング設定ファイルにおいて行います。

今回使用するテーブルDDLです。

/* vim:set ts=4 sw=4 et ws is nowrap ft=sql fenc=cp932 ff=dos: */
DROP TABLE CATEGORY IF EXISTS;
CREATE TABLE CATEGORY(
     id   int         auto_increment
    ,name varchar(40) not null
    ,primary key(id)
);

insert into CATEGORY (name) values ('メモ');
insert into CATEGORY (name) values ('仕事');
insert into CATEGORY (name) values ('Java');
insert into CATEGORY (name) values ('Ruby');
insert into CATEGORY (name) values ('Python');

/* vim:set ts=4 sw=4 et ws is nowrap ft=sql fenc=cp932 ff=dos: */
DROP TABLE MEMO IF EXISTS;
CREATE TABLE MEMO(
     id          int           auto_increment
    ,category_id int           not null
    ,title       varchar(100)  not null
    ,contents    varchar(4000)
    ,created     timestamp
    ,updated     timestamp
    ,primary key(id)
    ,foreign key(category_id) references CATEGORY(id)
);

insert into MEMO (category_id, title, contents, created) values (1, 'メモ',               'やっと梅雨明け〜.',                   now());
insert into MEMO (category_id, title, contents, created) values (2, '案件情報',           'ｘｘｘｘｘｘｘｘｘｘｘ',              now());
insert into MEMO (category_id, title, contents, created) values (3, 'H2 Database Engine', 'なかなか使いやすい.',                 now());
insert into MEMO (category_id, title, contents, created) values (3, 'Tapestry',           '結構難しい・・・',                    now());
insert into MEMO (category_id, title, contents, created) values (3, 'Echo2',              '最近お気に入り',                      now());
insert into MEMO (category_id, title, contents, created) values (3, 'Click',              'すごく簡単で使いやすい',              now());
insert into MEMO (category_id, title, contents, created) values (3, 'Wicket',             'まだ使っていないので不明・・',        now());
insert into MEMO (category_id, title, contents, created) values (4, 'Ruby on Rails',      'こないだ2歳になったらしい。',         now());
insert into MEMO (category_id, title, contents, created) values (5, 'Turbo Gears',        'Python版 Ruby on Railsみたいな感じ.', now());

MEMOテーブルは、1対1の関係でCATEGORYテーブルと関連を持っています。
メモを検索する画面が存在すると考えてください。

各テーブルに対応するJAVAクラスは、以下です。

// vim:set ts=4 sw=4 et ws is nowrap ft=java fenc=cp932 ff=dos:
package gsf.samples.ibatis.sample004;

import java.io.*;

import org.apache.commons.lang.builder.*;

/**
 * CATEGORYテーブルに対応するドメインクラス.<br/>
 *
 * @author gsf_zero1
 *
 */
public class Category implements Serializable{

    /** ID */
    private Integer id;

    /** NAME */
    private String  name;

    /**
     * コンストラクタ.<br/>
     *
     */
    public Category(){
    }
    
    /**
     * Get id.
     *
     * @return id as Integer.
     */
    public Integer getId(){
        return this.id;
    }
    
    /**
     * Set id.
     *
     * @param id the value to set.
     */
    public void setId(Integer id){
        this.id = id;
    }
    
    /**
     * Get name.
     *
     * @return name as String.
     */
    public String getName(){
        return this.name;
    }
    
    /**
     * Set name.
     *
     * @param name the value to set.
     */
    public void setName(String name){
        this.name = name;
    }

    /**
     * オブジェクトの文字列表現を返す.<br/>
     *
     * @return 文字列表現
     *
     */
    @Override
    public String toString(){
        return new ReflectionToStringBuilder(this).toString();
    }
}

// vim:set ts=4 sw=4 et ws is nowrap ft=java fenc=cp932 ff=dos:
package gsf.samples.ibatis.sample004;

import java.io.*;
import java.sql.*;

import org.apache.commons.lang.builder.*;

/**
 * MEMOテーブルに対応するドメインクラス.<br/>
 *
 * @author gsf_zero1
 *
 */
public class Memo implements Serializable{

    /** ID */
    private Integer   id;

    /** CATEGORY_ID */
    private Category  category;

    /** TITLE */
    private String    title;

    /** CONTENTS */
    private String    contents;

    /** CREATED */
    private Timestamp created;
    
    /** UPDATED */
    private Timestamp updated;

    /**
     * コンストラクタ.<br/>
     *
     */
    public Memo(){
    }
    
    /**
     * Get id.
     *
     * @return id as Integer.
     */
    public Integer getId(){
        return this.id;
    }
    
    /**
     * Set id.
     *
     * @param id the value to set.
     */
    public void setId(Integer id){
        this.id = id;
    }
    
    /**
     * Get category.
     *
     * @return category as Category.
     */
    public Category getCategory(){
        return this.category;
    }
    
    /**
     * Set category.
     *
     * @param category the value to set.
     */
    public void setCategory(Category category){
        this.category = category;
    }
    
    /**
     * Get title.
     *
     * @return title as String.
     */
    public String getTitle(){
        return this.title;
    }
    
    /**
     * Set title.
     *
     * @param title the value to set.
     */
    public void setTitle(String title){
        this.title = title;
    }
    
    /**
     * Get contents.
     *
     * @return contents as String.
     */
    public String getContents(){
        return this.contents;
    }
    
    /**
     * Set contents.
     *
     * @param contents the value to set.
     */
    public void setContents(String contents){
        this.contents = contents;
    }
    
    /**
     * Get created.
     *
     * @return created as Timestamp.
     */
    public Timestamp getCreated(){
        return this.created;
    }
    
    /**
     * Set created.
     *
     * @param created the value to set.
     */
    public void setCreated(Timestamp created){
        this.created = created;
    }
    
    /**
     * Get updated.
     *
     * @return updated as Timestamp.
     */
    public Timestamp getUpdated(){
        return this.updated;
    }
    
    /**
     * Set updated.
     *
     * @param updated the value to set.
     */
    public void setUpdated(Timestamp updated){
        this.updated = updated;
    }

    /**
     * オブジェクトの文字列表現を返す.<br/>
     *
     * @return 文字列表現
     *
     */
    @Override
    public String toString(){
        return new ReflectionToStringBuilder(this).toString();
    }
}

動的SQLは、SQLマッピング設定ファイル内のSQLステートメント内にて、
特定のタグを使用して記述します。以下のルールがあります。

動的SQLが存在する部分には、<dynamic>要素にて、スタートさせる。（実際には、指定しなくてもいい場合もあるのですが指定しておいた方がいいでしょう。）
prepend属性は、その条件がtrueとなった場合に、補われるワードを指定する。(andやorなど）
property属性には、対象のオブジェクトのプロパティを指定する。
compareValue属性には、比較値を指定する。
<や>などが式の中に存在する場合は、CDATAセクションで囲む。（パースエラーとなるため）

<?xml version="1.0" encoding="Windows-31J"?>
<!-- vim:set ts=4 sw=4 et ws is nowrap ft=xml fenc=cp932 ff=dos: -->
<!DOCTYPE sqlMap
    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="DynamicSqlSample">

    <typeAlias alias="Category" type="gsf.samples.ibatis.sample004.Category"/>
    <typeAlias alias="Memo"     type="gsf.samples.ibatis.sample004.Memo"/>

    <resultMap id="memoResultMap" class="Memo">
        <result property="id"            column="id"/>
        <result property="category.id"   column="category_id"/>
        <result property="category.name" column="category_name"/>
        <result property="title"         column="title"/>
        <result property="contents"      column="contents"/>
        <result property="created"       column="created"/>
        <result property="updated"       column="updated"/>
    </resultMap>

    <select id="searchMemo" parameterClass="Memo" resultMap="memoResultMap">
        select
             memo.id          as id
            ,memo.category_id as category_id
            ,category.name    as category_name
            ,memo.title       as title
            ,memo.contents    as contents
            ,memo.created     as created
            ,memo.updated     as updated
        from
             category
            ,memo
        where
            memo.category_id = category.id
            <dynamic>
                <isNotNull prepend="and" property="id">
                    memo.id = #id#
                </isNotNull>
                <isNotNull prepend="and" property="category.id">
                    category.id = #category.id#
                </isNotNull>
                <isNotNull prepend="and" property="title">
                    memo.title like '%' || #title# || '%'
                </isNotNull>
                <isNotNull prepend="and" property="contents">
                    memo.contents like '%' || #contents# || '%'
                </isNotNull>
            </dynamic>
    </select>
</sqlMap>

今回は、大小比較などが存在しないため、isNotNull要素以外は出てきていませんが、
そのほかにもいろいろあります。詳しくは、iBatisのドキュメントを参照願います。
よく使うところでは、

isNull
isEmpty
isNotEmpty
isEqual
isNotEqual
isGreaterThan
isLessThan
isGreaterEqual
isLessEqual

といったところでしょう。使い方は、ほとんど同じです。大小比較などの場合の要素には、
compareValue要素が必要となります。

後は、マッピング設定ファイルをSqlMap設定ファイルに定義します。

    <sqlMap resource="gsf/samples/ibatis/sample004/DynamicSql.ibatis.xml"/>

最後に、動作確認をしてみます。

// vim:set ts=4 sw=4 et ws is nowrap ft=java fenc=cp932 ff=dos:
package gsf.samples.ibatis.sample004;

import java.util.*;

import com.ibatis.common.resources.*;
import com.ibatis.sqlmap.client.*;

public class IBatisSample004{

    public static void main(String[] args) throws Exception{

        SqlMapClient sqlMap 
                        = SqlMapClientBuilder.buildSqlMapClient(
                                Resources.getResourceAsReader("SqlMapConfig.xml"));

        final Integer CATEGORY_JAVA = 3;

        Memo searchTarget = new Memo();
        Category category = new Category();
        category.setId(CATEGORY_JAVA);
        searchTarget.setCategory(category);
        searchTarget.setTitle("Click");

        for(Memo aMemo : (List<Memo>) sqlMap.queryForList("searchMemo", searchTarget)){
            System.out.println(aMemo);
        }
    }
}

サンプルデータの中から、カテゴリーがJAVAで、タイトルに"Click"を含む
エントリーを検索しています。
実行すると、一件だけヒットし、表示されます。

ちなみに、INなどを使用する場合は、ある特定のリストを
ループして使用する必要があると思いますが、その場合には、iterate要素が使用できるっぽいです。
今度これについてもサンプルを作成してみます。

いろいろ備忘録日記

主に .NET とか Go とか Flutter とか Python絡みのメモを公開しています。

iBatis奮闘記-0005 (動的SQL(Dynamic Mapped Statement))