How to access a member of object in native query params with 'IN' clause in JPA?-CodePudding

I have following SQL query

SELECT *
FROM data_table
WHERE field_one = '1000'
  AND (field_two, field_three, field_four) IN (('70033100', '0090047002', 'CAT'), ('70033100', '0004712', 'SAT'), ('70031337', '0233263', 'DAT'), ('70033100', '0090000001', 'CAT'))
  AND field_type IN ('TYPE_1', 'TYPE_2')
  AND date <= '2022-09-17'

I need to write corresponding JPA native query. I have a Entity Class given below, which is referred by query above.

  class Entity {
    String field_one;
    String field_two;
    String field_three;
    String field_four;
    LocalDate date;
    String field_type;
    }

I understand that we can simply refer the list in jpa query like :list, for example I can have a method like

@Query(nativeQuery=true,
value = "SELECT * FROM Entity WHERE field_one IN :field_one_list")
public List<Entity> findByFieldOneList(List<String> field_one_list)

How to do it for the given query?

CodePudding user response：

You can try like this.

@Query(value="select * from data_table where field_one = '1000' And (field_two, field_three, field_four) IN :?1 And field_type = ?2 And date <='2022-09-17'", nativeQuery = true) List findByEntityConditions(String[] data, String[] fieldType);

Take data and fieldType as String Array.

CodePudding user response：

You can try this:

@Query( SELECT *
FROM data_table
WHERE field_one = '1000'
AND (field_two, field_three, field_four) IN (:param1)
AND field_type IN (:param2)
AND date <= '2022-09-17', nativeQuery = true)
List<Entity> findByCustomData(@Param("param1")List<String> list1, @Param("param2) List<String> list2)