前两天,上面叫我改一下一个查询的sql语句,改成模糊查询。觉得这个非常简单,于是在mysql中查询之后,看到数据正常,就直接写到代码里面去了,没有测试直接交了上去。今天部门老大突然给我发了一个异常过来,刚刚开始怀疑部门老大发错地方了,结果才发现原来就是自己错了。感觉很没面子,这么点小东西都没做好,刚刚开始还不知道问题出在哪里,怎么看都像是对的,在网上查阅之后发现真的很容易出错,所以贴出来,希望以后不要遇到同样的事情,然后不管多简单的东西,记得测试之后再交上去。
在mysql中的测试语句:select serviceCode from smsconfiginfo where configName like '%aa%'
这样肯定是没问题的。
用预编译PreparedStatement,直接把语句改成了
stmt = con.prepareStatement(select serviceCode from smsconfiginfo where configName like '%?%')
然后stmt.setString(1,serviceNameKeyword);
是否貌似没什么问题。
当查询的时候发现抛这样一个异常:
java.sql.SQLException: No parameters defined during prepareCall()
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ServerPreparedStatement.getBinding(ServerPreparedStatement.java:751)
at com.mysql.jdbc.ServerPreparedStatement.setString(ServerPreparedStatement.java:1857)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:131)
at org.apache.jsp.stat.statSmsMainProcessInclude_jsp._jspService(statSmsMainProcessInclude_jsp.java:195)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:672)
at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:574)
at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:499)
at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:966)
at org.apache.jsp.stat.smsYuanjing_jsp._jspService(smsYuanjing_jsp.java:59)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:541)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
刚刚开始找到问题的根源,只知道自己只改了那一点代码于是先把
stmt = con.prepareStatement(select serviceCode from smsconfiginfo where configName like '%?%')
这个改为
stmt = con.prepareStatement(select serviceCode from smsconfiginfo where configName like '%aa%')
屏蔽掉设置参数,发现异常没有了
问题肯定就出在这个问号上,但是想象设置参数不就是用问号吗
仔细观察,发现这里把问号当成了条件,而不是参数的设置
这样
stmt = con.prepareStatement(select serviceCode from smsconfiginfo where configName like '%'?'%')
就可以了。
别小看字符串的问题,越容易的东西越容易出错。