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

Sybase Dialect not supporting max result - paging

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects versions: 5.2.12
    • Fix versions: 5.3.5, 5.4.0.CR1
    • Components: hibernate-core
    • Labels:
      None
    • Last commented by a user?:
      true
    • Sprint:

      Description

      Hi,

      I am a contributor to EAI Open Source Platform Ikasan (http://www.ikasan.com/).

      We have a real world implementation of this platform against a Sybase DB with Hibernate as our ORM. We service large amounts of real time data on our bus and we have harvesting and housekeeping processes continually running. We observed locking and blocking spids when under heaving load. We performed a deep dive to discover that Hibernate is not limiting the result set size when issuing queries as follows:

                     Criteria criteria = session.createCriteria(WiretapFlowEvent.class);
                      criteria.add(Restrictions.eq("harvested", false));
                      criteria.setFirstResult(0);
                      criteria.setMaxResults(housekeepingBatchSize);
                      criteria.addOrder(Order.asc("timestamp"));
      
                      List<WiretapEvent> wiretaps = criteria.list();
      
                      return wiretaps;  
      

      I have re-mediated this by creating our own Hibernate dialect:

      public class IkasanSybaseASE157Dialect extends SybaseASE157Dialect
      {
          @Override
          public boolean supportsLimit()
          {
              return true;
          }
      
          @Override
          public LimitHandler buildLimitHandler(String sql, RowSelection selection)
          {
              return new IkasanSybaseASE157LimitHandler(sql, selection);
          }
      }
      

      Along with our own limit handler:

      public class IkasanSybaseASE157LimitHandler extends AbstractLimitHandler
      {
          public IkasanSybaseASE157LimitHandler(String sql, RowSelection selection)
          {
              super(sql, selection);
          }
      
          @Override
          public String getProcessedSql()
          {
              String select = sql.substring(0, sql.indexOf("select") + "select".length());
              String statement = sql.substring(sql.indexOf("select") + "select".length(), sql.length());
      
              if(selection.getFirstRow() != null && selection.getMaxRows() != null)
              {
                  return select + " top " + (selection.getFirstRow() + selection.getMaxRows()) + statement;
              }
              else if(selection.getMaxRows() != null)
              {
                  return select + " top " +  selection.getMaxRows() + statement;
              }
              else
              {
                  return sql;
              }
          }
      
          @Override
          public boolean supportsLimit()
          {
              return true;
          }
      
          public int bindLimitParametersAtStartOfQuery(PreparedStatement statement, int index) throws SQLException
          {
              return 0;
          }
      
          public int bindLimitParametersAtEndOfQuery(PreparedStatement statement, int index) throws SQLException {
              return 0;
          }
      
      }
      

      Is there any reason that this sort of functionality has not been implemented in Hibernate. I realise that Sybase has some limitations and does not support offset. However, i think that my solution goes part way to solving the problem, and I have confirmed that the burden on out database is greatly reduced.

      Could you please share some thoughts on this? Have I overlooked anything? If not would you consider including this kind of functionality?

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: