Should a connection obtained from a hibernate session be closed?

Question

Do I need to manually close a connection obtained from a hibernate session?

If I do this, will I be closing one of the connections in the connection pool?

Will hibernate automatically close the connection if I do not?

            Connection con = null;
            PreparedStatement ps = null;
            ResultSet rs = null;

            String query = "sql query";
            try {
                Session session = this.sessionFactory.getCurrentSession();
                con = session.connection();
                ps = con.prepareStatement(query);
                rs = ps.executeQuery();
                while (rs.next()) {
                    //read result set
                }
            } catch (SQLException exp) {
                log.error("Error: ", exp);
            } finally{
               if(ps != null)
                   ps.close();
               if(con != null)
                   con.close();  //Is this required?
            }

Answer 1

In general no you should not close that connection. The hibernate session may still want to use the same connection object for other tasks later in the life-cycle of the session that you are going to break by closing its connection. It is hibernate's job to manage the lifecycle of that connection object.

The exception is, as described in the javadoc, if you have ConnectionReleaseMode.AFTER_STATEMENT turned on and the JDBC provider supports that mode. Which is not often the case since you have to be in auto-commit mode for it to work.

Answer 2

Yes, you should close the connection. If you are using a connection pool, you should disconnect the session, this will not actually close it, but it will release it back to the pool.

For non-connection pools. In the javadocs it states

Note: A Connection object is automatically closed when it is garbage collected. Certain fatal errors also close a Connection object.

However, as it will be closed, it would be better to do by yourself.