[hibernate-dev] Continue "parameter list" support?

[Steve Ebersole]

To be clear, this is the feature that lets you define a query like:

select ... from Person p where p.name in (:names)

And then bind varied multiple values into that single parameter holder:

query.setParameterList( "names", new String[] { "Larry", "Curly", "Moe" } );
query.setParameterList( "names", new String[] { "John", "Jane" } );

Which magically transforms to the following (rough) SQL:

select ... from PERSON p where p.name in (?, ?, ?)
select ... from PERSON p where p.name in (?, ?)

Effectively parameter lists allow expansion of the HQL statement - they
literally are handled by altering the HQL on the fly as we prepare to
execute the query.  What that means is that we can really not cache these
queries, at least not until the parameters are bound (which kind of defeats
the purpose).

I'd like to discuss dropping support for parameter lists.  There are quite
a few reasons I would like to drop this support:

   1. This is the main culprit that leads to the ever-resurrecting
   discussion about DB limits on IN clauses.  The one valid use case I saw for
   that lead me to add multi-load support in 5.1.
   2. In terms of a QueryPlan cache, this support means we can never
   effectively cache the plans for these queries because the SQL is different
   every time we execute the query.  The problem though is that we do not know
   this until well after the point that we'd resolve the QueryPlan.
    chicken-egg.
   3. This is more an internal detail, but handling the parameter bindings
   for these differently gets quite complicated.
   4. An additional internal detail is that re-writing the HQL on the fly
   is problematic.  And some of that leaks to the user in terms of result
   caching and stats (which HQL do we use?).

I get that this can be a useful feature for apps that dynamically build
HQL, although really for dynamic query building I think a criteria approach
is more appropriate.  It is not so much supporting this feature that bugs
me, it's how we expose it.  So an alternative to dropping this support
would be to support it in a different way.  The main issue is that I would
like to *syntactically* understanding that a parameter placeholder will be
used for multi-valued parameter from the query itself.  This is a
beyond-JPA feature, so we definitely have some leeway here to define this
however we want.

I am open to suggestions as to the best syntax to declare that.

An alternative would be to make some assumptions.  Specifically, the only
time I can think this is used is inside an IN predicate.  Am I missing
others?  If that is the case, we could simply assume that a IN predicate
with a single parameter placeholder is going to be a multivalued
parameter.  That assumption holds valid even if just a single value is
bound.  The main win there is that we can get rid of the Query#setParameterList
variants.  setParameterList was only ever needed so that we could
understand that the parameter is multivalued - here we'd assume that from
its context as the IN predicate value.

Continuing to support parameters-lists in any form does not really
address point
(1) above; but that's ok - the user really could , But each of the
alternatives does help with the other problems currently stemming from
parameter-list support.