Returning values from MyBatis <insert> mapped methods

Question

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 INSERT statement, and I want to use it to return the auto-generated BIGSERIAL id of newly created records. So, I change the insert command 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 long instead of void.

When I try to run this, I get an org.xml.sax.SAXParseException saying that 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 INSERT statement.

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?

Answer 1

The return type of mapped insert method can be void or int (in which case it will return the number of the inserted row). You can do the following mechanism to return the generated 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 id column to id property of your parameter class. After that, object you passed as parameter will have generated id set in its property.

Answer 2

You can use as follows. In 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().

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