we are using stateless session beans inside Glassfish. And I have a following question. Our methods support transactions by default, that is, the transaction is started at business method start point and is committed (or rollbacked) at the exit point. So let's assume I have code like this:
public void businessMethod() {
Connection conn;
try {
conn = getConnectionFromPool();
// Do some queries to the database
} finally {
if (conn != null) conn.close(); // Line A
// Line B
}
}
So what happens at line A is that I don't literally close the connection, it's simply returned to the connection pool, but the transaction of the method is not committed yet. It is done only after line B is finished. But since the connection is returned to the pool, that means that someone else can start a transaction using the same connection, before this method commits (or rollbacks).
So, what happens next? Is connection inside a pool unavailable to others until the transaction is completed by the method? Or will the second transaction just block until the first transaction is over? I am using MySQL as database.
I definitely have to close the connection like this, otherwise it will just not return to the pool. But how do I combine it with transactional methods, so that I don't specify directly the transaction boundaries?
Whether you are using connection pool or not, you should always close all the resources as well.
You can do it in the finally block of the try block or starting from Java 7 by using try-with-resources statement.
try-with-resources:
try(Connection con = getConnection(url, username, password, "org.postgresql.Driver");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
) {
//statements
}catch(....) {}
In this way the connection, the statement and the resultset objects will be automatically closed after use.
All the transactions will be commited anyway even if you don't close the connection.