I faced with a strange thing with native queries:
If I'm trying to use named native query like the following
@NamedNativeQuery(name = "native_count_avg_guest_quizzes", resultClass = java.math.BigDecimal.class, query = "select avg(c) as r from ((select count(*) as c from user_quiz uq join user u on (uq.user_id = u.id) where u.status = 'temporary' group by u.id) as res)")
The application cannot run and I have
org.hibernate.HibernateException: Errors in named queries: native_count_avg_guest_quizzes_
But the same query works fine if I do not use NamedNativeQuery and merely create a dynamic native query like the following:
entityManager.createNativeQuery(
"select avg(c) as r from ((select count(*) as c from user_quiz uq join user u on (uq.user_id = u.id) where u.status = 'temporary' group by u.id) as res)")
.getSingleResult()
Why? What I'm doing wrong with NamedNativeQuery? Thanks
Update: Entity class is as following
@Entity
@Table(name = "user_quiz")
@NamedNativeQueries({
@NamedNativeQuery(name = "native_count_avg_guest_quizzes", resultClass = java.math.BigDecimal.class, query = "select avg(c) as r from ((select count(*) as c from user_quiz uq join user u on (uq.user_id = u.id) where u.status = 'temporary' group by u.id) as res)")
})
@NamedQueries({
@NamedQuery(name = "list_clients_quizzes", query = "select uq from UserQuiz uq where uq.quiz.client.id = :clientId"),
.......
})
public class UserQuiz extends Measurable {
.......
}
Answer
createNativeQuery is used for native SQL query language that mean DB can understand and execute that query (Eg, select * from some_table where id = '0001');
It may cause DB dependency. Now you are using JPQL language, that's why, use createQuery() or createNameQuery() with @NameQuery annotation sing.
Example :
@NameQuery
@NamedQuery(name = "findAllEmployee", query = "select e from Employee e")
@Entity
public class Employee {
@Id int id;
String name;
// ...
}
Query query = em.createNamedQuery("findAllEmployee");
query.getResultList()
Dynamic
Query query = em.createQuery("select e from Employee e");
query.getResultList()?
*Native
Query query = em.createNativeQuery("SELECT * FROM EMPLOYEE_TBL");
query.getResultList()?