[FIXED] Spring boot Hibernate error "Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set" when working with multiple data sources

Issue

I am building a Springboot application that needs to talk to 2 different databases (DB2 and Oracle).

Spring-boot-starter-parent version 2.6.6

hibernate version 5.6.7.Final

I first added DB2 support and that was working fine until I added oracle related settings in application.properties file below.

Adding oracle related settings causes following error:

org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set

Below are the databases and hibernate related settings from my application.properties:

# ==============================
# = DB2 
db2.datasource.jdbc-url=jdbc:db2://SERVER1:PORT/DATABASE-1:currentSchema=SCHEMA;
db2.datasource.username=USER1
db2.datasource.password=PASSWORD1
db2.datasource.driver-class-name=com.ibm.db2.jcc.DB2Driver
db2.jpa.properties.hibernate.dialect=org.hibernate.dialect.DB2390Dialect

# ==============================
# = ORACLE
oracle.datasource.jdbc-url=jdbc:oracle:thin:@SERVER2:PORT/DATABASE-2
oracle.datasource.username=USER2
oracle.datasource.password=PASSWORD2
oracle.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

# ==============================
# = JPA / HIBERNATE
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

My pom.xml contains dependencies for oracle and DB2 amongs other required dependencies:

... 
<dependency>
        <groupId>com.ibm.db2</groupId>
        <artifactId>jcc</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc8</artifactId>
        <scope>runtime</scope>
    </dependency>
...

I have placed my entities, repositories, and data source configurations into different packages as I read is required in this article https://www.javadevjournal.com/spring-boot/multiple-data-sources-with-spring-boot/. My package structure looks like:

project
  - dataconfig
      - db2
        - config
        - entity
        - repository
      - oracle
        - config
        - entity
        - repository

I also added some entities and repositories and my configuration classes.

Here is my DB2Configuration class:

package project.dataconfig.db2.config;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "db2EntityManagerFactory",
        transactionManagerRef = "db2TransactionManager",
        basePackages = {
                "project.dataconfig.db2.repository"
        }
)
public class Db2Configuration {

    @Primary
    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "db2.datasource")
    public DataSource db2DataSource() {

        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "db2EntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder
            , @Qualifier("db2DataSource") DataSource dataSource) {

        return builder
                .dataSource(dataSource)
                .packages("project.dataconfig.db2.entity")
                .persistenceUnit("db2persistanceunit")
                .build();
    }

    @Primary
    @Bean(name = "db2TransactionManager")
    public PlatformTransactionManager db2TransactionManager(
      @Qualifier("db2EntityManagerFactory") EntityManagerFactory db2EntityManagerFactory) {

        return new JpaTransactionManager(db2EntityManagerFactory);
    }
}

Here is my OracleConfiguration class:

package project.dataconfig.oracle.config;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "oracleEntityManagerFactory",
        transactionManagerRef = "oracleTransactionManager",
        basePackages = {
                "project.dataconfig.oracle.repository"
        }
)
public class OracleConfiguration {

        @Bean(name = "oracleDataSource")
        @ConfigurationProperties(prefix = "oracle.datasource")
        public DataSource oracleDataSource() {

                return DataSourceBuilder.create().build();
        }

        @Bean(name = "oracleEntityManagerFactory")
        public LocalContainerEntityManagerFactoryBean entityManagerFactory(
                EntityManagerFactoryBuilder builder
                , @Qualifier("oracleDataSource") DataSource dataSource) {

                return builder
                        .dataSource(dataSource)
                        .packages("project.dataconfig.oracle.entity")
                        .persistenceUnit("oraclepersistanceunit")
                        .build();
        }

        @Bean(name = "oracleTransactionManager")
        public PlatformTransactionManager oracleTransactionManager(
                @Qualifier("oracleEntityManagerFactory") EntityManagerFactory oracleEntityManagerFactory) {

                return new JpaTransactionManager(oracleEntityManagerFactory);
        }
}

Before I added oracle related settings in application.properties file, my application worked just find with only DB2 settings as described above.

Once I added oracle related settings and configuration, I started getting this error:

org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set

Based on the error, I think the problem is caused by my settings for Hibernate dialect in application.properties file. I think it is caused by one of the two settings

...
db2.jpa.properties.hibernate.dialect=org.hibernate.dialect.DB2390Dialect

...
oracle.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

How do I resolve this issue?

Solution

I figure it out.

Modify method entityManagerFactory for both Db2Configuration and OracleConfiguration to supply them with the information about hibernate dialect:

for DB2

@Primary
@Bean(name = "db2EntityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
        EntityManagerFactoryBuilder builder
        , @Qualifier("db2DataSource") DataSource dataSource) {

    final HashMap<String, Object> hibernateProperties = new HashMap<String, Object>();
    hibernateProperties.put("hibernate.dialect", "org.hibernate.dialect.DB2390Dialect");

    return builder
            .dataSource(dataSource)
            .packages("project.dataconfig.db2.entity")
            .properties(hibernateProperties)
            .persistenceUnit("db2persistanceunit")
            .build();
}

for Oracle

@Bean(name = "oracleEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
        EntityManagerFactoryBuilder builder
        , @Qualifier("oracleDataSource") DataSource dataSource) {

    final HashMap<String, Object> hibernateProperties = new HashMap<String, Object>();
    hibernateProperties.put("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");

    return builder
            .dataSource(dataSource)
            .packages("project.dataconfig.oracle.entity")
            .properties(hibernateProperties)
            .persistenceUnit("oraclepersistanceunit")
            .build();
}

After this, my console shows when running app, indicating all is good:

HHH000400: Using dialect: org.hibernate.dialect.DB2390Dialect
 Initialized JPA EntityManagerFactory for persistence unit 'db2persistanceunit'
HHH000204: Processing PersistenceUnitInfo [name: oraclepersistanceunit]
HHH000400: Using dialect: org.hibernate.dialect.Oracle10gDialect
Initialized JPA EntityManagerFactory for persistence unit 'oraclepersistanceunit'

My actuator /health endpoint also sees both databases as up and running.

Answered By - pixel
Answer Checked By - Katrina (JavaFixing Volunteer)