CODEWITHSUNDEEP
jpanativequery
jjd
jjd

Reputation: 2288

jpa and native queries

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

Upvotes: 2

Views: 13817

Answers (2)

apptaro
apptaro

Reputation: 289

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.

Upvotes: 0

Zaw Than oo
Zaw Than oo

Reputation: 9935

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()?

Upvotes: 5

Related Questions