Hibernate - 从多个表到一个对象的复杂查询 [英] Hibernate - Complex Query from multiple Tables to one object

问题描述

我有一个跨越7个表的复杂查询，并希望知道如何在Hibernate中实现它。

我目前的尝试是使用session.createSQLQuery进行查询，我将结果映射到一个特定的实体。

我不知道该如何做，因为过去我只使用一个表来处理一个实体。我需要在哪里指定我想要使用可能跨越多个表的复杂查询？这只是在我的代码？我的hbm.xml文件？我想不出任何超出我目前的尝试。

这是我的查询的一个例子：

  String stringQuery = 
选择WI.Customer_Id，CU.Card，CU.Code，+ 
PI.Identity_Card，PI.Name，PI.Surname， PI.Gender，+ 
AD.Zip，AD.Geo_Lat，AD.Geo_Long，+ 
CO.City_Geo_Level，+ 
CU.Address_id，CA.Name + 
CU.Category_Id，+ 
CU.Status，+ 
总和（MO.Charged_Points）为Charged_Points，+ 
总和（MO.Total_Money）作为Total_Money，+ 
通过Promotions_Winner WI+ 
计算（MO.id）为AmountTransWinner+ 
在WI.Customer_id上加入客户CU+ 
 CU.id+ 
在CU.Personal_Info_Id = PI.id+ 
加入Personal_Info PI+ 
CU.Address_Id =加入地址AD+ 
 AD.id+ 
加入国家CO+ 
在AD.country_id = CO.id+ 
加入活动CA+ 
在CU.Campaign_Id = CA.id+ $ b $ b加入Movements MO+ 
在WI.Movement_Id = MO.id+ 
其中WI.Promotion_Id =：pPromotionID+ 
group by+ 
 WI.Customer_Id，CU.Card，CU.Fidely_Code，+ 
PI.Identity_Card，PI.Name，PI.Surname，PI.Gender，+ 
AD.Zip，AD.Geo_Lat ，AD.Geo_Long，+ 
CO.City_Geo_Level，+ 
CU.Address_id，CA.Name，+ 
CU.Category_Id，+ 
CU 。状态; 
  

解决方案

您不需要SQL来执行此查询。 HQL将会很好。这样的查询返回包含结果行的 List< Object []> ，每个 Object [] 组。因此，您可以在索引0处找到客户ID，在索引1处找到卡片，等等。您只需循环遍历行并在每次迭代中创建一个轻量级对象的实例。

请参阅 http://docs.jboss.org/ hibernate / core / 3.6 / reference / zh-CN / html_single /＃queryhql-select

I have a complex query crossing 7 tables and want to know how to implement it within Hibernate.

My current attempt is to make the query using session.createSQLQuery and I would map the result to a particular entity.

I am not sure how to do that as in the past I have only worked with one table to one entity. Where would I need to specify that I would like to use a complex query that could span multiple tables? Does that go only in my code? My hbm.xml file? I can't think of anything else beyond my current attempt.

Here is an example of my query:

String stringQuery = 
        "select  WI.Customer_Id, CU.Card, CU.Code, "+
                "PI.Identity_Card, PI.Name, PI.Surname, PI.Gender, "+
                "AD.Zip, AD.Geo_Lat, AD.Geo_Long, "+
                "CO.City_Geo_Level, "+
                "CU.Address_id, CA.Name, "+
                "CU.Category_Id, "+
                "CU.Status, "+
                "Sum(MO.Charged_Points) as Charged_Points, "+
                "Sum(MO.Total_Money) as Total_Money, "+
                "Count(MO.id) as AmountTransWinner "+
        "from Promotions_Winner WI "+ 
        "join Customers CU "+
          "on WI.Customer_id = CU.id "+
        "join Personal_Info PI "+
          "on CU.Personal_Info_Id = PI.id "+
        "join Address AD "+
          "on CU.Address_Id = AD.id "+
        "join Countries CO "+
          "on AD.country_id = CO.id "+
        "join Campaigns CA "+
          "on CU.Campaign_Id = CA.id "+
        "join Movements MO "+
          "on WI.Movement_Id = MO.id "+
        "where WI.Promotion_Id = :pPromotionID "+
        "group by "+
          "WI.Customer_Id, CU.Card, CU.Fidely_Code, "+
          "PI.Identity_Card, PI.Name, PI.Surname, PI.Gender, "+
          "AD.Zip, AD.Geo_Lat, AD.Geo_Long, "+
          "CO.City_Geo_Level, "+
          "CU.Address_id, CA.Name, "+
          "CU.Category_Id, "+
          "CU.Status";

解决方案

You don't need SQL to execute this query. HQL will do fine. And such a query returns a List<Object[]>, each Object[] containing a row of the result set. So you will find the customer ID at index 0, the card at index 1, etc. You just have to loop througn the rows and create an instance of your lightweight object at each iteration.

See http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#queryhql-select

这篇关于Hibernate - 从多个表到一个对象的复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！