在JPQL查询中设置可选参数 [英] Set optional parameters in JPQL Query

问题描述

我得到了要使用这3个可选参数进行过滤的硬币清单.数量和年份)

如何将JPQL中的参数设置为可选?我不想做9"if else"检查它是否为空

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

我得到了函数filtradoMonedas(filterCoins)，该函数使用这3个可选参数过滤对象Moneda(coin)，但是如果存在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.

如果不设置空参数，则正常工作，如果candidad或ano为"则返回错误查询的异常.只希望它是可选的.继承方法:

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;
}

推荐答案

JPQL不支持可选参数.

JPQL doesn't supports optional parameters.

实际上，您应该认为不是参数是可选的，而是条件是可选的.这个想法将引导您创建动态"查询.这将使您接下来从JPQL切换到Criteria API.这将导致您编写如下内容:

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 

这篇关于在JPQL查询中设置可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！