How can I get a spring JdbcTemplate to read_uncommitted?

Question

Firstly, I can't use the declarative @Transactional approach as the application has multiple JDBC data-sources, I don't want to bore with the details, but suffice it to say the DAO method is passed the correct data-source to perform the logic. All JDBC data sources have the same schema, they're separated as I'm exposing rest services for an ERP system.

Due to this legacy system there are a lot of long lived locked records which I do not have control over, so I want dirty reads.

Using JDBC I would perform the following:

private Customer getCustomer(DataSource ds, String id) {
    Customer c = null;
    PreparedStatement stmt = null;
    Connection con = null;
    try {
        con = ds.getConnection();
        con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        stmt = con.prepareStatement(SELECT_CUSTOMER);
        stmt.setString(1, id);
        ResultSet res = stmt.executeQuery();
        c = buildCustomer(res);
    } catch (SQLException ex) {
        // log errors
    } finally {
        // Close resources
    }
    return c;
}

Okay, lots' of boiler-plate, I know. So I've tried out JdbcTemplate since I'm using spring.

Use JdbcTemplate

private Customer getCustomer(JdbcTemplate t, String id) {
    return t.queryForObject(SELECT_CUSTOMER, new CustomerRowMapper(), id);
}

Much nicer, but it's still using default transaction isolation. I need to somehow change this. So I thought about using a TransactionTemplate.

private Customer getCustomer(final TransactionTemplate tt,
                             final JdbcTemplate t,
                             final String id) {
    return tt.execute(new TransactionCallback<Customer>() {
        @Override
        public Customer doInTransaction(TransactionStatus ts) {
            return t.queryForObject(SELECT_CUSTOMER, new CustomerRowMapper(), id);
        }
    });
}

But how do I set the transaction isolation here? I can't find it anywhere on the callback or the TransactionTemplate to do this.

I'm reading Spring in Action, Third Edition which explains as far as I've done, though the chapter on transactions continues on to using declarative transactions with annotations, but as mentioned I can't use this as my DAO needs to determine at runtime which data-source to used based on provided arguments, in my case a country code.

Any help would be greatly appreciated.

Answer 1

I've currently solved this by using the DataSourceTransactionManager directly, though it seems like I'm not saving as much boiler-plate as I first hoped. Don't get me wrong, it's cleaner, though I still can't help but feel there must be a simpler way. I don't need a transaction for the read, I just want to set the isolation.

private Customer getCustomer(final DataSourceTransactionManager txMan,
                             final JdbcTemplate t,
                             final String id) {
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);

    TransactionStatus status = txMan.getTransaction(def);
    Customer c = null;
    try {
        c = t.queryForObject(SELECT_CUSTOMER, new CustomerRowMapper(), id);
    } catch (Exception ex) {
        txMan.rollback(status);
        throw ex;
    }
    txMan.commit(status);
    return c;
}

I'm still going to keep this one unanswered for a while as I truly believe there must be a better way.

Refer to Spring 3.1.x Documentation - Chapter 11 - Transaction Management

Answer 2

Using the TransactionTemplate helps you here, you need to configure it appropriately. The transaction template also contains the transaction configuration. Actually the TransactionTemplate extends DefaultTransactionDefinition.

So somewhere in your configuration you should have something like this.

<bean id="txTemplate" class=" org.springframework.transaction.support.TransactionTemplate">
  <property name="isolationLevelName" value="ISOLATION_READ_UNCOMMITTED"/>
  <property name="readOnly" value="true" />
  <property name="transactionManager" ref="transactionManager" />
</bean>

If you then inject this bean into your class you should be able to use the TransactionTemplate based code you posted/tried earlier.

However there might be a nicer solution which can clean up your code. For one of the projects I worked on, we had a similar setup as yours (single app multiple databases). For this we wrote some spring code which basically switches the datasource when needed. More information can be found here.

If that is to far fetched or overkill for your application you can also try and use Spring's AbstractRoutingDataSource, which based on a lookup key (country code in your case) selects the proper datasource to use.

By using either of those 2 solutions you can start using springs declarative transactionmanagement approach (which should clean up your code considerably).