Anjaneya Tech: Hibernate

Showing posts with label Hibernate. Show all posts

Friday, 24 May 2013

Hibernate Criteria Query Example

The Criteria interface allows to create and execute object-oriented queries. It is powerful alternative to the HQL but has own limitations. Criteria Query is used mostly in case of multi criteria search screens, where HQL is not very effective.

The interface org.hibernate.Criteria is used to create the criterion for the search. The org.hibernate.Criteria interface represents a query against a persistent class. The Session is a factory for Criteria instances. Here is a simple example of Hibernate Criterial Query:

The above Criteria Query example selects all the records from the table and displays on the console. In the above code the following code creates a new Criteria instance, for the class Insurance:

Criteria crit = session.createCriteria(Insurance.class);

The code:

List insurances = crit.list();

creates the sql query and execute against database to retrieve the data.

Criteria Query Examples

In the last lesson we learnt how to use Criteria Query to select all the records from Insurance table. In this lesson we will learn how to restrict the results returned from the database. Different method provided by Criteria interface can be used with the help of Restrictions to restrict the records fetched from database.

Criteria Interface provides the following methods:

Method	Description
add	The Add method adds a Criterion to constrain the results to be retrieved.
addOrder	Add an Order to the result set.
createAlias	Join an association, assigning an alias to the joined entity
createCriteria	This method is used to create a new Criteria, "rooted" at the associated entity.
setFetchSize	This method is used to set a fetch size for the underlying JDBC query.
setFirstResult	This method is used to set the first result to be retrieved.
setMaxResults	This method is used to set a limit upon the number of objects to be retrieved.
uniqueResult	This method is used to instruct the Hibernate to fetch and return the unique records from database.

Class Restriction provides built-in criterion via static factory methods. Important methods of the Restriction class are:

Method	Description
Restriction.allEq	This is used to apply an "equals" constraint to each property in the key set of a Map
Restriction.between	This is used to apply a "between" constraint to the named property
Restriction.eq	This is used to apply an "equal" constraint to the named property
Restriction.ge	This is used to apply a "greater than or equal" constraint to the named property
Restriction.gt	This is used to apply a "greater than" constraint to the named property
Restriction.idEq	This is used to apply an "equal" constraint to the identifier property
Restriction.ilike	This is case-insensitive "like", similar to Postgres ilike operator
Restriction.in	This is used to apply an "in" constraint to the named property
Restriction.isNotNull	This is used to apply an "is not null" constraint to the named property
Restriction.isNull	This is used to apply an "is null" constraint to the named property
Restriction.le	This is used to apply a "less than or equal" constraint to the named property
Restriction.like	This is used to apply a "like" constraint to the named property
Restriction.lt	This is used to apply a "less than" constraint to the named property
Restriction.ltProperty	This is used to apply a "less than" constraint to two properties
Restriction.ne	This is used to apply a "not equal" constraint to the named property
Restriction.neProperty	This is used to apply a "not equal" constraint to two properties
Restriction.not	This returns the negation of an expression
Restriction.or	This returns the disjuction of two expressions

Here is an example code that shows how to use Restrictions.like method and restrict the maximum rows returned by query by setting the Criteria.setMaxResults() value to 5.

package roseindia.tutorial.hibernate;

import org.hibernate.Session;
import org.hibernate.*;
import org.hibernate.criterion.*;
import org.hibernate.cfg.*;
import java.util.*;
/**
* @author Deepak Kumar
*
* http://www.roseindia.net
Hibernate Criteria Query Example
*
*/public class HibernateCriteriaQueryExample2 {
  public static void main(String[] args) {
  Session session = null;
  try {
  // This step will read
hibernate.cfg.xml and prepare hibernate for
  // use
  SessionFactory sessionFactory
= new Configuration().configure()
  .buildSessionFactory();
  session =
sessionFactory.openSession();
  //Criteria Query Example
  Criteria crit =
session.createCriteria(Insurance.class);
  crit.add(Restrictions.like("
insuranceName", "%a%")); //Like condition
  crit.setMaxResults(5); //
Restricts the max rows to 5

  List insurances = crit.list();
  for(Iterator it =
insurances.iterator();it.hasNext();){
  Insurance insurance =
(Insurance) it.next();
  System.out.println("
ID: " + insurance.getLngInsuranceId());
  System.out.println("
Name: " + insurance.getInsuranceName());

  }
  session.close();
  } catch (Exception e) {
  System.out.println(e.getMessage());
  } finally {
  }
  }
}

HQL Order By Example

Order by clause is used to retrieve the data from database in the sorted order by any property of returned class or components. HQL supports Order By Clause. In our example we will retrieve the data sorted on the insurance type. Here is the java example code:

To run the example select Run-> Run As -> Java Application from the menu bar. Following out is displayed in the Eclipse console:

Hibernate: select insurance0_.ID as col_0_0_ from insurance insurance0_ order by insurance0_.insurance_name

ID: 1

Hibernate: select insurance0_.ID as ID0_, insurance0_.insurance_name as insurance2_2_0_, insurance0_.invested_amount as invested3_2_0_, insurance0_.investement_date as investem4_2_0_ from insurance insurance0_ where insurance0_.ID=?

Name: Car Insurance

ID: 4

Name: Car Insurance

ID: 5

Name: Dental Insurance

ID: 11

Name: Home Insurance

ID: 12

Name: Home Insurance

ID: 2

Name: Life Insurance

ID: 3

Name: Life Insurance

ID: 6

Name: Life Insurance

ID: 9

Name: Medical Insurance

ID: 10

Name: Medical Insurance

ID: 13

Name: Motorcycle Insurance

ID: 14

Name: Motorcycle Insurance

ID: 7

Name: Travel Insurance

ID: 8

Name: Travel Insurance

HQL Where Clause Example

Where Clause is used to limit the results returned from database. It can be used with aliases and if the aliases are not present in the Query, the properties can be referred by name. For example:

from Insurance where lngInsuranceId='1'

Where Clause can be used with or without Select Clause. Here the example code:

package roseindia.tutorial.hibernate;

import org.hibernate.Session;
import org.hibernate.*;
import org.hibernate.cfg.*;

import java.util.*;

/**
* @author Deepak Kumar
*
* http://www.roseindia.net
* HQL Where Clause Example
* Where Clause With Select Clause Example
*/
public class WhereClauseExample {
  public static void main(String[] args) {
  Session session = null;

  try{
  // This step will read hibernate.cfg.
xml and prepare hibernate for use
  SessionFactory sessionFactory = new
Configuration().configure().
buildSessionFactory();
  session =sessionFactory.openSession();

  System.out.println("***************
****************");
  System.out.println("Query using
Hibernate Query Language");
  //Query using Hibernate Query Language
String SQL_QUERY =" from Insurance
as insurance where insurance.
lngInsuranceId='1'";
Query query = session.createQuery
(SQL_QUERY);
for(Iterator it=query.iterate()
;it.hasNext();){
Insurance insurance=(Insurance)it
.next();
System.out.println("ID: " + insurance.
getLngInsuranceId());
System.out.println("Name: "
+ insurance. getInsuranceName());

}
System.out.println("****************
***************");
System.out.println("Where Clause With
Select Clause");
  //Where Clause With Select Clause
SQL_QUERY ="Select insurance.
lngInsuranceId,insurance.insuranceName," +
"insurance.investementAmount,
insurance.investementDate from Insurance
insurance "+ " where insurance.
lngInsuranceId='1'";
query = session.createQuery(SQL_QUERY);
for(Iterator it=query.iterate();it.
hasNext();){
Object[] row = (Object[]) it.next();
System.out.println("ID: " + row[0]);
System.out.println("Name: " + row[1]);

}
System.out.println("***************
****************");

  session.close();
  }catch(Exception e){
  System.out.println(e.getMessage());
  }finally{
  }
  }
}

To run the example select Run-> Run As -> Java Application from the menu bar. Following out is displayed in the Eclipse console:

*******************************

Query using Hibernate Query Language

Hibernate: select insurance0_.ID as col_0_0_ from insurance insurance0_ where (insurance0_.ID='1')

ID: 1

Name: Car Insurance

*******************************

Where Clause With Select Clause

Hibernate: select insurance0_.ID as col_0_0_, insurance0_.insurance_name as col_1_0_, insurance0_.invested_amount as col_2_0_, insurance0_.investement_date as col_3_0_ from insurance insurance0_ where (insurance0_.ID='1')

ID: 1

Name: Car Insurance

*******************************

Friday, 24 May 2013

Hibernate Criteria Query Example

HQL Order By Example

HQL Where Clause Example

Simple CRUD in Laravel Framework