Hibernate Criteria Query Language(HCQL)
Criteria is nothing but a Condition. CQL is used to apply conditions on SELECT Queries. The Criteria interface provides methods to apply criteria on SELECT queries.
Criteria Interface
The Criteria interface provides many methods to specify criteria. The object of Criteria can be obtained by calling the createCriteria() method of Session interface
public Criteria createCriteria(Class c)
commonly used methods of Criteria interface are as follows:
-
public Criteria add(Criterion c) is used to add restrictions.
-
public Criteria addOrder(Order o) specifies ordering.
-
public Criteria setFirstResult(int firstResult) specifies the first number of record to be retreived.
-
public Criteria setMaxResult(int totalResult) specifies the total number of records to be retreived.
-
public List list() returns list containing object.
-
public Criteria setProjection(Projection projection) specifies the projection
Working with Criteria Query Language
Example Criteria
Criteria crit = session.createCriteria(Products.class);
Criterion c1=Restrictions.gt("price", new Integer(12000));
//price is our pojo class variable
crit.add(c1); // adding criterion object to criteria class object
List l = crit.list(); // executing criteria query
-
If we want to put, we need to create one Criterion Interface object and we need to add() this object to Criteria Class object
-
In order to get Criterion object, we need to use Restrictions class. Restrictions is the factory for producing Criterion objects
-
In Restrictions class, we have all static methods and each method of this class returns Criterion object
REMEMBER: CQL is only used on SELECT Queries
Example: Get the details from bank table where balance>3000.
Table : Bank
mysql> select * from bank;
+-------+---------+---------+
| accno | accname | balance |
+-------+---------+---------+
| 101 | Satya | 2000 |
| 102 | Surya | 3000 |
| 103 | Ravi | 1000 |
| 104 | Rakesh | 4000 |
| 105 | CHANDU | 5000 |
+-------+---------+---------+
5 rows in set (0.00 sec)
BankBo.java
package hql;
public class BankBo {
private int accno;
private String accname;
private double balance;
public int getAccno() {
return accno;
}
public void setAccno(int accno) {
this.accno = accno;
}
public String getAccname() {
return accname;
}
public void setAccname(String accname) {
this.accname = accname;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
}
BankBo.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="hql.BankBo" table="bank">
<id name="accno" column="accno" />
<property name="accname" column="accname" />
<property name="balance" column="balance" />
</class>
</hibernate-mapping>
Example: Adding ORDERBY Conditions to Criteria
If we want to add some sorting order for the objects, we need to add an Order class object to the Criteria class object by calling addOrder() method.In Order class, we have asc() and dsc() for getting an objects in required order.
public class CriteriaOrderExample {
public static void main(String[] args) {
Configuration cfg = new Configuration();
cfg.configure("hibernate.cfg.xml");
SessionFactory factory = cfg.buildSessionFactory();
Session session = factory.openSession();
Criteria criteria = session.createCriteria(BankBo.class);
Criterion cn = Restrictions.gt("balance", new Double(2000));
criteria.add(cn);
criteria.addOrder(Order.asc("balance"));
List list = criteria.list();
Iterator it = list.iterator();
while (it.hasNext()) {
BankBo bo = (BankBo) it.next();
System.out.println(bo.getAccno() + ", "+ bo.getBalance());
}
session.close();
factory.close();
}
}
----------------------------------------------------
log4j:WARN No appenders could be found for logger
102, 3000.0
104, 4000.0
105, 5000.0
hibernate will select the records (rows) from BankBo table and stores them into a ResultSet and then converts each row data of resultset into a POJO class object basing on our field type, then all these objects into a list according to the order you have given
Example: Adding multiple conditions
If we want to put more conditions on the data (multiple conditions) then we can use and method, or method given by the Restrictions class
crit.add(Restrictions.and(Restrictions.like("accname","%satya%"),
Restrictions.eq("price",new Integer(12000))));
List l=crit.list();
Iterator it = l.iterator();
Like this we can add any number of conditions
Hibernate Projections
In criteria, we are able to load complete object only, to load the partial objects (Selected Columns only) we need to use projections.
-
Projection is an Interface, Projections is an class for producing projection objects.
-
In Projections class, we have all static methods and each method of this class returns Projection interface object.
-
If we want to add a Projection object to Criteria then we need to call a method setProjection()
Projections Syntax
while adding projection object to criteria, it is possible to add one object at a time. It means if we add 2nd projection object then this 2nd one will override the first one (first one won’t be work), so at a time we can only one projection object to criteria object.
Using criteria, if we want to load partial object from the database, then we need to create a projection object for property that is to be loaded from the database
Example:
Criteria crit = session.createCriteria(Products.class);
crit.setProjection(Projections.proparty("proName"));
List l=crit.list();
Iterator it=l.iterator();
while(it.hasNext())
{
String s = (String)it.next();
// ---- print -----
}
Example 1: Load Single Column using Projections
In below example we are using same configuration files and BanlBo.java as above examples
public class ProjectionsDemo {
public static void main(String[] args) {
Configuration cfg = new Configuration();
cfg.configure("hibernate.cfg.xml");
SessionFactory factory = cfg.buildSessionFactory();
Session session = factory.openSession();
Criteria criteria = session.createCriteria(BankBo.class);
Criterion cn = Restrictions.gt("balance", new Double(2000));
criteria.add(cn);
criteria.setProjection(Projections.property("balance"));
List list = criteria.list();
System.out.println("List Size : " + list.size());
Iterator it = list.iterator();
while (it.hasNext()) {
Double bal = (Double) it.next();
System.out.println("Balance : " + bal);
}
session.close();
factory.close();
}
}
--------------------------------------
Hibernate: select this_.balance as y0_ from bank this_ where this_.balance>?
List Size : 3
Balance : 3000.0
Balance : 4000.0
Balance : 5000.0
Example 2: Load Multiple Columns using Projections
If we want to load partial object, with multiple columns using criteria then we need to create the ProjectionList with the multiple properties and then we need to add that Projectionist to the criteria.
public class ProjectionsMultipleColumns {
public static void main(String[] args) {
Configuration cfg = new Configuration();
cfg.configure("hibernate.cfg.xml");
SessionFactory factory = cfg.buildSessionFactory();
Session session = factory.openSession();
Criteria criteria = session.createCriteria(BankBo.class);
Criterion cn = Restrictions.gt("balance", new Double(2000));
criteria.add(cn);
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.property("accname"));
projectionList.add(Projections.property("balance"));
criteria.setProjection(projectionList);
List list = criteria.list();
Iterator it = list.iterator();
while (it.hasNext()) {
Object[] o = (Object[]) it.next();
System.out.println(o[0] + " : " + o[1]);
}
session.close();
factory.close();
}
}
-------------------------------------
Hibernate: select this_.accname as y0_, this_.balance as y1_ from bank this_ where this_.balance>?
Surya : 3000.0
Rakesh : 4000.0
CHANDU : 5000.0
Difference between HQL and CQL
| HQL (Hibernate Query Language) | CQL (Criteria Query Language) |
|---|---|
| We can perform **both select and non-select operations ** | Criteria is only for selecting the data, we cannot perform non-select operations |
| suitable for executing Static Queries | suitable for executing Dynamic Queries |
| Takes less time to execute | Takes more time to execute |