Hibernate Community • View topic - Using CreateSQLQuery to return unmanaged entity (I think)

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

Using CreateSQLQuery to return unmanaged entity (I think)

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

hitch

Post subject: Using CreateSQLQuery to return unmanaged entity (I think)

Posted: Thu Apr 05, 2007 9:36 am

Beginner

Joined: Wed Nov 29, 2006 12:23 pm
Posts: 42

I'm trying to use the following code to return an unmanaged entity (well, I think that's the term - it's not a mapped entity, but I thought I had to import the classtype)

Code:

            string sql = "SELECT   eb.EventID as CourseEventId, " +
                "ce.fromdate as FromDate,    " +
                "l.name as LocationName,    " +
                "isnull(v.name, '') as VenueName,    " +
                "ce.maxdelegates as MaxDelegates,    " +
                "SUM(CASE eb.Status WHEN 'Firm' THEN 1 ELSE 0 END) AS Firm as Firm, " +
                "SUM(CASE eb.Status WHEN 'On Waiting List' THEN 1 ELSE 0 END) AS OnWaitingList as OnWaitingList," +
                "SUM(CASE WHEN eb.Status = 'Invoiced' AND eb.CancelDate IS NULL THEN 1 ELSE 0 END) AS Invoiced as Invoiced," +
                "SUM(CASE eb.Status WHEN 'Cancelled' THEN 1 ELSE 0 END) AS Cancelled as Cancelled," +
                "SUM(CASE eb.Status WHEN 'Transferred' THEN 1 ELSE 0 END) AS Transferred as Transferred," +
                "ce.status  as Status" +
                "FROM " +
                "EventBooking eb INNER JOIN " +
                "Events ce ON eb.EventID = ce.EventID INNER JOIN " +
                "Courses c ON ce.CourseID = c.CourseID INNER JOIN " +
                "Locations l ON ce.LocationID = l.LocationID LEFT OUTER JOIN " +
                "tblVenues v ON ce.VenueID = v.VenueID " +
                "WHERE " +
                //"c.CourseID = :courseId " +
                "c.CourseID = 2545 " +
                "GROUP BY " +
                "eb.EventID,   ce.fromdate,   l.name,   v.name,   ce.maxdelegates,   ce.status";

            List<CourseEventBookingInfo> results = (List<CourseEventBookingInfo>)session
                .CreateSQLQuery(sql)
                .AddEntity("courseEventBookingInfo", typeof(CourseEventBookingInfo))
                .List<CourseEventBookingInfo>();

note I have also tried adding:

Code:

.SetResultTransformer(Transformers.AliasToBean(typeof(CourseEventBookingInfo)))

to the creation of the query.

I'm importing the class using the following (in a hbm.xml file)

Code:

I continually receive the following error:

Code:

NHibernate.MappingException: No persister for: ReportingEntities.CourseEventBookingInfo, ReportingEntities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null

Am I not using CreateSQLQuery properly? I've searched around everywhere, and have seen Sergey write a couple of times that the documentation around CreateSQLQuery needs to be updated due to some changes in the implementation, but the documentation in the latest release (1.2 CR1) doesn't appear to have many differences.

What am I doing wrong?

OR

Is there a better way to do this?

TIA.

Top

hitch

Post subject:

Posted: Tue Apr 10, 2007 6:01 am

Beginner

Joined: Wed Nov 29, 2006 12:23 pm
Posts: 42

Does anyone have any suggestions? I have a feeling that I'm going about this the wrong way, and that I may have to return all attributes of the entity when using CreateSQLQuery.

Any pointers in the right direction would be appreciated.

Thanks.

Top

hitch

Post subject:

Posted: Tue Apr 10, 2007 6:28 am

Beginner

Joined: Wed Nov 29, 2006 12:23 pm
Posts: 42

I've now changed this:

Code:

List<CourseEventBookingInfo> results = (List<CourseEventBookingInfo>)session
                .CreateSQLQuery(sql)
                .AddEntity("courseEventBookingInfo", typeof(CourseEventBookingInfo))
                .List<CourseEventBookingInfo>(); 

to be this:

Code:

List<CourseEventBookingInfo> results = (List<CourseEventBookingInfo>)session
                .CreateSQLQuery(sql)
                .AddEntity("eb",typeof(EventBooking))
                .AddEntity("ce",typeof(CourseEvent))
                .AddEntity("c",typeof(Course))
                .AddEntity("l",typeof(Location))
                .AddEntity("v",typeof(Venue))
                .SetResultTransformer(Transformers.AliasToBean(typeof(CourseEventBookingInfo)))           
                .SetInt32("courseId", courseId)
                .List<CourseEventBookingInfo>();

and I feel I'm getting closer. Now the error is:

Code:

System.IndexOutOfRangeException: EventBookingID

Which seems to be indicating that NHibernate is expecting all attributes of each entity in an

Code:

AddEntity("", typeof())

statement to be returned by the SQL.

If I'm returning all attributes/columns, is NHibernate actually able to transform the resultset into my return type? (i.e. CourseEventBookingInfo), or am I still barking up the wrong tree.

Next I'll try adding scalars to the query and then transforming the results, but after that I think my other option is to just work with my domain objects, although this will be massively inefficient as I'll have to execute many individual sql statements to return the same info as this one can.

Top

jobz

Post subject:

Posted: Tue Apr 10, 2007 6:34 am

Newbie

Joined: Tue Apr 10, 2007 5:29 am
Posts: 4

I do not have any experience working directly with NHibernate, but I have managed to accomplish what you are asking, but using ActiveRecord, which runs on top of NHibernate.

Take a look at

http://www.kenegozi.com/blog/2006/10/08/ProjectionUsingActiveRecordsImportAttributeAndHQLsSelectNewClause.aspx

Note you need to create the 'CommentView' class.

Top

hitch

Post subject:

Posted: Tue Apr 10, 2007 8:03 am

Beginner

Joined: Wed Nov 29, 2006 12:23 pm
Posts: 42

Just to keep this thread up to date, I managed to solve the problem. the resulting code is as follows:

Code:

string sql = "SELECT   eb.EventID as CourseEventId, " +
                "ce.fromdate as FromDate,    " +
                "l.name as LocationName,    " +
                "isnull(v.name, '') as VenueName,    " +
                "ce.maxdelegates as MaxDelegates,    " +
                "SUM(CASE eb.Status WHEN 'Firm' THEN 1 ELSE 0 END) AS Firm as Firm, " +
                "SUM(CASE eb.Status WHEN 'On Waiting List' THEN 1 ELSE 0 END) AS OnWaitingList as OnWaitingList," +
                "SUM(CASE WHEN eb.Status = 'Invoiced' AND eb.CancelDate IS NULL THEN 1 ELSE 0 END) AS Invoiced as Invoiced," +
                "SUM(CASE eb.Status WHEN 'Cancelled' THEN 1 ELSE 0 END) AS Cancelled as Cancelled," +
                "SUM(CASE eb.Status WHEN 'Transferred' THEN 1 ELSE 0 END) AS Transferred as Transferred," +
                "ce.status  as Status" +
                "FROM " +
                "EventBooking eb INNER JOIN " +
                "Events ce ON eb.EventID = ce.EventID INNER JOIN " +
                "Courses c ON ce.CourseID = c.CourseID INNER JOIN " +
                "Locations l ON ce.LocationID = l.LocationID LEFT OUTER JOIN " +
                "tblVenues v ON ce.VenueID = v.VenueID " +
                "WHERE " +
                //"c.CourseID = :courseId " +
                "c.CourseID = 2545 " +
                "GROUP BY " +
                "eb.EventID,   ce.fromdate,   l.name,   v.name,   ce.maxdelegates,   ce.status"; 

List<CourseEventBookingInfo> results = (List<CourseEventBookingInfo>)session
               .CreateSQLQuery(sql)
               .AddScalar("CourseEventId", NHibernateUtil.Int32)
               .AddScalar("FromDate", NHibernateUtil.DateTime)
               .AddScalar("LocationName", NHibernateUtil.String)
               .AddScalar("VenueName", NHibernateUtil.String)
               .AddScalar("MaxDelegates", NHibernateUtil.Int32)
               .AddScalar("Firm", NHibernateUtil.Int32)
               .AddScalar("OnWaitingList", NHibernateUtil.Int32)
               .AddScalar("Invoiced", NHibernateUtil.Int32)
               .AddScalar("Cancelled", NHibernateUtil.Int32)
               .AddScalar("Transferred", NHibernateUtil.Int32)
               .AddScalar("Status", NHibernateUtil.String)              
               .SetResultTransformer(Transformers.AliasToBean(typeof(CourseEventBookingInfo)))
               .SetInt32("courseId", courseId)
               .List<CourseEventBookingInfo>();

Top

Page 1 of 1

[ 5 posts ]

Board index » Hibernate & Java Persistence » NHibernate Users

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