Where

WHERE expression

A huge variety of possible expressions, some of them database-dependent, are allowed in a WHERE clause. Regardless of the expression, parentheses ( ) are used to indicate grouping and precedence. Some of the possible elements in a where clause are shown in Table 8.4.

Table 8.4. Where Operations

Element	Examples
Mathematical operators	+, -, *, /
Binary comparison operators	=, >=, <=, <>, !=, like
Logical operations	and, or
String concatenation	||
JDBC IN parameters	?
Named parameters	:name, :start_date, :x1
SQL scalar functions	upper(), lower()
SQL literals	'foo', 69, '1970-01-01 10:00:01.0'
Java public static final constants	java.lang.Integer.MAX_VALUE

The interpretation of SQL functions is database-dependent. Hibernate will automatically convert the SQL literal ' delimiters to the proper characters for the database.

A where expression may include mathematical operations, logical operations, boolean operations, or a quantified expression (in SQL terms, a subselect).

Logical Operations

Two logical operations are commonly used in a where statement.

The and operator requires both sides to evaluate to true, otherwise it evaluates to false.

The or operator evaluates both sides. If either side evaluates to true, it will return true; otherwise it evaluates to false.

For example, the statement select student from Student as student where student.id=1 or student.id=3 will return two records, with an id of 1 and 2.

Boolean Operations

Table 8.5 lists the boolean operations supported by the majority of databases.

Table 8.5. Boolean Operations

Operator	Meaning	Inverse
!=	Is not equal	=
<	Less than	>=
<=	Less than or equal	>
<>	Is not equal	=
=	Equals	!= or <>
>	Greater than	<=
>=	Greater than or equal	<
between	Inclusive query (used with the and operator). For example, select student from Student as student where id between 1 and 3 will return students 1, 2, and 3.	not between
ilike (case-insensitive)	Case-insensitive like (not supported on all databases).	not ilike
in	Used to limit in conjunction with a collection. Alternatively, used in statements like select student from Student as student where first Name in ('Bob', 'John').	not in
is	Used for queries such as is null or is not null	is not
is not	Used as part of the is not null	is
l ike	Like	not like
not between	Reverse of between	between
not ilike (case-insensitive)	Reverse of ilike	llike
not in	Reverse of in	in
not like	Reverse of like	like

When using the like and ilike statements, the % (percent) character is used as a multiple-character wildcard, and the _ (underscore) character is used to indicate a single character. For example, the statement select student.firstName from Student as student where firstName like '_ob' returns the value Bob. The statement select student.firstName from Student as student where firstName like 'B%' returns Bob and Betty.

The tokens #, ~, !#, !~, =>, =<, !<, and !> are reserved by Hibernate as boolean operators but have no meaning in standard SQL. The meaning of these tokens (if any) is database-dependent. Consult your database manual for more information.

Quantified Expression

'exists' | ( expression 'in' ) | ( expression OPERATION 'any' | 'some' ) collection

Valid options for OPERATION are like, =, <, >, <>, !=, ^=, <=, and >=.

A qualified expression is an HQL mechanism for expressing a SQL subselect of a collection. A subselect describes using the results of a SELECT statement as part of the WHERE clause of another statementin effect, a nested select. The subselect must return a single column, which is compared against an expression using an operation. The evaluation of this operation determines whether the resulting record should be returned. A subselect is described by the term any, some, all, exists, or not exists.

any	some or any returns true when the comparison specified is true for any of the values returned by the subselect (typically a collection column). Imagine a subselect that returns the numbers 1 through 10. Therefore, the statement 5 < any (subselect expression) would evaluate to true.
some	Synonym for any.
all	Returns true when the comparison specified is true for all pairs (scalar_expression, x) where x is a value in the single-column set; otherwise returns false. Imagine a subselect that returns the numbers 1 through 10. Therefore, the statement 5 < any (subselect expression) would evaluate to false, because some of the results of the subselect are greater than or equal to 5.
exists	Returns true if the subselect matches elements from the select.
not exists	Reverse of exists.

Parameter Binding

When working with a WHERE clause, you may be tempted to include constants directly in the string. For example, you may wish to use a string such as select student from Student as student where firstName='Bob'. Instead, you are advised (and in some instances required) to use parameter binding. HQL supports two styles of parameter binding, JDBC and named. JDBC uses the ? character to indicate bindings, and named uses the : character to preface a named parameter substitution.

Use the Session and Query classes to set parameters, as described in Chapter 6. Listing 8.1 shows an example of the use of a named parameter.

Listing 8.1. Named Parameter Binding

Query myQuery = hibernateSession.
 createQuery("from Student as student where student.firstName =
  :name");
myQuery.setString("name", "Bob");
Iterator students = myQuery.iterate();

Listing 8.2 shows an example of the use of a JDBC-style bound parameter. Note that the parameters are set based on an index of 0, not 1.

Listing 8.2. JDBC Parameter Binding

Query myQuery = hibernateSession.
 createQuery("from Student as student where student.firstName =
  ?");
myQuery.setString(0, "Bob");
Iterator students = myQuery.iterate();