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

Question

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

I've tried to fix the issue by using many loops, conditions, etc. but the Resultset inside those loops either returns a NullPointer or SQLSyntaxError. I never succeded, so I would appreciate it if you could reply my question.

<小时/>

没有循环的原始 java 类

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。查看文档 here