Set optional parameters in JPQL Query

Question

I got this list of coins that i want to filter with that 3 optional parameters(Currency, quantity and year)

How can I set parameters in JPQL as optional? I dont want to do 9 "if else" for checking if its null

I got the function filtradoMonedas (filterCoins) that filter the object Moneda(coin) using that 3 OPTIONAL parameters but its not working if there´s a null parameter.

This just work well if dont set empty parameters, if cantidad or ano is "" returns exception of bad query. Just want it as an optional. Heres the method:

public List<Moneda> filtradoMonedas(Divisa divisa, BigDecimal cantidad, 
        BigDecimal ano){

    EntityManager em = getEntityManager();

    Query consulta = em.createQuery("SELECT m FROM Moneda m "
            + "WHERE m.divisa = :divisa "
            + "AND m.cantidad= :cantidad "
            + "AND m.ano = :ano");

    consulta.setParameter("divisa", divisa);
    consulta.setParameter("cantidad", cantidad);
    consulta.setParameter("ano", ano);

    List<Moneda> filtradoMonedas = (List<Moneda>) consulta.getResultList();
    // sincronizar los objetos devueltos con la base de datos
    for (Moneda m : filtradoMonedas) {
        em.refresh(m);
    }

    em.close();
    return filtradoMonedas;
}

Answer 1

this for native queries, Simply pass null values when parameter values need to be optional. check whether the parameter is null or not in the query.

"SELECT m FROM Moneda m WHERE"
+ "(:divisa is null || m.divisa = :divisa) "
+ "AND (:cantidad is null || m.cantidad= :cantidad) "
+ "AND (:ano is null || m.ano = :ano)"

Answer 2

In my case I had the problem that my optional parameter was a List<String> and the solution was the following:

@Query(value = "SELECT *
                FROM ...
                 WHERE (COLUMN_X IN :categories OR COALESCE(:categories, null) IS NULL)"
, nativeQuery = true)
List<Archive> findByCustomCriteria1(@Param("categories") List<String> categories);

This way:

• If the parameter has one or more values it is selected by the left side of the OR operator
• If the parameter categories is null, meaning that i have to select all values for COLUMN_X, will always return TRUE by the right side of the OR operator

Why COALESCE and why a null value inside of it?

Let's explore the WHERE clause in all conditions:

Case 1: categories = null

(COLUMN_X IN null OR COALESCE(null, null) IS NULL)

The left part of the OR will return false, while the right part of the OR will always return true, in fact COALESCE will return the first non-null value if present and returns null if all arguments are null.

Case 2: categories = ()

(COLUMN_X IN null OR COALESCE(null, null) IS NULL)

JPA will automatically identify an empty list as a null value, hence same result of Case 1.

Case 3: categories = ('ONE_VALUE')

(COLUMN_X IN ('ONE_VALUE') OR COALESCE('ONE_VALUE', null) IS NULL)

The left part of the OR will return true only for those values for which COLUMN_X = 'ONE_VALUE' while the right part of the OR will never return true, because it is equals to 'ONE_VALUE' IS NULL (that is false).

Why the null as second parameter? Well, that's because COALESCE needs at least two parameters.

Case 4: categories = ('ONE_VALUE', 'TWO_VALUE')

(COLUMN_X IN ('ONE_VALUE', 'TWO_VALUE') OR COALESCE('ONE_VALUE', 'TWO_VALUE', null) IS NULL)

As in Case 3, the left part of the OR operator will select only the rows for which COLUMN_X is equale to 'ONE_VALUE' or 'TWO_VALUE'.

Answer 3

After reading Ilya Dyoshin's comment, and the smart thinking behind "you should think that not parameters are optional but rather conditions are optional" I decided to go my own way by using JPQL with @Query annotation, and creating one dynamic SQL query which works beautifully.

The key is to apply some SQL logic to make the conditions optional rather than the parameters:

    @Query("SELECT a " +
           "FROM amazing " +
           "WHERE (:filterSuperAwesome IS NULL " +
                        "OR a.filterSuperAwesome = :filterSuperAwesome)"); 

    List<FilterSuperAwesome> filterAwesomeORSuperAwesome(
                    @Param("filterSuperAwesome ") FilterSuperAwesome filterSuperAwesome);

Note how here ☝️, I use an OR statement based on the fact that my parameter can present two forms, an instance of FilterSuperAwesome or NULL. When NULL, the condition is always True, as if it wasn't there.

This works just fine inside a JpaRepository class of a JHipster project.

Answer 4

JPQL doesn't supports optional parameters.

Actually you should think that not parameters are optional but rather conditions are optional. This idea will lead you to creating "dynamic" queries. And this in place will lead you next to switch from JPQL to Criteria API. And this will lead you to writting something like this:

    // Actually can be generated during build, and thus can be ommited
    @StaticMetamodel(Moneda.class)
    abstract class Moneda_ {
        public static volatile SingularAttribute<Moneda, BigDecimal> cantidad;
        public static volatile SingularAttribute<Moneda, Divisia> divisia;
        public static volatile SingularAttribute<Moneda, BigDecimal> ano;
    }

    final CriteriaBuilder cb = em.getCriteriaBuilder();

    final CriteriaQuery<Moneda> cq = cb.createQuery(Moneda.class);
    final Root<Moneda> root = cq.from(Moneda.class);

    Set<Predicate> predicates = new HashSet<>(3);
    if (cantidad != null) {
        predicates.add(cb.equal(root.get(Moneda_.cantidad), cantidad));
    }

    if (ano != null) {
        predicates.add(cb.equal(root.get(Moneda_.ano), ano));
    }

    if (divisia != null) {
        predicates.add(cb.equal(root.get(Moneda_.divisia), divisia));
    }

    cq.where(predicates.toArray(new Predicate[predicates.size()]));

    em.createQuery(cq).getResultList();

    // and do whatever you want