EJB3 QL: Using the COUNT function

If you are using EJB3 Query Language to build your application, you will probably need to use some functions other than the regular SELECT statements and logical WHERE clauses. One of the most simplest of these functions is the COUNT function.
The syntax for the COUNT function is very simple, and it receives only a parameter which is the identifier to be count, as in:

SELECT COUNT(c) FROM Customers AS c WHERE c.address.country = 'BR'

This query will count all the Customers who live in Brazil.

The COUNT function can be used with an identifier, in which case it always counts entities (as the example above demonstrates), or with path expressions but this last one can always be converted into an expression that counts entities only by managing the conditions in the WHERE clause.

Below is an example on how you could write a piece of code that would count the Patients from a medical database, depending on which Clinic they are registered to:


Query query = entityManager.createQuery("SELECT COUNT (p) FROM Patients p WHERE p.clinic.idtClinic = :idtClinic");
query.setParameter("idtClinic", idtClinic);
return (Long)query.getSingleResult();


I will write about other EJB QL functions later! :)

CU!