Setup

import jakarta.persistence.EntityManagerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;


import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "secondary.repositories",
        entityManagerFactoryRef = "secondaryEntityManagerFactory",
        transactionManagerRef = "secondaryTransactionManager"
)
public class SecondaryDatabaseConfiguration {

    @Bean(name = "secondaryDbDataSource")
    public DataSource secondaryDbDataSource() {
        return DataSourceBuilder.create().url("jdbc:vertica://myhost:myport/mydb")
                                .username(username)
                                .password(password)
     
             // implementation 'com.vertica.jdbc:vertica-jdbc:24.1.0-0'
                                .driverClassName("com.vertica.jdbc.Driver")
                                .build();
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("secondaryDbDataSource") DataSource secondaryDataSource)
    {
        return builder.dataSource(secondaryDataSource)
                      .packages("secondary.entities")
                      .persistenceUnit("secondary")
                      .properties(Map.of(
                              "hibernate.hbm2ddl.auto", "none",
                              "hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect",
                      )).build();
    }

    @Bean(name = "secondaryTransactionManager")
    public PlatformTransactionManager secondaryTransactionManager(
            @Qualifier("secondaryEntityManagerFactory") EntityManagerFactory secondaryEntityManagerFactory)
    {
        return new JpaTransactionManager(secondaryEntityManagerFactory);
    }
}

I have two databases, one is a PostgreSQL DB, and the above is my secondary DataSource configuration. Note that before I set "hibernate.dialect", I had this error (I’m not sure if it’s relevant, but it might help):

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'secondaryEntityManagerFactory' defined in class path resource [...SecondaryDatabaseConfiguration.class]: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Unable to determine Dialect without JDBC metadata (please set 'jakarta.persistence.jdbc.url' for common cases or 'hibernate.dialect' when a custom Dialect implementation must be provided)

Therefore I used org.hibernate.dialect.PostgreSQLDialect as the "hibernate.dialect". When I create an entity and use a repository to fetch/query it, everything works well (maybe accidentally).

Problem

However, when I pass a PageRequest.of(0, 10, Sort...) to my repository method, the query gets mistranslated, and the following error gets thrown:

[Vertica]VJDBC ERROR: Syntax error at or near “rows” at character 607
java.lang.RuntimeException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement [[Vertica]VJDBC ERROR: Syntax error at or near “rows” at character 607] [select distinct [...abbreviated...] offset ? rows fetch first ? rows only];

Vertica doesn’t support the offset ? rows fetch first ? rows only syntax, but rather uses the limit ? offset ? syntax, from what I’ve seen.

Attempted Solution 1

In the source code for org.hibernate.dialect.PostgreSQLDialect (I am using hibernate version 6.4.4, by the way), I noticed it uses an instance of OffsetFetchLimitHandler, so I’ve tried to make a custom dialect which would use LimitOffsetLimitHandler instead.

import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.dialect.pagination.LimitOffsetLimitHandler;

public class MyVerticaDialect extends PostgreSQLDialect {
    @Override
    public LimitHandler getLimitHandler() {
        return LimitOffsetLimitHandler.INSTANCE;
    }
}

When I specify mypackage.MyVerticaDialect as the "hibernate.dialect", the query is still mistranslated as offset ? rows fetch first ? rows only. Placing a breakpoint inside my custom implementation reveals it’s never called.

Is there a way to ensure that my dialect is indeed used, or is my configuration somehow wrong?

Attempted Solution 2

The repository at https://github.com/vertica/hibernate-verticadialect contains an implementation of VerticaDialect that’s supposed to be compatible with hibernate 6.4.x and Vertica 24.x, but even after creating VerticaDialect-1.0.jar with mvn and putting it into libs directory, and including it into build.gradle using

repositories {
   mavenControl()
   flatDir {
      dirs 'libs'
   }
}

dependencies {
   ...
   implementation name: 'VerticaDialect-1.0'
}

after the refresh the build passes, but I’m not sure how to reference it in the code or include it. My IDE (IntelliJ’s IDEA) recognizes it as being under org.hibernate.dialect.VerticaDialect, but when I choose the import option, it shows the class doesn’t exist, and it throws an error. When I call gradlew dependencyInsight --dependency VerticaDialect, I see the Variant default table and the compileClasspath tree entry.

Edit:
I’ve realized the class name string ought to be a .class literal instead. The Dialect class gets created but the query still gets mistranslated.