java.sql.SQLException：没有为参数5指定值，但字符串长度为4，而不是5 [英] java.sql.SQLException: No value specified for parameter 5, but the string length is 4, not 5

问题描述

我计划使用yahoo finance csv api来生成url，以获得股票实时价格和时间。网址是这样的 http://download.finance.yahoo.com/d/quotes.csvs=%40%5YHOO,GOOG,MSFT,BAC&f=nsl1o&e=.csv ，并返回

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.

我使用jdbc将数据存储到Mysql，但总是有一个错误：没有为参数5指定值。代码如下：
public class Quote_Saver {
private void connection（）{
try {
Class.forName（com.mysql.jdbc.Driver）;
} catch（ClassNotFoundException e）{
e.printStackTrace（）;
}
}

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(); } }

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);
}

}

错误是

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)

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

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!

推荐答案

实际上， 。

下面这一行需要8个参数而不是4，因为你把问号放在你应该放置列名的地方。

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

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

如果您按照以下方式修改它（用stocks表中的真实名称替换列名），那么它应该像您期望的那样工作。

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 (?, ?, ?, ?);

这篇关于java.sql.SQLException：没有为参数5指定值，但字符串长度为4，而不是5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！