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(); }

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(); }

select * from home_equity order by loan_amount asc

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%") );

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
Similar Posts