Returning values from MyBatis 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?

score 24 · Accepted Answer · edited Feb 26 '20 at 13:56

24

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.

edited Feb 26 '20 at 13:56

ejuhjav

2,660
2
21
32

answered Mar 18 '13 at 09:49

partlov

13,789
6
63
82

I see. Is there any way to use this with PostgreSQL's `RETURNING` syntax? – Idan Arye Mar 18 '13 at 14:57
5

You can try add `useGeneratedKeys="true" keyColumn="id" keyProperty="id"`. I never used this but it should work. No need to use `returning` in query, let this job do JDBC driver and MyBatis. See [this link](http://edwin.baculsoft.com/2010/12/beginning-mybatis-3-part-3-how-to-get-tables-generated-ids/) for additional help. – partlov Mar 18 '13 at 15:06
OK, I looked at [these docs](http://www.mybatis.org/generator/configreference/generatedKey.html) and it looks like it does essentially the same thing - runs a second query to retrieve the key. Also, it is not defined for PostgreSQL, which makes sense - since in PostgreSQL you can get the key from the same query that inserted the row. – Idan Arye Mar 18 '13 at 15:26
Basically when you want to execute query with `returning` only way to get that value is from `ResultSet` which means you can't use `executeUpdate` which is used in MyBatis ``. So it can't be executed with ``. – partlov Mar 18 '13 at 15:32
@partlov Regarding `RETURNING` and `ResultSet`, is there a downside for using `INSERT... RETURNING` within a ` – Bruno May 12 '17 at 16:00
Well, probably not, bu I can't be completely sure. Besides it is not natural, there is possibility to optimize transaction if there are just reading queries in it, and inside ` – partlov May 15 '17 at 10:13
Is this "returning number of inserted rows" feature documented somewhere in mybatis's official doc? – wlnirvana Apr 26 '20 at 03:06
What is `currval('my_seq')`? – Seaky Lone Jun 09 '20 at 09:09
@SeakyLone That return current value of sequence with provided name (my_seq). That should be sequence used to populate primary key of table you are inserting value into. – partlov Jun 16 '20 at 12:28
When you have multiple statements inside ``, for example <`insert id="save"> select ... for update; insert.... ` Int method will always return -1 – Ivan Jun 02 '21 at 08:07

score 7 · Answer 2 · answered Sep 20 '19 at 09:55

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

For example, we have a class EntityDao:

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

1. Using the `insert` tag and returning an instance of object

MyBatis interface

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

MyBatis XML mapper:

<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 `select` and `resultType` tags to return the ID of the record only

MyBatis interface

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

MyBatis XML mapper:

<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);

score 6 · Answer 3 · answered Mar 19 '13 at 19:35

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.

score 1 · Answer 4 · answered Nov 22 '16 at 11:25

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 id set to value from column id.

score 1 · Answer 5 · answered Jul 28 '20 at 13:25

1

In this example using options

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface POJOCustomMapper {
    @Options(useGeneratedKeys = true, keyProperty = "ID", keyColumn = "ID") 
    @Insert({
        "insert into TABLE_A ( NAME "
        "values (#{NAME,jdbcType=VARCHAR})"
    })
    long insert(POJO record);

answered Jul 28 '20 at 13:25

Tiago Medici

1,944
22
22

2

Not work for me. I have to add `returning id` and use `@Select` without `@Options`. – chehsunliu May 23 '21 at 13:45
1

If you use \@Select instead of \@Insert it will start working ;) Do not forget to add "returning ID" at the end of the insert statement. – anydoby Dec 02 '21 at 15:43

score 0 · Answer 6 · answered Aug 19 '22 at 13:03

It can also be done with Java Records and mybatis annotations

Entity:

record UserEntity (
    int id,
    String name,
){}

Mapper:

@Select("INSERT INTO user (name) VALUES (#{name}) returning id")
int create(UserEntity entity);

The key here is to use @Select instead of @Insert and having returning id at the end of sql statement.

Returning values from MyBatis mapped methods

6 Answers6

1. Using the `insert` tag and returning an instance of object

2. Using `select` and `resultType` tags to return the ID of the record only

Linked

Returning values from MyBatis mapped methods

6 Answers6

1. Using the insert tag and returning an instance of object

2. Using select and resultType tags to return the ID of the record only

Linked

1. Using the `insert` tag and returning an instance of object

2. Using `select` and `resultType` tags to return the ID of the record only