mysql - 将数据库查询的值返回到 LinkedHashMap

Question

我不断从这个 LinkedHashMap 中获取一个值，它要么是第一个 [ if (resultset.next()) ] 要么是最后一个 [ while(resultset.next()) ]，只有一个结果返回，但我想要完整的结果 map 。如何返回表中符合我的条件的所有行？任何帮助将不胜感激。

/** A method to list all previous statuses for a certain user given the userID */

public StringBuilder showStatusHistory(int userID) {

    try {

        Date date;
        String status;
        preparedStatement = createStatement("select statusDate,statusText from statusTable where userID = ?");
        preparedStatement.setInt(1,userID);
        resultSet = preparedStatement.executeQuery();

        StringBuilder allStatus = new StringBuilder("");
        statusesList = new LinkedHashMap<>();

        while (resultSet.next()){
            date = resultSet.getDate(1);
            status = resultSet.getString(2);
            statusesList.put(date,status);
        }

        Set<Date> statusTime = statusesList.keySet();
        for(Date k:statusTime){
            allStatus.append(k+" "+statusesList.get(k));
        }
        return allStatus;
    }
    catch (SQLException sqlE){
        sqlE.printStackTrace();
    }
    return null;

/** 使用辅助方法来最大限度地减少代码重复。它肯定有效*/

private PreparedStatement createStatement(String query) {
    try {
        connection = DB_ConnectionConfiguration.getConnection();
        this.query = query;
        preparedStatement = connection.prepareStatement(query);
        return preparedStatement;
    } 
    catch (SQLException sqlE) {
        sqlE.printStackTrace();
        return null;
    }
}

Answer 1

下面的示例代码中有一些建议...

public StringBuilder showStatusHistory(int userID) {
    // define at top and return "" if no results
    StringBuilder allStatus = new StringBuilder("");

    try {

        Date date;
        String status;
        // define local in the method
        PreparedStatement preparedStatement = createStatement("select statusDate,statusText from statusTable where userID = ?");
        preparedStatement.setInt(1, userID);
        // define local in the method
        ResultSet resultSet = preparedStatement.executeQuery();

        // append to the all Status here and skip the other loop
        // if you need a distinct list (like you had fro the Set) you could use a DISTINCT on the SQL statement.
        while (resultSet.next()) {
            date = resultSet.getDate(1);
            status = resultSet.getString(2);
            // append to the allStatus here 
            allStatus.append(date).append(" ").append(status);  // add a newline to the end? append(System.lineSeparator()) 
        }
    } catch (SQLException sqlE) {
        sqlE.printStackTrace();
        // do you want to throw this if something bad happened? 
    } finally {
       // close up your jdbc resources
    }

    // will contain "" if no records or concatenated statuses from the resultset
    return allStatus;
}