com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; che

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/GoSaint/article/details/78762308

出现此种错误,我暂时遇到了两次。
1 我的字段的名称和数据库的关键字重合。
这里写图片描述
上图中的desc是默认降序排列的意思。
2 第二次出现的异常是我在重构代码阶段遇到的一个bug.不过我暂时不能理解,虽然解决了错误。
删除操作的代码如下:

 public void deleteProduct(Long id) {
        sql = "DELETE  FROM t_product WHERE id= ?";
        JDBCTemplate.templateDML(sql,id);
    }

或者如下的写法:都是正确的。

 public void deleteProduct(Long id) {
        sql = "DELETE  FROM t_product WHERE id="+id;
        JDBCTemplate.templateDML(sql);
    }

但是遇上查询一个结果的时候出现了问题:

public Product getProductById(Long id) {
        sql="SELECT * FROM  t_product  WHERE id=?";
        List<Product> list = JDBCTemplate.templateDQL(sql,id);
        return list.size()==1?list.get(0):null;
    }

修改之后又可以啦;

public Product getProductById(Long id) {
        sql="SELECT * FROM  t_product  WHERE id="+id;
        List<Product> list = JDBCTemplate.templateDQL(sql);
        return list.size()==1?list.get(0):null;
    }

有点疑惑。

阅读更多
换一批

遇到 You have an error in your SQL syntax

09-04

先说一下,小弟新手,是编写的一个小型SQL数据库查询功能时遇到的问题。rnrn贴一下问题日志:rn九月 04, 2014 1:29:02 上午 org.apache.catalina.core.StandardWrapperValve invokern严重: Servlet.service() for servlet [jsp] in context with path [/J2EEWEB] threw exception [javax.servlet.ServletException: 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 'NAME='123'' at line 1] with root causerncom.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 'NAME='123'' at line 1rn at sun.reflect.GeneratedConstructorAccessor12.newInstance(Unknown Source)rn at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)rn at java.lang.reflect.Constructor.newInstance(Constructor.java:526)rn at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)rn at com.mysql.jdbc.Util.getInstance(Util.java:386)rn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)rn at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)rn at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)rn at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)rn at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)rn at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)rn at beans.apps.Resultm.search(Resultm.java:25)rn at org.apache.jsp.jsp.result_jsp._jspService(result_jsp.java:74)rn at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)rn at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)rn at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)rn at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)rn at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)rn at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)rn at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1002)rn at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)rn at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)rn at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)rn at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)rn at java.lang.Thread.run(Thread.java:744)rn九月 04, 2014 1:31:03 上午 org.apache.catalina.core.StandardWrapperValve invokern严重: Servlet.service() for servlet [jsp] in context with path [/J2EEWEB] threw exception [javax.servlet.ServletException: 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 'NAME='123'' at line 1] with root causerncom.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 'NAME='123'' at line 1rn at sun.reflect.GeneratedConstructorAccessor12.newInstance(Unknown Source)rn at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)rn at java.lang.reflect.Constructor.newInstance(Constructor.java:526)rn at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)rn at com.mysql.jdbc.Util.getInstance(Util.java:386)rn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)rn at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)rn at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)rn at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)rn at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)rn at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)rn at beans.apps.Resultm.search(Resultm.java:25)rn at org.apache.jsp.jsp.result_jsp._jspService(result_jsp.java:74)rn at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)rn at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)rn at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)rn at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)rn at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)rn at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)rn at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1002)rn at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)rn at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)rn at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)rn at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)rn at java.lang.Thread.run(Thread.java:744)rnrnrnrn下面是搜索的类rn[code=javascript]rnpackage beans.apps;rnimport java.sql.*;rnrnimport beans.db.GetStatement;rnrnpublic class Resultm rn private ResultSet rs;rn private String sql;rn private GetStatement ges=new GetStatement();rn public Resultm()rn public ResultSet search(String name,String gender,String t_num) throws ClassNotFoundException, SQLExceptionrn //连接数据库rn sql="select * from sheet where 1=1 ";rn if(!name.equals(""))rn sql=sql+" and NAME like '%"+name+"%'";rn rn if(!gender.equals(""))rn sql=sql+" and SEX='"+gender+"'";rn rn if(!t_num.equals(""))rn sql=sql+" and num like '%"+t_num+"%'";rn rn //以上是sql拼接rn rn rs=ges.getst().executeQuery(sql);rn rn rn return rs;rnrnrn rn rn rn public void closed() throws SQLExceptionrn ges.closeAll();rn rn [/code]rnrn查询SQL中三个关键字内容 NAME SEX numrn我在查询后两个关键字是没有问题的只有NAME报错rn我最不理解的是为什么报错的是'NAME=123' 123是我输入的东西啊。rn希望高手指教rn

提示:You have an error in your SQL syntax

11-25

[code=Java]rn/**rn * 添加操作记录rn */rn public Statistics saveStatistics(Integer status, List batchProcess) rn Statistics stat = new Statistics();rn Article article = getArticle();rn User user = getLoggedOnUser();rn stat.setType(2);//文章rn stat.setUserId(user.getId());rn Date date = new Date();rn SimpleDateFormat dateformat=new SimpleDateFormat("yyyy-MM-dd");rn dateformat.format(date);rn stat.setStatisticsTime(date);rn if(status != null) rn switch (status) rn case 0://重申rn stat.setDescribe(user.getUsername() + "重申了"+article.getCommUser().getUserName()+"发表的文章" + article.getTitle());rn break;rn case 1://通过rn stat.setDescribe(user.getUsername() +article.getCommUser().getUserName()); //article.getTitle()rn break;rn case 2://屏蔽rn stat.setDescribe(user.getUsername() + "屏蔽了"+article.getCommUser().getUserName()+"发表的文章" + article.getTitle());rn break;rn case 3://删除rn stat.setDescribe(user.getUsername() + "删除了"+article.getCommUser().getUserName()+"发表的文章" + article.getTitle());rn break;rn rn rn if(batchProcess != null) rn stat.setDescribe(user.getUsername() + "处理了"+article.getCommUser().getUserName()+"发表的文章" + article.getTitle());rn rn [color=#FF0000statisticsManager.save(stat);][/color] //这里执行走不了rn this.saveStatisticsDesc(stat,article);rn return stat;rn rn[/code]rnrn错误信息如下:rnrnHibernate: insert into ibokee_statistics (describe, statisticsTime, type, userId) values (?, ?, ?, ?)rnWARN ["http-bio-8080"-exec-9] JDBCExceptionReporter.logExceptions(100) | SQL Error: 1064, SQLState: 42000rnERROR ["http-bio-8080"-exec-9] JDBCExceptionReporter.logExceptions(101) | 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 'describe, statisticsTime, type, userId) values ('adminzhender', '2011-11-25 10:4' at line 1rn

没有更多推荐了,返回首页