How to make a working SQL query a native query in spring data

Question

I created an SQL query:

SELECT ORDER_NUM, STUFF2, STUFF3 FROM table1 t1 WHERE (STUFF3 = 'poor') AND NOT EXISTS (SELECT ORDER_NUM FROM table2 t2 WHERE t1.ORDER_NUM = t2.ORDER_NUM)

The query works correctly and I get the desired result; However, I am now trying to use this working query in my spring data application as a native query, using the @Query annotation.

@Query(value = "SELECT ORDER_NUM, STUFF2, STUFF3 FROM table1 t1 WHERE (STUFF3 = ?0) AND NOT EXISTS (SELECT ORDER_NUM FROM table2 t2 WHERE t1.ORDER_NUM = t2.ORDER_NUM)", nativeQuery = true)
List<eOrder> findByStuff3(String stuff3);

I copied the exact same query and placed it in my repository (as shown above) but the value is binding as NULL and no records are showing. Is there a step that I am missing to make this query work with my spring application?

Answer 1

Through further research I was able to fix the problem I was having. The native query I used in my repository actually does work, there was a simple error in the code, instead of a 0, I replaced with a 1 and it worked great!

Old version:

@Query(value = "SELECT ORDER_NUM, STUFF2, STUFF3 FROM table1 t1 WHERE (STUFF3 = ?0) AND NOT EXISTS (SELECT ORDER_NUM FROM table2 t2 WHERE t1.ORDER_NUM = t2.ORDER_NUM)", nativeQuery = true)
List<eOrder> findByStuff3(String stuff3);

Modified version:

@Query(value = "SELECT ORDER_NUM, STUFF2, STUFF3 FROM table1 t1 WHERE (STUFF3 = ?1) AND NOT EXISTS (SELECT ORDER_NUM FROM table2 t2 WHERE t1.ORDER_NUM = t2.ORDER_NUM)", nativeQuery = true)
List<eOrder> findByStuff3(String stuff3);

I also came across another method that also works just as well using JPQL I was able to get my query to run in my spring application:

@Query(value = "SELECT t1.ORDER_NUM, t1.STUFF2, t1.STUFF3 FROM table1 t1 WHERE (t1.STUFF3 = ?0) AND NOT EXISTS (SELECT t2.ORDER_NUM FROM table2 t2 WHERE t1.ORDER_NUM = t2.ORDER_NUM)", nativeQuery = true)
List<eOrder> findByStuff3(String stuff3);