0

I want to use a calculated value in the WHERE clause and in an ORDER BY expression. In plain sql it would look like

SELECT some, colums, (some arbitrary math) AS calc_value FROM table WHERE calc_value <= ? ORDER BY calc_value

If I try in JPQL

entitymanager.createQuery("SELECT e, (some arbitrary math) AS calc_value FROM Entity e WHERE calc_value <= :param ORDER BY calc_value", Entity.class);

it fails. Obviously, because the return of the query is the tuple of Entity and calc_value (i.e. Double).

Is there a way of getting this into one query, using strong typed return values (i.e. Entity.class, as the calculated value doesn't matter).

Nils Bokermann
  • 121
  • 1
  • 5
  • Try creating a DTO and return it: http://stackoverflow.com/questions/2355728/jpql-create-new-object-in-select-statement-avoid-or-embrace – Robert Niestroj Dec 02 '15 at 13:38

1 Answers1

0

I've had a similar problem and didn't resolve the problem to fetch it into the correct object:

  • Tried all constructor combinations for the object - no luck.
  • tried Tuple.class - no luck.

Finally I used this approach and then fetched oject[0] into my real Java-Object:

TypedQuery<Object[]> q = em.createQuery("select v, (2 * 4) as temp from MyObject v order by temp", Object[].class);
List<Object[]> results = q.getResultList();

for (Object[] o : results) 
  MyObject mo = (MyObject) o[0];
fmi
  • 163
  • 1
  • 7