对JSONArray中的每一行使用相同的ResultSet

Question

我有一个java应用程序，它将数据保存在数据库(MySQL)中，我使用JSON以适当的形式传输数据。我可以在JSONArray中获取和插入数据，但是每当ResultSet游标到达第一行的最后一列时，执行就结束了。并且我在表中有两行，SQL查询执行成功。

我试图通过使用许多循环，条件等来解决这个问题，但是这些循环中的 Resultset 返回 NullPointer 或 SQLSyntaxError 。我从来没有成功过，所以如果你能回答我的问题，我会很感激的。

---

The original java class without the loops

public class JsonSuccessReport {
public JsonSuccessReport() {
}
private Connection connection;
private Statement statement;
public void GenerateSuccessReportAsJson(String idNumber) throws SQLException
{
    String query = "SELECT USERS.idnumber, SUCCESS_REPORT.subject_name, "
            + "SUCCESS_REPORT.hours, SUCCESS_REPORT.midterm, "
            + "SUCCESS_REPORT.`final`, SUCCESS_REPORT.average, "
            + "SUCCESS_REPORT.attandance, SUCCESS_REPORT.semester, "
            + "SUCCESS_REPORT.academic_year "
            + "FROM USERS "
            + "INNER JOIN SUCCESS_REPORT "
            + "ON USERS.iduser=SUCCESS_REPORT.`USERS_iduser` "
            + "WHERE USERS.idnumber=" + idNumber;
    ResultSet rs = null;
    try {
        connection = ConnectionFactory.getConnection();
        statement = connection.createStatement();
        rs = statement.executeQuery(query);
        JsonObject jsonResponse = new JsonObject();
        JsonArray data = new JsonArray();

        if (rs.next()) {
            JsonArray row = new JsonArray();
            row.add(new JsonPrimitive(rs.getString("subject_name")));
            row.add(new JsonPrimitive(rs.getString("hours")));
            row.add(new JsonPrimitive(rs.getString("midterm")));
            row.add(new JsonPrimitive(rs.getString("final")));
            row.add(new JsonPrimitive(rs.getString("average")));
            row.add(new JsonPrimitive(rs.getString("attandance")));
            row.add(new JsonPrimitive(rs.getString("semester")));
            row.add(new JsonPrimitive(rs.getString("academic_year")));
            data.add(row);
        }
        jsonResponse.add("jsonSuccessReport", data);
        System.out.println(data);
    } finally {
        ConnectionUtility.close(rs);
        ConnectionUtility.close(statement);
        ConnectionUtility.close(connection);
    }}}

---

GenerateSuccessReportAsJson("09010102234");

[["Information Technologies","3","0","0","0","0","spring","2013-2014"]]

---

The original SQL syntax and its result

SELECT USERS.idnumber, SUCCESS_REPORT.subject_name, 
       SUCCESS_REPORT.hours, SUCCESS_REPORT.midterm, 
       SUCCESS_REPORT.`final`, SUCCESS_REPORT.average, 
       SUCCESS_REPORT.attandance, SUCCESS_REPORT.semester, 
       SUCCESS_REPORT.academic_year
FROM USERS INNER JOIN SUCCESS_REPORT ON USERS.iduser=SUCCESS_REPORT.`USERS_iduser` WHERE USERS.idnumber=09010102234;

执行查询

+-------------+--------------------------+-------+---------+-------+---------+------------+----------+---------------+
| idnumber    | subject_name             | hours | midterm | final | average | attandance | semester | academic_year |
+-------------+--------------------------+-------+---------+-------+---------+------------+----------+---------------+
| 09010102234 | Information Technologies |     3 |       0 | 0     |       0 |          0 | spring   | 2013-2014     |
| 09010102234 | Conflict Management      |     3 |       0 | 0     |       0 |          0 | spring   | 2013-2014     |
+-------------+--------------------------+-------+---------+-------+---------+------------+----------+---------------+
2 rows in set (0.00 sec)

---

What I want to see in result

[["Information Technologies","3","0","0","0","0","spring","2013-2014"],["","3","0","0","0","0","spring","2013-2014"]]

Answer 1

尝试更改

if (rs.next())

while (rs.next())

next()移动结果集游标，如果新行有效则返回true。查看这里的文档