Spring boot nested native sql query is not working

Question

@Query(value = "select m from IN_MESSAGE m where m.masterOrderNo=(select n.masterOrderNo from IN_MESSAGE n where n.orderNo=:orderNo)", nativeQuery = true)
public List<InMessageDO> findAllByOrderNo(@Param("orderNo") String orderNo);

Above code is in my Jpa Repository.

List<InMessageDO> inMessages = inMessageRepo.findAllByOrderNo(input.getOrderNo());

Above line shows how I am calling JPA method in my Application. Whenever above line is getting executed I am getting exception.

'org.springframework.dao.InvalidDataAccessResourceUsageException' exception.

Thank you

J.F. · Accepted Answer · 2020-11-21 12:10:05Z

2

The problem is you are not using a native query at all. SELECT m FROM ... is not native SQL.

You have to use SELECT * or SELECT m.* like this (not tested at all but select m should be the problem):

@Query(value = "SELECT * FROM in_messagedo m WHERE m.MASTER_ORDER_NO = (SELECT n.MASTER_ORDER_NO FROM in_messagedo n WHERE n.ORDER_NO = :orderNo)", nativeQuery = true)
public List<InMessageDO> findAllByOrderNo(@Param("orderNo") String orderNo);

Edit: Also, if your @Entity is named InMessageDO check how is your table name, because you are trying to read IN_MESSAGE table, and should be in_messagedo or something similar.

Also your query has to be the same as your DB, not as your object attribute name, so you have to look for MASTER_ORDER_NO instead of masterOrderNo. And I suposse orderNo has the same problem.

Also, the last orderNo is not neccesary to edit, this is the parameter variable.

edited Nov 21, 2020 at 12:10

answered Nov 20, 2020 at 13:43

J.F.

15.3k10 gold badges41 silver badges74 bronze badges

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

9 Comments

Shivam Singh Over a year ago

Hi J.F. Thank you for reply. I have tried SELECT * and SELECT m.* But I am still getting the same exception.

J.F. Over a year ago

I've tested with my DB and works ok. Update with more details from the stack trace error. Also check your table is named IN_MESSAGE and not IN_MESSAGEDO or something similar.

Shivam Singh Over a year ago

From stack trace: java.sql.SQLSyntaxErrorException: ORA-00904: "M"."MASTERORDERNO": invalid identifier

Shivam Singh Over a year ago

And I also checked the table name. It is IN_MESSAGE

J.F. Over a year ago

Check how is named the row masterorderno in the DB

|

Collectives™ on Stack Overflow

Spring boot nested native sql query is not working

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related