CODEWITHSUNDEEP
javamysqlsqljoin
Bhuvan Sagar
Bhuvan Sagar

Reputation: 41

What is wrong with this sql query to cause this error?

What is wrong with this query?

" where mh.parent_id = ? "+ (roleIds != null ? " or mh.role_id in (" + roleIds + ")" : "") +

Error Message:

PreparedStatementCallback; bad SQL grammar [select u.id,u.user_id,count(distinct pf.id),IfNULL(upm.grievance_autoassign_enable,true) from users u inner join user_roles ur on ur.user_id=u.id left outer join user_preference_management upm on u.id = upm.id inner join management_hierarchy mh on mh.child_id = u.id or mh.role_id =ur.role_id left outer join ( jbpm4_task jt inner join process_flow pf on pf.pid = jt.execution_id_ and pf.grievance_mgmt_id is not null) on jt.assignee_ = u.user_id where mh.parent_id = ? or mh.role_id in ({role_id=2},{role_id=4},{role_id=1},{role_id=8},{role_id=38},{role_id=22},{role_id=69}) group by u.id,u.user_id order by count(pf.id) ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=2},{role_id=4},{role_id=1},{role_id=8},{role_id=38},{role_id=22},{role_id=69}) ' at line 1

Upvotes: 0

Views: 293

Answers (1)

clay
clay

Reputation: 6017

The problem is your string roleIds, which appear to be in JSON notation. The correct syntax for a SQL where..in statement would be comma-separated.

Make your query look more like this: 

 . . . where mh.parent_id = ? or mh.role_id in (2,4,1,8,38,22,69) group by . . .

Upvotes: 0

Related Questions