使用Spring中Service更新模块测试代码时出错org.springframework.jdbc.BadSqlGrammarException: ### Error updating data

精选原创

让世界更美好 2024-02-16 13:08:26 博主文章分类：Java ©著作权

文章标签 java spring lua 文章分类 Java 后端开发 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者让世界更美好的原创作品，谢绝转载，否则将追究法律责任

一概述

在通过Spring框架中的Service对数据库进行更新操作时报错出现了这个错误，这个错误在开发中应该还是比较常见的，主要是因为未设置更新的set引起的。

二：具体说明

<1>问题代码

@Test
public void testUpdate() {
    userService.lambdaUpdate()
        .gt(User::getAge,28)
        .likeRight(User::getName,"王")
        .update();
}

<2>问题的具体说明和解释

2.1问题的详细错误信息

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 'WHERE (age > 28 AND name LIKE '王%')' at line 3
### The error may exist in org/example/space3v/yonghengyu/stack/mapper/UserMapper.java (best guess)
### The error may involve org.example.space3v.yonghengyu.stack.mapper.UserMapper.update-Inline
### The error occurred while setting parameters
### SQL: UPDATE user        WHERE (age > ? AND name LIKE ?)
### 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 'WHERE (age > 28 AND name LIKE '王%')' at line 3
; 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 'WHERE (age > 28 AND name LIKE '王%')' at line 3

	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at jdk.proxy2/jdk.proxy2.$Proxy56.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:65)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
	at jdk.proxy2/jdk.proxy2.$Proxy58.update(Unknown Source)
	at com.baomidou.mybatisplus.extension.conditions.update.ChainUpdate.update(ChainUpdate.java:45)
	at com.baomidou.mybatisplus.extension.conditions.update.ChainUpdate.update(ChainUpdate.java:35)
	at org.example.space3v.yonghengyu.stack.test.ServiceTest.testUpdate(ServiceTest.java:55)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:578)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: 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 'WHERE (age > 28 AND name LIKE '王%')' at line 3
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:578)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at jdk.proxy3/jdk.proxy3.$Proxy76.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:56)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:85)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:578)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
	... 40 more

使用Spring中Service更新模块测试代码时出错org.springframework.jdbc.BadSqlGrammarException: ### Error updating data_spring

使用Spring中Service更新模块测试代码时出错org.springframework.jdbc.BadSqlGrammarException: ### Error updating data_spring_02

使用Spring中Service更新模块测试代码时出错org.springframework.jdbc.BadSqlGrammarException: ### Error updating data_lua_03

2.2错误的详细说明以及解析

错误消息表明在更新查询中存在 SQL 语法错误。错误消息具体提到在第三行附近的 'WHERE (age > 28 AND name LIKE '王%')' 处有语法错误。遇到的错误消息表明在新查询中存在 SQL 语法错误。错误消息具体提到在第三行附近的 'WHERE (age > 28 AND name LIKE '王%')' 处有语法错误。

要解决这个问题，需要检查 SQL 查询语句的语法。看起来 UPDATE 语句缺少在 UPDATE 关键字后面的表名。正确的语法应该类似于 UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition。

确保在 UPDATE 关键字后面指定表名，并提供要更新的正确列名和值。此外，确保 WHERE 子句正确构造，包含所需的条件。

根据提供的 Java 代码，你正在使用 MyBatis Plus 的 LambdaWrapper 进行更新查询。根据你的代码，你正在更新符合以下条件的用户数据：

年龄大于 28
名字以 "王" 开头

首先，请确保已经正确导入 MyBatis Plus 相关的类和方法。确保你已经正确配置了 MyBatis Plus 的依赖项，并且 UserService 类已经正确注入了。

接下来，请确保你的数据库表名与 UserService 类中的实体类名一致。确保你已经在实体类上使用了 @TableName 注解，并指定了正确的表名。

另外，请确保你的实体类中的字段名与数据库表中的列名一致。确保你已经在实体类的字段上使用了 @TableField 注解，并指定了正确的列名。

最后，请确保你的数据库连接配置正确，并且数据库中存在满足查询条件的数据。

我出现这个错误的原因是未设置set引起的，设置set之后，运行之后就不会报错了。

<3>问题的解决

在我的代码加上set之后，运行就不会报错了。

 @Test
         public void testUpdate() {
              userService.lambdaUpdate()
                      .gt(User::getAge,28)
                      .likeRight(User::getName,"王")
                      .set(User::getEmail,"3456907856@qq.com")
                      .update();
         }

至此，这个问题得到了解决。