如何在用Java关闭连接之前返回ResultSet

I want to write only 1 execute method and re-use it whenever I need without rewrite the whole thing. Below is my method to return a ResultSet.

我只想写一个执行方法，在我需要的时候再用它，而不用重写整个东西。下面是返回结果集的方法。

public ResultSet executeSelect() {
    Connection con = null;
    PreparedStatement prepared = null;
    ResultSet rs = null;
    try {
        con = C_DB.getConnection();
        prepared = con.prepareStatement(this.getQuery());

        if(!this.midQuery.equals("")) {
            int i = 1;
            for (String val: this.values) {
                prepared.setString(i, val);
                i++;
            }
        }

        rs =  prepared.executeQuery();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (prepared != null) {
                prepared.close();
            }
            if (con != null) {
                con.close();
            }
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }
    return rs;
}

The problem is after executeSelect() executed, Connection, PreparedStatement and ResultSet are closed before returning data so I got this error java.sql.SQLException: Operation not allowed after ResultSet closed

问题是在执行executesselect()之后，连接、PreparedStatement和ResultSet在返回数据之前被关闭，所以我得到了这个错误java.sql。SQLException:关闭ResultSet后不允许进行操作

The reason I want to return the ResultSet is I can re-use the executeData method for any object data. For example:

我要返回ResultSet的原因是我可以对任何对象数据重用executeData方法。例如:

C_Query q = new C_Query(dataSearch,"SELECT * FROM people ", " ORDER BY id DESC");
ResultSet rs = q.executeSelect()

ObservableList<C_Person> peopleData = FXCollections.observableArrayList();
while(rs.next()){
    C_Person person = new C_Person();
    person.setFirstName(rs.getString("firstName"));
    person.setLastName(rs.getString("lastName"));
    peopleData.add(person);
}

AND

和

C_Query q = new C_Query(dataSearch,"SELECT * FROM cars", " ORDER BY id DESC");
ResultSet rs = q.executeSelect()

ObservableList<C_Car> CarData = FXCollections.observableArrayList();
while(rs.next()){
    C_Car car = new C_Car();
    car.type(rs.getString("type"));
    car.brand(rs.getString("brand"));
    CarData.add(car);
}

How can I fix this?

我该怎么解决这个问题呢?

3 个解决方案

#1

There is 'CachedRowSet', exactly for that: https://docs.oracle.com/javase/6/docs/api/javax/sql/rowset/CachedRowSet.html

有“CachedRowSet”，确切地说就是:https://docs.oracle.com/javase/6/docs/api/javax/sql/rowset/CachedRowSet.html

#2

The reason I want to return the ResultSet is I can re-use the executeData method for any object data

我要返回ResultSet的原因是我可以对任何对象数据重用executeData方法

You can't close the ResultSet & Connection objects before reading the results from the database, rather you need to close the ResultSet & Connection objects in the calling methods i.e., whenever you are reading the results are completed.

在从数据库读取结果之前，您不能关闭ResultSet & Connection对象，而是需要在调用方法中关闭ResultSet & Connection对象。当你读到结果的时候。

I think your objective is to avoid the boilerplate code and populate the objects (like your C_Person or C_Car, etc..) from the database, then I strongly suggest, you need to go for the ORM (which maps a Java object to a relational table) frameworks like Hibernate.

我认为您的目标是避免使用样板代码并从数据库中填充对象(如C_Person或C_Car等)，然后我强烈建议您使用ORM(将Java对象映射到关系表)框架，如Hibernate。

#3

You have finally block here. It is executed whenever there was an exception or not, so it closes the connection in any case before you return the result set from the method.

你终于来了。无论是否存在异常，它都将执行，因此在从方法返回结果集之前，它将在任何情况下关闭连接。

#1