public void AverageRatingByProductName(String prodName) {
    SessionFactory sf = HibernateUtility.getSessionfactory();

    Session session = sf.openSession();

    session.beginTransaction();
    String sql1 = "SELECT * FROM product where productName = :productName";
    SQLQuery query1 = session.createSQLQuery(sql1);
    query1.setParameter("productName", prodName);
    query1.addEntity(Product.class);
    Product product = (Product) query1.uniqueResult();

    String sql2 = "SELECT * FROM ratingdetails where productId = :productId";
    SQLQuery query2 = session.createSQLQuery(sql2);
    query2.setParameter("productId", product.getProductId());
    query2.addEntity(RatingDetails.class);

    List ratings = query2.list();
    session.getTransaction().commit();
    float count = ratings.size();

    int sum = 0;
    for (Object r : ratings) {
      RatingDetails rate = (RatingDetails) r;
      sum = sum + rate.getRating();
    }
    float averageRating = (sum / count);

    System.out.println("pid " + product + " avg " + averageRating);
  }
  public void deleteReview(Review review) {
    SessionFactory sf = HibernateUtility.getSessionfactory();

    Session session = sf.openSession();

    session.beginTransaction();
    session.delete(review);
  }
  public void updateReview(Review review) {
    SessionFactory sf = HibernateUtility.getSessionfactory();

    Session session = sf.openSession();
    session.beginTransaction();
    session.update(review);
    session.getTransaction().commit();
  }
  public void insertReview(Review review) {
    SessionFactory sf = HibernateUtility.getSessionfactory();

    Session session = sf.openSession();

    session.beginTransaction();

    session.save(review);

    session.getTransaction().commit();
    System.out.println("in review");
  }
  public int getReviewById(int productId) {
    SessionFactory sf = HibernateUtility.getSessionfactory();

    Session session = sf.openSession();
    session.beginTransaction();
    String sql = "SELECT * FROM review where product_productId = :pId";
    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(Review.class);
    query.setParameter("pId", productId);
    int count = query.list().size();
    session.getTransaction().commit();
    // System.out.println(catobj.getCategoryId()+catobj.getCategoryName());
    return count;
  }
  public List<Review> getReviewListbyProductId(int productId) {
    SessionFactory sf = HibernateUtility.getSessionfactory();

    Session session = sf.openSession();
    session.beginTransaction();
    String sql = "SELECT * FROM review where product_productId = :pId";
    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(Review.class);
    query.setParameter("pId", productId);
    System.out.println(query.list().size());
    List<Review> reviewlist = query.list();
    session.getTransaction().commit();

    return reviewlist;
  }
  public void calulateAverageRating() {

    SessionFactory sf = HibernateUtility.getSessionfactory();

    Session session = sf.openSession();

    session.beginTransaction();
    String sql = "SELECT * FROM product";
    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(Product.class);
    List listProductIds = query.list();
    for (Object pro : listProductIds) {
      Product p = (Product) pro;

      String sql1 = "SELECT * FROM ratingdetails where productId = :productId";
      SQLQuery query1 = session.createSQLQuery(sql1);
      query1.setParameter("productId", p.getProductId());
      query1.addEntity(RatingDetails.class);

      List ratings = query1.list();
      session.getTransaction().commit();
      float count = ratings.size();

      int sum = 0;
      for (Object r : ratings) {
        RatingDetails rate = (RatingDetails) r;
        sum = sum + rate.getRating();
      }
      float averageRating = (sum / count);

      avgProdList.put(p, averageRating);
      System.out.println("pid " + p.getProductId() + " avg " + averageRating);
    }
    System.out.println(avgProdList);

    System.out.println();
    sortAverageRating();
    /*for (Iterator iterator = listProductIds.iterator(); iterator.hasNext();) {
    	RatingDetails ratingobj = (RatingDetails) iterator.next();
    	System.out.print("First Name: " + employee.getFirstName());
    	System.out.print("  Last Name: " + employee.getLastName());
    	System.out.println("  Salary: " + employee.getSalary());
    }*/
  }