Is it possible in ibatis to have more than one selectKey clause in the same insert query?

Question

I have need to populate 2 different ids in the same table on insert and I am trying to use selectKey to pull values from Oracle sequence to populate the ids.

With one id and selectKey I have no problems but when I add the second selectKey the value does not seem to be populating (see insert stanza below).

Is it possible to do this? Or will I need to create another query to update the second id?

Thanks

<insert id="create" parameterClass="MyObject">
<selectKey keyProperty="id" resultClass="long" type="pre">
  <include refid="sequences.myObjectId" />
</selectKey>
<selectKey keyProperty="mySecondId" resultClass="long" type="pre">
  <include refid="sequences.mySecondId" />
</selectKey>    
INSERT INTO MY_OBJECT_TABLE 
(
MY_OBJECT_ID,
MY_SECOND_ID,
...
)
VALUES
)
#id#,
#mySecondId#,
...
)
</insert>

Pasquale

Answer 1

THERE CAN BE ONLY ONE!

Eventually I have discovered that there can only be one stanza in an ibatis insert stanza.

However I was able to update the second key as follows (I believe this is oracle specific):

<insert id="create" parameterClass="MyObject">
<selectKey keyProperty="id" resultClass="long" type="pre">
  <include refid="sequences.myObjectId" />
</selectKey>
INSERT INTO MY_OBJECT_TABLE 
(
MY_OBJECT_ID,
MY_SECOND_ID,
...
)
VALUES
)
#id#,
MY_SECOND_ID_SEQUENCE.nextval,
...
)
</insert>

MY_SECOND_ID_SEQUENCE is the Oracle sequence name that I previously defined.

Pasquale

I don't like answering my own deal but did eventually find the answer elsewhere. Hopefully this will help someone else in future. – Pasquale

I am using MS SQL, I have a similar requirement. Now here instead of getting MY_SECOND_ID from sequence, I need to fetch from some other table. Is it possible to use select query instead of sequence? – Belkisbelknap

It has been a while since I used MS SQL but maybe adding (SELECT ID FROM yourQuery), in place of the MY_SECOND_ID_SEQUENCE.nextval, ? – Pasquale

Answer 2

Not sure about IBatis but in MyBatis we can surely do like this :

 <insert id="some_id">

        <selectKey keyProperty="key1,key2" keyColumn="key_1,key_2" order="BEFORE" resultType="java.util.Map">
            SELECT seq_nextval('seq') as key_1, seq_nextval('seq') as key_2 from dual
        </selectKey>

        INSERT INTO table_name (column1, column2) 
        VALUES (#{key1},#{key2})

 </insert>

Matrilocal