java - JPA/MySQL - 这个 JPQL 查询有什么问题？

Question

我有实体Post:

@Entity
@Table(name = "post")
@NamedQueries({
    @NamedQuery(name = "getNewestPosts", query = "SELECT p FROM Post p ORDER BY p.date DESC"), // getting resultList ordered by date
    @NamedQuery(name = "getMostVisitedPosts", query = "SELECT p FROM Post p ORDER BY p.visitors DESC"), // ordered by most visited
    @NamedQuery(name = "getMostCommentedPosts", query = "SELECT p FROM Post p ORDER BY SIZE(p.comments) DESC")
})
public class Post implements Serializable {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "post_id", unique = true, nullable = false)
    private Integer id; 

    @Column(name = "post_title", length=300, unique = false, nullable = false)
    private String title;

    @Column(name = "post_date", unique = false, nullable = false)
    private Date date;

    @Column(name = "post_summary", length=1000, unique = false, nullable = true)
    private String summary;

    @Column(name = "post_content", length=50000, unique = false, nullable = false)
    private String content;

    @Column(name = "post_visitors", unique = false, nullable = false)
    private Integer visitors;

    @ManyToOne
    @JoinColumn (name = "user_id", referencedColumnName="user_id", nullable = false)
    private User user;

    @ManyToOne
    @JoinColumn (name = "category_id", referencedColumnName="category_id", nullable = false)
    private Category category;

    @OneToMany(cascade = { ALL }, fetch = EAGER, mappedBy = "post")
    private Set<Comment> comments = new HashSet<Comment>();
...

实体评论:

@Entity
@Table(name = "comment")
public class Comment implements Serializable {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "comment_id", unique = true, nullable = false)
    private Integer id; 

    @Column(name = "comment_title", length=300, unique = false, nullable = false)
    private String title;

    @Column(name = "comment_date", unique = false, nullable = false)
    private Date date;

    @Column(name = "comment_content", length=600, unique = false, nullable = false)
    private String content;

    @ManyToOne
    @JoinColumn (name = "user_id", referencedColumnName="user_id", nullable = false)
    private User user;

    @ManyToOne
    @JoinColumn (name = "post_id", referencedColumnName="post_id", nullable = false)
    private Post post;
...

然后，在PostDAOBean中是以下方法:

public List<Post> getMostCommentedPosts(int page, int postsPerPage){

    Query q = em.createNamedQuery("getMostCommentedPosts");
    q.setFirstResult(page - 1);
    q.setMaxResults(postsPerPage);
    List<Post> resultList = (List<Post>) q.getResultList();

    if (resultList.isEmpty())
        return null;
    else
        return resultList;
}

当我在 servlet 中调用此方法时，出现下一个异常:

[07.01.2014 09:06] Class name: class mbs2.blog.server.session.PostDAOBean, method name: public java.util.List mbs2.blog.server.session.PostDAOBean.getMostCommentedPosts(int,int) started
[07.01.2014 09:06] Class name: class mbs2.blog.server.session.PostDAOBean, method name: public java.util.List mbs2.blog.server.session.PostDAOBean.getMostCommentedPosts(int,int)<openjpa-2.2.0-r422266:1244990 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: "Encountered "SIZE" at character 31, but expected: ["AVG", "COUNT", "KEY", "MAX", "MIN", "SUM", "VALUE", <IDENTIFIER>]." while parsing JPQL "SELECT p FROM Post p ORDER BY SIZE(p.comments) DESC". See nested stack trace for original parse error.
    at org.apache.openjpa.kernel.jpql.JPQLParser.parse(JPQLParser.java:51)
    at org.apache.openjpa.kernel.ExpressionStoreQuery.newCompilation(ExpressionStoreQuery.java:154)
    at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:672)
    at org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:654)
    at org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:620)
    at org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:682)
    at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:589)
    at org.apache.openjpa.persistence.EntityManagerImpl.createNamedQuery(EntityManagerImpl.java:1038)
    at org.apache.openjpa.persistence.EntityManagerImpl.createNamedQuery(EntityManagerImpl.java:102)
    at org.apache.openejb.persistence.JtaEntityManager.createNamedQuery(JtaEntityManager.java:274)
    at mbs2.blog.server.session.PostDAOBean.getMostCommentedPosts(PostDAOBean.java:38)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
...

您可以看到命名查询为:SELECT p FROM Post p ORDER BY SIZE(p.comments) DESC。

异常原因是什么？

编辑好的，我发现函数 SIZE(p.comments) 不能在 JPQL 中使用来进行排序。

使用 JPQL 获取相同数据的替代方法是什么？正确的 JPQL 查询？

编辑尝试使用 JPA Criteria API 实现此查询:

    public List<Post> getMostCommentedPosts(int page, int postsPerPage){

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Post> cq = cb.createQuery(Post.class);
        Root<Post> p = cq.from(Post.class);
        cq.select(p);
        cq.orderBy(cb.desc(???));
                ...???...
}

需要有关 JPA Criteria API 的帮助。

Answer 1

好吧，编译器的咆哮:“在第 31 个字符处遇到“SIZE”，但预期为:[“AVG”、“COUNT”、“KEY”、“MAX”、“MIN”、“SUM”、“VALUE”、] ”。解析 JPQL 时......”

SIZE 是一个不符合 ORDER BY 子句规范的函数:

The ORDER BY clause allows the objects or values that are returned by the query to be ordered. The syntax of the ORDER BY clause is orderby_clause ::= ORDER BY orderby_item {, orderby_item}* orderby_item ::= state_field_path_expression [ASC | DESC] It is legal to specify DISTINCT with MAX or MIN, but it does not affect the result. When the ORDER BY clause is used in a query, each element of the SELECT clause of the query must be one of the following: an identification variable x, optionally denoted as OBJECT(x), a single_valued_association_path_expression, or a state_field_path_expression.

此外:

The SIZE function returns an integer value, the number of elements of the collection. If the collection is empty, the SIZE function evaluates to zero.

<小时/>

试试这个:

public List<Post> getMostCommentedPosts(int page, int postsPerPage){

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Post> cq = cb.createQuery(Post.class);
        Root<Post> p = cq.from(Post.class);
        cq.select(p).where(cb.isNotEmpty(p.get("comments")));
        List list = em.createQuery(cq).getResultList();
        for(Set each : list)
        {
            System.out.println(each.size());
        }

}