0

I want the two rows of a count and groupby (sqlite)sql query to be displayed in the text area, but it is only displaying the first row.

When I place the conn.close() statement outside the "while" block, it displays only the second row and leaves out the first row

@FXML
private void viewResult(ActionEvent event)
{
    try
    {
        Connection conn = dbConnection.getConnection();
        Statement resultStmt = conn.createStatement();   
        ResultSet rs = resultStmt.executeQuery("select candidate, count(candidate) from voteResult group by candidate");
        while (rs.next()) {
            String news = rs.getString(1)+" "+rs.getString(2);
            this.result.setText(news);
            conn.close();
        }
    }
    catch (SQLException e)
    {
        System.err.println("Error " + e);
    }

}

the expected result should be like this:

JOSEPH THANKGOD 4
ORJI DANIEL 1
Bill P
  • 3,622
  • 10
  • 20
  • 32
Nsoha
  • 1

1 Answers1

6

Each time you call setText(String value) the previous value will be overwritten. So you have to build your result string first and set it as content of the TextArea afterwards. I don't know how exactly Connection and the other components work together but i guess if you close your Connection in the first while-loop-cycle the ResultSet won't be usable to get the next row anymore. Moreover, calling the close method of Connection the second or third time will not cause anything to happen because it is a no-op if the Connection object is already closed.

@FXML
private void viewResult(ActionEvent event) {

    // Open resources with try-with-resources block
    try (Connection conn = dbConnection.getConnection();
        Statement resultStmt = conn.createStatement();
        ResultSet rs = resultStmt.executeQuery("select candidate, count(candidate) from voteResult group by candidate");){

        StringBuilder builder = new StringBuilder();
        while (rs.next()) {
            // Append each row
            builder.append(rs.getString(1)).append(" ").append(rs.getString(2)).append("\n");
        }

        // Remove last newline character
        if (builder.length() > 0) {
            builder.setLength(builder.length() - 1);
        }

        // Get complete string and set as TextArea content
        this.result.setText(builder.toString());

    // Resources will be closed automatically due to try-with-resources
    }
}

Besides you shouldn't mix UI and database code and should use the try-with-resources block (take a look at this answer).

Lesurglesum
  • 515
  • 4
  • 10