SQL查询执行MySQL工作台,但不是在生产
SQL查询:SQL查询执行MySQL工作台,但不是在生产
SELECT COUNT(suggestedfood.id) AS num_same, suggestedfood.place_id FROM suggestedfood WHERE suggestedfood.group_id=? AND suggestedfood.selected=? AND suggestedfood.removed=? GROUP BY suggestedfood.place_id LIMIT 1
这个查询能够到MySQL Workbench中运行,但在生产中它抛出这个错误:
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 '? AND suggestedfood.selected=? AND suggestedfood.removed=? GROUP BY suggestedfoo' at line 1
MySQL Error code: 1064
MySQL State: 42000
MySQL服务器位于AWS RDS上,如果有帮助,并且运行SQL查询的代码是Glassfish上的Java容器。还有许多其他查询能够运行,这只是给我麻烦的一个。
如果任何人都可以指出我的一个简单的错误,那就太好了。或者如果有人知道我可以如何更好地诊断这个问题,那将非常感激。
编辑,这里是我的Java代码,如要求group_id is a long
long numSame = 0L;
String count = "SELECT COUNT(" + SUGGESTEDFOOD_ID + ") AS num_same, "
+ SUGGESTEDFOOD_PLACEID + " FROM " + SUGGESTEDFOOD
+ " WHERE " + SUGGESTEDFOOD_GROUP_ID + "=? AND "
+ SUGGESTEDFOOD_SELECTED + "=? AND "
+ SUGGESTEDFOOD_REMOVED + "=? "
+ "GROUP BY "
+ SUGGESTEDFOOD_PLACEID + " LIMIT 1";
PreparedStatement ps = conn.connection.prepareStatement(count);
ps.setLong(1, group_id);
ps.setBoolean(2, true);
ps.setBoolean(3, false);
/* Line 607 */ ResultSet rs = ps.executeQuery(count);
while (rs.next()) {
numSame = rs.getLong("num_same");
}
以下是完整的堆栈跟踪:
com.mysql.jdbc.exceptions.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 '? AND suggestedfood.selected=? AND suggestedfood.Removed=? GROUP BY suggestedfoo' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)
at com.cow10.endpoints.FoodSessionHandler.togglePlace(FoodSessionHandler.java:607)
at com.cow10.endpoints.FoodSessionHandler$Proxy$_$$_WeldClientProxy.togglePlace(Unknown Source)
at com.cow10.endpoints.FoodWebSocket.handleMessage(FoodWebSocket.java:91)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.glassfish.tyrus.core.AnnotatedEndpoint.callMethod(AnnotatedEndpoint.java:477)
at org.glassfish.tyrus.core.AnnotatedEndpoint.access$100(AnnotatedEndpoint.java:87)
at org.glassfish.tyrus.core.AnnotatedEndpoint$WholeHandler$1.onMessage(AnnotatedEndpoint.java:573)
at org.glassfish.tyrus.core.TyrusSession.notifyMessageHandlers(TyrusSession.java:542)
at org.glassfish.tyrus.core.TyrusEndpointWrapper.onMessage(TyrusEndpointWrapper.java:748)
at org.glassfish.tyrus.core.TyrusWebSocket.onMessage(TyrusWebSocket.java:200)
at org.glassfish.tyrus.core.frame.TextFrame.respond(TextFrame.java:135)
at org.glassfish.tyrus.core.ProtocolHandler.process(ProtocolHandler.java:622)
at org.glassfish.tyrus.core.TyrusWebSocketEngine$TyrusReadHandler.handle(TyrusWebSocketEngine.java:394)
at org.glassfish.tyrus.servlet.TyrusHttpUpgradeHandler.onDataAvailable(TyrusHttpUpgradeHandler.java:164)
at org.apache.catalina.connector.InputBuffer$ReadHandlerImpl.processDataAvailable(InputBuffer.java:488)
at org.apache.catalina.connector.InputBuffer$ReadHandlerImpl.onDataAvailable(InputBuffer.java:453)
at org.glassfish.grizzly.http.io.InputBuffer.invokeHandler(InputBuffer.java:1101)
at org.glassfish.grizzly.http.io.InputBuffer.invokeHandlerOnProperThread(InputBuffer.java:1092)
at org.glassfish.grizzly.http.io.InputBuffer.append(InputBuffer.java:975)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:271)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:284)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:201)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:133)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:112)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:561)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:565)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:545)
at java.lang.Thread.run(Thread.java:748)]]
一个典型的错误。
ResultSet rs = ps.executeQuery(count);
应该是
ResultSet rs = ps.executeQuery();
作为ps
是延伸Statement
一个PreparedStatement
,和executeQuery(String)
是不可用的声明方法。
应该有人创建一个类似的API,覆盖executeQuery(String)
并使其具有良好的javadoc的@Deprecated
。
+10。接得好。 – spencer7593
问号?
是该声明的情况下无效; MySQL不会将它看作数字文字,字符串文字或有效的引用。
我们也会导致同样的错误运行这样一个简单的查询:
和MySQL返回此错误:
Error Code: 1064
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 '?, NOW()' at line 1
这个问号字符很可能意味着是一个一个绑定变量的占位符。在没有看到实际代码的情况下,我们只是猜测......我们希望将SQL文本作为参数传递给prepareStatement
,然后在执行之前调用适当的方法将变量绑定到占位符。
PreparedSatement ps = con.prepareStatement(sqltext);
ps.setInt(1,groupId);
ps.setInt(2,selected);
ps.setInt(3,removed);
编辑
这个答案被张贴之前 OP的问题添加任何Java代码。正如Joop Eggen的回答指出的那样,在(现在提供的)OP代码中这是一个简单的问题......参数在executeQuery
方法中提供。不要为准备好的声明做到这一点。
由于我正在使用预准备语句,因此只有在打印无准备语句的调试语句中执行代码时,问号才出现。 –
尝试在=和?之间添加一个空格。
String count = "SELECT COUNT(" + SUGGESTEDFOOD_ID + ") AS num_same, "
+ SUGGESTEDFOOD_PLACEID + " FROM " + SUGGESTEDFOOD
+ " WHERE " + SUGGESTEDFOOD_GROUP_ID + "= ? AND "
+ SUGGESTEDFOOD_SELECTED + "= ? AND "
+ SUGGESTEDFOOD_REMOVED + "= ? "
+ "GROUP BY "
+ SUGGESTEDFOOD_PLACEID + " LIMIT 1";
你在哪里设置参数? –
您很可能错误地执行了查询,即您的** Java代码**有缺陷,因此如果您需要我们的帮助,您需要1)显示代码,并且2)显示错误的完整堆栈跟踪。 – Andreas