postgresql

[GenweiWu]

RETURNING关键字

常见用法：insert后returning

CREATE TABLE items (
  id SERIAL primary key,
  name varchar,
  created timestamp with time zone default now()
);

INSERT INTO items (name) values ('bear') RETURNING id, created;

结合mybatis的用法

https://github.com/mybatis/mybatis-3/issues/1293

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

参考

• https://www.postgresql.org/docs/current/dml-returning.html

[GenweiWu]

返回insert的主键id(自增类型)

http://www.tianshouzhi.com/api/tutorials/mybatis/378

• useGeneratedKeys设置为true，然后把keyColumn指定的数据库表字段，保存到parameterType对应类的keyProperty字段

  <insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
      INSERT INTO user (name, age) VALUES (#{name}, #{age})
  </insert>

• 即插入开始前id是null，插入完成后id就被填充值了

  @Test
  public void testInsert() {
      User user = new User();
      user.setName("tianshouzhi");
      user.setAge(27);
      System.out.println("插入前 id:" + user.getId());
      sqlSession.insert("com.tianshouzhi.mybatis.batch.mappers.UserMapper.insert", user);
      System.out.println("插入后：" + user.getId());
  }

或

<insert id="insert" useGeneratedKeys="true" keyProperty="id" keyColumn="user.id">
        INSERT INTO user (name, age) VALUES (#{user.name}, #{user.age})
    </insert>

insert(@Param("user") userInfo)

如果是batch插入

https://stackoverflow.com/a/42162859 则循环对象要取名为list

<insert id="batchInsert" parameterType="list" useGeneratedKeys="true" keyProperty="id">
<!-- obj2 is the object with variable id to store generated key --> 
insert into object2 (value) values 
<foreach collection="list" item="obj1" separator=",">
(#{obj1.xxx})
</foreach>