时间戳

首页 > 解决方案 > 如何解决 org.springframework.jdbc.BadSqlGrammarException

postgresql - 如何解决 org.springframework.jdbc.BadSqlGrammarException

问题描述

我有以下数据类：

package gulfnet.tts.inthanon.data.product.datadomain;

import java.time.ZonedDateTime;
import lombok.Data;


@Data
public class CommunityItemRecord{

    private String communityId; 
    private String itemId; 
    private ZonedDateTime publicStartDt; 
    private ZonedDateTime publicEndDt; 
    private ZonedDateTime handleEndDt; 
    private String makerCommunityId; 
    private Integer makerApprovalFlg; 
    private String makerApprovalUserId; 

    private ZonedDateTime insDttm; 
    private String insUserId; 
    private ZonedDateTime upDttm; 
    private String upUserId; 

}

映射器定义如下：

<insert id="insertCommunityItemList" parameterType='list'>
        INSERT ALL
            <foreach collection="list" item="element" index="index">
                     INSERT INTO product.m_community_item (
                        community_id,
                        item_id,
                        public_start_dt,
                        public_end_dt,
                        handle_end_dt,
                        maker_community_id,
                        maker_approval_flg,
                        maker_approval_user_id,
                        ins_user_id,
                        up_user_id
                    ) VALUES (
                        #{element.communityId},
                        #{element.itemId},
                        #{element.publicStartDt},
                        #{element.publicEndDt},
                        #{element.handleEndDt},
                        #{element.makerCommunityId},
                        #{element.makerApprovalFlg},
                        #{element.makerApprovalUserId},
                        #{element.insUserId},
                        #{element.insUserId}
                    )
                on conflict on constraint m_community_item_pk
                do update set
                        public_start_dt = #{element.publicStartDt},
                        public_end_dt = #{element.publicEndDt},
                        up_dttm = current_timestamp,
                        up_user_id = #{element.insUserId}
            </foreach>  
    </insert>

映射器方法如下：

public void insertCommunityItemList(java.util.ArrayList<CommunityItemRecord> communityItemRecordList);

我收到以下错误：

异常=org.springframework.jdbc.BadSqlGrammarException: gulfnet.tts.inthanon.data.product.mapper.CatalogMapper.insertCommunityItemList (batch index #1) 失败。原因：java.sql.BatchUpdateException：批处理条目 0 INSERT ALL 已中止。调用 getNextException 查看原因。; 糟糕的 SQL 语法 []; 嵌套异常是 org.postgresql.util.PSQLException: ERROR: syntax error at or near "ALL" Position: 8

标签： postgresqlspring-bootmybatis

解决方案

INSERT ALL对 Postgres 无效。我不知道 MyBatis，但可能需要以下内容：

<insert id="insertCommunityItemList" parameterType='list'>
  INSERT INTO product.m_community_item (
          community_id,
          item_id,
          public_start_dt,
          public_end_dt,
          handle_end_dt,
          maker_community_id,
          maker_approval_flg,
          maker_approval_user_id,
          ins_user_id,
          up_user_id)
  VALUES 
  <foreach collection="list" item="element" index="index">
    (
        #{element.communityId},
        #{element.itemId},
        #{element.publicStartDt},
        #{element.publicEndDt},
        #{element.handleEndDt},
        #{element.makerCommunityId},
        #{element.makerApprovalFlg},
        #{element.makerApprovalUserId},
        #{element.insUserId},
        #{element.insUserId}
    )
  </foreach>
  on conflict on constraint m_community_item_pk
  do update set
      public_start_dt = excluded.public_start_dt,
      public_end_dt = excluded.public_end_dt,
      up_dttm = current_timestamp,
      up_user_id = excluded.public_end_dt
</insert>

foreach 循环应该只遍历实际的行。和部分只能出现一次INSERT INTO。on conflict

推荐阅读