Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns

Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns

ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("id.state"));
projList.add(Projections.property("id.uspsCity"));
criteria.setProjection(Projections.distinct(projList));

Count rows on distinct select with multiple columns

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.

Hibernate criteria. Select all distinct entities

You can set multiple properties as in Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns

 ProjectionList projList = new ProjectionList();
 projList.add(Projections.property("id.state"));
projList.add(Projections.property("id.uspsCity"))
criteria.setProjection(Projections.distinct(projList));

Criteria query to select multiple columns in hibernate?

You could try to give a ProjectionList to your Projections.distinct:

Session ses = sessionFactory.getCurentSession();
Criteria c = ses.createCriteria(user.class);
c.add(Restrictions.eq("id", uid));
c.add(Restrictions.eq("name", uname));

ProjectionList pl = Projections.projectionList();
pl.add(Projections.property("testid"));
pl.add(Projections.property("marks"));

c.setProjection(Projections.distinct(pl));

Get Two different count from one field, for a distinct date using hibernate criteria

Found answer to my question:

Criteria crit1 = sessionC.createCriteria(OP_DOCTOR_VISIT.class, "OPDV1");
crit.createAlias("OPDV1.OP_VISIT", "OPDV2", JoinType.LEFT_OUTER_JOIN,
Restrictions.and(Restrictions.eq("OPDV2.FORM", "NEW"), Restrictions.ge("OPDV2.USER_DATETIME", fdate), Restrictions.le("OPDV2.USER_DATETIME", tdate)));
    crit.add(Restrictions.ge("OPDV1.USER_DATETIME", fdate));
    crit.add(Restrictions.le("OPDV1.USER_DATETIME", tdate));
    ProjectionList p1 = Projections.projectionList();
    p1.add(Projections.alias(Projections.count("OPDV1.OP_VISIT_ID"), "TOTAL"));
    p1.add(Projections.count("OPDV2.FORM"));
    p1.add(Projections.alias(Projections.sqlGroupProjection("date(this_.USER_DATETIME) as createdDate", "createdDate", new String[]{"createdDate"}, new Type[]{StandardBasicTypes.DATE}), "DAT"));
    crit.setProjection(p1);
    return crit.list();

---

Related Topics