iBatis遇到Postgresql的JSON/JSONB type

Postgresql等資料庫在較新的版本中，都提供了JSON/JSONB這樣的data type，這提供了開發者更動態的便利性。關於JSON/JSONB可以參考這裡，本文就不贅述。

如果是使用iBatis來做ORM，又該如何與JSON做mapping呢
這裡記錄一下我的試做

我的table結構是
CREATE TABLE public.test
(
  oid bigint NOT NULL,
  data text,
  jdata1 json,
  jdata2 jsonb,
  CONSTRAINT "PK_TEST" PRIMARY KEY (oid)
)


Java class是
public class CTest {
private long oid;
private String data;
private String jdata1;
private String jdata2;
  // getter/setter略
}

則我們可以用下列的iBatis mapper XML
<resultMap id="test" type="CTest">
<result property="oid" column="oid"/>
<result property="data" column="data"/>
<result property="jdata1" column="jdata1"/>
<result property="jdata2" column="jdata2"/>
</resultMap>
<insert id="newTest" parameterType="Map" statementType="PREPARED">
insert into test (oid, data, jdata1, jdata2)
values (#{oid}, #{data}, cast(#{jdata1} as json), cast(#{jdata2} as jsonb));
</insert>
<select id="getTest" resultMap="test" parameterType="Map" statementType="PREPARED">
select * from test where oid=#{oid};
</select>


這樣就可以用下列的iBatis mapper程式來存取table了
public void newTest() throws Exception {
JSONObject o1 = new JSONObject();
o1.put("name", "Yoyo Chen");
o1.put("age", 25);
o1.put("qualified", true);
Map map = new HashMap();
map.put("oid", 333);
map.put("data", o1.toJSONString());
map.put("jdata1", o1.toJSONString());
map.put("jdata2", o1.toJSONString());
execute("newTest", map);
}

public CTest getTest(long oid) {
Map map = new HashMap();
map.put("oid", oid);
CTest t = (CTest) selectOne("getTest", map);
return t;
}


ps. execute()與selectOne()是我的methods，主要是包裝了iBatis的methods