1

This is my query, I have written in Repository file. 

@Query(value = "select * from A a join B b ON a.allocateBy = b.empNo join D d ON b.departmentName = d.departmentName where a.allocateBy in (:allocateByList)",  nativeQuery = true)

ArrayList<A> findAllByAllocateByIn(@Param("allocateByList") String allocateByList);

There are no errors but :allocateByList is not getting data. I have printed the value of allocateByList just before. It has data.

Am I missing Something?

Harry.ver36
  • 19
  • 4
  • Why "allocateByList" is a String ? Do you have an example of value for "allocateByList" and for "a.allocateBy" ? – Julien Feb 18 '20 at 07:06
  • Thanks Julien. It is a comma separated String. Example value is 'M06','M81'. – Harry.ver36 Feb 18 '20 at 07:20
  • You can [log your queries with params](https://stackoverflow.com/a/1713464/4778343) and see what's going on. – Stefan Golubović Feb 18 '20 at 08:17
  • 1
    Does this answer your question? [JPA passing list to IN clause in named native query](https://stackoverflow.com/questions/6277807/jpa-passing-list-to-in-clause-in-named-native-query) – SternK Feb 18 '20 at 08:49

2 Answers2

0

Change allocateByList to list and use below query

@Query(value = "select * from A a join B b ON a.allocateBy = b.empNo join D d ON b.departmentName = d.departmentName where a.allocateBy in ?1",  nativeQuery = true);

ArrayList<PointAllocation> findAllByAllocateByIn(List<String> allocateByList);
Gaurav Dhiman
  • 953
  • 6
  • 11
0

As you mentionned in your comment, "allocateByList" has, for example, the value "'M06','M81'".

So you are doing the following request:

select * 
from A a 
join B b ON a.allocateBy = b.empNo 
join D d ON b.departmentName = d.departmentName
where a.allocateBy in ('''M06'',''M81''')

You are looking for a value "a" having an "allocateBy" value at " 'M06','M81' ".

I believe you want one which have either the value "M06" or the value "M81".

So you have to do the following modifications:

1) Modify the type of allocateByList from String to List<String>

2) Format the searched value correctly: you have to split on , and then remove the ' wrapping your values.

So:

String allocateByListInitial = "'M06','M81'";

String[] allocateByListSplitted = allocateByListInitial.split(",");

// allocateByListSplitted = ["'M06'", "'M81'"] (with single quote wrapping each value)

List<String> allocateByList = Arrays.stream(allocateByListSplitted)
                                    .map(str -> str.substring(1, str.length() - 1)) // Remove first and last character of the string
                                    .collect(Collectors.toList());

// allocateByList = ["M06", "M81"] (without the single quote)

List<PointAllocation> pointAllocations = myRepo.findAllByAllocateByIn(allocateByList);
Julien
  • 2,256
  • 3
  • 26
  • 31