Java Tutorial/Hibernate/Projections

AVG Projection

File: Main.java

import java.util.Date;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
public class Main {
  public static void main(String[] args) throws Exception {
    HibernateUtil hibernateUtil = new HibernateUtil();
    hibernateUtil
        .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
    Session session = hibernateUtil.getSession();
    Survey survey = new Survey();
    survey.setName("Survey");
    survey.setPurchaseDate(new Date());
    session.save(survey);
    
    survey = new Survey();
    survey.setName("Survey1");
    survey.setPurchaseDate(new Date());    
    session.save(survey);
    
    session.flush();
    
    Criteria crit = session.createCriteria(Survey.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.avg("Id"));
    crit.setProjection(projList);
    
    
    List results = crit.list();
    System.out.println(results);
    
    session.close();
    hibernateUtil.checkData("select * from survey");
  }
}

Group Field

File: Main.java

import java.util.Date;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
public class Main {
  public static void main(String[] args) throws Exception {
    HibernateUtil hibernateUtil = new HibernateUtil();
    hibernateUtil
        .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
    Session session = hibernateUtil.getSession();
    Survey survey = new Survey();
    survey.setName("Survey");
    survey.setPurchaseDate(new Date());
    session.save(survey);
    
    survey = new Survey();
    survey.setName("Survey1");
    survey.setPurchaseDate(new Date());    
    session.save(survey);
    
    session.flush();
    
    Criteria crit = session.createCriteria(Survey.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.groupProperty("id"));
    projList.add(Projections.property("id"));
    crit.setProjection(projList);
    
    
    List results = crit.list();
    System.out.println(results);
    
    session.close();
    hibernateUtil.checkData("select * from survey");
  }
}

Projection: Count Distinct

File: Main.java

import java.util.Date;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
public class Main {
  public static void main(String[] args) throws Exception {
    HibernateUtil hibernateUtil = new HibernateUtil();
    hibernateUtil
        .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
    Session session = hibernateUtil.getSession();
    Survey survey = new Survey();
    survey.setName("Survey");
    survey.setPurchaseDate(new Date());
    session.save(survey);
    
    survey = new Survey();
    survey.setName("Survey1");
    survey.setPurchaseDate(new Date());    
    session.save(survey);
    
    session.flush();
    
    Criteria crit = session.createCriteria(Survey.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.countDistinct("Id"));
    crit.setProjection(projList);
    
    
    List results = crit.list();
    System.out.println(results);
    
    session.close();
    hibernateUtil.checkData("select * from survey");
  }
}

Projection: Max

File: Main.java

import java.util.Date;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
public class Main {
  public static void main(String[] args) throws Exception {
    HibernateUtil hibernateUtil = new HibernateUtil();
    hibernateUtil
        .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
    Session session = hibernateUtil.getSession();
    Survey survey = new Survey();
    survey.setName("Survey");
    survey.setPurchaseDate(new Date());
    session.save(survey);
    
    survey = new Survey();
    survey.setName("Survey1");
    survey.setPurchaseDate(new Date());    
    session.save(survey);
    
    session.flush();
    
    Criteria crit = session.createCriteria(Survey.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.max("Id"));
    crit.setProjection(projList);
    
    
    List results = crit.list();
    System.out.println(results);
    
    session.close();
    hibernateUtil.checkData("select * from survey");
  }
}

Projection: Min

File: Main.java

import java.util.Date;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
public class Main {
  public static void main(String[] args) throws Exception {
    HibernateUtil hibernateUtil = new HibernateUtil();
    hibernateUtil
        .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
    Session session = hibernateUtil.getSession();
    Survey survey = new Survey();
    survey.setName("Survey");
    survey.setPurchaseDate(new Date());
    session.save(survey);
    
    survey = new Survey();
    survey.setName("Survey1");
    survey.setPurchaseDate(new Date());    
    session.save(survey);
    
    session.flush();
    
    Criteria crit = session.createCriteria(Survey.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.min("Id"));
    crit.setProjection(projList);
    
    
    List results = crit.list();
    System.out.println(results);
    
    session.close();
    hibernateUtil.checkData("select * from survey");
  }
}

Scalar SQL Query

File: Main.java

import java.util.List;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
public class Main {
  public static void main(String[] args) throws Exception {
    HibernateUtil hibernateUtil = new HibernateUtil();
    hibernateUtil
        .executeSQLCommand("create table Product(id int, name varchar, description varchar, price decimal(6,2), supplierid int)");
     
    hibernateUtil
    .executeSQLCommand("create table Supplier (id int , name varchar)");
    hibernateUtil
    .executeSQLCommand("create table Software(id int, name varchar, description varchar, price decimal(6,2), supplierid int, version varchar)");
    
    Session session = hibernateUtil.getSession();
    Supplier superCorp = new Supplier();
    superCorp.setName("Supplier1");
    session.save(superCorp);
    
    Supplier megaInc = new Supplier();
    megaInc.setName("Supplier2");
    session.save(megaInc);        
    Product mouse = new Product("Product1","first product", 20.0);
    mouse.setSupplier(superCorp);
    superCorp.getProducts().add(mouse);
    session.flush();
    Product mouse2 = new Product("Product2","second product", 22.0);
    mouse2.setSupplier(superCorp);
    superCorp.getProducts().add(mouse2);        
    
    Product keyboard = new Product("Product3", "third product", 30.0);
    keyboard.setSupplier(megaInc);
    megaInc.getProducts().add(keyboard);
    Software webBrowser = new Software("Web Browser","new browser", 75.0, "2.0");
    webBrowser.setSupplier(superCorp);
    superCorp.getProducts().add(webBrowser);
    Software email = new Software("Email","email client", 49.99, "4.1 Edition");
    email.setSupplier(megaInc);
    megaInc.getProducts().add(email);    
    session.flush();
    
    
    
    String sql = "select avg(product.price) as avgPrice from Product product";
    
    SQLQuery query = session.createSQLQuery(sql);
    query.addScalar("avgPrice",Hibernate.DOUBLE);
    List results = query.list();
    System.out.println(results);
    
    
    
    session.close();
    hibernateUtil.checkData("select * from Product");
    hibernateUtil.checkData("select * from Software");
    hibernateUtil.checkData("select * from Supplier");
  }
}

Select Specific Fields in a Entity with Criteria and Projections

File: Main.java

import java.util.Date;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
public class Main {
  public static void main(String[] args) throws Exception {
    HibernateUtil hibernateUtil = new HibernateUtil();
    hibernateUtil
        .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
    Session session = hibernateUtil.getSession();
    Survey survey = new Survey();
    survey.setName("Survey");
    survey.setPurchaseDate(new Date());
    session.save(survey);
    
    survey = new Survey();
    survey.setName("Survey1");
    survey.setPurchaseDate(new Date());    
    session.save(survey);
    
    session.flush();
    
    Criteria crit = session.createCriteria(Survey.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.property("Id"));
    projList.add(Projections.property("name"));
    crit.setProjection(projList);
    
    
    List results = crit.list();
    System.out.println(results);
    
    session.close();
    hibernateUtil.checkData("select * from survey");
  }
}