java - sql insert语法报错?

Question

写了一个简单的插入语句，但是一直报错：

### Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')values( 'hello' at line 11 ### The error may exist in file [D:\mes-master\mes-master\ruoyi-system\target\classes\mapper\system\pnMapperRuKu.xml] ### The error may involve com.ruoyi.system.mapper.pnMapperRuKu.insertPnAssertRukuDetails-Inline ### The error occurred while setting parameters ### SQL: insert into pnAssertRukuDetails ( storageRoom, )values( ?, ) ### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')values( 'hello' at line 11 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')values( 'hello' at line 11

调用方法：

@Override
    public int insertPnCode(PnModelInOutInventory pnModelInOutInventory) {
//        pnMapperRuKu.insertPnAssertRuku(pnModelInOutInventory);
        return pnMapperRuKu.insertPnAssertRukuDetails(pnModelInOutInventory);
    }


mapper.xml----
<insert id="insertPnAssertRukuDetails" parameterType="com.ruoyi.system.domain.PnModelInOutInventory">
        insert into pnAssertRukuDetails (
     <if test="parentPnAssertRukuId != null and parentPnAssertRukuId != '' ">parent_pnAssertRuku_id,</if>
     <if test="materialInformation != null and materialInformation != '' ">materialInformation,</if>
     <if test="numbersRuKu != null and numbersRuKu != '' ">numbersRuKu,</if>
     <if test="qualified != null and qualified != '' ">qualified,</if>
     <if test="unit != null and unit != '' ">unit,</if>
     <if test="storageRoom != null and storageRoom != ''">storageRoom,</if>
     <if test="sn != null and sn != ''">sn,</if>
     <if test="remark != null and remark != ''">remark,</if>
     <if test="batchNumber != null and batchNumber != ''">batchNumber,</if>
    )values(
     <if test="parentPnAssertRukuId != null and parentPnAssertRukuId != '' ">#{parentPnAssertRukuId},</if>
     <if test="materialInformation != null and materialInformation != '' ">#{materialInformation},</if>
     <if test="numbersRuKu != null and numbersRuKu != '' ">#{numbersRuKu},</if>
     <if test="qualified != null and qualified != '' ">#{qualified},</if>
     <if test="unit != null and unit != '' ">#{unit},</if>
     <if test="storageRoom != null and storageRoom != ''">#{storageRoom},</if>
     <if test="sn != null and sn != ''">#{sn},</if>
     <if test="remark != null and remark != ''">#{remark},</if>
     <if test="batchNumber != null and batchNumber != ''">#{batchNumber},</if>
    )
</insert>

我对比了Mapper.xml中的语法，包括调整英文符号，调整英文的括号；都没有作用；

有么有大佬帮忙看下，问题出在哪里？？

Answer 1

问题点在于: 类似如下示例SQL

name后面的 --> , <--

insert newschema.new_table (name,) values ('hello',);

Answer 2

问题出在你的 SQL 插入语句中，特别是在字段列表和值列表的结尾处。由于你使用了 <if> 标签来动态添加字段和值，如果所有的 <if> 条件都不满足（即所有字段都为空或未设置），那么 SQL 语句的字段列表和值列表的末尾将会包含多余的逗号，这会导致 SQL 语法错误。

为了解决这个问题，你可以使用 MyBatis 的 <trim> 标签来自动去除多余的逗号和可能的空白。<trim> 标签提供了 prefix、suffix、prefixOverrides 和 suffixOverrides 属性，可以帮助你更好地控制 SQL 语句的生成。

下面是修改后的 mapper.xml 中的 <insert> 语句：

<insert id="insertPnAssertRukuDetails" parameterType="com.ruoyi.system.domain.PnModelInOutInventory">
    INSERT INTO pnAssertRukuDetails 
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="parentPnAssertRukuId != null and parentPnAssertRukuId != ''">parent_pnAssertRuku_id,</if>
        <if test="materialInformation != null and materialInformation != ''">materialInformation,</if>
        <if test="numbersRuKu != null and numbersRuKu != ''">numbersRuKu,</if>
        <if test="qualified != null and qualified != ''">qualified,</if>
        <if test="unit != null and unit != ''">unit,</if>
        <if test="storageRoom != null and storageRoom != ''">storageRoom,</if>
        <if test="sn != null and sn != ''">sn,</if>
        <if test="remark != null and remark != ''">remark,</if>
        <if test="batchNumber != null and batchNumber != ''">batchNumber,</if>
    </trim>
    <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
        <if test="parentPnAssertRukuId != null and parentPnAssertRukuId != ''">#{parentPnAssertRukuId},</if>
        <if test="materialInformation != null and materialInformation != ''">#{materialInformation},</if>
        <if test="numbersRuKu != null and numbersRuKu != ''">#{numbersRuKu},</if>
        <if test="qualified != null and qualified != ''">#{qualified},</if>
        <if test="unit != null and unit != ''">#{unit},</if>
        <if test="storageRoom != null and storageRoom != ''">#{storageRoom},</if>
        <if test="sn != null and sn != ''">#{sn},</if>
        <if test="remark != null and remark != ''">#{remark},</if>
        <if test="batchNumber != null and batchNumber != ''">#{batchNumber},</if>
    </trim>
</insert>

这里，<trim> 标签的 prefix 和 suffix 属性分别用于在字段列表和值列表的开头和结尾添加括号。suffixOverrides="," 属性则用于去除字段列表和值列表末尾的逗号。这样，无论哪些 <if> 条件满足，生成的 SQL 语句都会是正确的。