Actualmente tengo una consulta como esta:
SELECT DISTINCT t.column1, SUM(t2.column2 IS NOT NULL) FROM table t LEFT OUTER JOIN table t2 on table.id = t2.id GROUP BY column1, column2;Estoy tratando de implementar la consulta usando Spring JPA CriteriaBuilder. Veo el método CriteriaBuilder.sum(), pero no veo una forma de aplicar la parte NO ES NULO a la selección. El tipo de datos de Column2 es cadena.
muestra de mi codigo
criteriaBuilder.multiselect(root.get("column1"), cb.sum(root.get("column2")));Solo en MySQL se ejecutaría una consulta de este tipo, debido a las reglas de sintaxis relajadas de MySQL.
En particular, en mysql sum(column2 is not null) es una cuenta , no una suma . La expresión column2 is not null es booleana y en mysql falso es 0 y verdadero es 1, por lo que sumar esta expresión es un truco de mysql para contar el número de veces que columna2 no es nula.
Para convertirlo a sql estándar:
select t.column1, count(t2.column2) from table t left join table t2 on t.id = t2.id group by t.column1 Esto funciona porque count() (y todas las funciones agregadas) ignoran los valores nulos.
Esta versión también corrige la columna errante en la cláusula group by : en cualquier otra base de datos, su consulta habría producido un error de "agrupación por expresión agregada".
Esta consulta producirá el mismo resultado en MySQL que su consulta actual.
Pude encontrar una solución a mi problema. Gracias a @bohemian por ayudarme a escribir una expresión de suma correcta.
final CriteriaBuilder cb = em.getCriteriaBuilder(); final CriteriaQuery<Model1> cq = cb.createQuery(Model1.class); final Root<Model1> root = cq.from(Model1.class); final Join<Model1, Model1> selfJoin = root.join("tableJoinColumn", JoinType.LEFT); selfJoin.on(...); cq.multiselect(root.get("column1"), cb.sum(cb.selectCase() .when(cb.isNull(selfJoin.get("column2")), 0).otherwise(1).as(Long.class))); ...La unión automática me obligó a crear una propiedad adicional en mi modelo. Modelo1.java
/** * Property for LEFT INNER JOIN. */ @OneToOne(fetch = FetchType.LAZY) @JoinColumn(name="id") private Model1 tableJoinColumn;¿Cómo usar JPA CriteriaBuilder selectCase() para que pueda tener Predicate como resultado?