休眠 - 从多个表到一个对象的复杂查询

java sql hibernate

2022-09-04 02:30:35

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

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

我不确定如何做到这一点，因为过去我只使用一个表到一个实体。我需要在哪里指定要使用可以跨多个表的复杂查询？这只在我的代码中吗？我的 hbm.xml 文件？除了我现在的尝试之外，我想不出其他任何东西。

下面是我的查询示例：

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";

答案 1

他们有两种方法可以做到这一点。

1. 您将获得一个列表对象数组。

 List<Object[]>

此处，数组的一个元素表示查询的一行。

2. 您可以使用休眠功能 ResultTransformer - 为查询的输出创建一个简单的类。- 创建结果转换器。

前任。

 public class MyResultTransformer implements ResultTransformer {


/*
Method to convert to generic type list
*/
    @Override
    public List<Employee> transformList(List arg0) {
        List<Employee> employees = new ArrayList<Employee>();
        for (Object employee : arg0) {
            employees.add((Employee) employee);
        }
        return employees;
    }

    /*
    Code to transform your query output to Object
    */
    @Override
    public Employee transformTuple(Object[] arg0, String[] arg1) {
        System.out.println("MyResultTransformer.transformTuple()");
        Employee tempEmp = new Employee();
        tempEmp.setEmployee_id((BigInteger) arg0[0]);
        return tempEmp;
    }
}

- 将转换器设置为查询。

Query query=session.createSQLQuery("SELECT * FROM employeedetail"); // You can use named query, SQL native also
      query.setResultTransformer(new MyResultTransformer());
          List<Employee> employees=query.list();