JDBC with try with resources

Question

I am trying to create a centralized class that connects and returns the ResultSet of a SQL query so that I don't always have to create a new connection every time I am trying to get a query.

I am using the try-with-resources, however, I am getting a compile-time error whenever I use the try-with-resources and I don't know why?

public class JDBC {

    // logger declaration is omitted

    private static final String dbURL = "jdbc:oracle:";
    private static final String userName = "blah";
    private static final String password = "12345";

    public ResultSet retrieveSQLQuery(String sqlQuery) {            
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;

        try (conn = DriverManager.getConnection(dbUrl, user, password);
             statement = conn.createStatement();
             rs = statement.executeQuery(sqlQuery)) {               

        } catch (SQLException e) {
            logger.info(e.getMessage());
        }                    
        return rs;        
    }
}

@AlexandruMarina I am getting quite a few errors. Once on the getConnection that says `unknown class collection` and an `unhandle sql exception` on `statement.executeQuery(sqlQuery)` — Robben, Jul 19 '16 at 18:12
You're returning a resultSet after it's been closed. this won't work. — Nathan Hughes, Jul 19 '16 at 18:15
@NathanHughes Hmm, so how can I be able to create a jdbc class that can dynamically return values from the sql query? For example, if I wanted to query a clob instead of a string? Won't I then have to return a ResultSet? — Robben, Jul 19 '16 at 18:18
Probably your `JDBC` class should return an object or collection with then data from the ResultSet and the close then connection. — PeterMmm, Jul 19 '16 at 18:20
You don't have to create every time a new connection if you use a JDBC connection pool like `c3p0`. — PeterMmm, Jul 19 '16 at 18:23
Hmm I am getting a bit confused, sorry. But do i use a connection pool or spring-jdbc? — Robben, Jul 19 '16 at 18:29
I think what you need is actually a CachedRowSet. But beware of the size of the result set :) — Alain Pannetier, Jul 19 '16 at 19:03

Vladimir Vagaytsev · Accepted Answer · 2019-08-28T13:39:34.173

4

Java 7

When you use try-with-resources, variables pointing to Closeable resources must be declared inside try-with-resources block.

Moreover, returning rs is a bad idea, it would be closed after method is done. So you might get an SQLException outside your method (something like "ResultSet is closed"). You should parse rs inside try-with-resources block and return SQL agnostic object from your method:

public ResultSet retrieveSQLQuery(String sqlQuery) {            
    try (Connection conn = DriverManager.getConnection(dbUrl, user, password);
         Statement statement = conn.createStatement();
         ResultSet rs = statement.executeQuery(sqlQuery)) {
        MyResult result = ...; // parse rs here
        return myResult;               
    } catch (SQLException e) {
        logger.info(e.getMessage());
        // return something (empty MyResult or null) from here or rethrow the exception
        // I'd recommend to get rid of this catch block and declare the SQLException on method signature
    }                    
}

You're getting compile-time error on incorrect try-with-resources syntax, that's it.

Update

Java 9 Java 9 provides more flexible syntax for try-with-resources. You can declare Closeable resource outside the try (...) block:

public ResultSet retrieveSQLQuery(String sqlQuery) {
    Connection conn = DriverManager.getConnection(dbUrl, user, password);
    try (conn; ResultSet rs = conn.createStatement().executeQuery(sqlQuery)) {
        MyResult result = ...; // parse rs here
        return myResult;               
    } catch (SQLException e) {
        // handle error
    }                    
}

edited Aug 28 '19 at 13:39

answered Jul 19 '16 at 19:00

Vladimir Vagaytsev

2,871
9
33
36

I see. Hmm but then I can't return rs because it is not in scope. However, I was just told that I am not suppose to return resultset – Robben Jul 19 '16 at 19:03
Edited, you should return from `try`-block – Vladimir Vagaytsev Jul 19 '16 at 19:04
Hmm but I still get a compile error when I return from try block – Robben Jul 19 '16 at 19:08
Oh, it's because of catch block, see update. It's better to declare your method as `throws SQLException` and catch it outside. – Vladimir Vagaytsev Jul 19 '16 at 19:09
1

Added more edits, returning `rs` is a bad idea, see updated answer. – Vladimir Vagaytsev Jul 19 '16 at 19:15
Thanks! What alternatives do I have instead of returning resultset? Since it is bad to return it then how can I use the resultset api when I get my query if I can't return? – Robben Jul 19 '16 at 19:18
Well, it depends on your table data. Check [`ResultSet` javadocs](https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html). You can iterate it and map each `ResultSet` entry to a custom object, then return a collection of the custom objects. – Vladimir Vagaytsev Jul 19 '16 at 19:21
K, thanks! Do I need to do any sort of connection pooling? Or will this method get it done and be good practice? How would you do it? – Robben Jul 19 '16 at 19:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/117746/discussion-between-vladimir-vagaytsev-and-robben). – Vladimir Vagaytsev Jul 19 '16 at 19:24

score -2 · Answer 2 · edited Jul 20 '16 at 08:57

You should use it like this:

   public ResultSet retrieveSQLQuery(String sqlQuery) {            
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;

        try {      
            conn = DriverManager.getConnection(dbUrl, user, password);
            statement = conn.createStatement();
            rs = statement.executeQuery(sqlQuery);         
        } catch (SQLException e) {
            logger.info(e.getMessage());
        }                     
        return rs; 
    }

It didn't work because you put code in brackets.You should just put it inside these brackets -> {}. And that's also why the error showed because there is no class where there's a method that goes like:

    try(bla bla bla) {}

The OP explicitly asked for try-with-resources syntax. You should go and read an up to date language manual. — mtraut, Jul 19 '16 at 19:36

JDBC with try with resources

2 Answers2

Linked