Hibernate Bulk Operations Related Techniques(HQL,Native-SQL,Criteria-API)

Bulk Operations Related Techniques:

We can use one of the Following three techniques to manipulate more than one row at a time.

1.HQL

2.Native SQL

3.Criteria API

Among these 3 HQL is the most recommanded…

1.HQL:

üHQL Queries are database independent Queries. So these Queries based persistence logic is database independent.

üHQL Queries will be written based on POJO classes and member variables of POJO classes.

üHQL Queries are Object-level queries. So they written Hibernate POJO class object as result.

üHQL queries and keywords are very much similar to SQL Queries of Oracle

üHibernate software converts HQL queries into SQL queries and sends them to Database software for execution

üHQL queries supports operators, expressions, conditions, joins, Sub-queries, Aggregate Function and etc.

üWe Can Use HQL queries for both select and non-select operations

üQuery Object represents HQL query in Hibernate applications. This is the object of a class that implements "org.hibernate.Query” interface

üHQL Query supports Two types of paramaters.

a)Positional Parameters

b)Named Parameters

Limitations:

1.HQL Queries can not perform DDL operations

2.HQL Queries Can not be used in PL/SQL Programming

3.HQL Queries can not be used to insert Single record into table

4.HQL Queries gives neglisible performance degradation because Conversion(HQL to SQL) when compare SQL.

We can use HQL by using list and iterator

By Using List

Query e=s.createQuery("select eb from emp1 as eb");

List<emp1> obj=e.list();

for(int i=0;i<obj.size();i++)

{

emp1 obj2=(emp1)obj.get(i);

System.out.println("empno is "+obj2.getEmpno());

}

By Using Iterator:

Query e1=s.createQuery("select eb from emp1 as eb");

Iterator<emp1> ie=e1.iterator();

System.out.println("By Using Iterator.......");

while(ie.hasNext())

{

emp1 obj2=(emp1)ie.next();

System.out.println("empno is "+obj2.getEmpno());

}

For example project

What is the diff b/w executing select HQL Queries By using list() and by Using iterate()?

àlist() method generates result by selecting all the records through the execution of single select HQL Query(NO lazy loading) that mean if results objects are used or not used by the programmer (by calling getXXX()) the POJO class objects representing results will be initialized with data(records)

àiterate() method selects the records from database table by executing multiple SQL select queries (almost one select query per record) and also generates first SQL select query only to retrive column names of the database table.iterate() method performs lazy loading…

So

List() method is recommended to use…

In Most of the Situations the hibernate software generated JDBC code internally uses pre-complied SQL queries with support of prepared statement object to perform persistance operation on the table.

What is the difference between executing SQL select Queries in JDBC and HQL Select Queries In hibernate.

Ans: Jdbc code base select queries Execution  gives ResultSet Object which is not Serializable object. So we can not send ResultSet Object over the network.

Hibernate based Select HQL Query Execution gives result in the form of collection  framework list data structure. Since this list data structure is serializable object by default. We can send that object over the network.

Note:

1.All collection Frames data structures are Serializable object by default.

2.In order to get above said benfit in hibernate the programmer should make hibernate POJO classes implementing java.io.Serializable interface.

Parameters in HQL queries:

1.Positional Parameters(?)

2.Named Parameters(:<name)(Recommended to use)

Positional Parameters:

We can pass values out side the query like PreparedStatement.

We can’t pass HQL keywords,POJO classnames,POJO class member variable names as values of parameters in HQL Queries. It is against of HQL syntax.

Query q=s.createQuery("select eb from emp1 as eb where eb.empno=?");

q.setInteger(0, 102);

List<emp1> obj=q.list();

for(int i=0;i<obj.size();i++)

{

emp1 e=(emp1)obj.get(i);

System.out.println("number is"+e.getEmpno()+"\n ename is "+e.getEname());

}

Wrong HQL Query is…

Query q=ses.createQuery(“select eb from ? As eb”);

q.setString(0,”emp1”);

Named Parameters:

Example Code:

Query q1=s.createQuery("select eb from emp1 as eb where eb.empno=:p1");

System.out.println("Named Parameters...........");

q1.setInteger("p1", 102);

List<emp1> obj1=q1.list();

for(int i=0;i<obj1.size();i++)

{

emp1 e1=(emp1)obj1.get(i);

System.out.println("number is"+e1.getEmpno()+"\n ename is "+e1.getEname());

}

In JDBC Programming we can’t use named parameters in SQL Queries.

In HQL Queries we can pass both named,positional parameters in a single Query. But we must pass positional parameters before the named parameters.

Valid Query:

Query q1=ses.createQuery(“select eb from emp as eb where eb.fname=? And eb.sname=:p”);

For example project

Executing Non-Select HQL Queries( Insert,Update,Delete)

1.Must be executed as Transactional Statement

2.HQL Insert Query is not given to insert only one record at a time but given to insert multiple record by selecting Them from anther table.

      Executing HQL delete Query

    

Transaction tx=ses.beginTransaction();

Query q1=ses.createQuery(“delete from emp1 as eb where eb.no=(select min(eb.no) from emp1 as eb”);

Int res=q1.executeUpdate();

Tx.commit();

Executing HQL update query with Parameters

Transaction tx=ses.beginTransaction(“update emp as eb set eb.ename=? And where eb.ename=:p1”);

Q1.setString(0,”zyz”);

Q1.setInteger(“p1”,9);

Int res=q1.executeQuery();

Tx.commit();

HQL insert Query is not given in the following from

Insert into emp eb value(?,?,?,?);//wrong Query

Insert into emp eb values(:p1,:p2,:p3,:p4);//wrong Query

The Suppoted Form of HQL insert Query is:

Insert into…Select …..

            (table1)       (table2)

Native SQL:

Native SQL queries are underlying database software specific SQL queries. These queries based persistance logic of hibernate is database dependent persistance logic.

If programmer feels certain operation is complex or not possible with HQL then it is recommended to use  “Native SQL” Queries.

There are two types of Native SQL Queries

1.Entity Queries:(These Queries returns all the Column values of the table

2.Scalar Queries:(These Queries returns specific column values or non-column values as result

Example code:

String sql="select {e.*} from emp e";

SQLQuery q1=s.createSQLQuery(sql);

q1.addEntity("e",emp1.class);

List<emp1> l=q1.list();

for(int i=0;i<l.size();i++)

{

emp1 obj=(emp1)l.get(i);

System.out.println("number is "+obj.getEmpno()+" ename "+obj.getEname());

}

Anther Form of above Example….

String sql1="select * from emp";

SQLQuery q2=s.createSQLQuery(sql1);

q2.addEntity(emp1.class);

List<emp1> l1=q2.list();

for(int i=0;i<l1.size();i++)

{

emp1 obj=(emp1)l.get(i);

System.out.println("number is "+obj.getEmpno()+" ename "+obj.getEname());

}

We can pass both named and positional parameters in NativeSql Queries.

SQLQuery q3=s.createSQLQuery(sql2);

q3.addEntity(emp1.class);

q3.setInteger(0, 1);

q3.setString("p1", "xyz");

List<emp1> l2=q3.list();

for(int i=0;i<l2.size();i++)

{

emp1 obj=(emp1)l.get(i);

System.out.println("number is "+obj.getEmpno()+" ename "+obj.getEname());

}

Like JDBC datatype in the form of the Types.INTEGER,Types.VARCHAR and etc.

Hibernate is also giving its own built-in data types as constants of “org.hibernate.Hibernate” class.

Ex:Hibernate.INTEGER,Hibernate.FLOAT,Hibernate.LONG,Hibernate.STRING etc..

These Hibernate Data types are useful while catching scalar native sql query results in hibernate environment.

Example code:

String sql3="select max(empno) as mval from emp";

SQLQuery q4=s.createSQLQuery(sql3);

q4.addScalar("mval", Hibernate.INTEGER);

List<emp1> l3=q4.list();

System.out.println("min value is "+l3.toString());

Making Scalar Query Returning More Than one result

String sql4="select count(*) as a,avg(empno) as b from emp";

SQLQuery q5=s.createSQLQuery(sql4);

q5.addScalar("a", Hibernate.FLOAT);

q5.addScalar("b", Hibernate.INTEGER);

List l4=q5.list();

Object res[]=(Object[])l4.get(0);

System.out.println("count is "+res[0].toString());

System.out.println("count is "+res[1].toString());

All Non-select Native SQL Queries must be executed as Transactional Statements

By Using Native Sql Queries we can directly insert single record into DB table by directly passing values

String sql5="insert into emp values(:p1,:p2,:p3)";

SQLQuery q6=s.createSQLQuery(sql5);

q6.setInteger("p1", 6);

q6.setString("p2", "skrkr");

q6.setInteger("p3", 200);

int res1=q6.executeUpdate();

Transaction t=s.beginTransaction();

t.commit();

We can Execute DDL Queries as Native SQL Queries

String sql6="drop table emp1";

SQLQuery q7=s.createSQLQuery(sql6);

q7.executeUpdate();

System.out.println("Table is dropped...............");

For Sample Project 

The Native SQL Query that is placed in hibernate mapping file having logical name is called Named Native SQL Query. These Queries are required.

1.To Make Native SQL Queries visible across the multiple Session Object of HB application

2.To make Native SQL Queries as flexible Queries by passing them from outside persistence logic

Calling PL/SQL Procedures or Functions for Hibernate

1.Pagination is not possible on the result generated by Queries of HB Specific PL/SQL procedures That means we can’t use setFirstResult(),setMaxResults() methods in this Environment.

2.We must follow the following syntax to call pl/sql procedure                                               

        { call Procedurename(<parameters>)}

3.We must follow the following syntax to call pl/sql function  {?=call functionname(<parameters>)}

4.Pl/sql procedure doesn’t return value.where as pl/sql function returns a value

Rules Specific to Oracle:

1.Pl/Sql procedure first parameter must be an out parameter returning resultset (Cursor having Records)

2.Pl/SQL function must return a result set(Cursor having records)

Note: sys_refcursor is cursor type data type given by PL/SQL Programming of oracle having the ability to store selected records (one or more) like result set object of JDBC Programming

Criteria API

This API is given to develop HB Persistence logic purely in java Statement and without using SQL,HQL

Queries.

Criteria API  means working with classes and interfaces of org.hibernate.criterian package classes and interfaces  to develop persistence logic

We have to use criteria API only to select all the column values of the table that means criteria API is not design to select specific column values of a table

Criteria API based persistence logic is database independent persistence logic

Criteria ct1=s.createCriteria(emp1.class);

List<emp1> l1=ct1.list();

for(int i=0;i<l1.size();i++)

{

emp1 obj=(emp1)l1.get(i);

System.out.println("empno is "+obj.getEmpno());

}

Example code to execute logic with condition.

Criteria ct2=s.createCriteria(emp1.class);

Criterion cond2=Restrictions.ge("empno", 5);

ct2.add(cond2);

List<emp1> l2=ct2.list();

for(int i=0;i<l2.size();i++)

{

emp1 obj=(emp1)l2.get(i);

System.out.println("empno is "+obj.getEmpno());

}

sample project