CODEWITHSUNDEEP
jpah2jpql
Gabriel Moretti
Gabriel Moretti

Reputation: 676

How to have optional parameters in a JPQL/JpaRepository?

I'm currently using the following query:

@Query("SELECT new ResumoHistorico(c.id, c.nome, h.isParalisado, COUNT(h), SUM(h.valor), c.isPrioritaria) "
        + "FROM Historico h "
        + "JOIN h.categorias c "
        + "WHERE h.dataRef = :dataRef "
        + "AND h.dataProjecao <= :dataProjecao "
        + "AND c IN :categorias "
        + "AND c.isAgrupador = false "
        + "AND (COALESCE(:agencias, NULL) IS NULL OR h.agencia IN :agencias) "
        + "GROUP BY c.id, c.nome, c.isPrioritaria, h.dataPrevisao, h.isParalisado")

The line + "AND (COALESCE(:agencias, NULL) IS NULL OR h.agencia IN :agencias) " is meant to ignore ":agencias" param when it's null. It works fine in the application, but I have a repo test, in which I insert some dummy data to a H2 database, and it fails with this exception: Caused by: org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "NULL, ?"; SQL statement:

If I change the line to + "AND (:agencias IS NULL OR h.agencia IN :agencias) " the test works, but not the app: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: {vector}

Is there a better way to work with optional parameters and still make use of JpaRepository?

Upvotes: 0

Views: 33

Answers (0)

Related Questions