New Error in spring boot 2.3.0.RELEASE : UnsatisfiedDependencyException for Oracle 12.2.0.1 jdbcdriver but not with mysql jdbcdriver

java spring oracle jdbc spring-data-jdbc

11,794

Solution 1

What is happening with the oracle jdbc driver in spring boot 2.3.0.RELEASE ?

The JDBC Driver is doing just fine.

As you already learned in the comments the problem is that

a) Spring Data JDBC now requires a Dialect for each database

b) Spring Data JDBC does not ship with a Dialect for Oracle.

The fix:

As described in Spring Data JDBC Firebird dialect not recognized and in https://spring.io/blog/2020/05/20/migrating-to-spring-data-jdbc-2-0 you need to provide your own Dialect implementation right now.

Something like this should work:

class MyOracleDialect extends AnsiDialect {

    private static final LimitClause LIMIT_CLAUSE = new LimitClause() {

        @Override
        public String getLimit(long limit) {
            return String.format("FETCH NEXT %d ROWS ONLY", limit);
        }

        @Override
        public String getOffset(long offset) {
            return String.format("OFFSET %d ROWS", offset);
        }

        @Override
        public String getLimitOffset(long limit, long offset) {
            return String.format("OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", offset, limit);
        }

        @Override
        public Position getClausePosition() {
            return Position.AFTER_ORDER_BY;
        }
    };

    @Override
    public LimitClause limit() {
        return LIMIT_CLAUSE;
    }

}

You then need to make the Dialect available via a DialectProvider as described in the referenced Stackoverflow question:

Some background:

So far the Spring Data team does not run their integration tests with an Oracle database. This is because for quite some time it was unclear how to do that in a legal way since all the code including the build infrastructure was open source and Oracle required accepting of all kinds of stuff to use a database instance or their driver.

By now Docker images are available for testing and the JDBC driver is available from Maven Central. It is still a little tricky since the current options for docker images that I am aware of let you choose between an image size of many GBs or a start up time of about 15 minutes.

Anyway there is a community member working on the issue: https://jira.spring.io/browse/DATAJDBC-256

I'm therefore confident that we will be able to fully support Oracle in the near future.

Solution 2

This Dialect resolver fixed my issue with Oracle.

 @Configuration
   public class SpringDataJdbcConfiguration extends AbstractJdbcConfiguration {
    
        @Override
        public Dialect jdbcDialect(NamedParameterJdbcOperations operations) {
            return AnsiDialect.INSTANCE;
        }
    }

Solution 3

Currently spring data jdbc doesn't support oracle dialect You need to define your own dialect resolver Add following class for Oracle Dialect which implements JdbcDialectProvider.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.Locale;
import java.util.Optional;

import org.springframework.data.jdbc.repository.config.DialectResolver.JdbcDialectProvider;
import org.springframework.data.relational.core.dialect.AnsiDialect;
import org.springframework.data.relational.core.dialect.Dialect;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcOperations;

public class OracleDialectResolver implements JdbcDialectProvider {

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

    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;
    }

}

Register your provider by putting a file spring.factories in the META-INF folder of your class path and add the following line.

org.springframework.data.jdbc.repository.config.DialectResolver$JdbcDialectProvider=com.mypackage.jdbctemplate.dialect.OracleDialectResolver //fuly qualified class name of your dialect resolver class

Solution 4

I had exactly the same problem last week.

The change I did in my project was simpler: I just replaced HikariCP dependency with tomcat-jdbc in pom.xml:

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
    <!-- HikariCP doesn't deals with jdbcDialect properly -->
    <exclusions>
        <exclusion>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </exclusion>
    </exclusions>
    <!-- Dependency removed -->
    </dependency>
    <!-- Dependency to tomcat-jdbc to deal with jdbcDialect -->
    <dependency>
        <groupId>org.apache.tomcat</groupId>
        <artifactId>tomcat-jdbc</artifactId>
    </dependency>
    <!--...-->

If I undo this, the exception you posted in title happens again.

Good luck for you.

View more solutions

11,794

Author by

malifa

sdfsdfsdf sdf sdfsdfsdsdsdfsdf sdfdsfsdsdfssdfsd

Updated on June 25, 2022

Comments

malifa almost 2 years
Created a fresh boot project with just jdbc and Oracle jdbc as dependencies
```
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>

    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>12.2.0.1</version>
    </dependency>
```
I configure the datasource in the application.properties file and run this simple application and it bombs with below stacktrace.

2020-05-25 15:07:16.769 WARN 11840 --- [ main] s.c.a.AnnotationConfigApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'jdbcConverter' defined in class path resource [org/springframework/boot/autoconfigure/data/jdbc/JdbcRepositoriesAutoConfiguration$SpringBootJdbcConfiguration.class]: Unsatisfied dependency expressed through method 'jdbcConverter' parameter 4; nested exception is org.springframework.beans.factory.BeanCreationException: 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@7fe083b1. Please provide a Dialect. 2020-05-25 15:07:16.769 INFO 11840 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated... 2020-05-25 15:07:17.023 INFO 11840 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed. 2020-05-25 15:07:17.028 INFO 11840 --- [ main] ConditionEvaluationReportLoggingListener :

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled. 2020-05-25 15:07:17.035 ERROR 11840 --- [ main] o.s.boot.SpringApplication
: Application run failed

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'jdbcConverter' defined in class path resource [org/springframework/boot/autoconfigure/data/jdbc/JdbcRepositoriesAutoConfiguration$SpringBootJdbcConfiguration.class]: Unsatisfied dependency expressed through method 'jdbcConverter' parameter 4; nested exception is org.springframework.beans.factory.BeanCreationException: 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@7fe083b1. Please provide a Dialect. at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:798) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:539) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1338) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:226) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:895) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:878) ~[spring-context-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) ~[spring-context-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758) [spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750) [spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE] at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) [spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237) [spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE] at net.sincera.dbmigration.liquibasedemo.LiquibasedemoApplication.main(LiquibasedemoApplication.java:21) [classes/:na] Caused by: org.springframework.beans.factory.BeanCreationException: 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@7fe083b1. Please provide a Dialect. at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:656) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:636) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1338) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:226) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1306) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1226) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:885) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:789) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] ... 19 common frames omitted Caused by: 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@7fe083b1. Please provide a Dialect. at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:651) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] ... 33 common frames omitted Caused by: org.springframework.data.jdbc.repository.config.DialectResolver$NoDialectException: Cannot determine a dialect for org.springframework.jdbc.core.JdbcTemplate@7fe083b1. Please provide a Dialect. at org.springframework.data.jdbc.repository.config.DialectResolver.lambda$getDialect$2(DialectResolver.java:76) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE] at java.util.Optional.orElseThrow(Optional.java:290) ~[na:1.8.0_241] at org.springframework.data.jdbc.repository.config.DialectResolver.getDialect(DialectResolver.java:75) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE] at org.springframework.data.jdbc.repository.config.AbstractJdbcConfiguration.jdbcDialect(AbstractJdbcConfiguration.java:144) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE] at org.springframework.boot.autoconfigure.data.jdbc.JdbcRepositoriesAutoConfiguration$SpringBootJdbcConfiguration$$EnhancerBySpringCGLIB$$5730cfab.CGLIB$jdbcDialect$1() ~[spring-boot-autoconfigure-2.3.0.RELEASE.jar:2.3.0.RELEASE] at org.springframework.boot.autoconfigure.data.jdbc.JdbcRepositoriesAutoConfiguration$SpringBootJdbcConfiguration$$EnhancerBySpringCGLIB$$5730cfab$$FastClassBySpringCGLIB$$5eb1865b.invoke() ~[spring-boot-autoconfigure-2.3.0.RELEASE.jar:2.3.0.RELEASE] at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:244) ~[spring-core-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:331) ~[spring-context-5.2.6.RELEASE.jar:5.2.6.RELEASE] at org.springframework.boot.autoconfigure.data.jdbc.JdbcRepositoriesAutoConfiguration$SpringBootJdbcConfiguration$$EnhancerBySpringCGLIB$$5730cfab.jdbcDialect() ~[spring-boot-autoconfigure-2.3.0.RELEASE.jar:2.3.0.RELEASE] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_241] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_241] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_241] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_241] at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE] ... 34 common frames omitted

If I run the above code using spring boot 2.2.7.RELEASE, it works just fine -- No exceptions!!

After 2 days, I thought of configuring a mysql datasource on localhost. The same program runs just fine (with spring boot 2.3.0.RELEASE) but this time it used the mysql datasource.
```
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
```
What is happening with the oracle jdbc driver in spring boot 2.3.0.RELEASE ?

What is mysql jdbc driver doing correctly in spring boot 2.3.0.RELEASE ?

Thank you.
- Mark Rotteveel about 4 years
  
  The problem is that Spring Data JDBC does not provide a dialect for Oracle out of the box. See Spring Data JDBC Firebird dialect not recognized for a solution (though you'll need to modify it to work for Oracle).
- malifa about 4 years
  
  Hi @MarkRotteveel : thanks very much. I pinged you on that post. How can spring data jdbc 2.0 provide a dialect for mysql but not provide for oracle -- did suddenly spring come to recognize that they don't want to support the very popular oracle out of the box ? OR is that an oversight ?
- Mark Rotteveel about 4 years
  
  I don't know, you should ask the maintainers for Spring Data JDBC that question.
- malifa about 4 years
  
  Hi @MarkRotteveel, thank you. I've tweeted them. Hoping to hear from them after the holiday.
Mark Rotteveel about 4 years

As far as I know, Oracle supports the SQL standard OFFSET/FETCH syntax (see this), so it should be possible to use AnsiDialect without replacing the limit clause.
Mark Rotteveel about 4 years

This doesn't use Spring Data JDBC.
malifa about 4 years

Thanks but I need to use spring data jdbc stuff like JdbcTemplate, etc. Not plain jdbc.
malifa about 4 years

@JensSchauder, thank you for the detailed explanation. But my expectation of spring and more so of spring boot is that things work out of the box for most common stuff. And a lot of enterprise applications run on Oracle db even today. Come on, Oracle, please work with spring to make Oracle DB and Java developers' life easier. We know that you have developers's good in your heart!
malifa about 4 years

Thanks @user3634250.. Given the answer from above, how does your solution work for a spring data jdbc application ?
malifa almost 4 years

Thanks @Nirmala. But we're not using Oracle 19.x -- we're using Oracle 12 and 11 -- not sure if using a later version of ojdbc.jar will work.
francois Bernard-Bouissieres almost 4 years

Your are using 2.2.6.RELEASE and the thread is related to 2.3.x.RELEASE ...
malifa almost 4 years

@JensSchauder : I see that the issue is in RESOLVED state as of May. What's preventing Oracle from being used in the integration tests ?
Jens Schauder almost 4 years

@anjanb The issue was resolved in July. And the main problems we had are described in the "Some background" section of the answer.
malifa almost 4 years

thank you. Would you know why ? The answer above mentions that it's because Oracle DB is currently not easily made available for unit testing.
Doogle over 3 years

Simple yet elegant not sure why this is not fixed yet