Hibernate Criteria examples

Hibernate Criteria API is a more object oriented and elegant alternative to Hibernate Query Language (HQL). It’s always a good solution to an application which has many optional search criteria.

Example in HQL and Criteria

Here’s a case study to retrieve a list of StockDailyRecord, with optional search criteria – start date, end date and volume, order by date.

1. HQL example

In HQL, you need to compare whether this is the first criteria to append the ‘where’ syntax, and format the date to a suitable format. It’s work, but the long codes are ugly, cumbersome and error-prone string concatenation may cause security concern like SQL injection.


public static List getStockDailtRecord(Date startDate,Date endDate,
   Long volume,Session session){
		
   SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
   boolean isFirst = true; 
		
   StringBuilder query = new StringBuilder("from StockDailyRecord ");
		
   if(startDate!=null){
	if(isFirst){
		query.append(" where date >= '" + sdf.format(startDate) + "'");
	}else{
		query.append(" and date >= '" + sdf.format(startDate) + "'");
	}
	isFirst = false;
   }
		
   if(endDate!=null){
	if(isFirst){
		query.append(" where date <= '" + sdf.format(endDate) + "'");
	}else{
		query.append(" and date <= '" + sdf.format(endDate) + "'");
	}
	isFirst = false;
   }

   if(volume!=null){
	if(isFirst){
		query.append(" where volume >= " + volume);
	}else{
		query.append(" and volume >= " + volume);
	}
	isFirst = false;
   }
		
   query.append(" order by date");
   Query result = session.createQuery(query.toString());
	
   return result.list();
}

2. Criteria example

In Criteria, you do not need to compare whether this is the first criteria to append the ‘where’ syntax, nor format the date. The line of code is reduce and everything is handled in a more elegant and object oriented way.


   public static List getStockDailyRecordCriteria(Date startDate,Date endDate,
        Long volume,Session session){
		
	Criteria criteria = session.createCriteria(StockDailyRecord.class);
	if(startDate!=null){
		criteria.add(Expression.ge("date",startDate));
	}
	if(endDate!=null){
		criteria.add(Expression.le("date",endDate));
	}
	if(volume!=null){
		criteria.add(Expression.ge("volume",volume));
	}
	criteria.addOrder(Order.asc("date"));
		
	return criteria.list();
  }

Criteria API

Let go through some popular Criteria API functions.

1. Criteria basic query

Create a criteria object and retrieve all the ‘StockDailyRecord’ records from database.


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

2. Criteria ordering query

The result is sort by ‘date’ in ascending order.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
    .addOrder( Order.asc("date") );

The result is sort by ‘date’ in descending order.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
    .addOrder( Order.desc("date") );

3. Criteria restrictions query

The Restrictions class provide many methods to do the comparison operation.

Restrictions.eq

Make sure the valume is equal to 10000.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
    .add(Restrictions.eq("volume", 10000));
Restrictions.lt, le, gt, ge

Make sure the volume is less than 10000.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
   .add(Restrictions.lt("volume", 10000));

Make sure the volume is less than or equal to 10000.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
   .add(Restrictions.le("volume", 10000));

Make sure the volume is great than 10000.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
   .add(Restrictions.gt("volume", 10000));

Make sure the volume is great than or equal to 10000.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
   .add(Restrictions.ge("volume", 10000));
Restrictions.like

Make sure the stock name is start with ‘MKYONG’ and follow by any characters.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
   .add(Restrictions.like("stockName", "MKYONG%"));
Restrictions.between

Make sure the date is between start date and end date.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
   .add(Restrictions.between("date", startDate, endDate));
Restrictions.isNull, isNotNull

Make sure the volume is null.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
   .add(Restrictions.isNull("volume"));

Make sure the volume is not null.


Criteria criteria = session.createCriteria(StockDailyRecord.class)
   .add(Restrictions.isNotNull("volume"));

Many other Restrictions functions can find here.
https://www.hibernate.org/hib_docs/v3/api/org/hibernate/criterion/Restrictions.html

3. Criteria paging the result

Criteria provide few functions to make pagination extremely easy. Starting from the 20th record, and retrieve the next 10 records from database.


Criteria criteria = session.createCriteria(StockDailyRecord.class);
criteria.setMaxResults(10);
criteria.setFirstResult(20);

Why not Criteria !?

The Criteria API do bring some disadvantages.

1. Performance issue

You have no way to control the SQL query generated by Hibernate, if the generated query is slow, you are very hard to tune the query, and your database administrator may not like it.

1. Maintainece issue

All the SQL queries are scattered through the Java code, when a query went wrong, you may spend time to find the problem query in your application. On the others hand, named queries stored in the Hibernate mapping files are much more easier to maintain.

Conclusion

Nothing is perfect, do consider your project needs and use it wisely.

author image

mkyong

Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities. Read all published posts by

Comments

avatar
newest oldest most voted
Aecho
Guest
Aecho

Can you add `Restrictions.in` example ?

Debiprasad
Guest
Debiprasad

Yes

roshan
Guest
roshan

class Student{

String name;

int age;

String address;

List phoneNumbers;

}

Hello yong,

Here i want name & phoneNumbers(List) of all students as a result

How i’ll achieve this

Please Help Me

trackback
Hibernate Tutorials | Tutorials

[…] Hibernate Criteria examples Criteria examples – basic query, ordering query, restrictions query and paging the result. […]

Stan
Guest
Stan

Hello Yong. I’m new in Hibernate, but that code in the first example could be writting in more convenient way, for example:


StringBuilder query = new StringBuilder(“from StockDailyRecord”);

if (startDate != null)
query.append(” %s date >= ‘” + sdf.format(startDate) + “‘”);
if (endDate != null)
query.append(” %s date = ” + volume);

query.append(” order by date”);
Query result = session.createQuery(
String.format(query.toString(), “where”, “and”, “and”));

How do you think?

Stan
Guest
Stan

Yeah, you are right. I thought about this, but decided that its still convenient. Because you don’t have to dublicate all the sql strings, that like you said “ugly, cumbersome and error-prone”. It is just another way 🙂

PS: Thank you for this site which is great, I learned a lot of new things while reading it.

pierangelo
Guest
pierangelo

really thx m8…..better than jboss reference!
Cheers

vp
Guest
vp

Good ones.
thanks for all the tutorials.

Arby
Guest
Arby

Thank you for telling it like it is. There is a point at which we have to consider what is right for the project and not right by the tool. If we insist on using EVERYTHING a tool provides just because we HAVE to use the TOOL – a lot of “architects” are guilty of this – we end up with an inefficient solution. This is not just problem with Hibernate. This is problem with any ORM tool which we rely on to generate underlying SQL. As is explained in the first couple of chapters of the Hibernate book, just… Read more »

sikinder
Guest
sikinder

Very good article. Thanks for such nice tutorial.
Please keep it up Yong.

–Sikinder

Cristian Daniel Ortiz Cuellar
Guest
Cristian Daniel Ortiz Cuellar

hi yonk. how can i replace this code to criteria or which is the better way to do a select count using hibernate. thanks a lot. this is my code.. StringBuilder Hql = new StringBuilder("Select Count(D.Producto.Id)"); Hql.append(" from Detalle D "); Hql.append("inner join D.Factura.Cliente C"); Hql.append(" where (D.Factura.Fecha >=:startDate And D.Factura.Fecha <=:endDate)"); Hql.append(" And (D.Producto.IdLinea=:idLinea)"); if(!idSucursal.equals(-1)) Hql.append("And (C.Sucursal.Id=:idSucursal)"); Hql.append(" Group by D.Producto.Id"); i am doing this query but take huge time to finish. thanks a lot.

giri
Guest
giri

if i am using hibernate with spring .. where should i set values for hibernate criteria in the action class or in the dao class?

User
Guest
User

dao

giri
Guest
giri
Criteria criteria = session.createCriteria(Customer.class)
   .add(Restrictions.like("countryName", "India"));

for example Customer is a class which has reference to Country Class and Country Class has a property CountryName .Now if i want to search customer based on country name how to do? is it right to provide

.add(Restrictions.like("country.countryName", "India"));

assuming country is the reference name for Country class.

geeth
Guest
geeth

good article.

I am slightly worried about switching from Ibatis to Hibernate though.

sumit
Guest
sumit

Dear All,

What are your suggestion on using the criteria API, instead of simply hard-coding the SQL-select query in the session.createSQLQuery(myQuery) ???

murali
Guest
murali

Nice Tutorial on Criteria usage.

Nithi
Guest
Nithi

Hi I need a help in contructing a criteria Query..
When i need to compare two fields in a class say,
“where startdate== enddate”; where both startdate and end date are two fields in the same class. Kindly help me in resolving it…

shubham
Guest
shubham

u can do this by using hql..in place of criteria such as..
Query query=session.createQuery(“from Employee e where e.startdate==e.enddate”);
List employees=query.list();
…i think this will hemp you..

thanks.

Milan
Guest
Milan

Try this: criteria.add(Restrictions.eqProperty(“startdate”, “enddate”));

trackback
Hibernate Criteria « Life in USA

[…] FROM HERE Like this:LikeBe the first to like this. […]

trackback
Hibernate Criteria and Example queries | Life in USA

[…] From mkyong    And  Hibernate Doc […]

moradi
Guest
moradi

thank you so much,it was helpful

abhijit
Guest
abhijit

Can you please add hibernate pagination + struts/spring example?

Vignesh
Guest
Vignesh

I have a doubt,
my following criteria isn’t working…

Criteria criteria = session.createCriteria(AgencyDetail.class,”AgencyDetail”)
.add(Restrictions.eq(“AgencyDetail.agencyId”, agencyId)).add(Restrictions.eq(“AgencyDetail.active”, ‘Y’));
agencyDetail = (AgencyDetail)criteria.uniqueResult();
agencyId = agencyDetail.getAgencyId();

I can get the criteria object.
But the error seems in the criteria.uniqueResult();

satya
Guest
satya

edi oka doubt ah ra

Saood
Guest
Saood

What exactly is your issue? What error is it throwing? Try to replace ‘Y’ by “Y”

Try this as well:

Criteria criteria = session.createCriteria(AgencyDetail.class)
.add(Restrictions.eq(“this.agencyId”, agencyId)).add(Restrictions.eq(“this.active”, ‘Y’));
agencyDetail = (AgencyDetail)criteria.uniqueResult();
agencyId = agencyDetail.getAgencyId();

fashion
Guest
fashion

Spot on with this write-up, I really think this website wants way more consideration. I?l in all probability be once more to learn way more, thanks for that info.

maveric
Guest
maveric
Adtc
Guest
Adtc

“In HQL, you need to compare whether this is the first criteria to append the ‘where’ syntax, and format the date to a suitable format.” If you think this is true, you’re not thinking hard enough. One thing I’ve gotten really good at is how I can refactor code to avoid unnecessary repetitions (having to deal with a whole load of unnecessary repetitions in workplace source code just st. There are several ways you can come up with to tackle the above problem. 1) Use “Where 1=1 ” (yea, may have performance issue) 2) Use “Where True ” (maybe better… Read more »

Adtc
Guest
Adtc

Correction: (having to deal with a whole load of unnecessary repetitions in workplace source code just strengthens me to get better at it, if it doesn’t stress me out too much. it’s an eyesore!)

bird
Guest
bird

thanks for this awesome tutorial!

Rodrigo Magalhães
Guest
Rodrigo Magalhães

PERFECT, helped me a lot!

Shahul
Guest
Shahul

That’s Nice tutorial. Thank you so much. I have question. In my table the date is stored as mm/dd/yyyy. here is some code snippet

SimpleDateFormat sdf = new SimpleDateFormat(“MM/dd/yyyy”);

criteria.add(Restrictions.between(“registered”, sdf.parse(searchCriteriaDto.getFromDate()),sdf.parse(searchCriteriaDto.getToDate())));

Where searchCriteriaDto.getFromDate() and searchCriteriaDto.getToDate() returns string version of date like (04/10/2014);

I know I have records matching this criteria. But it’s not picking up. Not sure what the issue is. When i Print

sdf.parse(searchCriteriaDto.getFromDate()

I see date like this
Thu Apr 10 00:00:00 CDT 2014

Not sure if it is wrong. Could you help?

David
Guest
David

Hi, i need the date of database like string.
select TO_CHAR(sysdate, ‘yyyy/mm/dd’); from dual:–oracle
I am trying with this:
select to_char(current_timestamp(), ‘yyyy/mm/dd’ ) from Variable
But it do not work.

I don know like do it with hql?
I need to do the conversion to string with hql( no whit Java).

somebody can help me?

bhanu prakash Shukla
Guest
bhanu prakash Shukla

how to write the following query in criteria..
SELECT ua.id AS activity_id, ua.created_date, f.filter_name, uaf.filters AS filter_id
FROM user_activity ua
JOIN user_activity_filters uaf
ON ua.id = uaf.user_activity
JOIN filters f
ON uaf.filters = f.id
WHERE ua.id IN (
SELECT * FROM (
SELECT DISTINCT ua.id as activity_id
FROM user_activity ua
JOIN user_activity_filters uaf
ON ua.id = uaf.user_activity
WHERE ua.user_id = 2
ORDER BY ua.created_date DESC
LIMIT 3
)
AS f)
ORDER BY ua.created_date DESC