Spring Data JDBC Firebird dialect not recognized
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
fromJdbcOperations
, typically by inspectingConnection
. You can let Spring auto-discover yourDialect
by registering a class that implementsorg.springframework.data.jdbc.repository.config.DialectResolver$JdbcDialectProvider
throughMETA-INF/spring.factories
.DialectResolver
discovers dialect provider implementations from the class path using Spring’sSpringFactoriesLoader
.
To be able to use Firebird, you will need to define three things:
- A dialect
- A dialect resolver
- 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:
- Implement the Dialect
- 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.
ndwd
Updated on June 04, 2022Comments
-
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 about 4 yearsThanks for your help! I've read the description in the manual, but struggled to get it right.
-
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 almost 4 years@anjanb The cause and solution is the same: provide your own dialect resolver.
-
malifa almost 4 yearsHi @MarkRotteveel, Thank you. I've chosen to rollback to boot 2.2.7.RELEASE.
-
Prabhaker A almost 4 yearsThank you., if you can help more by providing some reasoning behind it.,
-
Estefany Velez almost 4 yearsThis worked for me. I am connecting to oracle DB. Can someone explain why?
-
George over 3 yearsI guess the hikari bits on spring-boot-starter-data-jdbc lacks dialects implementations for some databases...
-
George over 3 yearsSadly it seems hikari is broken for less popular databases
-
Haroldo_OK over 2 yearsThe 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.