Prepare statement failing with Pageable object - Athena JDBC + Spring Data Repository in Java

Question

Hello, I am running into issue using the Athena JDBC driver AthenaJDBC42-2.0.36.1000.jar from a Spring Boot Application when using Spring Data and Hibernate with an `@Repository` to fetch data from my Athena DB. I have everything setup, authenticating, and connecting correctly to my Athena table. I am using Athena engine 3. The issue arises with the `Pageable` object in the repository method: ``` public interface EventRepository { Page findAll(Predicate predicate, Pageable pageable); } ``` What is *working* -- when `Pageable` is a `Pageable.unpaged()` object. I can successfully retrieve all objects in the db for my query, including additions to the `Predicate` which successfully translate to Athena database columns and filter accordingly. What is *not* working -- when `Pageable` is anything but an unpaged object, i.e. has limit and sort parameters. I have narrowed this down to what happens in the Athena JDBC driver when trying to execute its prepare statement. When paging is set, Hibernate adds a `limit ?` to the SQL of the prepared query, which makes sense since we want to limit the number of items retrieved. When this then gets down into the Athena JDBC driver during the execute, the `AjQueryExecutor` in AthenaJDBC driver replaces all the `?` with NULLs. This is usually ok, but the Athena Query Engine does NOT accept a NULL for `limit`. Example prepare statement BEFORE replacement (some items replaced with fillers). The query that hibernate generates is what is inside the parens: ``` SELECT * FROM (select from where

.columnA =? and .columnB=? order by

from

.timestamp desc limit ?) T LIMIT 0 ``` Example prepare AFTER replacement (see that all 3 `?` are replaced with `NULL`) ``` SELECT * FROM (select where

.columnA= NULL and .columnB= NULL order by

.timestamp desc limit NULL) T LIMIT 0 ``` When running the query above in the Athena Query Editor, it complains about the `limit NULL` inside the parens. If I change this to any number >=0, the query succeeds. It seems as though there is a bug in the Athena JDBC driver when there is a `limit` parameter in the passed in query (or alternatively the Athena Engine failing on `limit NULL`). Either way, I am stuck using paging with Spring Data and the Athena JDBC driver. I did also find in the decompiled java source of the Athena JDBC driver that the `AJReplacer` class is the object that seems to replace all `PARAM`s in the query with NULL. Question: is there a way to workaround this, or to allow the prepare statement to succeed when passing a limit in my JDBC query ? My goal was to get this working through the standard Spring data framework and not with the Session manager object itself (almost there!!), so any insights would be helpful.

Accepted Answer

Answering my own question here for anyone that is looking to use Spring Data with the Athena JDBC driver.

So, it turns out that `limit NULL` was indeed the issue, and the parameterized `limit` field is not supported with Hibernate/Athena JDBC/MySQLDialect. I *was* able to workaround the issue by extending the `MySQLDialect` class, and implementing my own "limit handler" to fill in the limit/offset fields with the real values instead of a parameterized value. Here is an example of the SQL Dialect that works with hibernate and the JDBC driver:
```
package com.acme.athena;

import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.pagination.AbstractLimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.dialect.pagination.LimitHelper;
import org.hibernate.engine.spi.RowSelection;

import java.sql.PreparedStatement;

public class AthenaMySQLDialect extends MySQLDialect {
    private static final LimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
        @Override
        public String processSql(String sql, RowSelection selection) {
            final boolean hasOffset = LimitHelper.hasFirstRow( selection );
            String _sql = sql + " limit %d".formatted(selection.getMaxRows());
            if (hasOffset) {
                _sql += " offset %d".formatted(selection.getFirstRow());
            }
            return _sql;
        }

@Override
        public boolean supportsLimit() {
            return true;
        }

@Override
        public int bindLimitParametersAtEndOfQuery(RowSelection selection, PreparedStatement statement, int index) {
            return 0;
        }
    };

@Override
    public boolean supportsLimit() {
        return true;
    }

@Override
    public LimitHandler getLimitHandler() {
        return LIMIT_HANDLER;
    }

@Override
    public String getLimitString(String sql, boolean hasOffset) {
        return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
    }
}
```
Once you have this dialect, you can set the hibernate driver with a property:
```
spring:
  jpa:
    properties:
      hibernate:
        dialect: "com.acme.athena.AthenaMySQLDialect"
        ddl-auto: validate
```

Prepare statement failing with Pageable object - Athena JDBC + Spring Data Repository in Java

Relevant content