PostgreSQL の再帰SQL→Array → mybatis で取得

Java SQL mybatis

先日の例、
PostgreSQL の再帰SQL で要素の連結結果を求める。 - Oboe吹きプログラマの黙示録
テーブル名：division

id parent_id name
1 null A
2 1 B
3 2 C
4 2 D

ここから、連結文字列の結果、id と name

"1"
"1,2"
"1,2,3"
"1,2,4"
"A"
"A,B"
"A,B,C"
"A,B,D"

と、配列としての結果も求めるＳＱＬにします。

[1]
[1,2]
[1,2,3]
[1,2,4]
["A"]
["A", "B"]
["A", "B", "C"]
["A", "B", "D"]

再帰ＳＱＬ

WITH RECURSIVE r (depth, id, parent_id, chain_id, chain_name,
                                aryid, aryname) AS (
SELECT 0, id, parent_id,
    ''||id AS chain_id,
    name AS chain_name, 
    ARRAY[id]   AS aryid,
    ARRAY[name] AS aryname
    FROM division
UNION ALL
SELECT r.depth + 1, d.id, d.parent_id,
    concat(d.id , ',', ''||r.chain_id),
    concat(d.name, ',', r.chain_name),
    array_cat(ARRAY[d.id] , r.aryid),
    array_cat(ARRAY[d.name] , r.aryname)
    FROM r, division d
    WHERE r.parent_id = d.id )
SELECT r.chain_id, r.chain_name, r.aryid, r.aryname FROM r
WHERE parent_id is null

↑ PostreSQL 独特で嫌なところは、数値型→文字列型変換している ''||r.chain_id この書き方です。

このクエリ結果を mybatis の SELECT で求めるオブジェクトのクラス

public class DivisionRecusive implements Serializable{
   public String chain_id;
   public String chain_name;
   public Integer[] aryid;
   public String[] aryname;

   public DivisionRecusive(){}
}

aryid を int にしたいところですが、ArrayTypeHandler を書かなくてはならないので、
このまま Integer で済ませます。必ず null 以外なら構わないでしょう。
mybatis の <typeHandlers> でカスタムで用意する ArrayTypeHandler を定義しなくても、
ＳＱＬＭａｐの方で、以下のように、resultMap による JdbcType 定義を解決させれば、
配列として ARRAY を取得できます。

typeHandler="org.apache.ibatis.type.ArrayTypeHandler" を ARRAY として抽出するカラムに定義します。

<resultMap id="mapDivisionRecusive" type="test.data.DivisionRecusive">
   <result column="aryid"   property="aryid"
     typeHandler="org.apache.ibatis.type.ArrayTypeHandler"
     jdbcType="INTEGER"  javaType="java.lang.Integer" />
   <result column="aryname" property="aryname"
     typeHandler="org.apache.ibatis.type.ArrayTypeHandler"
     jdbcType="VARCHAR"  javaType="java.lang.String" />
</resultMap>

<select id="getDivisionRecusiveList" resultMap="mapDivisionRecusive">
WITH RECURSIVE r (depth, id, parent_id, chain_id, chain_name,
                                aryid, aryname) AS (
SELECT 0, id, parent_id,
    ''||id AS chain_id,
    name AS chain_name, 
    ARRAY[id]   AS aryid,
    ARRAY[name] AS aryname
    FROM division
UNION ALL
SELECT r.depth + 1, d.id, d.parent_id,
    concat(d.id , ',', ''||r.chain_id),
    concat(d.name, ',', r.chain_name),
    array_cat(ARRAY[d.id] , r.aryid),
    array_cat(ARRAY[d.name] , r.aryname)
    FROM r, division d
    WHERE r.parent_id = d.id )
SELECT r.chain_id, r.chain_name, r.aryid, r.aryname FROM r
WHERE parent_id is null
</select>

resultMap を書く時、昔は SELECT 句で取得する列全てに対して、
<result column="" > を記述していたのですが、
property と columnが同じであれば、このように、typeHandler が必要なカラムに対してだけ書いても良いようです。