The Where clause is used to narrow down the number of entities handled by a query. In the case of the Select statement, it determines the number of entities returned. With a Delete statement it determines which entities will be removed from a table.
The Where clause uses a number of operators to control which entities will be affected. In this recipe we will illustrate how many of them are used.
The Where clause consists of the WHERE keyword followed by a conditional expression. The conditional expression determines which entities are affected by the query. The Where clause is optional and if omitted will identify all of the entities in a table. If it is present, only those entities matching the conditional expression will be affected.
The process of using a Where clause consists of:
- Adding the WHERE keyword to the query
- Adding a condition expression
- Executing the query
The condition expression possesses a number of operators that can be used to select a set of entities. The expression will evaluate to either true or false. If the expression is true, then the row will be returned otherwise it will not be returned.
To illustrate these operators we will use a Select query. This query, in its initial form, simply returns all entities in the Medication table. The displayAll method implements this query and is passed a PrintWriter object. The query is created and executed with the resulting list being displayed.
public void displayAll(PrintWriter out) {
Query query = entityManager.createQuery("SELECT m FROM Medication m WHERE m.dosage = 10");
List<Medication> list = query.getResultList();
for (Medication medication : list) {
out.println(
"<h5>Medication ID: " + medication.getId() +
" Name: " + medication.getName() +
" Type: " + medication.getType() +
" Dosage: " + medication.getDosage() +
" Frequency: " + medication.getFrequency()+ "</h5>");
}
}
Add a call to displayAll in the PatientFacade class after the body tag is displayed:
...
out.println("<body>");
medicationFacade.displayAll(out);
Execute the servlet using the URL displayed in the following screenshot:
In the Select query, only those medications whose dosage level is 10 will be returned. It used the navigation operator to select the dosage field and used the equality operator to compare it to 10. The getResultList method was executed returning a list of medications. Each element of the list was then displayed using the PrintWriter object.
There are numerous operators available for use with a conditional expression. Specialized comparison operators include:
- [NOT] BETWEEN Used to define a range of values
- [NOT] LIKE Useful when used with wild card characters
- [NOT] IN Used with lists
- IS [NOT] NULL Whether the field is NULL or not
- IS [NOT] EMPTY Whether the field is EMPTY or not
- [NOT] MEMBER OF Used with collections
There are also three logical operators: NOT, AND, and OR and they perform as you would expect. The Where clause also supports these literals:
- String Enclosed in single quotes (Use two single quotes back-to-back to represent a single quote)
- Numeric Such as 12, 3.407, and +45
- Boolean Use either TRUE or FALSE
- Enum Any enum used as a field of an entity can also be used as part of the
Whereclause
Here we will examine several different types:
- Comparison operators
- Between operator
- Like operator
- IN and IS operators
The comparison operators are similar to those used in Java. They include:
|
Operator |
Symbol |
|---|---|
|
Equal |
= |
|
Greater than |
> |
|
Less than |
< |
|
Greater than or equal |
>= |
|
Less than or equal |
<= |
|
Not equal |
<> |
For example, the following query will select those medications whose dosage level exceeds 5.
SELECT m FROM Medication m WHERE m.dosage > 5
Arithmetic operators are also available and perform simple arithmetic operations including: unary + and -, *, /, + and -. These operators work in the same way as they do in Java.
The Between operator is used to determine whether a number falls between a range of values. We can also use arithmetic operators in conjunction with logical operators to make the same determination. For example, these two expressions are equivalent:
SELECT m FROM Medication m WHERE m.dosage >= 5 AND m.dosage <= 10 SELECT m FROM Medication m WHERE m.dosage BETWEEN 5 AND 10
Notice the entity and field name had to be used twice in the first expression. One of the advantages of the Between operator is the entity and field name is used only once. The Not operator is not inclusive, that is, in this case the dosage is not 5 or 10 or any number in between.
SELECT m FROM Medication m WHERE m.dosage <5 OR m.dosage > 10 SELECT m FROM Medication m WHERE m.dosage NOT BETWEEN 5 AND 10
Replace the query used in the MedicationFacde class's displayAll method with the following query:
SELECT m FROM Medication m WHERE m.dosage BETWEEN 5 AND 10
Execute the PatientServlet and you should get the results as shown in the following screenshot:
The Like expression allows us to use wild cards to specify a match using either strings or numbers. Two wild card characters are supported:
- % Percent which matches 0 or more characters
- _ Underscore character which matches any single character
For example, the % character can be used to match any medication name starting with a capital A.
SELECT m FROM Medication m WHERE m. name LIKE 'A%'
Replace the query used in the MedicationFacde class's displayAll method with this query and you should get the same values as shown in the following screenshot:
The underscore is used here to match any six character last name starting with 'B' and ending with 'nson' such as 'Benson' or 'Binson'.
WHERE patient.lastName LIKE 'B_nson'
If either of these wild card characters needs to be used as part of an expression, then they can be escaped using the back slash character. Here, the name must start with an underscore.
WHERE patient.lastName LIKE '\_%'
The IN operator can be used with strings or numbers to determine whether a value is in a set of values. The values representing the set are enclosed in parentheses. For example, the following illustrates using the IN operator to select from a set of frequencies.
SELECT m FROM Medication m WHERE m.frequency IN (1, 2) SELECT m FROM Medication m WHERE m.frequency NOT IN (1, 2)
Strings can also be used in a list.
SELECT m FROM Medication m WHERE m.type IN ('Anti-Fungal', 'Diuretic')
SELECT m FROM Medication m WHERE m.type NOT IN ('Anti-Fungal', 'Diuretic')
Replace the query used in the MedicationFacde class's displayAll method with the second to last query and you should get the same values as shown in the following screenshot:
The IS NULL operator determines whether a field is NULL. The following queries illustrate how this operator can be used.
SELECT m FROM Medication m WHERE m.type IS NULL SELECT m FROM Medication m WHERE m.type IS NOT NULL