いろいろ備忘録日記

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

iBatis奮闘記-0010 (結果をMapで受け取る)

iBatisには、クエリの結果をMapで受け取る機能があります。

SqlMapClientの以下のメソッドを使用します。

Map queryForMap(statement, parameterClass, keyProperty);

以下サンプルです。

[DDL]

 -- vim:set ts=4 sw=4 et ws is nowrap ft=sql:
CREATE TABLE SAMPLE008_TEST_TABLE(
     id int auto_increment
    ,key_column1 varchar(10) not null
    ,key_column2 varchar(10) not null
    ,value1 varchar(100)
    ,value2 timestamp default now()
    ,primary key(id)
    ,unique(key_column1)
    ,unique(key_column2)
);

 --
 -- サンプルデータ
 --
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key1-1', 'key1-2', 'value1');
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key2-1', 'key2-2', 'value2');
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key3-1', 'key3-2', 'value3');
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key4-1', 'key4-2', 'value4');
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key5-1', 'key5-2', 'value5');
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key6-1', 'key6-2', 'value6');
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key7-1', 'key7-2', 'value7');
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key8-1', 'key8-2', 'value8');
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key9-1', 'key9-2', 'value9');
insert into SAMPLE008_TEST_TABLE (key_column1, key_column2, value1) values ('key10-1', 'key10-2', 'value10');

[データクラス]

// vim:set ts=4 sw=4 et ws is nowrap ft=java:
package gsf.samples.ibatis.sample008;

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

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

public class Sample008TestTable implements Serializable{

    private Integer   id;
    private String    keyColumn1;
    private String    keyColumn2;
    private String    value1;
    private Timestamp value2;

    public Sample008TestTable(){
        // nop;
    }
    
    /**
     * 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 keyColumn1.
     *
     * @return keyColumn1 as String.
     */
    public String getKeyColumn1(){
        return this.keyColumn1;
    }
    
    /**
     * Set keyColumn1.
     *
     * @param keyColumn1 the value to set.
     */
    public void setKeyColumn1(String keyColumn1){
        this.keyColumn1 = keyColumn1;
    }
    
    /**
     * Get keyColumn2.
     *
     * @return keyColumn2 as String.
     */
    public String getKeyColumn2(){
        return this.keyColumn2;
    }
    
    /**
     * Set keyColumn2.
     *
     * @param keyColumn2 the value to set.
     */
    public void setKeyColumn2(String keyColumn2){
        this.keyColumn2 = keyColumn2;
    }
    
    /**
     * Get value1.
     *
     * @return value1 as String.
     */
    public String getValue1(){
        return this.value1;
    }
    
    /**
     * Set value1.
     *
     * @param value1 the value to set.
     */
    public void setValue1(String value1){
        this.value1 = value1;
    }
    
    /**
     * Get value2.
     *
     * @return value2 as Timestamp.
     */
    public Timestamp getValue2(){
        return this.value2;
    }
    
    /**
     * Set value2.
     *
     * @param value2 the value to set.
     */
    public void setValue2(Timestamp value2){
        this.value2 = value2;
    }

    @Override
    public String toString(){
        return new ReflectionToStringBuilder(this).toString();
    }
}

[SQLマッピングファイル]

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

<sqlMap namespace="QueryForMap">

    <typeAlias alias="Sample008TestTable" type="gsf.samples.ibatis.sample008.Sample008TestTable"/>

    <select id="findAllSample008TestTable" resultClass="Sample008TestTable">
        select
             id
            ,key_column1 as keyColumn1
            ,key_column2 as keyColumn2
            ,value1
            ,value2
        from
            SAMPLE008_TEST_TABLE
        order by
            id
    </select>

</sqlMap>

[サンプルクラス]

// vim:set ts=4 sw=4 et ws is nowrap ft=java:
package gsf.samples.ibatis.sample008;

import java.util.*;
import java.sql.*;

import gsf.interfaces.sqlmap.*;
import gsf.utils.sqlmap.*;

import com.ibatis.sqlmap.client.*;

public class IBatisSample008{

    private static class QueryMapKeyColumn1Executor implements SqlMapClientExecutor{
        
        public Object execute(SqlMapClient sqlMap) throws SQLException{
            return sqlMap.queryForMap("findAllSample008TestTable", null, "keyColumn1");
        }

    }

    private static class QueryMapKeyColumn2Executor implements SqlMapClientExecutor{
        
        public Object execute(SqlMapClient sqlMap) throws SQLException{
            return sqlMap.queryForMap("findAllSample008TestTable", null, "keyColumn2");
        }

    }

    public static void main(String[] args){

        Map&lt;String, Object> result = (Map&lt;String, Object>) SqlMapUtils.executeNoTransaction(new QueryMapKeyColumn1Executor());

        assert result.get("key8-1") != null : "key8-1が存在しない";
        assert result.get("key8-2") == null : "key8-2が存在する";

        System.out.println(result.get("key8-1"));
        System.out.println(result.get("key8-2"));   // ==> null

        result = (Map&lt;String, Object>) SqlMapUtils.executeNoTransaction(new QueryMapKeyColumn2Executor());

        assert result.get("key8-1") == null : "key8-1が存在する";
        assert result.get("key8-2") != null : "key8-2が存在しない";

        System.out.println(result.get("key8-1"));   // ==> null
        System.out.println(result.get("key8-2"));

    }

}

結果を確認してみると、keyPropertyで指定したプロパティをマップのキーとして
結果が生成されていることがわかります。

結構便利ですねこれは。