Map entity using query in Hibernate

Question

consider table

sales (id, seller_id, amount, date)

and here is a view that is generated from sales using query SELECT seller_id, SUM(amount) FROM sales GROUP BY seller_id

total_sales (seller_id, amount)

I want to make an entity for total sales but without the view on the sql side.

This entity will be constructed from a query. The closest thing I found is this, but I could not make it work.

Even if I define the loader, hibernate looks for the entity's table and gives an error if it cannot find it. If I create the table it does not load the entity from the named query I defined, Hibernate generates the query itself.

Is there a way to make @Loader to work or is there another way that I can map a query to entity?

Answer 1

Why don't you just use new in the query?

select new TotalSales(seller_id, count(seller_id))
from sales
group by seller_id

You just write a class TotalSales with a constructor taking the seller_id and an integer.

---

Edit: When using criteria API, you can use the AliasToBeanResultTransformer (See API docs). It copies every alias name to a property of the same name.

 List list = s.createCriteria(Sales.class)
  .setProjection(Projections.projectionList()
    .add( Projections.property("id"), "SellerId" )
    .add( Projections.rowCount("id"), "Count" ) )
  .setResultTransformer( 
    new AliasToBeanResultTransformer(TotalSales.class) )
  .list();

Then your TotalSales needs a SellerId and Count property.

Answer 2

In addition to Stefan's answer, you could also use an explicit HQL query to

    SELECT seller_id, SUM(amount) FROM sales GROUP BY seller_id

The result is naturally stored in List. If this datatype is not convenient for you, you could:

• create new TotalSale objects with them (use Stefan's answer would be better probably)
• create a Map to store the data (you can also embed this directly into the request).

Answer 3

You may try to define a customized loader. I never used this, but it seems to be reasonable:

<sql-query name="totalSale">
    <return alias="ts" class="TotalSale" />
    SELECT seller_id as {ts.seller_id}, SUM(amount) as ts.amount 
    FROM sales 
    WHERE seller_id = ?
    GROUP BY seller_id
</sql-query>

Not sure if the filter on the seller_id is needed.

You reference this named query in a class mapping:

<class name="TotalSale">
    <id name="seller_id">
        <generator class="increment"/>
    </id>
    <property name="seller_id" />
    <property name="amount" />
    <loader query-ref="totalSale"/>
</class>

There are more details in the manual.

Alternatively, you can directly use a name query from the code, this way you don't need a mapping of TotalSale and don't have to write the query in the code. Named queries can also return objects. See details about named queries in the documentation.

Answer 4

if you really want a specific entity only for this job you can use a 'formula' on a custom property

<class name="SellerSales" table="Sales" lazy="true">
    <id name="SellerId" column="SellerId" type="int">
        <generator class="native" />
    </id>
    <property name="SalesSum" type="float" update="false" insert="false" 
            formula="select SUM(sal.ammount) from sales sal where sal.seller_id = SellerId)" />
</class>

public class SellerSales
{
 public int SellerId {get; set;}
 public float SalesSum {get; set;}
}

as such its accesible to the Criteria engine for order-by's, restrictions etc which i think is the reason you want to use it for.