0

I'm trying to put the result of a native query into a projection. But the query result values do not seem to be set to the proper fields in the projection object.

My native query is something like this:


@Query(value = "select p.siteId,"
        + " sum(case when p.arrivalDate = :date then 1 else 0 end) as arrivalTotal "
        + " sum(case when p.departureDate = :date then 1 else 0 end) as departureTotal "
        + " from product p "
        + " where p.collectionSite = :siteId"
        + " and (p.arrivalDate = :date or p.departureDate = :date", nativeQuery = true)
List<DeliverySummary> getDeliverySummary(@Param("siteId") String siteId,
        @Param("date") String date);

My projection class is:


    public interface DeliverySummary {
        String getSiteId();
        String getArrivalTotal();
        String getDepartureTotal();
    }

The results would return like:

siteId = 5 (when actual is site1)
arrivalTotal = "site1" (when actual is 2)
departureTotal = 2 (when actual is 5)

I suspect the issue is because the fields in the projection object are not native fields of the table (and entity), which are sums. Is there a way to set this correctly? TIA

newbee
  • 1
  • have you tried also to add an alias to siteId (as siteId)? – Simon Martinelli Dec 15 '21 at 12:59
  • @SimonMartinelli, yes. also tried with other variable name and correspondingly the field name in the projection class. but still the field values are not being set to the expected fields. – newbee Dec 15 '21 at 13:34
  • That sounds very strange. And you are 100% sure that the query returns the correct values? – Simon Martinelli Dec 15 '21 at 13:59
  • @SimonMartinelli, yup. The values are correct, but their variable assignments are not. But I found a solution that worked for me here https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection?rq=1 – newbee Dec 15 '21 at 15:56

1 Answers1

0

Use @SqlResultSetMapping with any existing @Entity class and just add the @SqlResultSetMapping snippets. Thanks to @Edwin Dalorzo

Here's the solution I used: JPA 2.1 with @SqlResultSetMapping** section

Matt Andruff
  • 4,974
  • 1
  • 5
  • 21
newbee
  • 1