Re: Prepared Statements and large where-id-in constant blocks?

James Robinson wrote:
> Howdy:
>
> Java middlewares like JBossCMP issue many queries in the general
> form of:
>
> SELECT t1.att1, t1.att2 ... t1.attN FROM t1 WHERE (t1.id = X) or
> (t1.id = Y) ....
>
> where there may be anywhere between 1 and thousands of "(id = N)" blocks
> ORed together.

> 2) Does the JDBC spec allow any hooks for passing in a set of ids as
> one single param? We'd need the SQL-template to be prepared to look
> something like:
>
> SELECT t1.attr1 FROM t1 where t1.id in ( ? )

This was discussed a while ago when fixing some SQL escaping issues.
Previously you could use setObject to get around the usual string
escaping and do the above if you constructed the IN set string yourself,
but the driver lost a lot of type information, and if the input was
hostile you were in trouble.

The JDBC spec doesn't seem to provide a portable way to fill an IN
clause with a single parameter. An extension for supporting IN lists
safely was discussed at the time, but nothing concrete came out of it.

The CMP layer could perhaps use the = ANY array syntax and setArray()
(note that setArray() in the current driver needs fixing, I have an old
patch to do this):

SELECT t1.attr1 FROM t1 where t1.id = ANY (?)

Alternatively, perhaps the CMP layer could generate a largish N-value IN
(?,?,?,...). Then it can reuse that single prepared query for all
queries with <= N values, filling the trailing parameters that aren't
needed with NULL or a dummy/duplicate value.

I don't know how these queries would perform compared to a
correctly-sized IN clause, though.

-O