Wednesday, 20 November 2013

Query to select single or several columns with Hibernate Criteria instead of whole table


Here i am discuss Projection in hibernate when you want to retrieve columns instead of whole table


Criteria API :-Criteria is a simplified API for retrieving entities by composing Criterion objects.
This is a very convenient approach for functionality like "search" screens where there is a variable number of conditions to be placed upon the result set.

Projection :-Projections is a factory for Projection instances. You can apply a projection to a query by calling setProjection().
Projection means "Select" clause In SQL.




Sample Code you have a Student Model Class :-

package com.javastoreroom;


import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "Student_Info")
public class Student  {

 /**
  * @author Arun
  */
 private static final long serialVersionUID = 1L;

   @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "Id", nullable = false, length = 10)
  private int Id;

  @Column(name = "studentName", nullable = false, length = 100)
  private String studentName;

  @Column(name = "classId", nullable = false, length = 10)
  private int classId;

  @Column(name = "emailAddress", nullable = false)
  private Date emailAddress;

  @Column(name = "lastModifiedBy", nullable = false, length = 10)
  private int lastModifiedBy;

  @Column(name = "address", nullable = false)
  private Date address;

 public int getId() {
  return Id;
 }

 public void setId(int id) {
  Id = id;
 }

 public String getStudentName() {
  return studentName;
 }

 public void setStudentName(String studentName) {
  this.studentName = studentName;
 }

 public int getClassId() {
  return classId;
 }

 public void setClassId(int classId) {
  this.classId = classId;
 }

 public Date getEmailAddress() {
  return emailAddress;
 }

 public void setEmailAddress(Date emailAddress) {
  this.emailAddress = emailAddress;
 }

 public int getLastModifiedBy() {
  return lastModifiedBy;
 }

 public void setLastModifiedBy(int lastModifiedBy) {
  this.lastModifiedBy = lastModifiedBy;
 }

 public Date getAddress() {
  return address;
 }

 public void setAddress(Date address) {
  this.address = address;
 }
  
}

CASE 1 . If you want to fetch only students name instead of whole table :-

@SuppressWarnings("unchecked")
 public void showName() {
  Session  session = SessionConfigration.getSessionFactory().openSession();
  try{
 
     Criteria  criteria  =session.createCriteria(Student.class);
       criteria.setProjection(Projections.property("studentName")); //Projections.property is used to retrieve specific columns
   
      List students = criteria.list();
     printAllName(students); //Iterate list to show name
   
  }catch (HibernateException e) {
   e.printStackTrace();
  }finally{
   session.close();
  }
 }

CASE 2. When you want to fetch 2,3 or several column as your need add more property in projectionlist.
projectionlist added then we would get a query like select id, studentName, address from Student :-

@SuppressWarnings("unchecked")
 public void showMoreField() {
  Session  session = SessionConfigration.getSessionFactory().openSession();
  try{
   Criteria  criteria  =session.createCriteria(Student.class);
 
   criteria.setProjection(Projections.projectionList().add(
     Projections.property("studentName")).add(Projections.property("address"))); 
     criteria.addOrder(Order.desc("studentName"));
 
      List students = criteria.list();
     printAllName(students); //Iterate list to show student name , address etc.
   
  }catch (HibernateException e) {
   e.printStackTrace();
  }finally{
   session.close();
  }
 
 }

To know more see Hibernate Doc
done :)