The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Vertica Integration with Hibernate: Connection Guide

shwetanksshwetanks
in General Discussion

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?

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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

  • GianPendolaGianPendola Community Edition User

    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.

  • ajabrolajabrol Vertica Employee Employee
    edited May 12

    Hi,

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

  • ajabrolajabrol Vertica Employee Employee

    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

  • GianPendolaGianPendola Community Edition User

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

  • GianPendolaGianPendola Community Edition User

    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.sprin[email protected]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.sprin[email protected]59dde20d

    .
    .
    .
    [36mcom.zaxxer.hikari.pool.HikariPool [0;39m [2m:[0;39m HikariPool-1 - Added connection [email protected]
    [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.

  • GianPendolaGianPendola Community Edition User

    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.sprin[email protected]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

  • siddyashsiddyash Vertica Employee Employee

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...