PostgreSql批量插入如何返回自增列

[william-abel]

代码如下

    final Class<?> entityClass = getEntityClass(ms);
    //开始拼sql
    StringBuilder sql = new StringBuilder();
    sql.append("<bind name=\"listNotEmptyCheck\" value=\"@tk.mybatis.mapper.util.OGNL@notEmptyCollectionCheck" +
            "(recordList, '" + ms.getId() + " 方法参数为空')\"/>");
    sql.append(SqlHelper.insertIntoTable(entityClass, tableName(entityClass), "recordList[0]"));
    sql.append(insertColumns(entityClass, false, true, false));
    sql.append(" VALUES ");
    sql.append("<foreach collection=\"recordList\" item=\"record\" separator=\",\" >");
    sql.append("<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">");
    //获取全部列
    Set<EntityColumn> columnList = EntityHelper.getColumns(entityClass);
    //单独增加对 genId 方式的支持
    for (EntityColumn column : columnList) {
        if (column.getGenIdClass() != null) {
            sql.append("<bind name=\"").append(column.getColumn()).append("GenIdBind\" value=\"@tk.mybatis.mapper" +
                    ".genid.GenIdUtil@genId(");
            sql.append("record").append(", '").append(column.getProperty()).append("'");
            sql.append(", @").append(column.getGenIdClass().getCanonicalName()).append("@class");
            sql.append(", '").append(tableName(entityClass)).append("'");
            sql.append(", '").append(column.getColumn()).append("')");
            sql.append("\"/>");
        }
    }
    //当某个列有主键策略时，不需要考虑他的属性是否为空，因为如果为空，一定会根据主键策略给他生成一个值
    for (EntityColumn column : columnList) {
        if (column.isInsertable()) {
            String value = column.getColumnHolder("record") + ",";
            column.setProperty("record." + column.getProperty());
            sql.append(SqlHelper.getIfNotNull(column, value, true));
            column.setProperty(column.getProperty().replace("record.", ""));

        }
    }
    sql.append("</trim>");
    sql.append("</foreach>");

    sql.append(" ON CONFLICT(${columns})");
    sql.append(" DO UPDATE  ");
    sql.append("<foreach collection=\"recordList\" item=\"record\" index=\"index\" separator=\",\" >");
    sql.append("<if test=\"index == 0\">");
    sql.append("<trim prefix=\"set\" suffixOverrides=\",\">");
    String retId = " RETURNING ";
    for (EntityColumn column : columnList) {
        if (column.isInsertable()) {
            String value =
                    column.getProperty().replace("record.", "") + " = excluded." + column.getEntityField().getName() + ",";
            String property = column.getProperty();
            column.setProperty("record." + column.getEntityField().getName());
            sql.append(SqlHelper.getIfNotNull(column, value, true));
            column.setProperty(property);
        }
        if (column.isIdentity()) {
            retId += column.getColumn();
        }
    }
    sql.append("</trim>");
    sql.append(retId);
    sql.append("</if>");
    sql.append("</foreach>");

    <bind name="listNotEmptyCheck"
          value="@tk.mybatis.mapper.util.OGNL@notEmptyCollectionCheck(recordList, 'com.cywlfw.whui.shcm.dao.AdminDao.insertConflictList 方法参数为空')"/>INSERT INTO ONEPIECES_ADMIN <trim
    prefix="(" suffix=")" suffixOverrides=",">
    <if test="recordList[0].username != null">USERNAME,</if>
    <if test="recordList[0].nickname != null">NICKNAME,</if>
    <if test="recordList[0].password != null">PASSWORD,</if>
    <if test="recordList[0].password_salt != null">PASSWORD_SALT,</if>
    <if test="recordList[0].last_login_ip != null">LAST_LOGIN_IP,</if>
    <if test="recordList[0].last_login_time != null">LAST_LOGIN_TIME,</if>
    <if test="recordList[0].avatar != null">AVATAR,</if>
    <if test="recordList[0].admin_role_id != null">ADMIN_ROLE_ID,</if>
    <if test="recordList[0].create_time != null">CREATE_TIME,</if>
    <if test="recordList[0].update_time != null">UPDATE_TIME,</if>
    <if test="recordList[0].info_id != null">INFO_ID,</if>
    <if test="recordList[0].company_id != null">COMPANY_ID,</if>
    </trim>  VALUES <foreach collection="recordList" item="record" separator=",">
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="record.username != null and record.username != '' ">#{record.username},</if>
        <if test="record.nickname != null and record.nickname != '' ">#{record.nickname},</if>
        <if test="record.password != null and record.password != '' ">#{record.password},</if>
        <if test="record.password_salt != null and record.password_salt != '' ">#{record.password_salt},</if>
        <if test="record.last_login_ip != null and record.last_login_ip != '' ">#{record.last_login_ip},</if>
        <if test="record.last_login_time != null">#{record.last_login_time},</if>
        <if test="record.avatar != null and record.avatar != '' ">#{record.avatar},</if>
        <if test="record.admin_role_id != null">#{record.admin_role_id},</if>
        <if test="record.create_time != null">#{record.create_time},</if>
        <if test="record.update_time != null">#{record.update_time},</if>
        <if test="record.info_id != null">#{record.info_id},</if>
        <if test="record.company_id != null">#{record.company_id},</if>
    </trim>
  </foreach> ON CONFLICT(${columns}) DO UPDATE  <foreach collection="recordList" item="record" index="index"
                                                         separator=",">
    <if test="index == 0">
      <trim prefix="set" suffixOverrides=",">
          <if test="record.username != null and record.username != '' ">username = excluded.username,</if>
          <if test="record.nickname != null and record.nickname != '' ">nickname = excluded.nickname,</if>
          <if test="record.password != null and record.password != '' ">password = excluded.password,</if>
          <if test="record.password_salt != null and record.password_salt != '' ">password_salt =
              excluded.password_salt,
          </if>
          <if test="record.last_login_ip != null and record.last_login_ip != '' ">last_login_ip =
              excluded.last_login_ip,
          </if>
          <if test="record.last_login_time != null">last_login_time = excluded.last_login_time,</if>
          <if test="record.avatar != null and record.avatar != '' ">avatar = excluded.avatar,</if>
          <if test="record.admin_role_id != null">admin_role_id = excluded.admin_role_id,</if>
          <if test="record.create_time != null">create_time = excluded.create_time,</if>
          <if test="record.update_time != null">update_time = excluded.update_time,</if>
          <if test="record.info_id != null">info_id = excluded.info_id,</if>
          <if test="record.company_id != null">company_id = excluded.company_id,</if>
      </trim>
      RETURNING id
    </if>
  </foreach>

//SQL如下

  INSERT INTO 
      ONEPIECES_ADMIN 
      ( USERNAME, NICKNAME, PASSWORD, PASSWORD_SALT, AVATAR, ADMIN_ROLE_ID, INFO_ID , COMPANY_ID )
      VALUES 
     ('1','1','2','2','2',1,1,1)
  ON 
      CONFLICT 
      ( 
          username DO 
  UPDATE 
  SET 
      username = excluded.username, 
      nickname = excluded.nickname, 
      PASSWORD = excluded.password, 
      password_salt = excluded.password_salt, 
      avatar = excluded.avatar, 
      admin_role_id = excluded.admin_role_id, 
      info_id = excluded.info_id, 
      company_id = excluded.company_id

无法返回自增主键 已经使用RETURNING id 返回了自增ID了 @abel533