postgresql 从 MyBatis <insert> 映射方法返回值

Returning values from MyBatis <insert> mapped methods

提问by Idan Arye

I have a Java project that uses MyBatis to access a PostgreSQL database. PostgreSQL allows to return fields of a newly created row after an INSERTstatement, and I want to use it to return the auto-generated BIGSERIALidof newly created records. So, I change the insertcommand in the XML to use feature of PostgreSQL, add an resultType="long"attribute to the <insert>tag, and in the Java interface of the mapper I set the insertion method to return longinstead of void.

我有一个使用 MyBatis 访问 PostgreSQL 数据库的 Java 项目。PostgreSQL 允许在INSERT语句后返回新创建的行的字段，我想用它来返回自动生成BIGSERIALid的新创建的记录。因此，我insert将 XML 中的命令更改为使用 PostgreSQL 的resultType="long"特性，向<insert>标签添加一个属性，并在映射器的 Java 接口中将插入方法设置为 returnlong而不是void.

When I try to run this, I get an org.xml.sax.SAXParseExceptionsaying that Attribute "resultType" must be declared for element type "insert".

当我尝试运行它时，我得到了一个org.xml.sax.SAXParseException说法Attribute "resultType" must be declared for element type "insert"。

Now, when I change the <insert>tag to <select>everything works fine, but it bothers me that I use <select>tag to perform an INSERTstatement.

现在，当我将<insert>标签更改为<select>一切正常时，但我使用<select>标签来执行INSERT语句让我感到困扰。

Is there a way to make methods mapped to <insert>tags return results, or is MyBatis not designed for that, and I should just keep them as <select>tags?

有没有办法让映射到<insert>标签的方法返回结果，或者 MyBatis 不是为此而设计的，我应该将它们作为<select>标签保留？

回答by partlov

The return type of mapped insert method can be voidor int(in which case it will return the number of the inserted row). You can do the following mechanism to return the generated id:

映射插入方法的返回类型可以是void或int（在这种情况下，它将返回插入行的编号）。您可以执行以下机制来返回生成的 id：

<insert id="insert" parameterClass="MyParameter">
  <selectKey order="AFTER" keyProperty="id" resultType="long">
    SELECT currval('my_seq')
  </selectKey>
  INSERT INTO mytable(col1, col2) VALUES (#{val1}, #{val2})
</insert>

This will set generated idcolumn to idproperty of your parameter class. After that, object you passed as parameter will have generated idset in its property.

这会将生成的id列设置id为参数类的属性。之后，您作为参数传递的对象将id在其属性中生成集合。

回答by Bhabani

You can use as follows. In xml

您可以按如下方式使用。在 xml 中

 <insert id="insertNewUser" parameterType="User">
            <selectKey keyProperty="userId" resultType="Integer" order="BEFORE">
                select NEXTVAL('base.user_id_seq')
            </selectKey>
            INSERT INTO base.user(
                user_id, user_name)
            VALUES (#{userId}, #{userName});
    </insert>

In Java class from where you have called the method to insert, you can get the value by calling user.getUserId().

在您调用插入方法的 Java 类中，您可以通过调用user.getUserId().

Basically the next val is stored inside the variable of the object. Here userId inside User.

基本上下一个 val 存储在对象的变量中。 Here userId inside User.

回答by Sergey Nemchinov

There are two ways (at least that I know) to get the ID of the one inserted record:

有两种方法（至少我知道）可以获取一条插入记录的 ID：

For example, we have a class EntityDao:

例如，我们有一个类EntityDao：

public class EntityDao {
     private Long id;
     private String name;
     // other fields, getters and setters
}

---

---

1. Using the inserttag and returning an instance of object

1. 使用insert标签并返回对象的实例

MyBatis interface

MyBatis 界面

public interface EntityDaoMapper {
    EntityDao insert(EntityDao entity);
}

MyBatis XML mapper:

MyBatis XML 映射器：

<insert id="insert" parameterType="com.package.EntityDao" useGeneratedKeys="true" keyColumn="entity_id" keyProperty="id">
    INSERT INTO some_table (name, type, other_fields, etc)
    VALUES (#{name}, #{type}, #{other_fields}, #{etc}) 
</insert>

Sample code:

示例代码：

    EntityDao saved = entityDaoMapper.insert(entityToSave);
    System.out.println(saved.getId());

2. Using selectand resultTypetags to return the ID of the record only

2.使用select和resultType标签只返回记录的ID

MyBatis interface

MyBatis 界面

public interface EntityDaoMapper {
    Long insert(EntityDao entity);
}

MyBatis XML mapper:

MyBatis XML 映射器：

<select id="insert" parameterType="com.package.EntityDao" resultType="long">
    INSERT INTO some_table (name, type, other_fields, etc)
    VALUES (#{name}, #{type}, #{other_fields}, #{etc}) 
    RETURNING entity_id       <-- id only or many fields
</select>

Sample code:

示例代码：

Long id = entityDaoMapper.insert(entityToSave);
System.out.println(id);

回答by agad

You can also use generated keys:

您还可以使用生成的密钥：

  <insert id="create" parameterType="Skupina" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        INSERT INTO ODBOR 
            (NAZEV, POPIS, ZKRATKA, WEBROLE, JEODBOR, AKTIVNI)
        VALUES 
            (#{nazev}, #{popis}, #{webrole}, #{webrole}, false, #{aktivni})
  </insert>

After insert, parameter has property idset to value from column id.

插入后，参数的属性id设置为列id 中的值。