Message Title

The current Informix limit handler doesn't have an offset support. But Informix does.
There is a skip keyword for that. Without offset support a scrollable result will be used. I've identified the scrollable result as a performance problem in my application.

I've create a custom limit handler below.
If required I'll provide a pull request on github and integrate it directly in the InformixDialect.

public class CustomInformixDialect extends InformixDialect {
     
       @Override
       public LimitHandler getLimitHandler() {
             return new InformixLimitHandler();
       }
 
       private final class InformixLimitHandler extends AbstractLimitHandler {
             @Override
             public String processSql(String sql, RowSelection selection) {
                    final boolean hasOffset = LimitHelper.hasFirstRow(selection);
                    String sqlOffset = hasOffset ? " SKIP " + selection.getFirstRow() : "";
                    String sqlLimit = " FIRST " + getMaxOrLimit(selection);
                    String sqlOffsetLimit = sqlOffset + sqlLimit;
                    String result = new StringBuilder(sql.length() + 10)
                                  .append(sql)
                                  .insert(
                                               sql.toLowerCase(Locale.ROOT).indexOf("select") + 6,
                                               sqlOffsetLimit)
                                  .toString();
                    return result;
             }
 
             @Override
             public boolean supportsLimit() {
                    return true;
             }
 
             @Override
             public boolean bindLimitParametersFirst() {
                    return true;
             }
 
             @Override
             public boolean useMaxForLimit() {
                    return true;
             }
 
             @Override
             public boolean supportsLimitOffset() {
                    return true;
             }
 
             @Override
             public boolean supportsVariableLimit() {
                    return false;
             }
       }
 
}