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