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.

Answer 2

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<SomeEntity> root = criteria.from(SomeEntity.class);
criteria.select(builder.function("pairCountDistinct", Long.class, root.get(SomeEntity_.field1), root.get(SomeEntity_.field2)));
final long result = entityManager.createQuery(criteria).getSingleResult();
// some close actions

Answer 3

As others have pointed out, build up an Expression consisting of all desired columns using the concat method, but then you can just pass that Expression to the countDistinct method.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<MyEntity> root = cb.from(MyEntity.class);
// build up an expression with multiple columns
Expression exp = root.get("column1");
exp = cb.concat(exp, root.get("column2");
exp = cb.concat(exp, root.get("column3");

cq.select(cb.contDistinct(exp));

var result = em.createQuery(cq).getSingleResult();
var count = Math.toIntExact(result);

Answer 4

Seems like there is no way to do this with JPA2