Java Reference
In-Depth Information
This will return all of the
JOB
records, grouped by job title, listing the number of each job that contains an
Employee
whose status is
ACTIVE
, as shown below.
HR TITLE A 1
HR TITLE B 0
IT TITLE A 0
IT TITLE B 0
TITLE A 0
The following method includes the JPQL that is the equivalent of the SQL above, and it uses the new
ON
condition.
This method, named
activeEmployeeCount
, can be found within the
org.javaee7.jpa.session.EmployeeSession
file.
public List obtainActiveEmployeeCount() {
TypedQuery<Object[]> qry = em.createQuery("SELECT j.title, count(e) "
+ "FROM Jobs j LEFT JOIN j.employees e "
+ "ON e.status = 'ACTIVE' "
+ "WHERE j.salary >= 50000 "
+ "GROUP BY j.title", Object[].class);
List data = new ArrayList();
if (!qry.getResultList().isEmpty()) {
List<Object[]> tdata = qry.getResultList();
for (Object[] t : tdata) {
HashMap resultMap = new HashMap();
resultMap.put("title", t[0]);
resultMap.put("count", t[1]);
data.add(resultMap);
}
}
return data;
}
In the end, the
ON
condition helps to make JPQL outer joins more concise and easy to use. Although the same
capability has been available in previous versions of JPQL, but the
ON
clause helps to make record filtering with joins
much easier.
Invocation of Database Functions
It is possible to invoke database functions directly within a JPQL query using the new function invocation syntax. Both
predefined database functions or user-defined database functions are supported for use via this feature. The
FUNCTION
keyword can be used within a query to invoke a database function using the following syntax:
FUNCTION(function_name {,args}*)
When calling a database function, zero or more arguments can be passed to the function by listing them after
the String-based name of the function, separated by commas. The following example JPQL invokes a function named
“
aboveTaxBracket
”, passing the employee salary as an argument.
SELECT e FROM Employee e WHERE FUNCTION('aboveTaxBracket', e.salary)
