Let's take a look at how to add restrictions, which are equal to the WHERE
clause in SQL.
Let's consider that we have four records in the employee table, as shown in the following tables:
This is the Employee
table:
department |
salary |
firstName |
id |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This is the Department
table:
deptName |
id |
---|---|
|
|
|
|
|
|
Now, the scenario is that we want to get only those employees whose salary is greater than 35000
.
The equivalent SQL query to select the above employees is as follows:
SELECT * FROM employee WHERE salary > 35000;
Now, let's look at how to do the same using hibernate.
Enter the following code to create a criteria for employee
:
Criteria criteria = session.createCriteria(Employee.class); criteria.add(Restrictions.gt("salary", 35000)); List<Employee> employees = criteria.list(); for (Employee employee : employees) { System.out.println(employee.toString()); }
The output will be as follows:
Hibernate: select this_.id as id0_1_, this_.department as department0_1_, this_.firstName as firstName0_1_, this_.salary as salary0_1_, department2_.id as id1_0_, department2_.deptName as deptName1_0_ from employee this_ left outer join department department2_ on this_.department=department2_.id where this_.salary>? Employee id: 1 first name: yogesh salary: 50000.0 department: developement Employee id: 6 first name: vishal salary: 75000.0 department: R&D
Here, you need to understand the line criteria.add(Restrictions.gt("salary", 35000d));
only.
We represent WHERE salary > 35000
in Restrictions.gt("salary", 35000d),
gt, that is, using the greater than sign. It will find all the records of the employees having a salary greater than 35000
.
There are many functions available in the class Restrictions
. You can use logical operators such as:
gt
(>
, greater than)ge
(>=
, greater than or equal to)lt
(<
, less than)le
(<=
, less than or equal to)eq
(=
, equal to)ne
(<>
, !=
, not equal to)Apart from these logical operators, you can use:
like
(to perform Like
operation)iLike
(to perform Like
operation with ignore
case)Not
Between
In
Or
isNull
isNotNull
isEmpty
isNotEmpty
, and many more useful functionsThese will help us to represent the SQL expression in hibernate notation.
We can add multiple conditions by adding more criteria.add()
statements.