Hibernate Criteria Tutorial with Examples (Joins, Sub Query, Sorting)

By Amit Swain, 09 March,2017  
778
hibernate

As we know hibernate criteria is a very important aspect of Hibernate framework as it simplifies the way to fetch tabular data from DB. In this article, we will be discussing about different examples on hibernate criteria. Starting from what is criteria, we will look into how to construct criterias to perform common read operations that we do by using query langages such as joins, sorting, sub queries etc.

What is Criteria

To fetch data from relational DB, we use different query Languages such as HQL or SQL but the common thing here is we use certain query language which is again a new set of language that relational DB understands. Criteria deals with this restrictions by providing a simplified API for retrieving entities by composing Criterion objects.

It is very ideal and elegant for creating dyamic queries and simple to add ordering, restrictions and paginations while fetching data from hibernate supported databases.

Criteria Prefernces over HQL

Though it completly depends upon the requirements and projects type whether you should use criteria over HQL or SQL, following are some points that support hibernate criteria.

1. Criteria is safe from vulnerable to SQL injection as queries are fixed or parameterized.

2. HQL is ideal for static queries.

3. HQL does not support pagination but it can be achieved by Criteria.

4. Criteria helps us to build queries in a cleaner way and most of our errors are solved during compile time.

However Crtieria also lags in some use cases. For example HQL can perform both select and non-select operations. Criteria can only select data, you can not perform non-select operations using criteria queries. In case of performance Criteria is slower than HQL.

Hibernate Criteria Examples

Lets take an example using two entities Book.java and Publisher.java and fetch desired records using criteria.

Book.java
@Entity @Table(name = "BOOK") public class Book { @Id @GeneratedValue (strategy = GenerationType.AUTO) @Column (name = "BOOK_ID") private long id; @Column(name = "TITLE") private String title; @Column(name = "BOOK_NAME") private String name; @Column(name = "BOOK_DESC") private String description; @Column(name = "ISBN") private String isbn; @Column(name = "PRICE") private long price; @Column(name = "PUBLISHED_DATE") private Date publishDate; @ManyToOne @JoinColumn(name = "PUBLISHER") private Publisher publisher; //getters and setters goes here }
Publisher.java
@Entity @Table(name = "PUBLISHERS") public class Publisher { @Id @GeneratedValue (strategy = GenerationType.AUTO) @Column (name = "PUBLISHER_ID") private long id; @Column (name = "PUBLISHER_NAME") private String name; @Column (name = "PUBLISHER_DESC") private String description; //getters and setters goes here }

A basic criteria query looks like this

Criteria criteria = session.createCriteria(Book.class) List books = criteria.list();

It fetches all the records from Book table and the equivalent sql query is follow

SELECT book.* ,publisher.* FROM BOOK book LEFT OUTER JOIN PUBLISHERS publisher ON book.PUBLISHER = publisher.PUBLISHER_ID

In the above example * is used to represent all the column names.As we are using JPA specifcation EAGER Type is by default provided for ManyToOne relationship. so we are getting a LEFT OUTER JOIN with Publisher entity.

Using Restrictions with Hibernate Criteria

Following is an example to apply restrictions in criteria

Criteria criteria = session.createCriteria(Book.class); Criterion nameCriterion = Restrictions.eq("name", "Hibernate"); criteria.add(nameCriterion); List books = criteria.list();

This is equivalent to the following SQL query:

SELECT book.* ,publisher.* FROM BOOK book LEFT OUTER JOIN PUBLISHERS publisher ON book.PUBLISHER = publisher.PUBLISHER_ID WHERE book.BOOK_NAME = 'Hibernate'

Now lets add more filter using AND ,OR using restrictions.

Criteria criteria = session.createCriteria(Book.class).add(Restrictions.or( Restrictions.like("name", "%Hibernate%"), Restrictions.like("name", "%Java%")));

This is equivalent to the following SQL:

SELECT book.*,publisher.* FROM BOOK book LEFT OUTER JOIN PUBLISHERS publisher ON book.PUBLISHER = publisher.PUBLISHER_ID WHERE (book.BOOK_NAME LIKE '%Hibernate%' OR book.BOOK_NAME LIKE '%Java%')

Similiarly we can use Restrictions.gt(..) ,Restrictions.lt(..), Restrictions.isEmpty(..) ,Restrictions.isNotEmpty(..),Restrictions.isNull(..) ,Restrictions.between(..)

SQL functions using Restrictions

We can add the sql functions like min(), max(), avg() etc using criteria. Here is the criteria to find a hibernate book of least price

session.createCriteria(Book.class).add(Restrictions.like("name", "%Hibernate%")). setProjection(Projections.min("price")).uniqueResult();

The generated sql query will be:

SELECT min(book.PRICE) AS minPrice FROM BOOK book WHERE book.BOOK_NAME like '%Hibernate%'

Ordering Results using Criteria

You can order the results using org.hibernate.criterion.Order.

Criteria criteria = session.createCriteria(Book.class).add( Restrictions.like("name", "%Hibernate%")) .addOrder(Order.asc("name") .addOrder(Order.desc("publishDate")) List books = criteria.list();

The equivalent sql query will look as :

SELECT book.*, publisher.* FROM BOOK book LEFT OUTER JOIN PUBLISHERS publisher ON book.PUBLISHER = publisher.PUBLISHER_ID WHERE book.BOOK_NAME LIKE '%Hibernate%' ORDER BY book.BOOK_NAME ASC,book.PUBLISHED_DATE DESC

Fetching association using Criteria

By navigating associations using createCriteria() you can specify constraints upon related entities.lets write criteria to fetch books with name containing 'Hibernate' of publishers with name containing 'Apress'

List books = session.createCriteria(Book.class). add(Restrictions.like("name", "%Hibernate%")) .createCriteria("publisher").add(Restrictions.like("name", "%Apress%")) .list();

The second criteria used above creates a new criteria instance representing publisher instance.The generated sql query is

SELECT book.*,publisher.* FROM BOOK book INNER JOIN PUBLISHERS publisher ON book.PUBLISHER = publisher.PUBLISHER_ID WHERE book.BOOK_NAME LIKE '%Hibernate%' AND publisher.PUBLISHER_NAME LIKE '%Apress%'

Fetching dynamic association using Criteria

To demonstrate this example instead of creating an separate entity lets change the fetch type for publisher to LAZY in Book entity and acheive the eager fetching via criteria.

@ManyToOne(fetch= FetchType.LAZY) @JoinColumn(name = "PUBLISHER") private Publisher publisher;

Lets fetch it using criteria.

List books = session.createCriteria(Book.class). add(Restrictions.like("name", "%Hibernate%")) .setFetchMode("publisher", FetchMode.EAGER).list();

This fetches book along with corresponding publisher if present.The equivalent sql output is

SELECT book.*, publisher.* FROM BOOK book LEFT OUTER JOIN PUBLISHERS publisher ON book.PUBLISHER = publisher.PUBLISHER_ID WHERE book.BOOK_NAME LIKE '%Hibernate%'

Writing SubQuery using Criteria

Lets retain the same LAZY relationship as above to avoid the LEFT OUTER JOIN with Publisher entity and compose a subquery.

We are going to use DetachedCriteria for sub query.DetachedCriteria class allows us to create a query outside the scope of a session and then execute it using an arbitrary Session.

DetachedCriteria subQuery = DetachedCriteria.forClass(Publisher.class, "publisher"). add(Restrictions.eq("publisher.id", 1)). setProjection(Projections.property("publisher.id")); Criteria books = session.createCriteria(Book.class, "book").add(Property.forName("book.publisher").notIn(subQuery));

The generated SQL query is:

SELECT book.* FROM BOOK book WHERE book.PUBLISHER NOT IN (SELECT publisher.PUBLISHER_ID AS publisher_id FROM PUBLISHERS publisher WHERE publisher.PUBLISHER_ID = 1)

Conclusion

I hope this article served you whatever you were looking for. If you have anything that you want to add or share then please share it below in the comment section.

References

Jboss References

Hibernate Wiki

Suggest more topics in suggestion section or write your own article and share with your colleagues.

Is this page helpful to you? Please give us your feedback below. We would love to hear your thoughts on these articles, it will help us improve further our learning process.

Further Reading: