Spring Data JDBC Firebird dialect not recognized

10,420

Solution 1

The Spring Data JDBC library itself does not contain a Firebird dialect out of the box, so you need to provide one yourself. How to do this is documented in Annotation-based Configuration of the Spring Data JDBC documentation:

Dialects are resolved by JdbcDialectResolver from JdbcOperations, typically by inspecting Connection. You can let Spring auto-discover your Dialect by registering a class that implements org.springframework.data.jdbc.repository.config.DialectResolver$JdbcDialectProvider through META-INF/spring.factories. DialectResolver discovers dialect provider implementations from the class path using Spring’s SpringFactoriesLoader.

To be able to use Firebird, you will need to define three things:

  1. A dialect
  2. A dialect resolver
  3. A config file for Spring to locate the dialect resolver

The dialect can be something like (note, this dialect assumes Firebird 3 or higher):

package spring.firebird;

import org.springframework.data.relational.core.dialect.AnsiDialect;
import org.springframework.data.relational.core.dialect.ArrayColumns;
import org.springframework.data.relational.core.dialect.LockClause;
import org.springframework.data.relational.core.sql.LockOptions;

public class FirebirdDialect extends AnsiDialect {

    public static final FirebirdDialect INSTANCE = new FirebirdDialect();

    @Override
    public LockClause lock() {
        return LOCK_CLAUSE;
    }

    @Override
    public ArrayColumns getArraySupport() {
        return ArrayColumns.Unsupported.INSTANCE;
    }

    private static final LockClause LOCK_CLAUSE = new LockClause() {

        @Override
        public String getLock(LockOptions lockOptions) {
            return "WITH LOCK";
        }

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

A dialect resolver to return the dialect if the connection is to a Firebird database.

package spring.firebird;

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

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

public class FirebirdDialectResolver implements DialectResolver.JdbcDialectProvider {

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

    private static Dialect getDialect(Connection connection) throws SQLException  {
        DatabaseMetaData metaData = connection.getMetaData();
        String name = metaData.getDatabaseProductName().toLowerCase(Locale.ROOT);
        if (name.contains("firebird")) {
            return FirebirdDialect.INSTANCE;
        }
        return null;
    }
}

Finally, you need to define a resource in META-INF with the name spring.factories, this file must contain the line:

org.springframework.data.jdbc.repository.config.DialectResolver$JdbcDialectProvider=spring.firebird.FirebirdDialectResolver

This allows Spring Data JDBC to discover the dialect resolver and use it.

Solution 2

I Solve the problem with my colleague,my solution is (remove the dependency spring-boot-stater-data-jdbc keep the dependency spring-boot-starter-jdbc)

 <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
   <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jdbc</artifactId>
   </dependency>

Solution 3

Working solution for MariaDB / MySQL with Spring Boot 2.3.1.RELEASE

create directory/file if not exists: resources/META-INF/spring.factories

org.springframework.data.jdbc.repository.config.DialectResolver$JdbcDialectProvider=com.yourapp.config.MariaDbDialectResolver

create class:

package com.yourapp.config;

import org.springframework.data.jdbc.repository.config.DialectResolver;
import org.springframework.data.relational.core.dialect.Dialect;
import org.springframework.data.relational.core.dialect.MySqlDialect;
import org.springframework.jdbc.core.JdbcOperations;

import java.util.Optional;

public class MariaDbDialectResolver implements DialectResolver.JdbcDialectProvider {
    @Override
    public Optional<Dialect> getDialect(JdbcOperations jdbcOperations) {
        return Optional.of(MySqlDialect.INSTANCE);
    }
}

Solution 4

I found myself facing the same issue after upgrading to SB 2.3.0. Mark's excellent answer (which btw has found itself to Spring Data migration docs), helped me solve it, but I found that adding the extra configuration to META-INF less than ideal.

My solution, extending from the original answer:

  1. Implement the Dialect
  2. Provide a custom JDBC Configuration and override jdbcDialect():
@Configuration
public class SpringDataJdbcConfiguration extends AbstractJdbcConfiguration {

    @Override
    public Dialect jdbcDialect(NamedParameterJdbcOperations operations) {
        return operations.getJdbcOperations().execute((ConnectionCallback<Dialect>)
                connection -> isInformix(connection) ? InformixDialect.INSTANCE : super.jdbcDialect(operations));
    }

    private boolean isInformix(Connection connection) throws SQLException {
        return connection.getMetaData().getDatabaseProductName().toUpperCase().contains("INFORMIX");
    }
}

Solution 5

I'm facing similar issues, but using Oracle DB.

The change I did in my project was simpler (I don't know if it will solve your problem with Spring Boot and Firebase)

Just replace 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>
    <!--...-->

Good luck for you.

Share:
10,420
ndwd
Author by

ndwd

Updated on June 04, 2022

Comments

  • ndwd
    ndwd almost 2 years

    I'm trying to connect to Firebird database using Spring Data JDBC and Spring Boot. I've created a simple app using Spring Tools. Spring Data JDBC doesn't recognize the dialect. I believe the problem is that the DialectResolver doesn't support Firebird.

    private static Dialect getDialect(Connection connection) throws SQLException {
    
        DatabaseMetaData metaData = connection.getMetaData();
    
        String name = metaData.getDatabaseProductName().toLowerCase(Locale.ENGLISH);
    
        if (name.contains("hsql")) {
            return HsqlDbDialect.INSTANCE;
        }
        if (name.contains("h2")) {
            return H2Dialect.INSTANCE;
        }
        if (name.contains("mysql")) { // catches also mariadb
            return new MySqlDialect(getIdentifierProcessing(metaData));
        }
        if (name.contains("postgresql")) {
            return PostgresDialect.INSTANCE;
        }
        if (name.contains("microsoft")) {
            return SqlServerDialect.INSTANCE;
        }
        if (name.contains("db2")) {
            return Db2Dialect.INSTANCE;
        }
        return null;
    }
    

    I'm new to Java and Spring Boot. Can someone help me to solve this issue?

    org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'jdbcConverter' defined in class path resource [com/example/relationaldataaccess/ApplicationConfig.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 [com/example/relationaldataaccess/ApplicationConfig.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@74fef3f7. 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.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:143) ~[spring-boot-2.3.0.RELEASE.jar:2.3.0.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 com.example.relationaldataaccess.Relationaldataaccess1Application.main(Relationaldataaccess1Application.java:10) [classes/:na]
    Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jdbcDialect' defined in class path resource [com/example/relationaldataaccess/ApplicationConfig.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@74fef3f7. 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]
        ... 20 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@74fef3f7. 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]
        ... 34 common frames omitted
    Caused by: org.springframework.data.jdbc.repository.config.DialectResolver$NoDialectException: Cannot determine a dialect for org.springframework.jdbc.core.JdbcTemplate@74fef3f7. 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(Unknown Source) ~[na:1.8.0_66]
        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 com.example.relationaldataaccess.ApplicationConfig$$EnhancerBySpringCGLIB$$6ef57e7e.CGLIB$jdbcDialect$2(<generated>) ~[classes/:na]
        at com.example.relationaldataaccess.ApplicationConfig$$EnhancerBySpringCGLIB$$6ef57e7e$$FastClassBySpringCGLIB$$f93edce9.invoke(<generated>) ~[classes/:na]
        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 com.example.relationaldataaccess.ApplicationConfig$$EnhancerBySpringCGLIB$$6ef57e7e.jdbcDialect(<generated>) ~[classes/:na]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_66]
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_66]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_66]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_66]
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
        ... 35 common frames omitted
    

    application.properties

    spring.datasource.url=jdbc:firebirdsql://localhost:3051/C:\\example.fbd?sql_dialect=3
    spring.datasource.driverClassName=org.firebirdsql.jdbc.FBDriver
    spring.datasource.username=SYSDBA
    spring.datasource.password=password
    

    app.java

    package com.example.relationaldataaccess;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class Relationaldataaccess1Application {
    
        public static void main(String[] args) {
            SpringApplication.run(Relationaldataaccess1Application.class, args);
        }
    
    }
    

    pom.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.3.0.RELEASE</version>
            <relativePath/> <!-- lookup parent from repository -->
        </parent>
        <groupId>com.example</groupId>
        <artifactId>relationaldataaccess-1</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>relationaldataaccess-1</name>
        <description>Demo project for Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>
    
  • ndwd
    ndwd about 4 years
    Thanks for your help! I've read the description in the manual, but struggled to get it right.
  • malifa
    malifa almost 4 years
    @Mark Rotteveel : I posted a related question at stackoverflow.com/questions/61999807/…. Would you care to take a look ?
  • Mark Rotteveel
    Mark Rotteveel almost 4 years
    @anjanb The cause and solution is the same: provide your own dialect resolver.
  • malifa
    malifa almost 4 years
    Hi @MarkRotteveel, Thank you. I've chosen to rollback to boot 2.2.7.RELEASE.
  • Prabhaker A
    Prabhaker A almost 4 years
    Thank you., if you can help more by providing some reasoning behind it.,
  • Estefany Velez
    Estefany Velez almost 4 years
    This worked for me. I am connecting to oracle DB. Can someone explain why?
  • George
    George over 3 years
    I guess the hikari bits on spring-boot-starter-data-jdbc lacks dialects implementations for some databases...
  • George
    George over 3 years
    Sadly it seems hikari is broken for less popular databases
  • Haroldo_OK
    Haroldo_OK over 2 years
    The side effect is that you won't be able to use Spring Data, but depending on the project, that may or may not be a problem.