hibernate - How to countDistinct on multiple columns

Question

How can I, using the JPA criteria API do the following:

select count(distinct column1, column2) from table

Doing this on one column/path is simple using CriteriaBuilder.countDistinct, but how can I do this on two paths/columns?

Answer 1

Here is a late answer :-) though I'm not sure if things had changed.

Recently I encountered the very same need, and worked around it using concat, i.e., by concatenating the columns into a pseudo column, then countDistinct on the pseudo column.

But I couldn't use criteriaBuilder.concat because it generated JPQL using || for the concatenation, which Hibernate had trouble with.

Fortunately there's @Formula, thus, I mapped the pseudo column to a field with @Formula:

@Entity
public class MyEntity {
  @Column(name="col_a")
  private String colA;

  @Column(name="col_b")
  private String colB;

  @Formula("concat(col_a, col_b)") // <= THE TRICK
  private String concated;
}

This way I can finally use the concated field for CriteriaBuilder.countDistinct:

//...
Expression<?> exp = criteriaBuilder.countDistinct(entity.get("concated"));
criteriaQuery.select(exp);

TypedQuery<Long> query = entityManager.createQuery(criteriaQuery);
return query.getSingleResult();

I wish JPA would (or hopefully already) support countDistinct with multiple columns, then all these mess could have been avoided.