ORMLite --- After .commit , .setAutoCommit --- Connection NOT closed

Question

I use ORMLite on a solution made by server and clients.

On server side I use PostgreSQL, on client side I use SQLite. In code, I use the same ORMLite methods, without taking care of the DB that is managed (Postgres or SQLite). I used also pooled connection.

I don't have connection opened, when I need a Sql query, ORMLite takes care to open and close the connection.

Sometime I use the following code to perform a long operation in background on server side, so in DB PostgreSql.

final ConnectionSource OGGETTO_ConnectionSource = ...... ;
final DatabaseConnection OGGETTO_DatabaseConnection =
     OGGETTO_ConnectionSource.getReadOnlyConnection( "tablename" ) ;
OGGETTO_DAO.setAutoCommit(OGGETTO_DatabaseConnection, false); 
// do long operation with Sql Queries ;
OGGETTO_DAO.commit(OGGETTO_DatabaseConnection);
OGGETTO_DAO.setAutoCommit(OGGETTO_DatabaseConnection, true);

I noted that the number of open connections increased, therefore after sometime the number is so big to stop the server (SqlException "too many clients connected to DB"). I discovered that it's due to the code snippet above, it seems that after this snippet the connection is not closed e remain open. Of course I cannot add at the end a "OGGETTO_ConnectionSource.close()", because it closes the pooled connection source. If I add at the end "OGGETTO_DatabaseConnection.close();", it doesn't work, open connections continue to increase.

How to solve it?

Answer 1

I discovered that it's due to the code snippet above, it seems that after this snippet the connection is not closed e remain open.

Let's RTFM. Here are the javadocs for the ConnectionSource.getReadOnlyConnection(...) method. I will quote:

Return a database connection suitable for read-only operations. After you are done,
you should call releaseConnection(DatabaseConnection).

You need to do something like the following code:

DatabaseConnection connection = connectionSource.getReadOnlyConnection("tablename");
try {
   dao.setAutoCommit(connection false);
   try {
      // do long operation with Sql Queries
      ...
      dao.commit(connection);
   } finally {
      dao.setAutoCommit(connection, true);
   }
} finally {
   connectionSource.releaseConnection(connection);
}

BTW, this is approximately what the TransactionManager.callInTransaction(...) method is doing although it has even more try/finally blocks to ensure that the connection's state is reset. You should probably switch to it. Here are the docs for ORMLite database transactions.