insert always returns "1" instead of the newly generated key value

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

insert always returns "1" instead of the newly generated key value

Brian Barnett
I have tried as many variations as I could think of using
useGeneratedKeys, keyProperty and <selectKey> but my insert method
always returned 1. If the previous version of ibatis, it would return
the new key value. Do I have to make separate request to get the key
value now?

        <insert id="insertDbLog" parameterType="dbLogDTO"
useGeneratedKeys="true" keyProperty="dbLogId">

                INSERT INTO db_log (
                        date_added,
                        table_name,
                        table_pk_id,
                        event,
                        user_id
                )
                values (
                        #{dateAdded},
                        #{tableName},
                        #{tablePkId},
                        #{event},
                        #{userId}
                )

                <selectKey resultType="int" keyProperty="dbLogId" order="AFTER">
                        SELECT LAST_INSERT_ID();
                </selectKey>
        </insert>

If I use a mapper class to execute the mapped statement, I get "1"
back. If I call it directly using the sqlSession, I get a "1" back. If
I remove the <selectKey> I get a "1" back, etc., etc. The insert works
fine and inserts the row in the database, but I want to have the
method call return to me the newly generated key.
Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Larry Meadors
Because that's how many rows it inserted. Check the dbLogId property
on your inserted object.

Larry


On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:

> I have tried as many variations as I could think of using
> useGeneratedKeys, keyProperty and <selectKey> but my insert method
> always returned 1. If the previous version of ibatis, it would return
> the new key value. Do I have to make separate request to get the key
> value now?
>
>        <insert id="insertDbLog" parameterType="dbLogDTO"
> useGeneratedKeys="true" keyProperty="dbLogId">
>
>                INSERT INTO db_log (
>                        date_added,
>                        table_name,
>                        table_pk_id,
>                        event,
>                        user_id
>                )
>                values (
>                        #{dateAdded},
>                        #{tableName},
>                        #{tablePkId},
>                        #{event},
>                        #{userId}
>                )
>
>                <selectKey resultType="int" keyProperty="dbLogId" order="AFTER">
>                        SELECT LAST_INSERT_ID();
>                </selectKey>
>        </insert>
>
> If I use a mapper class to execute the mapped statement, I get "1"
> back. If I call it directly using the sqlSession, I get a "1" back. If
> I remove the <selectKey> I get a "1" back, etc., etc. The insert works
> fine and inserts the row in the database, but I want to have the
> method call return to me the newly generated key.
Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

François Schiettecatte
I had a little trouble with this with 3.x, here are the two variations that worked for me:

<!--
        Can't use 'useGeneratedKeys="true" keyProperty="categoryID"' here because it does not work with 'ON DUPLICATE KEY UPDATE'
-->
        <insert id="insertObject" parameterType="com.poplar.db.beans.dictionaries.Category">

                /* dictionaries.Category.insertObject */
                INSERT INTO categories
                                        (category_name)
                VALUES (#{categoryName})
                ON DUPLICATE KEY UPDATE
                                        category_id=LAST_INSERT_ID(category_id);
                                       
                <selectKey resultType="Integer" keyProperty="categoryID" order="AFTER">
                        SELECT LAST_INSERT_ID();
                </selectKey>

        </insert>




        <insert id="insertObject" parameterType="com.poplar.db.beans.channels.Channel" useGeneratedKeys="true" keyProperty="channelID">

                /* channels.Channel.insertObject */
                INSERT INTO channels
                                        (user_key,
                                        title,
                                        description)
                VALUES (UNHEX(#{userKey}),
                                        #{title},
                                        #{description,jdbcType=VARCHAR}})

<!--
                <selectKey resultType="Integer" keyProperty="channelID" order="AFTER">
                        SELECT LAST_INSERT_ID();
                </selectKey>
-->
        </insert>


François


On Oct 6, 2010, at 2:17 PM, Larry Meadors wrote:

> Because that's how many rows it inserted. Check the dbLogId property
> on your inserted object.
>
> Larry
>
>
> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
>> I have tried as many variations as I could think of using
>> useGeneratedKeys, keyProperty and <selectKey> but my insert method
>> always returned 1. If the previous version of ibatis, it would return
>> the new key value. Do I have to make separate request to get the key
>> value now?
>>
>>        <insert id="insertDbLog" parameterType="dbLogDTO"
>> useGeneratedKeys="true" keyProperty="dbLogId">
>>
>>                INSERT INTO db_log (
>>                        date_added,
>>                        table_name,
>>                        table_pk_id,
>>                        event,
>>                        user_id
>>                )
>>                values (
>>                        #{dateAdded},
>>                        #{tableName},
>>                        #{tablePkId},
>>                        #{event},
>>                        #{userId}
>>                )
>>
>>                <selectKey resultType="int" keyProperty="dbLogId" order="AFTER">
>>                        SELECT LAST_INSERT_ID();
>>                </selectKey>
>>        </insert>
>>
>> If I use a mapper class to execute the mapped statement, I get "1"
>> back. If I call it directly using the sqlSession, I get a "1" back. If
>> I remove the <selectKey> I get a "1" back, etc., etc. The insert works
>> fine and inserts the row in the database, but I want to have the
>> method call return to me the newly generated key.

Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Brian Barnett
In reply to this post by Larry Meadors
Thanks Larry. That was the disconnect. I was assuming the return value
would be the new Id like it used to be in earlier versions. I checked
the dbLogId property of my DTO (POJO) object and the newly generated
key value is there.

On Oct 6, 12:17 pm, Larry Meadors <[hidden email]> wrote:

> Because that's how many rows it inserted. Check the dbLogId property
> on your inserted object.
>
> Larry
>
> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
> > I have tried as many variations as I could think of using
> > useGeneratedKeys, keyProperty and <selectKey> but my insert method
> > always returned 1. If the previous version of ibatis, it would return
> > the new key value. Do I have to make separate request to get the key
> > value now?
>
> >        <insert id="insertDbLog" parameterType="dbLogDTO"
> > useGeneratedKeys="true" keyProperty="dbLogId">
>
> >                INSERT INTO db_log (
> >                        date_added,
> >                        table_name,
> >                        table_pk_id,
> >                        event,
> >                        user_id
> >                )
> >                values (
> >                        #{dateAdded},
> >                        #{tableName},
> >                        #{tablePkId},
> >                        #{event},
> >                        #{userId}
> >                )
>
> >                <selectKey resultType="int" keyProperty="dbLogId" order="AFTER">
> >                        SELECT LAST_INSERT_ID();
> >                </selectKey>
> >        </insert>
>
> > If I use a mapper class to execute the mapped statement, I get "1"
> > back. If I call it directly using the sqlSession, I get a "1" back. If
> > I remove the <selectKey> I get a "1" back, etc., etc. The insert works
> > fine and inserts the row in the database, but I want to have the
> > method call return to me the newly generated key.
Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Fadye
In reply to this post by Larry Meadors
How do you then return the new Id? I'm migrating from iBatis to
myBatis and I'm running into this issue. I want the newly generated
key to be returned, not the number of rows that were inserted.

Thanks

On Oct 6, 2:17 pm, Larry Meadors <[hidden email]> wrote:

> Because that's how many rows it inserted. Check the dbLogId property
> on your inserted object.
>
> Larry
>
> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
> > I have tried as many variations as I could think of using
> > useGeneratedKeys, keyProperty and <selectKey> but myinsertmethod
> >alwaysreturned 1. If the previous version of ibatis, it would return
> > the new key value. Do I have to make separate request to get the key
> > value now?
>
> >        <insertid="insertDbLog" parameterType="dbLogDTO"
> > useGeneratedKeys="true" keyProperty="dbLogId">
>
> >                INSERTINTO db_log (
> >                        date_added,
> >                        table_name,
> >                        table_pk_id,
> >                        event,
> >                        user_id
> >                )
> >                values (
> >                        #{dateAdded},
> >                        #{tableName},
> >                        #{tablePkId},
> >                        #{event},
> >                        #{userId}
> >                )
>
> >                <selectKey resultType="int" keyProperty="dbLogId" order="AFTER">
> >                        SELECT LAST_INSERT_ID();
> >                </selectKey>
> >        </insert>
>
> > If I use a mapper class to execute the mapped statement, I get "1"
> > back. If I call it directly using the sqlSession, I get a "1" back. If
> > I remove the <selectKey> I get a "1" back, etc., etc. Theinsertworks
> > fine and inserts the row in the database, but I want to have the
> > method call return to me the newly generated key.
Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Clinton Begin
Administrator
It's set on the parameter object.  I'm pretty certain the docs explain
it.  Let me know if they don't.



On 2010-10-22, Fadye <[hidden email]> wrote:

> How do you then return the new Id? I'm migrating from iBatis to
> myBatis and I'm running into this issue. I want the newly generated
> key to be returned, not the number of rows that were inserted.
>
> Thanks
>
> On Oct 6, 2:17 pm, Larry Meadors <[hidden email]> wrote:
>> Because that's how many rows it inserted. Check the dbLogId property
>> on your inserted object.
>>
>> Larry
>>
>> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
>> > I have tried as many variations as I could think of using
>> > useGeneratedKeys, keyProperty and <selectKey> but myinsertmethod
>> >alwaysreturned 1. If the previous version of ibatis, it would return
>> > the new key value. Do I have to make separate request to get the key
>> > value now?
>>
>> >        <insertid="insertDbLog" parameterType="dbLogDTO"
>> > useGeneratedKeys="true" keyProperty="dbLogId">
>>
>> >                INSERTINTO db_log (
>> >                        date_added,
>> >                        table_name,
>> >                        table_pk_id,
>> >                        event,
>> >                        user_id
>> >                )
>> >                values (
>> >                        #{dateAdded},
>> >                        #{tableName},
>> >                        #{tablePkId},
>> >                        #{event},
>> >                        #{userId}
>> >                )
>>
>> >                <selectKey resultType="int" keyProperty="dbLogId"
>> > order="AFTER">
>> >                        SELECT LAST_INSERT_ID();
>> >                </selectKey>
>> >        </insert>
>>
>> > If I use a mapper class to execute the mapped statement, I get "1"
>> > back. If I call it directly using the sqlSession, I get a "1" back. If
>> > I remove the <selectKey> I get a "1" back, etc., etc. Theinsertworks
>> > fine and inserts the row in the database, but I want to have the
>> > method call return to me the newly generated key.

--
Sent from my mobile device
Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Fadye
The part about "insert, update, delete" doesn't explain how to return
the newly generated key.  It just says

" As mentioned, insert is a little bit more rich in that it has a few
extra attributes and sub-elements that
allow it to deal with key generation in a number of ways.
First, if your database supports auto-generated key fields (e.g. MySQL
and SQL Server), then you can
simply set useGeneratedKeys=”true” and set the keyProperty to the
target property and you’re done."

Then gives an example, then gives an example about databases that
don't support auto-generated column types, gives a description about
"selectKey".

Maybe it's just me who's not seeing it.
If we take his example (1st post), how would you return the newly
generated key?

Thanks


On Oct 22, 9:00 pm, Clinton Begin <[hidden email]> wrote:

> It's set on the parameter object.  I'm pretty certain the docs explain
> it.  Let me know if they don't.
>
> On 2010-10-22, Fadye <[hidden email]> wrote:
>
>
>
> > How do you then return the new Id? I'm migrating from iBatis to
> > myBatis and I'm running into this issue. I want the newly generated
> > key to be returned, not the number of rows that were inserted.
>
> > Thanks
>
> > On Oct 6, 2:17 pm, Larry Meadors <[hidden email]> wrote:
> >> Because that's how many rows it inserted. Check the dbLogId property
> >> on your inserted object.
>
> >> Larry
>
> >> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
> >> > I have tried as many variations as I could think of using
> >> > useGeneratedKeys, keyProperty and <selectKey> but myinsertmethod
> >> >alwaysreturned 1. If the previous version of ibatis, it would return
> >> > the new key value. Do I have to make separate request to get the key
> >> > value now?
>
> >> >        <insertid="insertDbLog" parameterType="dbLogDTO"
> >> > useGeneratedKeys="true" keyProperty="dbLogId">
>
> >> >                INSERTINTO db_log (
> >> >                        date_added,
> >> >                        table_name,
> >> >                        table_pk_id,
> >> >                        event,
> >> >                        user_id
> >> >                )
> >> >                values (
> >> >                        #{dateAdded},
> >> >                        #{tableName},
> >> >                        #{tablePkId},
> >> >                        #{event},
> >> >                        #{userId}
> >> >                )
>
> >> >                <selectKey resultType="int" keyProperty="dbLogId"
> >> > order="AFTER">
> >> >                        SELECT LAST_INSERT_ID();
> >> >                </selectKey>
> >> >        </insert>
>
> >> > If I use a mapper class to execute the mapped statement, I get "1"
> >> > back. If I call it directly using the sqlSession, I get a "1" back. If
> >> > I remove the <selectKey> I get a "1" back, etc., etc. Theinsertworks
> >> > fine and inserts the row in the database, but I want to have the
> >> > method call return to me the newly generated key.
>
> --
> Sent from my mobile device
Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Clinton Begin
Administrator
Whether you use selectKey or auto generated keys, you set the keyProperty of the parameter object, and the parameter object will be updated.

Cheers,
Clinton

On Mon, Oct 25, 2010 at 9:08 AM, Fadye <[hidden email]> wrote:
The part about "insert, update, delete" doesn't explain how to return
the newly generated key.  It just says

" As mentioned, insert is a little bit more rich in that it has a few
extra attributes and sub-elements that
allow it to deal with key generation in a number of ways.
First, if your database supports auto-generated key fields (e.g. MySQL
and SQL Server), then you can
simply set useGeneratedKeys=”true” and set the keyProperty to the
target property and you’re done."

Then gives an example, then gives an example about databases that
don't support auto-generated column types, gives a description about
"selectKey".

Maybe it's just me who's not seeing it.
If we take his example (1st post), how would you return the newly
generated key?

Thanks


On Oct 22, 9:00 pm, Clinton Begin <[hidden email]> wrote:
> It's set on the parameter object.  I'm pretty certain the docs explain
> it.  Let me know if they don't.
>
> On 2010-10-22, Fadye <[hidden email]> wrote:
>
>
>
> > How do you then return the new Id? I'm migrating from iBatis to
> > myBatis and I'm running into this issue. I want the newly generated
> > key to be returned, not the number of rows that were inserted.
>
> > Thanks
>
> > On Oct 6, 2:17 pm, Larry Meadors <[hidden email]> wrote:
> >> Because that's how many rows it inserted. Check the dbLogId property
> >> on your inserted object.
>
> >> Larry
>
> >> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
> >> > I have tried as many variations as I could think of using
> >> > useGeneratedKeys, keyProperty and <selectKey> but myinsertmethod
> >> >alwaysreturned 1. If the previous version of ibatis, it would return
> >> > the new key value. Do I have to make separate request to get the key
> >> > value now?
>
> >> >        <insertid="insertDbLog" parameterType="dbLogDTO"
> >> > useGeneratedKeys="true" keyProperty="dbLogId">
>
> >> >                INSERTINTO db_log (
> >> >                        date_added,
> >> >                        table_name,
> >> >                        table_pk_id,
> >> >                        event,
> >> >                        user_id
> >> >                )
> >> >                values (
> >> >                        #{dateAdded},
> >> >                        #{tableName},
> >> >                        #{tablePkId},
> >> >                        #{event},
> >> >                        #{userId}
> >> >                )
>
> >> >                <selectKey resultType="int" keyProperty="dbLogId"
> >> > order="AFTER">
> >> >                        SELECT LAST_INSERT_ID();
> >> >                </selectKey>
> >> >        </insert>
>
> >> > If I use a mapper class to execute the mapped statement, I get "1"
> >> > back. If I call it directly using the sqlSession, I get a "1" back. If
> >> > I remove the <selectKey> I get a "1" back, etc., etc. Theinsertworks
> >> > fine and inserts the row in the database, but I want to have the
> >> > method call return to me the newly generated key.
>
> --
> Sent from my mobile device

Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Fadye
I have set the keyProperty, but it's still returning the number of
rows entered. This is what I have:

     <insert id="insertDevice" parameterType="Device" >
        insert into DEVICES(DEVICE_CODE, CREATED, MODIFIED)
        values (#{deviceCode}, #{createTimestamp}, #{modifyTimestamp})
       <selectKey keyProperty="deviceId" resultType="long"
order="AFTER" >
                SELECT @@IDENTITY AS DEVICE_ID
       </selectKey>
    </insert>

This returned the number of rows ( 1).

I also tried this

     <insert id="insertDevice" parameterType="Device"
useGeneratedKeys="true" keyProperty="deviceId">
        insert into DEVICES(DEVICE_CODE, CREATED, MODIFIED)
        values (#{deviceCode}, #{createTimestamp}, #{modifyTimestamp})
       <selectKey keyProperty="deviceId" resultType="long"
order="AFTER" >
                SELECT DEVICE_ID AS deviceId FROM DEVICES WHERE
DEVICE_ID = @@IDENTITY
       </selectKey>
    </insert>

Also returned 1.
I want the deviceId to be returned. How would I do that?

Thanks for your help.

On Oct 25, 11:55 am, Clinton Begin <[hidden email]> wrote:

> Whether you use selectKey or auto generated keys, you set the keyProperty of
> the parameter object, and the parameter object will be updated.
>
> Cheers,
> Clinton
>
> On Mon, Oct 25, 2010 at 9:08 AM, Fadye <[hidden email]> wrote:
> > The part about "insert, update, delete" doesn't explain how to return
> > the newly generated key.  It just says
>
> > " As mentioned, insert is a little bit more rich in that it has a few
> > extra attributes and sub-elements that
> > allow it to deal with key generation in a number of ways.
> > First, if your database supports auto-generated key fields (e.g. MySQL
> > and SQL Server), then you can
> > simply set useGeneratedKeys=”true” and set the keyProperty to the
> > target property and you’re done."
>
> > Then gives an example, then gives an example about databases that
> > don't support auto-generated column types, gives a description about
> > "selectKey".
>
> > Maybe it's just me who's not seeing it.
> > If we take his example (1st post), how would you return the newly
> > generated key?
>
> > Thanks
>
> > On Oct 22, 9:00 pm, Clinton Begin <[hidden email]> wrote:
> > > It's set on the parameter object.  I'm pretty certain the docs explain
> > > it.  Let me know if they don't.
>
> > > On 2010-10-22, Fadye <[hidden email]> wrote:
>
> > > > How do you then return the new Id? I'm migrating from iBatis to
> > > > myBatis and I'm running into this issue. I want the newly generated
> > > > key to be returned, not the number of rows that were inserted.
>
> > > > Thanks
>
> > > > On Oct 6, 2:17 pm, Larry Meadors <[hidden email]> wrote:
> > > >> Because that's how many rows it inserted. Check the dbLogId property
> > > >> on your inserted object.
>
> > > >> Larry
>
> > > >> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
> > > >> > I have tried as many variations as I could think of using
> > > >> > useGeneratedKeys, keyProperty and <selectKey> but myinsertmethod
> > > >> >alwaysreturned 1. If the previous version of ibatis, it would return
> > > >> > the new key value. Do I have to make separate request to get the key
> > > >> > value now?
>
> > > >> >        <insertid="insertDbLog" parameterType="dbLogDTO"
> > > >> > useGeneratedKeys="true" keyProperty="dbLogId">
>
> > > >> >                INSERTINTO db_log (
> > > >> >                        date_added,
> > > >> >                        table_name,
> > > >> >                        table_pk_id,
> > > >> >                        event,
> > > >> >                        user_id
> > > >> >                )
> > > >> >                values (
> > > >> >                        #{dateAdded},
> > > >> >                        #{tableName},
> > > >> >                        #{tablePkId},
> > > >> >                        #{event},
> > > >> >                        #{userId}
> > > >> >                )
>
> > > >> >                <selectKey resultType="int" keyProperty="dbLogId"
> > > >> > order="AFTER">
> > > >> >                        SELECT LAST_INSERT_ID();
> > > >> >                </selectKey>
> > > >> >        </insert>
>
> > > >> > If I use a mapper class to execute the mapped statement, I get "1"
> > > >> > back. If I call it directly using the sqlSession, I get a "1" back.
> > If
> > > >> > I remove the <selectKey> I get a "1" back, etc., etc. Theinsertworks
> > > >> > fine and inserts the row in the database, but I want to have the
> > > >> > method call return to me the newly generated key.
>
> > > --
> > > Sent from my mobile device
Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Jeff Butler
This is the way ot works. Mybatis will always return the number of
rows inserted. The newly generated key will be set in your parameter
object and will never be returned from the insert method.

If you must have the key returned, then you will need to write your
own method that extracts the key from the parameter object and returns
it.

Jeff Butler

On 10/25/10, Fadye <[hidden email]> wrote:

> I have set the keyProperty, but it's still returning the number of
> rows entered. This is what I have:
>
>      <insert id="insertDevice" parameterType="Device" >
>         insert into DEVICES(DEVICE_CODE, CREATED, MODIFIED)
>         values (#{deviceCode}, #{createTimestamp}, #{modifyTimestamp})
>        <selectKey keyProperty="deviceId" resultType="long"
> order="AFTER" >
>                 SELECT @@IDENTITY AS DEVICE_ID
>        </selectKey>
>     </insert>
>
> This returned the number of rows ( 1).
>
> I also tried this
>
>      <insert id="insertDevice" parameterType="Device"
> useGeneratedKeys="true" keyProperty="deviceId">
>         insert into DEVICES(DEVICE_CODE, CREATED, MODIFIED)
>         values (#{deviceCode}, #{createTimestamp}, #{modifyTimestamp})
>        <selectKey keyProperty="deviceId" resultType="long"
> order="AFTER" >
>                 SELECT DEVICE_ID AS deviceId FROM DEVICES WHERE
> DEVICE_ID = @@IDENTITY
>        </selectKey>
>     </insert>
>
> Also returned 1.
> I want the deviceId to be returned. How would I do that?
>
> Thanks for your help.
>
> On Oct 25, 11:55 am, Clinton Begin <[hidden email]> wrote:
>> Whether you use selectKey or auto generated keys, you set the keyProperty
>> of
>> the parameter object, and the parameter object will be updated.
>>
>> Cheers,
>> Clinton
>>
>> On Mon, Oct 25, 2010 at 9:08 AM, Fadye <[hidden email]> wrote:
>> > The part about "insert, update, delete" doesn't explain how to return
>> > the newly generated key.  It just says
>>
>> > " As mentioned, insert is a little bit more rich in that it has a few
>> > extra attributes and sub-elements that
>> > allow it to deal with key generation in a number of ways.
>> > First, if your database supports auto-generated key fields (e.g. MySQL
>> > and SQL Server), then you can
>> > simply set useGeneratedKeys=”true” and set the keyProperty to the
>> > target property and you’re done."
>>
>> > Then gives an example, then gives an example about databases that
>> > don't support auto-generated column types, gives a description about
>> > "selectKey".
>>
>> > Maybe it's just me who's not seeing it.
>> > If we take his example (1st post), how would you return the newly
>> > generated key?
>>
>> > Thanks
>>
>> > On Oct 22, 9:00 pm, Clinton Begin <[hidden email]> wrote:
>> > > It's set on the parameter object.  I'm pretty certain the docs explain
>> > > it.  Let me know if they don't.
>>
>> > > On 2010-10-22, Fadye <[hidden email]> wrote:
>>
>> > > > How do you then return the new Id? I'm migrating from iBatis to
>> > > > myBatis and I'm running into this issue. I want the newly generated
>> > > > key to be returned, not the number of rows that were inserted.
>>
>> > > > Thanks
>>
>> > > > On Oct 6, 2:17 pm, Larry Meadors <[hidden email]> wrote:
>> > > >> Because that's how many rows it inserted. Check the dbLogId
>> > > >> property
>> > > >> on your inserted object.
>>
>> > > >> Larry
>>
>> > > >> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
>> > > >> > I have tried as many variations as I could think of using
>> > > >> > useGeneratedKeys, keyProperty and <selectKey> but myinsertmethod
>> > > >> >alwaysreturned 1. If the previous version of ibatis, it would
>> > > >> > return
>> > > >> > the new key value. Do I have to make separate request to get the
>> > > >> > key
>> > > >> > value now?
>>
>> > > >> >        <insertid="insertDbLog" parameterType="dbLogDTO"
>> > > >> > useGeneratedKeys="true" keyProperty="dbLogId">
>>
>> > > >> >                INSERTINTO db_log (
>> > > >> >                        date_added,
>> > > >> >                        table_name,
>> > > >> >                        table_pk_id,
>> > > >> >                        event,
>> > > >> >                        user_id
>> > > >> >                )
>> > > >> >                values (
>> > > >> >                        #{dateAdded},
>> > > >> >                        #{tableName},
>> > > >> >                        #{tablePkId},
>> > > >> >                        #{event},
>> > > >> >                        #{userId}
>> > > >> >                )
>>
>> > > >> >                <selectKey resultType="int" keyProperty="dbLogId"
>> > > >> > order="AFTER">
>> > > >> >                        SELECT LAST_INSERT_ID();
>> > > >> >                </selectKey>
>> > > >> >        </insert>
>>
>> > > >> > If I use a mapper class to execute the mapped statement, I get
>> > > >> > "1"
>> > > >> > back. If I call it directly using the sqlSession, I get a "1"
>> > > >> > back.
>> > If
>> > > >> > I remove the <selectKey> I get a "1" back, etc., etc.
>> > > >> > Theinsertworks
>> > > >> > fine and inserts the row in the database, but I want to have the
>> > > >> > method call return to me the newly generated key.
>>
>> > > --
>> > > Sent from my mobile device

--
Sent from my mobile device
Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Larry Meadors
In reply to this post by Fadye
It's not going to return the id.

It just doesn't work that way.

The parameter object (the Device passed in) will have it's "deviceId"
property set.

Larry


On Mon, Oct 25, 2010 at 11:09 AM, Fadye <[hidden email]> wrote:

> I have set the keyProperty, but it's still returning the number of
> rows entered. This is what I have:
>
>     <insert id="insertDevice" parameterType="Device" >
>        insert into DEVICES(DEVICE_CODE, CREATED, MODIFIED)
>        values (#{deviceCode}, #{createTimestamp}, #{modifyTimestamp})
>       <selectKey keyProperty="deviceId" resultType="long"
> order="AFTER" >
>                SELECT @@IDENTITY AS DEVICE_ID
>       </selectKey>
>    </insert>
>
> This returned the number of rows ( 1).
>
> I also tried this
>
>     <insert id="insertDevice" parameterType="Device"
> useGeneratedKeys="true" keyProperty="deviceId">
>        insert into DEVICES(DEVICE_CODE, CREATED, MODIFIED)
>        values (#{deviceCode}, #{createTimestamp}, #{modifyTimestamp})
>       <selectKey keyProperty="deviceId" resultType="long"
> order="AFTER" >
>                SELECT DEVICE_ID AS deviceId FROM DEVICES WHERE
> DEVICE_ID = @@IDENTITY
>       </selectKey>
>    </insert>
>
> Also returned 1.
> I want the deviceId to be returned. How would I do that?
>
> Thanks for your help.
>
> On Oct 25, 11:55 am, Clinton Begin <[hidden email]> wrote:
>> Whether you use selectKey or auto generated keys, you set the keyProperty of
>> the parameter object, and the parameter object will be updated.
>>
>> Cheers,
>> Clinton
>>
>> On Mon, Oct 25, 2010 at 9:08 AM, Fadye <[hidden email]> wrote:
>> > The part about "insert, update, delete" doesn't explain how to return
>> > the newly generated key.  It just says
>>
>> > " As mentioned, insert is a little bit more rich in that it has a few
>> > extra attributes and sub-elements that
>> > allow it to deal with key generation in a number of ways.
>> > First, if your database supports auto-generated key fields (e.g. MySQL
>> > and SQL Server), then you can
>> > simply set useGeneratedKeys=”true” and set the keyProperty to the
>> > target property and you’re done."
>>
>> > Then gives an example, then gives an example about databases that
>> > don't support auto-generated column types, gives a description about
>> > "selectKey".
>>
>> > Maybe it's just me who's not seeing it.
>> > If we take his example (1st post), how would you return the newly
>> > generated key?
>>
>> > Thanks
>>
>> > On Oct 22, 9:00 pm, Clinton Begin <[hidden email]> wrote:
>> > > It's set on the parameter object.  I'm pretty certain the docs explain
>> > > it.  Let me know if they don't.
>>
>> > > On 2010-10-22, Fadye <[hidden email]> wrote:
>>
>> > > > How do you then return the new Id? I'm migrating from iBatis to
>> > > > myBatis and I'm running into this issue. I want the newly generated
>> > > > key to be returned, not the number of rows that were inserted.
>>
>> > > > Thanks
>>
>> > > > On Oct 6, 2:17 pm, Larry Meadors <[hidden email]> wrote:
>> > > >> Because that's how many rows it inserted. Check the dbLogId property
>> > > >> on your inserted object.
>>
>> > > >> Larry
>>
>> > > >> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
>> > > >> > I have tried as many variations as I could think of using
>> > > >> > useGeneratedKeys, keyProperty and <selectKey> but myinsertmethod
>> > > >> >alwaysreturned 1. If the previous version of ibatis, it would return
>> > > >> > the new key value. Do I have to make separate request to get the key
>> > > >> > value now?
>>
>> > > >> >        <insertid="insertDbLog" parameterType="dbLogDTO"
>> > > >> > useGeneratedKeys="true" keyProperty="dbLogId">
>>
>> > > >> >                INSERTINTO db_log (
>> > > >> >                        date_added,
>> > > >> >                        table_name,
>> > > >> >                        table_pk_id,
>> > > >> >                        event,
>> > > >> >                        user_id
>> > > >> >                )
>> > > >> >                values (
>> > > >> >                        #{dateAdded},
>> > > >> >                        #{tableName},
>> > > >> >                        #{tablePkId},
>> > > >> >                        #{event},
>> > > >> >                        #{userId}
>> > > >> >                )
>>
>> > > >> >                <selectKey resultType="int" keyProperty="dbLogId"
>> > > >> > order="AFTER">
>> > > >> >                        SELECT LAST_INSERT_ID();
>> > > >> >                </selectKey>
>> > > >> >        </insert>
>>
>> > > >> > If I use a mapper class to execute the mapped statement, I get "1"
>> > > >> > back. If I call it directly using the sqlSession, I get a "1" back.
>> > If
>> > > >> > I remove the <selectKey> I get a "1" back, etc., etc. Theinsertworks
>> > > >> > fine and inserts the row in the database, but I want to have the
>> > > >> > method call return to me the newly generated key.
>>
>> > > --
>> > > Sent from my mobile device
Reply | Threaded
Open this post in threaded view
|

Re: insert always returns "1" instead of the newly generated key value

Fadye
Thanks for your answers. iBatis returned the id and I was trying to
keep it that way and avoid having to write another method.
Thanks a lot for your help.

On Oct 25, 1:28 pm, Larry Meadors <[hidden email]> wrote:

> It's not going to return the id.
>
> It just doesn't work that way.
>
> The parameter object (the Device passed in) will have it's "deviceId"
> property set.
>
> Larry
>
> On Mon, Oct 25, 2010 at 11:09 AM, Fadye <[hidden email]> wrote:
> > I have set the keyProperty, but it's still returning the number of
> > rows entered. This is what I have:
>
> >     <insert id="insertDevice" parameterType="Device" >
> >        insert into DEVICES(DEVICE_CODE, CREATED, MODIFIED)
> >        values (#{deviceCode}, #{createTimestamp}, #{modifyTimestamp})
> >       <selectKey keyProperty="deviceId" resultType="long"
> > order="AFTER" >
> >                SELECT @@IDENTITY AS DEVICE_ID
> >       </selectKey>
> >    </insert>
>
> > This returned the number of rows ( 1).
>
> > I also tried this
>
> >     <insert id="insertDevice" parameterType="Device"
> > useGeneratedKeys="true" keyProperty="deviceId">
> >        insert into DEVICES(DEVICE_CODE, CREATED, MODIFIED)
> >        values (#{deviceCode}, #{createTimestamp}, #{modifyTimestamp})
> >       <selectKey keyProperty="deviceId" resultType="long"
> > order="AFTER" >
> >                SELECT DEVICE_ID AS deviceId FROM DEVICES WHERE
> > DEVICE_ID = @@IDENTITY
> >       </selectKey>
> >    </insert>
>
> > Also returned 1.
> > I want the deviceId to be returned. How would I do that?
>
> > Thanks for your help.
>
> > On Oct 25, 11:55 am, Clinton Begin <[hidden email]> wrote:
> >> Whether you use selectKey or auto generated keys, you set the keyProperty of
> >> the parameter object, and the parameter object will be updated.
>
> >> Cheers,
> >> Clinton
>
> >> On Mon, Oct 25, 2010 at 9:08 AM, Fadye <[hidden email]> wrote:
> >> > The part about "insert, update, delete" doesn't explain how to return
> >> > the newly generated key.  It just says
>
> >> > " As mentioned, insert is a little bit more rich in that it has a few
> >> > extra attributes and sub-elements that
> >> > allow it to deal with key generation in a number of ways.
> >> > First, if your database supports auto-generated key fields (e.g. MySQL
> >> > and SQL Server), then you can
> >> > simply set useGeneratedKeys=”true” and set the keyProperty to the
> >> > target property and you’re done."
>
> >> > Then gives an example, then gives an example about databases that
> >> > don't support auto-generated column types, gives a description about
> >> > "selectKey".
>
> >> > Maybe it's just me who's not seeing it.
> >> > If we take his example (1st post), how would you return the newly
> >> > generated key?
>
> >> > Thanks
>
> >> > On Oct 22, 9:00 pm, Clinton Begin <[hidden email]> wrote:
> >> > > It's set on the parameter object.  I'm pretty certain the docs explain
> >> > > it.  Let me know if they don't.
>
> >> > > On 2010-10-22, Fadye <[hidden email]> wrote:
>
> >> > > > How do you then return the new Id? I'm migrating from iBatis to
> >> > > > myBatis and I'm running into this issue. I want the newly generated
> >> > > > key to be returned, not the number of rows that were inserted.
>
> >> > > > Thanks
>
> >> > > > On Oct 6, 2:17 pm, Larry Meadors <[hidden email]> wrote:
> >> > > >> Because that's how many rows it inserted. Check the dbLogId property
> >> > > >> on your inserted object.
>
> >> > > >> Larry
>
> >> > > >> On Wed, Oct 6, 2010 at 12:07 PM, ooper <[hidden email]> wrote:
> >> > > >> > I have tried as many variations as I could think of using
> >> > > >> > useGeneratedKeys, keyProperty and <selectKey> but myinsertmethod
> >> > > >> >alwaysreturned 1. If the previous version of ibatis, it would return
> >> > > >> > the new key value. Do I have to make separate request to get the key
> >> > > >> > value now?
>
> >> > > >> >        <insertid="insertDbLog" parameterType="dbLogDTO"
> >> > > >> > useGeneratedKeys="true" keyProperty="dbLogId">
>
> >> > > >> >                INSERTINTO db_log (
> >> > > >> >                        date_added,
> >> > > >> >                        table_name,
> >> > > >> >                        table_pk_id,
> >> > > >> >                        event,
> >> > > >> >                        user_id
> >> > > >> >                )
> >> > > >> >                values (
> >> > > >> >                        #{dateAdded},
> >> > > >> >                        #{tableName},
> >> > > >> >                        #{tablePkId},
> >> > > >> >                        #{event},
> >> > > >> >                        #{userId}
> >> > > >> >                )
>
> >> > > >> >                <selectKey resultType="int" keyProperty="dbLogId"
> >> > > >> > order="AFTER">
> >> > > >> >                        SELECT LAST_INSERT_ID();
> >> > > >> >                </selectKey>
> >> > > >> >        </insert>
>
> >> > > >> > If I use a mapper class to execute the mapped statement, I get "1"
> >> > > >> > back. If I call it directly using the sqlSession, I get a "1" back.
> >> > If
> >> > > >> > I remove the <selectKey> I get a "1" back, etc., etc. Theinsertworks
> >> > > >> > fine and inserts the row in the database, but I want to have the
> >> > > >> > method call return to me the newly generated key.
>
> >> > > --
> >> > > Sent from my mobile device