JDBC Pool - How to properly use

Question

I use JDBC pool for my Tomcat web applications.

As I read in docs, I need get connection, execute query and close. After close, connection returns to pool.

I think Way 1 better when query DB depend on external event and for the task which running every 5sec Way 2 more correct.

Could someone explain which way use for task which repeat every 5 sec?

PS: I skip extra checks in code to make code looks readable.

Way #1 Gets connection from pool and close every 5 sec

Connection c = null;
Statement s = null;
ResultSet rs = null;
DataSource ds = ... Get DataSource  ...

while(running) {
    try {
        c = ds.getConnection();
        s = c.createStatement();
        rs = s.executeQuery('SELECT data FROM my_table');
        ... do something with result ...
    } catch (SQLException sec) {
        ... print exception ...
    } finally {
        try {
            rs.close();
            s.close();
            c.close();
        } catch (SQLException sec) { ... print exception ... }

        ... Thread sleep 5 seconds and repeat ...
    }
}

Way #2 Get connection before loop and close after, reconnect inside loop

Connection c = null;
Statement s = null;
ResultSet rs = null;
DataSource ds = ... Get DataSource  ...

c = ds.getConnection();

while(running) {
    try {
        s = c.createStatement();
        rs = s.executeQuery('SELECT data FROM my_table');
        ... do something with result ...
    } catch (SQLException sec) {
        ... print exception ...
        ... if connection lost, try reconnect and execute query again ...
    } finally {
        try {
            rs.close();
            s.close();
        } catch (SQLException sec) {
            ... print exception ...
        }
        ... Thread sleep 5 seconds and repeat ...
    }
}

c.close();

Pool config

minus · Accepted Answer

I think the most common pattern is this:

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet res = null;
    try {
        conn = ds.getConnection();
        stmt = conn.prepareStatement(sqlStatement);
        //
        // ....
        res = stmt.executeQuery();

        // use the resultset

        conn.commit();
    } catch (SQLException e) {
        // Manage the exception
        try {
            conn.rollback();
        } catch (SQLException e1) {
            // SWALLOW
        }

    } finally {
        close(res);
        close(stmt);
        close(conn);
    }

I use these helper functions to safely close without too much boilerplate, from java 7 on you can autoclose so these helper are no use anymore.

public static void close(Connection conn) {
    try {
        if (conn != null)
            conn.close();
    } catch (SQLException e) {
        // SWALLOW
    }
}

public static void close(Statement stmt) {
    try {
        if (stmt != null)

            stmt.close();
    } catch (SQLException e) {
        // SWALLOW
    }
}

public static void close(ResultSet res) {
    try {
        if (res != null)
            res.close();
    } catch (SQLException e) {
        // SWALLOW
    }
}

You should really be sure to close the connection in the finally statement, bad things happens if you do not close the connection, and if (for example) the rs is null in your example (not so difficult) you won't close the connection.

Getting and releasing the connection from the pool is not a performance problem, it happens in microseconds , thousands of times faster then any possible query.

The reason why you do not release the connection eagerly is transactions, you want to keep the same connection for the whole transaction (no way around this).

When you commit (or rollback) then you don't need that peculiar connection anymore, so just release it.

Another hint, close the connection in finally, even if you catch SQL Exceptions, because there are always Runtime Exceptions and even Errors (which you will not catch), but finally will be executed even in the face of OutOfMemoryError or ClassDefNotFoundError or any other, and the connection will be returned to the pool.

Last but not least is the pool that will try to reconnect in case of disconnection, in fact the pool will just ditch invalid connection and create a fresh batch when needed.

You should choose a good policy of connection validation, a bad choice will lead to much extra time in getting the connection thus hitting hard on performance, or to exceptions caused by invalid connection acquired from the pool.

Optimizing the pool is like many other performance tuning tasks: HARD .

For example: testOnBorrow="true" will hit the DB before acquiring the connection, it is safe, but it will cost, tens or hundreds of time slower that not checking on borrow.

testWhileIdle="true" instead is less safe (you could get an invalid connection) but is much faster an has the advantage of keeping alive the connections.

You have to choose considering how you use connection, how you deal with errors, where is the DB (on the same machine, on a lan, on a wan) and many other factors.

JDBC Pool - How to properly use

Answers (2)

Related Questions