What is wrong with my NamedQuery

Question

What is wrong with this NamedQuery?

 @NamedQuery(name = "Queries.findQueryIdsByRoleOfSameSid",
 query = "SELECT q "+
         "FROM Queries q "+
         "WHERE ((q.issueRole = :issueRole) AND "+
         "(SELECT COUNT(*) FROM Queries qb WHERE ( (q.sessionId=qb.sessionId) AND (q.issueRole=qb.issueRole))) IS NOT EMPTY)"+
         "ORDER BY q.reqTime "),

and this is the Queries Entity attributes:

public class Queries implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "query_id")
    private Integer queryId;
    @Basic(optional = false)
    @NotNull
    @Column(name = "issue_time")
    private int issueTime;
    @Basic(optional = false)
    @NotNull
    @Column(name = "issue_role")
    private int issueRole;
    @Basic(optional = false)
    @NotNull
    @Column(name = "req_time")
    @Temporal(TemporalType.TIMESTAMP)
    private Date reqTime;
    @Basic(optional = false)
    @NotNull
    @Column(name = "sucess_flag")
    private int sucessFlag;
    @Size(max = 50)
    @Column(name = "session_id")
    private String sessionId;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "queriesQueryId")
    private Collection<Statement> statementCollection;

I am getting this error, and the modul is not deployed:

Exception Description: Syntax error parsing the query 
[Queries.findQueryIdsByRoleOfSameSid: SELECT q FROM Queries q WHERE 
((q.issueRole = :issueRole) AND (SELECT COUNT(*) FROM Queries qb WHERE 
( (q.sessionId=qb.sessionId) AND (q.issueRole=qb.issueRole))) IS NOT EMPTY)ORDER 
BY q.reqTime ], line 1, column 44: syntax error at [=].
Internal Exception: MismatchedTokenException(82!=84). Please see server.log for 
more details.`

FYI, I am working with netbeans 7.2.1, default org.eclipse.persistence.jpa.PersistenceProvider(JPA 2.0) in persistence.xml

I even tried to upgrade the eclipsLink version to 2.6, but failed. I don't know why, I exactly followed these instrctions How do you use EclipseLink 2.3 as persistence provider in NB 7? And also this one Adding the latest EclipseLink version to a Netbeans project?

Please help me out

line 1 column 44 of the query is what ? there is the problem ... — Neil Stockton, May 15 '16 at 14:51
as COUNT(*) expression that evaluates to number,instead of IS NOT EMPTY , I changed it to > 1 . But still no success :( Any other suggestion? — maria, May 16 '16 at 04:29
@maria BTW I spotted that before `ORDER BY` there's not space... Even though it may be not the cause of problem - add space just for sake of clarity. — Darek, May 16 '16 at 05:42

score 2 · Answer 1 · answered May 15 '16 at 14:56

2

Your inner query is wrong. SELECT COUNT(*) returns a Long, so you can't use IS NOT EMPTY (it's meant for collections).

Use > 0 instead.

answered May 15 '16 at 14:56

Kayaman

72,141
5
83
121

Darek · Answer 2 · 2016-05-16T05:21:00.527

Check out this formatted version of your query:

SELECT q 
FROM Queries q 
WHERE (
        (
            q.issueRole = :issueRole
        ) 
        AND 
        (
            SELECT COUNT(*) 
            FROM Queries qb 
            WHERE (
                (
                    q.sessionId=qb.sessionId
                ) 
                AND 
                (
                    q.issueRole=qb.issueRole
                )
            )
        ) IS NOT EMPTY
    )
ORDER BY q.reqTime

IS NOT EMPTY part refers to COUNT(*) expression that evaluates to number. From docs:

The IS [NOT] EMPTY expression applies to collection-valued path expressions. It checks whether the particular collection has any associated values.

In your query that part says that NUMBER IS NOT EMPTY. IS NOT EMPTY should follows set of rows result - not a number! Change IS NOT EMPTY to simple condition like so:

...
(
    SELECT COUNT(*) 
    FROM Queries qb 
    WHERE (
        (
            q.sessionId=qb.sessionId
        ) 
        AND 
        (
            q.issueRole=qb.issueRole
        )
    )
) > 0 -- instead of IS NOT EMPTY
...

score 1 · Answer 3 · answered May 15 '16 at 14:59

1

I think the problem is there is no space after IS NOT EMPTY).

Please check all the spaces since you are concatenating

answered May 15 '16 at 14:59

shankarsh15

1,947
1
11
16

What is wrong with my NamedQuery

3 Answers3