Java Reference
In-Depth Information
If you recall our discussions from
chapter 9
, 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 re-
trieve 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
expression returns
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 single-value
path expression, or an input parameter exists in a collection-value path expression. Here's
the syntax for the
MEMBER OF
operator:
entity_expression [NOT] MEMBER [OF] collection_value_path_expression
The
OF
and
NOT
keywords are optional and can be omitted. Here's an example of using an
input parameter with
MEMBER OF
:
WHERE :item MEMBER OF c.items
This condition will return
TRUE
if the entity instance passed (
as:item
) in the query ex-
ists in the collection of
c.items
for a particular category
c
.
