1

My Repository

@Repository
public interface TestNativeQRepository extends CrudRepository<TestNativeQ, String> {

    @Query( value="SELECT qplt.name price_list_name,  qplab.status_code, qplab.start_date, (SELECT charge_definition_code FROM oalfsaas_repl.QP_CHARGE_DEFINITIONS_B WHERE charge_definition_id=qplab.charge_definition_id  ) chargedefinitioncode "
                + "FROM  pricelistsall qplab, PRICELISTSTL qplt "
                + " WHERE qplab.price_list_id  =qplt.price_list_id ", nativeQuery = false)
    public List<TestNativeQDTO> getAllDetails();
}

Actual Result:

[{"ABC", "DEF", "15/05/2018", "XXZ"}]

Expected Result

[{name: "ABC", statuscode: "DEF", startDate: "15/05/2018", chargedefintioncode: "XXZ"}]
Manoj Kumar
  • 139
  • 1
  • 3
  • 12
  • The result of native query is not automatically transformed to an Entity, you must do it manually or define mappings via `@SqlResultSetMapping` and `@ColumnResult`. Refer this question for details https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection – Nikolai Shevchenko Nov 05 '18 at 12:19
  • @NikolayShevchenko thanks for the correcting – Alien Nov 05 '18 at 13:33
  • Hi, Thanks for the answer. What if nativeQuery = false and columns are from more than 2 tables – Manoj Kumar Nov 05 '18 at 16:28

1 Answers1

1

As @Nikolay gave hint in comment.

The result of native query is not automatically transformed to an Entity, you must do it manually or define mappings via @SqlResultSetMapping and @ColumnResult.

to make that work follow the below code.

@Entity
@Configurable
@SqlResultSetMapping(name = "someName", entities = @EntityResult(entityClass = SamplePojo.class), columns = @ColumnResult(name = "columnName"))
public class SamplePojo{
//fields and getters/setters
}

and Then in query

List<SamplePojo> list = entityManager().createNativeQuery("Select ......", "someName").getResultList();

Note : someName should be same in both places.

Refer this-question

Alien
  • 15,141
  • 6
  • 37
  • 57
  • Hi, Is there any blog respected to it ? – Manoj Kumar Nov 05 '18 at 10:52
  • My hint was related to using native queries in repo methods denoted with `@Query`. If you use EntityManager directly then following should be enough: `List list = entityManager().createNativeQuery("Select ......", SamplePojo.class).getResultList();` (without extra mappings) – Nikolai Shevchenko Nov 06 '18 at 04:56