JDBC: multicolumn IN query

Question

I have a following query:

SELECT
  date, userId, value
FROM
  tbl_table
WHERE
  date = to_date(:date, 'YYYY-MM-DD')
    AND
  userId = :userId

It allows to request for a single value like this:

MapSqlParameterSource args = new MapSqlParameterSource();

args.addValue("date", date, Types.VARCHAR);
args.addValue("userId", userId, Types.VARCHAR);

SqlRowSet rowSet = jdbcTemplate.queryForRowSet(SQL_SELECT, args);

jdbcTemplate.queryForRowSet(SQL_SELECT_MARKET_VALUE, args);

This is totally ok, but extremelly slow in case you have to query value for many date/userId pairs.

I would like to optimize it using multicolumn IN clause, but how do I handle multicolumn list via JDBC (or better question: is it possible using JDBC)?

`where (date, userid) in ( (?,?), (?,?), (?,?) )` will work in plain JDBC. I don't know if it will work with JdbcTemplate and named parameters though — , Jan 16 '15 at 08:38
@a_horse_with_no_name That's a big question: JDBC template expands list parameters into wildcards itself. — Denis Kulagin, Jan 16 '15 at 08:39
I am solving this with a dynamically created query. Fast enough for me. — stg, Jan 16 '15 at 08:40

score 2 · Answer 1 · edited May 23 '17 at 12:27

2

Oracle supports multiple columns in "in" predicate:

SELECT
  date, userId, value
FROM
  tbl_table
WHERE
  (date, userId) IN ((to_date(:date1, 'YYYY-MM-DD'), :userId1), (to_date(:date2, 'YYYY-MM-DD'), :userId2))

However JDBC doesn't provide a decent support of in-statement parameters - you will have to build the query using StringBuilder or use some of workarounds described here

edited May 23 '17 at 12:27

Community

1
1

answered Jan 16 '15 at 08:38

bedrin

4,458
32
53

Yes, it does. Question here is more related to JDBC-ORA bridge and the case, when number of parameters is unknown. – Denis Kulagin Jan 16 '15 at 08:41
2

Unrelated, but: the `to_date()` seems unnecessary if you are passing real dates (which you should) – Jan 16 '15 at 08:43
@a_horse_with_no_name not only unnecessary but probably harmful, as it would trigger an implicit date-to-string conversion using the default date format – gpeche Jan 16 '15 at 08:55

gpeche · Answer 2 · 2015-01-16T09:30:45.187

If what you want is to pass JDBC a list of date/userId pairs, or a list of dates and a list of userIds, I think it will not work.

A possible workaround in Oracle would be using a global temporary table with ON COMMIT DELETE ROWS. Your would have:

  -- DDL for the workaround
  CREATE GLOBAL TEMPORARY TABLE admin_work_area
    (d DATE,
     userId VARCHAR2(10))
  ON COMMIT DELETE ROWS;
...
-- Start of query method pseudo-code
...
-- You should be able to JDBC-batch these for better performance
INSERT INTO temp_multicolumn_filter (d, userId) VALUES (date1, userId1);
INSERT INTO temp_multicolumn_filter (d, userId) VALUES (date2, userId2);
...
-- Query using temp_multicolumn_filter
SELECT date, userId, value
FROM tbl_table
WHERE
(date, userId) in (select d, userId from temp_multicolumn_filter);
...
-- End of query method pseudo-code

As the temporary table has the ON COMMIT DELETE ROWS, each transaction will only see its own date/userId pairs. Just remember that if you use the temporary table more than once in the same transaction, you might need to clear it before using it.

UPDATE:

Another option would be using a PIPELINED PL/SQL function to "build" your table inside the query:

-- DDL for this workaround
CREATE TYPE date_userid_pair AS OBJECT (
    d DATE,
    userId VARCHAR2(10));

CREATE TYPE date_userid_dataset IS TABLE OF date_userid_pair;

CREATE FUNCTION decode_date_userid_pairs(dates in varchar2, userIds in varchar2) 
RETURN date_userid_dataset PIPELINED IS    
    result_row date_userid_pair;
BEGIN  
    WHILE there are more "rows" in the parameters LOOP
        result_row.d := -- Decode next date from dates
        result_row.userId := -- Decode next userId from userIds

        PIPE ROW(result_row);
    END LOOP;
END;

// Start of query method pseudo-code
...
// This is Java code: encodeList encodes a List of elements into a String.
encodedDates = encodeList(listOfDates);
encodedUserIds = encodeList(listOfUserIds);
...
// Query using temp_multicolumn_filter
SELECT date, userId, value
FROM tbl_table
WHERE
(date, userId) in (
    select date, userId 
    from TABLE(decode_date_userid_pair(:encodedDates, :encodedUserIds));
...
// End of query method pseudo-code

But this is more hacky, and if you don't have privileges to create a temporary table, then you probably won't have CREATE TYPE either (you might not even have CREATE FUNCTION privilege).

Great solution, but creating temp tables is above my privileges and due to bureaucracy reasons I have only option to come up with workaround. — Denis Kulagin, Jan 16 '15 at 08:54

score 0 · Answer 3 · edited Jan 16 '15 at 09:09

It depends of details. If user/date filter is quite persistent (should be user more than once) temporary table will be the best decision. You can fill it once, you can edit it, and you can use it several times without reloading.

If you need of quite large number of pairs, I'd recommend you to use a table type. It would be something like this:

create type DateUserPair as object (dt date, userid integer);
create type DateUserPairs as table of DateUserPair;
....
SELECT
  date, userId, value
FROM
  tbl_table src,
  table(cast :filter as DateUserPairs) flt
WHERE
  src.date = flt.dt and
  src.userId = flt.userId;

If filter would be small, filtering by (date, userId) in ((?,?), (?,?), ...) would be simple and clever.

Btw, your approach

  date = to_date(:date, 'YYYY-MM-DD')

isn't good practise. Such conversions should be done by client, not by server. Use

  date = :date

and assign it as date instead.

Date conversion is done on server on purpose. Otherwise there are so many places, where timezone mess could happen. — Denis Kulagin, Jan 16 '15 at 09:29

JDBC: multicolumn IN query

3 Answers3