In our application we are using spring boot AND sqljdbc4.jar.We need to connect to different databases(catalogs) inside the same schema (meaning they are in same sql server instance). So we need to configure data source in such a way that it can connect to multiple databases and do database operations without issues. But when tried to connect it throws below error
invalid object
table_namefound error. // I donot have the complete stack trace but the main error is as mentioned invalid object error.
So we configured two datasources and the source code is as below
Project Structure:
src/main/java
- com.foobar
- foo
- domain
- repo
- bar
- domain
- repo
spring.datasource.jdbc-url=jdbc:jdbc:microsoft:sqlserver://HOST:1433;it_foo ```(important note: it_foo will be replaced with qa_foo in different environment which will be managed by profiles)
spring.datasource.username=fooadmin
spring.datasource.password=foo123
spring.datasource.driver-class-name=com.microsoft.jdbc.sqlserver.SQLServerDriver
bar.datasource.jdbc-url=jdbc:jdbc:microsoft:sqlserver://HOST:1433;it_bar(important note: it_foo will be replaced with qa_foo in different environment which will be managed by profiles)
bar.datasource.username=baradmin
bar.datasource.password=bar123
bar.datasource.driver-class-name=com.microsoft.jdbc.sqlserver.SQLServerDriver
Datasource configuration for foo database
package com.foobar;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactory",
basePackages = { "com.foobar.foo.repo" }
)
public class FooDbConfig {
@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean
entityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier("dataSource") DataSource dataSource
) {
return builder
.dataSource(dataSource)
.packages("com.foobar.foo.domain")
.persistenceUnit("foo")
.build();
}
@Primary
@Bean(name = "transactionManager")
public PlatformTransactionManager transactionManager(
@Qualifier("entityManagerFactory") EntityManagerFactory
entityManagerFactory
) {
return new JpaTransactionManager(entityManagerFactory);
}
}
Datasource configuration for bar
med “BarDbConfig.java”
package com.foobar;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "barEntityManagerFactory",
transactionManagerRef = "barTransactionManager",
basePackages = { "com.foobar.bar.repo" }
)
public class BarDbConfig {
@Bean(name = "barDataSource")
@ConfigurationProperties(prefix = "bar.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "barEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean
barEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier("barDataSource") DataSource dataSource
) {
return
builder
.dataSource(dataSource)
.packages("com.foobar.bar.domain")
.persistenceUnit("bar")
.build();
}
@Bean(name = "barTransactionManager")
public PlatformTransactionManager barTransactionManager(
@Qualifier("barEntityManagerFactory") EntityManagerFactory
barEntityManagerFactory
) {
return new JpaTransactionManager(barEntityManagerFactory);
}
}
and the overall code is same as in this link: https://medium.com/@joeclever/using-multiple-datasources-with-spring-boot-and-spring-data-6430b00c02e7
I was also going through this links: How to configure Spring boot for work with two databases?
Spring boot connect with multiple schemas in mysql
I had followed many tutorials to find the optimal solution and went through different posts on stack overflow, but none have a proper solution
Also in short, I need to satisfy these requirements.
To figure out optimal solution for this problem so if we need to configure more databases or schemas, the current code should not affect at all.
It should work for different profiles/ environments which has different database names.
EDIT: With the above code it works fine but as the databases are in the same database server instance, is there any other optimal solution so as configuring only one datasource?
