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.

Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.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

  • Pingback: pay per day loans plan()

  • Pingback: YouTube likes kopen()

  • Pingback: laan penge nu()

  • Pingback: xnxx()

  • Pingback: car parking()

  • Pingback: penge lan nu()

  • Pingback: car parking()

  • Pingback: Gloryhole secrets interview alejandra see her enjoy to blow anonymous pipe()

  • Pingback: Blue Coaster33()

  • 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

    “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
  • http://www.fthia.tv/index.php/forum/6-sports/39028-m6qb-28-xna-hx#39028 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

    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

      edi oka doubt ah ra

    • 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

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

  • moradi

    thank you so much,it was helpful

  • Pingback: Hibernate Criteria and Example queries | Life in USA()

  • Pingback: Hibernate Criteria « Life in USA()

  • 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

      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

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

  • murali

    Nice Tutorial on Criteria usage.

  • http://www.mpcz.co.in 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

    good article.

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

  • giri
    Criteria criteria = session.createCriteria(Customer.class)
       .add(Restrictions.like(&quot;countryName&quot;, &quot;India&quot;));

    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(&quot;country.countryName&quot;, &quot;India&quot;));

    assuming country is the reference name for Country class.

    • http://www.mkyong.com mkyong

      Thanks for your tips

  • 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

      dao

  • 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(&quot;Select Count(D.Producto.Id)&quot;);
               Hql.append(&quot; from Detalle D &quot;);
               Hql.append(&quot;inner join  D.Factura.Cliente C&quot;);
               Hql.append(&quot; where (D.Factura.Fecha &gt;=:startDate And D.Factura.Fecha &lt;=:endDate)&quot;);
               Hql.append(&quot; And (D.Producto.IdLinea=:idLinea)&quot;);
               if(!idSucursal.equals(-1)) Hql.append(&quot;And (C.Sucursal.Id=:idSucursal)&quot;);           
               Hql.append(&quot; Group by  D.Producto.Id&quot;);

    i am doing this query but take huge time to finish.
    thanks a lot.

  • sikinder

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

    –Sikinder

  • 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 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.

  • vp

    Good ones.
    thanks for all the tutorials.

  • pierangelo

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

  • 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.

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

    • http://www.mkyong.com mkyong

      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.

      • cscsaba242

        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

      • Cesar G M

        What about:

        StringBuilder query = new StringBuilder(“from StockDailyRecord where 1 = 1″);

        This eliminates the need to check the initial condition

        • Marcin

          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.

        • Marcin

          i think “where true” is better in terms of performance :)

  • Pingback: Hibernate Tutorials | Tutorials()