Is there any way of getting resultset after preparedstatement closed ? ( java )

Question

I made a static method for executing the queries like this below

(this is in ServerProcess class)

public static ResultSet insertRow(Connection conn, String query){
    PreparedStatement pstmt = conn.prepareStatement(query);
    ResultSet rs = pstmt.executeQuery();
    pstmt.close();
    return rs;
}

and trying to get & use the resultset from here

ResultSet rs = ServerProcess.insertRow(conn, query)
    while(rs.next()){
    String nameOfEachOne = rs.getString("MEMBER_NAME");
    System.out.println(nameOfEachOne);  
}

But, as far as I know, once statement(or preparedstatement) closed, resultset would be bound to get closed as a knock-on consequences.

I would like to use it in this way somehow, but I can't figure out how to do it. Is there any possible ways to get information set after the statement closed ?

_Is there any way of getting resultset after preparedstatement closed ? ( java )_ No — Felix, Jun 10 '17 at 04:47
suppose this [post](https://stackoverflow.com/questions/25493837/java-cant-use-resultset-after-connection-close) discuss more about your issue and a solution for it. — Rajith Pemabandu, Jun 10 '17 at 04:50
Yes, I know it sounds like non-sense, but I'm just curious and wonder if there's some good idea. — Guieen, Jun 10 '17 at 04:50

Felix · Answer 1 · 2017-06-10T05:06:05.193

No, you can't receive the ResultSet or read anything from it when the parent PreparedStatement is already closed.

What you can do is to work with a Consumer which reads the ResultSet before you close the PreparedStatement and then returns any Business-Object you need:

The Consumer-Interface:

@FunctionalInterface
public interface SQLConsumer<T extends ResultSet, E>// where E is going to be your Business-Object Type
{
    E accept(T resultSet) throws IOException;
}

The Method now returns what the parser returns, but closes everything properly:

public static <E> E insertRow(Connection conn, String query, SQLConsumer<? super ResultSet, ? extends E> parser)
{
    E result;

    // use the try-with-resources functionality
    try (PreparedStatement pstmt = conn.prepareStatement(query)
    ; ResultSet rs = pstmt.executeQuery())
    {
        // give the ResultSet to the parser to process it before closing the PreparedStatement
        result = parser.accept(rs);
    }

    return result;
}

And parsing the result:

public void callingMethod()
{
    ArrayList<String> result = insertRow(conn, "MY_QUERY", this::parseResultSet);
}

private ArrayList<String> parseResultSet(ResultSet resultSet) throws IOException
{
    ArrayList<String> values = new ArrayList<>();
    while (resultSet.next())
    {
        values.add(resultSet.getString("MEMBER_NAME"));
    }

    return values;
}

careful, many ResultSet operations throw SQLException, which a `Consumer` will not allow — Rogue, Jun 10 '17 at 04:53
Thank you for your kind answer. It's the first time I saw the Consumer Interface. For using&referring your answer, I needed to study it ^^; — Guieen, Jun 10 '17 at 05:02

Rogue · Answer 2 · 2017-06-10T05:00:49.183

1

Ideally you would never expose a ResultSet like that at all, it's a very mutable/stated object. However if you want to provide it, and still close it, you can use it via a lambda function:

@FunctionalInterface interface SQLConsumer<T> {
    public void accept(T t) throws SQLException; //default functional interfaces can't throw
}

public void query(String sql, SQLConsumer<? super ResultSet> action) {
    ResultSet rs = /* various jdbc code */;
    action.accept(rs);
    rs.close(); //and other resources
}

//In usage:
query("SELECT * FROM my_table", rs -> {
    String s = rs.getString(1); //example
});

Of course, you can make various methods like this, and I've actually done abstraction like that myself for personal projects across different RDBMSes.

edited Jun 10 '17 at 05:00

answered Jun 10 '17 at 04:53

Rogue

11,105
5
45
71

Thank you for your kind answer. the term 'lambda' looks difficult and makes me remind physics and mathematics :) I'll try it on today ! – Guieen Jun 10 '17 at 05:07
here a lambda is essentially an "anonymous method" (like anonymous classes). It's similar to the mathematics definition only in that it represents a function (but not physics, since that's wavelength) – Rogue Jun 10 '17 at 05:09

score 0 · Answer 3 · edited Jun 05 '20 at 23:18

You can use CachedRowSet.

RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet rowset = factory.createCachedRowSet();
rowset .setUsername(username);
rowset .setPassword(password);
rowset .setUrl("jdbc:mySubprotocol:mySubname"); // or setDataSource() 
rowset .setCommand("select * from table");
rowset.execute();  // now you have populate data

There are two advantages

Don't need prepareStatement
No problem to closing your connection or prepare statement comparing your method

Is there any way of getting resultset after preparedstatement closed ? ( java )

3 Answers3