Spring / Hikari / Postgres / Multiple datasources

AppConfig.java

package test.app.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class AppConfig {

    @Bean(name = "readWriteDataSource")
    @ConfigurationProperties(prefix = "spring.rw-datasource")
    public DataSource readWriteDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "readOnlyDataSource")
    @ConfigurationProperties(prefix = "spring.ro-datasource")
    public DataSource readOnlyDataSource() {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setReadOnly(true);

        return hikariDataSource;
    }

    @Bean(name = "readWriteJdbcTemplate")
    @DependsOn("readWriteDataSource")
    public NamedParameterJdbcTemplate readWriteJdbcTemplate(@Qualifier("readWriteDataSource") DataSource readWriteDataSource) {
        return new NamedParameterJdbcTemplate(readWriteDataSource);
    }

    @Bean(name = "readOnlyJdbcTemplate")
    @DependsOn("readOnlyDataSource")
    public NamedParameterJdbcTemplate readOnlyJdbcTemplate(@Qualifier("readOnlyDataSource") DataSource readOnlyDataSource) {
        return new NamedParameterJdbcTemplate(readOnlyDataSource);
    }
}

Repo.java

package test.app.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.PostConstruct;

/**
 * Usage example
 */
@Repository
public class Repo {

    private final NamedParameterJdbcTemplate readOnlyJdbcTemplate;
    private final NamedParameterJdbcTemplate readWriteJdbcTemplate;

    public Repo(
        @Qualifier("readOnlyJdbcTemplate") NamedParameterJdbcTemplate readOnlyJdbcTemplate,
        @Qualifier("readWriteJdbcTemplate") NamedParameterJdbcTemplate readWriteJdbcTemplate
    ) {
        this.readOnlyJdbcTemplate = readOnlyJdbcTemplate;
        this.readWriteJdbcTemplate = readWriteJdbcTemplate;
    }

    @PostConstruct
    public void trySomething() {
        readWriteJdbcTemplate.update("INSERT INTO a (i) VALUES(1)", new MapSqlParameterSource());
        try {
            readOnlyJdbcTemplate.update("INSERT INTO a (i) VALUES(1)", new MapSqlParameterSource());
        } catch (DataAccessException e) {
            System.out.println("It's not okay, 'cause ro only");
        }
        Integer num = readOnlyJdbcTemplate.query(
            "SELECT 1 as num",
            new MapSqlParameterSource(),
            rs -> rs.next() ? rs.getInt("num") : null
        );//read is ok, returns 1
        System.out.println(num);
    }
}

Setup.md

• build.gradle

implementation group: 'org.springframework.boot', name: 'spring-boot-starter-jdbc'
implementation 'org.postgresql:postgresql:42.2.5'

• application.properties:

spring.rw-datasource.type=com.zaxxer.hikari.HikariDataSource
spring.rw-datasource.jdbc-url=jdbc:postgresql://localhost:5432/test1
spring.rw-datasource.driver-class-name=org.postgresql.Driver
spring.rw-datasource.username=postgres
spring.rw-datasource.password=example
spring.rw-datasource.hikari.connection-timeout=20000
spring.rw-datasource.hikari.minimum-idle=10
spring.rw-datasource.hikari.maximum-pool-size=100
spring.rw-datasource.hikari.idle-timeout=10000
spring.rw-datasource.hikari.max-lifetime=1800000
spring.rw-datasource.hikari.auto-commit=true

spring.ro-datasource.type=com.zaxxer.hikari.HikariDataSource
spring.ro-datasource.jdbc-url=jdbc:postgresql://localhost:5432/test2
spring.ro-datasource.driver-class-name=org.postgresql.Driver
spring.ro-datasource.username=postgres
spring.ro-datasource.password=example
spring.ro-datasource.hikari.connection-timeout=20000
spring.ro-datasource.hikari.minimum-idle=10
spring.ro-datasource.hikari.maximum-pool-size=100
spring.ro-datasource.hikari.idle-timeout=10000
spring.ro-datasource.hikari.max-lifetime=1800000
spring.ro-datasource.hikari.auto-commit=true

logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.com.zaxxer.hikari=TRACE