Hibernate provides a query language called Hibernate Query Language (HQL). HQL is similar to SQL in syntax, but HQL queries are written against Hibernate's entity objects, not database tables. Hibernate also provide Criteria Queries as an object-oriented alternative to HQL. Criteria Query is used to modify the objects and provide the restriction for the objects. Here we will see the basics of HQL and later in another tutorial we will see criteria queries.

If you are not following the tutorials in order, refer to the article http://javajee.com/lab-your-first-hibernate-43-program for the basic setup including hibernate configuration file. Change hbm2ddl.auto property to update and run that test class once to create a record, if you have not created a record already; if a record is already present with the same primary key, you will get duplicate key exception. You should also be familiar with Java Collection Lists.

In HQL, first you need to create a Query object. You can create a Query object from session as session.createQuery("From User"). The session.createQuery() method returns a org.hibernate.Query object NOT a javax.persistence.Query object.  Then you execute the query and retrieve results using the Query object methods. For instance, the query.list() method return the result as a List. We will first do a select all query using HQL. HQL doesn’t have a “select * from TableName” syntax. Instead, in HQL, you say “from ClassName”.  In HQL, you specify the class name instead of table name. A query.list() in this case will return a List of objects of type ClassName.  Complete test class is given below:

import java.util.List;

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.cfg.Configuration;

import com.javajee.hiberex.dto.User;

 

public class UserTest {

  public static void main(String[] args) {

Configuration configuration = new Configuration();

    configuration.configure();

    ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties()).build();

    SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);

    Session session = sessionFactory.openSession();

    session.beginTransaction();

    Query query = session.createQuery("From User");

    List<User> user = (List<User>)query.list();

    for (User us : user) {

      System.out.println(us.getName());

    }

    session.getTransaction().commit();

  }

}

Using where clause in HQL

You can add a where clause to the HQL statement similar to a SQL statement; but in an HQL “where clause”, you specify the field name rather than the column name.

Query query = session.createQuery("From User where id = 1 ");

You can try out similar examples to understand this better. There is no teacher better than experience.

Selecting fields in HQL

The “from ClassName” syntax returns a List of the class type with all fields in it. You can instead select one or more fields selectively than the table itself. If you are selecting a single column in the result, it will return a List of that field type.

   Query query = session.createQuery("Select name from User");

    List<String> names = (List<String>) query.list();

    for (String n : names) {

      System.out.println(n);

    }

Selecting multiple fields in HQL

If you select more than one field in an HQL statement, query.list will return a list of lists, one list each for each field.   

   Query query = session.createQuery("Select id, name from User");

    List<Object[]> users= (List<Object[]>)query.list();

    for(Object[] user: users){

        Integer id = (Integer)user[0];

        System.out.println(id);

        String name = (String)user[1];

        System.out.println(name);

    }

Using aggregate functions in HQL

We can use aggregate functions like max() in HQL similar to SQL:

  Query query = session.createQuery("Select max(id) from User");

    List<Integer> ids = (List<Integer>) query.list();

    for (int i : ids) {

      System.out.println(i);

    }

Note that you specify a field, not a column. Also, the query still return a List with a single value.

Pagination in HQL using Query API

Pagination is the process of dividing (content) into discrete pages, either electronic pages or printed pages. Assume that we have huge number of rows in the database. Retrieving all the rows together will be time consuming. Instead, we can retrieve few records at a time. For instance, record 1 to 10, then 10 to 20, and so on. We can make use of two methods of the Query class for this: query.setFirstResult(), and query.setMaxResults(). Method query.setFirstResult() is used to set the first record that needs to be returned and  query.setMaxResults() is used to set the maximum number of records that might be returned.

For instance, the below combination will return 10 records from the 11^th record:

query.setFirstResult(10);

query.setMaxResults(10);

query.setFirstResult(0) will return a list starting from the 1^st record and query.setFirstResult(10) will return a list starting from the 11^th record. query.setMaxResults(10) will return upto max 10 records. When used together as above, the query.list() will return 10 records from the 11^th record.

To try out pagination, you can add more records to the table using a for-loop as:

User user = null;

    for (int i = 0; i < 25; i++) {

Configuration configuration = new Configuration();

    configuration.configure();

    ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties()).build();

    SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);

    Session session = sessionFactory.openSession();

    session.beginTransaction();

      user = new User();

      user.setId(i);

      user.setName("user_" + i);

      session.save(user);

      session.getTransaction().commit();

    }

Once the rows are created, you can comment out the above code. Now try out pagination with different values for query.setFirstResult() and query.setMaxResults().