java.sql.SQLException: 没有为参数 5 指定值，但字符串长度是 4，而不是 5

java.sql.SQLException: No value specified for parameter 5, but the string length is 4, not 5

提问by user3263529

i plan to use yahoo finance csv api to generate url to get stock realtime price and time. The url is like this http://download.finance.yahoo.com/d/quotes.csvs=%40%5YHOO,GOOG,MSFT,BAC&f=nsl1o&e=.csv, and it returns a table with 4 columns.

我计划使用雅虎金融 csv api 生成 url 以获取股票实时价格和时间。网址是这样的http://download.finance.yahoo.com/d/quotes.csvs=%40%5YHOO,GOOG,MSFT,BAC&f=nsl1o&e=.csv，它返回一个有 4 列的表。

I use jdbc to store the data to Mysql, but there is always an error:"No value specified for parameter 5". The code is as below: public class Quote_Saver { private void connection(){ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } }

我使用jdbc将数据存入Mysql，但是一直报错：“No value specified for parameter 5”。代码如下： public class Quote_Saver { private void connection(){ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } }

private String retrieveQuote() {
    StringBuffer buf = new StringBuffer();
    try {
        URL page = new URL("http://download.finance.yahoo.com/d/quotes.csv?s=%40%5YHOO,GOOG,MSFT,BAC&f=nsl1o&e=.csv");
        String line;
        URLConnection conn = page.openConnection();
        conn.connect();
        InputStreamReader in = new InputStreamReader(conn.getInputStream());
        BufferedReader data = new BufferedReader(in);
        while ((line = data.readLine()) != null) {
            buf.append(line + "\n");
        }
    } catch (MalformedURLException mue) {
        System.out.println("Bad URL: " + mue.getMessage());
    } catch (IOException ioe) {
        System.out.println("IO Error:" + ioe.getMessage());
    }
    return buf.toString();
}

//use the retrieveQuote class , pass it to data in ConnectionToMysql
private void ConnectionToMysql(String data){
    StringTokenizer tokens = new StringTokenizer(data, ",");
    String[] fields = new String[4];
    for (int i = 0; i < fields.length; i++) {
        fields[i] = stripQuotes(tokens.nextToken());
    }

    connection();
    String host ="jdbc:mysql://localhost/yahoo";
    String username = "root";
    String password = ".....";
    try {
        Connection connection = DriverManager.getConnection(host, username, password);
        String query = "insert into `stocks`(?,?,?,?) values (?,?,?,?);";
        PreparedStatement statement = (PreparedStatement) connection.prepareStatement(query);
        statement.setString(1, fields[0]);
        statement.setString(2, fields[1]);
        statement.setString(3, fields[2]);
        statement.setString(4, fields[3]);
        statement.executeUpdate();
        connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

private String stripQuotes(String input) {
    StringBuffer output = new StringBuffer();
    for (int i = 0; i < input.length(); i++) {
        if (input.charAt(i) != '\"') {
            output.append(input.charAt(i));
        }
    }
    return output.toString();
}

public static void main (String args[])
{
    Quote_Saver qd= new Quote_Saver();
    String data = qd.retrieveQuote();
    qd.ConnectionToMysql(data);
}

}

The error is

错误是

java.sql.SQLException: No value specified for parameter 5
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2594)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2569)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2366)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2350)
at Quote_Saver.ConnectionToMysql(Quote_Saver.java:68)
at Quote_Saver.main(Quote_Saver.java:89)

But clearly, the string fields only has size of 4,so does my table columns, so my question is where is the parameter 5? and how to solve this? I'm new to java,plz help me. Thanks a lot!

但很明显，字符串字段的大小只有 4，我的表列也是如此，所以我的问题是参数 5 在哪里？以及如何解决这个问题？我是java新手，请帮助我。非常感谢！

采纳答案by John Humphreys - w00te

Actually,you just made a minor mistake - probably a copy paste error.

实际上，您只是犯了一个小错误 - 可能是复制粘贴错误。

The following line requires 8 parameters instead of 4 because you put question marks where you should have put column names.

以下行需要 8 个参数而不是 4 个参数，因为您在应该放置列名的位置放置了问号。

insert into `stocks`(?,?,?,?) values (?,?,?,?);";

If you modify it as follows (replacing the column names with your real names from the stocks table) then it should function as you were expecting.

如果您按如下方式修改它（用股票表中的真实姓名替换列名），那么它应该按您的预期运行。

insert into stocks(ColumnNameOne, ColumnNameTwo, ColumnNameThree, ColumnNameFour)
values (?, ?, ?, ?);

回答by Salah

Your sending 8 parameters by using, because each ?represent a parameter that required a value to be set at Run time.

您使用 using 发送 8 个参数，因为每个?参数代表一个需要在运行时设置值的参数。

String query = "insert into `stocks`(?,?,?,?) values (?,?,?,?);";

Your query should look like this:

您的查询应如下所示：

String query = "insert into `stocks`(clmName1, clmName2, clmName3, clmName4) values (?,?,?,?);";