com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ‘password=’rrrr’

spring boot配合mybatis写一个增删改查的小demo,在更新的的时候一顿报错

// controller
    @RequestMapping("update")
    public String UpdateUser(){
        User user = new User();
        user.setUserId("6cf9a91f-2815-4813-85a4-12fd610a124a");
        user.setUserName("奥尔特2");
        user.setPassword("rrrr");
//        user.setLoved("galaxy");

        return JSON.toJSONString(userMapper.updateUserById(user));
    }
// mapper interface
    // 更新用户
    int updateUserById(User user);

// mapper xml
    <update id="updateUserById" parameterType="com.xiaosheng.springbootdemo.pojo.User">
        update blognote.user
        <set>
            <if test="UserName != null">
                user_name = #{UserName}
            </if>
            <if test="password!=null">
                 password=#{password}
            </if>
            <if test="field!=null">
                 field=#{field}
            </if>
            <if test="loved!=null">
                loved=#{loved}
            </if>
        </set>
        where user_id = #{UserId}
    </update>

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'password='rrrr'

2022-08-24 10:52:12.465 ERROR 7372 --- [nio-8082-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'password='rrrr' 
        where user_id = '6cf9a91f-2815-4813-85a4-12fd610a124a'' at line 5
### The error may exist in file [E:\proj\learn\springbootLearn\test\spring-boot-demo\target\classes\mybatis\UserMapper.xml]
### The error may involve com.xiaosheng.springbootdemo.dao.UserMapper.updateUserById-Inline
### The error occurred while setting parameters
### SQL: update blognote.user          SET user_name = ?                                            password=?          where user_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'password='rrrr' 
        where user_id = '6cf9a91f-2815-4813-85a4-12fd610a124a'' at line 5
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'password='rrrr' 
        where user_id = '6cf9a91f-2815-4813-85a4-12fd610a124a'' at line 5] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'password='rrrr' 
        where user_id = '6cf9a91f-2815-4813-85a4-12fd610a124a'' at line 5
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_112]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_112]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_112]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_112]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:403) ~[mysql-connector-java-5.1.49.jar:5.1.49]

在网上找了好久,报这个错误的全是多了一个空格,或者数据库驱动版本不匹配,或者是批量查询出错

找到问题所在,

是set中if后面语句有,号

    <update id="updateUserById" parameterType="com.xiaosheng.springbootdemo.pojo.User">
        update blognote.user
        <set>
            <if test="UserName != null">
                user_name = #{UserName},
            </if>
            <if test="password!=null">
                 password=#{password},
            </if>
            <if test="field!=null">
                 field=#{field},
            </if>
            <if test="loved!=null">
                loved=#{loved}
            </if>
        </set>
        where user_id = #{UserId}
    </update>

分类: java

站点统计

  • 文章总数:309 篇
  • 分类总数:19 个
  • 标签总数:191 个
  • 运行天数:1006 天
  • 访问总数:124625 人次

浙公网安备33011302000604

辽ICP备20003309号