Can I separate JDBC reads and writes using Spring Framework?

Question

I'm using Spring Framework JDBC to deal with all my DB jobs on PostgreSQL. Now I want to separate my reads and writes into master and slave servers. Can I achieve this without involving other frameworks like Hibernate? What is the best practice of this?

Answer 1

You can do that by dealing with multiple datasource configuration. There are several way to do this, but I prefer as below.

In your context.xml, set master and slave datasource each.

<bean id="masterDataSource" class="...">
    <property name = "driverClassName" value="value">
    ...
</bean>

<bean id="slaveDataSource" class="...">
    <property name = "driverClassName" value="...">
    ...
</bean>

And set Redirector which will device requests

<bean id="dataSourceRedirector" class="..">
    <constructor-arg name="readDataSource" ref="slaveDataSource"/>
    <constructor-arg name="writeDataSource" ref="masterDataSource"/>
</bean>

and make redirector as main datasource. Note that we are using LazyConnectionDataSourceProxy.

<bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
    <constructor-arg name="targetDataSource" ref="dataSourceRedirector" />
</bean>

And implement the class Redirector like:

public class DataSourceRedirector extends AbstractRoutingDataSource {

    private final DataSource writeDataSource;
    private final DataSource readDataSource;

    public DataSourceRedirector(DataSource writeDataSource, DataSource readDataSource) {
        this.writeDataSource = writeDataSource;
        this.readDataSource = readDataSource;
    }

    @PostConstruct
    public void init() {
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("write", writeDataSource);
        dataSourceMap.put("read", readDataSource);
        this.setTargetDataSources(dataSourceMap);
        this.setDefaultTargetDataSource(writeDataSource);
    }

    @Override
    protected Object determineCurrentLookupKey() {
        String dataSourceType =
                TransactionSynchronizationManager.isCurrentTransactionReadOnly() ? "read" : "write";
        return dataSourceType;
    }
} 

And then @Transactional(readOnly = true) to the method you want to make it querying on slave.