Return values from database queries into a LinkedHashMap

Question

I keep getting one value from this LinkedHashMap it's either the first [ if (resultset.next()) ] or the last [ while(resultset.next()) ], only one result is coming back but I want the full map. How do I return all rows in the table that fit my criteria? Any help would be appreciated.

/** 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;

/** A helper method was used to minimize code duplication. It works for sure */

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

score 0 · Accepted Answer · answered Apr 28 '17 at 20:34

here are a couple suggestions in sample code below...

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

Return values from database queries into a LinkedHashMap

1 Answers1