Tag Archives: jpql

jpa nativeQueries

Posted on November 1, 2011 | Comments Off on jpa nativeQueries

Sometimes JPQL just isnt enough… JPQL is rendered differently then expected, ie nested selects dont work… so native SQL queries need to be done…

main problem is the return value. It is possible to define SqlResultSetMappings, but these where kinda confusing. had problems with primary keys… so without these structures, a typeless list of object-array is returned:

   final List result = entityManager.createNativeQuery(“SELECT a, b FROM table”).getResultList(); 

for each row an Object[] is put into the list.The Array contains Objects of a and b.

Comments Off on jpa nativeQueries

Posted in developing

Tagged , , ,

SQL having

Posted on November 1, 2011 | Comments Off on SQL having

I needed to select an object-id of a row where a value reached a max value. Kinda confusing, so example:

SELECT id, MAX(value) FROM table
GROUP BY id

simple… and now i needed to check, if the value is bigger then something… lets say 5:

SELECT id FROM (
       SELECT id, MAX(value) AS bigValue FROM table
       GROUP BY id)
WHERE bigValue > 5

and yes. A join would be fine too… but actually i needed this query in JPA. and JPQL can’t handle nested selects… so a different solution was needed: HAVING! having is a select on a group…

SELECT id FROM table
GROUP BY id
HAVING MAX(value) > 5

cool 🙂 JPA didnt handle the HAVING… 🙁

Comments Off on SQL having

Posted in developing

Tagged , ,