Related to this discussion earlier today, I have a feature request. I'm not
sure if this should be in iBATIS or Ibator.
Problem: if I let Oracle autogenerate a primary key for me, like this:
<insert id="insertSelective"
parameterType="com.intuit.websites.models.generated.Business">
<selectKey keyProperty="id" order="BEFORE"
resultType="java.math.BigDecimal">
SELECT businesses_sequence.nextval FROM dual
</selectKey>
insert into WEBSITE_USER.BUSINESSES
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
ID,
</if>
....
then the Ibator-generated "insertSelective" statement fails with an error:
ORA-01400: cannot insert NULL into ("WEBSITE_USER"."BUSINESSES"."ID")
Why? Because I didn't set the ID (I was planning to let it be generated) and
apparently the <if test="id != null"> gets evaluated before the value gets
inserted into the ID.
I do have a workaround: always insert a dummy ID of 0 before calling
insertSelective. But it would be nice if I didn't have to.
Not sure what the best solution is but it seems like it would be nice to fix.
Brian
-----Original Message-----
From: Morearty, Brian [mailto:brian_morea...@intuit.com]
Sent: Wednesday, March 24, 2010 11:18 AM
To: user-java@ibatis.apache.org
Subject: RE: newbie Q: get back generated key with Oracle
Oh yeah, it does! Duh! Woo hoo.
Thanks for the quick reply, Larry.
-----Original Message-----
From: Larry Meadors [mailto:larry.mead...@gmail.com]
Sent: Wednesday, March 24, 2010 11:15 AM
To: user-java@ibatis.apache.org
Subject: Re: newbie Q: get back generated key with Oracle
It should set the id property on the Business bean passed in as a parameter.
Larry
On Wed, Mar 24, 2010 at 12:11 PM, Morearty, Brian
<brian_morea...@intuit.com> wrote:
> 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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
For additional commands, e-mail: user-java-h...@ibatis.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
For additional commands, e-mail: user-java-h...@ibatis.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
For additional commands, e-mail: user-java-h...@ibatis.apache.org
