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

[ooper]

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.

[Larry Meadors]

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

Larry

[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

[ooper]

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.

[Fadye]

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 <larry.mead...@gmail.com> 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 <br...@fincalc.com> 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 (

[Clinton Begin]

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

--
Sent from my mobile device

[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 <clinton.be...@gmail.com> wrote:
> It's set on the parameter object.  I'm pretty certain the docs explain
> it.  Let me know if they don't.
>

[Clinton Begin]

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

[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 <clinton.be...@gmail.com> 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
>

[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

[Larry Meadors]

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

[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 <larry.mead...@gmail.com> 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
>