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.
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?
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??
Very useful!! Thanks.. Keep Posting such nice articles!
Can you add `Restrictions.in` example ?
Yes
Hi,
Thanks for the great tutorial. May I know how to find the number of days between two dates?
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………
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
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.
Thanks a lot Mykong !!!
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
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?
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?
PERFECT, helped me a lot!
thanks for this awesome tutorial!
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!)
“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”;
}
This article may be of good help
http://techdive.in/spring/spring-hibernate-integration
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.
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();
edi oka doubt ah ra
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();
Can you please add hibernate pagination + struts/spring example?
thank you so much,it was helpful
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…
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.
Try this: criteria.add(Restrictions.eqProperty(“startdate”, “enddate”));
Nice Tutorial on Criteria usage.
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) ???
good article.
I am slightly worried about switching from Ibatis to Hibernate though.
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.
Thanks for your tips
if i am using hibernate with spring .. where should i set values for hibernate criteria in the action class or in the dao class?
dao
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.
Very good article. Thanks for such nice tutorial.
Please keep it up Yong.
–Sikinder
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.
Good ones.
thanks for all the tutorials.
really thx m8…..better than jboss reference!
Cheers
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.
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?
yo great! your code is more convenient. Just little concern, if you have a query which accept 10+ conditions, the string format may cause a maintainability issue, cause you need count the “and” string one by one… After all, this will always happened in legacy system.
Hello mkyong,
I would like to build up a criteria with following conditions
– some variables must be checked whether it is null or not.
– query must be in criteria
the outcome must be look like this:
(fieldname1 == var1)
and (if var2 != null (fieldname2 == var2)
and if var3 != null (fieldname3 == var3)
and if var4 != null (fieldname4 == var4) )
or (fieldname5 == null)
I don’t know the way how can I add the middle part with checking null.
Thanks ahead!
Chaba
What about:
StringBuilder query = new StringBuilder(“from StockDailyRecord where 1 = 1”);
This eliminates the need to check the initial condition
this will cause your query to check in every row if 1 is equal to 1. in bigger databases it will dramatically increase your execute time.
i think “where true” is better in terms of performance 🙂