需要帮助创建JPA条件查询 [英] Need help creating JPA criteria query

问题描述

我正在使用Glassfish和JSF构建我的第一个Java EE Web应用程序。我对条件查询相当新，我有一个我需要执行的查询，但javaee6教程在示例上似乎有点薄。无论如何，我很难创建查询。

I'm building my first Java EE web application using Glassfish and JSF. I'm fairly new to the criteria query and I have a query I need to perform but the javaee6 tutorial seems a little thin on examples. Anyway, I'm having a hard time creating the query.

目标：我希望将存储的文档数量最多的公司拉出来。
公司与Documents有一个OneToMany关系。
Documents与多个表有一个ManyToOne关系，usertype列区分它们。

Goal: I want to pull the company with the most documents stored. Companies have a OneToMany relationship with Documents. Documents has a ManyToOne relationship with several tables, the "usertype" column distinguishes them.

MySQL查询：

SELECT USERID, COUNT(USERID) AS CNT 
FROM DOCUMENTS 
WHERE USERTYPE="COMPANY" 
GROUP BY USERID 
ORDER BY CNT DESC

谢谢

- 更新 -
根据用户反馈，这是我到目前为止：

--update-- Based on user feedback, here is what I have so far:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Documents> cqry = cb.createQuery(Documents.class);
        //Intersting Stuff
        Root<Documents> root = cqry.from(Documents.class);
        Expression userid = root.get("userID");
        Expression usertype = root.get("userType");
        Expression count = cb.count(userid);
        cqry.multiselect(userid, count);
        Predicate userType = cb.equal(usertype, "COMPANY");
        cqry.where(userType);
        cqry.groupBy(userid);
        cqry.orderBy(cb.desc(count));
        //more boilerplate
        Query qry = em.createQuery(cqry);
        List<Documents> results = qry.getResultList();

我得到的错误是：

Exception Description: Partial object queries are not allowed to maintain the cache or be edited.  You must use dontMaintainCache().

典型错误，对我来说没有任何意义！

Typical error, means nothing to me!

推荐答案

您的查询不返回完整的实体对象，因为您只选择给定表的两个字段（这就是为什么您要获取 yadayadapartialyadayada ）的错误。

Your query doesn't return a complete entity object as you're only selecting two fields of the given table (this is why you're getting an error that says yadayadapartialyadayada).

您的解决方案几乎是正确的，这是您需要更改以使其正常工作部分。

Your solution is almost right, here's what you need to change to make it work—making it partial.

而不是简单的 CriteriaQuery< ...> 你必须创建一个元组 CriteriaQuery< ..> 通过调用 CriteriaBuilder.createTupleQuery（） 。 （基本上，您可以调用 CriteriaBuilder.createQuery（...）并传递 Tuple.class 作为参数。元组是一种通配符实体类。）

Instead of a plain CriteriaQuery<...> you have to create a tuple CriteriaQuery<..> by calling CriteriaBuilder.createTupleQuery(). (Basically, you can call CriteriaBuilder.createQuery(...) and pass Tuple.class to it as an argument. Tuple is a sort of wildcard entity class.)

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq= cb.createTupleQuery();

Root<Documents> root = cq.from(Documents.class);
Expression<Integer> userId = root.get("USERID");
Expression<String> userType = root.get("USERTYPE");
Expression<Long> count = cb.count(userId);

cq.multiselect(userId.alias("USERID"), count.alias("CNT"));
cq.where(cb.equal(userType, "COMPANY");
cq.groupBy(userId);
cq.orderBy(cb.desc(count));

TypedQuery<Tuple> tq = em.createQuery(cq);
for (Tuple t : tq.getResultsList()) {
  System.out.println(t.get("USERID"));
  System.out.println(t.get("CNT"));
}

_{（如果我没有为它们使用别名，则访问 Tuple 的字段会给我一个错误（在中） multiselect（...））。这就是我使用别名的原因，但是使用JPA 2的 Metamodel API 可以更清晰地解决这个问题。规范非常彻底。}）

_{(Accessing fields of a Tuple gave me an error if I didn't use aliases for them (in multiselect(...)). This is why I've used aliases, but this can be tackled more cleanly by using JPA 2's Metamodel API, which is described in the specification quite thoroughly.} )

CriteriaQuery.multiselect（...） 描述使用 Tuple 对象的查询行为更深。

The documentation for CriteriaQuery.multiselect(...) describes the behaviour of queries using Tuple objects more deeply.

这篇关于需要帮助创建JPA条件查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！