query by example in hibernate
If you have multiple criteria to be set for fetching data from database , Hibernate provide a feature called query by example. Here you can create an example object with all the properties set to it . This object can be passed to Example.create() method. Lets try to understand querybyExample with the below example code.
Here in this example lets pull the record from Employee table with column value for emp_name as “Alex” .
Employee.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
package com.j2eereference.hibernate.queryByExample; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name = "employee") public class Employee { private int emp_id; private String emp_name; @Id @GeneratedValue @Column(name = "emp_id") public int getEmp_id() { return emp_id; } public void setEmp_id(int emp_id) { this.emp_id = emp_id; } @Column(name = "emp_name") public String getEmp_name() { return emp_name; } public void setEmp_name(String emp_name) { this.emp_name = emp_name; } } |
QueryByExampleDemo .java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
package com.j2eereference.hibernate.queryByExample; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.cfg.AnnotationConfiguration; import org.hibernate.criterion.Example; public class QueryByExampleDemo { public static void main(String[] args) { AnnotationConfiguration config = new AnnotationConfiguration(); config.addAnnotatedClass(Employee.class); SessionFactory sessionFactory = config.configure().buildSessionFactory(); Session session = sessionFactory.openSession(); Employee empExample=new Employee(); empExample.setEmp_name("Alex"); Example example= Example.create(empExample); Criteria crtiteria=session.createCriteria(Employee.class).add(example); List empList=crtiteria.list(); for(Employee employee:empList){ System.out.println("Emp Name is "+employee.getEmp_name()); } } } |
output:
Hibernate: select this_.emp_id as emp1_0_0_, this_.emp_name as emp2_0_0_ from employee this_ where (this_.emp_name=?)
Emp Name is Alex.
Query by Example : excludeProperty
It is possible to exclude properties also as below. We can have as many exclude property as we need.
1 |
Example example= Example.create(empExample).excludeProperty("emp_age"); |
Query by Example :enableLike
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
package com.j2eereference.hibernate.queryByExample; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.cfg.AnnotationConfiguration; import org.hibernate.criterion.Example; public class QueryByExampleDemo { public static void main(String[] args) { AnnotationConfiguration config = new AnnotationConfiguration(); config.addAnnotatedClass(Employee.class); SessionFactory sessionFactory = config.configure().buildSessionFactory(); Session session = sessionFactory.openSession(); Employee empExample=new Employee(); empExample.setEmp_name("A%"); Example example= Example.create(empExample).enableLike(); Criteria crtiteria=session.createCriteria(Employee.class).add(example); List empList=crtiteria.list(); for(Employee employee:empList){ System.out.println("Emp Name is "+employee.getEmp_name()); } } } |
output:
Hibernate: select this_.emp_id as emp1_0_0_, this_.emp_name as emp2_0_0_ from employee this_ where (this_.emp_name like ?)
Emp Name is Alex
Conclusion:
Query by Example is useful if you have lot of properties to set for your query.
Query by Example is not applicable for Primary key column, So if you give primary key column for QueryByExample it is going to return all the records.
Query by Example is not applicable for column with null value.
Leave a Reply