java - JPA2 Criteria查询空结果

Question

此代码使用 JPA2 标准查询返回一个空结果列表:

CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<VwReplPrevia> query = criteriaBuilder.createQuery(VwReplPrevia.class);
Root<VwReplPrevia> root = query.from(VwReplPrevia.class);
query.multiselect(root.get(VwReplPrevia_.codigoCentroCustos), 
            root.get(VwReplPrevia_.numeroCartaoPostagem),
            root.get(VwReplPrevia_.codigoServico),
            root.get(VwReplPrevia_.quantidadeItems),
            root.get(VwReplPrevia_.valorServico));
TypedQuery<VwReplPrevia> typedQuery = getEntityManager().createQuery(query);
String contrato       = StringUtils.trim(paginador.getEntityBean().getContrato());
String centroCustos   = StringUtils.trim(paginador.getEntityBean().getCentroCustos());
String cartaoPostagem = StringUtils.trim(paginador.getEntityBean().getCartaoPostagem());
String servico        = StringUtils.trim(paginador.getEntityBean().getServico());
query.where(criteriaBuilder.equal(root.get(VwReplPrevia_.numeroContrato), contrato));
    if (StringUtils.isNotBlank(centroCustos)) {
        criteriaBuilder.and(criteriaBuilder.equal(root.get(VwReplPrevia_.codigoCentroCustos), "9912231583"));
    }
    if (StringUtils.isNotBlank(cartaoPostagem)) {
        criteriaBuilder.and(criteriaBuilder.equal(root.get(VwReplPrevia_.numeroCartaoPostagem), cartaoPostagem));
    }
    if (StringUtils.isNotBlank(servico)) {
        criteriaBuilder.and(criteriaBuilder.equal(root.get(VwReplPrevia_.codigoServico), servico));
    }

    query.orderBy(criteriaBuilder.asc(root.get(VwReplPrevia_.codigoCentroCustos)));
    query.groupBy(root.get(VwReplPrevia_.codigoCentroCustos), 
            root.get(VwReplPrevia_.numeroCartaoPostagem), 
            root.get(VwReplPrevia_.codigoServico),
            root.get(VwReplPrevia_.quantidadeItems),
            root.get(VwReplPrevia_.valorServico));
List<VwReplPrevia> resultado = typedQuery.getResultList();

相同的查询，使用原生查询，返回正确的结果数。

String sql = "SELECT vwreplprev0_.sdan8  AS col_0_0_, " +
   "vwreplprev0_.sdcnid AS col_1_0_, " +
   "vwreplprev0_.sdlitm AS col_2_0_, " +
   "vwreplprev0_.sduorg AS col_3_0_, " +
   "vwreplprev0_.sdaexp AS col_4_0_ "  +
"FROM   sfe_view.vw_f4211_repl_previa vwreplprev0_ " +
"WHERE  vwreplprev0_.sddmct = '9912231583' " +
"GROUP  BY vwreplprev0_.sdan8, " +
      "vwreplprev0_.sdcnid, " +
      "vwreplprev0_.sdlitm, " +
      "vwreplprev0_.sduorg, " +
      "vwreplprev0_.sdaexp " +
"ORDER  BY vwreplprev0_.sdan8 ASC  ";
    Collection<PreviaFaturaBean> result =    getEntityManager().createNativeQuery(sql).getResultList();
    paginador.setColecaoDeRegistros(result);

我不知道出了什么问题。我通过条件查询实现的唯一引用是 http://docs.oracle.com/javaee/6/tutorial/doc/gjivm.html ，有人可以给我提示吗？

Answer 1

以下部分看起来错误，与 native 查询不匹配:

query.where(criteriaBuilder.equal(root.get(VwReplPrevia_.numeroContrato), contrato));
if (StringUtils.isNotBlank(centroCustos)) {
    criteriaBuilder.and(criteriaBuilder.equal(root.get(VwReplPrevia_.codigoCentroCustos), "9912231583"));
}

应该是这样

query.where(criteriaBuilder.equal(root.get(VwReplPrevia_.numeroContrato), "9912231583"));
if (StringUtils.isNotBlank(centroCustos)) {
    criteriaBuilder.and(criteriaBuilder.equal(root.get(VwReplPrevia_.codigoCentroCustos), centroCustos));
}

如果这不是问题，请查看 JPA 引擎在日志中生成了哪个 SQL 查询，并将其与 native 查询进行比较以找出差异所在。