Hibernate - Criteria Queries

• import com.durgasoft.pojo.Employee;
• public class Test {
• public static void main(String[] args) throws Exception{
• Configuration cfg = new Configuration();
• cfg.configure();
• StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder();
• builder = builder.applySettings(cfg.getProperties());
• StandardServiceRegistry registry = builder.build();
• SessionFactory sessionFactory = cfg.buildSessionFactory(registry);
• Session session = sessionFactory.openSession();
• Query query = session.getNamedQuery("scalarl_sql_query");
• query.setFloat(0, 6000);
• query.setFloat("max_Sal", 8000);
• List<Object[]> list = query.list();
• System.out.println("ENO\tENAME\tESAL\tEADDR");
• System.out.println("---------------------------------");
• for(Object[] obj: list) {
• System.out.println(obj[0]+"\t"+obj[1]+"\t"+obj[2]+"\t"+obj[3]);
• session.close();
• sessionFactory.close();

In Database related applications, first we will define database logic at JAVA applications adn we will transfer that logic to databases inorder to execute, If we have any requirement like to execute a particular database logic frequently then it is suggestible to use Stored Procedures and functions in database related applications.

In the above context, define the frequently executed database logic at the database side in the form of Stored Procedures and functions, not at java side and prepare stored procedure call and function call at java application and send that procedure or function call to Database when we want to perform that respective database action. What is the difference between Stored Procedure and Function?

 Stored Procedure is a set of sql qeureis mantained at Database representing a particular action and it is not having return statement to return value.

Syntax:

create or replace PROCEDURE proc_Name[(Param_List)] AS ---Global Declarations---- BEGIN ---Database Logic----- END proc_Name; / --> To save and Compile Procedure.

 Stored Function is a set of sql queries maintained at Database representing a particular action and it is using return statement to return a value.

Syntax:

create or replace FUNCTION fun_Name[(Param_List)] return Data_Type AS ---Global Declarations---- BEGIN ---Database Logic---- return value; END fun_Name; / --> To Save and Compile Function

There are three types of parameters in Stored Procedures and Functions.

1)IN Type Parameter: It will get value from Procedure/function call to procedure body or function body.

EX: no IN number

2)OUT Type Parameter: It will get value from Procedure/function body to procedure/function call. EX: sal OUT float

3)INOUT Type parameter: It is acting as both IN type parameter and OUT type parameter. EX: sal INOUT float Note: If we want to execute select sql query and if we want to represent records of data then we have to use CURSOR type variable, Oracle database has provided a predefined CURSOR in the form of SYS_REFCURSOR to represent the result of a particular SQL Query.

If we want to use Stored Procedures and Functions in Hibernate applications then we have to use the following steps.

• Define Stored Procedure orr Function at Database side.
• Configure the respective Stored procedure/Function call in hibernate mapping file.
• In Client Application, create Query object with the procedure call or Function call logical

name.

• Execute Procedure or Function call.

EX:

create or replace PROCEDURE getEmps(emps OUT SYS_REFCURSOR , sal IN float) AS BEGIN open emps for

select * from emp1 where esal<sal; END getEmps;

1 SQL> create or replace FUNCTION getEmployees return SYS_REFCURSOR 2 AS 3 employees SYS_REFCURSOR; 4 BEGIN 5 open employees for 6 select * from emp1; 7 return employees; 8 END getEmployees; 9/

Function created.

By using Session interface methods like save(), persist(), update(), delete(),..... we are able to perform single record manipulation, but, if we want to perform manipulations over multiple records then we must go for HQL, Native SQL and Criterion API.

Where HQL is a powerfull, Object Oriented and Database INdependent Query language provided by Hibernate, but, HQL is not providing environment to perform DDL kind of operations and it is not supporting database dependent native operations like invoking stored procedures and functions,.....

To overcome the above problem with HQL we will use "Native SQL", in case of Native SQL , we have to write database dependent SQL queries directly, but, it is against to Hibernate, as per the Hibernate view we must not write database dependent sql queries in JAVA applications.

In Hibernate applications, to avoid totally query langugaes like SQL and HQL,....and to provide the complete dPersistence logic in the form of JAVA code we must use "Criterion API".

In the case of Criterion API, we will define persistence logic by using JAVA code only, where the required predefined library was provided by Hibernate in the form of "org.hibernate" package and "org.hibernate.criterion" package. If we want to use Criterion API in Hibernate applications then we have to use the following steps.

Criteria object is a central object in Critera API, it able to manage HQl query repersentation internally and it has provided predefined methods to defined query logic.

To create Criteria object we have to use the following method from Session.

public Criteria createCriteria(Class cls); EX: Criteria c = session.createCriteria(com.durgasoft.pojo.Employee.class); Note: It is equalent to the HQL query internally "from Employee".

Criterion is an object , it able to manage a single Conditional expression in database logic.

To create Criterion object we have to use the following methods from "org.hibernate.Restrictions" class.

public static Criterion isEmpty(String property) public static Criterion isNotEmpty(String property) public static Criterion isNull(String property) public static Criterion isNotNull(String property) public static Criterion in(String property, Object[] obj) public static Criterion in(String property, Collection c) public static Criterion between(String property, Object min_Val, Object max_Val) public static Criterion between(String property, Object[] obj) public static Criterion eq(String property, Object val) public static Criterion ne(String property, Object val) public static Criterion lt(String property, Object val) public static Criterion le(String property, Object val) public static Criterion gt(String property, Object val ) public static Criterion ge(String property, Object val) ----- ----- To add a particular Criterion object to Criteria object we have to use the following method. public void add(Criterion c)

EX:

Criterion c1 = Restrictions.ge("esal", 60000); Criterion c2 = Restrictions.le("esal", 90000); c.add(c1); c.add(c2); Note: With the above steps, Critera object is able to prepare the query like "from Employee esal>=6000 and esal<=9000".

• Create Projection objects , add Projection objects to ProjectionList and add

The main intention of Projection object is to represent a single POJO class property.

To get Projection object with a particular Property name we have to use the following method from "Projections" class.

public static Projection projection(String pro_Name)

To create ProjectionList object we have to use the following method from Projections class. public static ProjectionList projectionList()

To add Projection object to ProjectionList we have to use the following method from ProjectionList class.

public void add(Projection p)

To set ProjectionList to Criteria object we have to use the following method. public void setProjection(ProjectionList pl)

Ex:

ProjectionList pl = Projections.projectionList(); pl.add(Projections.property("eno")); pl.add(Projections.property("ename")); pl.add(Projections.property("esal")); pl.add(Projections.property("eaddr")); c.setProjection(pl);

EX: With the above , Criteria object is able to prepare HQL query internally like below. "select eno, ename, esal, eaddr from Employee where esal >=6000 and esal<=90000".

To represent a particular Order over the results, we have to use either asc(-) or desc(-) methods from "Order" class.

public static Order asc(String prop_Name) public static Order desc(String prop_Name)

To add Order object to Criteria object we have to use the following method. public void addOrder(Order o)

EX:

Order o = Order.desc("ename"); c.addOrder(o);

Note: With this, Criteria object is able to create HQl query like "select eno, ename, esal, eaddr from Employee where esal >=6000 and esal<=90000 order by desc(ename)"

To execute database logic existed in Criteria object we have to use the following methods.

public List list() public Iterator iterate() public ScrollableResults scroll() public Object uniqueResult()

EX:

List<Object[]> list = c.list();