-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
loverde
 Post subject: Problem with Criteria.setFirstResult in 2.1final
PostPosted: Sun Dec 21, 2003 2:38 am 
Beginner
Beginner

Joined: Fri Oct 10, 2003 4:54 pm
Posts: 26
Location: Chicago, IL
This problem has been fixed by upgrading to the 2.1.1 release, but just posting here in case anyone encounters this problem with 2.1final.

Basically, I was having a problem where if I called Criteria.setFirstResult with any value > 0, I was not getting any results back.

As mentioned above, upgrading to 2.1.1 resolved this issue (but I didn't see it in the 2.1.1 release notes)
Top
 Profile  
 
loverde
 Post subject:
PostPosted: Sun Dec 21, 2003 5:07 am 
Beginner
Beginner

Joined: Fri Oct 10, 2003 4:54 pm
Posts: 26
Location: Chicago, IL
Actually, the 2.1.1 upgrade didn't fix the problem. (I thought it did because I forgot to turn my workaround code off).

Here is a partial listing of the code (it works if isHibernateBugged is true, but fails if isHibernateBugged is false). My test cases had firstResult = 1 and 11 and maxResults was always 10.

Code:

            boolean isHibernateBugged = true;
            if (isHibernateBugged) {
               result = _hibernateBugWorkaround(criteria, hc);
            } else {
               result = new ListAndCount();
               if (criteria.getFirstResult() > 0) {
                  hc.setFirstResult(criteria.getFirstResult());
               }
               if (criteria.getMaxResults() > 0) {
                  hc.setMaxResults(criteria.getMaxResults());
               }
               result.setList(hc.list());
               // ... get the count ...
            }
            return result;


And here is the (very ugly) workaround code:

Code:
   private ListAndCount _hibernateBugWorkaround(
      DocLineCriteria criteria,
      Criteria hc)
      throws FSSDAOException, HibernateException {

      ListAndCount result = new ListAndCount();

      // NOTE: there is some problem with setting the
      //       criteria where no results get returned
      //       so doing this manually for now...
      if (criteria.getFirstResult() == 0) {
         _LOGGER.debug("hibernateBugWorkaround: normal mode");
         if (criteria.getMaxResults() > 0) {
            hc.setMaxResults(criteria.getMaxResults());
         }
         result.setList(hc.list());
      } else {
         _LOGGER.debug("hibernateBugWorkaround: workaround mode");

         // Set the hibernate max results equal to
         // at least the first result + max results
         // of the request so that we can manually
         // page through the results to build the list
         // the user wants
         if (criteria.getMaxResults() > 0) {
            if (criteria.getMaxResults() == Integer.MAX_VALUE) {
               hc.setMaxResults(Integer.MAX_VALUE);
            } else {
               // Otherwise, the the result to the first result
               // plus criteria max results
               hc.setMaxResults(
                  criteria.getFirstResult() + criteria.getMaxResults());
            }
         }

         // NOTE: criteria doesn't support an iterator
         //       based query unfortunately
         List realList = hc.list();
         if (_LOGGER.isLoggable(Level.DEBUG)) {
            _LOGGER.debug("realList.size: " + realList.size());
         }

         // Now page through the results and only
         // grab the ones after the first desired result                  
         List tmp = new ArrayList();
         int firstIndex = criteria.getFirstResult() - 1;
         int lastIndex = 0;
         if (criteria.getMaxResults() == 0
            || criteria.getMaxResults() == Integer.MAX_VALUE) {
            lastIndex = Integer.MAX_VALUE;
         } else {
            lastIndex =
               criteria.getFirstResult() + criteria.getMaxResults() - 1;
         }

         int index = 0;
         for (Iterator iter = realList.iterator();
            iter.hasNext() && index < lastIndex;
            index++) {
            Object o = iter.next();
            if (index >= firstIndex) {
               tmp.add(o);
            }
         }
         result.setList(tmp);
      }
      // ... get the count ...
      return result;
   }


I'm not sure if this has anything to do with the other parts of the Criteria object or not at this point. I'll try to reduce this to a smaller set of reproducable sample code next week.
Top
 Profile  
 
loverde
 Post subject:
PostPosted: Mon Dec 22, 2003 8:03 pm 
Beginner
Beginner

Joined: Fri Oct 10, 2003 4:54 pm
Posts: 26
Location: Chicago, IL
Just to continue this sage, the problem also occurs for Query objects. The following test code:

Code:
   public void testFirstResultOnQuery() throws Exception {
      SessionFactory sessionFactory =
         (SessionFactory) _lookup(FSSNamingConstants
            .FSS_HIBERNATE_SESSION_FACTORY);
      Session session = sessionFactory.openSession();

      Connection conn = session.connection();
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM TFCDB02.GLACCT");
      rs.next();
      _LOGGER.info("count(*) of glaccounts: " + rs.getInt(1));
      rs.close();
      stmt.close();

      // Now try a query without firstResult for 10 results
      Query q1 = session.createQuery("from " + GLAccountDO.class.getName());
      q1.setMaxResults(10);
      _LOGGER.info("q1.list().size(): " + q1.list().size());

      Query q2 = session.createQuery("from " + GLAccountDO.class.getName());
      q2.setFirstResult(0);
      q2.setMaxResults(10);
      _LOGGER.info("q2.list().size(): " + q2.list().size());

      Query q3 = session.createQuery("from " + GLAccountDO.class.getName());
      q3.setFirstResult(5);
      q3.setMaxResults(10);
      _LOGGER.info("q3.list().size(): " + q3.list().size());

      Query q4 = session.createQuery("from " + GLAccountDO.class.getName());
      q4.setFirstResult(10);
      q4.setMaxResults(10);
      _LOGGER.info("q4.list().size(): " + q4.list().size());

      session.close();
   }


Produces this output:

Code:
INFO  servlet.XMLFormatter - ***** STARTING TEST: HibernateTest.testFirstResultOnQuery
INFO  common.HibernateTest - count(*) of glaccounts: 3196
INFO  common.HibernateTest - q1.list().size(): 10
INFO  common.HibernateTest - q2.list().size(): 10
INFO  common.HibernateTest - q3.list().size(): 0
INFO  common.HibernateTest - q4.list().size(): 0
INFO  servlet.XMLFormatter - ***** TEST COMPLETED: HibernateTest.testFirstResultOnQuery


Or with debug included:

Code:

DEBUG util.JUnitVersionHelper - <getTestCaseName([testFirstResultOnQuery(test.data.common.HibernateTest)]) [Servlet.Engine.Transports:10]

DEBUG util.JUnitVersionHelper - >getTestCaseName = [testFirstResultOnQuery]

INFO  servlet.XMLFormatter - ***** STARTING TEST: HibernateTest.testFirstResultOnQuery

DEBUG sqllog - before executeQuery(String) sql=SELECT COUNT(*) FROM TFCDB02.GLACCT

DEBUG sqllog - after executeQuery(String) [time=16ms] sql=SELECT COUNT(*) FROM TFCDB02.GLACCT

INFO  common.HibernateTest - count(*) of glaccounts: 3196

DEBUG hql.QueryTranslator - HQL: from com.hcsc.cfs.fss.service.reference.GLAccountDO

DEBUG hql.QueryTranslator - SQL: select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets

DEBUG hibernate.SQL - select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG impl.BatcherImpl - preparing statement

DEBUG sqllog - before executeQuery() sql=select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG sqllog - after executeQuery() [time=93ms] sql=select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG loader.Loader - processing result set

DEBUG loader.Loader - result row: 100101   

DEBUG loader.Loader - Initializing object from ResultSet: 100101   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100101   

DEBUG loader.Loader - result row: 100102   

DEBUG loader.Loader - Initializing object from ResultSet: 100102   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100102   

DEBUG loader.Loader - result row: 100103   

DEBUG loader.Loader - Initializing object from ResultSet: 100103   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100103   

DEBUG loader.Loader - result row: 100109   

DEBUG loader.Loader - Initializing object from ResultSet: 100109   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100109   

DEBUG loader.Loader - result row: 100111   

DEBUG loader.Loader - Initializing object from ResultSet: 100111   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100111   

DEBUG loader.Loader - result row: 100123   

DEBUG loader.Loader - Initializing object from ResultSet: 100123   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100123   

DEBUG loader.Loader - result row: 100171   

DEBUG loader.Loader - Initializing object from ResultSet: 100171   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100171   

DEBUG loader.Loader - result row: 100175   

DEBUG loader.Loader - Initializing object from ResultSet: 100175   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100175   

DEBUG loader.Loader - result row: 100176   

DEBUG loader.Loader - Initializing object from ResultSet: 100176   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100176   

DEBUG loader.Loader - result row: 100177   

DEBUG loader.Loader - Initializing object from ResultSet: 100177   

DEBUG loader.Loader - Hydrating entity: com.hcsc.cfs.fss.service.reference.GLAccountDO#100177   

DEBUG loader.Loader - done processing result set (10 rows)

DEBUG impl.BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets

DEBUG impl.BatcherImpl - closing statement

DEBUG loader.Loader - total objects hydrated: 10

INFO  common.HibernateTest - q1.list().size(): 10

DEBUG hql.QueryTranslator - HQL: from com.hcsc.cfs.fss.service.reference.GLAccountDO

DEBUG hql.QueryTranslator - SQL: select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets

DEBUG hibernate.SQL - select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG impl.BatcherImpl - preparing statement

DEBUG sqllog - before executeQuery() sql=select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG sqllog - after executeQuery() [time=79ms] sql=select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG loader.Loader - processing result set

DEBUG loader.Loader - result row: 100101   

DEBUG loader.Loader - result row: 100102   

DEBUG loader.Loader - result row: 100103   

DEBUG loader.Loader - result row: 100109   

DEBUG loader.Loader - result row: 100111   

DEBUG loader.Loader - result row: 100123   

DEBUG loader.Loader - result row: 100171   

DEBUG loader.Loader - result row: 100175   

DEBUG loader.Loader - result row: 100176   

DEBUG loader.Loader - result row: 100177   

DEBUG loader.Loader - done processing result set (10 rows)

DEBUG impl.BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets

DEBUG impl.BatcherImpl - closing statement

DEBUG loader.Loader - total objects hydrated: 0

INFO  common.HibernateTest - q2.list().size(): 10

DEBUG hql.QueryTranslator - HQL: from com.hcsc.cfs.fss.service.reference.GLAccountDO

DEBUG hql.QueryTranslator - SQL: select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets

DEBUG hibernate.SQL - select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG impl.BatcherImpl - preparing statement

DEBUG sqllog - before executeQuery() sql=select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG sqllog - after executeQuery() [time=94ms] sql=select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG loader.Loader - processing result set

DEBUG loader.Loader - done processing result set (0 rows)

DEBUG impl.BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets

DEBUG impl.BatcherImpl - closing statement

DEBUG loader.Loader - total objects hydrated: 0

INFO  common.HibernateTest - q3.list().size(): 0

DEBUG hql.QueryTranslator - HQL: from com.hcsc.cfs.fss.service.reference.GLAccountDO

DEBUG hql.QueryTranslator - SQL: select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets

DEBUG hibernate.SQL - select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG impl.BatcherImpl - preparing statement

DEBUG sqllog - before executeQuery() sql=select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG sqllog - after executeQuery() [time=109ms] sql=select glaccountd0_.GLACCT_NBR as GLACCT_NBR, glaccountd0_.GLACCT_NM as GLACCT_NM from TFCDB02.GLACCT glaccountd0_

DEBUG loader.Loader - processing result set

DEBUG loader.Loader - done processing result set (0 rows)

DEBUG impl.BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets

DEBUG impl.BatcherImpl - closing statement

DEBUG loader.Loader - total objects hydrated: 0

INFO  common.HibernateTest - q4.list().size(): 0

DEBUG util.JUnitVersionHelper - <getTestCaseName([testFirstResultOnQuery(test.data.common.HibernateTest)])

DEBUG util.JUnitVersionHelper - >getTestCaseName = [testFirstResultOnQuery]

INFO  servlet.XMLFormatter - ***** TEST COMPLETED: HibernateTest.testFirstResultOnQuery

DEBUG util.JUnitVersionHelper - <getTestCaseName([testFirstResultOnQuery(test.data.common.HibernateTest)])

DEBUG util.JUnitVersionHelper - >getTestCaseName = [testFirstResultOnQuery]



Very strange indeed....
Top
 Profile  
 
loverde
 Post subject:
PostPosted: Mon Dec 22, 2003 9:00 pm 
Beginner
Beginner

Joined: Fri Oct 10, 2003 4:54 pm
Posts: 26
Location: Chicago, IL
After stepping through the debugger, I discovered part of the source of the problem. Apparently Hibernate is attempting to use ResultSet.absolute on my DataSource even though it doesn't support scrollable results.

What is really strange is that the SQLException is getting eaten by Hibernate somewhere. I'll try to see if I can pinpoint where this is happening (along with why Hibernate thinks that my JDBC driver supports scrollable results).

In the meantime, the workaround I can use is to explicitly set hibernate.jdbc.use_scrollable_resultset to false in my configuration xml file.
Top
 Profile  
 
loverde
 Post subject:
PostPosted: Mon Dec 22, 2003 9:23 pm 
Beginner
Beginner

Joined: Fri Oct 10, 2003 4:54 pm
Posts: 26
Location: Chicago, IL
And here's the bottom line result...it's not a bug in hibernate.

The bug is in the JDBC Driver. Some test code:

Code:
   public void testScrollableResultSet() throws Exception {
      SessionFactory sessionFactory =
         (SessionFactory) _lookup(FSSNamingConstants
            .FSS_HIBERNATE_SESSION_FACTORY);
      Session session = sessionFactory.openSession();

      Connection conn = session.connection();

      DatabaseMetaData metadata = conn.getMetaData();
      _LOGGER.info(
         "metadata.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY): "
            + metadata.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));
      _LOGGER.info(
         "metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE): "
            + metadata.supportsResultSetType(
               ResultSet.TYPE_SCROLL_INSENSITIVE));
      _LOGGER.info(
         "metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE): "
            + metadata.supportsResultSetType(
               ResultSet.TYPE_SCROLL_SENSITIVE));

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM TFCDB02.GLACCT");
      rs.next();
      _LOGGER.info("count(*) of glaccounts: " + rs.getInt(1));
      rs.close();
      stmt.close();

      stmt =
         conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
      rs = stmt.executeQuery("SELECT * FROM TFCDB02.GLACCT");
      boolean absoluteResult = rs.absolute(5);
      _LOGGER.info("absolute(5) result: " + absoluteResult);

      rs.close();
      stmt.close();
      session.close();
   }


And the output:

Code:
INFO  servlet.XMLFormatter - ***** STARTING TEST: HibernateTest.testScrollableResultSet
INFO  common.HibernateTest - metadata.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY): false
INFO  common.HibernateTest - metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE): true
INFO  common.HibernateTest - metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE): false
INFO  common.HibernateTest - count(*) of glaccounts: 3196
INFO  common.HibernateTest - absolute(5) result: false
INFO  servlet.XMLFormatter - ***** TEST COMPLETED: HibernateTest.testScrollableResultSet


So basically, the JDBC driver is reporting that it supports scrollable result sets and Hibernate is attempting to use them, but the call to "ResultSet.absolute()" always returns false. Very annoying and unfortunately, there really isn't any way for Hibernate to detect this problem short of having it explicitly test all of the JDBC features that the JDBC driver says it supports during SessionFactory configuration (which would be cool, but somewhat above and beyond what the tool should do).

The environment causing the problem is DB2 Connect 7.1 fp3 on a mainframe DB2 7.1 database.
Top
 Profile  
 
dimas
 Post subject:
PostPosted: Mon Dec 22, 2003 9:29 pm 
Expert
Expert

Joined: Tue Sep 16, 2003 4:06 pm
Posts: 318
Location: St. Petersburg, Russia
you can tell Hibernate not to use scrollable resultset by setting "jdbc.use_scrollable_resultset" to "false".

Needles to say this will be extremly inefficient since Hibernate will "scroll" resultset manuall (by calling rs.next() many times).

By the way, as I see in DB2Dialect code, it DOES SUPPORT query result limiting. And that meant Hibernate should use this limiting to support firstResult/maxResults. Are you sure you are teling the right dialect to Hibernate?
Top
 Profile  
 
loverde
 Post subject:
PostPosted: Mon Dec 22, 2003 9:46 pm 
Beginner
Beginner

Joined: Fri Oct 10, 2003 4:54 pm
Posts: 26
Location: Chicago, IL
dimas wrote:
you can tell Hibernate not to use scrollable resultset by setting "jdbc.use_scrollable_resultset" to "false".

Needles to say this will be extremly inefficient since Hibernate will "scroll" resultset manuall (by calling rs.next() many times).

By the way, as I see in DB2Dialect code, it DOES SUPPORT query result limiting. And that meant Hibernate should use this limiting to support firstResult/maxResults. Are you sure you are teling the right dialect to Hibernate?


Yes, I am now explicitly setting "use_scrollable_resultset" to "false" to get around this problem and that works.

Regarding the DB2Dialect, please see this post. (Basically, I had to turn off limit support because our mainframe DB2 database is not happy with the limit queries):

http://forum.hibernate.org/viewtopic.ph ... highlight=
Top
 Profile  
 
mikemcelligott
 Post subject: DB2 AbstractMethodError
PostPosted: Fri Feb 24, 2006 4:56 pm 
Newbie

Joined: Fri Feb 24, 2006 4:50 pm
Posts: 2
I've encountered the same problem, apparently the DB2 driver I'm using doesn't implement the SupportsResultSetType on the metadata implementation. Not that this isn't obvious, but I've swapped in my own version of SettingsFactory, that just catches and ignores the Error..

org.hibernate.cfg.SettingsFactory:80

try
{
metaSupportsScrollable = meta.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
metaSupportsBatchUpdates = meta.supportsBatchUpdates();
}
catch(AbstractMethodError ame)
{
metaSupportsScrollable = false;
metaSupportsBatchUpdates = false;
}

Dropping the new class in works for me, whereas specifying the hibernate properties below does not.

hibernate.jdbc.batch_size = 0
hibernate.jdbc.use_scrollable_resultset = false

Mike
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.