Scalar SQL Average : HQL Select « Hibernate « Java





Scalar SQL Average

/////////////////////////////////////////////////////////////////////////
import java.util.*;

import java.sql.*;
import org.hibernate.*;
import org.hibernate.criterion.*;

public class Main {
  
  public static void main(String[] args) {
    HibernateUtil.setup("create table Supplier ( id int, name VARCHAR);");
    HibernateUtil.setup("create table Product ( id int, name VARCHAR, description VARCHAR, price double,supplierId int);");
    
    prepareData();
    Session session = HibernateUtil.currentSession();
    
        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();
        displayObjectsList(results);
    
    
        HibernateUtil.checkData("select * from Supplier");
        HibernateUtil.checkData("select * from Product");

  }
    static public void displayObjectsList(List list)
    {
        Iterator iter = list.iterator();
        if (!iter.hasNext())
        {
            System.out.println("No objects to display.");
            return;
        }
        while (iter.hasNext())
        {
            System.out.println("New object");
            Object obj = (Object) iter.next();
            System.out.println(obj);
            
            
        }
    }       


  private static void prepareData(){
        Session session = HibernateUtil.currentSession();

        Supplier supplier1 = new Supplier();
        supplier1.setName("Supplier Name 1");
        session.save(supplier1);
        
        Supplier supplier2 = new Supplier();
        supplier2.setName("Supplier Name 2");
        session.save(supplier2);        
        
        Product product1 = new Product("Product 1","Name for Product 1", 2.0);
        product1.setSupplier(supplier1);
        supplier1.getProducts().add(product1);
        session.save(product1);
        
        Product product12 = new Product("Product 2","Name for Product 2", 22.0);
        product12.setSupplier(supplier1);
        supplier1.getProducts().add(product12);        
        session.save(product12);
        
        Product product2 = new Product("Product 3", "Name for Product 3", 30.0);
        product2.setSupplier(supplier2);
        supplier2.getProducts().add(product2);
        session.save(product2);
        
        session.flush();
        HibernateUtil.closeSession();
  }
}




/////////////////////////////////////////////////////////////////////////
public class Product
{
    private int id;
    private Supplier supplier;
    
    private String name;
    private String description;
    private double price;
    
    public Product()
    {
        super();
    }
    
    public Product(String name, String description, double price)
    {
        super();
        this.name = name;
        this.description = description;
        this.price = price;
    }
    
    public String getDescription()
    {
        return description;
    }
    public void setDescription(String description)
    {
        this.description = description;
    }
    public int getId()
    {
        return id;
    }
    public void setId(int id)
    {
        this.id = id;
    }
    public String getName()
    {
        return name;
    }
    public void setName(String name)
    {
        this.name = name;
    }
 
    public Supplier getSupplier()
    {
        return supplier;
    }
    public void setSupplier(Supplier supplier)
    {
        this.supplier = supplier;
    }
    
    public double getPrice()
    {
        return price;
    }
    public void setPrice(double price)
    {
        this.price = price;
    }
}


           
       










HibernateHQLScalarSQLAverage.zip( 3,692 k)

Related examples in the same category

1.Unique Result HQL
2.Select SQL
3.HQL: Paging
4.HQL: Projection
5.HQL: Order
6.HQL: Order Two Properties