SQLException: Operation not allowed after ResultSet closed. Java.Spring MVC

Question

I'm using Prepared Statement for my SELECT query. After that i'm trying to get my informating from ResultSet and to put it into ArrayList. I'm doing it into Result Set loop but i'm getting an error java.sql.SQLException: Operation not allowed after ResultSet closed. I tried to close it with rs.close() but it gave me same error. Here is my method. Help pls!

    @SuppressWarnings("unchecked")
@Override
public List<Users> listUsersSort(Integer weight, String gender, String place, Integer ageTo, String currentUser) {
    System.out.println(weight + gender + place + ageTo + currentUser);
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    boolean weightFlag = false;
    boolean genderFlag = false;
    boolean placeFlag = false;
    boolean ageToFlag = false;
    int iterator = 0;
    List<Users> usersList = new ArrayList<Users>();
    String selectSQL = "select*from users where users.enabled = ?";
    if (weight < 40 == false) {
        String weightParam = " AND users.weight <= ?";
        selectSQL = selectSQL.concat(weightParam);
        weightFlag = true;
        System.out.println(selectSQL);
    }
    if (gender.isEmpty() == false) {
        String genderParam = " AND  users.gender LIKE ?";
        selectSQL = selectSQL.concat(genderParam);
        genderFlag = true;
    }

    if (place.isEmpty() == false) {
        String placeParam = " AND users.place LIKE ?";
        selectSQL = selectSQL.concat(placeParam);
        placeFlag = true;
        // query = query.concat(placeParam);
    }
    if (ageTo < 19 == false) {
        String age = " AND users.age <= ?";
        selectSQL = selectSQL.concat(age);
        ageToFlag = true;
    }
    String withoutUser = " AND users.username NOT LIKE ?";
    selectSQL = selectSQL.concat(withoutUser);
    System.out.println("FINAL QUERY IS: " + selectSQL);
    Connection con = getConnection();
    try {

        preparedStatement = con.prepareStatement(selectSQL);
        preparedStatement.setBoolean(++iterator, true);
        if (weightFlag)
            preparedStatement.setInt(++iterator, weight);

        if (genderFlag)
            preparedStatement.setString(++iterator, gender);

        if (placeFlag)
            preparedStatement.setString(++iterator, place);

        if (ageToFlag)
            preparedStatement.setInt(++iterator, ageTo);

        preparedStatement.setString(++iterator, currentUser);

    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
    ResultSet rs = null;
    try {
        rs = preparedStatement.executeQuery();
        con.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    try {
        Users userrs = new Users();
        while (rs.next()) {             
            userrs.setUsername(rs.getString("username"));
            userrs.setName(rs.getString("name"));
            userrs.setSurname(rs.getString("surname"));
            userrs.setGender(rs.getString("gender"));
            userrs.setWeight(rs.getInt("weight"));
            userrs.setHeight(rs.getInt("height"));
            userrs.setSport(rs.getString("sport"));
            userrs.setSport(rs.getString("place"));
            usersList.add(userrs);

        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }



    return usersList;
}

Answer 1

Closing the connection will also close the prepared statement.

You should only close the connection after finishing your business with the resultset.

Your code should look like:

Connection dbConnection=null;
PreparedStatement preparedStatement=null;
ResultSet rs = null;
try {
  dbConnection = getConnection();
  preparedStatement = dbConnection.preparedStatement(...);

  ... bind variables to preparedStatement ...

  rs = preparedStatement.executeQuery();

  ... work with result set ...

} finally {
  if (rs!=null)
    rs.close();
  if (preparedStatement!=null)
    preparedStatement.close();
  if (dbConnection!=null)
    dbConnection.close();
}

Edit: in Java7 you would write:

try (Connection dbConnection = getConnection();
     PreparedStatement preparedStatement = dbConnection.preparedStatement(...)) {

  ... bind variables to preparedStatement ...

  try (ResultSet rs = preparedStatement.executeQuery()) {

    ... work with result set ...
  }
}