Use of parameters in IN statement

[Farrukh Najmi]

Dear colleagues,

Does EJBQL grammar support named/positional in the IN statement?

According to 4.6.4 Input Parameters of spec:

"Input parameters can only be used in the WHERE clause or HAVING clause
of a query."

Since an IN expression is within a WHERE clause I assumed I could use
parameter within the IN statement.

However, when I use the :type parameter in query below I get no errors
and no matches (I was expecting a match):

SELECT DISTINCT Object(p) FROM PersonType p, AssociationType a WHERE
    (a.sourceObject = 'urn:wellfleetsoftware:Association:farrukhNajmi' AND
     p.id = a.targetObject AND
     a.type IN ( :type ))

When I replace :type in above with the parameter value in the query
(hard code it) then all works as expected and I get some matches.

Is this expected? If so where is it defined in the spec? Thanks.

--
Regards,
Farrukh

Web: http://www.wellfleetsoftware.com

[Michael Bouschen-2]

Hi,

is :type a collection or a single value? The Java Persistence Query Language does not support collections as part of the IN expression. But if :type is a single value, then the expression
    a.type IN ( :type )
is the same as
    a.type = :type

Section "4.6.8 In Expressions" of the Java Persistence API specification defines the IN expression:
  in_expression ::=
    state_field_path_expression [NOT] IN ( in_item {, in_item}* | subquery)
  in_item ::= literal | input_parameter

This means you can use an input parameter in the list of IN-expression items, but it must have the same type the expression left of the IN keyword. So if 'a.type' is a string, the following expression is legal if the parameter is of type string:
  ... a.type IN ('int', 'float', :type) ...

If this is what you are trying, please run the query in debug mode and check the generated SQL including the binding of the SQL input parameters whether the correct valued gets bound.

I hope this helps.

Regards Michael

Dear colleagues,

Does EJBQL grammar support named/positional in the IN statement?

According to 4.6.4 Input Parameters of spec:

"Input parameters can only be used in the WHERE clause or HAVING clause of a query."

Since an IN expression is within a WHERE clause I assumed I could use parameter within the IN statement.

However, when I use the :type parameter in query below I get no errors and no matches (I was expecting a match):

SELECT DISTINCT Object(p) FROM PersonType p, AssociationType a WHERE
   (a.sourceObject = 'urn:wellfleetsoftware:Association:farrukhNajmi' AND
    p.id = a.targetObject AND
    a.type IN ( :type ))

When I replace :type in above with the parameter value in the query (hard code it) then all works as expected and I get some matches.

Is this expected? If so where is it defined in the spec? Thanks.

[Michael Bouschen-2]

Hi,

are you using a JPA 1.0 or JPA 2.0 implementation?

The IN-expression has been extended in JPA 2.0
  in_expression ::=
    {state_field_path_expression | type_discriminator} [NOT] IN
    { ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }

Now you can use a collection valued input parameter, such as
  ... a.type IN :types
and types is e.g. a collection of strings. Please note, in this case there are no parenthesis around :types.

The above is not supported in JPA 1.0.

Regards Michael

Hi,

is :type a collection or a single value? The Java Persistence Query Language does not support collections as part of the IN expression. But if :type is a single value, then the expression
    a.type IN ( :type )
is the same as
    a.type = :type

Section "4.6.8 In Expressions" of the Java Persistence API specification defines the IN expression:
  in_expression ::=
    state_field_path_expression [NOT] IN ( in_item {, in_item}* | subquery)
  in_item ::= literal | input_parameter

This means you can use an input parameter in the list of IN-expression items, but it must have the same type the expression left of the IN keyword. So if 'a.type' is a string, the following expression is legal if the parameter is of type string:
  ... a.type IN ('int', 'float', :type) ...

If this is what you are trying, please run the query in debug mode and check the generated SQL including the binding of the SQL input parameters whether the correct valued gets bound.

I hope this helps.

Regards Michael

Dear colleagues,

Does EJBQL grammar support named/positional in the IN statement?

According to 4.6.4 Input Parameters of spec:

"Input parameters can only be used in the WHERE clause or HAVING clause of a query."

Since an IN expression is within a WHERE clause I assumed I could use parameter within the IN statement.

However, when I use the :type parameter in query below I get no errors and no matches (I was expecting a match):

SELECT DISTINCT Object(p) FROM PersonType p, AssociationType a WHERE
   (a.sourceObject = 'urn:wellfleetsoftware:Association:farrukhNajmi' AND
    p.id = a.targetObject AND
    a.type IN ( :type ))

When I replace :type in above with the parameter value in the query (hard code it) then all works as expected and I get some matches.

Is this expected? If so where is it defined in the spec? Thanks.

[Farrukh Najmi]

Hi Michael,

Many thanks for your helpful responses. The :type parameter is a collection_valued_input_parameter. Thus I must use JPA 2.0 in order for this to work.
Thanks for the tip. I tried using hibernate-entitymanager with JPA 2.0 support and found that it did not support collection_valued_input_parameter in IN expression. So I have filed the following JIRA issue:

<http://opensource.atlassian.com/projects/hibernate/browse/HHH-4541>

BTW, it seems a the JPA 2.0 spec does not support a mix of literal | single_valued_input_parameter | collection_valued_input_parameter.
IMHO the following modified grammar would have been even more useful.
in_expression ::=
        {state_field_path_expression | type_discriminator} [NOT] IN
                   { ( in_item {, in_item}* ) | (subquery) }
in_item ::= literal | single_valued_input_parameter | collection_valued_input_parameter

Any guesses why this is not supported in the JPA 2.0 spec? Thanks again for your valuable help.

Michael Bouschen wrote:

Hi,

are you using a JPA 1.0 or JPA 2.0 implementation?

The IN-expression has been extended in JPA 2.0
  in_expression ::=
    {state_field_path_expression | type_discriminator} [NOT] IN
    { ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }

Now you can use a collection valued input parameter, such as
  ... a.type IN :types
and types is e.g. a collection of strings. Please note, in this case there are no parenthesis around :types.

The above is not supported in JPA 1.0.

Regards Michael

Hi,

is :type a collection or a single value? The Java Persistence Query Language does not support collections as part of the IN expression. But if :type is a single value, then the expression
    a.type IN ( :type )
is the same as
    a.type = :type

Section "4.6.8 In Expressions" of the Java Persistence API specification defines the IN expression:
  in_expression ::=
    state_field_path_expression [NOT] IN ( in_item {, in_item}* | subquery)
  in_item ::= literal | input_parameter

This means you can use an input parameter in the list of IN-expression items, but it must have the same type the expression left of the IN keyword. So if 'a.type' is a string, the following expression is legal if the parameter is of type string:
  ... a.type IN ('int', 'float', :type) ...

If this is what you are trying, please run the query in debug mode and check the generated SQL including the binding of the SQL input parameters whether the correct valued gets bound.

I hope this helps.