Hi there,
I have question about using JDBC DataSource in JavaEE application
using Glassfish.
This is how session bean can use DataSource:
@Stateless
public class LoanCacheServiceBean implements LoanCacheServiceLocal {
@Resource(name = "jdbc/sop")
private DataSource dataSource;
public void someMethod() {
try {
dataSource.getConnection().createStatement().execute(someSQL);
} catch (SQLException ex) {
//log that exception
}
}
}
In the example above, there is java.sql.Connection object established
from DataSource, then that connection is used to create and execute
some query.
The question is: is the code above correct? I mean, do I need to close
that connection manually, or maybe, when transaction commits,
Glassfish will take that connection back into connection-pool?
I am asking, because we had problems with our application recently. We
never had that issues when we were testing it in our office, but after
deploying that application on our customer's server, we had strange
exceptions after few days. First issue was about connection pool -
server was complaining it cannot use connection, because there was no
free connection left in pool (I was surprised, because I was thinking
that 32 connections was much more that our application will ever
need). I changed max-pool-size from 32 to 64 and everything was OK.
Today we had something even more strange: I found exceptions telling
that the maximum amount of cursors in database (300 by default) was
reached and we had to restart database and Glassfish :/
Our application is using JPA almost everywhere, but few operations we
are doing using JDBC. I found, we never close connection in JDBC code
like in the example above.
This is why I wrote that letter. I hope the problem with our
application is because we are not closing JDBC connection, but before
we correct that SessionBean I would like to know is this the cause of
our problem. If Glassfish returns that connection to pool after JTA
commits, then I have to seek for problem somewhere else :/