Search code examples
javahibernateobjecthql

HQL new List(..) within new Object(..)

I have requirement where in I would like to create a new object within HQL query. And one of the parameters to be provided in the new Object constructor is a list of some other objects.

eg:

SELECT new Object1(a.id, new List(SELECT b FROM table2 AS b WHERE b.id>0)) FROM table1 AS a;

So I would be getting an object of type Object1 which has a list retrieved from another table.

Please do help out..

Solution

  • You can't do it in one statement as you plan to. Think in a different way. There are some possibilities.

    1. You create the mapping for Object1 to the table1 and Object2 to table2. In the mapping of Object1 you create a one-to-many relation to Object2, and this relation contains the where condition "WHERE Object2.id>0" from your select. Then you only need the HQL statement "from Object1".
      This is the nicest and most hibernate-style possibility, but it only works if the where condition always is the same. And then you need a database relation between table1 and table2 (which you don't have in your example, but which probably exists and you only forgot it in your example).

      or

    2. You only load table1 first SELECT new Object1(a.id) FROM Table1 a and then in a loop over all elements of this list you load the second table FROM table2 b WHERE b.id>0 and put it into the element of type Object1.

      or

    3. You only load table1 first SELECT new Object1(a.id) FROM Table1 a and in Object1 you have a method getObject2List() which at the first call loads the list of elements from table2.