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 RE: newbie Q: get back generated key with Oracle
Date Fri, 26 Mar 2010 20:12:05 GMT
Thanks, Jeff.  And now I see the likely fix: when there's a <generatedKey type="pre"/>
in the Ibator config, Ibator should always include the ID in the insert statement instead
of conditionally including it.

Brian


-----Original Message-----
From: Jeff Butler [mailto:jeffgbutler@gmail.com] 
Sent: Friday, March 26, 2010 1:01 PM
To: user-java@ibatis.apache.org
Subject: Re: newbie Q: get back generated key with Oracle

This is an Ibator issue and it's an interesting case that I had not
considered previously.  I'll fix it.

Jeff Butler

On Wed, Mar 24, 2010 at 5:33 PM, Morearty, Brian
<Brian_Morearty@intuit.com> wrote:
> 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_Morearty@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.meadors@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_Morearty@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-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org


Mime
View raw message