SQL, HQL and Criteria API of hibernate

Question

In which cases we choose SQL, Hibernate Query Language and Criteria API of hibernate?

Answer 1

• Criteria Queries are suitable for creating dynamic queries. For example it is much easier to add some ordering dynamically or leave some parts (e.g. restrictions) out depending on the value of a parameter.

• Criteria queries, are defined by instantiation of Java objects that represent query elements not as Strings. The advantage of this is that errors can be detected at compile time. Criteria queries are type-safe

• HQL queries are defined as strings, similarly to SQL. Use HQL for static and complex queries, because it's much easier to understand/read.

• Also HQL doesn't depends on the table of the database. Instead of table name, we can use Entity name in HQL. So it can be used as a database independent query language.

  @Entity(name="MyEntityName")
  @Table(name="MyEntityTableName")
  class MyEntity {
  
  //A table with name MyEntityTableName is created and the entity name is MyEntityName.
  //Your JPQL/HQL query would be :
  
  select * from MyEntityName
  

• Both of these allows you to decouple your application from SQL. SQL syntax can vary depending on the database vendor(Oracle, MySQL .etc). So, if you want to change the underlying database, there will not be any impact(or minimum impact)

• There can be vendor specific, optimized SQL features that you could take advantage of but they are not ANSI compliant. ORM tools such as Hibernate takes the responsibility of dealing with them while providing the optimized solution.

• SQL queries with JDBC are a bit faster than Criteria queries and HQL. But this performance scarification is well worth for the advantages gained.

Hope this helps.

Answer 2

HQL - this is fit for most queries and easy to read and write.

Criteria - If you don't want to write query then you can use Criteria API. ex:-

SELECT * FROM employee WHERE emp_id =1

Criteria query will be

 Criteria criteria = session.createCriteria(Employee.class)
  .add(Restrictions.eq("id", new Integer(1));

Get all employees

  List customers = criteria.list();

Other than that API provides pagination, max row id, child object selection etc..

SQL - Other two API not fit your work then go with native SQL.