Returning multiple object types using hibernate using an inner join

Question

I seem to be having some difficulty with a query in hibernate. I am performing an inner join on two tables.

SELECT * FROM product p INNER JOIN warehouse w ON p.wid = w.id

Product Table:

id | name | wid | price | stock .....

Warehouse Table:

id | name | city | lat | long .....

The join result:

id | name | wid | price | stock | id | name | city | lat | long .....

When I run the query..

Session.createSQLQuery(this.query)
        .addEntity("p", Product.class)
        .addEntity("w", Warehouse.class).list();

So for every result I get an object containing a Product object and a Warehouse object.

This is expected. The issue is hibernate assigns the id and name of the product to the warehouse objects id and name property. Its as if the first two columns in the join result are over riding when it comes to creating the Warehouse project. The Product object always contains the correct data.

Any suggestion on finding a way around this issue so the id and name columns representing the correct Warehouse data would be much appreciated.

Thanks in advance.

Answer 1

Use the {} form to avoid problems with column name duplication:

SELECT {p.*}, {w.*} FROM product p INNER JOIN warehouse w ON p.wid = w.id

From Hibernate Reference Documentation, section 18.1.4. Returning multiple entities:

Until now, the result set column names are assumed to be the same as the column names specified in the mapping document. This can be problematic for SQL queries that join multiple tables, since the same column names can appear in more than one table.

Column alias injection is needed in the following query (which most likely will fail):

sess.createSQLQuery("SELECT c.*, m.*  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")
 .addEntity("cat", Cat.class)
 .addEntity("mother", Cat.class)

The query was intended to return two Cat instances per row: a cat and its mother. The query will, however, fail because there is a conflict of names; the instances are mapped to the same column names. Also, on some databases the returned column aliases will most likely be on the form "c.ID", "c.NAME", etc. which are not equal to the columns specified in the mappings ("ID" and "NAME").

The following form is not vulnerable to column name duplication:

sess.createSQLQuery("SELECT {cat.*}, {mother.*}  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")
 .addEntity("cat", Cat.class)
 .addEntity("mother", Cat.class)

This query specified:

the SQL query string, with placeholders for Hibernate to inject column aliases the entities returned by the query The {cat.*} and {mother.*} notation used above is a shorthand for "all properties".

Answer 2

Reference

In case the entities aren't from the same class, then here's a sample :

public static void main(String[] args) {
        Session sess = NewHibernateUtil.getSessionFactory().openSession();
        SQLQuery q = null;
        String query = "select a.*, u.* from user u, account a where a.iduser=u.iduser";
        q = sess.createSQLQuery(query);
        q.addEntity(User.class);
        q.addEntity(Account.class);
        List lst = q.list();
        System.out.println("" + lst.size());

        for (int i = 0; i < lst.size(); i++) {
            System.out.println(((Object[]) lst.get(i))[0]);     //account bean, actually this is in reverse order - so this is user bean
            System.out.println(((Object[]) lst.get(i))[1]);     //user bean         & this account bean
        }
        sess.close();
    }