开发者社区> 问答> 正文

为什么mybatis的sql文件 里的sql要是传了参数就会报错?报错

如题, 如果sql是个需要设置参数的,就会报出找不到参数的错误

2016-01-08 15:33:45 JRebel: Monitoring Spring bean definitions in 'jar:file:/D:/eclipse_workspace/canyin/WebRoot/WEB-INF/lib/spring-jdbc-3.2.1.RELEASE.jar!/org/springframework/jdbc/support/sql-error-codes.xml'.
org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLException: Unknown column 'PhoneNum' in 'where clause'
### The error may exist in com/elinesoft/map/User.xml
### The error may involve user.findUserByUsernameAndPassword-Inline
### The error occurred while setting parameters
### SQL: SELECT * FROM sys_user WHERE ( UserName = ? OR PhoneNum = ? ) AND password = ?  AND Status != 0
### Cause: java.sql.SQLException: Unknown column 'PhoneNum' in 'where clause'
; bad SQL grammar []; nested exception is java.sql.SQLException: Unknown column 'PhoneNum' in 'where clause'
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)
	at com.sun.proxy.$Proxy19.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
	at com.elinesoft.dao.impl.system.UserDaoImpl.findUserByUsernameAndPassword(UserDaoImpl.java:23)
	at com.elinesoft.service.impl.system.UserServiceImpl.login(UserServiceImpl.java:60)
	at com.elinesoft.service.impl.system.UserServiceImpl$$FastClassByCGLIB$$26901b08.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
	at com.elinesoft.service.impl.system.UserServiceImpl$$EnhancerByCGLIB$$8fc62ffc.login(<generated>)
	at com.elinesoft.controller.LoginController.login(LoginController.java:58)
	at com.elinesoft.controller.LoginController$$FastClassByCGLIB$$9d90610b.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:55)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
	at com.elinesoft.controller.LoginController$$EnhancerByCGLIB$$14d941a3.login(<generated>)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
	at com.elinesoft.servlet.CustomerDispatcherServlet.doService(CustomerDispatcherServlet.java:28)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:165)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:203)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:181)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
	at org.apache.catalina.core.StandardContextValve.__invoke(StandardContextValve.java:122)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
	at org.apache.catalina.core.StandardHostValve.__invoke(StandardHostValve.java:170)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:957)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Unknown column 'PhoneNum' in 'where clause'
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:770)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:1392)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at net.bull.javamelody.JdbcWrapper.doExecute(JdbcWrapper.java:396)
	at net.bull.javamelody.JdbcWrapper$StatementInvocationHandler.invoke(JdbcWrapper.java:135)
	at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:282)
	at com.sun.proxy.$Proxy24.execute(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)
	at com.sun.proxy.$Proxy25.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
	at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:54)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:115)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:62)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)
	... 70 more



这是为什么呢? 我该怎么才能解决呢?sql放在数据库中执行是没有错的

展开
收起
爱吃鱼的程序员 2020-06-10 15:12:36 638 0
1 条回答
写回答
取消 提交回答
  • https://developer.aliyun.com/profile/5yerqm5bn5yqg?spm=a2c6h.12873639.0.0.6eae304abcjaIB

    终于没有这个错误了,应该是我哪个时候不小心动了关于mybatis的框架必须的类的原因,导致mybatis的sqlMap设置参数出现问题,现在我替换了所有的框架相关的类,终于不再报这个错误
    所有的框架相关的类是指哪些啊?我现在也出现了这个问题。。。求教楼主是怎么解决的啊?这种问题估计是没加单引号''引起SELECT*FROMsys_userWHERE(UserName=#{userName}ORPhoneNum=#{userName})ANDpassword=#{password}ANDStatus!=0是这样的sql很明显的错误啊:java.sql.SQLException:Unknowncolumn'PhoneNum'。 Java代码里面是不是<spanstyle="font-size:13.3333px;">PhoneNum呢...注意大小写回复<aclass='referer'target='_blank'>@稻草鸟人:嗯谢谢<atarget='_blank'>@合取debug看看吧,没什么好办法,无非就是参数,SQL啦回复<aclass='referer'target='_blank'>@稻草鸟人:这里虽然是报这个错但是是代码里的问题,而且数据库里不分大小写的回复<aclass='referer'target='_blank'>@合取:这里是说没有这个列...你确认过?还是你的数据库字段区分大小写嘛??不是sql语句的错误啊,在数据库里执行都是没问题的

    2020-06-10 15:12:55
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
SQL Server在电子商务中的应用与实践 立即下载
GeoMesa on Spark SQL 立即下载
Java Spring Boot开发实战系列课程【第6讲】:Spring Boot 2.0实战MyBatis与优化(Java面试题) 立即下载