CODEWITHSUNDEEP
javaspringjdbcconnectionspring-transactions
Aaron Digulla
Aaron Digulla

Reputation: 328830

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

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?

Upvotes: 12

Views: 24232

Answers (6)

parsifal
parsifal

Reputation: 141

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.

Edit: I completely rewrote my answer 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.

Upvotes: 10

Beto Neto
Beto Neto

Reputation: 4112

3 another ways:

@Component
public class MyServiceNonTransactional {

    @Autowired
    private TransactionTemplate transactionTemplate;
    
    @Autowired
    private DataSource dataSource;
    
    public void doStuff() {
        transactionTemplate.executeWithoutResult(status -> {
            Connection connection = DataSourceUtils.getConnection(dataSource);
            // here we go...
        });
    }

}

@Service
public class MyServiceTransactional {

    @Autowired
    private DataSource dataSource;
    
    @Transactional
    public void doStuff() {
        Connection connection = DataSourceUtils.getConnection(dataSource);
        // here we go...
    }

}

@Service
public class MyServiceViaJdbc {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void doStuff() {
        jdbcTemplate.execute((ConnectionCallback<Void>) conn -> {
            // "conn" here we go!
            return null;
        });
    }
        
}

Upvotes: 2

user1050755
user1050755

Reputation: 11691

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.

Upvotes: 5

Biju Kunjummen
Biju Kunjummen

Reputation: 49935

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

Upvotes: 15

Nandkumar Tekale
Nandkumar Tekale

Reputation: 16158

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();
       //....
    }
}

Upvotes: 7

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7322

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.

Upvotes: 3

Related Questions