SQL "IN" Clause

[Mu4h4h4]

Is there any support for the SQL IN clause? Database.test() .select("select score from person where name IN (?)") .parameters("FRED", "JOSEPH") // or a list of Strings .getAs(Integer.class) .blockingForEach(System.out::println);

I tried a bunch of different ways and I couldn't figure how I could possibly make it work.

Thank you in advance for your time!

[davidmoten]

Don't know, I'll have a look soon.

[davidmoten]

Works for me, are you using the latest version?:

Database.test() //
  .select("select score from person where name in (?)") //
  .parameters("FRED", "JOSEPH") //
  .getAs(Integer.class) //
  .blockingForEach(System.out::println);

Output:

21
34

[Mu4h4h4]

Thank you for the prompt response, much appreciated! My initial example didn't really represent my use case, my apologies. :(

I have multiples criterias, e.g., SELECT score FROM person WHERE name = :name AND group IN (:groupIds) If I use .parameter("groupIds", String.join("','", groupIds)) as a work around on a single item List it works fine but with 2+ items it stops returning anything. the parameters method doesn't have a (name, List<>) option a well.

thank you for your time :)

EDIT: I just noticed that when using ' on the join it gets escaped into the built SQL, so it turns into 'id1\',\'id2'. PS: the ids are string value

[davidmoten]

I see, so we want this to pass as a unit test:

@Test
public void testSelectUsingInClauseWithListParameter() {
        Database.test() //
                .select("select score from person where score > ? and name in (?) order by score") //
                .parameters(0, Lists.newArrayList("FRED", "JOSEPH")) //
                .getAs(Integer.class) //
                .test() //
                .awaitDone(TIMEOUT_SECONDS, TimeUnit.SECONDS) //
                .assertNoErrors() //
                .assertValues(21, 34) //
                .assertComplete();
    }

Would be very good to have, I'll implement. Might need some adjustments to api because of parameters(List) override.

[Mu4h4h4]

You are missing the score parameter but yes. The idea is being able to set one of the parameters as list and have the same behavior when you are setting a single parameter as list.

@Test
public void testSelectUsingInClauseWithListParameter() {
        Database.test() //
                .select("select score from person where score > ? and name in (?) order by score") //
                .parameter(0, 70) //
                .parameters(0, Lists.newArrayList("FRED", "JOSEPH")) //
                .getAs(Integer.class) //
                .test() //
                .awaitDone(TIMEOUT_SECONDS, TimeUnit.SECONDS) //
                .assertNoErrors() //
                .assertValues(75, 98) //
                .assertComplete();
    }

Right now my work around was to create a list have all of my parameters in order and do multiple selects which isn't ideal performance wise.

List params = new ArrayList();
    names.forEach(name -> {
    params.add(score);
    params.add(name);
})
        Database.test() //
                .select("select score from person where score > ? and name in (?) order by score") //
                .parameters(params) //

Would named parameters also be supported?

[davidmoten]

You are missing the score parameter

It's there, it's the first zero in the parameters.

The arrival of parameters must match the order in the statement (if named parameters are not being used) so the way you are calling the api does not match expected usage (and .parameter(0,70) should not compile).

For a single statement the most obvious workaround at the moment is to replace the single ? in the in clause with n comma delimited ? where n is the size of the params list. i.e. you dynamically build the select sql according to the number of parameters. This is what will be happening under the covers when I add support mainly because as I understand it PreparedStatement.setArray support is patchy (DB2 and MySql don't support if I remember rightly).

I would seek to support named parameters.

[Mu4h4h4]

I see, for some reason I thought that the anonymous parameters where set using an index (0, firstParam),(1, second),... like PreparedStatement.

my main concern here is performance since I'm spanning multiple SELECTs for each of the IDs that would be inside the IN clause.

List<String> names = "FRED","JOSEPH";
List params = new ArrayList();
names.forEach(name -> {
    params.add(0);
    params.add(name);
});
Database.test() //
                .select("select score from person where score > ? and name = ?") //
                .parameters(params) //

instead of

Database.test() //
                .select("select score from person where score > ? and name in (?) order by score") //
                .parameters(0, names) //

as I was saying I tried hacking a string param FRED','JOSEPH so that it would transform from (?) into ('FRED','JOSEPH') but the ' get escaped, killing my hopes and dreams :P

If I have 2 anonymous parameters placeholder can I do the below?

select()
.parameter(first)
.parameter(second)

?

[davidmoten]

You seem to have missed my point. I'll explain the workaround. I'm not talking about the originally suggested hack which would not work because of protection from sql injection but rather of modifying the sql (not the parameters). Here's an example.

List<String> list = Lists.newArrayList("FRED","JOSEPH");
String questionMarks =  list
  .stream() 
  .map(x -> "?")
  .collect(Collectors.joining(","));
String sql = "select score from person where name in (" + questionMarks + ");
db.test()
  .select(sql)
  .parameters(list)
  .getAs(Integer.class)
  .blockingForEach(System.out::println);

The full implementation in the api for this will take me a little while (not a quick fix) but would be good to have and with luck I'll get to it soon but in the meantime you have the above style of workaround.

By the way, many databases have quite low limits for the number of parameters in a sql statement. Oracle used to be about 1000. I think I've used 20,000 with H2.

[Mu4h4h4]

It all makes sense now, awesome! Thank you very much!

[davidmoten]

I've added full support for collection parameters to select statements in 0.1-RC22 which has just been released to Maven Central. See https://github.com/davidmoten/rxjava2-jdbc#collection-parameters.

Take it for a spin and see if it works for you.

Update and callable statement support will come later.

[davidmoten]

Added update support in 0.1-RC23 which is on Maven Central now.

[Mu4h4h4]

Thank you very much! Checking it out!

[davidmoten]

Beaut, let me know if more docs needed too