如何解析org.springframework.jdbc.badsqlgrammareXception

Question

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

exception=org.springframework.jdbc.badsqlgrammareXception：gulfnet.tts.inthanon.data.product.mapper.catalogmapper.insertCommunityItemlist（批索引#1)失败。原因：java.sql.BatchUpdateException：批条目0 INSERT ALL被中止。调用getNextException查看原因。；糟糕的SQL语法[]；嵌套异常是org.PostgreSQL.util.psqlException：error：“all”位置或附近的语法错误：8

Answer 1

您可以使用java迭代器迭代您的communityItemRecordList并一个接一个地插入数据库。

//java
communityItemRecordList.forEach(c -> {
    mapperInstance.insert(c);
});
//mapper xml
<insert id="insert">
    ....
</insert>

我认为在这个演示中使用动态SQL是不合适的。

我刚刚使用了foreach元素并测试成功。代码：

//java
@RunWith(SpringRunner.class)
@SpringBootTest
public class SmsContentDaoTest {

    @Autowired
    private SmsContentDao smsContentDao;

    @Test
    public void insertList() {
        List<SmsContentDO> list = new ArrayList<>();
        SmsContentDO item = SmsContentDO.builder().name("john").company("abc").job("farmer").build();
        list.add(item);
        item = SmsContentDO.builder().name("tom").company("nba").job("player").build();
        list.add(item);
        int rows = smsContentDao.insertList(list);
        System.out.println(rows);//2
    }
}
//mapper interface
int insertList(List<SmsContentDO> list);
//mapper xml, the dialect of mysql 
<!-- int insertList(List<SmsContentDO> list);-->
    <insert id="insertList">
        insert into
            sms_content (`name`,`company`,`job`)
        values
        <foreach collection="list" item="item" separator=",">
            (#{item.name},#{item.company},#{item.job})
        </foreach>
    </insert>