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
