Hibernate Tips: Get the SQL Connection used by your Hibernate Session

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please leave a comment below.

Question:

We’re using Hibernate in our application, and we now need to implement a reporting use case for which I want to use plain JDBC. How can I get the Connection that’s currently used by Hibernate?

Solution:

Hibernate doesn’t provide any method to retrieve the java.sql.Connection that’s used by the current Session. But you can call the doWork(Work work) or doReturningWork(ReturningWork<T> work) method on the Session interface to perform JDBC related work.

If you use Hibernate via the JPA API, you first need to unwrap your EntityManager to get a Hibernate Session. I explained it in more details in Hibernate Tips: How to access Hibernate APIs from JPA.

OK, let’s take a closer look at the doWork and doReturningWork methods.

Operations without a return value

If you want to process some data without returning any values to the caller, you should call the doWork method with an implementation of the Work interface. You can see an example of it in the following code snippet.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

Session session = em.unwrap(Session.class);
session.doWork(new Work() {
	
	@Override
	public void execute(Connection con) throws SQLException {
		// do something useful
		try (PreparedStatement stmt = con.prepareStatement("SELECT count(b.id) FROM Book b")) {
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				log.info("Found " + rs.getInt(1) + " books.");
			}
		}
	}
});

em.getTransaction().commit();
em.close();

The interface only defines the execute(Connection connection) method. Hibernate calls it with a parameter of type java.sql.Connection. This is the connection that’s used by the current Hibernate Session, and you can use it within the execute method to perform your JDBC operations.

Operations with a return value

For all operations that return a value, you need to use the doReturningWork method and call it with an implementation of the ReturningWork interface. As you can see in the following code snippet, you can do that in basically the same way as you call the doWork method. The only difference is that the execute and the doReturningWork method return a value instead of void.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

Session session = em.unwrap(Session.class);
Integer bookCount = session.doReturningWork(new ReturningWork<Integer>() {

	@Override
	public Integer execute(Connection con) throws SQLException {
		// do something useful
		try (PreparedStatement stmt = con.prepareStatement("SELECT count(b.id) FROM Book b")) {
			ResultSet rs = stmt.executeQuery();
			rs.next();
			return rs.getInt(1);
		}
	}
	
});
log.info("Found " + bookCount + " books.");

em.getTransaction().commit();
em.close();

Learn more:

Before you use the doWork and doReturningWork methods to get access to the java.sql.Connection, please check if you can implement your operation as a native SQL query or using jOOQ. Both provide a more comfortable way to execute complex SQL queries.