Vertica Integration with Hibernate: Connection Guide

shwetanks

I am following this document but not able to find VerticaDialect.jar on the Micro Focus Vertica marketplace. Please provide this jar for vertica-jdbc-9.1.0-0 and vertica-jdbc-7.2.3-0.jar

or Can we use org.hibernate.dialect.PostgreSQLDialect to connect vertica, Is it feasible?

Jim_Knicely

Hi,

Looks like "org.hibernate.dialect.PostgreSQLDialect" will work:

See:
https://stackoverflow.com/questions/7571890/does-eclipse-link-work-together-with-vertica

Also, found this... Patch for Vertica dialect to support Hibernate 5...

See:
https://github.com/koteika/vertica-hibernate5-dialect

GianPendola

Seems like it's broken for Hibernate 6. I'm changing to PostgreSALDialect. But still have a bunch of issues with Spring Security migration , so haven't tested it thoroughly.

ajabrol

Hi,

I am working on this issue and will check vertica dialect with Hibernate 6. I will update you accordingly.

ajabrol

Hi,

Please share me more details about the issues which you have encountered with Hibernate 6 and also provide me the exact version of hibernate which you were use.

Please let me know if you have any queries.

Regards,
Ajay Abrol

GianPendola

I tried PostgreSQL dialect but it didn't work either. I will post the errors with verticadialect.jar

GianPendola

My application is working fine with Spring Boot 2.7, but when using Spring Boot 3.0 (after changing all javax.* to jakarta.*), that uses Hibernate 6, I get the following when I start the application:

2023-05-20T09:36:54.996-05:00[0;39m [31mERROR[0;39m [35m27740[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.boot.SpringApplication [0;39m [2m:[0;39m Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Unable to resolve name [org.hibernate.dialect.VerticaDialect] as strategy [org.hibernate.dialect.Dialect]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1770) ~[spring-beans-6.0.9.jar:6.0.9]
at
.
.
.
at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-3.1.0.jar:3.1.0]
Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Unable to resolve name [org.hibernate.dialect.VerticaDialect] as strategy [org.hibernate.dialect.Dialect]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:277) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
.
.
.

Caused by: org.hibernate.boot.registry.selector.spi.StrategySelectionException: Unable to resolve name [org.hibernate.dialect.VerticaDialect] as strategy [org.hibernate.dialect.Dialect]
at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java:154) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
.
.
.
... 36 common frames omitted
Caused by: org.hibernate.boot.registry.classloading.spi.ClassLoadingException: Unable to load class [org.hibernate.dialect.VerticaDialect]
at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.classForName(ClassLoaderServiceImpl.java:126) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java:150) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
... 44 common frames omitted
Caused by: java.lang.NoClassDefFoundError: org/hibernate/dialect/function/SQLFunction
at java.base/java.lang.Class.forName0(Native Method) ~[na:na]
at java.base/java.lang.Class.forName(Class.java:467) ~[na:na]
at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.classForName(ClassLoaderServiceImpl.java:120) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
... 45 common frames omitted
Caused by: java.lang.ClassNotFoundException: org.hibernate.dialect.function.SQLFunction
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641) ~[na:na]
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188) ~[na:na]
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520) ~[na:na]
... 48 common frames omitted

---

Dependencies for Vertica on Maven are:

    <dependency>
        <groupId>com.vertica</groupId>
        <artifactId>vjdbc12</artifactId>
        <version>12.0.3</version>
        <scope>system</scope>
        <systemPath>/Library/java/Extensions/vertica-jdbc-12.0.3-0.jar</systemPath>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>vDialect</artifactId>
        <version>9.0.1</version>
        <scope>system</scope>
        <systemPath>/Library/java/Extensions/VerticaDialect.jar</systemPath>
    </dependency>

---

The same application, but on Spring Boot 2.7 (Hibernate 5), starts properly:

09:43:07.978 [main] INFO com.tecnidatos.nomina.NominaApplication - I0001 INFO Iniciando la aplicación de Nomina - NominaApplication
09:43:08.081 [Thread-0] DEBUG org.springframework.boot.devtools.restart.classloader.RestartClassLoader - Created RestartClassLoader org.springframework.boot.devtools.restart.classloader.RestartClassLoader@59dde20d
09:43:08.082 [restartedMain] INFO com.tecnidatos.nomina.NominaApplication - I0001 INFO Iniciando la aplicación de Nomina - NominaApplication

=========||==============|___/=////
[32m :: Spring Boot :: [39m [2m (v2.7.12-SNAPSHOT)[0;39m

[2m2023-05-20 09:43:08.297[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.e.DevToolsPropertyDefaultsPostProcessor[0;39m [2m:[0;39m Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
[2m2023-05-20 09:43:08.298[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.e.DevToolsPropertyDefaultsPostProcessor[0;39m [2m:[0;39m For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
[2m2023-05-20 09:43:08.794[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Bootstrapping Spring Data JPA repositories in DEFAULT mode.
[2m2023-05-20 09:43:08.819[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Finished Spring Data repository scanning in 20 ms. Found 1 JPA repository interfaces.
[2m2023-05-20 09:43:09.153[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.b.w.embedded.tomcat.TomcatWebServer [0;39m [2m:[0;39m Tomcat initialized with port(s): 9999 (https)
[2m2023-05-20 09:43:09.220[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mw.s.c.ServletWebServerApplicationContext[0;39m [2m:[0;39m Root WebApplicationContext: initialization completed in 922 ms
[2m2023-05-20 09:43:09.261[0;39m [32mDEBUG[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mcom.zaxxer.hikari.HikariConfig [0;39m [2m:[0;39m Driver class com.vertica.jdbc.Driver found in Thread context class loader TomcatEmbeddedWebappClassLoader
context: ROOT
delegate: true
----------> Parent Classloader:
org.springframework.boot.devtools.restart.classloader.RestartClassLoader@59dde20d

.
.
.
[36mcom.zaxxer.hikari.pool.HikariPool [0;39m [2m:[0;39m HikariPool-1 - Added connection com.vertica.jdbc.VerticaJdbc42ConnectionImpl@cf40384
[2m2023-05-20 09:43:09.552[0;39m [32mDEBUG[0;39m [35m27860[0;39m [2m---[0;39m [2m[onnection adder][0;39m [36mcom.zaxxer.hikari.pool.HikariPool [0;39m [2m:[0;39m HikariPool-1 - After adding stats (total=10, active=0, idle=10, waiting=0)
[2m2023-05-20 09:43:09.926[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mj.LocalContainerEntityManagerFactoryBean[0;39m [2m:[0;39m Initialized JPA EntityManagerFactory for persistence unit 'default'
[2m2023-05-20 09:43:10.116[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m
.
.
.
[2m2023-05-20 09:43:10.732[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.b.w.embedded.tomcat.TomcatWebServer [0;39m [2m:[0;39m Tomcat started on port(s): 9999 (https) with context path ''
[2m2023-05-20 09:43:11.674[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[on(9)-127.0.0.1][0;39m [36mo.s.web.servlet.DispatcherServlet [0;39m [2m:[0;39m Initializing Servlet 'dispatcherServlet'
[2m2023-05-20 09:43:11.675[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[on(9)-127.0.0.1][0;39m [36mo.s.web.servlet.DispatcherServlet [0;39m [2m:[0;39m Completed initialization in 1 ms
[2m2023-05-20

==================
It seems like Hibernate 6 is not able to use verticaDialect.jar, whereas Hibernate 5 is using it happily.

GianPendola

If I use PostgreSQLDialect, I get:

2023-05-20T13:31:40.830-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.e.DevToolsPropertyDefaultsPostProcessor[0;39m [2m:[0;39m Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
[2m2023-05-20T13:31:40.830-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.e.DevToolsPropertyDefaultsPostProcessor[0;39m [2m:[0;39m For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
[2m2023-05-20T13:31:41.171-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Bootstrapping Spring Data JPA repositories in DEFAULT mode.
[2m2023-05-20T13:31:41.203-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Finished Spring Data repository scanning in 27 ms. Found 1 JPA repository interfaces.
[2m2023-05-20T13:31:41.754-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.b.w.embedded.tomcat.TomcatWebServer [0;39m [2m:[0;39m Tomcat initialized with port(s): 9999 (https)
[2m2023-05-20T13:31:41.797-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mw.s.c.ServletWebServerApplicationContext[0;39m [2m:[0;39m Root WebApplicationContext: initialization completed in 967 ms
[2m2023-05-20T13:31:41.837-05:00[0;39m [32mDEBUG[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mcom.zaxxer.hikari.HikariConfig [0;39m [2m:[0;39m Driver class com.vertica.jdbc.Driver found in Thread context class loader org.springframework.boot.devtools.restart.classloader.RestartClassLoader@7c9a965d
[2m2023-05-20T13:31:42.052-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.o.j.p.SpringPersistenceUnitInfo [0;39m [2m:[0;39m No LoadTimeWeaver setup: ignoring JPA class transformer
[2m2023-05-20T13:31:42.588-05:00[0;39m [31mERROR[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper [0;39m [2m:[0;39m [Vertica]VJDBC ERROR: Schema "information_schema" does not exist
[2m2023-05-20T13:31:42.594-05:00[0;39m [31mERROR[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mj.LocalContainerEntityManagerFactoryBean[0;39m [2m:[0;39m Failed to initialize JPA EntityManagerFactory: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.GenericJDBCException: Unable to build DatabaseInformation [[Vertica]VJDBC ERROR: Schema "information_schema" does not exist] [n/a]**
[2m2023-05-20T13:31:42.594-05:00[0;39m [33m WARN[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mConfigServletWebServerApplicationContext[0;39m [2m:[0;39m Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.GenericJDBCException: Unable to build DatabaseInformation [[Vertica]VJDBC ERROR: Schema "information_schema" does not exist] [n/a]
[2m2023-05-20T13:31:42.603-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.b.a.l.ConditionEvaluationReportLogger[0;39m [2m:[0;39m

Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled.
[2m2023-05-20T13:31:42.614-05:00[0;39m [31mERROR[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.boot.SpringApplication [0;39m [2m:[0;39m Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.GenericJDBCException: Unable to build DatabaseInformation [[Vertica]VJDBC ERROR: Schema "information_schema" does not exist] [n/a]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1770) ~[spring-beans-6.0.9.jar:6.0.9]
.
.
.
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-6.0.9.jar:6.0.9]
... 25 common frames omitted
Caused by: java.sql.SQLException: [Vertica]VJDBC ERROR: Schema "information_schema" does not exist
at com.vertica.util.ServerErrorData.buildException(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.io.ProtocolStream.readExpectedMessage(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.dataengine.VDataEngine.prepareImpl(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.dataengine.VDataEngine.prepare(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.dataengine.VDataEngine.prepare(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.jdbc.common.SPreparedStatement.(SPreparedStatement.java:310) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.jdbc.jdbc41.S41PreparedStatement.(S41PreparedStatement.java:91) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.jdbc.jdbc42.S42PreparedStatement.(S42PreparedStatement.java:77) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.jdbc.VerticaJdbc42PreparedStatementImpl.(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.jdbc.VJDBCObjectFactory.createPreparedStatement(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.jdbc.common.SConnection.prepareStatement(SConnection.java:1309) ~[vertica-jdbc-12.0.3-0.jar:na]
at com.vertica.jdbc.common.SConnection.prepareStatement(SConnection.java:1263) ~[vertica-jdbc-12.0.3-0.jar:na]
at org.hibernate.tool.schema.extract.spi.ExtractionContext.getQueryResults(ExtractionContext.java:43) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:39) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:66) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.(DatabaseInformationImpl.java:60) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:187) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
... 38 common frames omitted
Caused by: com.vertica.support.exceptions.ErrorException: [Vertica]VJDBC ERROR: Schema "information_schema" does not exist
... 55 common frames omitted

siddyash

Hello @GianPendola , Ajay is currently working on it to resolve the issue and provide you a solution soon. Thanks.

GianPendola

Hello @siddyash, do you have any update on this issue?
Thanks.

ajabrol

Hi @GlanPendola, I am working on it and will update with my analysis.

Thanks & Regards,
Ajay Abrol

ajabrol

Hi @GlanPendola,

If you want to use postgreSQLDialect then please follow https://github.com/bryanherger/vertica-information-schema. It helps you to create INFORMATION_SCHEMA and other objects on Vertica DB which helps you to connect with Vertica.

Meantime, I am working on to update VerticaDialect as per Hibernate6 and will update you accordingly.

Thanks & Regards,
Ajay Abrol

ajabrol

Hi @GlanPendola,

Development of Hibernate6 is completed and latest code has been published to the Git repository (https://github.com/vertica/hibernate-verticadialect). Please follow the instructions from readme file to compile and deploy VerticaDialect into your environment, Also published complied VerticaDialect.jar into Git repository under location (https://github.com/vertica/hibernate-verticadialect/tree/main/target) and you can take then deploy into your environment.

Please let me know if you face any issue.

Thanks & Regards,
Ajay Abrol

robann

Hi @ajabrol

I see that library is in active development - you made a minor change yesterday!

I could do with using the hibernate vertica dialect. Will you be making this available via maven? I can't really use jars from other sources in the project I'm working on.

Thanks,
Robert.

ajabrol

Hi Robert,

I will try to publish hibernate vertica dialect into maven and meantime you can get jar from location(https://github.com/vertica/hibernate-verticadialect/tree/main/target).

Please let me know if you have any queries.

Thanks & Regards,
Ajay Abrol