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

Could you edit you question to add the stack trace and the code of the entities Announcement and Commentable? — Pablo, May 18 '12 at 12:13

JB Nizet · Answer 1 · 2012-05-18T12:41:32.243

1

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

edited May 18 '12 at 12:41

answered May 18 '12 at 12:20

JB Nizet

678,734
91
1,224
1,255

The problem is that the `Announcement` extends `Commentable`. I don't have any ToOne relationship in them. – Mr.J4mes May 18 '12 at 12:22
The you don't need any join: an Announcement IS A Commentable. I'll edit my answer. – JB Nizet May 18 '12 at 12:39

score 1 · Accepted Answer · answered May 18 '12 at 12:28

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

answered May 18 '12 at 12:28

Pablo

3,655
2
30
44

The problem is that I use `InheritanceType.JOINED`. Hence, the Announcement table will not have the column `createdDate`. I tried your solution earlier and GlassFish threw me the No Column SQLException. – Mr.J4mes May 18 '12 at 12:31
@Mr.J4mes Is this perhaps a bug? EclipseLink should do the join, as far as I know. – Pablo May 18 '12 at 12:49
Thanks for helping me. I messed up the creating table process earlier and it caused this problem. – Mr.J4mes May 18 '12 at 13:10

EJB QL Syntax error

2 Answers2