CODEWITHSUNDEEP
javasqloracle-databasejdbcjdbctemplate
Denis Kulagin
Denis Kulagin

Reputation: 8906

JDBC: multicolumn IN query

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)?

Upvotes: 0

Views: 1008

Answers (3)

gpeche
gpeche

Reputation: 22504

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).

Upvotes: 0

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

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.

Upvotes: 0

bedrin
bedrin

Reputation: 4586

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

Upvotes: 2

Related Questions