0

My Spring data JPA code to get the data from db based on some search criteria is not working. My DB is SQL Server 2012, same query seem to work with MYSQL DB.

Code Example : 

@Query(value = "select * from entity e where e.emp_id=:#{#mySearchCriteria.empId} and ((:#{#mySearchCriteria.deptIds} is null or :#{#mySearchCriteria.deptIds} ='') or e.dept_id in (:#{#mySearchCriteria.deptIds})) ", nativeQuery = true)
public List<Entity> search(@Param("mySearchCriteria") MySearchCriteria mySearchCriteria);

if list mySearchCriteria.deptIds has more than one value- it's not working(it's actually translating it to wrong query. Any lead? Thanks in advance.

Note: data type for deptIds is List of Integer

Praful Jha
  • 187
  • 4
  • 18
  • what error message it produces? what is the type of mySearchCriteria.deptIds field? – Woworks Nov 18 '19 at 08:30
  • @Woworks It's actually List of Integers, error log says- the result is not translating to boolean – Praful Jha Nov 18 '19 at 08:44
  • exact error message in log is : "message":"An expression of non-boolean type specified in a context where a condition is expected, near ','." – Praful Jha Nov 18 '19 at 09:11

2 Answers2

1

Its complaining because values of {#mySearchCriteria.deptIds} is comma separated list e.g. 'Value1', 'Value2' so the query gets translated as ('Value1', 'Value2' is null) which causes this error.

Need to verify if list is empty or not and then change the query with IN clause and one without IN clause.

Amit Naik
  • 983
  • 1
  • 5
  • 16
  • Can't be done with one query? Also mysql handled if list is null or empty by itself, is it specific to SQL Server 2012 that it's not handling? – Praful Jha Nov 18 '19 at 09:48
  • nope not specific to sql server. refer https://stackoverflow.com/questions/2488930/passing-empty-list-as-parameter-to-jpa-query-throws-error – Amit Naik Nov 18 '19 at 10:09
  • ,My Question is that, when I connect to MySql DB with same code-base (without null check for list object) and different mysql connector, it works( I mean if the list is null, it does not add it to query), however if I change the dialact, connector and db to MSSQL server , it add it to the query and my query result empty result set. – Praful Jha Nov 18 '19 at 10:53
  • I didn't understand.. i thought you get an error message as `An expression of non-boolean type specified in a context where a condition is expected, near ','`. so you mean same query works differently with MySQL and MsSQL? – Amit Naik Nov 19 '19 at 03:30
  • Yes you're right. My application works fine with mysql, i am migrating to sql server, so now some of the queries are failing – Praful Jha Nov 20 '19 at 04:33
0

Surround the list by parentheses. This works for me.

(:#{#mySearchCriteria.deptIds}) is null
Vojtěch Šobáň
  • 1