[FIXED] NamedNativeQuery on postgres sequence fails to determine return type

Issue

I have a native named query

@NamedNativeQuery(
  name = "Device.nextEuiOffset",
  query = "select nextval('eui_sequence');"
)

This is operating on a postgres database (11) and when invoked the following exception is thrown.

The return type is a bigint from postgres.

Rolling back the transaction on unhandled java.lang.ArrayIndexOutOfBoundsException:

java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
at [email protected]/org.hibernate.internal.AbstractSharedSessionContract.resultClassChecking(AbstractSharedSessionContract.java:952)
at [email protected]/org.hibernate.internal.AbstractSharedSessionContract.createNativeQuery(AbstractSharedSessionContract.java:911)
at [email protected]/org.hibernate.internal.AbstractSharedSessionContract.buildQueryFromName(AbstractSharedSessionContract.java:889)
at [email protected]/org.hibernate.internal.AbstractSharedSessionContract.createNamedQuery(AbstractSharedSessionContract.java:990)
at [email protected]/org.hibernate.internal.AbstractSharedSessionContract.createNamedQuery(AbstractSharedSessionContract.java:109)
at jdk.internal.reflect.GeneratedMethodAccessor115.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

The query is created using the following

createNamedQuery("Device.nextEuiOffset", Long.class)

Essentially what is occurring is hibernate is unable to determine the result type.

Setting the resultClass = Long.class in the NamedNativeQuery definition fails because hibernate is expecting an Entity class as the result type here.

Does anybody have any suggestions on how to do this correctly?

Solution

Not sure if this is sufficient for you needs, but you could give the value returned by the sequence an alias and reference it in a result set mapping

@SqlResultSetMapping(
        name = "Device.nextEuiOffsetResult",
        columns={
                @ColumnResult(
                        name="EUI_VAL"
                )
        }
)
@NamedNativeQuery(
        name = "Device.nextEuiOffset",
        query = "select nextval('eui_sequence') AS EUI_VAL;",
        resultSetMapping = "Device.nextEuiOffsetResult"
)

Execution of named query would be as follows

Long euiVal = em.createNamedQuery("Device.nextEuiOffset", BigInteger.class).getSingleResult().longValue();

Answered By - johnnyutts
Answer Checked By - Candace Johnson (JavaFixing Volunteer)