Main Tutorials

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 Author

author image
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.

Comments

Subscribe
Notify of
46 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Aecho
8 years ago

Can you add `Restrictions.in` example ?

Debiprasad
5 years ago
Reply to  Aecho

Yes

roshan
9 years ago

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

Mania RONCHAIL
4 years ago

I need to verify if the value of a field is even , I use SqlLite as Database, I tryed with
var sqlf= Projections.SqlFunction(“mod”, NHibernateUtil.Int32, Projections.ProjectionList().Add(Projections.Property(nameof(GsProcedure.Number))).Add(Projections.Constant(“2”)));
criteriaBuilder.Add(Restrictions.Eq(sqlf, 0));
but this create a request like this: “WHERE mod(this_.PRO_NO AS y0_, 2) = 0”
and it is not executable by sqlLite which accepts only “where PRO_NO%2=0”
Do you know what should I do?

Jayasree
8 years ago

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
8 years ago

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

Asvin
8 years ago

Hi,

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

evalls
8 years ago

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
https://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………

Ascalonian
9 years ago

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
9 years ago

Thanks a lot Mykong !!!

bhanu prakash Shukla
9 years ago

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
9 years ago

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
9 years ago

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
9 years ago

PERFECT, helped me a lot!

bird
10 years ago

thanks for this awesome tutorial!

Adtc
10 years ago

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
10 years ago

“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 in performance)

3) If there’s a mandatory condition that will always appear in the result SQL, append that *first*, so subsequent optional can simply be “And …”

4) Use a private helper method to determine whether to append Where or And:

if(startDate!=null){
query.append(where(isFirst)+” date >= ‘” + sdf.format(startDate) + “‘”);
isFirst=false;
}

private String where(boolean isFirst) {
if (isFirst) return ” where”; else return ” and”;
}

maveric
10 years ago
fashion
10 years ago

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
11 years ago

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

Saood
10 years ago
Reply to  Vignesh

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

satya
10 years ago
Reply to  Vignesh

edi oka doubt ah ra

abhijit
11 years ago

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

moradi
11 years ago

thank you so much,it was helpful

Nithi
11 years ago

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…

Milan
10 years ago
Reply to  Nithi

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

shubham
11 years ago
Reply to  Nithi

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.

murali
11 years ago

Nice Tutorial on Criteria usage.

sumit
11 years ago

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
11 years ago

good article.

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

giri
11 years ago
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
11 years ago

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
11 years ago
Reply to  giri

dao

Cristian Daniel Ortiz Cuellar
12 years ago

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
12 years ago

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

–Sikinder

Arby
12 years ago

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 because you don’t want to bother to understand RDBMS or SQL does not mean you can use Hibernate. Hibernate is not a “get out of jail free card” for incompetence.

For any complex query, IMHO Hibernate should NOT be used. It is just not a question of code scattered all over the place. Even if one uses named query, the SQL generated by Hibernate will be sub-optimal when there are multiple search criteria and pagination to consider. One needs to look at the underlying SQL extensions of the database, i.e. T-SQL or PL-SQL or whatever and learn to write SQL queries.