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.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Aecho
Guest
Aecho

Can you add `Restrictions.in` example ?

Debiprasad
Guest
Debiprasad

Yes

Jayasree
Guest
Jayasree

I am trying to do a search (input will come through JSP page and need to match with cust name or id, or contract id) by using hibenate org.apache.lucene.search and Querybuilder, but not getting the result. Kindly guide me how to do tat??

yash pgdost
Guest
yash pgdost

Very useful!! Thanks.. Keep Posting such nice articles!

Asvin
Guest
Asvin

Hi,

Thanks for the great tutorial. May I know how to find the number of days between two dates?

evalls
Guest
evalls

Hello everybody,

please how can i get data from a Manytomany relationship (Annotations) with criteria. For instance from the table stock_category of the example of
http://www.mkyong.com/hibernate/hibernate-many-to-many-relationship-example-annotation/

when the Stockcategory doesnt exist as java class. i can’t do :
criteria = session.createCriteria(stock_category.class);

thk you for your help………

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

Ascalonian
Guest
Ascalonian

To reduce that huge HQL example, you could just do “StringBuilder query = new StringBuilder(“from StockDailyRecord WHERE 1=1 “);” and then you can do the comparisons the same as the Criteria example.

Thilina
Guest
Thilina

Thanks a lot Mykong !!!

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

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?

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?

Rodrigo Magalhães
Guest
Rodrigo Magalhães

PERFECT, helped me a lot!

bird
Guest
bird

thanks for this awesome tutorial!

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!)

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 »
maveric
Guest
maveric
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.

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

abhijit
Guest
abhijit

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

moradi
Guest
moradi

thank you so much,it was helpful

trackback
Hibernate Criteria and Example queries | Life in USA

[…] From mkyong    And  Hibernate Doc […]

trackback
Hibernate Criteria « Life in USA

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

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

murali
Guest
murali

Nice Tutorial on Criteria usage.

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) ???

geeth
Guest
geeth

good article.

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

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.

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

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.
sikinder
Guest
sikinder

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

–Sikinder