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 the Criteria object and make custom SQL queries to the database to retrieve the data.

Hibernate criteria API is an alternative to Hibernate query language or HQL and generates complex queries using different criteria.

Here we cannot write any SQL or HQL queries instead we can add the different criteria parameters, and generate compatible 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 a 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 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 are all rows in the table, instead, if we want to restrict only 20 records, then we have to configure the 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 based on loan amount ordered by ascending order. we have to use criteria.addOrder method. we can also add conditional order by a parameter to the 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 retrieving unique objects We have many numbers of approaches to retrieving the distinct result

The first approach does the unique object filter in two steps, one step retrieves all the objects into the memory, and the next step is to filter for required uniqueness

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 the database is

select \* from home\_equity where loan\_amount <10000
equityCriteria.add(Restrictions.between("loan\_amount", "10000", "50000"));

native 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.

Hibernate criteria query | restriction projections Order by examples

#Hibernate Criteria API

Hibernate Criteria API