Selecting columns from multiple tables in hibernate when using aggregrate function

Question

I have following schema

Table: Product Columns : Product_ID Product_Name

Table: Sales_Detail Columns : Sales_Detail_ID Product_ID Quantity Total_Sales .. ..

What I want to do is to show the daily sales using the following query. SELECT Prouduct_ID, SUM(sd.Quantity), SUM(sd.Selling_Rate) FROM SalesDetail sd WHERE DATE(Date_Time)=CURDATE() GROUP BY Product_ID

And get the product name and product id from the result. How do I do that. I went through resources in the internet but non could help me to come to a correct way of doing it.

score 0 · Answer 1 · edited May 23 '17 at 12:07

I actually don't have Hibernate to test it but this HQL query might be a good start :

SELECT
  sd.Product_ID,
  p.Product_Name,
  SUM(sd.Quantity),
  SUM(sd.Selling_Rate)
FROM
  SalesDetail sd
INNER JOIN Product p 
WHERE
  p.Product_ID = sd.Product_ID AND
  sd.Date_Time > CURRENT_DATE()
GROUP BY
  sd.Product_ID, p.Product_Name

I don't know where Date_Time is coming from but I'm guessing the table SalesDetail.

I am not certain than DATE(Date_Time) will work so I used sd.Date_Time > CURRENT_DATE() but you should probably calculate the time range out of the HQL and instead use something like this sd.Date_Time BETWEEN :dateTimeStart AND :dateTimeEnd. See HQL to query records between two dates for more information on how to bind date parameters.

score 0 · Answer 2 · answered Dec 22 '15 at 13:23

Okay this is what I have done which solved my problem. It was actually easy but tricky one.

The main focus is on the SELECT query. Here productHeader is the object of ProductHeader class which refers to product_header named table in the database. Product_ID the primary key of product_header table is selected as productHeader.Product_ID i.e (PRODUCT_HEADER_OBJECT.PRIMARY_KEY_ATTRIBUTE)

        Session session = null;
        try{
            session = sessionFactory.openSession();
            session.beginTransaction();

            /*Query to get the daily sales report. This query will provide current date as input and sum the total product quanitity sold and total sales selling rate*/
            Query query = session.createQuery("SELECT productHeader.Product_ID, SUM(s.Quantity) as Quantity, SUM(Total) FROM SalesDetail s WHERE DATE(Date_Time)=CURDATE() GROUP BY Product_ID");

            List<Object[]> groupList = query.list();
            session.getTransaction().commit();
            session.close();
            ProductHeader productHeader = new ProductHeader();

            for(Object[] arr : groupList){
                productHeader =null;
                String productID = (String) arr[0]; //the first index in the object array list is productID 
                                                    //as it is the first one defined in the HQL query above
                double quantity = (double) arr[1];
                double totalSales = (double)arr[2];

                session = sessionFactory.openSession();
                session.beginTransaction();

                /*Getting the product header class by passing primary key value to session.get() method*/
                productHeader = (ProductHeader) session.get(ProductHeader.class, productID);
                String productName = productHeader.getProduct_Name();


                DailySales dailySales= new DailySales();
                dailySales.setSn(billSN);
                dailySales.setProductID(productID);
                dailySales.setPrductName(productName);
                dailySales.setQuantitySold(quantity);
                dailySales.setSalesAmount(totalSales);
                data.add(dailySales);

                tblDailySales.setItems(data);
                System.out.println("From object parsing "+Arrays.toString(arr));
            }

        } catch (Exception e) {
            System.out.println("Error while populating daily sales table : " + e.getMessage());
        }

Selecting columns from multiple tables in hibernate when using aggregrate function

2 Answers2