How to use multiple schema's in one database by using JPA + Hibernate with Spring-boot?

Question

I need it to access 2 different schema's in one database(MySQL).

I wrote two config classes here:

package twodb.webfi.config;
import java.util.Properties;
import javax.persistence.EntityManagerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;   
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver;
import org.springframework.orm.hibernate4.HibernateExceptionTranslator;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@Configuration
@ComponentScan
@EnableTransactionManagement
@EnableJpaRepositories(basePackages={"twodb.webfi","twodb.mc"},
entityManagerFactoryRef = "entityManagerFactory1", 
transactionManagerRef = "transactionManager1",
considerNestedRepositories = true)
public class FirstConfig {

@Autowired
private Environment env;

@Bean
public PlatformTransactionManager transactionManager1()
{
    EntityManagerFactory factory = entityManagerFactory1().getObject();
    return new JpaTransactionManager(factory);
}

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory1()
{
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(Boolean.TRUE);
    vendorAdapter.setShowSql(Boolean.TRUE);

    factory.setDataSource(dataSource1());
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan(new String[] {"twodb.webfi.entities","twodb.mc.model"});
    Properties jpaProperties = new Properties();

    jpaProperties.put("hibernate.hbm2ddl.auto", env.getProperty("hibernate.ddl-auto"));
    jpaProperties.setProperty("hibernate.dialect", env.getProperty("hibernate.dialect"));
    jpaProperties.setProperty("hibernate.show_sql", env.getProperty("hibernate.showSql"));
    factory.setJpaProperties(jpaProperties);

    factory.afterPropertiesSet();
    factory.setLoadTimeWeaver(new InstrumentationLoadTimeWeaver());
    return factory;
}

@Bean
public HibernateExceptionTranslator hibernateExceptionTranslator()
{
    return new HibernateExceptionTranslator();
}


@Bean(destroyMethod = "close")
 public HikariDataSource dataSource1() {
    System.out.println("<--HikariDataSource1-->");
        HikariConfig dataSourceConfig = new HikariConfig();
        dataSourceConfig.setDriverClassName(env.getProperty("ui.db.driver"));
        dataSourceConfig.setJdbcUrl(env.getProperty("ui.db.url"));
        dataSourceConfig.setUsername(env.getProperty("ui.db.username"));
        dataSourceConfig.setPassword(env.getProperty("ui.db.password"));           

        dataSourceConfig.setMaximumPoolSize(env.getProperty("hikari.maximumPoolSize", Integer.class, new Integer(1)));
        dataSourceConfig.setMinimumIdle(env.getProperty("hikari.minimumIdle", Integer.class, new Integer(1)));
        dataSourceConfig.setIdleTimeout(env.getProperty("hikari.idleTimeout", Long.class, new Long(600000)));   // 10 min
        dataSourceConfig.setMaxLifetime(env.getProperty("hikari.maxLifetime", Long.class, new Long(1800000)));  // 30 min
        dataSourceConfig.setPoolName(env.getProperty("hikari.poolName", "upiCp"));

        return new HikariDataSource(dataSourceConfig);
    }

 }

Another Config Class here:

package twodb.webfi.config;
import java.util.Properties;
import javax.persistence.EntityManagerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver;
import org.springframework.orm.hibernate4.HibernateExceptionTranslator;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@Configuration
@ComponentScan
@EnableTransactionManagement
@EnableJpaRepositories(basePackages={"twodb.webfi","twodb.mc"},
entityManagerFactoryRef = "entityManagerFactory2", 
transactionManagerRef = "transactionManager2",
considerNestedRepositories = true)
public class SecondConfig {

@Autowired
private Environment env;    

@Bean
public PlatformTransactionManager transactionManager2()
{
    EntityManagerFactory factory = entityManagerFactory2().getObject();
    return new JpaTransactionManager(factory);
}

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory2()
{
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(Boolean.TRUE);
    vendorAdapter.setShowSql(Boolean.TRUE);

    factory.setDataSource(dataSource2());
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan(new String[] {"twodb.webfi.entities","twodb.mc.model"});
    Properties jpaProperties = new Properties();

    jpaProperties.put("hibernate.hbm2ddl.auto", env.getProperty("hibernate.ddl-auto"));
    jpaProperties.setProperty("hibernate.dialect", env.getProperty("hibernate.dialect"));
    jpaProperties.setProperty("hibernate.show_sql", env.getProperty("hibernate.showSql"));
    factory.setJpaProperties(jpaProperties);

    factory.afterPropertiesSet();
    factory.setLoadTimeWeaver(new InstrumentationLoadTimeWeaver());
    return factory;
}

@Bean
public HibernateExceptionTranslator hibernateExceptionTranslator()
{
    return new HibernateExceptionTranslator();
}   

@Bean(destroyMethod = "close")
 public HikariDataSource dataSource2() {
    System.out.println("<--HikariDataSource2-->");
        HikariConfig dataSourceConfig = new HikariConfig();
        dataSourceConfig.setDriverClassName(env.getProperty("web.db.driver"));
        dataSourceConfig.setJdbcUrl(env.getProperty("web.db.url"));
        dataSourceConfig.setUsername(env.getProperty("web.db.username"));
        dataSourceConfig.setPassword(env.getProperty("web.db.password"));           

        dataSourceConfig.setMaximumPoolSize(env.getProperty("hikari.maximumPoolSize", Integer.class, new Integer(1)));
        dataSourceConfig.setMinimumIdle(env.getProperty("hikari.minimumIdle", Integer.class, new Integer(1)));
        dataSourceConfig.setIdleTimeout(env.getProperty("hikari.idleTimeout", Long.class, new Long(600000)));   // 10 min
        dataSourceConfig.setMaxLifetime(env.getProperty("hikari.maxLifetime", Long.class, new Long(1800000)));  // 30 min
        dataSourceConfig.setPoolName(env.getProperty("hikari.poolName", "upiCp"));

        return new HikariDataSource(dataSourceConfig);
    }

  }

Here i geeting exception like

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'webCommonDaoImpl': Injection of persistence dependencies failed; nested exception is org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type 'javax.persistence.EntityManagerFactory' available: expected single matching bean but found 2: entityManagerFactory1,entityManagerFactory

Can anybody point me to the correct direction of how to use multiple database by using JPA + Hibernate with Spring-boot?

I am ui and web schema's in mysql. Both are same database.

use @Primary for one of the datasource, transactionmanagers — pvpkiran, Commented Jun 8, 2018 at 12:48
This works for me. Requirements: spring boot 2.2.11.RELEASE stackoverflow.com/a/67716454/16044144 — Ruslan Temirbulatov, Commented May 28, 2021 at 6:16

gvk · Accepted Answer · 2018-06-08 14:06:54Z

Using qualifiers and bean names will avoid any conflicts.

I have created different data source configurations in my application yaml files like below

db-abc:
  datasource:
  url: ${ABC_DATABASE_URL}
  username: ${ABC_DATABASE_USERNAME}
  password: ${ABC_DATABASE_PASSWORD}
  driverClassName: org.postgresql.Driver
db-xyz:
  datasource:
  url: ${XYZ_DATABASE_URL}
  username: ${XYZ_DATABASE_USERNAME}
  password: ${XYZ_DATABASE_PASSWORD}
  driverClassName: org.postgresql.Driver
db-batch:
  datasource:
  url: ${DATABASE_URL}
  username: ${DATABASE_USERNAME}
  password: ${DATABASE_PASSWORD}
  driverClassName: org.postgresql.Driver

And have created my data source configuration some like below

@Configuration
public class DataSourceConfig {

private final Environment env;

@Autowired
public DataSourceConfig(Environment env) {
    this.env = env;
}

@Bean(name = "abcDataSource")
@ConfigurationProperties(prefix = "db-abc.datasource")
public DataSource abcDataSource() {
    return DataSourceBuilder
            .create()
            .url(env.getProperty("db-abc.datasource.url"))
            .driverClassName(env.getProperty("db-abc.datasource.driverClassName"))
            .username(env.getProperty("db-abc.datasource.username"))
            .password(env.getProperty("db-abc.datasource.password"))
            .build();
}

@Bean(name = "xyzDataSource")
@ConfigurationProperties(prefix = "db-xyz.datasource")
public DataSource xyzDataSource() {
    return DataSourceBuilder
            .create()
            .url(env.getProperty("db-xyz.datasource.url"))
            .driverClassName(env.getProperty("db-xyz.datasource.driverClassName"))
            .username(env.getProperty("db-xyz.datasource.username"))
            .password(env.getProperty("db-xyz.datasource.password"))
            .build();
}

@Primary
@Bean(name = "batchDataSource")
@ConfigurationProperties(prefix = "db-batch.datasource")
public DataSource batchDataSource() {
    return DataSourceBuilder
            .create()
            .url(env.getProperty("db-batch.datasource.url"))
            .driverClassName(env.getProperty("db-batch.datasource.driverClassName"))
            .username(env.getProperty("db-batch.datasource.username"))
            .password(env.getProperty("db-batch.datasource.password"))
            .build();
}
}

And use respective bean to do what ever i wanted to do...using qualifiers and bean names will avoid any conflicts.

for multiple schemas in a single database, you can use respective schema as above

@Table(
    schema = "schema1", 
    name = "TBL_SCHEMA1_TABLE"
)
public class Schema1Entity implements Serializable {                                                                            @Entity
@Table(
    schema = "schema2", 
    name = "TBL_SCHEMA2_TABLE"
)
public class Schema2Entity implements Serializable {

for multiple schemas in a single database, you can use respective schema as above — gvk, Commented Jun 8, 2018 at 14:03
This doesn't work for me, it's still saying: Table 'default_schema.table1' doesn't exist where it should really hit schema1.table1 — pavlee, Commented Apr 7, 2022 at 13:20

Collectives™ on Stack Overflow

How to use multiple schema's in one database by using JPA + Hibernate with Spring-boot?

1 Answer 1

Not the answer you're looking for? Browse other questions tagged
java
spring
spring-boot
spring-security
spring-data-jpa
or ask your own question.

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Not the answer you're looking for? Browse other questions tagged javaspringspring-bootspring-securityspring-data-jpa or ask your own question.

Linked

Related

Not the answer you're looking for? Browse other questions tagged
java
spring
spring-boot
spring-security
spring-data-jpa
or ask your own question.