I am using mysql 5.5 with openjpa 2.3.0.
I have entities with namedQueries (generated in netbeans - I would like to be able to use this), for example:
@NamedQuery(name = "User.findAll", query = "SELECT u FROM User u")
@NamedQuery(name = "User.findByGender", query = "SELECT u FROM User u WHERE u.gender = :gender")
I am creating restfull aplication with paged results. I would like to return for every paged result the Content-Range header as 1-20/250 where 20 is pagesize, 250 total count.
I tried to create a query
entityManager.createNativeQuery("SELECT count(1) FROM (" + namedQuery.toString() + ") as foo;");
where I could dynamicaly insert any named query and return the count without returning the result list -> it should be faster.
When I execute this, exception occurs
SQL state 42S22: Unknown column 'u' in 'field list'
Executing the query itself in entitymanager is ok. Can I use the entity manager or criteria builder to create a query for counting results without returning the result list (and without writing for every namedQuery a count duplicate)? thank you for helping.
You are mixing JPQL with native queries. JPQL says SELECT u FROM entity u, SQL would be SELECT * FROM entity u or SELECT col1,col2,col3 FROM entity u
You could write a JPQL named query counting the stuff, e.g. SELECT COUNT(u) FROM entity u. A getSingleResult() would then return an Object[], whose first element contains the count.
Not nice but working. Why do You have to query for the number anyway? Pagination means next = lastindex+pagesize. if next < lastindex+pagesize, the end is reached.