Hibernate Books

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 
Author Message
 Post subject: A Problem with an aggregate query
PostPosted: Tue Mar 07, 2006 4:05 pm 
Regular
Regular

Joined: Mon Aug 29, 2005 3:07 pm
Posts: 77
I am using NHIbernate 1.0.2.0.

I want to execute an aggregate query, that returns a single result.
I do it like this:
Code:
IQuery q = theSession.CreateQuery ("select sum(ol.Price) from OrderLine ol");
object o = q.UniqueResult();


This works just fine.
However, I want it a little bit different. I want the sum of all the Prices multiplied by the number of items:

Code:
IQuery q = theSession.CreateQuery ("select sum(ol.Price * ol.NumberOfItems) from OrderLine ol");
object o = q.UniqueResult();


Here, things go wrong. I get an exception that says 'could not execute query'.
The inner exception tells me something about a failed FieldNameLookUp call for field x1_0_
When I intercept the query that NHibernate has created, and execute this query directly into Query Analyzer, it all works.
This query looks like this:
Code:
select sum(orderline0_.NumberOfItems*orderline0_.ItemPrice) as x0_0_ from tblOrderLine orderline0_


So, it is not surprising that the FieldNameLookUp failed, since I do not have a field that is called x1_0_. The field is called x0_0_

Is this a bug in NHibernate ?
How can I solve or work around this ?


Last edited by whoami on Tue Mar 07, 2006 4:23 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 07, 2006 4:22 pm 
Regular
Regular

Joined: Mon Aug 29, 2005 3:07 pm
Posts: 77
Apparently, Hibernate (the Java version) has had this same bug:
http://saloon.javaranch.com/cgi-bin/ubb ... 8&t=000748

There, it seems to be solved in Hibernate 3.0

Any idea when it will be solved in NHibernate, and, has anybody encountered the same problem ?
And, if so, how did you fix it ?


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 07, 2006 4:35 pm 
Regular
Regular

Joined: Mon Aug 29, 2005 3:07 pm
Posts: 77
I am trying to work around this, by using the CreateSQLQuery method, but this won't work as the 3rd parameter of this method expectes a persistent type, while my query just returns a decimal.
:(


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 09, 2006 3:31 pm 
Regular
Regular

Joined: Mon Aug 29, 2005 3:07 pm
Posts: 77
While I'm waiting for a bugfix for this, I'm trying to work arround this problem, however, it is not working...

So, instead of executing an aggregate query, I now try to retrieve all the OrderLine objects that belong to a given customer.
I do this using the following HQL:

Code:
IQuery q = s.CreateQuery ("from OrderLine ol inner join Order inner join Customer " +
                                      "where Customer.Id = :custId");


However, when I try to execute this (after setting the named parameter), I receive an exception that says:
Quote:
BY expected after GROUP or ORDER

However, I'm not using a group by or order by expression here... Am I doing something terribly wrong ?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 09, 2006 3:56 pm 
Regular
Regular

Joined: Mon Aug 29, 2005 3:07 pm
Posts: 77
I've tried some things, and I'm a bit further...

I know have this query:
Code:
select ol from OrderLine ol, Order o, Customer c
where ol.Owner = o, o.Owner = c

This works. This gives me all the orderlines.

However, when I want to filter here, on customer Id, it doesn't work anymore:
Code:
select ol from OrderLine ol, Order o, Customer c
where ol.Owner = o, o.Owner = c
and c.Id = :custId


I set the named parameter, and when I execute the List() method of the IQuery, I receive an exception that says:
Quote:
Could not ressolve property:Id of of :ShopDomain.Customer

However, my Customer class does have a property that is named Id.
:?

Also, when I try to write the query like ths
Code:
select ol from ShopDomain.OrderLine ol
inner join ShopDomain.Order o
inner join ShopDomain.Customer c

I'm given an exception that says:
Quote:
outer or full join must be followed by path expression

I haven't found any HQL resources on the net that tell me what 's wrong with it...

update:
Ok, I've found out that the Id property was not mentioned in my mapping file; the id element in my mapping file is referencing to the field _id and not to the property Id.
So, I've added a property 'Id' in my mapping file now, with update='false' and insert='false' attributes.
However, this property is read-only (getter only), so I have now a problem while retrieving a Customer. I get an exception that says 'ArgumentException while setting the property value by reflection...'
This is offcourse, because I do not have a setter. However, there must be some way to set this property to read-only in the mapping file, so i'm going to search a bit for it.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 09, 2006 4:24 pm 
Regular
Regular

Joined: Mon Aug 29, 2005 3:07 pm
Posts: 77
Ok, this error that mentions that a BY is expected after an Order, is probably because I've a class that 's called Order, and Order is also a reserved word.
Is there a way to 'escape' class-names in hql ?

I Also do not succeed in mentionning in my mapping file that a property is read-only and that it should be read only and that nhibernate shouldn't try to set it


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 09, 2006 5:01 pm 
Regular
Regular

Joined: Mon Aug 29, 2005 3:07 pm
Posts: 77
I've got a solution... finally....
After 2 hours of time spent on something as simple as a select sum( bla * bla )

What I've done:

I've created my query like this:
Code:
IQuery q = s.CreateQuery ("select ol from OrderLine ol, Order o, Customer c " +
    " where ol.Owner = o and o.Owner = c and c.Id = :custId");


Executing this query gave me the exception that c:Id could not be found.
This is because the Id property did not appear in my mapping file.

So, I've added it in my mapping file with an update="false" and insert="false" attribute.
This didn't solve the problem yet, since NHibernate was not able to load a Customer now, because the Id property of my Customer class is read-only. (It's the key, and I use the field-access for the id).

So, thankfully, .NET 2.0 lets you know specify access modifiers for a getter and a setter separatly, so I've modified my Id property in my customer class like this:
Code:
public class Customer
{
    private int id;

    public int Id
    {
         get { return id; }
         private set { id = value; }
    }
}


This has cost my 2 hours.... So, using NHibernate is not always good for improving productivity.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 14, 2006 10:21 am 
Regular
Regular

Joined: Sun Feb 12, 2006 10:18 pm
Posts: 60
Location: Rosario, Argentina
Well, although you worked it around on your own and noone seem to know how to help you, I have to thank you because I had a problem with the setter and your "private" keyword in the set helped me solve it!

So... thank you!

Alejandro.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jul 10, 2006 8:28 pm 
Beginner
Beginner

Joined: Wed Jun 08, 2005 4:59 pm
Posts: 27
You could have also used the "access" parameter in your mapping file. Its well documented.

-MT


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 08, 2006 3:19 pm 
Regular
Regular

Joined: Mon Aug 29, 2005 3:07 pm
Posts: 77
mteper wrote:
You could have also used the "access" parameter in your mapping file. Its well documented.

-MT

Yes indeed, I'm using that now. ;)


Top
 Profile  
 
 Post subject: Came from your blog
PostPosted: Fri Jan 12, 2007 3:00 pm 
Beginner
Beginner

Joined: Thu Oct 19, 2006 1:03 pm
Posts: 29
About the
Quote:
BY expected after GROUP or ORDER
problem: perhaps it would work better, shouldn't you have the Order keyword in your query, but rather use the "qualified" assembly.class-path. I.e.
Code:
@"from NamespaceName.ShopDomain.OrderLine ol
inner join NamespaceName.ShopDomain.Order inner join NamespaceName.ShopDomain.Customer
where Customer.Id = :custId"


Perhaps that'd work.

I've been reading your blog entries; I'm at the second in the series now, and it's really really nice to have a source of domain driven design; a search on google gives almost nothing but books!

I'm also interested in why you are using repositories, as opposed to perhaps what Billy suggests in his article at codeproject. What Billy doesn't touch on though, is service oriented architecture, or the separation of logic between the dao objects (your repositories) and the domain model itself. He also doesn't discuss what value there might be of using Value Objects between your business layer and your presentation layer(s)...

I'll just finish reading your articles and ask some questions in the comments :p.
here they are btw...


Top
 Profile  
 
 Post subject: Re: Came from your blog
PostPosted: Wed Feb 28, 2007 4:31 am 
Regular
Regular

Joined: Mon Aug 29, 2005 3:07 pm
Posts: 77
echoSwe wrote:
.

I've been reading your blog entries; I'm at the second in the series now, and it's really really nice to have a source of domain driven design; a search on google gives almost nothing but books!

Thanks. :)

Quote:
I'm also interested in why you are using repositories

I use repositories to abstract the way I get my data out of a datastore. As you can see, in my unit-tests, I need to 'simulate' some behaviour in where I should get some data from a datastore. However, in my unit-test, I do not want to access a database. This means that I can create a repository-implementation which is merely a mock object. This lets me test my code withouth having to change the implementation of my code.
In my application, I can then use another implementation of my repository, which uses NHibernate f.i. to access the DB.
For more info on repositories, I also refer to the excellent 'Domain Driven Design' book by Eric Evans (which is not an easy read though), or the free e-book on DDD which can be found here: click


Top
 Profile  
 
 Post subject: Re: A Problem with an aggregate query
PostPosted: Mon Sep 21, 2009 4:16 am 
Newbie

Joined: Fri Aug 21, 2009 5:31 am
Posts: 2
access="field"? or access="property"


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 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.