Java Reference
In-Depth Information
Working with JPQL functions
JPQL provides several built-in functions for performing different kinds of operations.
These functions can be used in either the
WHERE
or
HAVING
clause of a JPQL statement.
You'll learn more about the
HAVING
clause when we cover aggregate functions later in
this chapter. Right now we're going to explore the three kinds of JPQL functions:
• String functions
• Arithmetic functions
• Date-time functions
String functions
string functions supported by JPQL. These functions are used to filter the results of the
query. You have to use the functions available in the Java language if you want to perform
any string manipulations on your data. The primary reason is that in-memory string manip-
ulation in your application will be much faster than doing the manipulation in the database.
Table 11.5. JPQL string functions
String functions
Description
Returns the value of concatenating two strings or literals to-
gether.
CONCAT(string1, string2)
SUBSTRING(string, position, length)
Returns the substring starting at position that's length long.
Trims the specified character to a new length. The trim-
ming can be LEADING, TRAILING, or from BOTH ends.
If no trim_character is specified, then a blank space is as-
sumed.
TRIM([LEADING | TRAILING | BOTH] [trim_character]
FROM] string_to_trimmed)
LOWER(string)
Returns the string after converting to lowercase.
UPPER(string)
Returns the string after converting to uppercase.
LENGTH(string)
Returns the length of a string.
Returns the position of a given string within another string.
The search starts at position 1 if initialPosition isn't speci-
fied.
LOCATE(searchString,
stringToBeSearched[initialPosition])
Let's look at a couple of common string function examples. Suppose you want to compare
the result of concatenating of two string expressions with a string literal. The following
WHERE
clause will perform the task well:
WHERE CONCAT(u.firstName, u.lastName) = 'ViperAdmin'
