Tuesday, January 31, 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 use case, where the 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 the database to retrieve the data.

Hibernate criteria API is alternative to Hibernate query language or HQL and generate complex queries using different criteria. Here we cannot write much SQL or hql queries instead we can add the different criteria 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 criteria on a persistent object. This object is created from the available 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 stores this all the rows in the list of HomeEquity Objects.

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

select * from home_equity
The above results all rows in the table, instead if we want 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 ascending order. we have to used criteria.addOrder method.
we can also add conditional order by a parameter to criteria

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

select * from home_equity order by loan_amount asc

Criteria Distinct Query for retrieve unique objects:-

We have many numbers of approaches to retrieve the distinct result

The first approach does the unique object filter in two steps, one step retrieves 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 executed 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 hibernating criteria queries


EmoticonEmoticon

Note: Only a member of this blog may post a comment.