0

I am running a SELECT query using JDBC which itself has a few subqueries. One of these subqueries returns a column of data which I then process as an ArrayList. Due to error handling, I prepare the subquery and run it separately then produce the java String in the form "(item_1, item_2, ... , item_n)".

Note the tuple elements are INTEGERS.

My overarching query is in the form of a PreparedStatement where I look to populate the ? using my subquery result.

String subqueryResult = "(1, 2, 3)";

// ...

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM table WHERE columnName in ?");
stmt.setString(subqueryResult);

I get the SQL 1064 error which then states that my query looks like WHERE columnName in ''(1, 2, 3)'.

I understand that the literal quotes are unbalanced, but even if they are balanced we cannot perform an IN clause on a string.

How would I go about safely populating the PreparedStatement so that the resulting query looks like SELECT * FROM table WHERE columnName in (1, 2, 3)?

Joel Biffin
  • 348
  • 1
  • 6
  • 18
  • 1
    AFAIK plain JDBC doesn't support that so you'd have to use a collection of strings and a matching number of `?` parameters in the query, i.e. build the query dynamically and then set the value of each individual parameter. Alternatively a framework could handle that for you (I know JPA implementations do it but there are others for sure). – Thomas Jun 18 '19 at 15:42
  • You would need to build your query first based on the elements of your set, then setting the parameters, for example: `String s = "( "; for (String x : set){ s += "?,"; }` – vc73 Jun 18 '19 at 15:42
  • @vc73 currently, my set is built as a String and does in fact have the correct form "`(item_1, item_2, item_3)`" but then the SQL looks like `[...] WHERE columnName in '(item_1, item_2, item_3)'`. How do I get rid of those pesky single quotes? – Joel Biffin Jun 18 '19 at 15:45
  • Is `columnName` a character column? If so, you need single quotes around each entry in the in clause. – Andrew Jun 18 '19 at 15:49
  • @Andrew it is just a generic table column name I used for the example (because I can't upload any of the actual code). I have just edited my question to state the type of variables inside the tuple – Joel Biffin Jun 18 '19 at 15:51
  • Check this out https://stackoverflow.com/questions/56135349/cannot-use-setarray-jdbc-integer-with-h2-database/56350387#56350387 – LHA Jun 18 '19 at 15:59
  • `PreparedStatement` doesn't support what you are trying to do. It's like trying to put a square peg into a round hole. You can use a `java.sql.Statement` and simply concatenate the string "(1, 2, 3)" to the string "SELECT * ..." – Abra Jun 18 '19 at 18:24

0 Answers0