How can I get a spring JdbcTemplate to read_uncommitted?

user_140037 0
Viewed 7026

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.

spring-jdbc jdbctemplate jdbc spring java
4 Answers

I'm not sure you can do it without working with at the 'Transactional' abstraction-level provided by Spring.

A more 'xml-free' to build your transactionTemplate could be something like this.

private TransactionTemplate getTransactionTemplate(String executionTenantCode, boolean readOnlyTransaction) {
    TransactionTemplate tt = new TransactionTemplate(transactionManager);
    tt.setReadOnly(readOnlyTransaction);
    tt.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);
    tt.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    return tt;
}

In any case I would "leverage" the @Transactional annotation specifying the appropriate transaction manager, binded with a separate data-source. I've done this for a multi-tenant application.

The usage:

@Transactional(transactionManager = CATALOG_TRANSACTION_MANAGER, 
    isolation = Isolation.READ_UNCOMMITTED, 
    readOnly = true)
public void myMethod() {
  //....
}

The bean(s) declaration:

public class CatalogDataSourceConfiguration {
    
    @Bean(name = "catalogDataSource")
    @ConfigurationProperties("catalog.datasource")
    public DataSource catalogDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = ENTITY_MANAGER_FACTORY)
    public EntityManagerFactory entityManagerFactory(
            @Qualifier("catalogEntityManagerFactoryBean") LocalContainerEntityManagerFactoryBean emFactoryBean) {
        return emFactoryBean.getObject();
    }

    @Bean(name= CATALOG_TRANSACTION_MANAGER)
    public PlatformTransactionManager catalogTM(@Qualifier(ENTITY_MANAGER_FACTORY) EntityManagerFactory emf) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
    }

    @Bean
    public NamedParameterJdbcTemplate catalogJdbcTemplate() {
        return new NamedParameterJdbcTemplate(catalogDataSource());
    }

}
user_5741470
Related