JDBI with in query

92 views
Skip to first unread message

Sethumadhavan Vasudevan

unread,
Feb 19, 2020, 9:09:43 AM2/19/20
to jDBI
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

unread,
Feb 19, 2020, 4:10:08 PM2/19/20
to jd...@googlegroups.com
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
Message has been deleted

eschwenk

unread,
Sep 22, 2020, 7:19:09 AM9/22/20
to jDBI
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:

Mattia Poli

unread,
Nov 3, 2021, 12:44:29 PM11/3/21
to jDBI

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
Reply all
Reply to author
Forward
0 new messages