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 xxxx
一句SQL虐的你头大----我是如此的渣
在做update修改语句的时候出现了这个问题
原来的SQL
<update id="TestUpdate">
update pc_mdm_product_certificate_relation
<set>
<if test="registerId != null and registerId != ''">
register_id = #{registerId}
</if>
<if test="productCode != null and productCode != ''">
product_code = #{productCode}
</if>
<if test="registerNo != null and registerNo != ''">
register_no = #{registerNo}
</if>
<if test="tenantId != null and tenantId != ''">
tenant_id = #{tenantId}
</if>
<if test="updatedBy != null and updatedBy != ''">
updated_by = #{updatedBy}
</if>
<if test="modifierId != null and modifierId != ''">
modifier_id =#{modifierId}
</if>
<if test="updatedAt != null ">
updated_at = #{updatedAt}
</if>
<if test="dr != null and dr != ''">
dr = #{dr}
</if>
</set>
where id = #{id}
</update>
控制台报错:
org.springframework.jdbc.BadSqlGrammarException:
### 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 'product_code = 'SDFA456'
register_no =' at line 5
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: update pc_mdm_product_certificate_relation SET register_id = ? product_code = ? register_no = ? updated_at = ? where id = ?
### 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 'product_code = 'SDFA456'
register_no =' at line 5
; 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 'product_code = 'SDFA456'
register_no =' at line 5
更改之后
<update id="TestUpdate">
update pc_mdm_product_certificate_relation
<set>
<if test="registerId != null and registerId != ''">
register_id = #{registerId},
</if>
<if test="productCode != null and productCode != ''">
product_code = #{productCode},
</if>
<if test="registerNo != null and registerNo != ''">
register_no = #{registerNo},
</if>
<if test="tenantId != null and tenantId != ''">
tenant_id = #{tenantId},
</if>
<if test="updatedBy != null and updatedBy != ''">
updated_by = #{updatedBy},
</if>
<if test="modifierId != null and modifierId != ''">
modifier_id =#{modifierId},
</if>
<if test="updatedAt != null ">
updated_at = #{updatedAt},
</if>
<if test="dr != null and dr != ''">
dr = #{dr}
</if>
</set>
where id = #{id}
</update>
瞬间就绿了!!,看见了吗 ,就是因为逗号,没加逗号的原因,问题解决了 然而我并不放弃,为什么这样呢?
有没有注意到最后一个<if>标签中我没有添加逗号,
如果添加了会怎样!!!!------>请看
竟然是200 成功了,,我有点失望......(是不是说我有病,是不是- _ -!!)
然而我又将第一个<if>标签中的参数置null,没有值---->
依旧成功.
由此得出结论:<set></set>标签是去除SQL中多余的逗号,最好在最后不要添加不必要的逗号
更换标签,使用
<trim prefix="set" suffixOverrides=","></trim>
<update id="TestUpdate">
update pc_mdm_product_certificate_relation
<trim prefix="set" suffixOverrides=",">
<if test="registerId != null and registerId != ''">
register_id = #{registerId},
</if>
<if test="productCode != null and productCode != ''">
product_code = #{productCode},
</if>
<if test="registerNo != null and registerNo != ''">
register_no = #{registerNo},
</if>
<if test="tenantId != null and tenantId != ''">
tenant_id = #{tenantId},
</if>
<if test="updatedBy != null and updatedBy != ''">
updated_by = #{updatedBy},
</if>
<if test="modifierId != null and modifierId != ''">
modifier_id =#{modifierId},
</if>
<if test="updatedAt != null ">
updated_at = #{updatedAt},
</if>
</trim>
where id = #{id}
</update>
注意此时 我将第第一个参数registerId置空不传值,并且最后一个<if>标签中有逗号------->执行结果
那也就是说此种方式也可以去掉SQL中多余的逗号
解释属性:
- prefix:给trim标签范围内的sql语句加上前缀,这个很灵活,比如说常见的:加上where条件 加上左括号 加上set 等等,下边会给例子的
- suffix:给trim标签范围内的sql语句加上后缀,用法和上边差不多,他们两个可以说是一对,但是也可以有所不同
- prefixOverrides:去除trim标签范围内多余的前缀内容
- suffixOverrides:去除trim标签范围内多余的后缀内容,如:suffixOverrides=","就是用来去除trim标签内sql语句多余的后缀","
这个是度娘回答的 但是以上实验中可以看到suffixOverrides可以去除多余的逗号,没有前后缀之分,可能我哪个点没有涉及到.欢迎点评
最后附上一些常用的标签使用,,以方便我这个爱健忘的家伙---嘻嘻!
//insert sql
<insert id="insert" parameterType="Contract">
insert into contract
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">id,</if>
<if test="userId != null">user_id,</if>
<if test="createDate != null">create_date,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">#{id,jdbcType=BIGINT},</if>
<if test="userId != null">#{userId,jdbcType=BIGINT},</if>
<if test="createTime != null">#{createDate,jdbcType=TIMESTAMP},</if>
</trim>
</insert>
//update sql
update user
<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">
<if test="name != null and name.length()>0"> name=#{name} , </if>
<if test="gender != null and gender.length()>0"> gender=#{gender} , </if>
</trim>
对了 ,此次验证我还为了实验:
如果修改的时候参数为一个对象可以直接写不用@param标识起别名,并且xml标签中也不用写parameterType也是可以的
mapper接口方法
xml中
如果使用@param
那么xml中对应的属性要用po点出来
使用了parameterType也是没问题的
mapper接口
xml中
\(^o^)/~今天就先总结到这里啦!!!欢迎指点评价哦!,希望我这个渣渣技术方面能步步提升---嘻嘻(#^.^#)