1

I have following SQL query 

SELECT
  customer.CUSTOMER_NUMBER customerNumber,
  contractDetail.START_DATE campaignStartDate,
  contractDetail.END_DATE  campaignEndDate,
  contractDetailCommitment.END_DATE  commitmentEndDate,
  contractDetail.CONTRACT_DETAIL_STATUS statusCode,
  baseOffer.NAME campaignName,
  property.CODE propertyCode,
  DECODE(property.TYPE, 'LOV', propertyListChoice.CODE, assetPropertyValue.PROPERTY_VALUE) propertyValue
FROM .............

The query structure is not important, it returns data like this

enter image description here

So, different values only in two fields: propertyCode and propertyValue. I want to map the result of this query to following dto object: 

@Data
public class ContractInfoDTO {
    private String customerNumber;
    private String campaignStartDate;
    private String campaignEndDate;
    private String campaignName;
    private String statusCode;
    private List<Property> properties;
}

Is it possible to do that automatically? Or the only solution is to write custom ResultTransformer and handle all three rows one by one?

iChrome
  • 443
  • 1
  • 6
  • 24
  • https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection – dbl Sep 18 '18 at 14:43
  • To which fields of `ContractInfoDTO` class would you like to map `propertyCode` and `propertyValue`? – Bartek Sep 18 '18 at 14:57

1 Answers1

0

Using JPA you can map query result directly into DTO, for example 

SELECT new com.company.MyDTO(p.someField, p.someField2...)

But firstly you need to transform your SQL query to JPQL query.

Bartek
  • 2,109
  • 6
  • 29
  • 40
  • And then the result will always be Collection resultSet. Also MyDTO should provide the respective constructor definition. – dbl Sep 18 '18 at 14:46
  • That's right. Constructor must much fields that you want to map. – Bartek Sep 18 '18 at 14:48
  • 1
    same result could be achieved by using this very same native query. Just it's recommended to use JPQL whenever it's possible. – dbl Sep 18 '18 at 14:48