Passing a list of parameters for IN function in RawSql

[Srivardhini Chinnivakkam Suresh]

When I try to pass a list of Strings for the IN  function as a parameter in a RawSql, I get an error while parsing. I tried different variations to this and had no luck.

Example of the RawSql related statements:

VARIATION 1 - 

  List<String> nameList = Arrays.asList("John", "Joe");

  String sql = "select id, 'address' AS location FROM employee WHERE name IN (?) UNION ALL select  id, 'address' AS location FROM labours WHERE name IN (?) ; " 

   final RawSql rawSql = RawSqlBuilder

                .parse(sql)

                .create();

   List<Employee> employees = DB.find(Employee.class)

                .setRawSql(rawSql)

                .setParameter(1, nameList)

                .setParameter(2, nameList)

                .findList();

Error:

java.sql.SQLSyntaxErrorException: (conn=582) Could not set parameter at position 3 (values was 'John')

Query - conn:582(M)  - "select id, 'address' AS location FROM employee WHERE name IN (?) UNION ALL select  id, 'address' AS location FROM labours WHERE name IN (?)"

VARIATION 2 - 

 List<String> nameList = Arrays.asList("John", "Joe");

String nameAsString =
 "(" + nameList.stream().map(name -> "'" + name + "'")
 .collect(Collectors.joining(",")) + ")";

  String sql = "select id, 'address' AS location FROM employee WHERE name IN ? UNION ALL select id, 'address' AS location FROM labours WHERE name IN ? ; " 

   final RawSql rawSql = RawSqlBuilder

                .parse(sql)

                .create();

   List<Employee> employees = DB.find(Employee.class)

                .setRawSql(rawSql)

                .setParameter(1, nameAsString)

                .setParameter(2, nameAsString)

                .findList();

Error:

Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: 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 ''\'John\',\'Joe' at line 1

I do not want to take the SQL injection way like below:

  String sql = "select id, 'address' AS location FROM employee WHERE name IN " + nameAsString +" UNION ALL select id, 'address' AS location FROM labours WHERE name IN " + nameAsString +" ; " 

I know I am missing something, would greatly appreciate your help to resolve this.

TIA.

 

[Rob Bygrave]

So have a look at https://ebean.io/docs/query/sqlquery#collections ... and see if that makes sense.

In short, ? is the standard JDBC bind parameter placeholder for a single parameter.  As such we need to use either named parameters (like :foo) 

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/30d32f44-c410-474b-be43-f2ff55409549n%40googlegroups.com.

[Srivardhini Chinnivakkam Suresh]

Thank you, Rob. That did the trick!