Not able to get array object response in @Query annotation in Spring

Question

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"}]

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 stackoverflow.com/questions/13012584/… — Nikolai Shevchenko
– Nikolai Shevchenko, Commented Nov 5, 2018 at 12:19
Hi, Thanks for the answer. What if nativeQuery = false and columns are from more than 2 tables — Manoj Kumar
– Manoj Kumar, Commented Nov 5, 2018 at 16:28

Alien · Accepted Answer · 2018-11-05 13:30:06Z

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

edited Nov 5, 2018 at 13:30

answered Nov 5, 2018 at 10:20

Alien

16k8 gold badges43 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manoj Kumar Over a year ago

Hi, Is there any blog respected to it ?

Nikolai Shevchenko Over a year ago

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<SamplePojo> list = entityManager().createNativeQuery("Select ......", SamplePojo.class).getResultList(); (without extra mappings)

Collectives™ on Stack Overflow

Not able to get array object response in @Query annotation in Spring

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related