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

Please refer stackoverflow.com/questions/21374550/…

Touchstone
– Touchstone

2014-01-27 11:09:38 +00:00
Commented Jan 27, 2014 at 11:09 — Touchstone
– Touchstone, Commented Jan 27, 2014 at 11:09

Ankit Katiyar · Accepted Answer · 2014-06-30 10:00:04Z

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();

JB Nizet · Accepted Answer · 2012-02-08 17:31:19Z

7

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

answered Feb 8, 2012 at 17:31

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

6 Comments

digitaljoel Over a year ago

rather than iterating the List<Object[]> and manually creating your lightweight object you can use select new my.package.myLightWeightObject(t1.field1, t2.field2, t3.field3) and just setup the constructor for myLightWeightObject to take the appropriate fields. That way Hibernate will return a List<MyLightWeightObject> instead of List<Object[]> for you.

Mark Comix Over a year ago

JB Nizet, yes, i know, but if I use List<Object[]> and in the future, other person adds one new field between the current fields, it doesn't work anymore. I like the digitaljoel idea, but I don't understand how to do it. Can you explain a little more?

JB Nizet Over a year ago

Yes. If he changes the database tables without changing the code, it won't work either. Implement unit tests, and check that the query works as expected in the unit tests. I don't know how I (or digitaljoel) can be clearer than that. What don't you understand? Have you at least tried something?

Mark Comix Over a year ago

I don't understand where the select new my.package.myLightWeightObject(t1.field1, t2.field2, t3.field3) go

JB Nizet Over a year ago

At the beginning of the query. Why don't you read the documentation? I gave you the link, and it has an example. You don't even have to scroll to find it.

|

Mark Comix · Accepted Answer · 2012-02-08 18:51:55Z

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();

I Just added the "As" part on Select and the addScalar + setResultTransformer

A problem in this approach is once if you receive the data as primitive types and if you want to write this data to another table which is created with schema of user defined types is going to create a problem.

alex.p · Accepted Answer · 2012-02-08 17:33:54Z

0

So as far as I can see this is a join of 7 tables. If you're using hibernate you'd map each of these tables to entities and then use @JoinColumn to map to each of it's dependencies. This is the sort of SQL query that hibernate is there to prevent occuring.

answered Feb 8, 2012 at 17:33

alex.p

2,74920 silver badges28 bronze badges

2 Comments

Mark Comix Over a year ago

but I have Count and Sum fields,

alex.p Over a year ago

Then you'd just have getTotalChargePoints() as a method - that would be your business logic. That way whatever thing needs the individual sum or count or whatever doesn't need to call this query each time. For the count you'd literally just do List.size() on whatever collection of items it joins to (for a one to many relationship)

Collectives™ on Stack Overflow

Hibernate - Complex Query from multiple Tables to one object

4 Answers 4

Comments

6 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

6 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related