Recent Posts

Saturday, 10 September 2016

Criteria Tutorial


1. The Criteria API allows queries to be built at run time without direct string manipulation.
2. Criteria is an API from hibernate to write Queries in object oriented manner rather than SQL or HQL.
3. Criteria is also database independent, Because it internally generates HQL queries.
4. We can execute only SELECT statements using Criteria; we can't execute UPDATE, DELETE statements using Criteria.
5. Criteria is suitable for executing dynamic queries.
6. Criteria API also include Query by Example (QBE) functionality for supplying example objects.
7. Criteria also includes projection and aggregation methods.
Note
     Queries expressed as criteria are less readable than queries expressed in HQL.
To work with all criteria examples I am using the following Entity
package com.ashok.hibernate;

import javax.persistence.Column;
import javax.persistence.Id;

import org.hibernate.annotations.GenericGenerator;

public class Account {
   @Id
   @GenericGenerator(name = "myGen", strategy = "increment")
   @Column(name = "ACCNO")
   private int accountId;
   @Column(name = "NAME")
   private int name;
   @Column(name = "BALANCE")
   private int balance;

   public Account() {

   }

   public Account(int accountId, int name, int balance) {
      super();
      this.accountId = accountId;
      this.name = name;
      this.balance = balance;
   }

   public int getAccountId() {
      return accountId;
   }

   public void setAccountId(int accountId) {
      this.accountId = accountId;
   }

   public int getName() {
      return name;
   }

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

   public int getBalance() {
      return balance;
   }

   public void setBalance(int balance) {
      this.balance = balance;
   }

   @Override
   public String toString() {
      return "Account [accountId=" + accountId + ", name=" + name + ", balance=" + balance + "]";
   }
}
     Here's a case study to retrieve a list of Account objects, with optional search criteria - balance, name, accountId, order by accountId.
1. HQL example
     In HQL, you need to compare whether this is the first criteria to append the 'where' syntax. It's work, but the long codes are ugly, cumbersome and error-prine string contatenation may cause security concern like SQL injection.
private static List<Account> getAccounts(Session session, Double balance, String name, Integer accountId) {
   boolean isFirst = true;
   StringBuilder query = new StringBuilder("From Account a");
   if(null != balance) {
      if(isFirst) {
         query.append(" WHERE a.balance >= "+balance);
      } else {
         query.append(" AND a.balance >= "+balance);
      }
     isFirst = false;
   }
   if(null != name) {
     if(isFirst) {
         query.append(" WHERE a.name LIKE ' "+name+" ' ");
      } else {
         query.append(" AND a.name LIKE ' "+name+" ' ");
      }
     isFirst = false;
   }
   if(null != accountId) {
      if(isFirst) {
          query.append(" WHERE a.accountId >= "+accountId);
       } else {
          query.append(" AND a.accountId >= "+accountId);
       }
      isFirst = false;
   }
   query.append("ORDER BY a.accountId");
   Query result = session.createQuery(query.toString());

   List<Account> accounts = result.list();
   System.out.println(accounts.size());
   return accounts;
}
2. Criteria example
     In criteria, you do not need to compare whether this is the first criteria to append the where sysntax. The line of code is reduce and everything is handled in a more elegant and object oriented way.
Code block
private static List<Account> getAccounts(Session session, Double balance, String name, Integer accountId) {
   boolean isFirst = true;
   Criteria criteria = session.createCriteria(Account.class);
   if(balance != null) {
      criteria.add(Restrictions.ge("balance",balance));
   }
   if(name != null) {
      criteria.add(Restrictions.like("name",name));
   }
   if(accountId != null) {
      criteria.add(Restrictions.like("accountId ",accountId));
   }
   criteria.addOrder(Order.asc("accountId"));
   List<Account> accounts = criteria.list();
   System.out.println(accounts);
   return accounts;
}
Steps to work with Criteria API
Step 1: Create org.hibernate.Criteria Object
Criteria criteria = session.createCriteria(EntityClassName.class);
Step 2: Create org.hibernate.criterion.Criterion Object per each condition of the query and add to Criteria object.
Criterion criterion = Restrictions.eq("propertyName", propertyValue);
criteria.ass(criterion);
Step 3: Execute org.hibernate.Criteria object (by calling list() method on Criteria object)
List list = criteria.list();
Write and execute criteria API code for the following SQL statement?
SQL: SELCT * FROM ACCOUNT WHERE NAME = 'ashok' AND BALANCE > 1500;

CRITERIA
// SELECT * FROM ACCOUNT
Criteria criteria = session.createCriteria(Account.class);

// NAME = 'ashok'
Criteria nameCriteria = Restrictions.eq("name", "ashok");

// BALANCE > 1500
Criteria balanceCriteria = Restrictions.gt("balance", 1500.0);

// NAME = 'ashok' AND BALANCE>1500
Criteria criterion = Restrictions.and(nameCriteria , balanceCriteria);

// SELCT * FROM ACCOUNT WHERE NAME = 'ashok' AND BALANCE > 1500;
criteria.add(criterion);

List<Account> accounts = criteria.list();
for(Account account : accounts) {
   System.out.println(account);
}
Implement the previous code using method chaining concept
List<Account> accounts = session.createCriteria(Account.class)
                              .add(Restrictions.eq("name", "ashok")
                              .add(Restrictions.gt("balance", 1500.0))
                              .list();
for(Account account : accounts) {
   System.out.println(account);
}
1. Criteria is an interface used to represent query in Object oriented format. It contains methods to add Criterion objects, Order objects, Projection objects, Pagination methods ...etc. CriteriaImpl is the implementation class of Criteria.
2. Restrictions is class which has methods to define conditions. There class methods return different XXXExpression classes which implements Criterion interface. So Restrictions is a static factory Criterion instances.
3. Expression class is child class of Restrictions. It defines methods to implement native SQL. As latest versions of Hibernate provides separate Native SQL API, this class made as deprecated.
4. Criterion is an interface which is used to represent one condition in object oriented manner. By calling methods on Restrictions we will get this object. There are different types of XXXExpression classes which are implementing Criterion interface. But all those implementation are Abstracted with Criterion interface
5. org.hibernate.criterion.Property is a class available in hibernate API
6. Property is used to create Criterion objects (means creating conditions of the query).
7. If we are using same property of entity, on multiple conditions it is better to go for Property rather than Restrictions
8. You can create a Property by calling Property.forName("property-name")
9. Proerty is just like Restrictions class. Both are meant for creating Criterion objects.
SQL : SELECT * FROM ACCOUNT WHERE NAME = 'ashok' AND BALANCE > 1500;
// SELECT * FROM ACCOUNT
Criteria criteria = session.createCriteria(Account.class);

// NAME = 'ashok'
Property nameProperty = Property.forName("name");
Criteria nameCriteria = nameProperty .eq("ashok");

// BALANCE > 1500
Property balanceProperty = Property.forName("balance");
Criteria balanceCriteria = balanceProperty .gt(1500.0);

// NAME = 'ashok' AND BALANCE>1500
Criteria criterion = Restrictions.and(nameCriteria , balanceCriteria);

// SELCT * FROM ACCOUNT WHERE NAME = 'ashok' AND BALANCE > 1500;
criteria.add(criterion);

List<Account> accounts = criteria.list();
for(Account account : accounts) {
   System.out.println(account);
}

OR
List<Account> accounts = session.createCriteria(Account.class)
                              .add(Restrictions.and(
                                 Property.forname("name").eq("ashok")
                                 Property.forname("balance").gt(1500.0))
                              .list();
Apply Pagination in criteria
Criteria criteria = session.createCriteria(Account.class);
criteria.setFirstResult(5);
criteria.setMaxResults(5);
                          
for(Account account : accounts) {
   System.out.println(account);
}
Examples based on different conditions
Example: Id equal Restriction
SQL: SELECT * FROM ACCOUNT WHERE ACCNO=1001;
CRITERIA:
List<Account> accounts = session.createCriteria(Account.class)
                          .add(Restrictions.eq("accountId",1001)).list();
OR
List<Account> accounts = session.createCriteria(Account.class)
                          .add(Restrictions.idEq(1001)).list();
OR
List<Account> accounts = session.createCriteria(Account.class)
                          .add(Property.forName("accountId").eq(1001))
                          .list();
Example: Sorting the result
SQL: SELECT * FROM ACCOUNT ORDER BY BALANCE DESC, NAME ASC, ACCNO DESC;
CRITERIA:
List<Account> accounts = session.createCriteria(Account.class)
                          .addOrder(Property.forName("balance").desc())
                          .addOrder(Property.forName("name").asc())
                          .addOrder(Property.forName("accountId").desc())
                          .list();
OR
List<Account> accounts = session.createCriteria(Account.class)
                          .addOrder(Order.desc("balance")))
                          .addOrder(Order.asc("name"))
                          .addOrder(Order.desc("accountId"))
                          .list();

No comments:

Post a Comment