EJB QL Syntax error

Question

In my app, I have the following 2 entities:

@Entity
@Inheritance(strategy=InheritanceType.JOINED)
public class Commentable implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    protected Long id;
    protected long createdDate;
    ...
}

@Entity
public class Announcement extends Commentable implements Serializable { 

    private int    type;  
    private String title;
    @Column(columnDefinition = "MEDIUMTEXT")
    private String content;
    ...
}

When I try to get some rows from the Announcement table, I keep getting syntax error on the following line:

Query q = em.createQuery("SELECT A FROM Announcement A JOIN Commentable C ON A.id = C.id WHERE A.type=:type ORDER BY C.createdDate DESC");

This is the stacktrace:

Caused by: Exception [EclipseLink-8023] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [SELECT A FROM Announcement A JOIN Commentable C ON A.id = C.id WHERE A.type=:type ORDER BY C.createdDate DESC].
Internal Exception: org.eclipse.persistence.internal.libraries.antlr.runtime.EarlyExitException

I'd be very grateful if you could tell me what I have done wrong here.

Best regards, James Tran

Answer 1

If Announcement extends Commentable, you shouldn't need a join:

select a from Announcement a where a.type = :type order by a.createdDate desc

Answer 2

There is no ON clause in JPQL. It's not necessary because you can only join two entities that have an association between them, and the mapping of the association already contains the necessary information necessary to know on which columns the join must be made. So the following is sufficient:

SELECT a FROM Announcement a JOIN a.commentable c
WHERE a.type = :type ORDER BY c.createdDate DESC

This assumes that you have a ToOne association between Announcement and Commentable.

EDIT: Announcement extends Commentable. So you don't need any join. The JPA engine does the join for you (if a join is necessary, depending on the inheritance strategy):

SELECT a FROM Announcement a WHERE a.type = :type ORDER BY a.createdDate DESC