1

I have a table which is something like this:

AccountNum Name RoutingNum
a1 name1 r1
a2 name2 r2
a2 name3 r1

I want to select all rows with a specific pair account number and routing number, for example :

input
List<accountNum, routingNumber> pairList = {<a1, r1>, <a2, r2>}

sql returns:
| AccountNum | Name           | RoutingNum      |
| --------   | -------------- |--------------
| a1         | name1          | r1              |
| a2         | name2          | r2              |

For some context I just want to make a single call, that I would be making using jdbc, this is my java code which only selects for account number, which is not what I want as I want to select using routingNum too:

String inSql = String.join(",", Collections.nCopies(plainAccountNumberEntries.size(), "?"));
        List<String>accountNumberList = Arrays.asList("a1", "a2");

        return ddsJdbc.query(
                String.format("SELECT * from table where AccountNum in (%s)", inSql),
                accountNumberList.toArray(),
                new someMapper()
        );

I want to avoid making multiple calls to the database for every entry in the list.

Thanks for your time.

Snedden27
  • 1,870
  • 7
  • 32
  • 60
  • This is a bit unclear. Are you asking to select rows with `((AccountNum="a1" and RoutingNum="r1") or (AccountNum="a2" and RoutingNum="r2"))`? Or is it `AccountNum in ("a1","a2") and RoutingNum in ("r1","r2")`, which would also return `[a1,r2]` and `[a2,r1]` rows? – Jim Garrison Dec 10 '20 at 18:57
  • sorry if it is I am asking to return (AccountNum="a1" and RoutingNum="r1") or (AccountNum="a2" and RoutingNum="r2")) – Snedden27 Dec 10 '20 at 19:00
  • 1
    Well, there's you `WHERE` clause. If you have a variable number of conditions you'll have to build the statement dynamically each time. But please use placeholders (`?`) instead of building in the values, to avoid SQL injection. – Jim Garrison Dec 10 '20 at 19:03
  • I am using placeholders ,ain't i? Also what do you mean by where clause , is it generating separate sql in a for loop? – Snedden27 Dec 10 '20 at 19:05

1 Answers1

2

Use an IN condition with multiple expressions in the list:

SELECT *
from   table_name
where  ( AccountNum, RoutingNum ) in ( ( 'A1', 'R1' ), ( 'A2', 'R2') )
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Just a caveat for using 'IN' that I found , for oracle it has a hard limit of 1000 entries per call. – Snedden27 Dec 11 '20 at 15:26
  • @Snedden27 Only for single value lists; lists with multiple values, like this one, can have up to 100,000 elements in the list. https://stackoverflow.com/a/17019130/1509264 – MT0 Dec 11 '20 at 16:18
  • well that's good, to know ,was thinking of batching the sqls but this helps , thanks – Snedden27 Dec 11 '20 at 16:44