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

[andrea boriero]

I am also not able to figure out another use case than the IN predicate so
I am for always considering IN predicates as multi-valued.

On 9 September 2016 at 14:20, Steve Ebersole <steve at hibernate.org> wrote:

> 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.
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>