Count rows on distinct select with multiple columns

Question

I have been searching for some time but not found enough answers to do this myself.

I have an MsSQL that looks like this:

select count(*) from 
   ( select distinct supplierName, supplierNr
   from dbo.InvoiceTypeBean
   ) as a

This returns what I want using pure SQL

But i need this in hibernate using criteria and/or detachedcriteria:

The detached criteria part:

DetachedCriteria dCriteria = DetachedCriteria.forClass(clazz);
ProjectionList p = Projections.projectionList();
p.add(Projections.property("supplierName"));
p.add(Projections.property("supplierNr"));
dCriteria.setProjection(Projections.distinct(p));

The problem is attaching it to the criteria:

Criteria criteria = session.createCriteria(clazz);
.... Some atachement
criteria.setProjection(Projections.rowCount());
int count = ((Number) criteria.uniqueResult()).intValue();

I really need the solution to use Criteria and/or DetachedCriteria as the queries are being build dynamically, and a greater solution is build using them.

Answer 1

Well found the solution, what I really needed was just the row count of the output when using distinct on multiple columns (could have been specified better I know).

The solution for interested:

session.beginTransaction();
Criteria criteria = session.createCriteria(clazz);

ProjectionList p = Projections.projectionList();
p.add(Projections.property("supplierName"));
p.add(Projections.property("supplierNr"));
criteria.setProjection(Projections.distinct(p));

criteria.setResultTransformer(Transformers.TO_LIST);

ScrollableResults results = criteria.scroll();
results.last();
count = results.getRowNumber() + 1;
results.close();
session.getTransaction().commit();

Where the interesting part is the scrollableresult and no use of DetachedCriteria.

Answer 2

I'm not sure, but I think the usage of ScrollableResults can be a serious performance bottleneck in case of huge recordsets.