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:
Post a Comment