How to bind to IN parameters in Java API Java Cloud Spanner

Question

Is it possible, using the Java Cloud SDK Java SDK, to associate with the parameters included in the IN part of the request?

eg.

List<String> names = new ArrayList<String>();
names.add("Alice");
names.add("Bob");
String sql = "SELECT * FROM people WHERE name IN (@names)";
Statement statement = Statement
                .newBuilder(sql)
                .bind("names").to(names)
                .build();

If we bind names using toStringArray, these are errors. And if we install the following:

names = "'Alice','Bob'";

Then the generated SQL:

SELECT * FROM people WHERE name IN ("'Alice','Bob'")

• Pay attention to additional rates. Any idea how we can do this without % s string replacement to avoid injections?

Answer 1

2 changes to your code:

List<String> names = new ArrayList<String>();
names.add("Alice");
names.add("Bob");
String sql = "SELECT * FROM people WHERE name IN UNNEST(@names)";
Statement statement = Statement
                .newBuilder(sql)
                .bind("names").toStringArray(names)
                .build();

First we need to make a condition IN UNNEST, since we're going to bind an array, not duplicate values.

to toStringArray, , .

, , , :

SELECT * FROM people WHERE name IN UNNEST(["Alice", "Bob"])