Java MyBatis，如何获取插入的自动生成的键？[MySql]

MyBatis, how to get the auto generated key of an insert? [MySql]

提问by user2572526

how can I get the generated key of an insert with MyBatis? I read many pages about this question but I'm still blocked, could anyone help me, please? This is my code:

如何使用 MyBatis 获取插入的生成键？我阅读了很多关于这个问题的页面，但我仍然被阻止，有人可以帮助我吗？这是我的代码：

The table:

桌子：

ID_ERROR long primary key
DATE timestamp
TYPE varchar
MESSAGE varchar
SOURCE varchar

The dao:

道：

Long returnedId = 0L;
MyMapper myMapper = this.sqlSession.getMapper(MyMapper.class);
myMapper.insertRecord(returnedId, Utils.now(), t.getClass().getName(), t.getMessage(), c.getName());
return returnedId;

The mapper.java:

mapper.java：

public void insertRecord(@Param("returnedId") Long returnedId, @Param("timestamp")Timestamp timestamp,@Param("type") String type,@Param("message") String message,@Param("source") String source);

The mapper.xml

mapper.xml

 <insert id="insertRecord" parameterType="map" useGeneratedKeys="true"  keyProperty="ID_ERROR">
    INSERT INTO errors (
        DATE,
        TYPE,
        MESSAGE,
        SOURCE
    )
    VALUES (
        #{timestamp},
        #{type},
        #{message},
        #{source}
    )
    <selectKey resultType="long" order="AFTER" keyProperty="returnedId">
        SELECT LAST_INSERT_ID() as returnedId
    </selectKey>
</insert>

What is wrong? How can I get the generated key of this insert? Thanks!

怎么了？如何获取此插入的生成密钥？谢谢！

采纳答案by Larry.Z

If you want to get the generated primary key, you should pass the arguments by Mapor POJO Object

如果你想获得生成的主键，你应该通过Map或传递参数POJO Object

public void insertRecord(Map<String, Object> map);

When call the mapping method, put values to map.

调用映射方法时，将值放入映射。

Map<String, Object> map = new HashMap<String, Object>();
map.put("returnedId", 0);
map.put("message", message);
// other paramters
mapper.insertRecord(map);
return map.get("returnedId");

回答by shola

Please follow below steps:

请按照以下步骤操作：

1. Create Error POJO with id as attribute

2. Replace returnId to error as below,

1. 使用 id 作为属性创建错误 POJO

2. 将 returnId 替换为错误，如下所示，

public void insertRecord(@Param("error") Error error, @Param("timestamp")Timestamp timestamp,@Param("type") String type,@Param("message") String message,@Param("source") String source);

public void insertRecord(@Param("error") 错误错误，@Param("timestamp")Timestamp timestamp,@Param("type") String type,@Param("message") String message,@Param("source" ) 字符串源);

1. Change keyProperty="ID_ERROR" to keyProperty="error.id"

2. Remove

   <selectKey resultType="long" order="AFTER" keyProperty="returnedId">
       SELECT LAST_INSERT_ID() as returnedId
   </selectKey>
   

3. 将 keyProperty="ID_ERROR" 更改为 keyProperty="error.id"

4. 消除

   <selectKey resultType="long" order="AFTER" keyProperty="returnedId">
       SELECT LAST_INSERT_ID() as returnedId
   </selectKey>
   

You will get inserted idin error.id

你将被插入id在error.id

回答by bharanitharan

You can achieve this by two ways,

您可以通过两种方式实现这一点，

1. By using useGeneratedKeys="true", keyProperty="id", keyColumn="id"

   keyPropertyrefers to the POJO variable name and keyColumnrefers to generated column name in database

2. By using <selectKey/>inside insert tag

1. 通过使用 useGeneratedKeys="true", keyProperty="id", keyColumn="id"

   keyProperty指的是POJO变量名，keyColumn指的是数据库中生成的列名

2. 通过使用<selectKey/>内部插入标签

回答by T M

For me it is working like this (mybatis 3.x) .. The id must be set auto increment in mysql table

对我来说，它是这样工作的（mybatis 3.x）.. 必须在 mysql 表中设置 id 自动递增

<insert id="createEmpty" parameterType="Project" useGeneratedKeys="true" keyProperty="project.projectId" keyColumn="PROJECT_ID">
    INSERT INTO PROJECT (TITLE,DESCRIPTION)
    VALUES
    (#{title},#{description})
</insert>

NOTEkeyProperty="project.projectId"and useGeneratedKeys="true"

注意keyProperty="project.projectId"和useGeneratedKeys="true"

my interface is:

我的界面是：

public int createEmpty(@Param("project") Project project, @Param("title") String title,
    @Param("description") String description);

finally to get the value (that will be automatically assigned to the pojo's id property) i use:

最后获取值（将自动分配给 pojo 的 id 属性）我使用：

projectRepository.createEmpty(p, "one", "two");
System.err.print(p.getProjectId() + "\n");

回答by Md. Naushad Alam

Easy Solution:

简单的解决方案：

Use KeyPropertyattribute as objectName.AutoincrementIdLike below...

使用KeyProperty属性objectName.AutoincrementId如下...

useGeneratedKeys="true", KeyProperty="person.id", KeyColumn="id"

useGeneratedKeys="true", KeyProperty="person.id", KeyColumn="id"

回答by Shubham Verma

Under the Mapper Xml, use the query :

在 Mapper Xml 下，使用查询：

    <insert id="saveDemo" parameterType="com.abc.demo"
       useGeneratedKeys="true" keyProperty="demoId" keyColumn="DEMOID">
       INSERT INTO TBL_DEMO (DEMONAME,DEMODESCRIPTION)
       VALUE (#{demoName},#{demoDescription})
       <selectKey keyProperty="demoId" resultType="int" order="AFTER">
        SELECT LAST_INSERT_ID();
       </selectKey>
    </insert>

Java Side

Java端

@Override
public boolean saveDemo(Demo demo) {
    boolean status = false;
    SqlSession session = this.sqlSessionFactory.openSession();
    try {
        DemoMapper mapper = session.getMapper(DemoMapper.class);
        mapper.saveDemo(demo);
        session.commit();
        status = true;
    } catch(PersistenceException e) {
        System.out.println(e);
    } finally {
        session.close();
    }
    return status;
}

回答by Audrey Carval

If you take a look at MyBatis documentation, useGeneratedKeysand keyPropertyis what you need at least to get auto increment data (for some database, you will need to add keyColumn).

如果您查看MyBatis 文档，useGeneratedKeys和keyProperty是您获取自动增量数据至少需要的东西（对于某些数据库，您需要添加keyColumn）。

As you can see, useGeneratedKeys depends on if/how is implemented the getGeneretadKeys method of the dataBase's JDBC.

如您所见，useGeneratedKeys 取决于数据库的 JDBC 的 getGeneretadKeys 方法是否/如何实现。

For exemple, with mysqlor H2, getGeneretadKeys support only one column. The last generated key will be the one return by getGeneretadKeys.

例如，对于mysql或 H2，getGeneretadKeys 仅支持一列。最后生成的键将是 getGeneretadKeys 返回的键。

In conclusion, in your case you need to add only useGeneratedKeys and keyProperty (with ID_ERROR auto_increment):

总之，在您的情况下，您只需要添加 useGeneratedKeys 和 keyProperty （带有 ID_ERROR auto_increment）：

Mapper.xml

映射器.xml

<resultMap type='pathToJavaClass/Error' id='error'>
    <id property='id' column='ID_ERROR' />
    <result property='timestamp' column='DATE' />
    <result property='type' column='TYPE'/>
    <result property='message' column='MESSAGE'/>
    <result property='source' column='SOURCE'/>
</resultMap>
<insert id="insertRecord" parameterType="error" useGeneratedKeys="true" keyProperty="id">
INSERT INTO errors (
    DATE,
    TYPE,
    MESSAGE,
    SOURCE
)
VALUES (
    #{timestamp},
    #{type},
    #{message},
    #{source}
)
</insert>

Interface.java

接口.java

public void insertRecord(@Param("error") Error error);

If you still get some issue to retrieve generated Keys, check also the documentation of mysql's JDBC (older version may not implement getGeneretadKeys).

如果您在检索生成的密钥时仍然遇到问题，请查看 mysql 的 JDBC 文档（旧版本可能没有实现 getGeneretadKeys）。

回答by Park JongBum

In the xml file Put below 5 lines:

在 xml 文件中放入以下 ​​5 行：

<insert id="createPet" parameterType="java.util.Map"
    useGeneratedKeys="true" keyProperty="id">
    INSERT INTO Pet (NAME, OWNER, SPECIES, SEX, BIRTH)
    VALUES (#{name}, #{owner}, #{species}, #{sex}, #{birth})
</insert>

Create this method in Java main class and call it in the main method:

在 Java 主类中创建此方法并在主方法中调用它：

public int createPet(PetDVO petDVO) throws Exception {
    HashMap<String, Object> inputMap = new HashMap<String, Object>();
    inputMap.put("name", petDVO.getName());
    inputMap.put("owner", petDVO.getOwner());
    inputMap.put("species", petDVO.getSpecies());
    inputMap.put("sex", petDVO.getSex());
    inputMap.put("birth", petDVO.getBirth());

    /**
     * Get the sql session and commit the data
     */
    SqlSession sqlSession = getSqlSession();
    sqlSession.insert("createPet", inputMap);
    sqlSession.commit();

    BigInteger newID = (BigInteger)inputMap.get("id");
    return newID.intValue();
}

But you should create PetDVO class yourself. That is it.

但是您应该自己创建 PetDVO 类。这就对了。