JDBI with in query

[Sethumadhavan Vasudevan]

Hello everyone,

Can someone help on how can we achieve in clause in JDBI?

I want to implement something like below

select * from employee where department = 'Finance' or department = 'HR' or department = 'Legal';

Thanks

[Steven Schlansker]

For a fixed number of parameters, it is just vanilla SQL:

select * from employee where department in ('Finance', 'HR', 'Legal');

To match elements of an array (Postgres specific)

select * from employee where department = any(?)

Hope that helps

[eschwenk]

You can use In and bindList:

List<Employee> list = handle

  .createQuery("SELECT * from employee where department in (<List>)")

  .bindList(Arrays.asList(new String[] ["Finance""HR","Legal"}))

  .map(employeeMapper)

  .list();

Should do the  Trick.

Beware: depending on the database you are using, the number of entries in bindList is limited. For example,

with MS SQL Server each list entry is counted as a "Parameter" and there is a Maximum of 2100 Parameters allowed,

which can easily be reached in more complex use-cases.

So maybe you have to split your Input up into Blocks of 2000 Entries or to use some other technique.

For the Record: with Oracle, there is also a "2000 Parameters" Limit on Queries, but the whole List counts as

one single Parameter.

Maybe some more Details here:

https://stackoverflow.com/questions/6581573/what-are-the-max-number-of-allowable-parameters-per-database-provider-type

[Mattia Poli]

If I have more than 1 list to associate, what do I do? I don't know at the beginning the number of lists to put in condition IN