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

[Vlad Mihalcea]

Hi,

I don't think we should deprecate such a feature.

First, the JPA specs says the follows:

"All input parameters must be single-valued, except in IN expressions (see
section 4.6.9), which support
the use of collection-valued input parameters."

So, we kinda need to support it one way or another.

Also, the JPA specs says that:

"Note that use of a collection-valued input parameter will mean that a
static query cannot be precompiled."

So, it's expected to have such a behavior.

I don't think that multi-load support can replace paremeterList since the
former cannot use any property from a given entity.
Also, the IN predicate with parameter list applies to DTO projections or
native queries, so it's useful to have it.

Vlad

On Fri, Sep 9, 2016 at 4:03 PM, andrea boriero <andrea at hibernate.org> wrote:

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