CODEWITHSUNDEEP
javamysqlhibernatejpanetbeans
maria
maria

Reputation: 19

What is wrong with my NamedQuery

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

Upvotes: 1

Views: 191

Answers (3)

Darek
Darek

Reputation: 570

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

Upvotes: 2

shankarsh15
shankarsh15

Reputation: 1967

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

Please check all the spaces since you are concatenating

Upvotes: 1

Kayaman
Kayaman

Reputation: 73578

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.

Upvotes: 2

Related Questions