How do I get the connection inside of a Spring transaction?

java spring jdbc connection spring-transactions
Aaron Digulla picture Aaron Digulla · Aug 2, 2012 · Viewed 15.6k times · Source

Imagine this code:

foo() {
     Connection conn = ...;
}

foo() has been called from a method that has the annotation @Transactional. How do I get the current JDBC connection? Note that foo() is in a bean (so it can have @Autowired fields) but foo() can't have parameters (so I can't pass the connection in from somewhere).

[EDIT] I'm using jOOQ which needs either a data source or a connection. My problem: I don't know which transaction manager is configured. It could be anything; Java EE, DataSource based, something which gets the data source via JNDI. My code isn't an application, it's a library. I need to swallow what others put on my plate. Along the same lines, I can't request a Hibernate session factory because the application using me might not use Hibernate.

But I know that other code, like the Spring Hibernate integration, somehow can get the current connection from the transaction manager. I mean, Hibernate doesn't support Spring's transaction manager, so the glue code must adapt the Spring API to what Hibernate expects. I need to do the same thing but I couldn't figure out how it works.

[EDIT2] I know that there is an active transaction (i.e. Spring has a Connection instance somewhere or at least a transaction manager which can create one) but my method isn't @Transactional. I need to call a constructor which takes java.sql.Connection as parameter. What should I do?

Answer

Biju Kunjummen picture Biju Kunjummen · Aug 2, 2012

You can probably try DataSourceUtils.getConnection(dataSource), per the API it should return you the current connection for the datasource.

Update: Based on your comments and the source code for org.springframework.transaction.support.TransactionSynchronizationManager :

Like I said, the key to getting the connection is the datasource name, if this cannot be obtained, one way out by looking at the source code is to try this:

TransactionSynchronizationManager.getResourceMap() will return a map of datasource to ConnectionHolder in the current thread, assuming that you have only 1 resource involved in transaction, you can probably do a map.values().get(0) to get the first ConnectionHolder, from which you can get a connection by calling .getConnection()

So essentially calling the following:

TransactionSynchronizationManager.getResourceMap().values().get(0).getConnection()

There probably has to be better way though :-)

Related questions

Spring-Boot: How do I set JDBC pool properties like maximum number of connections?

Spring-Boot is a pretty awesome tool, but the documentation is a bit sparse when it comes to more advanced configuration. How can I set properties like the maximum size for my database connection pool? Spring-Boot supports tomcat-jdbc, HikariCP and Commons …

Read More
How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?

I was wondering if there is a more elegant way to do IN() queries with Spring's JDBCTemplate. Currently I do something like that: StringBuilder jobTypeInClauseBuilder = new StringBuilder(); for(int i = 0; i < jobTypes.length; i++) { Type jobType = jobTypes[i]; if(…

Read More
Using prepared statements with JDBCTemplate

I'm using the JDBC template and want to read from a database using prepared statements. I iterate over many lines in a .csv file, and on every line I execute some SQL select queries with corresponding values. I want to …

Read More