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?

Improve this question
1
  • 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. Commented Sep 18, 2009 at 7:13

4 Answers 4

Reset to default
19
+50

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Because I want to use the new entity like session.createCriteria(TotalSale.class).list()
Add added a section to my answer for criteria.
the answers you gave are very nice indeed, +1 for it but it is not quite what I want
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: mikedesjardins.us/wordpress/2008/06/…
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.
|
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).
Improve this answer

Comments

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.

Improve this answer

2 Comments

This is not going to help with session.createCriteria() in any way.
I actually doubt that createCriteria is strongly needed in this case, so I propose alternative approaches.
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.

Improve this answer

2 Comments

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.
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.