sqlserver insert 返回id_DM7 与 mybatis(二)——ID 生成

在《DM7与mybatis(一)——基本CRUD》中，我们介绍了dm7与mybatis的环境集成和基本配置，实现基本的CRUD操作。

在应用开发过程中，ID生成是一种常见的需求，通常基于数据库的自增列（如sqlserver、mysql）、序列（如Oracle）来自动生成。DM7同时支持自增列、序列，本文将介绍如何利用DM7和mybatis实现ID自动生成。

环境准备参见《DM7与mybatis(一)——基本CRUD》。

一、读取序列值生成ID

1、创建示例表和序列

以test用户登录，执行以下脚本：

-- 以test用户登录执行脚本

-- user_id序列

CREATE SEQUENCE seq_user_id

INCREMENT BY 1 START WITH 1

MAXVALUE 2147483647;

-- 用户信息表

CREATE TABLE t_user_seq

(

id INT NOT NULL, --用户标识

name VARCHAR(20) NOT NULL, --姓名

phone VARCHAR(50), -- 手机

email VARCHAR(50), -- 邮箱

PRIMARY KEY(id)

);

2、pojo对象

由于表t_user_seq和（一）当中表t_user的字段相同，我们可以复用原来的User对象以及对应的alias定义。

package org.dmstudy.mybatis.domain;

public class User {

private Integer id;

private String name;

private String phone;

private String email;

//getter/setter省略

}

3、XML映射文件

在resourcesmybatis-config.xml中，新增一个mapper配置，加载IdMapper.xml。

……

<!-- 配置映射文件 -->

<mappers>

<mapper resource="org/dmstudy/mybatis/crud/dao/UserMapper.xml" />

<mapper resource="org/dmstudy/mybatis/id/dao/IdMapper.xml" />

</mappers>

……

新增 org/dmstudy/mybatis/id/dao/IdMapper.xml文件，其内容如下：

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//http://mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.dmstudy.mybatis.id.dao.IdMapper">

<select id="getNextUserId" resultType="int">

select seq_user_id.nextval from dual

</select>

<insert id="insert" parameterType="User">

insert into t_user_seq

(id,name,phone,email)

values

(#{id},#{name},#{phone},#{email})

</insert>

</mapper>

注意namespace值对应为"org.dmstudy.mybatis.id.dao.IdMapper"。

其中id=getNextUserId的SELECT节点配置的sql语句是从序列seq_user_id中获取下一个序列值，该值根据定义从1开始，每次增量为1。

id=insert的INSERT节点配置了向表t_user_seq插入记录的语句。

4、Mapper接口

创建org.dmstudy.mybatis.id.dao.IdMapper.java文件，内容如下:

package org.dmstudy.mybatis.id.dao;

import org.dmstudy.mybatis.domain.User;

public interface IdMapper {

int getNextUserId();

int insert(User user);

}

注意接口的全名org.dmstudy.mybatis.id.dao.IdMapper、接口方法getNextUserId、insert与IdMapper.xml中的namespace、语句映射节点id保持一致。

5、调用代码

package org.dmstudy.mybatis.id;

……

public class IdApp {

……

public void insert() {

System.out.println("--- insert ---");

// 新建user对象,注意未设置id值

User user = new User();

user.setName("dmtech");

user.setPhone("400-6489899");

user.setEmail("dmtech@dameng.com");

SqlSession sqlSession = sqlSessionFactory.openSession();

try {

// 获取mapper对象

IdMapper mapper = sqlSession.getMapper(IdMapper.class);

// 获取id值

Integer id = mapper.getNextUserId();

System.out.println("成功获取seq_user_id的nextval： " + id);

// 设置id值

user.setId(id);

// 将user插入数据库

int cnt = mapper.insert(user);

sqlSession.commit();

System.out.println("成功插入 " + cnt + "条记录！ ");

} catch (Exception e) {

System.out.println("insert执行失败 ,原因:" + e.getMessage());

e.printStackTrace();

sqlSession.rollback();

} finally {

sqlSession.close();

}

}

}

查看执行过程输出log4j日志信息

--- insert ---

DEBUG [main] - Opening JDBC Connection

DEBUG [main] - Created connection 288994035.

DEBUG [main] - Setting autocommit to false on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - ==> Preparing: select seq_user_id.nextval from dual

DEBUG [main] - ==> Parameters:

DEBUG [main] - <== Total: 1

成功获取序列seq_user_id的nextval： 40

DEBUG [main] - ==> Preparing: insert into t_user_seq (id,name,phone,email) values (?,?,?,?)

DEBUG [main] - ==> Parameters: 40(Integer), dmtech(String), 400-6489899(String), dmtech@dameng.com(String)

DEBUG [main] - <== Updates: 1

DEBUG [main] - Committing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

成功插入 1条记录！

DEBUG [main] - Resetting autocommit to true on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Closing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Returned connection 288994035 to pool.

当执行Integer id = mapper.getNextUserId()时，mybatis底层实际执行语句” select seq_user_id.nextval from dual”，将返回值40（该值每次运行都会递增）作为ID值赋值给user参数，并插入到数据库表t_user_seq中。

二、直接插入序列表达式

1、XML映射文件

在IdMapper.xml文件中，新增一个语句映射节点“insertSeqExpr“，其内容如下：

<!-- IdMapper.xml -->

<mapper namespace="org.dmstudy.mybatis.id.dao.IdMapper">

……

<insert id="insertSeqExpr" parameterType="User">

insert into t_user_seq

(id,name,phone,email)

values

(seq_user_id.nextval,#{name},#{phone},#{email})

</insert>

</mapper>

2、Mapper接口

在IdMapper接口中，添加相应方法，内容如下:

package org.dmstudy.mybatis.id.dao;

import org.dmstudy.mybatis.domain.User;

public interface IdMapper {

……

//直接用序列表达式作为id值插入

int insertSeqExpr(User user);

}

3、调用代码

package org.dmstudy.mybatis.id;

……

public class IdApp {

……

public void insertSeqExpr() {

System.out.println("--- insertSeqExpr ---");

// 新建user对象,不设置id值

User user = new User();

user.setName("dmtech");

user.setPhone("400-6489899");

user.setEmail("dmtech@dameng.com");

SqlSession sqlSession = sqlSessionFactory.openSession();

try {

// 获取mapper对象

IdMapper mapper = sqlSession.getMapper(IdMapper.class);

// 将user插入数据库

int cnt = mapper.insertSeqExpr(user);

sqlSession.commit();

System.out.println("成功插入 " + cnt + "条记录！ ");

if (user.getId() == null) {

System.out.println("无法获取插入后的id值： " + user.getId());

} else {

System.out.println("成功获取插入后的id值： " + user.getId());

}

} catch (Exception e) {

System.out.println("insertSeqExpr执行失败 ,原因:" + e.getMessage());

e.printStackTrace();

sqlSession.rollback();

} finally {

sqlSession.close();

}

}

}

查看执行过程输出log4j日志信息

--- insertSeqExpr ---

DEBUG [main] - Opening JDBC Connection

DEBUG [main] - Checked out connection 288994035 from pool.

DEBUG [main] - Setting autocommit to false on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - ==> Preparing: insert into t_user_seq (id,name,phone,email) values (seq_user_id.nextval,?,?,?)

DEBUG [main] - ==> Parameters: dmtech(String), 400-6489899(String), dmtech@dameng.com(String)

DEBUG [main] - <== Updates: 1

DEBUG [main] - Committing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

成功插入 1条记录！

无法获取插入后的id值： null

DEBUG [main] - Resetting autocommit to true on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Closing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Returned connection 288994035 to pool.

注意该方法执行时，只有name、phone、email列是用输入参数user的属性值设置，而id列则直接使用表达式 seq_user_id.nextval 作为值。

在插入成功后，利用DM7管理工具可以在数据库中查看t_user_seq可以发现已经新增了记录并且id值也生成了。对于输入参数user对象，我们试图访问user的id属性，发现仍然是null。

相对于上一种方法，本方法只需定义一个节点和一个接口方法，程序相对简单。但本方法最大的不足在于id值是在数据库中执行sql语句时产生，我们没法马上知道该值具体是多少，对于不怎么关心ID值的场景（如插入新日志记录）问题不大，对于有些业务场景必须马上获取新增记录的ID（如新增记录ID需要传入到下一流程，主表记录ID需要作为字表记录外键等）则不合适。

三、使用SelectKey返回序列值

1、XML映射文件

Mybatis提供了<selectKey>标签，可以帮助用户在获取ID值后，一方面作为作为sql执行的参数，同时还会把ID值反写到输入对象参数中。

在IdMapper.xml文件中，新增一个语句映射节点“insertSequence “，其内容如下：

<!-- IdMapper.xml -->

<mapper namespace="org.dmstudy.mybatis.id.dao.IdMapper">

……

<insert id="insertSequence" parameterType="User">

<selectKey resultType="int" keyProperty="id" order="BEFORE">

select seq_user_id.nextval from dual

</selectKey>

insert into t_user_seq

(id,name,phone,email)

values

(#{id},#{name},#{phone},#{email})

</insert>

</mapper>

注意selectKey中的sql语句用来获取ID值，order="BEFORE"表示该语句会在主体的insert语句之前执行。

2、Mapper接口

在IdMapper接口中，添加相应方法，内容如下:

package org.dmstudy.mybatis.id.dao;

import org.dmstudy.mybatis.domain.User;

public interface IdMapper {

……

//使用序列插入id

int insertSequence(User user);

}

3、调用代码

package org.dmstudy.mybatis.id;

……

public class IdApp {

……

public void insertSequence() {

System.out.println("--- insertSequence ---");

// 新建user对象,不设置id值

User user = new User();

user.setName("dmtech");

user.setPhone("400-6489899");

user.setEmail("dmtech@dameng.com");

SqlSession sqlSession = sqlSessionFactory.openSession();

try {

// 获取mapper对象

IdMapper mapper = sqlSession.getMapper(IdMapper.class);

// 将user插入数据库

int cnt = mapper.insertSequence(user);

sqlSession.commit();

System.out.println("成功插入 " + cnt + "条记录！ ");

if (user.getId() == null) {

System.out.println("无法获取插入后的id值： " + user.getId());

} else {

System.out.println("成功获取插入后的id值： " + user.getId());

}

} catch (Exception e) {

System.out.println("insertSequence执行失败 ,原因:" + e.getMessage());

e.printStackTrace();

sqlSession.rollback();

} finally {

sqlSession.close();

}

}

}

查看执行过程输出log4j日志信息

--- insertSequence ---

DEBUG [main] - Opening JDBC Connection

DEBUG [main] - Checked out connection 288994035 from pool.

DEBUG [main] - Setting autocommit to false on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - ==> Preparing: select seq_user_id.nextval from dual

DEBUG [main] - ==> Parameters:

DEBUG [main] - <== Total: 1

DEBUG [main] - ==> Preparing: insert into t_user_seq (id,name,phone,email) values (?,?,?,?)

DEBUG [main] - ==> Parameters: 42(Integer), dmtech(String), 400-6489899(String), dmtech@dameng.com(String)

DEBUG [main] - <== Updates: 1

DEBUG [main] - Committing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

成功插入 1条记录！

成功获取插入后的id值： 42

DEBUG [main] - Resetting autocommit to true on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Closing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Returned connection 288994035 to pool.

从调用代码看，跟方法二直接插入序列表达式的调用代码一样。从实际执行情况看，本方法首先执行了<selectKey>中定义的语句，并将其返回值42作为id的参数，执行主体的sql语句。

执行成功后，我们试图获取输入参数user对象的id属性，发现其值已经被设置为42，即刚刚生成的ID值。

四、使用SelectKey返回自增列值

DM7不仅支持序列，也支持自增列，对原来习惯mysql、sqlserver的用户，可以参考下面的方法。

1、创建示例表

以test用户登录，执行以下脚本：

-- 以test用户登录执行脚本

-- 以test用户登录执行

-- 用户信息表

CREATE TABLE t_user_ident

(

id INT IDENTITY(1,1) NOT NULL , --用户标识

name VARCHAR(20) NOT NULL, --姓名

phone VARCHAR(50), -- 手机

email VARCHAR(50), -- 邮箱

PRIMARY KEY(id)

);

创建表t_user_ident，其中id列使用了自增列，从1开始，每次增量为1。

2、XML映射文件

在IdMapper.xml文件中，新增一个语句映射节点“insertIdentity “，其内容如下：

<!-- IdMapper.xml -->

<mapper namespace="org.dmstudy.mybatis.id.dao.IdMapper">

……

<insert id="insertIdentity" parameterType="User">

<selectKey resultType="int" keyProperty="id" order="AFTER">

select @@identity

</selectKey>

insert into t_user_ident

(name,phone,email)

values

(#{name},#{phone},#{email})

</insert>

</mapper>

order=" AFTER "表示该语句会在主体的insert语句之后执行，而select @@identity语句则是返回刚刚插入的自增列的值。

2、Mapper接口

在IdMapper接口中，添加相应方法，内容如下:

package org.dmstudy.mybatis.id.dao;

import org.dmstudy.mybatis.domain.User;

public interface IdMapper {

……

//使用自增列插入id

int insertIdentity(User user);

}

3、调用代码

package org.dmstudy.mybatis.id;

……

public class IdApp {

……

public void insertIdentity() {

System.out.println("--- insertIdentity ---");

// 新建user对象,不设置id值

User user = new User();

user.setName("dmtech");

user.setPhone("400-6489899");

user.setEmail("dmtech@dameng.com");

SqlSession sqlSession = sqlSessionFactory.openSession();

try {

// 获取mapper对象

IdMapper mapper = sqlSession.getMapper(IdMapper.class);

// 将user插入数据库

int cnt = mapper.insertIdentity(user);

sqlSession.commit();

System.out.println("成功插入 " + cnt + "条记录！ ");

if (user.getId() == null) {

System.out.println("无法获取插入后的id值： " + user.getId());

} else {

System.out.println("成功获取插入后的id值： " + user.getId());

}

} catch (Exception e) {

System.out.println("insertIdentity执行失败 ,原因:" + e.getMessage());

e.printStackTrace();

sqlSession.rollback();

} finally {

sqlSession.close();

}

} }

查看执行过程输出log4j日志信息

--- insertIdentity ---

DEBUG [main] - Opening JDBC Connection

DEBUG [main] - Checked out connection 288994035 from pool.

DEBUG [main] - Setting autocommit to false on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - ==> Preparing: insert into t_user_ident (name,phone,email) values (?,?,?)

DEBUG [main] - ==> Parameters: dmtech(String), 400-6489899(String), dmtech@dameng.com(String)

DEBUG [main] - <== Updates: 1

DEBUG [main] - ==> Preparing: select @@identity

DEBUG [main] - ==> Parameters:

DEBUG [main] - <== Total: 1

DEBUG [main] - Committing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

成功插入 1条记录！

成功获取插入后的id值： 25

DEBUG [main] - Resetting autocommit to true on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Closing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Returned connection 288994035 to pool.

从调用代码看，跟前面两种方法的调用代码一样，但底层sql实际执行情况不一样。本方法首先执行的是主体中的insert语句，而且id列也没有出现在insert列中，是由数据库自增列来产生值。随后执行了<selectKey>中定义的语句。最后检查输入参数user对象的id属性，发现其值也已经被设置，其值25跟数据库中刚插入的记录一致。

五、使用GeneratedKeys返回自增列值

对于刚刚插入的自增列的值，除了使用select @@identity 返回，还可以利用jdbc提供的RETURN_GENERATED_KEYS选项来获取，mybatis支持该方法。

1、XML映射文件

在IdMapper.xml文件中，新增一个语句映射节点“insertUseGeneratedKeys “，其内容如下：

<!-- IdMapper.xml -->

<mapper namespace="org.dmstudy.mybatis.id.dao.IdMapper">

……

<insert id="insertUseGeneratedKeys" parameterType="User" useGeneratedKeys="true" keyProperty="id">

insert into t_user_ident

(name,phone,email)

values

(#{name},#{phone},#{email})

</insert>

</mapper>

useGeneratedKeys="true"表示mybatis执行该语句时，会在创建PreparedStatement对象时添加PreparedStatement.RETURN_GENERATED_KEYS标记，这样jdbc驱动会在执行成功后额外返回一个结果集，其中包含了服务器端刚生成的ID值。

2、Mapper接口

在IdMapper接口中，添加相应方法，内容如下:

package org.dmstudy.mybatis.id.dao;

import org.dmstudy.mybatis.domain.User;

public interface IdMapper {

……

int insertUseGeneratedKeys(User user);

}

3、调用代码

package org.dmstudy.mybatis.id;

……

public class IdApp {

……

public void insertUseGeneratedKeys() {

System.out.println("--- insertUseGeneratedKeys ---");

// 新建user对象,不设置id值

User user = new User();

user.setName("dmtech");

user.setPhone("400-6489899");

user.setEmail("dmtech@dameng.com");

SqlSession sqlSession = sqlSessionFactory.openSession();

try {

// 获取mapper对象

IdMapper mapper = sqlSession.getMapper(IdMapper.class);

// 将user插入数据库

int cnt = mapper.insertUseGeneratedKeys(user);

sqlSession.commit();

System.out.println("成功插入 " + cnt + "条记录！ ");

if (user.getId() == null) {

System.out.println("无法获取插入后的id值： " + user.getId());

} else {

System.out.println("成功获取插入后的id值： " + user.getId());

}

} catch (Exception e) {

System.out.println("insertUseGeneratedKeys执行失败 ,原因:" + e.getMessage());

e.printStackTrace();

sqlSession.rollback();

} finally {

sqlSession.close();

}

}

}

查看执行过程输出log4j日志信息

--- insertUseGeneratedKeys ---

DEBUG [main] - Opening JDBC Connection

DEBUG [main] - Checked out connection 288994035 from pool.

DEBUG [main] - Setting autocommit to false on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - ==> Preparing: insert into t_user_ident (name,phone,email) values (?,?,?)

DEBUG [main] - ==> Parameters: dmtech(String), 400-6489899(String), dmtech@dameng.com(String)

DEBUG [main] - <== Updates: 1

DEBUG [main] - Committing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

成功插入 1条记录！

成功获取插入后的id值： 26

DEBUG [main] - Resetting autocommit to true on JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Closing JDBC Connection [dm.jdbc.driver.DmdbConnection@1139b2f3]

DEBUG [main] - Returned connection 288994035 to pool.

从调用代码看，跟前面几种方法的调用代码一样，底层执行的sql语句只有主体中的insert语句，而最后检查输入参数user对象的id属性，发现其值也已经被正确设置，其值26跟数据库中刚插入的记录一致。