Search code examples
javahibernatehibernate-criteria

hibernate criteria group Query is wrong

I am trying to generate the following query using Criteria:

select order_number, order_date, sum(order_amount)
from st_pur_orders
group by ORDER_NUMBER,ORDER_DATE;

Here is my Criteria:

  Criteria cr = session.createCriteria(StPurOrders.class);
            cr.setProjection(Projections.projectionList()
                    .add(Projections.property("orderNumber"), "orderDate")
                    .add(Projections.sum("orderAmount"))
                    .add(Projections.groupProperty("orderNumber"))
                    .add(Projections.groupProperty("orderDate")));
            cr.setResultTransformer(Transformers.aliasToBean(PurOrderColl.class));
            list = cr.list();

But the query getting generate in the background is this:

select this_.ORDER_NUMBER as y0_, sum(this_.ORDER_AMOUNT) as y1_,
    this_.ORDER_NUMBER as y2_, this_.ORDER_DATE as y3_
from STOCK.ST_PUR_ORDERS this_
group by this_.ORDER_NUMBER, this_.ORDER_DATE;

My Question is why is the ORDER_NUMBER field being listed twice?

Solution

  • My Question is why is the ORDER_NUMBER field being listed twice?

    Your Criteria already has Projections.groupProperty("orderNumber"). So the generated query will include orderNumber in its select clause.

    I don't see a need for specifying Projections.property("orderNumber") explicitly. Can you please remove that and try.

    Same applies for any other field that is specified using groupProperty(..).