Hibernate provide Criteria Queries as an object-oriented alternative to HQL. Criteria Query is used to modify the objects and provide restriction for the objects. Many complex queries in HQL or even SQL may become larger over a period of time and spread over many lines; and will be difficult to update and maintain. We have already seen HQL in http://javajee.com/introduction-to-hibernate-query-language-hql-in-hibernate-43. Instead of writing an SQL like query, Criteria API works directly on the object setting conditions and restrictions; and hence is more object-oriented.

We will understand things better with examples. If you are not following the tutorials in order, refer to the article www.javajee.com/your-first-hibernate3-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.

Restrictions

There are two important concepts when dealing with Criteria API – Restrictions and Projections. Restrictions in Criteria API is similar to a where clause in HQL or SQL. Through Restrictions, we can filter the data returned, similar to a where clause.

Make the below changes in the UserTest class (inside main method) to see how Criteria API work:

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();

 

    Criteria criteria = session.createCriteria(User.class);

    criteria.add(Restrictions.eq("name", "user_11"));

    List<User> users = (List<User>) criteria.list();

    for (User u : users) {

      System.out.println(u.getId());

    }

    session.getTransaction().commit();

First we create org.hibernate.Criteria object using session.createCriteria() method passing in the class on which we want to apply restrictions. Now we can use Criteria.add(Criterion) method to add different criterion (or restrictions).  We add criterion (or restrictions) using the static methods of the org.hibernate.criterion.Restrictions class, like eq, ge, gt, like, between, in, isEmpty, isNotEmpty, isNull, isNotNull, sizeEq, sizeGe, sizeGt, sqlRestriction etc.

Chaining of Criteria Restrictions

Criteria.add(Criterion) method returns a Criteria object and hence you can chain different criteria operations.

Consider two restrictions on a Criteria:

criteria.add(Restrictions.eq("name", "user_11"));

criteria.add(Restrictions.like("name", "user_1%"));

You can instead chain them and get the same result as:

criteria.add(Restrictions.eq("name", "user_11")).add(Restrictions.like("name", "user_1%"));

When you add multiple restrictions on a criteria either in different lines or through chaining, it will be an AND operation when it is converted to native SQL by hibernate. For instance, query generated by hibernate with above restrictions will be:

select this_.id as id1_3_0_, this_.name as name2_3_0_ from User this_ where this_.name=? and this_.name like ?

So what if you want an OR instead? You can use Restrictions.or() within a Restrictions.add() as:

criteria.add(Restrictions.or(Restrictions.eq("name", "user_11"), Restrictions.like("name", "user_1%")));

Now hibernate will generate a query as:

select this_.id as id1_3_0_, this_.name as name2_3_0_ from User this_ where (this_.name=? or this_.name like ?)

Hibernate Criteria SQL Restriction

Restrictions.sqlRestriction(String sql) method apply a constraint expressed in SQL. Any occurrences of {alias} will be replaced by the table alias. The SQL statement you pass should be a constraint; in other words, it should go in the where clause of a regular SQL statement. As we had mentioned in the beginning, Criteria Restrictions are similar to where clause in HQL/SQL.

criteria.add(Restrictions.sqlRestriction("id=(Select max(id) from User where id < 10)"));

Now hibernate will generate a query against the database (I am using MySQL) as:

select this_.id as id1_3_0_, this_.name as name2_3_0_ from User this_ where id=(Select max(id) from User where id < 10)

So what will happen if you give any generic SQL statement like “Select * from User” as:

criteria.add(Restrictions.sqlRestriction("Select * from User"));

You will most probably get an exception similar to what I got with MySQL:

org.hibernate.exception.SQLGrammarException: could not execute query

…

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Select * from User' at line 1

…

This is because hibernate will generate the query in this case as:

select this_.id as id1_3_0_, this_.name as name2_3_0_ from User this_ where Select * from User

Note that whatever SQL you give comes after the where.

Projections

Projections are used to implement selections and aggregations with Criteria API. You use the method Criteria’s setProjection(Projection) method. Similar to Restrictions, you can use Projections class methods to create Projection. For instance you can write a projection to select only the id as:

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();

    Criteria criteria = session.createCriteria(User.class);

    criteria.add(Restrictions.like("name", "user_1%"));

    criteria.setProjection(Projections.property("id"));

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

    for (int i : ids) {

      System.out.println(i);

    }

    session.getTransaction().commit();

Similarly, you can use projection for aggregate function as:

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();

    Criteria criteria = session.createCriteria(User.class);

    criteria.add(Restrictions.like("name", "user_1%"));

    criteria.setProjection(Projections.max("id"));

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

    for (int i : ids) {

      System.out.println(i);

    }

    session.getTransaction().commit();

Note that even for aggregate functions, you will still get a list like before. Projections class has similar methods for almost all aggregate functions.

Through the examples here, we have seen the usages for most commonly used features along with example code. You now have the basic setup and enough code examples to get started. Try out more examples and ask any doubts you might come across.  I usually use these notes during my class room or online trainings. Also, I avoid lot of extra explanations like test books is because, I would like to treat these notes as a quick reference which you can come back and check when you need something like a syntax. And during that time, many people would like to just get the answers than going through all the explanations.  Still, if you find short of explanation for any area, please do let me know and I will try to add more details.