GeneratedKey + SelectKey

[Marco]

Hi,
I have a problem to return the generated key, if I have also an
"calculated" key.
Here my example. I have a customer table with an autoincrement column=
"id" and a column "customernumber"
which is calculated with my selectkey statement.
The problem now is that I don't get back the column "id", only the
column "customernumber" is filled.
Has anybody an idea?

CREATE TABLE customer(
id BIGINT NOT NULL AUTO_INCREMENT,
companyid INTEGER NOT NULL,
disabled INTEGER(1) NOT NULL,
customernumber INTEGER NOT NULL,
title VARCHAR(5),
name1 VARCHAR(50),
name2 VARCHAR(50),
PRIMARY KEY (id)
)

    <insert id="insertCustomer" useGeneratedKeys="true"
keyProperty="id" parameterType="CustomerBean">
        <selectKey keyProperty="customernumber" resultType="int"
order="BEFORE">
                        select IFNULL(MAX(id)+1,1) from customer where companyid =
#{company.id}
                </selectKey>
    INSERT INTO customer(createuser, companyid, disabled,
customernumber, title, name1, name2)
    VALUES (#{createuser}, #{company.id}, #{disabled},
#{customernumber}, #{title}, #{name1}, #{name2})
    </insert>

[Jeff Butler]

We don't support mixing <selectKey> and JDBC auto generated keys so
it's no surprise this isn't working.

Also, doing a select max()... for these types of things is dangerous
and has concurrency problems.  Don't do it!

I would define a sequence for the companyid field, and write the
insert so that it pulls the value from the sequence.

Then with the brand new MyBatis 3.1, you can return more than one
generated column in the JDBC3 key generator if your driver supports
it:

<insert id="insertCustomer" useGeneratedKeys="true"
keyProperty="id,customernumber" keyColumn="id,customernumber">
insert into customer(..., customernumber,...) values(...,sequence.nextval,...)
</insert>

If this doesn't work with your DB, I would still try to use a sequence
for the customer number, but you may need to do the select nextval
first before you insert.

Jeff Butler


On Mon, Mar 12, 2012 at 2:44 PM, Marco <[hidden email]> wrote:

> Hi,
> I have a problem to return the generated key, if I have also an
> "calculated" key.
> Here my example. I have a customer table with an autoincrement column=
> "id" and a column "customernumber"
> which is calculated with my selectkey statement.
> The problem now is that I don't get back the column "id", only the
> column "customernumber" is filled.
> Has anybody an idea?
>
> CREATE TABLE customer(
> id BIGINT NOT NULL AUTO_INCREMENT,
> companyid INTEGER NOT NULL,
> disabled INTEGER(1) NOT NULL,
> customernumber INTEGER NOT NULL,
> title VARCHAR(5),
> name1 VARCHAR(50),
> name2 VARCHAR(50),
> PRIMARY KEY (id)
> )
>
>    <insert id="insertCustomer" useGeneratedKeys="true"
> keyProperty="id" parameterType="CustomerBean">
>        <selectKey keyProperty="customernumber" resultType="int"
> order="BEFORE">
>                        select IFNULL(MAX(id)+1,1) from customer where companyid =
> #{company.id}
>                </selectKey>
>        INSERT INTO customer(createuser, companyid, disabled,
> customernumber, title, name1, name2)
>        VALUES (#{createuser}, #{company.id}, #{disabled},
> #{customernumber}, #{title}, #{name1}, #{name2})
>    </insert>

[Marco]

Thanks Jeff for the detailed answer.

One more question. I'm using MySql and therefore "sequence.nextval"
does not exist.
On the mysql page I found following:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

"......This can be used to simulate sequences:

Create a table to hold the sequence counter and initialize it:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
Use the table to generate sequence numbers like this:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();
The UPDATE statement increments the sequence counter and causes the
next call to LAST_INSERT_ID()
 to return the updated value. The SELECT statement retrieves that
value. ....."

Is there a way to integrate this into mybatis call?
The calls:
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();
are dangerous and has concurrency problems, too. Right?

What's your advice?

[Jeff Butler]

I think the MySql sequence advice is less troublesome than a select
max strategy and it should be fine as long as everything is kept in
the same session.

This is a perfect candidate for a stored procedure.  You could bundle
all this together on the server side and just have one MyBatis call.

Jeff Butler

On Tue, Mar 13, 2012 at 1:35 PM, Marco <[hidden email]> wrote:

> Thanks Jeff for the detailed answer.
>
> One more question. I'm using MySql and therefore "sequence.nextval"
> does not exist.
> On the mysql page I found following:
> http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
>
> "......This can be used to simulate sequences:
>
> Create a table to hold the sequence counter and initialize it:
>
> mysql> CREATE TABLE sequence (id INT NOT NULL);
> mysql> INSERT INTO sequence VALUES (0);
> Use the table to generate sequence numbers like this:
>
> mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
> mysql> SELECT LAST_INSERT_ID();
> The UPDATE statement increments the sequence counter and causes the
> next call to LAST_INSERT_ID()
>  to return the updated value. The SELECT statement retrieves that
> value. ....."
>
> Is there a way to integrate this into mybatis call?
> The calls:
> mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
> mysql> SELECT LAST_INSERT_ID();
> are dangerous and has concurrency problems, too. Right?
>
> What's your advice?