MySQLSyntaxErrorException generated by Hibernate when using count of children in query

Question

I have a query in which I want to retrieve only the Message objects that have one or more Link children.

My Message class is:

package zzz.community.domain;

/**
* Entity implementation class for Entity: Message.
*/
@Entity
@Table(name = "MESSAGE")
public class Message implements Serializable {

private static final long serialVersionUID = 2252587276028343749L;

public static final int MAX_LENGTH_TITLE = 255;

public static final int MAX_LENGTH_TEXT = 200000;

@Id @GeneratedValue
private Long id;

private String title;

@Column(columnDefinition = "TEXT")
private String text;

/**
 * The date at which the message was posted.
 */
private Date creationDate;

/**
 * The profile id of the user that posted this message.
 */
@OneToOne
private Profile creator;

/**
 * The id of the circle the message was posted to.
 */
@OneToOne
private Circle circle;

/**
 * When true, only members of the circle may see this message.
 */
private Boolean membersOnly;

@OneToMany(mappedBy="message")
@OrderBy("id DESC")
private Set<Link> links;

public Long getId() {
    return id;
}

public void setId(final Long id) {
    this.id = id;
}

public String getText() {
    return text;
}

public void setText(final String text) {
    this.text = text;
}

public String getTitle() {
    return title;
}

public void setTitle(final String title) {
    this.title = title;
}

public Date getCreationDate() {
    return creationDate;
}

public void setCreationDate(final Date creationDate) {
    this.creationDate = creationDate;
}

public Profile getCreator() {
    return creator;
}

public void setCreator(final Profile creator) {
    this.creator = creator;
}

public Circle getCircle() {
    return circle;
}

public void setCircle(final Circle circle) {
    this.circle = circle;
}

public Boolean getMembersOnly() {
    return membersOnly;
}

public void setMembersOnly(final Boolean membersOnly) {
    this.membersOnly = membersOnly;
}

public Set<Link> getLinks() {
    return this.links;
}

public void setLinks(final Set<Link> links) {
    this.links = links;
}

public void addLink(final Link link) {
    this.links.add(link);
}

}

The JPA query I wrote is:

 select m from Message m 
 where m.circle = :circle 
 and count(m.links) > 0
 order by m.creationDate DESC

Hibernate parses this code correctly (No AST Exception) but the resulting mysql statement is not correct and throws a MySQLSyntaxErrorException

09:01:22.153 [http-apr-8080-exec-3] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
09:01:22.154 [http-apr-8080-exec-3] DEBUG o.h.hql.ast.QueryTranslatorImpl - HQL: select m from zzz.domain.Message m where m.circle = :circle and count(m.links) > 0 order by m.creationDate DESC
09:01:22.154 [http-apr-8080-exec-3] DEBUG o.h.hql.ast.QueryTranslatorImpl - SQL: select message0_.id as id3_, message0_.circle_id as circle6_3_, message0_.creationDate as creation2_3_, message0_.creator_id as creator7_3_, message0_.membersOnly as membersO3_3_, message0_.text as text3_, message0_.title as title3_ from MESSAGE message0_ cross join LINK links1_ where message0_.id=links1_.message_id and message0_.circle_id=? and count(.)>0 order by message0_.creationDate DESC

Again, the resulting MySQL query:

select 
  message0_.id as id3_
, message0_.circle_id as circle6_3_
, message0_.creationDate as creation2_3_
, message0_.creator_id as creator7_3_
, message0_.membersOnly as membersO3_3_
, message0_.text as text3_
, message0_.title as title3_ 
from MESSAGE message0_ 
cross join LINK links1_ 
where message0_.id=links1_.message_id 
and   message0_.circle_id=? 
and   count(.)>0 
order by message0_.creationDate DESC

The workaround, of course, is to remove the selection criterion

count(m.links) > 0

and replace it with some code in java. But I hope to get a better suggestion of one of you.
Thanks in advance,

Henk

Answer 1

In JPQL this can be done for example with one of the following constructs:

• Empty collection comparison expression IS [NOT] EMPTY
• SIZE function

With first one following:

count(m.links) > 0

is replaced by:

m.links IS NOT EMPTY

And second approach is:

SIZE(m.links) > 0