CODEWITHSUNDEEP
javahibernate
anoop
anoop

Reputation: 1614

How to implement sum of a field query in Hibernate?

How can we implement the Hibernate sqlprojection in my query?

Here is my query

SELECT sum(total_amount) as total,created_at from order where created_at < DATE_SUB(curdate(), INTERVAL 7 DAY) and doctor_id = 193 GROUP BY created_at

I have implement DATE_SUB function using sqlRestriction like this:

String sqlWhere = "created_at > DATE_SUB(curdate(), INTERVAL "+activityGraph+" DAY) AND doctor_id = "+id +" GROUP BY created_at";
Criteria criteria = Hibernatesession.createCriteria(Order.class);
criteria.add(Restrictions.sqlRestriction(sqlWhere));

But I don't know how I get the sum of a field using Hibernate query.

I found out that setProjection in Hibernate is used to get the sum as we desired but I don't know how to use it. Also here I want to use sqlRestriction to write WHERE condition for date_sub function.
So I will use setProjection and sqlRestriction in a single query.

Upvotes: 1

Views: 16509

Answers (3)

Avijit Bairagi
Avijit Bairagi

Reputation: 1

public int getSum() {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();
    Criteria criteria = session.createCriteria(Product.class);
    criteria.setProjection(Projections.sum("productCount"));
    List list = criteria.list();
    session.getTransaction().commit();
    return (int) list.get(0);
}

//hibernate mpping

<property name="productCount" type="int" column="PRODUCT_COUNT"/>

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30819

criteria.setProjection((Projections.sum("/* name of the mapping variable for total_amount*/")));

Upvotes: 2

JB Nizet
JB Nizet

Reputation: 691715

You're making you life difficult. Why don't you simply compute the date limit in Java before executing the query?

Date today = DateUtils.truncate(new Date(), Calendar.DATE);
Date limit = DateUtils.addDays(today, -7);

And since the query is completely static, why using the Criteria API. HQL is much easier:

String hql = "SELECT sum(o.totalAmount) as total, o.createdAt from Order o"
             + " where o.createdAt < :limit"
             + " and o.doctor.id = 193"
             + " group by o.createdAt";

Upvotes: 3

Related Questions