Criteria API

Introduction to the Criteria API

There are two ways to write database queries in Hibernate:

• Hibernate Query Language
• Criteria API

You have already met the first one a long time ago, it is time to get acquainted with the Criteria API. This is a very powerful tool, at some point it was even more popular than HQL. Now it is not so popular anymore, but for some tasks it will definitely be a better solution than HQL.

In any case, you can't learn Hibernate without getting familiar with the Criteria API. Let's write a small example, and then we will analyze it. For example, we will request all employees (Employee) from the database. Here's what we'll get:

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Employee> critQuery = builder.createQuery(Employee.class);

Root<Employee> root = critQuery.from(Employee.class);
critQuery.select(root);

Query<Employee> query = session.createQuery(critQuery);
List<Employee> results = query.getResultList();

Looks complicated. Let's write the same HQL query for comparison:

String hqlQuery = "from Employee";

Query<Employee> query = session.createQuery(hqlQuery);
List<Employee> results = query.getResultList();

Note that the last two lines of both examples are almost identical: we create a Query object and use it to get a List. This hints that the rest of the lines are doing something identical.

Look at lines 3 and 4 of the first example:

Root<Employee> root = critQuery.from(Employee.class);
critQuery.select(root);

Let's write them in one line:

critQuery.select(critQuery.from(Employee.class));

Doesn't it remind you of anything? And if you color it a little differently:

critQuery.select(critQuery.from(Employee.class));

Yes, this is such a tricky construction of the SELECT FROM query.

Examples of working with the Criteria API

For a better understanding, I will just give a few examples.

Request 1 . Get all employees with a salary above 10 thousand:

critQuery.select(critQuery.from(Employee.class)).where(builder.gt(root.get("salary"), 10000));

Request 2 . Get all employees with a salary of less than 50 thousand:

critQuery.select(critQuery.from(Employee.class)).where(builder.lt(root.get("salary"), 50000));

Request 3 . Get all employees whose job title contains the word "test":

critQuery.select(critQuery.from(Employee.class)).where(builder.like(root.get("occupation"), "%test%"));

Request 4 . Get all employees with a salary of 10 to 50 thousand:

critQuery.select(critQuery.from(Employee.class)).where(builder.between(root.get("salary"), 10000, 50000));

Request 5 . Get all employees whose name is null:

critQuery.select(critQuery.from(Employee.class)).where(builder.isNull(root.get("name")));

Request 6 . Get all employees whose name is not null:

critQuery.select(critQuery.from(Employee.class)).where(builder.isNotNull(root.get("name")));

It's just such a tricky way to construct a query:

• First you get the objectCriteriaBuilder.
• Then use it to create an objectCriteriaQuery.
• Then you start adding parts to it withCriteriaQueryAndCriteriaBuilder.

This is how you can set parameters for:

• SELECT
• FROM
• WHERE

Also usingCriteriaBuilderyou can construct different conditions for WHERE.

Advanced work with Criteria API

Using the Criteria API, you can construct a query of any complexity. And this is great news. For example, you want a complex WHERE clause. Here's how to do it:

Predicate greaterThan = builder.gt(root.get("salary"), 1000);
Predicate testers = builder.like(root.get("occupation"), "test%");

critQuery.select(critQuery.from(Employee.class)).where(builder.or(greaterThan, testers));

If you want to write AND instead of OR, then you only need to change the last line:

critQuery.select(critQuery.from(Employee.class)).where(builder.and(greaterThan, testers));

Everything is actually very simple. Let me give you a table with a few comparisons:

Everything is simple, isn't it?

And how do we add sorting to the query? Very simple:

critQuery.select( critQuery.from(Employee.class) );
critQuery.where( builder.and(greaterThan, testers) );
critQuery.orderBy( builder.asc(root.get("salary"), builder.desc(root.get("joinDate") )

You just call on the objectCriteriaQueryorderBy() method and pass the required parameters to it.

Here's how the same query would look in HQL. Compare:

select * from Employee
where (…) and (…)
order by 'salary' asc, 'joinDate' desc

You just need to remember 3 things:

• Key operators like SELECT, FROM, WHERE are called on an objectCriteriaQuery.
• Auxiliary operators like AND, OR, DESC are called on the objectCriteriaBuilder.
• Field names are taken from the object via get()root.

1

Task

Module 4. Working with databases, level 16, lesson 0

Locked

Introducing the Criteria API

task1601

Open

1

Task

Module 4. Working with databases, level 16, lesson 0

Locked

Result filter in Criteria API

task1602

Open