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

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 
  Previous topic | Next topic 
Author Message
tinomen
 Post subject: CreateSQLQuery Misuse?
PostPosted: Tue Aug 30, 2005 1:19 pm 
Regular
Regular

Joined: Tue May 24, 2005 12:55 pm
Posts: 56
I believe I'm misusing NHibernate in this example, but it seems to work without any problems. I'm using NHibernate to wire a class that is not related to a table using CreateSQLQuery. All that was needed was a map to a dummy table and the SQL had to return unique data for the Id property. For those with a lot of crazy SQL in thier code, this may help you move some of it to NHibernate.

Here is the map I used
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping namespace="SourceNet.Data.SQLObjects" xmlns="urn:nhibernate-mapping-2.0">
   <class name="SourceNet.Data.SQLObjects.ClientOrderSummary1, SourceNet.Data" table="NOT_REAL_TABLE" lazy="false" mutable="false">
      <id column="NO_ID" type="Int32" access="field.camelcase-underscore" name="Id">
         <generator class="assigned" />
      </id>
      <property column="BULK_ORDER_ID" name="BulkOrderId" access="field.camelcase-underscore" type="Int32"/>
      <property column="TRACKING_NUM" name="TrackingNum" access="field.camelcase-underscore" type="String"/>
      <property column="TOTAL" name="Total" access="field.camelcase-underscore" type="Int32"/>
      <property column="ORDERS_COMPLETED" name="OrdersCompleted" access="field.camelcase-underscore" type="Int32"/>
      <property column="ORDERS_ON_HOLD" name="OrdersOnHold" access="field.camelcase-underscore" type="Int32"/>
      <property column="ORDERS_IN_QA" name="OrdersInQa" access="field.camelcase-underscore" type="Int32"/>
      <property column="TYPE" name="Type" access="field.camelcase-underscore" type="Int32"/>
      <property column="CL_PERS_ID" name="ClientPersId" access="field.camelcase-underscore" type="Int32"/>
      <property column="SYS_LOGIN_ID" name="SysLoginId" access="field.camelcase-underscore" type="Int32"/>
      <property column="CL_DEPT_ID" name="ClDeptId" access="field.camelcase-underscore" type="Int32"/>
      <property column="MIN_ORD_DT" name="MinOrderDate" access="field.camelcase-underscore" type="DateTime"/>
      <property column="FIRST_NAME" name="FirstName" access="field.camelcase-underscore" type="String"/>
      <property column="LAST_NAME" name="LastName" access="field.camelcase-underscore" type="String"/>
      <property column="BY_FNAME" name="ByFname" access="field.camelcase-underscore" type="String"/>
      <property column="BY_LNAME" name="ByLname" access="field.camelcase-underscore" type="String"/>
   </class>
</hibernate-mapping>


And here is the way the object was used in a test unit. It was suprisingly fast.
Code:
[Test]
[Category("GetSQLObject Tests")]
public void SQL_NH_ClientOrderSummary1()
{
   try
   {
      Console.WriteLine("Get SQL Object ClientOrderSummary1 for cl id 56");
      DateTime start = DateTime.Now;
      DateTime end;
      Console.WriteLine("{0}   Start\n", start.ToString(TIME_FORMAT));
            
      string sql = "SELECT MAX(o.ORDER_ID) as {cos1.Id}, o.BULK_ORDER_ID as {cos1.BulkOrderId}, o.TRACKING_NUM as {cos1.TrackingNum}, COUNT(*) AS {cos1.Total}, "+
         "SUM(CASE WHEN o.order_status_type_id IN (9, 11) THEN 1 ELSE 0 END) AS {cos1.OrdersCompleted}, "+
         "SUM(CASE WHEN o.order_status_type_id = 10 THEN 1 ELSE 0 END) AS {cos1.OrdersOnHold}, "+
         "SUM(CASE WHEN o.order_status_type_id IN (15, 19, 22, 23, 27) THEN 1 ELSE 0 END) AS {cos1.OrdersInQa}, "+
         "MIN(o.CL_ORD_DT) AS {cos1.MinOrderDate}, 0 AS {cos1.Type}, bo.CL_PERS_ID as {cos1.ClientPersId}, "+
         "cp.FIRST_NAME as {cos1.FirstName}, cp.LAST_NAME as {cos1.LastName}, bo.SYS_LOGIN_ID as {cos1.SysLoginId}, "+
         "cp1.FIRST_NAME AS {cos1.ByFname}, cp1.LAST_NAME AS {cos1.ByLname}, o.CL_DEPT_ID as {cos1.ClDeptId} "+
         "FROM [ORDER] as o "+
         "INNER JOIN BULK_ORDER as bo ON (o.BULK_ORDER_ID = bo.BULK_ORDER_ID) "+
         "LEFT OUTER JOIN CL_PERS as cp1 ON (bo.SYS_LOGIN_ID = cp1.SYS_LOGIN_ID) "+
         "LEFT OUTER JOIN CL_PERS as cp ON (bo.CL_PERS_ID = cp.CL_PERS_ID) "+
         "WHERE   o.CL_ID = :Clid AND   o.CL_ORD_DT > :ClOrdDtMin AND o.BULK_ORDER_ID IS NOT NULL "+
         "GROUP BY o.BULK_ORDER_ID, o.TRACKING_NUM, bo.CL_PERS_ID, cp.FIRST_NAME, "+
         "cp.LAST_NAME, bo.SYS_LOGIN_ID, cp1.FIRST_NAME, "+
         "cp1.LAST_NAME, o.CL_DEPT_ID "+
         "UNION "+
         "SELECT   MAX(o.ORDER_ID) as {cos1.Id}, 0 as {cos1.BulkOrderId}, '' as {cos1.TrackingNum}, COUNT(*) AS {cos1.Total}, "+
         "SUM(CASE WHEN o.order_status_type_id IN (9, 11) THEN 1 ELSE 0 END) AS {cos1.OrdersCompleted}, "+
         "SUM(CASE WHEN o.order_status_type_id = 10 THEN 1 ELSE 0 END) AS {cos1.OrdersOnHold}, "+
         "SUM(CASE WHEN o.order_status_type_id IN (15, 19, 22, 23, 27) THEN 1 ELSE 0 END) AS {cos1.OrdersInQa}, "+
         "MIN(o.CL_ORD_DT) AS {cos1.MinOrderDate}, 1 AS {cos1.Type}, 0 as {cos1.ClientPersId}, '' as {cos1.FirstName}, "+
         "'' as {cos1.LastName}, 0 as {cos1.SysLoginId}, '' AS {cos1.ByFname}, '' AS {cos1.ByLname}, 0 as {cos1.ClDeptId} "+
         "FROM   [ORDER] as o "+
         "WHERE   o.CL_ID = :Clid AND   o.CL_ORD_DT > :ClOrdDtMin AND o.BULK_ORDER_ID IS NULL "+
         "ORDER BY {cos1.BulkOrderId}, {cos1.ClDeptId}";
            
      IQuery qry = _mgr.Session.CreateSQLQuery(sql, "cos1", typeof(SourceNet.Data.SQLObjects.ClientOrderSummary1));
      qry.SetInt32("Clid", 56);
      qry.SetDateTime("ClOrdDtMin", new DateTime(2005, 6, 1));
      IList results = qry.List();
      
      Console.WriteLine("## results count: {0}", results.Count);
            
      end = DateTime.Now;
      Console.WriteLine("\n{0}   Stop", end.ToString(TIME_FORMAT));
      Console.WriteLine("======================================================");
      Console.WriteLine("Total Time {0}\n", end.Subtract(start));
            
      Console.WriteLine("BOrdId Total    Type     MinOrdDt  Cmpltd   OnHold   InQa   ClDpt  ClPers SysLogin");
      foreach(SourceNet.Data.SQLObjects.ClientOrderSummary1 cos in results)
      {
      Console.WriteLine("{0,6}{1,6}{2,8}{3,13}{4,8}{5,8}{6,8}{7,8}{8,8}{9,9} {10} [{11} {12} ({13} {14})]",
         cos.BulkOrderId, cos.Total, cos.Type,  cos.MinOrderDate.ToString("dd MMM yyyy"), cos.OrdersCompleted, cos.OrdersOnHold, cos.OrdersInQa,
         cos.ClDeptId, cos.ClientPersId, cos.SysLoginId, cos.TrackingNum, cos.FirstName, cos.LastName, cos.ByFname, cos.ByLname);
      }
            
      end = DateTime.Now;
      Console.WriteLine("\n{0}   Stop", end.ToString(TIME_FORMAT));
      Console.WriteLine("======================================================");
      Console.WriteLine("Total Time {0}", end.Subtract(start));
   } catch(Exception e) {
      Console.WriteLine("TEST ERROR\n{0}", e.Message);
      Console.WriteLine("STACK TRACE\n{0}", e.StackTrace);
      Console.WriteLine("INNNER ERROR\n{0}", e.InnerException);
      Assert.IsTrue(false, "SQL Object not obtained.");
   }
}


The output
Quote:
Get SQL Object ClientOrderSummary1 for cl id 56
04:14:08:23812 Start

## results count: 11

04:14:08:48812 Stop
======================================================
Total Time 00:00:00.2499968

BOrdId Total Type MinOrdDt Cmpltd OnHold InQa ClDpt ClPers SysLogin
0 4 1 13 Jun 2005 4 0 0 0 0 0 [ ( )]
19494 172 0 13 Jun 2005 171 0 1 451 1896 2713 Adverse BPO Order_Impac Funding 2005-06-015 [Tim Knight (Tim Knight)]

...

04:14:08:50374 Stop
======================================================
Total Time 00:00:00.2656216
Top
 Profile  
 
tinomen
 Post subject:
PostPosted: Thu Sep 08, 2005 6:44 pm 
Regular
Regular

Joined: Tue May 24, 2005 12:55 pm
Posts: 56
Changed this to be a named query and now it performs as well as a dataset. Even impressed my co-worker who is a NH nay sayer and die-hard DataSet guy.

Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping namespace="SourceNet.Data" xmlns="urn:nhibernate-mapping-2.0">
   <class name="SourceNet.Data.ClientOrderSummary1, SourceNet.Data" table="NOT_REAL_CLIENT_ORDER_SUMMARY1" lazy="false" mutable="false">
      <id column="NO_ID" type="Int32" access="field.camelcase-underscore" name="Id">
         <generator class="assigned" />
      </id>
      <property column="BULK_ORDER_ID" name="BulkOrderId" access="field.camelcase-underscore" type="Int32"/>
      <property column="TRACKING_NUM" name="TrackingNum" access="field.camelcase-underscore" type="String"/>
      <property column="TOTAL" name="Total" access="field.camelcase-underscore" type="Int32"/>
      <property column="ORDERS_COMPLETED" name="OrdersCompleted" access="field.camelcase-underscore" type="Int32"/>
      <property column="ORDERS_ON_HOLD" name="OrdersOnHold" access="field.camelcase-underscore" type="Int32"/>
      <property column="ORDERS_IN_QA" name="OrdersInQa" access="field.camelcase-underscore" type="Int32"/>
      <property column="TYPE" name="Type" access="field.camelcase-underscore" type="Int32"/>
      <property column="CL_PERS_ID" name="ClientPersId" access="field.camelcase-underscore" type="Int32"/>
      <property column="SYS_LOGIN_ID" name="SysLoginId" access="field.camelcase-underscore" type="Int32"/>
      <property column="CL_DEPT_ID" name="ClDeptId" access="field.camelcase-underscore" type="Int32"/>
      <property column="MIN_ORD_DT" name="MinOrderDate" access="field.camelcase-underscore" type="DateTime"/>
      <property column="FIRST_NAME" name="FirstName" access="field.camelcase-underscore" type="String"/>
      <property column="LAST_NAME" name="LastName" access="field.camelcase-underscore" type="String"/>
      <property column="BY_FNAME" name="ByFname" access="field.camelcase-underscore" type="String"/>
      <property column="BY_LNAME" name="ByLname" access="field.camelcase-underscore" type="String"/>
   </class>
   <sql-query name="GetClientOrderSummary1">
      <return alias="cos1" class="SourceNet.Data.ClientOrderSummary1, SourceNet.Data"/>
      SELECT   MAX(o.ORDER_ID) as {cos1.Id},
            o.BULK_ORDER_ID as {cos1.BulkOrderId},
            o.TRACKING_NUM as {cos1.TrackingNum},
            COUNT(*) AS {cos1.Total},
            SUM(CASE WHEN o.order_status_type_id IN (9, 11) THEN 1 ELSE 0 END) AS {cos1.OrdersCompleted},
            SUM(CASE WHEN o.order_status_type_id = 10 THEN 1 ELSE 0 END) AS {cos1.OrdersOnHold},
            SUM(CASE WHEN o.order_status_type_id IN (15, 19, 22, 23, 27) THEN 1 ELSE 0 END) AS {cos1.OrdersInQa},
            MIN(o.CL_ORD_DT) AS {cos1.MinOrderDate},
            0 AS {cos1.Type},
            bo.CL_PERS_ID as {cos1.ClientPersId},
            cp.FIRST_NAME as {cos1.FirstName},
            cp.LAST_NAME as {cos1.LastName},
            bo.SYS_LOGIN_ID as {cos1.SysLoginId},
            cpBy.FIRST_NAME AS {cos1.ByFname},
            cpBy.LAST_NAME AS {cos1.ByLname},
            o.CL_DEPT_ID as {cos1.ClDeptId}
      FROM   [ORDER] as o
            INNER JOIN BULK_ORDER as bo ON (o.BULK_ORDER_ID = bo.BULK_ORDER_ID)
            LEFT OUTER JOIN CL_PERS as cpBy ON (bo.SYS_LOGIN_ID = cpBy.SYS_LOGIN_ID)
            LEFT OUTER JOIN CL_PERS as cp ON (bo.CL_PERS_ID = cp.CL_PERS_ID)
      WHERE   o.CL_ID = :Clid
      AND      o.CL_ORD_DT > :ClOrdDtMin
      AND      o.BULK_ORDER_ID IS NOT NULL
      GROUP BY o.BULK_ORDER_ID, o.TRACKING_NUM, bo.CL_PERS_ID, cp.FIRST_NAME,
            cp.LAST_NAME, bo.SYS_LOGIN_ID, cpBy.FIRST_NAME,
            cpBy.LAST_NAME, o.CL_DEPT_ID
      UNION
      SELECT   MAX(o.ORDER_ID) as {cos1.Id},
            0 as {cos1.BulkOrderId},
            '' as {cos1.TrackingNum},
            COUNT(*) AS {cos1.Total},
            SUM(CASE WHEN o.order_status_type_id IN (9, 11) THEN 1 ELSE 0 END) AS {cos1.OrdersCompleted},
            SUM(CASE WHEN o.order_status_type_id = 10 THEN 1 ELSE 0 END) AS {cos1.OrdersOnHold},
            SUM(CASE WHEN o.order_status_type_id IN (15, 19, 22, 23, 27) THEN 1 ELSE 0 END) AS {cos1.OrdersInQa},
            MIN(o.CL_ORD_DT) AS {cos1.MinOrderDate},
            1 AS {cos1.Type},
            0 as {cos1.ClientPersId},
            '' as {cos1.FirstName},
            '' as {cos1.LastName},
            0 as {cos1.SysLoginId},
            '' AS {cos1.ByFname},
            '' AS {cos1.ByLname},
            0 as {cos1.ClDeptId}
      FROM   [ORDER] as o
      WHERE   o.CL_ID = :Clid
      AND      o.CL_ORD_DT > :ClOrdDtMin
      AND      o.BULK_ORDER_ID IS NULL
   </sql-query>
</hibernate-mapping>


Code:
IQuery qry = _session.GetNamedQuery("GetClientOrderSummary1");
qry.SetParameter("Clid", 56);
qry.SetParameter("ClOrdDtMin", DateTime.Now.AddDays(-90.0));
IList results = qry.List();
Top
 Profile  
 
sergey
 Post subject:
PostPosted: Thu Sep 08, 2005 7:28 pm 
Contributor
Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic
Nice trick :)
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 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

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