CODEWITHSUNDEEP
javasqlprepared-statementresultset
Guieen
Guieen

Reputation: 33

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

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 ?

Upvotes: 3

Views: 1706

Answers (3)

Boris Fain
Boris Fain

Reputation: 11

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

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

Upvotes: 0

Felix
Felix

Reputation: 2386

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

Upvotes: 1

Rogue
Rogue

Reputation: 11483

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.

Upvotes: 1

Related Questions