Proper Setup of jaybird/Firebird DataSource with HikariCP

Question

I'm trying to build a successful connection to a Firebird DB running on Windows using Spring Data JPA, Hibernate and a HikariCP Connection Pool. I have gotten everything to run correctly on other JDBC compliant DBs (H2, Derby, MySQL, etc.) but when I switch my driver .jar over to Firebird I get:

Exception in thread "main" org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in com.application.Application: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)
    at org.springframework.context.annotation.AnnotationConfigApplicationContext.<init>(AnnotationConfigApplicationContext.java:84)
    at com.application.Application.main(Application.java:43)
Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:267)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:231)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:240)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:858)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:885)
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:370)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:359)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624)
    ... 11 more
Caused by: org.hibernate.HibernateException: java.lang.RuntimeException: Property url does not exist on target class org.firebirdsql.ds.FBSimpleDataSource

Here's an extract of my Spring application.properties:

spring.jpa.properties.javax.persistence.provider=org.hibernate.jpa.HibernatePersistenceProvider

spring.jpa.properties.hibernate.hikari.dataSourceClassName=org.firebirdsql.ds.FBSimpleDataSource
spring.jpa.properties.hibernate.hikari.dataSource.url=jdbc:firebirdsql:server:/db/test123.GDB?charSet=UTF-8&amp;roleName=USER;
spring.jpa.properties.hibernate.hikari.dataSource.user=SYSDBA
spring.jpa.properties.hibernate.hikari.dataSource.password=password
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.FirebirdDialect

spring.jpa.properties.hibernate.hikari.minimumIdle=5
spring.jpa.properties.hibernate.hikari.maximumPoolSize=10
spring.jpa.properties.hibernate.hikari.idleTimeout=30000
spring.jpa.properties.hibernate.connection.handling_mode=delayed_acquisition_and_hold
spring.jpa.properties.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider

I have tested using a few of the other Firebird classes that appear to implement javax.sql.DataSource for the spring.jpa.properties.hibernate.hikari.dataSourceClassName property but all give variations of the cannot connect Exception.

Mark Rotteveel · Answer 1 · 2023-02-09T08:48:08.433

0

NOTE: This answer concerns a non-standard configuration in Spring Boot; the normal configuration should be done through the spring.datasource.* properties (see Working with SQL Databases in the Spring Boot documentation).

The HikariCP configuration properties under dataSource must match the properties available on the target data source, and the class org.firebirdsql.ds.FBSimpleDataSource has no property url (as reported by the error "Property url does not exist on target class org.firebirdsql.ds.FBSimpleDataSource")

See the org.firebirdsql.ds.FBSimpleDataSource javadoc for a list of available properties. Specifically, you'll need to configure database, charSet and roleName.

spring.jpa.properties.hibernate.hikari.dataSource.database=server:/db/test123.GDB
spring.jpa.properties.hibernate.hikari.dataSource.charSet=UTF-8
spring.jpa.properties.hibernate.hikari.dataSource.roleName=USER

The connection properties must be set explicitly on the data source: you can't provide those properties in the database property (which is in essence the JDBC URL without the jdbc:firebirdsql: prefix and without properties).

I have created improvement ticket JDBC-521 to add a url (or maybe jdbcUrl) property that accepts a full JDBC URL in a future version.

^{For those interested, see the follow-up discussion on HikariCP Connection Pool won't connect to Firebird DataSource}

edited Feb 09 '23 at 08:48

answered Feb 21 '18 at 17:42

Mark Rotteveel

100,966
191
140
197

Hi Mark, `code` Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean]: Factory method 'entityManager' threw exception; nested exception is java.lang.IllegalStateException: required key [spring.jpa.properties.hibernate.hikari.dataSource.url] not found – blyons Feb 21 '18 at 17:57
If I put the url key back in to satisfy Hikari, then I'm back to: org.hibernate.HibernateException: java.lang.RuntimeException: Property url does not exist on target class org.firebirdsql.ds.FBSimpleDataSource – blyons Feb 21 '18 at 17:58
This seems to be less straightforward than I expected. I'm not sure why JPA would require presence of this specific property, as the JDBC specification does not define this property at all (JDBC only requires existence of a `description` property). – Mark Rotteveel Feb 21 '18 at 18:03
Could you update your question to include exactly what you're using (Spring or Spring boot, versions), then I will try to reproduce this, and see if I can fix it with a little more priority. As a workaround, you could try to extend `FBSimpleDataSource`, and add a `getUrl`/`setUrl` property that does nothing. – Mark Rotteveel Feb 21 '18 at 18:07
It was simple enough. My starting point is a very simple Spring JPA/Hibernate/HikariCP demo connecting to a local H2 database. The complete source can be found here: https://github.com/juliuskrah/java-crud/tree/spring-data-hibernate-jpa I just switched it to a gradle project, removed H2 drivers, switched to jaybird and switched url, driver and connect info to an entity defined in our DB. I will try the extends to see if that helps. – blyons Feb 21 '18 at 18:17
I can provide complete project if desired. It's very small. – blyons Feb 21 '18 at 18:23
@blyons Scanning through that project, the problem could also be one of that example project itself, in class `com.tutorial.Application`, it explicitly tries to copy property values using `getRequiredProperty`, changing how that properties object is populated may help. – Mark Rotteveel Feb 21 '18 at 18:24
I'm not sure what mods to make there. I did try the simple extension on FBSimpleDataSource. I'm getting complaints now on a nonexistent method "" (Nice right - a reference to a method that can't be found). Do I have to new an instance of the FBSimpleDataSource in my class and then write delegate methods in my class to call each of the methods in FBSimpleDataSource? – blyons Feb 21 '18 at 18:36
PS - Boss says thanks for the quick help. My company is a Firebird Platinum sponsor. – blyons Feb 21 '18 at 18:46
I think we may have reached a point where it would be better to take this problem to the Firebird-Java mailing list, as that is probably a better venue for troubleshooting than comments here on SO. Could you post a recap there, and maybe provide some more information on the problem with the subclass. – Mark Rotteveel Feb 21 '18 at 19:22
1

Spring basically prefers JDBC URL-based configuration rather than DataSource-based. Try omitting the ``dataSourceClassName`` property and using ``jdbcUrl`` property instead. In that case, you *may* have to specify the ``driverClassName``, but try without it first to see if the DriverManager can resolve the driver based on the URL. – brettw Feb 22 '18 at 15:34
Note, even with URL-based configuration, you can typically still use ``dataSource`` properties. These are translated into a ``Properties`` set that is passed to the driver. ``dataSource.charSet=UTF-8`` will result in a ``charSet`` property with value ``UTF-8``. See here ["Illustration 2.3"](https://firebirdsql.org/file/Jaybird_2_1_JDBC_driver_manual.pdf) for equivalency. – brettw Feb 22 '18 at 15:43
How can i activate PreparedStatement caching in each connection with Jaybird 2.2.15, created by Hikari? For example, i have a pool with 5 connections and want to each of them have ability to support prepared statement caching. – lanmaster Nov 26 '20 at 07:40
1

@lanmaster That would have been better to ask as a new question than in a comment on an unrelated question. The answer however is, you can't: HikariCP doesn't provide statement caching, and neither does Jaybird itself. – Mark Rotteveel Nov 26 '20 at 09:08

Proper Setup of jaybird/Firebird DataSource with HikariCP

1 Answers1