pagination for informix database

Question

I am trying to do pagination for IBM Informix db but there are some restrictions from Hibernate dialect as it doesn't generates LIMIT queries. when I checked the IBM Informix manual tells me the following answer:-

"The Projection clause cannot include the SKIP, FIRST, or LIMIT keywords in these contexts:

when the SELECT statement is part of a view definition
in a subquery, except in the FROM clause of the outer query
in a cross-server distributed query in which a participating database server does not support the SKIP, FIRST, or LIMIT keywords."

I am trying to write my own implementation for LIMIT class and load it while loading Hibernate dialect. But every time I start my application it picks default dialect and not mine.

There is one issue open in hibernate - https://hibernate.atlassian.net/browse/HHH-5414.

The patch doesn't works on my local. But I fear as DB itself doesn't support then how efficient would it be if I try to do this manually as that would be an offset based pagination which I feel would be a performance hit and might not solve the problem.

I would like to know what best can be done considering these scenarios for pagination support for informix.

Answer 1

Yup, this is solved nowadays in Hibernate (at least in the 5.3.10.Final-redhat-00001 that is currently shipped with the jboss EAP7 servers we're mostly stuck with), you just need to add your

<property name="hibernate.dialect" value="org.hibernate.dialect.Informix10Dialect" />

to your persistence.xml then it works fine. By works I mean code in the lines of

if (pageSize > 0) {
    int firstResult = pageNo * pageSize - pageSize;
    query.setFirstResult(firstResult);
    query.setMaxResults(pageSize);
}

where query is a javax.persistence.Query.

A reasonable expectation firing queries such as these would be SQL 'native' output on the form

select skip <firstResult> limit <pageSize> [rest of the select statement]

to be sent to informix. However, Hibernate did not fix this until Hibernate 5.

Maybe it wasn't pretty but what can you do being stuck in ancient JBOSSes or other horrid old platforms bringing in/requiring ancient hibernate versions is to simply fix the code yourself. Not too long ago we used to hack hibernate-core going from

hibernate-core-4.x.x.Final

to hacked

hibernate-core-4.x.x.Final-pagination 

which was just replacing classes

org.hibernate.dialect.InformixDialect
org.hibernate.dialect.pagination.NoopLimitHandler
org.hibernate.dialect.pagination.FirstLimitHandler

so that these firstly supported limitOffSet by

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

and then we just implemented this stuff in some simple way like for instance

public final class InformixDialect extends Dialect {
..
private static final String SKIP = " SKIP ";
private static final String FIRST = " FIRST ";
private static final String SELECT = "select";
private static final int SELECT_LEN = SELECT.length();
..  
@Override        
public String getLimitString(String querySelect, int offset, int limit) {
    return new StringBuilder(querySelect.length() + 8)
            .append(querySelect)
            .insert(querySelect.toLowerCase().indexOf(SELECT) + SELECT_LEN, new StringBuilder(SKIP).append(offset).append(FIRST).append(limit).toString()).toString();
}
..

and (NoopLimitHandler is exactly the same)

public final class FirstLimitHandler extends AbstractLimitHandler {
..
private static final String SKIP = " SKIP ";
private static final String FIRST = " FIRST ";
private static final String EMPTY = "";
private static final String SELECT = "select";
private static final int SELECT_LEN = SELECT.length();
..
@Override
public int bindLimitParametersAtStartOfQuery(RowSelection selection, PreparedStatement statement, int index) {
    return 0;
}

@Override
public int bindLimitParametersAtEndOfQuery(RowSelection selection, PreparedStatement statement, int index) {
    return 0;
}
..
@Override
public String processSql(String sql, RowSelection selection) {
    if (selection == null || selection.getFirstRow() == null) {
        return sql;
    }
    boolean hasOffset = LimitHelper.hasFirstRow(selection);
    int maxOrLimit = this.getMaxOrLimit(selection);
    String sqlOffset = hasOffset? SKIP + selection.getFirstRow(): EMPTY;
    String sqlLimit = maxOrLimit > 0 ? FIRST + this.getMaxOrLimit(selection): EMPTY;
    String sqlOffsetLimit = sqlOffset + sqlLimit;
    return new StringBuilder(sql.length() + 10).append(sql).insert(sql.toLowerCase(Locale.ROOT).indexOf(SELECT) + SELECT_LEN, sqlOffsetLimit).toString();
}
..

Needles to say, if you can use Hibernate 5 this is of course the better alternative!