Howto return ids on Inserts with Ibatis ( with RETURNING keyword )

Question

I'm using iBatis/Java and Postgres 8.3. When I do an insert in ibatis i need the id returned.
I use the following table for describing my question:
CREATE TABLE sometable ( id serial NOT NULL, somefield VARCHAR(10) );
The Sequence sometable_id_seq gets autogenerated by running the create statement.

At the moment i use the following sql map:

<insert id="insertValue" parameterClass="string" >
 INSERT INTO sometable ( somefield ) VALUES ( #value# );
 <selectKey keyProperty="id" resultClass="int">
  SELECT last_value AS id FROM sometable_id_seq
 </selectKey>
</insert>

It seems this is the ibatis way of retrieving the newly inserted id. Ibatis first runs a INSERT statement and afterwards it asks the sequence for the last id.
I have doubts that this will work with many concurrent inserts. ( discussed in this question )

I'd like to use the following statement with ibatis:
INSERT INTO sometable ( somefield ) VALUES ( #value# ) RETURNING id;

But when i try to use it within a <insert> sqlMap ibatis does not return the id. It seems to need the <selectKey> tag.

So here comes the question:

How can i use the above statement with ibatis?

Answer 1

The <selectKey> element is a child of the <insert> element and its content is executed before the main INSERT statement. You can use two approaches.

Fetch the key after you have inserted the record

This approach works depending on your driver. Threading can be a problem with this.

Fetching the key before inserting the record

This approach avoids threading problems but is more work. Example:

<insert id="insert">
  <selectKey keyProperty="myId"
             resultClass="int">
    SELECT nextVal('my_id_seq')
  </selectKey>
  INSERT INTO my
    (myId, foo, bar)
  VALUES
    (#myId#, #foo#, #bar#)
</insert>

On the Java side you can then do

Integer insertedId = (Integer) sqlMap.insert("insert", params)

This should give you the key selected from the my_id_seq sequence.

Answer 2

Here is simple example:

<statement id="addObject"
     parameterClass="test.Object"
     resultClass="int">
     INSERT INTO objects(expression, meta, title,
     usersid)
     VALUES (#expression#, #meta#, #title#, #usersId#)
     RETURNING id
</statement>

And in Java code:

Integer id = (Integer) executor.queryForObject("addObject", object);
object.setId(id);

This way more better than use :

1. It's simpler;
2. It have not requested to know sequence name (what usually hidden from postgresql developers).