JPA @SqlResultSetMapping not able to handle empty sql result to be mapped to empty POJO - instead an exception is thrown

Question

I'm using JPA 2.1 (Eclipselink vendor) @SqlResultSetMapping to map sql query to none Entity POJO, it works when the sql result is not empty, but when the my table is empty

the construction of my POJO fails with the exception :

 2016-05-30 11:44:17,154 [tp520017379-230] ERROR - Exception [EclipseLink-6177] (Eclipse Persistence Services - 2.6.2.v20151217-774c696): org.eclipse.persistence.exceptions.QueryException
    Exception Description: The column result [st_agg] was not found in the results of the query.
    Query: ResultSetMappingQuery(name="nodeStatusAggQuery" sql="select max(status) as st_agg, max(clock_accuracy_health) as cac_agg, max(clock_analysis_health) as can_agg, max(ptp_net_analysis_health) as na_agg from sync_node where ncd_id = ? and type =?")
    javax.persistence.PersistenceException: Exception [EclipseLink-6177] (Eclipse Persistence Services - 2.6.2.v20151217-774c696): org.eclipse.persistence.exceptions.QueryException
    Exception Description: The column result [st_agg] was not found in the results of the query.

My expectation that an empty result should be handle automatically by JPA for example by calling default constructor of my POJO. i found a workaround to solve the problem by catching the exception, but my Question can i make JPA handle empty results automatically ?? or any other suggestions ?

Code Sample:

  public static SyncNodeStatusAggregation getMaxSeverStatusAndHealth(int ncdId, SyncProtocolType type){
    try {
      EntityManager em = ...
      Query aggregateQuery = em.createNamedQuery(SyncNodeDBImpl.NODE_STATUS_AGG_QUERY);
      aggregateQuery.setParameter(1, ncdId);
      aggregateQuery.setParameter(2, type.ordinal());
      return (SyncNodeStatusAggregation) aggregateQuery.getSingleResult();
      //javax.persistence.PersistenceException can be thrown when result is empty, JPA will not be able to map the result to object, thus we handle it be catching the exception
    } catch (PersistenceException e) {
      return new SyncNodeStatusAggregation(SyncNodeStatus.Ok, SyncHealthIndication.na, SyncHealthIndication.na, SyncHealthIndication.na );
    }
  }



@SqlResultSetMapping(
        name = SyncNodeDBImpl.RESULT_MAPPING_NAME,
        classes = {
                @ConstructorResult(
                        targetClass = SyncNodeStatusAggregation.class,
                        columns = {
                                @ColumnResult(name = "st_agg"),
                                @ColumnResult(name = "cac_agg"),
                                @ColumnResult(name = "can_agg"),
                                @ColumnResult(name = "na_agg"),
                        }
                )
        }
)
@NamedNativeQuery(
        name = SyncNodeDBImpl.NODE_STATUS_AGG_QUERY,
        query = "select max(status) as st_agg, max(clock_accuracy_health) as cac_agg, max(clock_analysis_health) as can_agg, max(ptp_net_analysis_health) as na_agg from sync_node where ncd_id = ?1 and type =?2",
        resultSetMapping =SyncNodeDBImpl.RESULT_MAPPING_NAME
)

Answer 1

You need to specify the type like this :

@ColumnResult(name = "na_agg",type="Double.class")

Answer 2

It seems there is this open bug on Eclipselink. I am using 2.6.3 and am too facing the same - https://bugs.eclipse.org/bugs/show_bug.cgi?id=484276