FAQ（55）：java.sql.SQLException: You have an error in your SQL syntax; check the manual that correspon

最新推荐文章于 2024-07-27 14:50:38 发布

后台技术汇

最新推荐文章于 2024-07-27 14:50:38 发布

阅读量1.2k

点赞数

分类专栏： FAQ FAQ

本文链接：https://blog.csdn.net/qq_29166327/article/details/79099746

版权

FAQ 同时被 2 个专栏收录

95 篇文章 0 订阅

订阅专栏

FAQ

91 篇文章 2 订阅

订阅专栏

springmvc + spring ：

看Log：

java.sql.SQLException: 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 '' at line 1
	com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)
	com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
	com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
	com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
	com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
	com.mysql.jdbc.Statement.executeQuery(Statement.java:822)
	com.User.Dao.UserDaoImpl.getUserList(UserDaoImpl.java:91)
	com.User.Service.UserServiceImpl.getUserList(UserServiceImpl.java:24)
	com.User.Controller.UserController.getUserList(UserController.java:113)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	java.lang.reflect.Method.invoke(Unknown Source)
	org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
	org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
	org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
	org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

解决：sql的一处语法出错，因为使用了jdbc，所以稍显繁琐，错在LIKE CONCAT('%','xxx','%')没写正确。
原：

		sql = "select u.userCode,u.userName,u.gender,u.birthday,u.phone,r.roleName "
				+ " from smbms_user u,smbms_role r"
				+ " where u.userName  like CONCAT('%',#{"+queryUserName+"},'%')" 
						+ " and u.userRole="+_queryUserRole
						+ " and u.userRole=r.id;";

改：

	       sql = "select u.userCode,u.userName,u.gender,u.birthday,u.phone,r.roleName "
				+ " from smbms_user u,smbms_role r"
				+ " where u.userName  like CONCAT('%','"+queryUserName+"','%')" 
						+ " and u.userRole="+_queryUserRole
						+ " and u.userRole=r.id;";