Course Module 4. Working with databases - Lecture: Request length limit

Working with Pages: LIMIT and OFFSET

Very often, data from a database is retrieved in “pages”. Because that's how it's convenient to work with large lists. We first query rows 1 to 20, then 21 to 40, and so on.

This is such a common situation that SQL has special operators for this LIMIT and OFFSET.

Analogs of these operators are also present in Hibernate. Only they immediately decided to make them in the form of separate methods:

setFirstResult() is analogous to OFFSET .
setMaxResults() is analogous to LIMIT .

Writing queries using these methods is very easy. Let's write a query where we ask to return 20 tasks starting from 41. This is how it will look like:

Query<EmployeeTask> query = session.createQuery( “from EmployeeTask”, EmployeeTask.class);
query.setFirstResult(41);
query.setMaxResults(20);
List<EmployeeTask> resultLIst = query.list();

Sort results

We figured out how to get part of the result strings of a query. The next pressing issue is sorting.

Sorting in Hibernate is written directly in the HQL query and looks generally expected:

from Employee order by joinDate

If you want to change the sort order, then you need to use familiar words: asc and desc. Example:

from Employee order by joinDate desc

Just like in SQL, you can sort by multiple fields:

from Employee order by joinDate desc, name asc

In addition, the sort field can be passed as a parameter:

String hql = "from EmployeeTask where employee.name = :username order by :ord";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameter(“username”, “Ivan Ivanovich”);
query.setParameter(“ord”, “name”);

List<EmployeeTask> resultLIst = query.list();

Functions in HQL

Just like in SQL in HQL, you can use various functions.

Here is a list of aggregate functions that HQL supports:

Aggregate functions	Description
count()	Returns the number of rows
sum()	Calculates the sum of values
min()	Returns the minimum value
max()	Returns the maximum value
avg()	Returns the mean

As you hopefully remember, aggregate functions are the ones that can be used in conjunction with group by. Group By works exactly the same as in SQL, so we won't repeat ourselves.

And, of course, the usual functions . With ordinary functions, everything is much simpler - they are performed by the SQL server, Hibernate only needs to correctly generate a query using them.

Therefore, Hibernate supports all the features that SQL Server supports. And the type of the SQL server is set by the sqlDialect parameter when you configure your SessionFactory.

Let's find the number of tasks assigned to a user:

String hql = "select count(*) from EmployeeTask where employee.name = :username ";
Query<Integer> query = session.createQuery( hql, Integer.class);
query.setParameter(“username”, “Ivan Ivanovich”);
Integer count = query.uniqueResult();