Course Module 4. Working with databases - Lecture: Criteria API Part 2

Grouping and aggregation functions

You have already figured out how to make simple requests to the Criteria API. Let's see how to make more complex queries.

For example, we want to write a query to determine the number of employees in a company. Here is how it will look like in HQL:

select count(*) from Employee

And like this on the Criteria API:

CriteriaQuery<Long> critQuery = builder.createQuery(Long.class);
critQuery.select(builder.count(critQuery.from(Employee.class)));

The complete Java code will look like this:

CriteriaBuilder builder = session.getCriteriaBuilder();

CriteriaQuery<Long> critQuery = builder.createQuery(Long.class);
critQuery.select(builder.count(critQuery.from(Employee.class)));

Query<Long> query = session.createQuery(critQuery);
Long count = query.getSingleResult();

And it's the same using HQL:

String hqlQuery = "select count(*) from Employee";

Query<Long> query = session.createQuery(hqlQuery);
Long count = query.getSingleResult();

Now let's try to calculate the average salary in the company. The HQL query will look like this:

select avg(salary) from Employee

And like this on the Criteria API:

CriteriaQuery<Double> critQuery = builder.createQuery(Double.class);
critQuery.select(builder.avg( critQuery.from(Employee.class).get("salary")));

The complete Java code will look like this:

CriteriaBuilder builder = session.getCriteriaBuilder();

CriteriaQuery<Double> critQuery = builder.createQuery(Double.class);
critQuery.select(builder.avg( critQuery.from(Employee.class).get("salary")));

Query<Double> query = session.createQuery(critQuery);
Double avgSalary = query.getSingleResult();

CriteriaUpdate

Modifying a table is as easy as getting data from it. To do this, CriteriaBuilder has a special method - createCriteriaUpdate() , which creates an objectCriteriaUpdate<T>The that updates the entities in the database.

Let's raise the salary of employees who receive less than 10 thousand. Here's what this HQL query would look like:

update Employee set salary = salary+20000 where salary<=10000

And this is how it will look on the Criteria API:

CriteriaUpdate<Employee> criteriaUpdate = builder.createCriteriaUpdate(Employee.class);
Root<Employee> root = criteriaUpdate.from(Employee.class);
criteriaUpdate.set("salary", "salary+20000");
criteriaUpdate.where(builder.lt(root.get("salary"), 10000));

Transaction transaction = session.beginTransaction();
session.createQuery(criteriaUpdate).executeUpdate();
transaction.commit();

CriteriaDelete

And deleting records is even easier than changing them. To do this, there is a special method createCriteriaDelete() , which creates an objectCriteriaDelete<T>.

Let's cut all employees who have no value: their salary is less than 10 thousand. Here's what this HQL query would look like:

delete from Employee where salary<=10000

And this is how it will look on the Criteria API:

CriteriaDelete<Employee> criteriaDelete = builder.createCriteriaDelete(Employee.class);
Root<Employee> root = criteriaDelete.from(Employee.class);
criteriaDelete.where(builder.lt(root.get("salary"), 10000));

Transaction transaction = session.beginTransaction();
session.createQuery(criteriaDelete).executeUpdate();
transaction.commit();

Benefits of the Criteria API

So what is the benefit of the Criteria API? Queries are cumbersome, HQL will definitely be more compact.

Firstly, HQL queries are not so short if you need to pass parameters to them. Compare:

We consider the number of employees with a salary of less than 10 thousand
HQL
`String hqlQuery = "from Employee where salary < :sal"; Query<Employee> query = session.createQuery(hqlQuery); query.setParametr("sal", 10000); List<Employee> results = query.getResultList();`
Criteria API
`CriteriaBuilder builder = session.getCriteriaBuilder(); critQuery.select(critQuery.from(Employee.class)).where(builder.lt(root.get("salary"), 10000)); Query<Employee> query = session.createQuery(critQuery); List<Employee> results = query.getResultList();`

Secondly, very often there is a situation when a query needs to be constructed dynamically. For example, you have a web page filtering employees, apartments, and anything. And if some parameter is not important to the user, then he simply does not indicate it. Accordingly, null is passed to the server instead.

Here is your task: to select employees with a certain profession (occupation), salary (salary) and year of employment (YEAR (join_date)). But if any parameter value is null, then don't use it in the filter.

Then the HQL query will look something like this:

from Employee
where (occupation = :ocp)
   	and (salary = :sal)
   	and ( YEAR(join_date) = :jny)

But it will not work correctly, since we want that if the "jny" parameter was null, then the request would look like this:

from Employee
where (occupation = :ocp)
   	and (salary = :sal)

We can try to rewrite the request with checking the parameter for null, then we will get something like this:

from Employee
where (occupation = :ocp or :ocp is null)
   	and (salary = :sal or :sal is null)
   	and ( YEAR(join_date)= :jny or :jny is null)

See how reality gets more complicated? Reality is often like this :)

But the filter can be complicated even more. How about searching for users who have tasks with the word "buy" in them? Or users who have overdue tasks?

from Employee
where (occupation = :ocp)
   	and (salary = :sal)
   	and (YEAR(join_date) = :jny)
   	and (tasks.name like '%buy%')
   	and (tasks.deadline < curdate())

If you write or is null somewhere in such a query , then this will not cancel the join between tables.

So in practice, when you do any complex filter on the fields of several tables, the Criteria API can just help you out. So it goes.

More details can be found in the official documentation .