How to create mapping of composed object with Hibernate when using a complex sqlquery?

Question

I am trying to use the below query with Hibernate's session.createSQLQuery. The Entity object corresponding to user has an attribute called address. The address object is created out of 5 fields from table 'user'. If I do not use an SQLQuery it gets filled auto-magically. However without the SQLQuery I can't get all the info I would get from the desired joins shown below. The user entity object also attributes like accessPlan which I am filling up using

.addEntity("accessPlan", AccessPlan.class)

Query:

SELECT 
    user.*, 
    ap.*,
    country.*,
    auth.*, 
    GROUP_CONCAT(coup.code SEPARATOR ' ') coupons
FROM 
    user
    INNER JOIN access_plan ap ON (user.access_plan = ap.id)
    INNER JOIN country ON (user.country=country.code)
    LEFT JOIN user_auth auth ON (user.id = auth.userid)
    LEFT JOIN (
        SELECT 
                    trans.user_id,coupon.code
        FROM 
                    payments_transaction AS trans
        INNER JOIN payments_coupon coupon ON (trans.payments_coupon_id=coupon.id)
    ) coup ON (user.id=coup.user_id)    
GROUP BY user.id;

What can be the easiest way to fill up the composed address object while using the SQLQuery? OR Is there a way to avoid using SQLQuery for a query like this?

BTW the result of this query should be a List. I am using .setResultTransformer(Transformers.aliasToBean(User.class)) to convert each record to User object — Vishal Gokhale, Aug 14 '15 at 10:46
Btw .. the atuo-magic part was actually the use of the @Embeddable annotation. — Vishal Gokhale, Aug 17 '15 at 05:46

score 0 · Answer 1 · answered Aug 24 '15 at 12:38

Please check below example from the section 'Returning multiple entities'

String sql = "SELECT ID as {c.id}, NAME as {c.name}, " + 
     "BIRTHDATE as {c.birthDate}, MOTHER_ID as {c.mother}, {mother.*} " +
     "FROM CAT_LOG c, CAT_LOG m WHERE {c.mother} = c.ID";

List loggedCats = sess.createSQLQuery(sql)
    .addEntity("cat", Cat.class)
    .addEntity("mother", Cat.class).list()

In your case, cat = user, mother = address... somewhat like that.

I do not have anything to try out at the moment but I guess this will help.

How to create mapping of composed object with Hibernate when using a complex sqlquery?

1 Answers1