What is best way to fetch jdbc connection in sub method

Question

I have query regarding fetching jdbc connection from pool in sub method.Following are two method i came across suggest me which one best to avoid connection leakage and tell if any other solution.

Method 1: getConnection is method which return Connection.

void testMain(){
    Connection conn = getConnection();
    subMethod(conn)
    conn.close();
}
void subMethod(connection conn){
    // use jdbc connection
    return;
}

Method2:

void testMain(){
    Connection conn = getConnection();
    subMethod()
    conn.close();
}
void subMethod(){
    Connection conn = getConnection();
    conn.close();
    return;
}

Answer 1

The place where you need a Connection should get the connection.

The way you ensure that no resources are "leaked" is by using java 7's try-with-resource syntax:

public String fetchSomeData() {
    try (Connection conn = getConnection()) { // This line, with this syntax, will ensure that it is automatically closed in an invisible "finally" block
        // Do what you need to do with the data, return it or something else
    } catch (SQLException e) {
        // No need to do clean up here, log the exception or do whatever you want.
    }
}

You can use try-with-resource syntax on any objects that implement AutoCloseable interface. That includes Connection, Statement, and Resultset among others.

If you need to do a transaction you might want to initialize the Connection in a method, and then pass that Connection to different other methods that adds to the transaction, and then commit it. If that's the case, you can do:

public String fetchSomeDataInTransactionStyle() {
    try (Connection conn = getConnection()) { // This line, with this syntax, will ensure that it is automatically closed in an invisible "finally" block
        conn.setAutocommit(false);
        addSomethingToTransaction(conn);
        addSomethingMore(conn);
        conn.commit();
    } catch (SQLException e) {
        // No need to do clean up here, log the exception or do whatever you want.
    }
}