checking for null parameters

[dominicus]

I want to check if an input parameter is null before use this parameter in a join.
In SQL the sentence is something like that
SELECT ... WHERE is null or . =
The idea is to allow a single finder method to work with the input parameters not restricting the search if they are null.
Can this be expressed using EJBQL

[jboynes]

No, it can't.

Generally, this isn't a good idea in SQL anyway. If your database caches execution plans (Oracle, SQL Server, ...) this will typically result in the most general plan, probably a full table scan.

You are better off using two finders/SQL queries - one with a (not null) param and one omitting it completely. If you have a large number of 'optional' parameters consider dynamic EJB-QL/SQL.

[dominicus]

I have a response, but I can not read it. What happens?

[dominicus]

Now I can see the response.

[erik777]

I too need to check for NULL in a parameter. This value is VALID and has meaning, so it is irrelevant how the DBMS processes the query. In the application, this implies a "global" entity, rather than simply an entity missing a foreign relationships. Using NULL to represent this prevents having to insert a dummy row in the parent tables and hardcoding its primary key to represent the concept.

We need this added to EJB-QL, or at least JBoss-QL in the meantime. It is rediculous for the standards body to dictate what is important to our applications.

[erik777]

One more note. This problem exists because

WHERE table.column = ?1

returns no rows when ?1 is null, even if null entries exist. You have to instead use

WHERE table.column = ?1 OR
(table.column IS null AND ?1 IS NULL)

which is valid SQL, but not valid EJB-QL.

To remedy this in a two parameter query, I had to turn one query into four queries, and use IF logic on the code based on whether the parameters were null. I had to do this in several places as well, significantly and unnecessarily increasing the size and maintenance of my ejb-jar and code.

For those that think this is not ideal SQL, I reply that I do not consider it ideal to create dummy parent rows and hardcoding their primary keys in the code. I consider this poor practice in all cases, yet especially true when the PK is auto generated.

Besides being a pain to setup and maintain, it would also require extensive additional coding and additions to other queries to exclude the dummy rows from showing up, as it should in effect be hidden from the user, or at least the user should never be able to delete or updated when maintaining the tables. This scenario does not appeal to me, and is rediculous when you can accomplish the same simple goal with a comparison in queries for a null value.

[jboynes]

There is a reason why SQL and EJB-QL don't use the normal operators for checking null values. For example, the expression "A = (A+1)" would evaluate to true if A was null. The effects when used in join operations are even worse.

The "?1 IS NULL" syntax is not valid in EJB-QL2.0 but will be in 2.1 (at least it's in the pfd).

However, be aware of the consequences of using it. This type of expression often causes the database to fall back to a table scan even if your column is indexed (as null values are typically not included in the index). So whilst this may work and save some coding, it may kill performance if you have a large number of rows.