ibatis db2 insert模板

iBatis and DB2 INSERT statements

0 comments · Posted on 2009-12-01 by Michael in development and tagged db2 · development · english · java

iBatis for Java is a good way of abstracting away the SQL statements from the business logic; it relies on XML files which contain all SQL statements and it is pretty simple to access a database. Because accessing DB2 from Java is pretty new for me I had some troubles finding a sample to setup a “insert” statement which returns the last inserted ID.
With PHP I’d use my_insert_id(), but how does it work with Java and DB2? Unfortunately all samples I could find where either based on Oracle or on the Microsoft SQL Server…. no luck.

So here is the XML required for an INSERT statement with iBatis on DB2:

<insert id=”insertTABLE” parameterclass=”TABLE”>
INSERT INTO TABLE
(…)
VALUES
(#…#)
<selectkey resultclass=”int” keyproperty=”id”>
SELECT IDENTITY_VAL_LOCAL() as ID FROM SYSIBM.SYSDUMMY1
</selectkey>
</insert>

 

After that the insert statement can be used like follows:

TABLE_BEAN table_bean = new table_bean();
table_bean.setXXX(…);
mySqlMapClient.insert(“insertTABLE”, table_bean);
// now table_bean.getId() returns the correct id

 

From now on iBatis works like a charm and the object value is automatically updated… nice!