from http://forum.hibernate.org/viewtopic.php?t=929934


I. Does createSqlQuery offer functionality that createQuery does not?

This query returns what I need, in sql terms (ids where
objects are needed, values where values are needed):
II.
1. Can this SQL query be used in createSqlQuery to
return objects and properties(or NULL when there is no t4.column_h) ?

2. If not, how do you correctly
create objects from the ids and values you've returned from SQL?

i.e. I want to create a new object with:

an association whose id corresponds to t2.column_c,
a property calculated from t1.column_g within the application,
and a property t1.column_h?

I could create the database values that would form the new objects
in a stored procedure, and then load them via HQL, but the calculations
are significant business logic, and I don't like the idea of duplicating the
code in the db layer as well as the application layer - seems like asking for trouble.

Is there a strategy for loading from SQL, building objects manually,
and saving the to the session when you can't do it through HQL?

Thank you for your time reading this question.

OK, perhaps that is a bit confusing. So I'll rephrase the question:
I'd like to perform a left join where the join is not on an id column.

A simple example where this would be useful is a self-joined table used to
implement a tree:

This is used to represent various points in a supply chain, where one level's price is another level's cost, and every price has a cost. (or put differently, every item that will be sold must be bought first). Now consider a separate table used to represent prices (read-only):

And one that represents potental prices (r/w):


Finally, you'd like to create a set of proposed prices for given items at a given price_level, but the catch is, you'd like to populate the current price where it exists, and 0 or null for current price where it does not. This implies that you need proposed costs for the items as well as current prices.

This is straightforward using SQL, but I'm not sure how to do it in HQL, or if it is even possible (the previous linked thread says it is not).

So is there a way to create these objects from SQL, perform some business logic on them, and store them back through ?
Or is it possible to use createSQLQuery to load the objects themselves?
(Item, PriceLevel, ProposedCost, CurrentPrice)?

Thanks for your help.

Ok, so I've implemented this as a hybrid solution. The SQL is used to return a JDBC resultset in the DAO, and a callback to the business object is used to create the object given the raw values. The new objects are saved in the session using save.

This provides a hook for calculating the new values without loading unnecessary objects or executing multiple queries per new object. The drawback is that this leads to tight coupling - however, the parameter list
of the callback forms a contract, I suppose you know what you can do
with the lightweight versions of the object because you create them yourself.
i.e.