先日の例、
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 が必要なカラムに対してだけ書いても良いようです。
