2

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 {
.......
}
jjd
  • 2,158
  • 2
  • 18
  • 31
  • Did you mark the class with the query on it as either @Entity or @MappedSuperclass? – user2800708 Feb 16 '15 at 16:03
  • @user2800708 I did. The thing is the class had quite a few hql queries which worked without any troubles. The problem occurred only with this native query. I have updated the question with Entity class definition just in case. The idea is: the same exactly native query works just fine if it is created dynamically. I do not think the problem is with the entity definition. I think the problem is with the way NamedNativeQueries are processed – jjd Feb 17 '15 at 16:41

2 Answers2

4

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()?
Zaw Than oo
  • 9,651
  • 13
  • 83
  • 131
  • Not sure I understood you. I'm having troubles with a 'native' SQL query if I use it as a named query with `@NamedNativeQuery` annotation. The strange thing is that if I use exactly the same query right from the application code with `entityManager.createNativeQuery(...)` it works perfectly. Could you please explain how I can find a way to fix my problem based on your post. Thanks. – jjd Oct 19 '12 at 21:18
0

Delete resultClass from @NamedNativeQuery.

See https://stackoverflow.com/a/9763489

For NamedNativeQueries you can only use resultClass when the result actually maps to an Entity.

Community
  • 1
  • 1
apptaro
  • 279
  • 2
  • 8