Uploaded image for project: 'Hibernate ORM'
  1. HHH-11509

Infomix limit handler support for offset

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 5.2.7
    • Fix Version/s: 5.2.9
    • Component/s: hibernate-core
    • Labels:

      Description

      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.

      *select SKIP 10 FIRST 10 FROM table. *

      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;
                   }
             }
       
      }
      

        Attachments

          Issue links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: