spring-boot hibernate spring-data-jpa datasource vertica

Spring, Hibernate issue with applying/using another SQL Dialect

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.

Solution

The configuration is correct except for the "hibernate.dialect" property, which needs a Class<Dialect> object (literal):

    @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.class,
                      )).build();
    }

The actual SQL rendering/translation depends on the dialect. However, by default, the provided dialect's LimitHandler is NOT used by StandardSqlAstTranslator during the translation of Pageables, etc., which is the default implementation. You can inspect StandardSqlAstTranslator to see how it works.
The concrete translator that is used is determined by getSqlAstTranslatorFactory() in the specified Dialect. If this method returns null, StandardSqlAstTranslator is used. Override it in your custom dialect in this way (and then provide its class literal for the "hibernate.dialect" property):

    @Override
    public SqlAstTranslatorFactory getSqlAstTranslatorFactory() {
        return new StandardSqlAstTranslatorFactory() {
            protected <T extends JdbcOperation> SqlAstTranslator<T> buildTranslator(SessionFactoryImplementor sessionFactory, Statement statement) {
                return new CustomVerticaSqlAstTranslator<>(sessionFactory, statement);
            }
        };
    }

Here I return an instance of CustomVerticaSqlAstTranslator in order to fix the issue in the question post. Create this class like this:

public class CustomVerticaSqlAstTranslator<T extends JdbcOperation> extends StandardSqlAstTranslator<T> {

    public CustomVerticaSqlAstTranslator(SessionFactoryImplementor sessionFactory, Statement statement) {
        super(sessionFactory, statement);
    }

    @Override
    protected void renderOffsetFetchClause(Expression offsetExpression, Expression fetchExpression, FetchClauseType fetchClauseType, boolean renderOffsetRowsKeyword) {
        if (fetchExpression != null) {
            renderFetch(fetchExpression, null, fetchClauseType);
        }

        if (offsetExpression != null) {
            renderOffset(offsetExpression, false);
        }
    }

    @Override
    protected void renderFetch(Expression fetchExpression, Expression offsetExpressionToAdd, FetchClauseType fetchClauseType) {
        final Stack<Clause> clauseStack = getClauseStack();
        appendSql(" limit ");
        clauseStack.push(Clause.FETCH);
        try {
            renderFetchExpression(fetchExpression);
        } finally {
            clauseStack.pop();
        }
    }
}

This fixes the issue with the incorrect rendering of the LIMIT and OFFSET SQL commands for Vertica. I've merely juggled and combined the existing commands within StandardSqlAstTranslator to get the desired functionality. I've tested it with simple Pageables, and it works well.