As we learned from previous chapters, we have "built-in methods" for working with entities. These methods are located within the Session. But what if we need to write some specific query? For example, we want to retrieve only those employees who were hired in the year 2023. For such a query, we don't have a built-in function in the session. However, we can do this using an SQL query. It will look like this:

Note

Yes, you can use the BETWEEN operator here, but I really don't like using this function. You can write the query however you like, as long as it works correctly.

Great, now we need to understand how to implement this in the code. If you looked into the tests in the previous chapter and saw how they work, you might have noticed that I used a method of the session object called createNativeQuery().

Now, we'll talk more about this method.

Native Query

The createNativeQuery() method creates an SQL query, which is persisted until executeUpdate() or executeQuery() is called. Let's look at an example where we need to retrieve employees who were hired within a specific timeframe.

First, we need to add this method to the DAO interface:

As you can see, we use 2 parameters of type Date to specify the exact timeframe we are interested in. Now, let's move on to the implementation class and implement this method.

For this, we need to:

1. Open a session;
2. Write an SQL query;
3. Use the createNativeQuery() method where we use this query;
4. Add data from the parameters to these queries to specify the timeframe;
5. Use the getResultList() method to retrieve data from the query;
6. Execute the query and save the result in a List;
7. Catch any potential errors and close the session.

Overall, the procedure is very similar to the one we did earlier. Only here we use a full-fledged SQL query to interact with the database. We just slightly edited this query to set parameters there. Well, let's repeat:

If we want to set some parameters in the SQL query, we write a placeholder for these parameters directly in the query instead of inserting the parameters directly.

For this, we use the following syntax:

After that, we use the setParameter() method where we use the following syntax:

In this way, Hibernate automatically substitutes data from the method parameters (or any other data) directly into the SQL query.

Then we retrieve a list with the type Employee using the getResultList() method.

Note

The getResultList() method will return a list with the type that we specified in the createNativeQuery() method. We used Employee.class, so the returned list will have that type. For example, if we want to retrieve a list of names, we use String.class in the parameters.

Then, everything follows the algorithm, catching errors and closing the session.

HQL

Hibernate has its own query language that we can also use. It's called HQL (Hibernate Query Language).

HQL is a more convenient query language than SQL in the context of its use in code, but you can use any language you find convenient.

Let's consider the main benefits and differences from SQL:

• Object-oriented approach: HQL works with objects and their properties, while SQL works with tables and columns. This means that in HQL, you refer to classes and fields in Java code, while in SQL, you refer to actual table and column names in the database;
• Platform independence: HQL isolates the application from dependence on a specific database. You can switch between different DBMSs without modifying HQL queries, while SQL queries may require modifications because different DBMSs may have differences in syntax and functions;
• Association navigation: In HQL, it is easy to navigate through associated objects via their associations, whereas in SQL, you have to use table joins;
• Type safety: HQL provides type safety as it uses Java classes and properties, reducing the chances of errors related to data types.

Let's compare queries made in SQL and HQL to see the difference between these two query languages:

SQL query for selection:

Equivalent HQL query:

SQL query with JOIN:

Equivalent HQL query:

SQL query for update:

Equivalent HQL query:

In these examples, it can be seen that HQL does not use the SELECT keyword for selecting objects and accessing properties using dots, while SQL operates with actual column names and requires the use of the SELECT keyword.

HQL provides greater flexibility and power for the object-oriented model when working with databases and offers a variety of features to simplify development, such as pagination, named queries, and integration with caching.

Query

To use HQL, we should use the createQuery() method instead of createNativeQuery().

Let's modify the method written earlier to incorporate HQL:

As you can see, some code has changed. Now, we are using the Query interface instead of NativeQuery. These interfaces have almost identical methods, so we didn't have to edit the query much. This way, we operate on objects and clearly see which objects will be retrieved from the tables.

But how does Hibernate know which table to work with? Hibernate looks at the Entity class and sees which table is specified there and the corresponding columns of that table. This way, it automatically substitutes the necessary data and executes the query.

Service Layer Implementation

Let's finish the full implementation of the method and implement the getEmployeesHiredInASpecificTimeframe() method in the service layer.

First, we need to create such a method in the EmployeeService interface:

As you can see, we added an additional method that will retrieve only employee names. Similarly to what we did before, such a method should be located in the service layer.

Now, let's take a look at the implementation:

Overall, such code will work, but there is one significant caveat. It will be very inconvenient for us to work with a class like Date() since it does not accept any convenient format for us. Let's use the LocalDate class introduced in Java 8 to write a private parser that will parse the date from a String format "yyyy-mm-dd" into a Date object, making it more convenient for us and the user to work with methods:

This code will automatically parse the date from String to Date.

Let's break down how it does it:

1. We create a formatter object of the DateTimeFormatter class in which we specify the pattern according to which the date will be parsed;
2. Then, we store such a date in a variable of type LocalDate, using the parse() method and passing the formatter object as a parameter so that Java knows exactly what time format we need;
3. Next, the atStartOfDay(ZoneId.systemDefault()) method converts LocalDate to ZonedDateTime at the start of the day in the system's default time zone. This is necessary because LocalDate does not contain time information. The toInstant() method converts ZonedDateTime to Instant. Date.from(Instant) is used to obtain a Date object from Instant.

Now, we need to change the method so that it accepts a String as a parameter, making it more convenient for us to work with it.

First, we need to change the data type of the parameters in the interface class, and then we proceed to edit the implementation:

Here is how our final code will look. As you can see, we used a private method to convert the date from the String format to the format of the Date object class.

Thus, to use this method, we now only need to specify the date in the format "yyyy-MM-dd". Let's check the functionality of these methods in the main method to ensure that everything works correctly: