Artikel Terbaru

Tuesday, 31 January 2012

Hibernate criteria query | restriction projections Order by examples


Hibernate criteria API:-


criteria are used to select the specific type of data from the database.
Let us take the usecase, where website has search capability provided, you have to search the website with different conditions, To handle this using hibernate, we can compose different parameters to Criteria object and make custom sql queries to database to retireve the data.

Hibernate criteria API is alternative to Hibernate query language or HQL and generate complex queries using different crierias. Here we can not write much sql or hql queries instead we can add the different critiera parameters, generates combatable sql queries

Criteria can be created using Session Object.

Create Criteria object using Hibernate API:-

Hibernate provides org.hibernate.Criteria to create criterias on a persistent object. This object is created from the avilable session objects.

HomeEquity is java persistence object which is mapped to home_equity in hibernate configuration.

Criteria equityCriteria = sess.createCriteria(HomeEquity.class);
	List homeEquitys = equityCriteria.list();
}

The above command retrieves all the rows in the table_homeequity table and store this all the rows in the list of HomeEquity Objects.

The following is the equvalent native SQL query that executes at database level is

select * from home_equity
The above results all rows in the table, instead if we wan to restrict only 20 records, then we have to configure number to restrict the data using Criteria.setMaxResults(Integer no)

Criteria equityCriteria = sess.createCriteria(HomeEquity.class);
equityCriteria.setMaxResults(20)
List homeEquitys = equityCriteria.list();
}
the corresponding SQl query executed in different databases are

For MySQL
select * from home_equity limit 20  
For Oracle
select * from home_equity rownum <=20 

Sort the objects using Criteria Order by query:-

To order the list of objects with based on loan amount ordered by asecending order. we have to used criteria.addOrder method.
we can also add conditioninal order by parameter to criteria

Criteria equityCriteria = sess.createCriteria(HomeEquity.class);
equityCriteria.addOrder(Order.asc("loan_amount"));
List homeEquitys = equityCriteria.list();
}
The equivalent query executed at database is

select * from home_equity order by loan_amount asc

Criteria Distinct Query for retrieve unique objects:-

We have many number of approaches to retrieve the distinct result

First approach does the unique object filter in two steps, one step is retrieve all the objects into the memory, and next step is to filter for required unique nes
equityCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
second approache using criteria projects
equityCriteria.setProjection(Projections.distinct(Projections.property("loan_type")));
the qual native sql executed at database is 
select distinct loan_type from home_equity

Criteria Restrictions Query for comparison of objects:-

hibernate Criteria API provides Restrictions class which has built in methods(ne,eq,isNotNull,gt) to do comparison operators.

equityCriteria.add( Restrictions.like("name", "A%") );

the equal native sql query executed at Database is
select * from home_equity where name like 'A%'
equityCriteria.add( Restrictions.lit("loan_amount", "100000") );
the equal native sql query exected at database is
select * from home_equity where loan_amount <10000

equityCriteria.add(Restrictions.between("loan_amount", "10000", "50000"));

natvive sql for the above criteria is
select * from home_equity where loan_amount between 10000 and 50000
Hope you understand the basic criteria queries. This is part 1 for hibernate criteria queries