Couldn't determine Dialect for "oracle"

Question

I am using Spring boot(2.3.5), Oracle19c DB, and Hibernate(5.4).

I tried to make multi-datasource connection, but I keep getting a dialect error Couldn't determine Dialect for "oracle".

Error creating bean with name 'jdbcDialect' defined in class path resource [org/springframework/boot/autoconfigure/data/jdbc/JdbcRepositoriesAutoConfiguration$SpringBootJdbcConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.data.relational.core.dialect.Dialect]: Factory method 'jdbcDialect' threw exception; nested exception is org.springframework.data.jdbc.repository.config.DialectResolver$NoDialectException: Cannot determine a dialect for org.springframework.jdbc.core.JdbcTemplate@2ba9ed19. Please provide a Dialect.

I basically followed this tutorial to configure multiple data sources.

application.properties:

spring.datasource-primary.username=oracleprimary
spring.datasource-primary.password=oracleprimary
spring.datasource-primary.url=jdbc:oracle:thin:@//localhost:1521/orcl

spring.datasource-secondary.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource-secondary.username=oraclesecondary
spring.datasource-secondary.password=oraclesecondary
spring.datasource-secondary.url=jdbc:oracle:thin:@//localhost:1521/orcl

Primary configuration:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "primaryEntityManagerFactory",
        transactionManagerRef = "primaryTransactionManager",
        basePackages = {"com.foo.primary.repository"})
public class PrimaryDataSourceConfiguration {

    @Primary
    @Bean(name = "primaryDataSourceProperties")
    @ConfigurationProperties("spring.datasource-primary")
    public DataSourceProperties primaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties("spring.datasource-primary.configuration")
    public DataSource primaryDataSource(@Qualifier("primaryDataSourceProperties") DataSourceProperties primaryDataSourceProperties) {
        return primaryDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            EntityManagerFactoryBuilder primaryEntityManagerFactoryBuilder, @Qualifier("primaryDataSource") DataSource primaryDataSource) {

        Map<String, String> primaryJpaProperties = new HashMap<>();
        primaryJpaProperties.put("hibernate.dialect", "org.hibernate.dialect.Oracle12cDialect");

        return primaryEntityManagerFactoryBuilder
                .dataSource(primaryDataSource)
                .packages("com.foo.primary.model")
                .persistenceUnit("primaryDataSource")
                .properties(primaryJpaProperties)
                .build();
    }

    @Primary
    @Bean(name = "primaryTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("primaryEntityManagerFactory") EntityManagerFactory primaryEntityManagerFactory) {

        return new JpaTransactionManager(primaryEntityManagerFactory);
    }

Second configuration:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "secondaryEntityManagerFactory",
        transactionManagerRef = "secondaryTransactionManager",
        basePackages = {"com.foo.secondary.repository"})
public class SecondaryDataSourceConfiguration {

    @Bean(name = "secondaryDataSourceProperties")
    @ConfigurationProperties("spring.datasource-secondary")
    public DataSourceProperties secondaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties("spring.datasource-secondary.configuration")
    public DataSource secondaryDataSource(@Qualifier("secondaryDataSourceProperties") DataSourceProperties secondaryDataSourceProperties) {
        return secondaryDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
            EntityManagerFactoryBuilder secondaryEntityManagerFactoryBuilder, @Qualifier("secondaryDataSource") DataSource secondaryDataSource) {

        Map<String, String> secondaryJpaProperties = new HashMap<>();
        secondaryJpaProperties.put("hibernate.dialect", "org.hibernate.dialect.Oracle12cDialect");

        return secondaryEntityManagerFactoryBuilder
                .dataSource(secondaryDataSource)
                .packages("com.foo.secondary.model")
                .persistenceUnit("secondaryDataSource")
                .properties(secondaryJpaProperties)
                .build();
    }

    @Bean(name = "secondaryTransactionManager")
    public PlatformTransactionManager secondaryTransactionManager(
            @Qualifier("secondaryEntityManagerFactory") EntityManagerFactory secondaryEntityManagerFactory) {

        return new JpaTransactionManager(secondaryEntityManagerFactory);
    }
}

I also tried org.hibernate.dialect.Oracle10gDialect, and set spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect in application.properties, but nothing changed.

How can I properly configure dialect for oracle?

Answer 1

Spring Data JDBC does not support oracle dialect. You need to define your dialect that implements JdbcDialectProvider.

public final class OracleDialect implements DialectResolver.JdbcDialectProvider {

    private static Dialect getDialect(Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        String name = metaData.getDatabaseProductName().toLowerCase(Locale.ROOT);

        if (name.contains("oracle")) {
            return AnsiDialect.INSTANCE;
        }

        return null;
    }

    @Override
    public Optional<Dialect> getDialect(JdbcOperations operations) {
        return Optional.ofNullable(operations.execute((ConnectionCallback<Dialect>) OracleDialect::getDialect));
    }
}

Add spring-boot-starter-data-jdbc dependency in your build.gradle or pom.xml.

Then, as mentioned in the blog, create spring.factories file in resources/META-INF, and paste the following command:

org.springframework.data.jdbc.repository.config.DialectResolver$JdbcDialectProvider=<your-package>.OracleDialect

Also, since both databases you use are the same (OracleDB), you do not need to set .properties() for entity manager. As @SternK mentioned, you can have spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect in your application.properties only.