java - mybatis-plus使用静态工具查询时出现语句错误报错？

Question

两个类，User中包含order

@Data@AllArgsConstructor@NoArgsConstructor@ToString@Table(name = "user_order",charset = MySqlCharsetConstant.UTF8,engine = MySqlEngineConstant.InnoDB)public class Order {    @Column(name = "id",type = MySqlTypeConstant.VARCHAR,length = 3,comment = "用户id")    private String user_id;    @Column(name = "address",type = MySqlTypeConstant.VARCHAR,length = 100,comment = "收货地址")    private String user_address;}

@Data@AllArgsConstructor@NoArgsConstructor@Accessors(chain = true)@ToString@Table(name = "user",charset = MySqlCharsetConstant.UTF8,engine = MySqlEngineConstant.InnoDB)public class User {    @Column(name = "id",isKey = true,type = MySqlTypeConstant.VARCHAR,length = 100,comment = "主键ID")    private String id;    @TableField("user_name")    private String name;    @TableField("user_age")    private Integer age;    @TableField("user_email")    private String email;    @TableField(exist = false)    private List<Order> orders;}

其中mapper和mapper.xml均用mybatis-plus自动生成
UserMapper

public interface UserServcie extends IService<User> {}

UserMapperImpl

@Servicepublic class UserServcieImp extends ServiceImpl<UserMapper,User> implements UserServcie{}

OrderMapper

public interface OrderService extends IService<Order> {}

OrderMapperImpl

@Servicepublic class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {}

测试

// 测试Db.lambdaQuery(Order.class).in(Order::getUser_id,id).list();

报错如下

org.apache.ibatis.exceptions.PersistenceException: ### Error querying 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 'order      WHERE  (user_id IN ('3'))' at line 1### The error may exist in com/train/spring_mybatis_plus/mapper/OrderMapper.java (best guess)### The error may involve defaultParameterMap### The error occurred while setting parameters### SQL: SELECT  user_id,user_address  FROM order      WHERE  (user_id IN (?))### 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 'order      WHERE  (user_id IN ('3'))' at line 1    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:156)    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166)    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)    at com.sun.proxy.$Proxy80.selectList(Unknown Source)    at com.baomidou.mybatisplus.extension.conditions.query.ChainQuery.lambda$list$1d3f6527$1(ChainQuery.java:39)    at com.baomidou.mybatisplus.extension.toolkit.SqlHelper.execute(SqlHelper.java:318)    at com.baomidou.mybatisplus.extension.conditions.ChainWrapper.execute(ChainWrapper.java:65)    at com.baomidou.mybatisplus.extension.conditions.query.ChainQuery.list(ChainQuery.java:39)    at com.train.spring_mybatis_plus.service.UserServcieImp.selectUserOrder(UserServcieImp.java:61)    at com.train.spring_mybatis_plus.service.UserServcieImp$$FastClassBySpringCGLIB$$ac685e70.invoke(<generated>)    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)    at org.springframework.aop.framework.CglibAopProxy.invokeMethod(CglibAopProxy.java:386)    at org.springframework.aop.framework.CglibAopProxy.access$000(CglibAopProxy.java:85)    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:703)    at com.train.spring_mybatis_plus.service.UserServcieImp$$EnhancerBySpringCGLIB$$b8ea8e2d.selectUserOrder(<generated>)    at com.train.spring_mybatis_plus.SpringMybatisPlusApplicationTests.testSelectOrderByUserId(SpringMybatisPlusApplicationTests.java:74)    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)    at java.lang.reflect.Method.invoke(Method.java:498)    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)    at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)    at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)    at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)    at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)    at java.util.ArrayList.forEach(ArrayList.java:1257)    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)    at java.util.ArrayList.forEach(ArrayList.java:1257)    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)    at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)    at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)    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 'order      WHERE  (user_id IN ('3'))' at line 1    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)    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.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:80)    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:65)    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:333)    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:90)    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)    ... 88 more

这种报错是什么造成的呢？如何破

Answer 1

Order类字段注解要用@TableField，不是 @Column