JPA/MySQL-此JPQL查询出了什么问题? [英] JPA/MySQL - Whats wrong with this JPQL query?

问题描述

我有实体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>();
...

实体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中使用以下方法:

Then, in the PostDAOBean is following method:

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中调用此方法时，出现下一个异常:

When I call this method in servlet I get next exception:

[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.

You can see that named query is: SELECT p FROM Post p ORDER BY SIZE(p.comments) DESC.

异常原因是什么?

编辑 好的，我看到不能在JPQL中使用函数SIZE(p.comments)来对其进行排序.

EDIT OK, I see that function SIZE(p.comments) can not be used in JPQL to be ordered by it.

使用JPQL获取相同数据的另一种方法是什么?正确的JPQL查询?

What is the alternative way of getting same data with JPQL? Proper JPQL query?

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

EDIT Trying to implement this query with 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的帮助.

Need help with JPA Criteria API.

推荐答案

编译器的吠叫声:在字符31处遇到"SIZE"，但预期:["AVG"，"COUNT"，"KEY"，"MAX" ，"MIN"，"SUM"，"VALUE"，].同时解析JPQL...."

Well, compiler's barking: "Encountered "SIZE" at character 31, but expected: ["AVG", "COUNT", "KEY", "MAX", "MIN", "SUM", "VALUE", ]." while parsing JPQL...."

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

SIZE is a function that doesn't conform to the specification of the ORDER BY clause:

ORDER BY子句允许对查询返回的对象或值进行排序. ORDER BY子句的语法是 orderby_clause :: = ORDER BY orderby_item {，orderby_item} * orderby_item :: = state_field_path_expression [ASC | DESC] 用MAX或MIN指定DISTINCT是合法的，但不会影响结果. 在查询中使用ORDER BY子句时，查询的SELECT子句的每个元素必须为以下之一:标识变量x(可选表示为OBJECT(x))，single_valued_association_path_expression或state_field_path_expression.

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.

此外:

SIZE函数返回一个整数值，即集合中元素的数量.如果集合为空，则SIZE函数的计算结果为零.

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.

---

尝试一下:

---

Try this:

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

}

这篇关于JPA/MySQL-此JPQL查询出了什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！