Hibernate - Complex Query from multiple Tables to one object

Question

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

Answer 1

Their are two ways to do this.

1. You will get a list object array.

 List<Object[]>

Here one element of array represent one row of your query.

2. You can use a hibernate feature ResultTransformer - Create a simple class for the output of your query. - Create a ResultTransformer.

Ex.

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

- set transformer to the query.

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

Answer 2

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