| Subject | Re: [Firebird-Java] HikariCP Connection Pool won't connect to Firebird DataSource |
|---|---|
| Author | Mark Rotteveel |
| Post date | 2018-02-22T10:29:56Z |
Hi Bill,
This is the question as you originally asked it on Stack Overflow, I had
hoped that you would post the state of the problem after our exchange there.
To summarize from my answer and comments there:
The cause of the error is "Property url does not exist on target class
org.firebirdsql.ds.FBSimpleDataSource", and the dataSource.* properties
of HikariCP depend on the properties supported by the underlying data
source. FBSimpleDataSource has no url property, so it cannot be set that
way.
The proper settings given your example would be:
dataSource.database=server:/db/test123.GDB
dataSource.charSet=UTF-8
dataSource.roleName=USER
(I left out the prefix spring.jpa.properties.hibernate.hikari. as that
is specific to your code)
After changing that you ran into an exception
java.lang.IllegalStateException: required key
[spring.jpa.properties.hibernate.hikari.dataSource.url] not found
I then suggested to try and subclass FBSimpleDataSource, but that gave
problems as well (details would be appreciated).
After looking at the example project on
https://github.com/juliuskrah/java-crud/tree/spring-data-hibernate-jpa
you used as a template, I concluded that is requiring the existence of
spring.jpa.properties.hibernate.hikari.dataSource.url
Specifically the populating a properties object in
com.tutorial.Application.properties().
As I see it, you have the following options to solve this:
1. Change the code in Application.properties() to copy the properties I
suggested above, and remove the properties not supported by
FBSimpleDataSource
2. Find a way to get all properties with the
spring.jpa.properties.hibernate.hikari.dataSource. prefix dynamiicaly
(however, Spring doesn't make that easy to do)
3. Instead of configuring the data source used by HikariCP through
configuration, inject it in code
3a. Hardcode the properties to use (this makes it inflexible though)
3b. Use reflection to iterate over all available writable properties on
the FBSimpleDataSource and query the Environment object dynamically.
As an additional complication, the original project uses two ways to
initialize data sources, one in plain view in Application, and one
hidden away in a class called
org.hibernate.hikaricp.internal.HikariCPConnectionProvider that is
configured on the entity manager. So it actually uses two separate data
source, which I think might be an error in the original project.
If you only ever need to connect to Firebird, and don't need to
dynamically support other data sources or other properties, then
solution 1 is probably the simplest:
Just modify the properties() method to copy the properties needed for
Firebird, and remove those properties not supported (eg dataSource.url).
Otherwise, to get the project to use one and only one data source,
configured using option 3b, you can do the following:
In properties(), comment out the setting of properties:
- hibernate.hikari.dataSourceClassName
- hibernate.hikari.dataSource.url
- hibernate.hikari.dataSource.user
- hibernate.hikari.dataSource.password
- hibernate.connection.provider_class
Add a method to initialize the (in our case FBSimpleDataSource) data
source for Hikari using reflection:
private DataSource createHikariFactoryDataSource() throws Exception {
String dataSourceClassName = env.getRequiredProperty(
"spring.jpa.properties.hibernate.hikari.dataSourceClassName");
Class<?> dataSourceClass = Class.forName(dataSourceClassName);
DataSource dataSource = (DataSource)
dataSourceClass.getConstructor().newInstance();
final String propertyPrefix =
"spring.jpa.properties.hibernate.hikari.dataSource.";
BeanInfo beanInfo = Introspector.getBeanInfo(dataSourceClass);
for (PropertyDescriptor propertyDescriptor :
beanInfo.getPropertyDescriptors()) {
if (propertyDescriptor.getWriteMethod() == null) continue;
String propertyKey = propertyPrefix + propertyDescriptor.getName();
String value = env.getProperty(propertyKey);
if (value == null) continue;
log.debug("Found '{}' value '{}'", propertyKey, value);
// Warning: assuming all properties are strings; does not hold
in reality
propertyDescriptor.getWriteMethod().invoke(dataSource, value);
}
return dataSource;
}
Change the dataSource() method to:
@Bean(destroyMethod = "close")
public DataSource dataSource() {
log.debug("Starting datasource driver...");
HikariConfig config =
HikariConfigurationUtil.loadConfiguration(properties());
try {
config.setDataSource(createHikariFactoryDataSource());
} catch (Exception e) {
throw new RuntimeException("Could not initialize datasource", e);
}
return new HikariDataSource(config);
}
Change the entityManager() method to:
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManager(DataSource
dataSource) {
log.debug("Starting EntityManager...");
LocalContainerEntityManagerFactoryBean entityManager = new
LocalContainerEntityManagerFactoryBean();
entityManager.setPackagesToScan("com.tutorial.entity");
entityManager.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
entityManager.setJpaProperties(properties());
entityManager.setDataSource(dataSource);
entityManager.setPersistenceUnitName("com.juliuskrah.tutorial");
return entityManager;
}
To actually get the example application working, you'll also need to
modify dbChangelog.xml, by adding:
<changeSet id="0" author="julius" dbms="firebird">
<createSequence sequenceName="hibernate_sequence"/>
</changeSet>
after the other changeSet with id 0.
This isn't the prettiest solution, and I'm sure there are other, better
ways to solve this, but I'll leave that to you. Although I use Spring
regularly, I usually work with projects where we don't need a lot of
dynamic configuration, so we can usually hardcode the mapping between
configuration properties and the Spring wired objects.
As I mentioned on Stack Overflow, I'll look at adding a url property on
some of the Jaybird data sources to allow for configuration using the
full JDBC url. Given this problem is caused by the specific setup of
this project, and not a general problem with HikariCP, Spring or
Hibernate, I won't give it priority, and I'm not sure when I'll get
around to it.
Mark
This is the question as you originally asked it on Stack Overflow, I had
hoped that you would post the state of the problem after our exchange there.
To summarize from my answer and comments there:
The cause of the error is "Property url does not exist on target class
org.firebirdsql.ds.FBSimpleDataSource", and the dataSource.* properties
of HikariCP depend on the properties supported by the underlying data
source. FBSimpleDataSource has no url property, so it cannot be set that
way.
The proper settings given your example would be:
dataSource.database=server:/db/test123.GDB
dataSource.charSet=UTF-8
dataSource.roleName=USER
(I left out the prefix spring.jpa.properties.hibernate.hikari. as that
is specific to your code)
After changing that you ran into an exception
java.lang.IllegalStateException: required key
[spring.jpa.properties.hibernate.hikari.dataSource.url] not found
I then suggested to try and subclass FBSimpleDataSource, but that gave
problems as well (details would be appreciated).
After looking at the example project on
https://github.com/juliuskrah/java-crud/tree/spring-data-hibernate-jpa
you used as a template, I concluded that is requiring the existence of
spring.jpa.properties.hibernate.hikari.dataSource.url
Specifically the populating a properties object in
com.tutorial.Application.properties().
As I see it, you have the following options to solve this:
1. Change the code in Application.properties() to copy the properties I
suggested above, and remove the properties not supported by
FBSimpleDataSource
2. Find a way to get all properties with the
spring.jpa.properties.hibernate.hikari.dataSource. prefix dynamiicaly
(however, Spring doesn't make that easy to do)
3. Instead of configuring the data source used by HikariCP through
configuration, inject it in code
3a. Hardcode the properties to use (this makes it inflexible though)
3b. Use reflection to iterate over all available writable properties on
the FBSimpleDataSource and query the Environment object dynamically.
As an additional complication, the original project uses two ways to
initialize data sources, one in plain view in Application, and one
hidden away in a class called
org.hibernate.hikaricp.internal.HikariCPConnectionProvider that is
configured on the entity manager. So it actually uses two separate data
source, which I think might be an error in the original project.
If you only ever need to connect to Firebird, and don't need to
dynamically support other data sources or other properties, then
solution 1 is probably the simplest:
Just modify the properties() method to copy the properties needed for
Firebird, and remove those properties not supported (eg dataSource.url).
Otherwise, to get the project to use one and only one data source,
configured using option 3b, you can do the following:
In properties(), comment out the setting of properties:
- hibernate.hikari.dataSourceClassName
- hibernate.hikari.dataSource.url
- hibernate.hikari.dataSource.user
- hibernate.hikari.dataSource.password
- hibernate.connection.provider_class
Add a method to initialize the (in our case FBSimpleDataSource) data
source for Hikari using reflection:
private DataSource createHikariFactoryDataSource() throws Exception {
String dataSourceClassName = env.getRequiredProperty(
"spring.jpa.properties.hibernate.hikari.dataSourceClassName");
Class<?> dataSourceClass = Class.forName(dataSourceClassName);
DataSource dataSource = (DataSource)
dataSourceClass.getConstructor().newInstance();
final String propertyPrefix =
"spring.jpa.properties.hibernate.hikari.dataSource.";
BeanInfo beanInfo = Introspector.getBeanInfo(dataSourceClass);
for (PropertyDescriptor propertyDescriptor :
beanInfo.getPropertyDescriptors()) {
if (propertyDescriptor.getWriteMethod() == null) continue;
String propertyKey = propertyPrefix + propertyDescriptor.getName();
String value = env.getProperty(propertyKey);
if (value == null) continue;
log.debug("Found '{}' value '{}'", propertyKey, value);
// Warning: assuming all properties are strings; does not hold
in reality
propertyDescriptor.getWriteMethod().invoke(dataSource, value);
}
return dataSource;
}
Change the dataSource() method to:
@Bean(destroyMethod = "close")
public DataSource dataSource() {
log.debug("Starting datasource driver...");
HikariConfig config =
HikariConfigurationUtil.loadConfiguration(properties());
try {
config.setDataSource(createHikariFactoryDataSource());
} catch (Exception e) {
throw new RuntimeException("Could not initialize datasource", e);
}
return new HikariDataSource(config);
}
Change the entityManager() method to:
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManager(DataSource
dataSource) {
log.debug("Starting EntityManager...");
LocalContainerEntityManagerFactoryBean entityManager = new
LocalContainerEntityManagerFactoryBean();
entityManager.setPackagesToScan("com.tutorial.entity");
entityManager.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
entityManager.setJpaProperties(properties());
entityManager.setDataSource(dataSource);
entityManager.setPersistenceUnitName("com.juliuskrah.tutorial");
return entityManager;
}
To actually get the example application working, you'll also need to
modify dbChangelog.xml, by adding:
<changeSet id="0" author="julius" dbms="firebird">
<createSequence sequenceName="hibernate_sequence"/>
</changeSet>
after the other changeSet with id 0.
This isn't the prettiest solution, and I'm sure there are other, better
ways to solve this, but I'll leave that to you. Although I use Spring
regularly, I usually work with projects where we don't need a lot of
dynamic configuration, so we can usually hardcode the mapping between
configuration properties and the Spring wired objects.
As I mentioned on Stack Overflow, I'll look at adding a url property on
some of the Jaybird data sources to allow for configuration using the
full JDBC url. Given this problem is caused by the specific setup of
this project, and not a general problem with HikariCP, Spring or
Hibernate, I won't give it priority, and I'm not sure when I'll get
around to it.
Mark
On 21-2-2018 20:56, blyons3@... [Firebird-Java] wrote:
> I'm trying to build a successful connection to a Firebird DB running on
> Windows using Spring Data JPA, Hibernate anda HikariCP Connection Pool.
> I'm using the jaybird 3.0.1 jar. 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(EntityManagerFactoryBuild
> erImpl.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(AbstractAut
> owireCapableBeanFactory.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&roleName=USER;
>
> spring.jpa.pro perties.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