CODEWITHSUNDEEP
javahibernate
Maurice Perry
Maurice Perry

Reputation: 32831

Expressions in hibernate criteria

Let's say I have a persistent class Item with a quantity field and a price field. Is there a way to build a Criteria that calculates the sum of quantity*price?

Upvotes: 6

Views: 12894

Answers (3)

Ori
Ori

Reputation: 12830

I think you can also use an SQL projection. It should be something like:

session.createCriteria(Item.class) 
        .createAlias("item", "i") 
        .setProjection( Projections.projectionList() 
            .add( Projections.groupProperty("i.id") ) 
            .add( Projections.groupProperty("i.price") ) 
            .add( Projections.groupProperty("i.quantity") ) 
            .add( Projections.sqlProjection( 
                    "price * quantity as total", 
                    new String[] { "total" }, 
                    new Type[] { Hibernate.DOUBLE } 
                  ) 
            ) 
        );

Ori

Upvotes: 9

Matej
Matej

Reputation: 6199

It is (probably) not possible to do it with Criteria. But HQL can be helpful for this. 

SELECT ent.quantity*ent.price from EntityName as ent WHERE ent.id = ?

Upvotes: 1

Ori
Ori

Reputation: 12830

It's not exactly what you asked for, but you can use "derived properties" to get something rather similar.

For example, you can map a totalPrice property to an SQL expression: 

<property name="totalPrice" formula="quantity * price" type="big_decimal"/>

The SQL formula "quantity * price" is evaluated every time the entity is retrieved from the database.

Ori

The Hibernate docs contain more info about this.

Upvotes: 1

Related Questions