Hibernate 多表查询

 

一 、 通过原生sQl查询 

 

老外的网站NB啊

http://stackoverflow.com/questions/9197780/hibernate-complex-query-from-multiple-tables-to-one-object

 

Finally I could solved using this code:

    String stringQuery =
     "select " +
       "CU.Card as card, " +
       "CU.Fidely_Code as fidelyCode, "+
             "PI.Identity_Card as identityCard, " +
             "PI.Name as name, " +
             "PI.Surname as surname, " +
             "PI.Gender as gender, "+
             "AD.Zip as zip, " +
             "AD.Geo_Lat as geo_lat, " +
             "AD.Geo_Long as geo_long, "+
             "CO.City_Geo_Level as cityGeoLevel, "+
             "CA.Name as campaignName, "+
             "CU.Status as status, "+
             "Sum(MO.Charged_Points) as pointsCharged, "+
             "Sum(MO.Total_Money) as amountPurchase, "+
             "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 ";
   
   //Query query = this.getSession().createSQLQuery(stringQuery).addEntity("", PromotionsWinnerLittle.class);
   //Query query = this.getSession().createSQLQuery(stringQuery).setResultSetMapping("PromotionsWinnerLittle");
   Query query = this.getSession().createSQLQuery(stringQuery)
    .addScalar("card", StandardBasicTypes.LONG)
    .addScalar("fidelyCode", StandardBasicTypes.LONG)
    .addScalar("identityCard", StandardBasicTypes.STRING)
    .addScalar("name", StandardBasicTypes.STRING)
    .addScalar("surname", StandardBasicTypes.STRING)
    .addScalar("gender", StandardBasicTypes.STRING)
    .addScalar("zip", StandardBasicTypes.STRING)
    .addScalar("geo_lat", StandardBasicTypes.BIG_DECIMAL)
    .addScalar("geo_long", StandardBasicTypes.BIG_DECIMAL)
    .addScalar("cityGeoLevel", StandardBasicTypes.LONG)
    .addScalar("campaignName", StandardBasicTypes.STRING)
    .addScalar("status", StandardBasicTypes.LONG)
    .addScalar("pointsCharged", StandardBasicTypes.BIG_DECIMAL)
    .addScalar("amountPurchase", StandardBasicTypes.LONG)
    .addScalar("amountTransWinner", StandardBasicTypes.LONG)   
    .setResultTransformer(Transformers.aliasToBean(PromotionsWinnerLittle.class));
   
   //Query query = this.getSession().createSQLQuery(stringQuery);
   
   query = query.setLong("pPromotionID", promotionID);

   List lista = query.list();

 

-------------------------------------------------------

 

二 通过 ScrollableResults 查询

 

Session session = null;
         ScrollableResults srs ＝ null;
         try{
             session = HibernateSessionFactory.openSession();//得到一个Hibernate Session
             //下面创建一个匿名Query实例并调用它的scroll()方法返回以ScrollableResults形式组织的查询结果
             srs = session.createQuery(“select b.name, count(a.fee) mix(a.chargeBeginTime) max(a.chargeEndTime) from charge a, customer b where a.idCustomer = b.idCustomer and a.chargeBeginTime >= ? and a.chargeEndTime < ? gourp by a.idCustomer“).setDate(0, statTimeBegin).setDate(1, statTimeEnd).scroll();
             //将查询结果放入List保存
             while(srs.next()){
                 res.add(new TotalCharge(srs.getString(0), srs,getDouble(1), srs.getDate(2), srs.getDate(3)));
             }

 

 

三 貌似通过 HQl  tuples 查询也可以

     try {
002   System.out.println("test..");
003   List list =
004     s
005       .createQuery("select user.id,relation.id from User user,Relationship relation")
006       .list();
007   for (Iterator iter = list.iterator(); iter.hasNext();) {
008     Object[] record = (Object[]) iter.next();
009     System.out.print(record[0] + "---" + record[1]);
010   }
011   s.close();
012 } catch (HibernateException e) {
013   System.err.println(e.getMessage());
014 }