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

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 2

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