How to get record count?

fafnir · **Joined:** Wed Dec 03, 2003 9:41 pm **Posts:** 87

I have Some code like the following:
Criteria crit = session.createCriteria(WsStockPriceModel.class);
crit.setFirstResult(firstResult);
crit.setMaxResults(pageSize);
List list = crit.list();

My question is how to get total record count?

dmmorris · **Posted:** Tue Dec 09, 2003 1:58 am

For the list you might try list.size() but that is probably not what you meant. For the potential result set you could use a standard hql query with an aggregate function something like select count(*) from YourClass as YourClass and setting your selection criteria before running the hql.

fafnir · **Joined:** Wed Dec 03, 2003 9:41 pm **Posts:** 87

My Criteria has complex query condition. If I use hql to count total result num that fits those complex query condition, shall I compose those condition with hql again? That means I would compose those query condition with two ways:hql and criteria. One for count and one for query. If it is true I have no reason to use criteria. Criteria means nothing.

emmanuel · **Posted:** Thu Dec 11, 2003 7:02 am

Somebody made a patch to add what you want I guess, but it has not been accepted yet : Hibernate team don't want API they will regret later, so... think time.

If you absolutly need, it you can find it on JIRA

jdragon · **Joined:** Mon Nov 17, 2003 3:50 pm **Posts:** 19

I need the same function also. My query is complex!

fafnir wrote:

I have Some code like the following:
Criteria crit = session.createCriteria(WsStockPriceModel.class);
crit.setFirstResult(firstResult);
crit.setMaxResults(pageSize);
List list = crit.list();

My question is how to get total record count?

emmanuel · **Posted:** Fri Jan 02, 2004 9:50 am

Vote on the JIRA bug

dannwebster · **Joined:** Wed Mar 24, 2004 7:40 pm **Posts:** 18

I built a class that solves this problem. I have done some minimal tests on it, and it works for my purposes. It seems kind of like I am cheating, but like I said...it seems to work.

I would like for some other people to look at this and

1) Suggest how I can make it less kludgy, and more consistent with hibernate code

2) Test it in their own code to see if they come up with any bugs

3) See if there is a better place to post it to make it useful to more hibernate users.

Here goes:

Code:

package test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.*;

import net.sf.hibernate.*;
import net.sf.hibernate.loader.CriteriaLoader;
import net.sf.hibernate.engine.QueryParameters;
import net.sf.hibernate.engine.SessionFactoryImplementor;
import net.sf.hibernate.engine.SessionImplementor;
import net.sf.hibernate.impl.CriteriaImpl;
import net.sf.hibernate.impl.SessionFactoryImpl;
import net.sf.hibernate.persister.OuterJoinLoadable;
import net.sf.hibernate.persister.ClassPersister;
import net.sf.hibernate.type.Type;
import net.sf.hibernate.type.IntegerType;
import com.twcaustin.filelibrary.model.FileVersion;

//TODO: this class depends directly upon CriteriaImpl, in the impl package ... add a CriteriaImplementor interface
/**
 * counts the results of a given Criteria; mostly used for pagination.
 */ 
public class CriteriaResultsCounter extends CriteriaLoader {

    public static int count(Session s, Criteria criteria) throws HibernateException {
        CriteriaImpl cImpl = (CriteriaImpl) criteria;
        SessionImplementor sImpl = (SessionImplementor) s;
        SessionFactoryImpl sfImpl = (SessionFactoryImpl) s.getSessionFactory();
        ClassPersister ps = sfImpl.getPersister(FileVersion.class) ;
      if ( !(ps instanceof OuterJoinLoadable) ) {
         throw new MappingException( "class persister is not OuterJoinLoadable: " + FileVersion.class.getName() );
      }
      OuterJoinLoadable ojl = (OuterJoinLoadable) ps;
        CriteriaResultsCounter counter = new CriteriaResultsCounter(ojl,
                sfImpl,
                cImpl);
        int count = counter.doCount(sImpl);
        return count;
    }

   protected CriteriaResultsCounter(OuterJoinLoadable persister, SessionFactoryImplementor factory, CriteriaImpl criteria) throws HibernateException {
        super(persister, factory, criteria);
    }

    protected void setSql() throws MappingException {
        String select = getSQLString();
        int start = select.indexOf("from");
        String from = select.substring(start, select.length());
        String count = "select count(*) ";
        sql = count+from;
    }

    protected int doCount(SessionImplementor sImpl) throws HibernateException {
        try {
            setSql();
            List l = list(sImpl);
            Iterator i = l.iterator();
            int count = ((Integer) i.next()).intValue();
            return count;
        } catch (SQLException e) {
            throw new HibernateException(e);
        }
    }


    protected List list(
      final SessionImplementor session,
      final QueryParameters queryParameters,
      final Set querySpaces,
      final Type[] resultType)
       throws SQLException, HibernateException {
        Type[] countType = new Type[1];
        countType[0] = new IntegerType();
      final SessionFactoryImplementor factory = session.getFactory();


        PreparedStatement st = prepareQueryStatement(
         applyLocks( getSQLString(), queryParameters.getLockModes(), session.getFactory().getDialect() ),
         queryParameters, false, session
      );

        List list = new ArrayList();
        ResultSet rs = st.executeQuery();
        if (rs.next()) {
            int count = rs.getInt(1);
            list.add(new Integer(count));
        }
        return list;
    }
}

dannwebster · **Joined:** Wed Mar 24, 2004 7:40 pm **Posts:** 18

Oh.... should read further.... looks like this is already done on JIRA. I thought I was SOOO smart.

Apologies.
DW

dannwebster · **Joined:** Wed Mar 24, 2004 7:40 pm **Posts:** 18

The code posted above is even the wrong code... older version. The static count() method should look like this:

Code:

 public static int count(Session s, Criteria criteria) throws HibernateException {
        CriteriaImpl cImpl = (CriteriaImpl) criteria;
        SessionImplementor sImpl = (SessionImplementor) s;
        SessionFactoryImpl sfImpl = (SessionFactoryImpl) s.getSessionFactory();
        Class clazz = cImpl.getCriteriaClass();
        ClassPersister ps = sfImpl.getPersister(clazz) ;
      if ( !(ps instanceof OuterJoinLoadable) ) {
         throw new MappingException( "class persister is not OuterJoinLoadable: " + clazz.getName() );
      }
      OuterJoinLoadable ojl = (OuterJoinLoadable) ps;
        CriteriaResultsCounter counter = new CriteriaResultsCounter(ojl,
                sfImpl,
                cImpl);
        int count = counter.doCount(sImpl);
        return count;
    }

additionally, the import of FileInfo should be removed.