CODEWITHSUNDEEP
postgresqlmybatis
Idan Arye
Idan Arye

Reputation: 12613

Returning values from MyBatis <insert> mapped methods

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?

Upvotes: 23

Views: 73429

Answers (7)

user10696574
user10696574

Reputation: 1

Using 'returning' to return an instance of object

<select id="insertOrderHeader" resultType="com.***.entity.OrderHeader">
    insert into order_header_t (order_id, delete_flag, organization_code)
    values (#{query.orderId}, #{query.deleteFlag}, #{query.organizationCode})
        returning order_id as orderId, delete_flag as deleteFlag, organization_code as organizationCode
</select>

Upvotes: 0

r6q
r6q

Reputation: 272

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.

Upvotes: 2

Tiago Medici
Tiago Medici

Reputation: 2194

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

Upvotes: 1

partlov
partlov

Reputation: 14277

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.

Upvotes: 24

Sergey Nemchinov
Sergey Nemchinov

Reputation: 1616

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

Upvotes: 7

agad
agad

Reputation: 2189

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.

Upvotes: 1

Bhabani
Bhabani

Reputation: 138

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.

Upvotes: 6

Related Questions