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

Question

我正在为我的 SELECT 查询使用准备好的语句。之后，我尝试从 ResultSet 获取信息并将其放入 ArrayList 中。我正在结果集循环中执行此操作，但收到错误 java.sql.SQLException: ResultSet 关闭后不允许操作。我尝试使用 rs.close() 关闭它，但它给了我同样的错误。这是我的方法。请帮忙!

    @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

关闭连接也会关闭准备好的语句。

您应该仅在完成结果集的处理后关闭连接。

您的代码应如下所示:

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

编辑:在 Java7 中你可以这样写:

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

  ... bind variables to preparedStatement ...

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

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