Using @Update or @Insert with PostgreSQL's RETURNING to get the updated object

[CatalinaMoisuc]

Would be really nice if it would be possible to use @Update, @Insert and also to map the returned object, using PostgreSQL's RETURNING in the statement like:

@Update("UPDATE users " +
  "SET name = #{name} " +
  "WHERE email = #{email} " +
  "RETURNING " +
  "id, " +
  "name, " +
  "email, " +
  "username;”)
User updateUser(@Param("email") String email, @Param(“name”) String name);

Currently this is not working. I get the following error:

org.apache.ibatis.binding.BindingException: Mapper method 'com.example.repository.UserDAO.updateUser’ has an unsupported return type: class com.example.model.User
        at org.apache.ibatis.binding.MapperMethod.rowCountResult(MapperMethod.java:110)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
        ...

I have also tried to map the result like:

@Results({
    @Result(property = "id", column = "id"),
    @Result(property = "name", column = "name"),
    @Result(property = "email", column = "email"),
    @Result(property = "username", column = "username")
  })

but I get the same error.

Note: I am using the following dependency: org.mybatis.spring.boot:mybatis-spring-boot-autoconfigure:jar:1.3.2

[romank0]

@CatalinaMoisuc
You can use @Select with flushCache option set to true.

[msamkov]

@select("INSERT INTO public.user(name) VALUES(#{name}) RETURNING id")
@options(flushCache = Options.FlushCachePolicy.TRUE)

[lichenhao]

How about use @InsertAndSelect instead @select for insert and returning operation.

[getarrenspieler]

I think that using insert-statement in <select> is tricky, it's looks like "undocummented feature"

I think there are two cases to "returnnig mappigs":

• get database generated values into input-parameter-object(s). It's not nesessarly to be genereated key value only, it may be any default value or value is set on db-side any another way (may be triggers?);
• get defferenly mapped inserted/updated values - when input and output types is different.

May be it shoud be more strict result mapping definition to use input object(s) or return new like:

<insert id="fillWithGenerated" returing="default" resultMap="resMap">
   INSERT INTO .....
   RETURING id, dateCreated, anotherGeneratedColumn;
</insert>

<insert id="returnGeneretad" returning="true" resultMap="resMap">
  INSERT INTO .....
  RETURINING *
</insert>

where returning="default" indicates that returned values should be mapped to same object(s) and returning="true" - to return new instance(s) according to result map.

[Koooooo-7]

I'm new to Mybatis + PostgreSQL.

I don't use the @update or @insert annotation , but when I tried it in the UserMapper.xml :

<insert id="insertUser"  parameterType="User" >  
INSERT INTO public.user VALUES(DEFAULT,'Koy',24) RETURNING *;
</insert>

There is the same error :
... has an unsupported return type: com.mt.pojo.User ....

Now, when I change the<insert>tag to <select> everything works fine(the same as @msamkov ),
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 what I expect ?

Can the methods in the mapper interface return the type besides Numeric Types (int,long,etc...)
on the INSERT statement ( eg: User insertUser(User u), return the User Object) ?

thx.

[getarrenspieler]

@Koooooo-7

Is there a way to make methods mapped to tags return results what I expect ?

No, it's the only way to do it with <select> yet.

[Koooooo-7]

@getarrenspieler Alright , it seems a bit werid to me 😄 thx.

And , I changed in another way.
I did't use the PostgreSQL RETURNING statement, like this.

 <insert id="insertOne"  parameterType="com.mt.pojo.User"  useGeneratedKeys="true"     
                          keyProperty="id,age,name"   keyColumn="id,age,name"  >

        INSERT INTO public.user VALUES(DEFAULT,#{name},#{age)) ;

    </insert>

then , it will return the values (id,age,name) to com.mt.pojo.User (the Object which is used in your Service.)

    @Override
    public int addOne(User user) {
        ......
        int i = userMapper.insertOne(user);
        System.out.println("user===>"+user);  // the  object returned
        ......
    }

On the Console :
user===>User{id=03d971b8-1763-4723-9a46-8fe6b10e31ca, name='Koy', age=24}
Although, the type of columns are different , it still works well.

[tyagiakhilesh]

@getarrenspieler Alright , it seems a bit werid to me 😄 thx.

And , I changed in another way.
I did't use the PostgreSQL RETURNING statement, like this.

 <insert id="insertOne"  parameterType="com.mt.pojo.User"  useGeneratedKeys="true"     
                          keyProperty="id,age,name"   keyColumn="id,age,name"  >

        INSERT INTO public.user VALUES(DEFAULT,#{name},#{age)) ;

    </insert>

then , it will return the values (id,age,name) to com.mt.pojo.User (the Object which is used in your Service.)

    @Override
    public int addOne(User user) {
        ......
        int i = userMapper.insertOne(user);
        System.out.println("user===>"+user);  // the  object returned
        ......
    }

On the Console :
user===>User{id=03d971b8-1763-4723-9a46-8fe6b10e31ca, name='Koy', age=24}
Although, the type of columns are different , it still works well.

This only fills id when the keys are auto generated. I don't think it updates anything else. For instance, I have one column createdDate in my table. While inserting I used now() function of postgres. i.e.

<insert id="insert" useGeneratedKeys="true" keyProperty="target.id">
    INSERT INTO mytable (created_date) VALUES (now())
</insert>

Then only id is updated in the value argument. createdDate is not updated.

[tyagiakhilesh]

<insert id="fillWithGenerated" returing="default" resultMap="resMap">

I don't see any attribute resultMap for <insert>. I am on latest version of mybatis3. Am I missing something ? (Even if I write it in xml - assuming for any reason it won't be in xsd ; which would be weird - my tests start throwing runtime exception saying you are doing weird stuff dude! :) )

[tyagiakhilesh]

+1

[romank0]

@tyagiakhilesh you aren't missing anything. resultMap is not supported on <insert>. As it was described above there are two workarounds currently:

1. RETURNING clause and <select> with flushCache=true
2. useGeneratedKeys. In order for this to work you need to specify all columns that you need to be fetched via keyProperty and keyColumn.

[nelsonthedev]

@select("INSERT INTO public.user(name) VALUES(#{name}) RETURNING id")
@options(flushCache = Options.FlushCachePolicy.TRUE)

I love you