Hibernate count issue using 2 fields

Question

In a SparePart table the uniqueness of the rows is given from a combination of materialName and materialNumber , what i want to do is get the total number of Spare Parts avalaible now.

I have tried using count(distinct ...) but it only works for one field not two.

The SQL query(using hibernate query language) with an inner query would look like this :

select count(*) from (
     select distinct materialName, materialNo from SparePart
) as col

score 4 · Accepted Answer · answered Jun 15 '12 at 12:43

Use a subquery+group by and then filter for the first ID that represents the particular group, finally count all those that match the filter:

SELECT COUNT(sp2) FROM SparePart sp2 WHERE EXISTS
  (SELECT sp.materialName, sp.materialNo
          FROM SparePart sp
          GROUP BY sp.materialName, sp.materialNo
          HAVING MIN(sp.id)=sp2.id)

score 1 · Answer 2 · answered Jul 21 '15 at 08:11

You can use hibernate dialect for this task. To do this, create your own dialect, that extends dialect of DB what used (list of all dialects), and then register new function. For example, I use MySQL 5 with InnoDB engine:

public final class MyDialect extends MySQL5InnoDBDialect {
    public MyDialect() {
        super();
        registerFunction("pairCountDistinct", new SQLFunctionTemplate(LongType.INSTANCE, "count(distinct ?1, ?2)"));
    }
}

After then add new property in persistence.xml:

<property name="hibernate.dialect" value="com.example.dialect.MyDialect" />

And now you can use this function:

// some init actions
final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Long> criteria = builder.createQuery(Long.class);
final Root<SparePart> root = criteria.from(SparePart.class);
criteria.select(builder.function("pairCountDistinct", Long.class, root.get(SparePart_.materialName), root.get(SparePart_.materialNo)));
final long result = entityManager.createQuery(criteria).getSingleResult();
// some close actions

Nayan Wadekar · Answer 3 · 2012-06-16T14:51:45.917

You can try it with Criteria API.

ProjectionList projectionList = Projections.projectionList();        
projectionList.add(Projections.property("materialName"));
projectionList.add(Projections.property("materialNo"));

Criteria criteria = session.createCriteria(SparePart.class); 
criteria.setProjection(Projections.distinct(projectionList);  
criteria.setProjection(Projections.rowCount());

Long results = criteria.uniqueResult();

Also, it may need some modifications as I haven't tried it, mapped according to your query.

Hibernate count issue using 2 fields

3 Answers3