7

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?

nimcap
  • 10,062
  • 15
  • 61
  • 69
  • 1
    You will NOT be able to make `session.createCriteria(TotalSales.class)` work via custom loader. You cannot (nor should you) map it as entity without having a backing object (table or view) in the database. – ChssPly76 Sep 18 '09 at 07:13

4 Answers4

19

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.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
  • Because I want to use the new entity like `session.createCriteria(TotalSale.class).list()` – nimcap Sep 11 '09 at 12:12
  • Add added a section to my answer for criteria. – Stefan Steinegger Sep 11 '09 at 12:40
  • the answers you gave are very nice indeed, +1 for it but it is not quite what I want – nimcap Sep 11 '09 at 12:56
  • What happens when I call `session.createCriteria(Sale.class).list()` or 'session.createQuery('from Sale').list()' ? Hibernate knows how to map a table to an entity so it fetches all the records in `sales` table. But because there is no table named `total_sales` it does not know how to fetch and map the fields when I use `session.createCriteria(TotalSale.class).list()`. I want hibernate to know to map a *query* to an entity. Like it was done here: http://mikedesjardins.us/wordpress/2008/06/use-hibernates-custom-loaders-to-fake/ – nimcap Sep 11 '09 at 14:01
  • Yes, I understand that you wan to to do this. The question is: why? What problem do you need to solve? If you get entities by a query, you can't usually update it anyway. So it does not matter if you get it using a query in code. But you won't go through all these troubles to make custom loading of an entity work. – Stefan Steinegger Sep 11 '09 at 22:28
  • I do not want to update those entities anyway. I need that because that represents my domain better. – nimcap Sep 13 '09 at 16:57
  • If TotalSale is read-only, and not referenced from somewhere, I would fetch it with a query. IMO, it's just not worth the effort to make createCriteria(TotalSale.class) work. – Stefan Steinegger Sep 18 '09 at 06:50
0

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).
KLE
  • 23,689
  • 4
  • 56
  • 62
0

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.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
0

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.

Jaguar
  • 5,929
  • 34
  • 48
  • This is not going to work unless the "Seller" table exists AND is not mapped to any other entity. And if that was the case, the whole point is moot. – ChssPly76 Sep 18 '09 at 17:20
  • actually i have a crap-top of mistakes in the post, the only reason i posted is maybe the topic-starter gets an idea or provides some information on what he really needs. otherwise stefan's answer seems proper – Jaguar Sep 20 '09 at 12:51