java使用mybatis报错之Mysql查询表字段带关键字

最新推荐文章于 2024-05-21 14:39:26 发布

原创最新推荐文章于 2024-05-21 14:39:26 发布 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #BadSqlGrammarException #mysql #关键字 #查询

数据库专栏收录该内容

10 篇文章

订阅专栏

本文记录了一次因SQL语法问题导致的HTTP 500错误，并详细分析了错误原因及解决过程。错误出现在MyBatis执行SQL时，由于字段名包含保留字未被正确引用引起。通过在字段名周围加上反引号(`)，问题得以解决。

HTTP Status 500 - Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:

type Exception report

message Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'condition conditions, createnum, send_num, use_num, send_start_time, 
    send_e' at line 3
### The error may exist in file [D:\workspace\SpingWind\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\serverPub\WEB-INF\classes\mapper\mysql\mall\FCouponMapper.xml]
### The error may involve mapper.mysql.mall.FCouponMapper.selectByPrimaryKey-Inline
### The error occurred while setting parameters
### SQL: select            id, name , type, money, condition conditions, createnum, send_num, use_num, send_start_time,      send_end_time, use_start_time, use_end_time, add_time, remark, status , img_url, send_condition,      validity         from f_coupon     where id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'condition conditions, createnum, send_num, use_num, send_start_time, 
    send_e' at line 3
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'condition conditions, createnum, send_num, use_num, send_start_time, 
    send_e' at line 3
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973)
	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	com.hbesct.core.controller.GrantedFilter.doFilter(GrantedFilter.java:136)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

------------------------------------------------------------------------------------

我们的SQL查询表字段中带有以关键字命名的字段时，Mybatis会报错，则查询字段需要使用特殊符号将其引起来（·），实例如下：

select id,name,condition,status,age from user;

修改后的SQL：

select id,`name`,`condition`,`status`,age from user;