使用MySQL数据库的SQL错误

Question

我正在尝试从MySQL数据库读取数据。我收到以下错误消息：

2011   1   21333333
2011   2   13500000
2011   3   17285714
2011   4   15500000
2011   5   15800000
2011   6   15900000
2011   7   15875000
2011   8   15600000
2011   9   17666666
2011   10   20000000
2011   11   15958333
2011   12   21583333
2012   1   21519230
2012   2   25450000
2012   3   21400000
2012   4   34166666
2012   5   27928571
2012   6   29250000
2012   7   17550000
2012   8   19111111
2012   9   18200000
2012   10   15181818
2012   11   14455555
2012   12   16900000
2013   1   13500000
2013   2   13600000
2013   3   12812500
java.sql.SQLException: After end of result set//and this one too
0   2.1333333E7    21333333
1   1.35E7    13500000
2   1.7285714E7    17285714
3   1.55E7    15500000
4   1.58E7    15800000
5   1.59E7    15900000
6   1.5875E7    15875000
7   1.56E7    15600000
8   1.7666666E7    17666666
9   2.0E7    20000000
10   1.5958333E7    15958333
11   2.1583333E7    21583333
12   2.151923E7    21519230
13   2.545E7    25450000
14   2.14E7    21400000
15   3.4166666E7    34166666
16   2.7928571E7    27928571
17   2.925E7    29250000
18   1.755E7    17550000
19   1.9111111E7    19111111
20   1.82E7    18200000
21   1.5181818E7    15181818
22   1.4455555E7    14455555
23   1.69E7    16900000
24   1.35E7    13500000
25   1.36E7    13600000
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
    at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
    at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2672)
    at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2813)
    at system.HOME.avgr.makeAvg(avgr.java:104)
    at system.HOME.avgr.<init>(avgr.java:64)
    at system.HOME.avgr.main(avgr.java:164)

这是我用注释表示的第104行的代码。

public long[] makeAvg() {
    double sum = 0.0;
    Connection conn;
    ResultSet rs1;
    PreparedStatement pstmt;
    try {
        String sql = "SELECT count( 'month' ) AS yt, `Month` , `Year` , avg( `asking_price` ) AS av FROM `tbl_p`WHERE `sale` LIKE 'S' AND b <7 and year between 2011 and 2013  GROUP BY `Year` , `Month` ORDER BY `tbl_p`.`year` ASC";
        conn = (Connection) DBConnection.getDBConnection();
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        rs1 = pstmt.executeQuery();
        System.out.println(count_of_set + "nmklnmkl");
        for (; count <= count_of_set; count++) {
            if (count == 0) {
                rs1.first();
                avgNumerator[count] = rs1.getInt("av");
                year[count] = rs1.getInt("year");
                month[count] = rs1.getInt("Month");
                System.out.println(year[count] + "   " + month[count] + "   " + avgNumerator[count]);
            } else {
                rs1.next();
                avgNumerator[count] = rs1.getInt("av");
                year[count] = rs1.getInt("year");
                month[count] = rs1.getInt("Month");
                System.out.println(year[count] + "   " + month[count] + "   " + avgNumerator[count]);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return avgNumerator;
}

Answer 1

而不是以这种方式使用for循环来检索use while循环。

while(rs1.next())
{
//your code
}

按照您的循环，当最后一个是行完成时，它仍然尝试检索数据。

Answer 2

这句话闻起来很俗气：

for (; count <= count_of_set;count++){

如果count从0开始(正如if(count==0)行所建议的那样)，这将尝试迭代(count+1)次数.

这将解决眼前的问题：

for (; count < count_of_set;count++){

但是，问题的真正解决方案是正确地使用JDBC API，如answer of @javaBeginner中所示。