how to generate barcode in vb.net 2008 Introducing JPQL in Java

Printing Data Matrix 2d barcode in Java Introducing JPQL

Dealing with null values and empty collections So far we have been able to avoid discussing null and how an expression deals with null values. Alas, now it is time to deal with this little mystery. You have to remember that null is different from an empty string, and JPQL treats them differently. However, not all databases treat an empty string and null differently. We already know that JPQL is translated into SQL by the persistence provider. If the database returns true when an empty string is compared with null, you cannot rely on consistent results from your queries across two different databases. We recommend that you test this situation with your database. When a conditional expression encounters a null value, the expression evaluates to null or unknown. A complex WHERE clause that combines more than one conditional expression with a boolean operator such as AND may produce a result that is unknown. Table 10.9 lists the results of a conditional expression when it is compared with a null value.

Table 10.9 Results of boolean operations involving null Boolean Operator AND AND AND OR OR OR NOT Expression 2 Value null null null null null null null Result UNKNOWN FALSE UNKNOWN TRUE UNKNOWN UNKNOWN UNKNOWN

You can use the IS NULL or IS NOT NULL operator to check whether a single-value path expression contains null or not null values. If a single-value path expression contains null, then IS NULL will return true and IS NOT NULL will return false. If you want to compare whether the single-value path expression is not null, use the following WHERE clause:

You cannot use the IS NULL expression to compare a path expression that is of type collection; in other words, IS NULL will not detect whether a collection type path expression is an empty collection. JPQL provides the IS [NOT] EMPTY comparison

operator to check whether a collection type path expression is empty. The following WHERE clause would work when you want to retrieve all Category entities that do not have any Items:

As we explained earlier, JPQL statements are translated to SQL statements by the persistence provider. There is no equivalent of the IS EMPTY clause in SQL. So, you must be wondering what SQL statement is generated when IS EMPTY is used. The IS EMPTY clause is used with a collection-valued path expression that is typically an association field, and therefore the generated SQL statement will be determining whether the JOIN for the association retrieves any record in a subquery. To clarify, let s examine this JPQL query:

If you recall our discussions from chapters 7 and 8, a many-to-many relationship exists between Category and Item entities, with CATEGORIES_ITEMS as the intersection table. This means the persistence provider will generate the following SQL statement:

SELECT c.CATEGORY_ID, c.CATEGORY_NAME, c.CREATE_DATE, c.CREATED_BY, c.PARENT_ID FROM CATEGORIES c WHERE ( (SELECT COUNT(*) FROM CATEGORIES_ITEMS ci, ITEMS i WHERE ( (ci.CATEGORY_ID = c.CATEGORY_ID) AND (i.ITEM_ID = ci.ITEM_ID))) = 0)

From this generated SQL, you can see that the persistence provider uses a subquery to retrieve the number of associated items for a category by using the COUNT group function, and then compares the result with 0. This means that if no items are found, the collection must be empty, and the IS EMPTY clause is true. Have you ever had an occasion to detect the presence of a single value in a collection Sure you have! In JPQL you can use the MEMBER OF operator for just that purpose. Let s take a look at how it works.

Checking for the existence of an entity in a collection You can use the MEMBER OF operator to test whether an identifier variable, a singlevalue path expression, or an input parameter exists in a collection-value path expression. Here is the syntax for the MEMBER OF operator: