You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Chris O'Connell <oc...@gorillachicago.com> on 2008/05/09 00:04:04 UTC
How to get the generated key from insert with Oracle
So, I need to insert a User record. I cannot change the database tables, so
I am stuck with the current behavior of my database. On insert, there is a
trigger that selects a value from a SEQUENCE and updates the key with that
value. I would like to return that value from the insert statement to my
java code. I'm looking at the iBatis in Action book, and trying to figure
out how to do that. I will include the parameter map I am using and the
<insert> statement below. To explain what you see below, the EMAIL field in
the database is unique, so if I create a new user with the provided email
address, I feel safe selecting that userID back out of the database based on
the email address.
However, this just feels clunky to me. Is this the right way to do it? Is
there a better way to get the new ID from the row I just created (given my
current constraint of not being able to change the database)?
Thanks,
Chris
<parameterMap id="UserParameterMap" class="com.gorilla.domain.users.User">
<parameter property="pass" jdbcType="VARCHAR" />
<parameter property="firstName" jdbcType="VARCHAR" />
<parameter property="lastName" jdbcType="VARCHAR" />
<parameter property="title" jdbcType="VARCHAR" />
<parameter property="company" jdbcType="VARCHAR" />
<parameter property="email" jdbcType="VARCHAR" />
<parameter property="description" jdbcType="VARCHAR" />
<parameter property="uuid" jdbcType="VARCHAR" />
<parameter property="screenName" jdbcType="VARCHAR" />
<parameter property="tradePortalId" jdbcType="VARCHAR" />
</parameterMap>
<insert id="insertNewUser" parameterMap="UserParameterMap">
INSERT INTO USER_USERS
(PASS,
FIRST_NAME,
LAST_NAME,
TITLE,
COMPANY,
EMAIL,
DESCRIPTION,
LOGIN_DATE,
USER_UUID,
MODIFY_DATE,
SCREEN_NAME,
TRADE_PORTAL_ID)
VALUES
(?,?,?,?,?,?,?,sysdate,?,sysdate,?,?)
<selectKey resultClass="java.math.BigDecimal">
select USER_ID from USER_USERS where EMAIL = #email#
</selectKey>
</insert>
Re: How to get the generated key from insert with Oracle
Posted by Sundar Sankar <fa...@gmail.com>.
Hi,
When using Hibernate, I have tested the same using rolling back one
session and committing the second one and vice versa. If you observe, The
value u get after the transaction is over will be greater than both the
sequence Id's. That is, if first ones id is X and seconds is X+1, no matter
which transaction got rolled back, a third person accessing would always get
back X+2, even if X or X+1 was available. I am not sure if this is something
hibernate does, but it would be a good way to test The concurrency issue in
the application.
My $0.002
-Sundar
On Fri, May 9, 2008 at 8:08 AM, Chris O'Connell <oc...@gorillachicago.com>
wrote:
> Argh... sometimes it is that easy.
>
> Yep, two different sql plus sessions, the first will always get *it's*
> correct value of sequence.currval, regardless of how many times another
> session calls sequence.nextval.
>
> Thanks,
> Chris
>
> -----Original Message-----
> From: Clinton Begin [mailto:clinton.begin@gmail.com]
> Sent: Friday, May 09, 2008 9:43 AM
> To: user-java@ibatis.apache.org
> Subject: Re: How to get the generated key from insert with Oracle
>
> Easy enough to test... open two sqlplus sessions and step through an
> example...
>
>
> Clinton
>
> On Fri, May 9, 2008 at 8:12 AM, Chris O'Connell
> <oc...@gorillachicago.com> wrote:
> > Thanks. I had never heard this before. Just to make sure we are talking
> > about the same thing, I'm not concerned about the sequence returning the
> > same value to two different people when the trigger is calling 'nextval'.
> > I'm worried about the fact that two inserts could happen before my code
> that
> > tries to call "sequence.currvalue" can execute for the first insert and I
> > end up with two difference insert actions that get the same value for
> > "sequence.currvalue".
> >
> > It isn't that I don't believe you :) but this is counter to what I
> *thought*
> > I knew, so I'm trying to get a full understanding of what is going on.
> >
> > Thanks,
> > Chris
> >
> > -----Original Message-----
> > From: Larry Meadors [mailto:larry.meadors@gmail.com]
> > Sent: Thursday, May 08, 2008 5:38 PM
> > To: user-java@ibatis.apache.org
> > Subject: Re: How to get the generated key from insert with Oracle
> >
> > Not with a sequence, I've been told that they are 'session' aware, so
> > if you get id 123 in your session, no one else ever will.
> >
> > Larry
> >
> >
> > On Thu, May 8, 2008 at 4:21 PM, Chris O'Connell
> > <oc...@gorillachicago.com> wrote:
> >> Even if I am executing all of this inside a transaction, don't I still
> > have
> >> concurrency issues with this approach?
> >>
> >>
> >>
> >> -----Original Message-----
> >> From: Larry Meadors [mailto:larry.meadors@gmail.com]
> >> Sent: Thursday, May 08, 2008 5:12 PM
> >> To: user-java@ibatis.apache.org
> >> Subject: Re: How to get the generated key from insert with Oracle
> >>
> >> I think this would be the preferred SQL for that:
> >>
> >> select ids.currval from dual
> >>
> >> ...where ids is your sequence's name.
> >>
> >> Larry
> >> No virus found in this incoming message.
> >> Checked by AVG.
> >> Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date:
> > 5/7/2008
> >> 5:23 PM
> >>
> >>
> > No virus found in this incoming message.
> > Checked by AVG.
> > Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date:
> 5/7/2008
> > 5:23 PM
> >
> >
> No virus found in this incoming message.
> Checked by AVG.
> Version: 8.0.100 / Virus Database: 269.23.14/1425 - Release Date: 5/9/2008
> 12:38 PM
>
>
Re: How to get the generated key from insert with Oracle
Posted by Larry Meadors <la...@gmail.com>.
On Fri, May 9, 2008 at 8:12 AM, Chris O'Connell
<oc...@gorillachicago.com> wrote:
> Thanks. I had never heard this before. Just to make sure we are talking
> about the same thing, I'm not concerned about the sequence returning the
> same value to two different people when the trigger is calling 'nextval'.
I can say this with a great deal of certainty: That will not happen.
I have used oracle for about 8 years, in a few pretty big systems, and
it's never happened, not even once.
I wouldn't bet my life on it, but I'd bet a day's pay without hesitation. ;-)
Larry
RE: How to get the generated key from insert with Oracle
Posted by Chris O'Connell <oc...@gorillachicago.com>.
Argh... sometimes it is that easy.
Yep, two different sql plus sessions, the first will always get *it's*
correct value of sequence.currval, regardless of how many times another
session calls sequence.nextval.
Thanks,
Chris
-----Original Message-----
From: Clinton Begin [mailto:clinton.begin@gmail.com]
Sent: Friday, May 09, 2008 9:43 AM
To: user-java@ibatis.apache.org
Subject: Re: How to get the generated key from insert with Oracle
Easy enough to test... open two sqlplus sessions and step through an
example...
Clinton
On Fri, May 9, 2008 at 8:12 AM, Chris O'Connell
<oc...@gorillachicago.com> wrote:
> Thanks. I had never heard this before. Just to make sure we are talking
> about the same thing, I'm not concerned about the sequence returning the
> same value to two different people when the trigger is calling 'nextval'.
> I'm worried about the fact that two inserts could happen before my code
that
> tries to call "sequence.currvalue" can execute for the first insert and I
> end up with two difference insert actions that get the same value for
> "sequence.currvalue".
>
> It isn't that I don't believe you :) but this is counter to what I
*thought*
> I knew, so I'm trying to get a full understanding of what is going on.
>
> Thanks,
> Chris
>
> -----Original Message-----
> From: Larry Meadors [mailto:larry.meadors@gmail.com]
> Sent: Thursday, May 08, 2008 5:38 PM
> To: user-java@ibatis.apache.org
> Subject: Re: How to get the generated key from insert with Oracle
>
> Not with a sequence, I've been told that they are 'session' aware, so
> if you get id 123 in your session, no one else ever will.
>
> Larry
>
>
> On Thu, May 8, 2008 at 4:21 PM, Chris O'Connell
> <oc...@gorillachicago.com> wrote:
>> Even if I am executing all of this inside a transaction, don't I still
> have
>> concurrency issues with this approach?
>>
>>
>>
>> -----Original Message-----
>> From: Larry Meadors [mailto:larry.meadors@gmail.com]
>> Sent: Thursday, May 08, 2008 5:12 PM
>> To: user-java@ibatis.apache.org
>> Subject: Re: How to get the generated key from insert with Oracle
>>
>> I think this would be the preferred SQL for that:
>>
>> select ids.currval from dual
>>
>> ...where ids is your sequence's name.
>>
>> Larry
>> No virus found in this incoming message.
>> Checked by AVG.
>> Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date:
> 5/7/2008
>> 5:23 PM
>>
>>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date: 5/7/2008
> 5:23 PM
>
>
No virus found in this incoming message.
Checked by AVG.
Version: 8.0.100 / Virus Database: 269.23.14/1425 - Release Date: 5/9/2008
12:38 PM
Re: How to get the generated key from insert with Oracle
Posted by Clinton Begin <cl...@gmail.com>.
Easy enough to test... open two sqlplus sessions and step through an example...
Clinton
On Fri, May 9, 2008 at 8:12 AM, Chris O'Connell
<oc...@gorillachicago.com> wrote:
> Thanks. I had never heard this before. Just to make sure we are talking
> about the same thing, I'm not concerned about the sequence returning the
> same value to two different people when the trigger is calling 'nextval'.
> I'm worried about the fact that two inserts could happen before my code that
> tries to call "sequence.currvalue" can execute for the first insert and I
> end up with two difference insert actions that get the same value for
> "sequence.currvalue".
>
> It isn't that I don't believe you :) but this is counter to what I *thought*
> I knew, so I'm trying to get a full understanding of what is going on.
>
> Thanks,
> Chris
>
> -----Original Message-----
> From: Larry Meadors [mailto:larry.meadors@gmail.com]
> Sent: Thursday, May 08, 2008 5:38 PM
> To: user-java@ibatis.apache.org
> Subject: Re: How to get the generated key from insert with Oracle
>
> Not with a sequence, I've been told that they are 'session' aware, so
> if you get id 123 in your session, no one else ever will.
>
> Larry
>
>
> On Thu, May 8, 2008 at 4:21 PM, Chris O'Connell
> <oc...@gorillachicago.com> wrote:
>> Even if I am executing all of this inside a transaction, don't I still
> have
>> concurrency issues with this approach?
>>
>>
>>
>> -----Original Message-----
>> From: Larry Meadors [mailto:larry.meadors@gmail.com]
>> Sent: Thursday, May 08, 2008 5:12 PM
>> To: user-java@ibatis.apache.org
>> Subject: Re: How to get the generated key from insert with Oracle
>>
>> I think this would be the preferred SQL for that:
>>
>> select ids.currval from dual
>>
>> ...where ids is your sequence's name.
>>
>> Larry
>> No virus found in this incoming message.
>> Checked by AVG.
>> Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date:
> 5/7/2008
>> 5:23 PM
>>
>>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date: 5/7/2008
> 5:23 PM
>
>
RE: How to get the generated key from insert with Oracle
Posted by Chris O'Connell <oc...@gorillachicago.com>.
Thanks. I had never heard this before. Just to make sure we are talking
about the same thing, I'm not concerned about the sequence returning the
same value to two different people when the trigger is calling 'nextval'.
I'm worried about the fact that two inserts could happen before my code that
tries to call "sequence.currvalue" can execute for the first insert and I
end up with two difference insert actions that get the same value for
"sequence.currvalue".
It isn't that I don't believe you :) but this is counter to what I *thought*
I knew, so I'm trying to get a full understanding of what is going on.
Thanks,
Chris
-----Original Message-----
From: Larry Meadors [mailto:larry.meadors@gmail.com]
Sent: Thursday, May 08, 2008 5:38 PM
To: user-java@ibatis.apache.org
Subject: Re: How to get the generated key from insert with Oracle
Not with a sequence, I've been told that they are 'session' aware, so
if you get id 123 in your session, no one else ever will.
Larry
On Thu, May 8, 2008 at 4:21 PM, Chris O'Connell
<oc...@gorillachicago.com> wrote:
> Even if I am executing all of this inside a transaction, don't I still
have
> concurrency issues with this approach?
>
>
>
> -----Original Message-----
> From: Larry Meadors [mailto:larry.meadors@gmail.com]
> Sent: Thursday, May 08, 2008 5:12 PM
> To: user-java@ibatis.apache.org
> Subject: Re: How to get the generated key from insert with Oracle
>
> I think this would be the preferred SQL for that:
>
> select ids.currval from dual
>
> ...where ids is your sequence's name.
>
> Larry
> No virus found in this incoming message.
> Checked by AVG.
> Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date:
5/7/2008
> 5:23 PM
>
>
No virus found in this incoming message.
Checked by AVG.
Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date: 5/7/2008
5:23 PM
Re: How to get the generated key from insert with Oracle
Posted by Larry Meadors <la...@gmail.com>.
Not with a sequence, I've been told that they are 'session' aware, so
if you get id 123 in your session, no one else ever will.
Larry
On Thu, May 8, 2008 at 4:21 PM, Chris O'Connell
<oc...@gorillachicago.com> wrote:
> Even if I am executing all of this inside a transaction, don't I still have
> concurrency issues with this approach?
>
>
>
> -----Original Message-----
> From: Larry Meadors [mailto:larry.meadors@gmail.com]
> Sent: Thursday, May 08, 2008 5:12 PM
> To: user-java@ibatis.apache.org
> Subject: Re: How to get the generated key from insert with Oracle
>
> I think this would be the preferred SQL for that:
>
> select ids.currval from dual
>
> ...where ids is your sequence's name.
>
> Larry
> No virus found in this incoming message.
> Checked by AVG.
> Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date: 5/7/2008
> 5:23 PM
>
>
RE: How to get the generated key from insert with Oracle
Posted by Chris O'Connell <oc...@gorillachicago.com>.
Even if I am executing all of this inside a transaction, don't I still have
concurrency issues with this approach?
-----Original Message-----
From: Larry Meadors [mailto:larry.meadors@gmail.com]
Sent: Thursday, May 08, 2008 5:12 PM
To: user-java@ibatis.apache.org
Subject: Re: How to get the generated key from insert with Oracle
I think this would be the preferred SQL for that:
select ids.currval from dual
...where ids is your sequence's name.
Larry
No virus found in this incoming message.
Checked by AVG.
Version: 8.0.100 / Virus Database: 269.23.10/1421 - Release Date: 5/7/2008
5:23 PM
Re: How to get the generated key from insert with Oracle
Posted by Larry Meadors <la...@gmail.com>.
I think this would be the preferred SQL for that:
select ids.currval from dual
...where ids is your sequence's name.
Larry