Course Module 4. Working with databases - Lecture: Learning to write queries in HQL

1.1 Introduction to HQL

Previously, you got acquainted with Hibernate, and now I will introduce you to HQL , aka Hibernate Query Language . In fact, this is SQL converted for writing queries in Hibernate. It has several key differences.

Using the class name instead of the table name.
Using the class field name instead of the table column name.
Optional use of select.

Let's ask Hibernate to return to us all the users that it has in the database. Here's what that request would look like:

from User

That's all, for comparison, we present a similar query in SQL:

select * from user

Here Useris the name of the class, and useris the name of the table.

The complete Java code will look like this:


public List<User> getAllUsers() {
    try (Session session = sessionFactory.openSession()) {
            return session.createQuery("from User", User.class).list();
    }
}

Otherwise, HQL is very similar to SQL - it also has operators:

WHERE
ORDER BY
GROUP BY
HAVING

1.2 Example of working with HQL

Perhaps the previous example is a bit confusing due to the same table and field names. Let's come up with a special example where this would be easier.

Let's say we have a user_data table that contains the following fields:

id INT
username VARCHAR(100)
user_level INT
user_created DATE

We will create a Java class that will map to this table:


@Entity
@Table(name="user_data")
class User {
   @Id
   @GeneratedValue
   public Integer id;
 
   @Column(name="user_name")
   public String name;
 
   @Column(name="user_level")
   public Integer level;
 
   @Column(name="user_created")
   public Date created;
}

Now let's write some examples:

HQL	SQL
from User	select * from user_data
from User where id=3	select * from user_data where id=3
from User where level in (10,20,30)	select * from user_data where user_level IN (10, 20, 30)
from User order by created asc	select * from user_data order by user_created asc
from User where name like 'test'	select * from user_data where user_name like 'test'

The queries are very similar, and reading HQL queries when you are familiar with class names and their fields is just as easy as reading SQL queries. It may be a little more difficult to write, but then again, very complex queries are rarely written in HQL.

1.3 Using select

In HQL, you can use selectwhen the data type of the result does not match the type specified in from.

For example, we want to get the names of all users that are in our user_data table , then we need to write the following query:

select name from User

Also, if there are duplicates among the names, then you can use the operator DISTINCT:

select distinct name from User

Aliases work the same as in SQL:

select distinct u.name from User u where u.created > '2020-01-01'

Well, completely in the form of Java code, this request will look like this:

public List<String> getUserNames() {
    try (Session session = sessionFactory.openSession()) {
            String hql = "select distinct u.name from User u where u.created > '2020-01-01'";
            Query<String> query = session.createQuery(hql , String.class);
            return query.list();
    }
}

Note that the query must return a list of names. Names are of type String, so both the type of the function and the type parameter of the Query class are of type String.