JPA createNativeQuery getResultList List skipping fields that are null

Question

We are using JPA's createNativeQuery such that we get back a List<Object[]>; see: https://stackoverflow.com/a/13701011/34806

Our Java code expects to be able to iterate through the list and get back Object arrays with a fixed number of "fields" (Objects in the Object array). However if any of those fields were null in the database, the Object[] array does not contain them, instead skipping such null fields entirely such that the "row" (Object[] array in the List) contains fewer fields than other rows -- this is obviously problematic.

Answer 1

Our solution was to use coalesce in the query's SQL for each nullable field, to return a default value other than null. For instance the following:

SELECT EMPNO, COALESCE(SALARY, 0) FROM EMPLOYEE

Will return 0 if Salary was null. See: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000780.html NOTE: I just replaced the previous link because it no longer worked, sadly that may happen again :( I guess just google for "database coalesce" in that case.