ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "J.F. Zarama" <jf.zar...@gmail.com>
Subject Re: execute SQL statement that are not in a map
Date Sun, 27 Mar 2005 16:22:30 GMT
Brandon Goodin: thanks for the explanation I was worried about my
practice to do same.

Example using hsqldb I have:

    <insert id="insertPerson" parameterClass="Person" >
        INSERT INTO PERSONS
            (FIRSTNAME, LASTNAME)
        VALUES (#firstName#, #lastName#)
        <selectKey resultClass="int" keyProperty="id">
            CALL IDENTITY()
        </selectKey>
    </insert>

wich return the inserted auto-incremented (identity) key for column 'id'

key = (Integer) sqlMap.insert("insertPerson", newPerson);

replacing 'CALL IDENTITY'  with select 'LAST_INSERT_ID()' should work for MySql

Thanks for the clarification

jfz.


On Sun, 27 Mar 2005 08:47:43 -0700, Brandon Goodin
<brandon.goodin@gmail.com> wrote:
> I don't think you need to worry about LAST_INSERT_ID so long as you
> have the same connection that inserted it. With IBatis this is not a
> problem.
> 
> "The last ID that was generated is maintained in the server on a
> per-connection basis. This means the value the function returns to a
> given client is the most recent AUTO_INCREMENT value generated by that
> client. The value cannot be affected by other clients, even if they
> generate AUTO_INCREMENT values of their own. This behavior ensures
> that you can retrieve your own ID without concern for the activity of
> other clients, and without the need for locks or transactions."
> 
> Full text from manual is below.
> 
> Brandon
> 
> From the MySQL manual:
> 
> LAST_INSERT_ID()  , LAST_INSERT_ID(expr)
> 
> Returns the last automatically generated value that was inserted into
> an AUTO_INCREMENT column.
> 
> mysql> SELECT LAST_INSERT_ID();
>         -> 195
> 
> The last ID that was generated is maintained in the server on a
> per-connection basis. This means the value the function returns to a
> given client is the most recent AUTO_INCREMENT value generated by that
> client. The value cannot be affected by other clients, even if they
> generate AUTO_INCREMENT values of their own. This behavior ensures
> that you can retrieve your own ID without concern for the activity of
> other clients, and without the need for locks or transactions.
> 
> The value of LAST_INSERT_ID() is not changed if you update the
> AUTO_INCREMENT column of a row with a non-magic value (that is, a
> value that is not NULL and not 0).
> 
> If you insert many rows at the same time with an insert statement,
> LAST_INSERT_ID() returns the value for the first inserted row. The
> reason for this is to make it possible to easily reproduce the same
> INSERT statement against some other server.
> 
> On Sun, 27 Mar 2005 08:55:15 -0500, John Fereira <jaf30@cornell.edu> wrote:
> > At 01:58 PM 3/23/2005 +0000, James, Steven wrote:
> > >hi steven
> > >
> > >it was actually $$ not ##.
> > >this works for me it will also get the last insert key change to suit db
> > >ie @@identity sqlserver
> > >
> > ><insert id="test" parameterClass="string">
> > >                   $value$
> > >                   <selectKey resultClass="int">
> > >                         select LAST_INSERT_ID()
> > >                 </selectKey>
> > >         </insert>
> >
> > You might want to be careful about using the LAST_INSERT_ID().   That
> > function returns the last auto_incremented value maintained by the
> > server.  In a multiuser environment their is the possibility of a race
> > condition.  The function is also not portable.
> >
> > As an alternative I create a Sequence table containing "name, id"
> > fields.  When an insert to a table is performed, first query the sequence
> > table to get the last id, use it for the value for the primary key (don't
> > use an auto-increment value), then increment the value after the insert
> > succeeds.
> > John Fereira
> > jaf30@cornell.edu
> > Ithaca, NY
> >
> >
>

Mime
View raw message