These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: Stored Procedure with Join
PostPosted: Fri Nov 02, 2007 11:17 am 
Newbie

Joined: Fri Nov 02, 2007 11:08 am
Posts: 6
So I have two entities, ProductPrice and Currency. ProductPrice has a Currency property.

I'm able to map a stored procedure to populate a list of ProductPrice entities. The problem is that during when I'm databinding, I need to display the Currency.Name property. So I have the Select N+1 problem: for each ProductPrice entity, I'm executing an additional query to get the associated Currency entity.

My stored procedure does a join with the Currency table, but I can't figure out how to get NHibernate to use the extra columns to prepopulate the Currency object. I've used FetchMode.Join with the Criteria API, but that doesn't apply to a stored proc. I've read about aliasing, but that too doesn't apply here.

Thanks in advance.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 02, 2007 12:16 pm 
Senior
Senior

Joined: Thu Feb 09, 2006 1:30 pm
Posts: 172
Two options immediately come to me. If neither of these work for you then I'm not sure I can really be of much help.

First, do you really need to use stored procedures? I assume you do this because it is a company policy? The application will perform better if you allow it to write the dynamic sql. Development time would also be greatly reduced. I realize this may not be an option.

Second, you could use a "reporting object" to encapsulate just the fields you want to return. So make a new object which is named something like ProductPriceList which has a child Price property and child CurrencyPrice property. (Of course you would also include any other columns you needed). This will then let you use just one query to populate all values, but if you ever needed to modify the price or currency you would still need to do a load from the database for that record. The "reporting object" would not be an object which mapped directly to any table, it would just be used to return the results of the stored procedure.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 02, 2007 12:32 pm 
Hibernate Team
Hibernate Team

Joined: Tue Jun 13, 2006 11:29 pm
Posts: 315
Location: Calgary, Alberta, Canada
Ditto on jchapman's points. In addition, it sounds like the Currency objects are relatively static. If that is the case, employing the second-level cache would also be a viable option.

_________________
Karl Chu


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 02, 2007 2:29 pm 
Newbie

Joined: Fri Nov 02, 2007 11:08 am
Posts: 6
jchapman wrote:
First, do you really need to use stored procedures? I assume you do this because it is a company policy? The application will perform better if you allow it to write the dynamic sql. Development time would also be greatly reduced. I realize this may not be an option.


Yes, our standard is to use stored procs, but I have relaxed that requirement a bit by letting NHibernate generate the query when it is a simple fetch, or a simple join on a foreign key. This particular query is a little more involved so I prefer to implement it as a stored proc.

jchapman wrote:
Second, you could use a "reporting object" to encapsulate just the fields you want to return. So make a new object which is named something like ProductPriceList which has a child Price property and child CurrencyPrice property. (Of course you would also include any other columns you needed). This will then let you use just one query to populate all values, but if you ever needed to modify the price or currency you would still need to do a load from the database for that record. The "reporting object" would not be an object which mapped directly to any table, it would just be used to return the results of the stored procedure.


The ProductPrice object already has the schema that I want to use. I could create another object that flattens the ProductPrice and Currency fields into a single object, but this is ugly.

My stored procedure returns the following fields:
ProductID
CurrencyCode
CurrencyName
CurrencySymbol
CurrencyUsdValue
Price

Can I not group the Currency* fields into a Currency object? Can I map them to a Currency component?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 02, 2007 2:41 pm 
Senior
Senior

Joined: Thu Feb 09, 2006 1:30 pm
Posts: 172
rmuti wrote:
Yes, our standard is to use stored procs, but I have relaxed that requirement a bit by letting NHibernate generate the query when it is a simple fetch, or a simple join on a foreign key. This particular query is a little more involved so I prefer to implement it as a stored proc.


Honestly, I would recommend learning the new technology. HQL is a very powerful query language. If possible write the query that way.

rmuti wrote:
The ProductPrice object already has the schema that I want to use. I could create another object that flattens the ProductPrice and Currency fields into a single object, but this is ugly.


I don't think this approach is ugly at all. If you really do find it ugly you could do the really ugly thing and just return an array of objects. You don't have to wrap it in a type if you don't want to. The approach we are describing with the "reporting objects" is very similar to what Microsoft will recommend with Linq to Sql, except Linq to Sql will use the new anonymous type feature of .NET 3.5 so it will create the new type for you.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 02, 2007 2:51 pm 
Newbie

Joined: Fri Nov 02, 2007 11:08 am
Posts: 6
jchapman wrote:
Honestly, I would recommend learning the new technology. HQL is a very powerful query language. If possible write the query that way.


I cannot use HQL. This code will have to be maintained by others in the organization and I cannot expect them to learn a proprietary query language.

jchapman wrote:
I don't think this approach is ugly at all. If you really do find it ugly you could do the really ugly thing and just return an array of objects. You don't have to wrap it in a type if you don't want to. The approach we are describing with the "reporting objects" is very similar to what Microsoft will recommend with Linq to Sql, except Linq to Sql will use the new anonymous type feature of .NET 3.5 so it will create the new type for you.


Flattening the object graph makes it more relational and less OO. My object shouldn't have CurrencyCode, CurrencyName, CurrencySymbol fields; that's why I have a Currency object. In Linq you can map the result set columns to properties and sub-properties of child objects. Can I not achieve this with an NHibernate <sql-query> mapping? Isn't that what components are for?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 02, 2007 3:05 pm 
Senior
Senior

Joined: Thu Feb 09, 2006 1:30 pm
Posts: 172
rmuti wrote:
Flattening the object graph makes it more relational and less OO. My object shouldn't have CurrencyCode, CurrencyName, CurrencySymbol fields; that's why I have a Currency object. In Linq you can map the result set columns to properties and sub-properties of child objects. Can I not achieve this with an NHibernate <sql-query> mapping? Isn't that what components are for?


Ok, I disagree regarding the OO statement, but that is ok. I don't feel that data binding is a very OO style concept. Business logic is a different story and you would never use the "reporting object" for anything besides, well, reporting.

If those ideas don't work, take the other suggestion which karlchu said. Add a second level cache. How many unique currencies are there?

Plus if all prices are the same currency it won't load it n times, it'll just load it once. Are we trying to solve a problem before we know it exists?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 02, 2007 3:23 pm 
Newbie

Joined: Fri Nov 02, 2007 11:08 am
Posts: 6
jchapman wrote:
If those ideas don't work, take the other suggestion which karlchu said. Add a second level cache. How many unique currencies are there?

Plus if all prices are the same currency it won't load it n times, it'll just load it once. Are we trying to solve a problem before we know it exists?


I'm loading the price list for a single product in the various currencies, so it's not the same currency. Caching is an option, as there aren't that many currencies, but it's unnecessary because if I were using a DataSet for example I'd just do a join and be done with it. Of course this works because the table is a flat object, and I can do a similar thing with NHibernate. But to me the OO approach would be to encapsulate all the currency information in my Currency object, and I'm thinking that there must be a way to do this. Otherwise, a stored proc in NHibernate can return only a single, flat entity?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.