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

ibatis, java, postgresql

Solution

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.

Problem

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?

Original source