Total Pageviews

2019/03/08

[Spring Boot] How to configure multiple datasource in Spring boot?

Problem
I have Sybase and Microsoft SQL Server database connection in my Spring boot, how to configure two data source in my project?

Sybase repository and entity classes are located in test.repository.sybase, and test.entity.sybase

Microsoft SQL Server repository and entity classes are located in test.repository.sqlserver and test.entity.sqlserver


How-To
Configure 2 database configuration in application.yml:
spring:
  jpa:
    show-sql: true
    open-in-view: false
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
  datasource:
    url: jdbc:sybase:Tds:192.168.0.1:5000/TEST
    username: user
    password: secret
    driver-class-name: com.sybase.jdbc4.jdbc.SybDriver
    hikari:
      pool-name: pool-primary
      connection-test-query: SELECT 1

sqlserver:      
    datasource:
      url: jdbc:sqlserver://192.168.0.2:1433
      username: user
      password: secret
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      hikari:
        pool-name: pool-sqlserver
        connection-test-query: SELECT 1

Sybase configuration:
package test.config;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.autoconfigure.jdbc.JdbcProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import com.zaxxer.hikari.HikariDataSource;

@Configuration
@Primary
@EnableJpaRepositories(transactionManagerRef = "transactionManager", basePackages = {
        "test.repository.sybase" })
public class SybaseDbConfig {

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driver-class-name}")
    private String driver;

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariDataSource dataSource(DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class)
                .build();
    }

    @Bean
    @Primary
    public JdbcTemplate jdbcTemplate(@Qualifier("dataSource") DataSource dataSource,
            JdbcProperties properties) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    @Primary
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate(
            @Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        return new NamedParameterJdbcTemplate(jdbcTemplate);
    }

    @Bean(name = "entityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("dataSource") DataSource dataSource) {
        return builder.dataSource(dataSource).packages("test.entity.sybase")
                .persistenceUnit("sybase").build();
    }

    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

}


Microsoft SQL Server Configuration:
package test.config;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import com.zaxxer.hikari.HikariDataSource;

@Configuration
@EnableJpaRepositories(entityManagerFactoryRef = "sqlserverEntityManagerFactory", 
                       transactionManagerRef = "sqlserverTransactionManager", 
                       basePackages = {"test.repository.sqlserver" })
public class SqlServerDbConfig {

    @Value("${sqlserver.datasource.url}")
    private String url;

    @Value("${sqlserver.datasource.username}")
    private String username;

    @Value("${sqlserver.datasource.password}")
    private String sqlPassword;

    @Value("${sqlserver.datasource.driver-class-name}")
    private String sqlDriver;

    @Bean
    @Qualifier("sqlserverDataSource")
    @ConfigurationProperties(prefix = "npa.ai.datasource.hikari")
    public HikariDataSource sqlserverDataSource() {
        return DataSourceBuilder.create().url(url).username(username).password(sqlPassword)
                .driverClassName(sqlDriver).type(HikariDataSource.class).build();
    }

    @Bean
    @Qualifier("sqlserverJdbcTemplate")
    public JdbcTemplate sqlserverJdbcTemplate(@Qualifier("sqlserverDataSource") DataSource sqlserverDataSource) {
        return new JdbcTemplate(sqlserverDataSource);
    }

    @Bean
    @Primary
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate(
            @Qualifier("sqlserverJdbcTemplate") JdbcTemplate jdbcTemplate) {
        return new NamedParameterJdbcTemplate(jdbcTemplate);
    }

    @Bean(name = "sqlserverEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean sqlserverEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("sqlserverDataSource") DataSource sqlserverDataSource) {
        return builder.dataSource(sqlserverDataSource).packages("test.entity.sqlserver")
                .persistenceUnit("sqlserver").build();
    }

    @Bean(name = "sqlserverTransactionManager")
    public PlatformTransactionManager sqlserverTransactionManager(
            @Qualifier("sqlserverEntityManagerFactory") EntityManagerFactory sqlserverEntityManagerFactory) {
        return new JpaTransactionManager(sqlserverEntityManagerFactory);
    }

}



Reference
[1] https://scattercode.co.uk/2016/01/05/multiple-databases-with-spring-boot-and-spring-data-jpa/

No comments: