Hibernate : Named Query and Criteria

Named Queries

Often, while writing HQL, the queries are scattered all over the Java code. Named queries in hibernate is a technique to group the HQL statements in single location, and lately refer them by some name whenever need to use them. Advantages : –

  • Their syntax is checked when the session factory is created, making the application fail fast in case of an error.
  • They can be accessed and used from several places which increase re-usability.

I created duplicate table in DB – ’employee_details’ with ’emp_id’ and ‘name’ as columns. The table has some dummy data. Lets write it in our Employee entity.


package com.hibernate.namedQueries;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@NamedQueries({
 @NamedQuery(
 name = "findEmployeeById",
 query = "from Employee where id = :passId"
 )
})
@Entity
@Table(name="employee_details")
public class Employee {

 @Id
 @Column(name = "emp_id")
 @GeneratedValue(strategy = GenerationType.AUTO)
 private int id;
 private String name;

 public int getId() {
 return id;
 }

 public void setId(int id) {
 this.id = id;
 }

 public String getName() {
 return name;
 }

 public void setName(String name) {
 this.name = name;
 }
}

Named query definition has two important attributes:

  • name: The name of name query by which it will be located using hibernate session.
  • query: Here you give the HQL statement to get executed in database.

Notice that we have used class name attribute name for selection, while the actual table name and column name is different in DB. HibernateNamedQueryTest.java :


package com.hibernate.namedQueries;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateNamedQueryTest {

public static void main(String[] args) {

 SessionFactory sessionFactory = new Configuration().configure(
 "/com/hibernate/namedQueries/hibernate.cfg.xml")
 .buildSessionFactory();
 Session session = sessionFactory.openSession();
 session.beginTransaction();

 Query query1=session.getNamedQuery("findEmployeeById");
 query1.setInteger("passId", 2);
 List<Employee> list1=query1.list();
 System.out.println("Name=>"+list1.get(0).getName());

 session.getTransaction().commit();
 session.close();
 }

}

Output:


Hibernate: select employee0_.id as id1_0_, employee0_.name as name2_0_ from Employee employee0_ where employee0_.id=?
Name=>Employee Name2

We can also write plain SQl queries in a similar manner as follows:


@NamedNativeQueries({
 @NamedNativeQuery(
 name = "findEmployeeByIdNativeSQL",
 query = "select * from Employee_details where emp_id = :passId",
 resultClass = Employee.class
 )
})

In native SQL, you have to declare the ‘resultClass‘ to let Hibernate know what is the return type, failed to do it will caused the exception. Also notice that we are using actual table and column name in DB here. Calling similarry like previous we get the output:


Hibernate: select * from Employee_details where emp_id = ?
Name=>Employee Name2

One of the advantages of native is that we can call a stored procedure here. Suppose you have a stored procedure like following.


PROCEDURE `GET_EMPLOYEE_BY_ID`(
 in_id integer
)
BEGIN

Select * from hibernatedb.employee_details e where e.emp_id=in_id;

END

Writing this in out named natice query


@NamedNativeQuery(
 name = "findEmployeeByIdStoreProcedure",
 query = "CALL GET_EMPLOYEE_BY_ID(:passId)",
 resultClass = Employee.class
 )

Calling from our test class similarly:


Query query3=session.getNamedQuery("findEmployeeByIdStoreProcedure")
 .setInteger("passId", 2);
 List<Employee> list3=query3.list();
 System.out.println("Name=>"+list3.get(0).getName());

Output:


Hibernate: CALL GET_EMPLOYEE_BY_ID(?)
Name=>Employee Name2

Well, we can also call stored prcedure from createSQLQuery() method as follows:

Query query4 = session.createSQLQuery(
"CALL GET_EMPLOYEE_BY_ID(:passId)")
.addEntity(Employee.class)
.setParameter("passId", "5");
List<Employee> list4=query4.list();
System.out.println("Name=>"+list4.get(0).getName());

Output:


Hibernate: CALL GET_EMPLOYEE_BY_ID(?)
Name=>Employee Name5

Criteria

Hibernate Criteria API is a more object oriented and elegant alternative to Hibernate Query Language (HQL). The Hibernate Session interface provides createCriteria() method which can be used to create aCriteria object that returns instances of the persistence object’s class when your application executes a criteria query.


Criteria cr1 = session.createCriteria(Employee.class);
 List<Employee> list1 = cr1.list();
 for (Employee employee : list1) {
 System.out.println(employee.getId()+" " +employee.getName());
 }

Output will be all th employee details. You can use add() method available for Criteria object to add restriction for a criteria query.


Criteria cr2 = session.createCriteria(Employee.class)
 .add(Restrictions.lt("id", 8))
 .add(Restrictions.gt("id", 4));
 List<Employee> list2 = cr2.list();
 for (Employee employee : list2) {
 System.out.println(employee.getId()+" " +employee.getName());
 }

By default it considersAND’ among Restriction. Output:


5 Employee Name5
6 Employee Name6
7 Employee Name7

For ‘OR’ :


Criteria cr3 = session.createCriteria(Employee.class)
 .add(Restrictions.or(Restrictions.gt("id", 8),Restrictions.lt("id", 4)));
 List<Employee> list3 = cr3.list();

Output:


1 Employee Name1
2 Employee Name2
3 Employee Name3
9 Employee Name9
10 Employee Name10

If there are many Restrciton , for a cleaner code, you can create seperate Criteria objects using just restriction and then using it in your ‘and’ and ”or’.

The Criteria API provides the org.hibernate.criterion.Order class to sort your result set in either ascending or descending order, according to one of your object’s properties.


Criteria cr4 = session.createCriteria(Employee.class)
 .add(Restrictions.lt("id", 4))
 .addOrder(Order.desc("id"));
 List<Employee> list4 = cr4.list();

Output:


3 Employee Name3
2 Employee Name2
1 Employee Name1

The Criteria API provides the org.hibernate.criterion.Projections class which can be used to get average, maximum or minimum of the property values. The Projections class is similar to the Restrictions class in that it provides several static factory methods for obtaining Projection instances.


Criteria cr5 = session.createCriteria(Employee.class)
.setProjection(Projections.max("id"));
List maxIdList=cr5.list();
System.out.println("maxId=>"+maxIdList.get(0));

Output:


maxId=>10

Notice that .list() method now returned a list of Integers. Had we used Projects.property(“name”), it would have return a list of String with all names.

Query by example(QBE) is an alternative querying technique supported by the main JPA vendors but not by the JPA specification itself. QBE returns a result set depending on the properties that were set on an instance of the queried class. So if in out Employee entity we fill in the ‘name’ field then the query will select all the Employee entities having the same ‘name’ field as the given Employee entity. The typical use case of QBE is evaluating a search form where the user can fill in any search fields and gets the results based on the given search fields. In this case QBE can reduce code size significantly.


Employee myEmployee=new Employee();
myEmployee.setId(2);//primary key is not considered
myEmployee.setName("Employee Name3");

Example example=Example.create(myEmployee);
Criteria cr6 = session.createCriteria(Employee.class)
.add(example);
List<Employee> list6 = cr6.list();

for (Employee employee : list6) {
System.out.println(employee.getId()+" " +employee.getName());
}

Output:


Hibernate: select this_.id as id1_0_0_, this_.name as name2_0_0_ from Employee this_ where (this_.name=?)
3 Employee Name3

Notice that the primary key is not considered. Even if the is is set to 2, it is not in where clause. Suppose you have to exclude some property in your Example object, just used .excludeProperty(“propertyName”) at the end and hibernate will not consider it. Similarly there are other methods like .ignoreCase() , .excludeZeroes() etc.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: