@SelectKey generation at insert with SQL Server

[Hernán J. González]

How to make the insert with identity key setting work in SQL Server? 

According to MyBatisGenerator, http://www.mybatis.org/generator/configreference/generatedKey.html 

one should rely on "SELECT SCOPE_IDENTITY()" for that, but it does not work for me

public class TestMapper {
    @Insert({ "insert into TEST (VAL_INT) values " +
          "(#{valInt,jdbcType=INTEGER})" })
    @SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class)
    int insert(Test record);
}

(more details here: https://stackoverflow.com/questions/47024704/mybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key )

Also, I've seen an old post (2008!) http://ibatis.10938.n7.nabble.com/SELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html 

that seems to conclude that MyBatis executes the insert and the select in different batches (strange!), and that's

why  SELECT SCOPE_IDENTITY() cannot work. 

If that is so, then MyBatisGenerator is wrong in recommending that way, no? Or am I doing something wrong?

Has this ever worked?

BTW, I've use this approach with Postgresql (reading the sequence, instead of SELECT SCOPE_IDENTITY() , of course), without problems.

[Kazuki Shimizu]

I've reproduced this issue using SQLServer on Linux (using Docker image provided Microsoft).

In this case, using @@IDENTITY variable work fine. But we should be careful the specification that @@IDENTITY is returning the last identity on all scopes. 

FOR details for @@IDENTITY, please see reference document.

As an alternative method, I will suggest using the auto-generated keys feature provided JDBC 3.0 rather than the selectKey feature provided MyBatis.

The SQL Server JDBC Driver supports it. (https://docs.microsoft.com/en-us/sql/connect/jdbc/using-auto-generated-keys)

How to use auto-generated Keys feature provided by JDBC 3.0 on MyBatis:

public interface TestMapper {

  @Insert({ "insert into TEST (VAL_INT) values " +
      "(#{valInt,jdbcType=INTEGER})" })

  @Options(useGeneratedKeys = true) // Adding this line instread of @SelectKey 
  int insert(Test record);
}

Note:

I don't know why SCOPE_IDENTITY() return null via JDBC Driver. It works fine via "sqlcmd" (CUI tool provided the SQLServer).

Thanks.

[Kazuki Shimizu]

If PreparedStatment is used, return null. I tried using Statement as follow:

public interface TestMapper {
  @Insert("insert into TEST (VAL_INT) values (100)") // No use bind variable (=#{..}) in SQL
  @Options(statementType = StatementType.STATEMENT) // Adding this line
  @SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class, statementType = StatementType.STATEMENT) // Set statementType = StatementType.STATEMENT
  int insert(Test record);
}

However, this solution is not practical.

I propose confirming the specification of the SQLServer JDBC Driver.

Thanks.

[Tim]

Try using just the generatedKeys

In xml I use:

insert id="insertFoo" useGeneratedKeys="true" keyProperty="id" keyColumn="id"

Note that keyProperty and keyColumn are important.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tim]

The annotation equivalent would be similar to what Kazuki posted:

@Insert({ "insert into TEST (VAL_INT) values (#{valInt,jdbcType=INTEGER})" })
@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")

This is what I use on SQL Server but if it's not working for you let me know what version of SQL server and which driver you are using (jtds or the official ms one?)

[Hernán J. González]

This worked for me, thanks.

A pity that MyBatisGenerator does not generate this...

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.

[Jeff Butler]

Read the manual.  Set sqlStatament="JDBC"

http://www.mybatis.org/generator/configreference/generatedKey.html

Jeff Butler