ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Morearty, Brian" <Brian_Morea...@intuit.com>
Subject newbie Q: get back generated key with Oracle
Date Wed, 24 Mar 2010 18:11:32 GMT
Hi, here's a newbie question: 

 

I'm using iBATIS, Ibator, and Oracle. I have a "businesses" table. I
also created a "businesses_sequence" sequence so I can autogenerate
primary keys. (Apparently Oracle does it that way instead of allowing an
autoincrement column.)

 

How do I get back the primary key after an insert statement? (So I can,
for example, add reference the thing I just inserted from other tables.)

 

Originally I was using a before-insert trigger to select the next value
from the sequence but I don't get the value back from the mapper's
insert statement. I realized I probably had to use the 'generatedKey'
statement so I tried this:

 

<table schema="website_user" tableName="businesses"
domainObjectName="Business">

      <generatedKey column="id" sqlStatement="SELECT
businesses_sequence.nextval FROM dual" type="pre"/>

</table>

 

This generates the following mapping:

 

  <insert id="insert"
parameterType="com.intuit.websites.models.generated.Business">

    <!--

      WARNING - @ibatorgenerated

      This element is automatically generated by Apache iBATIS Ibator,
do not modify.

      This element was generated on Wed Mar 24 10:49:12 PDT 2010.

    -->

    <selectKey keyProperty="id" order="BEFORE"
resultType="java.math.BigDecimal">

      SELECT businesses_sequence.nextval FROM dual

    </selectKey>

    insert into WEBSITE_USER.BUSINESSES (ID, NAME, PHONE, 

      WEBSITE, CREATED_AT, UPDATED_AT

      )

    values (#{id,jdbcType=DECIMAL}, #{name,jdbcType=VARCHAR},
#{phone,jdbcType=VARCHAR}, 

      #{website,jdbcType=VARCHAR}, #{createdAt,jdbcType=TIMESTAMP},
#{updatedAt,jdbcType=TIMESTAMP}

      )

  </insert>

 

It successfully selects from the sequence but it does not return the
inserted id to me, porbably because the last statement is an insert, not
a select.

 

What else should I try? Or am I thinking about this the wrong way?

 

P.S. This is Oracle 10g Express Edition with ojdbc14.jar. Supposedly it
supports the JDBC getGeneratedKey() function-I'm not sure if iBATIS
under the hood calls that function.

 

 

Brian Morearty | Grow Your Business Division, Intuit | Staff Software
Engineer | direct 650-944-6852 

 


Mime
View raw message