PreparedStatement错误 [英] Error with PreparedStatement

问题描述

所以我有一个方法可以返回我得到的物品名称.但是对于第一个查询，它返回一个错误.我认为哪个不应该存在，因为它的语法正确.这是代码:

So I have a method which returns the items names that I get. But with the first query it returns an error. Which to my opinion shouldn't be there because its the right syntax. Here is the code:

void showInventory(String userId, MessageReceivedEvent event) throws HTTP429Exception, DiscordException, 
MissingPermissionsException{
    sendMessage("test0",event);
    String sql = "SELECT itemID FROM inventory WHERE playerID=?";
    String sql2 = "SELECT Name FROM items WHERE ID=?";
    java.sql.PreparedStatement state;
    java.sql.PreparedStatement state2;

    try {
        state  = Main.conn.prepareStatement(sql);
        state2 = Main.conn.prepareStatement(sql2);
        state.setString(1, userId);
        ResultSet results = state.executeQuery(sql);
        ResultSet resultname = null;
        String invent = "";
        sendMessage("test",event);

        while(results.next()){
            sendMessage("test1",event);
            int result = results.getInt("itemID");
            state2.setInt(1, result);
            resultname = state2.executeQuery(sql2);

            while(resultname.next()){
                String name = resultname.getString("Name");
                invent += (name + "\n");
            }
        }
        sendMessage(invent, event);
    } catch (SQLException e) {
        e.printStackTrace();
    }

}

这是错误:

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 '?' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2505)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1370)
at martacus.mart.bot.rpg.InventoryHandler.showInventory(InventoryHandler.java:48)
at martacus.mart.bot.rpg.InventoryHandler.OnMesageEvent(InventoryHandler.java:29)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sx.blah.discord.handle.EventDispatcher.dispatch(EventDispatcher.java:104)
at sx.blah.discord.api.internal.DiscordWS.messageCreate(DiscordWS.java:323)
at sx.blah.discord.api.internal.DiscordWS.onMessage(DiscordWS.java:144)
at org.java_websocket.client.WebSocketClient.onWebsocketMessage(WebSocketClient.java:312)
at org.java_websocket.WebSocketImpl.decodeFrames(WebSocketImpl.java:368)
at org.java_websocket.WebSocketImpl.decode(WebSocketImpl.java:157)
at org.java_websocket.client.WebSocketClient.interruptableRun(WebSocketClient.java:230)
at org.java_websocket.client.WebSocketClient.run(WebSocketClient.java:188)
at java.lang.Thread.run(Unknown Source)

该错误正在谈论名为sql的第一个字符串.称为:

The error is talking about the 1st String called sql. Which is called in:

ResultSet results = state.executeQuery(sql);

推荐答案

通过调用executeQuery(sql)，您实际上在Statement上调用了一个方法，而不是在PreparedStatement上调用了方法.因此，您丢弃已经分配的参数值，并执行查询，在该查询中占位符?仍未解析-这样您就会得到该错误.

By invoking executeQuery(sql) you actually invoke a method on Statement - not on PreparedStatement. So you throw away the parameter value already assigned and execute a query where the placeholder ? remains unparsed - so you get that error.

更改

 ResultSet results = state.executeQuery(sql);

到

 ResultSet results = state.executeQuery();

你应该没事.

(和原因

resultname = state2.executeQuery(sql2);

需要成为

resultname = state2.executeQuery();

也是)

这篇关于PreparedStatement错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！