Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Querying on null parameter values 
Thanks, I was wondering if maybe there was a property or query hint
that could be used.  I'm assuming setting prepare to "false" has other
implications though?  Or does this hint not refer to jdbc prepared
statements?  When I was first have issues with this I actually ran a
debugger on eclipselink to see if it was binding the null values
correcting using the setNull method of prepared statement and it does
actually detect the null value and properly uses setNull when it's
null... but the query that's generated wasnt affected.

Anyway thanks for the hint about the hint :)

On Wed, Nov 9, 2011 at 11:00 AM, James Sutherland <jamesssss@xxxxxxxxx> wrote:
>
> One solution is to set the query to not prepare itself using the query hint,
>
> "eclipselink.prepare"="false"
>
> This should cause IS NULL to be used.
>
>
> Phillip Ross-4 wrote:
>>
>> Thanks Bernard.
>>
>> I did some cursory searching for the issue on google before sending my
>> message to the mailing list and I was super surprised that I didnt get
>> any quick hits with people running into the same thing.  I do know
>> that databases not supporting =NULL and making you do IS NULL is
>> literally a decades old thing that devs run into.  And I thought for
>> sure EclipseLink handled the automatic creation of =NULL versus IS
>> NULL predicates depending on the parameter values, but now seeing the
>> bug you filed and Tom's response, I guess I just have false memories
>> :)
>>
>> Your solution for writing the query to check for null then use an OR
>> operator is a neat workaround.  I'll play with it but I usually avoid
>> relying on inherent behaviors such as query expression evaluation
>> short circuiting.  I've since just rewritten code to use the criteria
>> API and evaluate the parameters as I'm building the expression using
>> the equals or isNull methods depending on the parameter value.  I'm
>> already suspecting there are going to be performance problems due to
>> using these null checks for locating records due to a lot of databases
>> not being able to utilize indexes to find null values :(
>>
>> I voted for the issue in bugzilla.  Thanks again for the response!
>> - Phillip
>>
>>
>> On Fri, Nov 4, 2011 at 2:10 AM,  <bht@xxxxxxxxxxxxx> wrote:
>>> Phillip,
>>>
>>> You are one of probably thousands of developers who encounter this
>>> type of issue. Your chances to get the someColumnName = null test to
>>> work are fairly slim, because SQL dictates that that "IS NULL" must be
>>> coded instead for null values.
>>>
>>> There are varying outcomes with "=" even with the same database but
>>> different drivers such as in Sybase native versus Sypacce over ODBC.
>>> Alternatively, you can code in your named query:
>>>
>>> select object(p) from Purchase p where :detail1 is null or p.detail1 =
>>> :detail1
>>>
>>> This is efficient, elegant, compact and correct if one accepts that
>>> the database is smart enough to avoid the second test if the first
>>> (null is null) is true.
>>>
>>> Unfortunately, even this fails with EclipseLink 2.3.0 on postgresql or
>>> derby.
>>>
>>> This issue has just been discussed under the subject "Cannot pass null
>>> parameter"
>>>
>>> Please refer to
>>>
>>> http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples
>>>
>>> and
>>>
>>> https://bugs.eclipse.org/bugs/show_bug.cgi?id=362414
>>>
>>> I would suggest to:
>>>
>>> 1) study the testcase and file a bug with the providers of these
>>> stupid SQL database engines
>>>
>>> 2) vote for the EclipseLink issue
>>>
>>> Kind Regards,
>>>
>>> Bernard
>>>
>>>
>>> On Thu, 3 Nov 2011 16:33:31 -0400, you wrote:
>>>
>>>>Hi all... I'm looking for a little assistance on using named queries
>>>>using parameters that may have null values.  I'm positive this worked
>>>>before but it's not working for me now and I'm wondering if I'm
>>>>running into incorrect usage scenario or a bug.
>>>>
>>>>Basically I have an entity with some fields that are nullable and a
>>>>named query that uses them in the query.  Example code:
>>>>
>>>>query annotation:
>>>>@NamedQuery(name="Purchase.findByDetails", query="select p from
>>>>Purchase p where p.detail1 = :detail1 and p.detail2 = :detail2 and
>>>>p.detail3 = :detail3")
>>>>
>>>>The property declarations in the Purchase entity:
>>>>private String detail1;
>>>>private String detail2;
>>>>private String detail3;
>>>>
>>>>Example query code:
>>>>query = entityManager.createNamedQuery("Purchase.findByDetails");
>>>>query.setParameter("detail1", detail1);
>>>>query.setParameter("detail2", detail2);
>>>>query.setParameter("detail3", detail3);
>>>>purchases = query.getResultList();
>>>>
>>>>
>>>>So when the content of detail1, 2, 3 parameters are null, the query
>>>>fails to return records where the values of detail1/2/3 are null.
>>>>This is surprising as I was positive that the provider would
>>>>automatically handle the translation to database platform specific
>>>>clauses that would use "is null" predicates if necessary.  This is NOT
>>>>what happens with eclipselink 2.3.0 on postgresql or derby.  It simply
>>>>doesnt return the results.
>>>>
>>>>Am I just doing this wrong and really have to dynamically build the
>>>>JPQL query to use JPQL "is null" with null checks on the parameter
>>>>values and all of that?  Or am I running into a bug?
>>>>
>>>>Thanks
>>>>- Phillip
>>>>_______________________________________________
>>>>eclipselink-users mailing list
>>>>eclipselink-users@xxxxxxxxxxx
>>>>https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@xxxxxxxxxxx
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>
>>
>
>
> -----
> http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
> http://www.eclipse.org/eclipselink/
>  EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
> TopLink
> Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink ,
> http://wiki.oracle.com/page/TopLink TopLink
> Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
> http://www.nabble.com/EclipseLink-f26430.html EclipseLink
> Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
> Blog:  http://java-persistence-performance.blogspot.com/ Java Persistence
> Performance
> --
> View this message in context: http://old.nabble.com/Querying-on-null-parameter-values-tp32776036p32811897.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

Back to the top