Getting SQLException errors in connection pool

Question

I am getting a Oracle JDBC connection from a connection pool using a data source object. And using the connection to insert some logs into DB from java (via java static method). Since there will be around 10 to 20 inserts per user, what I am doing is, I am commiting the connection at request level in Filter and closing the connection at session level (via Session Listener's sessionDestroyed() method) I did not get any error in testing but in production environment, I am getting few error as follows,

java.sql.SQLException: execute, Exception = null (one scenario)
java.sql.SQLException: close, Exception = null (for another scenario)

How to avoid these errors? Can I instead commit and close the connection in java static method, instead of commiting at request level and closing at session level?

But what amuses me is that, those errors are occuring inspite of having the below logic in my java static method,

if (con.isClosed() || con == null) {
 DBConnectionHelper connHelper = DBConnectionHelper.createInstance();
    con=connHelper.getConnection("ds");
    con.setAutoCommit(false);
}

So I am checking for the above, and so there is no way connection can be closed when I am trying for execute, am I right? But not sure and confused why its occuring.

Why would you close the connection in a connection pool? Also, in your if, I would check to see if con was null first before the con.isClosed(). — Mike
– Mike, Commented May 15, 2012 at 13:27
@Mike: Some connection pools intercept the close() call and instead return the connection to the pool. — skaffman
– skaffman, Commented May 15, 2012 at 13:29
@skaffman - Well, before a first connection, what does con point to? — Ravinder Reddy
– Ravinder Reddy, Commented May 15, 2012 at 13:31
@Mike If you use a connection pool, then you do need to close the connection: that is the signal for the connection pool that it is available for re-use. You (normally) don't get the actual connection, but a proxy which is managed by the connection pool (and as skaffman says: intercepts the close). — Mark Rotteveel
– Mark Rotteveel, Commented May 15, 2012 at 17:49

Thomas Rokamp · Accepted Answer · 2012-05-15 13:28:13Z

1

I'm guessing your con object is null?

Consider the following block:

if (con.isClosed() || con == null) {
 DBConnectionHelper connHelper = DBConnectionHelper.createInstance();
    con=connHelper.getConnection("ds");
    con.setAutoCommit(false);
}

First you ask if the connection is closed. Then you see if its null. If con really is null, you will get a NullpointerException.

Switch the checks and see if it helps anything :-)

if (con == null || con.isClosed()) {
 DBConnectionHelper connHelper = DBConnectionHelper.createInstance();
    con=connHelper.getConnection("ds");
    con.setAutoCommit(false);
}

answered May 15, 2012 at 13:28

Thomas Rokamp

1411 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gopi Over a year ago

Fine let me change that and see. And should I manually close the connection pool object as and when in static java method or close it once at last( as I do in sessionDestroyed() method in session Listener) ?

Ravinder Reddy · Accepted Answer · 2012-05-15 13:28:43Z

1

Change

if (con.isClosed() || con == null) {

To

if ( con == null || con.isClosed() ) {

And see if there is a difference.

How did you check con=connHelper.getConnection("ds"); as a success?
I fear con is null before executing con.setAutoCommit(false);
If that is the case, you need to check if connection string for production db is correct.

answered May 15, 2012 at 13:28

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

1 Comment

Gopi Over a year ago

Fine let me change that and see. And should I manually close the connection pool object as and when in static java method or close it once at last( as I do in sessionDestroyed() method in session Listener) ?

Collectives™ on Stack Overflow

Getting SQLException errors in connection pool

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related