CODEWITHSUNDEEP
sqlspringspring-bootspring-data-jpa
monster
monster

Reputation: 816

No result for native query in jpa

I have created a native query of following form -

@Query(value="SELECT * FROM ORDER_DETAILS WHERE ORDER_ID IS NULL AND tag = ?2 AND CREATED_DATE >= ?3 AND STATUS IN ?1",nativeQuery=true)
    List<Order> findCases(String status, String tag, Date date);

where in my case the parameters are as follows -

String status="('ACCEPTED')";
String tag="0";
Calendar c = Calendar.getInstance();
c.add(Calendar.DATE, -5);
Date date =c.getTime();

On executing the query i dont empty output. But when i execute the same query(written below) on oracle, i get the required result.

SELECT * FROM ORDER_DETAILS WHERE ORDER_ID IS NULL AND TAG = '0' AND CREATED_DATE >='23-11-2018'  AND ORDER_STATUS IN ('ACCEPTED');

Can anyone tell what i did wrong?

Upvotes: 0

Views: 710

Answers (1)

Alan Hay
Alan Hay

Reputation: 23226

If you want to use an in clause then there's not much point having a single String as a param.

@Query(value="SELECT * FROM ORDER_DETAILS WHERE ORDER_ID IS NULL 
                  AND tag = ?2 AND CREATED_DATE >= ?3 
                     AND STATUS IN ?1", nativeQuery=true)
List<Order> findCases(List<String> statuses, String tag, Date date);

List<String> statuses = new ArrayList<>();
statuses.add("ACCEPTED");
String tag="0";
Calendar c = Calendar.getInstance();
c.add(Calendar.DATE, -5);
Date date = c.getTime();

List<Order> order = repo.findCases(statuses, tag, date);

Upvotes: 1

Related Questions