executing case expressions in jpql

Question

I'm learning JPA and doing some hands one with JPQL. I am having trouble in CASE expressions. For example, this query,

Query caseQuery = em
            .createQuery("SELECT t , CASE WHEN t.salary = 20000 THEN '20k' WHEN t.salary = 40000 THEN '40k' ELSE 'No salary' END FROM Teacher t");

and executing it using

List<Teacher> teachers = (List<Teacher>) caseQuery.getResultList();

but whenever I try to print the results out, I'm getting ClassCastException that Object cannot be converted to Teacher

I've tried using TypedQuery for Teacher but it didn't work. Could you experts please throw some light on executing this CASE statements in JPQL?

I think that's case was introduced in JPA 2.0 - see http://stackoverflow.com/questions/427447/is-there-such-thing-case-expression-in-jpql — Leo, Apr 04 '14 at 11:49
@Leo I've already seen that question, that explains how to write CASE expressions and even books and official docs explain that. I'm having trouble in executing CASE query and getting results. — Prasad Kharkar, Apr 04 '14 at 11:51
I see. In your case, you're retrieving more than a Teacher object, so I think you have to return something else than a Teacher object in your query — Leo, Apr 04 '14 at 11:58
Maybe you could use something like this http://stackoverflow.com/questions/2355728/jpql-create-new-object-in-select-statement-avoid-or-embrace — Leo, Apr 04 '14 at 11:59
@Leo, well I'm selecting only Teacher entities but the introduction of CASE expression causes problems, this is where I'm not getting how it works. — Prasad Kharkar, Apr 04 '14 at 12:15
You are selecting a `Teacher` and a `String`, so your result is going to be `List — DannyMo, Apr 07 '14 at 14:57

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

The reason is due to multiple select expressions in your query and not due to CASE Expression ,I believe with multiple select expressions the result will be Object[] rather than translated to the Entity. Some references JPA Tutorial ;JPQL. A related query already in stack overflow.

edited May 23 '17 at 12:21

Community

1
1

answered May 13 '14 at 21:57

firefox784

657
1
6
18

score 0 · Answer 2 · answered May 14 '14 at 13:35

You should modify

List<Teacher> teachers = (List<Teacher>) caseQuery.getResultList();

to

List<Object[]> teachers = (List<Object[]>) caseQuery.getResultList();

where Object[] will be an array of Teacher& String

For TypedQuery also similar change should work.

executing case expressions in jpql

2 Answers2