Monthly Archives: November 2011

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 , , ,

logging eclipselink

Posted on November 1, 2011 | Comments Off on logging eclipselink

the big downside of JPA is in my opinion the JPQL… normaly it is pretty, pretty,pretty good… when only doing simple stuff. As soon as it get complex, where i first try it in SQL and then rewrite it in JPQL it gets bad… as long as no error has occured, it didnt output the from-JPQL-rendered-SQL… and i actually tried to debug it, tried to access it in java… but didnt tried to change the config…

<property name=”eclipselink.logging.level.sql” value=”FINE” />
<property name=”eclipselink.logging.parameters” value=”true” />

these two lines enable the logging of SQL… … bah…

Comments Off on logging eclipselink

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 , ,