jpa - Counting Null values in JPQL

Question

i want to count null values in JPQL but count (case when "column" is null then.. end) doesn't work it work only on MYSQL ,i don't want to use count(*) what is the solution ??

String jpql ="select c.commande.user.login ,count(CASE WHEN c.commande.commandeTms is Null THEN 1 else 0 END) AS count1   from Designation c GROUP BY c.commande.user.login";

here my database

Answer 1

Here you go : I have used CriteriaBuilder to count them ... as a result, you get a List of Tuple containing the user login and count elements ... you may want to change names and "count cases" according to your needs :

public List<Tuple> test() {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
    Root<Designation> designation = cq.from(Designation.class);

    Join<Designation, Commande> commande = designation.join("commande");
    Join<Commande, User> user = commande.join("user");

    Expression expr = commande.get("commandeTms");

    cq.multiselect(user.get("login").alias("login"), 
            cb.sum(cb.<Long>selectCase().when(expr.isNotNull(), 1L).otherwise(0L)).alias("NotNull"),
            cb.sum(cb.<Long>selectCase().when(expr.isNull(), 1L).otherwise(0L)).alias("Null"));
    cq.groupBy(user.get("login"));

    Query query = entityManager.createQuery(cq);
    return query.getResultList();
}