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

Question

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?

Solution 1

(completely rewritten based on comment thread; not sure why my original answer was focused on Hibernate, other than that's what I'm working with right now)

The transaction manager is completely orthogonal to data sources. Some transaction managers interact directly with data sources, some interact through an intermediate layer (eg, Hibernate), and some interact through services provided by the container (eg, JTA).

When you mark a method as @Transactional, all that means is that Spring will generate a proxy when it loads your bean, and that proxy will be handed to any other class that wants to use your bean. When the proxy's method is invoked, it (the proxy) asks the transaction manager to either give it an outstanding transaction or create a new one. Then it calls your actual bean method. When your bean method returns, the proxy interacts with the transaction manager again, to either say "I can commit" or "I must rollback". There are twists to this process; for example, a transactional method can call another transactional method and share the same transaction.

While the transaction manager interacts with the DataSource, it does not own the DataSource. You cannot ask the transaction manager to give you a connection. Instead, you must inject a frame-specific object that will return connections (such as the Hibernate SessionFactory). Alternatively, you can use the static transaction-aware utility classes, but these again are tied to a specific framework.

Solution 2

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 :-)

Solution 3

I assume you are using Plain Jdbc, you need to do is :

BaseDao {
    @Autowired
    private DataSource dataSource;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Connection getConnection() {
        // ....use dataSource to create connection
        return DataSourceUtils.getConnection(dataSource);
    }
}

FooDao extends BaseDao {
    // your foo() method
    void foo() {
       Connection conn = getConnection();
       //....
    }
}

Solution 4

What's a bit irritating about all of this is that the Spring docs are written mostly in marketing language that hides the ugliness behind the scenes.

In clear words:

your data source is stored in thread local context based on the (mostly valid) assumption that requests are always processed by a unique thread.

So, what Spring does in a pretty complicated way, is to store your stuff locally to your current execution thread, which is a trivial thing to do, but not clearly enough repeated throughout the spring docs. Spring basically puts your stuff into a "global context" to avoid pulling it through all of your interfaces and method definitions. It looks a bit magic at first, but is really just makeup.

Therefore you end up with a static DataSourceUtils method call to retrieve your stuff.

Solution 5

If you are using Spring Transaction with JDBC, configure a JdbcTemplate, and access to current transaction using JdbcTemplate.execute(ConnectionCallback). That's the standard way of accessing a connection which is configured by Spring.

View more solutions

Comments

[Aaron Digulla]

No, I'm using a Spring TransactionManager. There might be a datasource somewhere but I can't count on it.

[Aaron Digulla]

How do I get a JdbcTemplate from a Spring TransactionManager?

[Nandkumar Tekale]

There might be a datasource somewhere but I can't count on it...how can we count it then? Please provide your configuration details

[Aaron Digulla]

Deep inside the transaction manager, the must be a way to get a connect. I don't think that, for example, Spring's Hibernate session factory has a special implementation for each type of TransactionManager but I can't figure out how the two work together :-(

[Biju Kunjummen]

Since there can be multiple datasources in a transaction, the datasource is a key to retrieving the connection from the threadlocal holding the details of the transaction - you should be able to inject the relevant datasource into this class?

[Nandkumar Tekale]

you can get datasource from transaction manager but cannot connection check API static.springsource.org/spring/docs/3.0.x/javadoc-api/org/…. Totally wrong idea I think.

[Nandkumar Tekale]

even if you are using hibernateTransactionManager.

[Aaron Digulla]

But what do I do when I write a library that some unknown application wants to use? I want to write my own SessionFactory. How does that work when you need to implement it yourself?

[parsifal]

Having read some of your comments, I'm going to echo what other posters have said: trying to get your datasource from the transaction manager is the wrong way to do things, and will just confuse anyone else reading your code. To quote from the Spring docs: " Typically, applications will work with either TransactionTemplate or declarative transaction demarcation through AOP". By marking your method @Transactional, you're doing the latter.

[Aaron Digulla]

Please see my edits to my question. I'm writing a library, not an application. I get a txManager and nothing else.

[Aaron Digulla]

Please see my edits to my question. I'm writing a library, not an application. I get a txManager and nothing else.

[parsifal]

If you're planning to write your own SessionFactory -- which is not what your question asked -- then you need to carefully study both the Hibernate and Spring source, and understand how they interact. Actually, start with the Spring reference manual sections on transaction management, because I think you have a misconception about how Spring transactions work.

[Aaron Digulla]

Well, what I need is dead simple but Spring just won't let me do it. I know that there is an active transaction but my method isn't @Transactional. I need to create an object which takes Connection as constructor parameter. What should I do?

[parsifal]

After reading the edit to your question, I'm convinced that you have a misconception about how Spring's transactions work. The transaction manager -- of whatever type -- works with the datasource. It does not own the datasource. If you're not using Hibernate (not sure why I thought you were), then follow Nandkumar's advice and inject the DataSource.

[Aaron Digulla]

Sounds good but which will I do when the user uses a JtaTransactionManager? That doesn't seem to use a data source (even though I can't imagine how that works...)

[parsifal]

I'm sorry to be blunt, but there's something you need to understand: Marking a method as @Transactional does not mean that you "get" a transaction manager. It means that Spring will create a proxy class that interacts with the transaction manager. If you want to "get" anything, you need to explicitly inject it.

[parsifal]

And the Spring reference manual has an example that uses a JTA transaction manager. Note that the example defines a datasource. That datasource needs to be injected into anything that wants to access the data.

[Aaron Digulla]

Okay, the JTA example made this easier to understand. I have a TxManager and that talks to the session factory which in turn knows about the data source. So all the TxManager ever does is tell all the session factories when to create/flush connections because a new transaction has been started. Is that more or less correct?

[Matt]

new JdbcTemplate(datasource); Spring's transaction manager doesn't care about JdbcTemplate. However, JdbcTemplate will hook into the DataSourceUtils above to use a connection appropriate to the datasource it's given in the constructor.

[Matt]

That's correct. And it decides when to do this based on where your Transactional annotations are placed. When a method is called with Transactional, you're actually calling a proxy class which inspects the method being called for annotations, and does the appropriate transaction management before/after the method call.

[Shailendra]

Storing objects like datasource in threadlocal variable seems to be ugly but that is probably the only way to pass around information in a particular thread of execution. In a typical 2 phase commit invoving multiple datasources, the way transaction manager (usually provided by app servers but can be standalone as well) work is by associating a javax.transaction.Transaction object to the thread and then each datasource is enlisted using the enlistresource method of Transaction object. So this usage pattern is quite prevalent.

[Nandkumar Tekale]

@parsifal great explanation really... :)

[deFreitas]

Make sure you're running DataSourceUtils.getConnection(dataSource) inside a @Transactional method, otherwise Spring will give you a new Connection which need to be manually closed, Spring won't do that for your in that case.