How to set parameters for a Named Query

Question

Hi I have a Named Query

@NamedQuery(name = "StudyplanCategory.findByStatusAndLimit",
            query = "SELECT s FROM StudyplanCategory s WHERE 
            s.status =:status LIMIT s.start=:start,s.end=end")

I want to set the limit like this:

@NamedQuery(name = "StudyplanCategory.findByStatusAndLimit", 
            query = "SELECT s FROM StudyplanCategory s WHERE 
            s.status =:status LIMIT s.start=:start,s.end=end")

But this is showing error at the start up of the Server. I am using the below code to call the query in the DAO class:

Query query = entityManager.createNamedQuery("StudyplanCategory.findByStatusAndLimit");
int end=(start*pageNumber);
query.setParameter("status", status);
query.setParameter("start", start);
query.setParameter("end", end);
return (List<StudyplanCategory>) query.getResultList();

Start and End Parameters needs to be set. Please Help.

Solution

As @DataNucleus said, LIMIT is not a valid keyword in JPQL. That's not the way to specify how many rows to return. This is how you do it:

@NamedQuery(name = "StudyplanCategory.findByStatusAndLimit", 
        query = "SELECT s FROM StudyplanCategory s WHERE 
                    s.status =:status")

And this would be the code to call the named query:

Query query = entityManager.createNamedQuery("StudyplanCategory.findByStatusAndLimit");
int end=(start*pageNumber);
query.setParameter("status", status);
query.setFirstResult(start);
query.setMaxResults(end - start);
return (List<StudyplanCategory>) query.getResultList();

Comments

[Praveen V]

Hi yes It is working already. I have a Lazy Loading data table in primefaces and i want this named query to be executed wit this.

[Praveen V]

Hi @Damian I used the same and it is working.But the pagination but Next is not getting activated when the DataTable is loaded first.The reason for this is that it is fetching only limited data required to load the first page So the Next Button is not active.Please help.

[damian]

I don't understand a word of what you are saying.. the question is about jpa, and now you are talking about a datatable.. create a new question for that, explaining what technology you are using, and also some code snippets..

[Praveen V]

Hi have posted it as a seperate question please check.