How to get slim list of objects with SQLQuery

hiron · **Joined:** Fri Sep 02, 2005 9:03 am **Posts:** 5

Hibernate version: 3.0.5

Mapping documents:
<hibernate-mapping>
<class name="emercom.locations.model.Location" table="DP_D_LOCATION">
[skip]
<many-to-one name="level"
column="SYDIC_LOCATION_LEVEL_ID"
not-null="true">
</many-to-one>
[skip]
</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="emercom.commons.model.Dictionary" table="SY_DICTIONARY">
[skip nothing intresting - just plain class]
</class>
</hibernate-mapping>

Code:

public class Location implements Serializable {
[skip]

  private Dictionary level;
[skip]
}
public class Dictionary implements Serializable {
  private Long id;
  private String name;
  private String code;
   [skip nothing intresting - just plain class]
}

I want to select Location and its lazy level property using SQLQuery
I found addJoin method and saw examples in test.
I tried this:

Code:

.createSQLQuery(
   "select {loc.*}, {dic.*} " +
   "from DP_D_LOCATION loc left outer join SY_DICTIONARY dic on (dic.SYDIC_ID = loc.SYDIC_LOCATION_LEVEL_ID)" +
   " start with DPLCN_LOCATION_ID = :incId connect by prior etc....")
   .addEntity("loc", Location.class)
   .addJoin("dic", "loc.level")
   .setLong("incId", locationId)
   .list();

It works fine - loads Location and lazy level property but it also loads Dictionary as separate entity
and returns List of Object[] {Location.class, Dictionary.class}
I want List of just Location.class

I tried to remove {dic.*}

Code:

session.createSQLQuery(
   "select {loc.*} " +
   "from DP_D_LOCATION loc left outer join SY_DICTIONARY dic on (dic.SYDIC_ID = loc.SYDIC_LOCATION_LEVEL_ID)" +
   " start with DPLCN_LOCATION_ID = :incId connect by prior DPLCN_PARENT_ID = DPLCN_LOCATION_ID and DPLCN_LOCATION_ID > 904"
   )
   .addEntity("loc", Location.class)
   .addJoin("dic", "loc.level")
   .setLong("incId", locationId)
   .list()

: could not execute query; bad SQL grammar
[
select loc.DPLCN_LOCATION_ID as DPLCN1_0_, loc.DPLCN_NAME as DPLCN2_14_0_, loc.DPLCN_CODE as DPLCN3_14_0_, loc.DPLCN_START_DATE as DPLCN4_14_0_, loc.DPLCN_FINISH_DATE as DPLCN5_14_0_, loc.DPLCN_NOTE as DPLCN6_14_0_,loc.DPLCN_PARENT_ID as DPLCN7_14_0_, loc.SYDIC_LOCATION_LEVEL_ID as SYDIC8_14_0_
from DP_D_LOCATION loc
left outer join SY_DICTIONARY dic on (dic.SYDIC_ID = loc.SYDIC_LOCATION_LEVEL_ID)
start with DPLCN_LOCATION_ID = ?
connect by prior DPLCN_PARENT_ID = DPLCN_LOCATION_ID and DPLCN_LOCATION_ID > 904
] //I executed this query - it works.
; nested exception is java.sql.SQLException: Invalid column name
[junit] java.sql.SQLException: Invalid column name
[junit] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseEr
ror.java:125)
it seems Hibernate want to access missing Dictionary(SY_DICTIONARY) columns, but I am not sure.

After that I removed .addJoin("dic", "loc.level")

Code:

session.createSQLQuery(
   "select {loc.*} " +
   "from DP_D_LOCATION loc left outer join SY_DICTIONARY dic on (dic.SYDIC_ID = loc.SYDIC_LOCATION_LEVEL_ID)" +
   " start with DPLCN_LOCATION_ID = :incId connect by prior DPLCN_PARENT_ID = DPLCN_LOCATION_ID and DPLCN_LOCATION_ID > 904"
   )
   .addEntity("loc", Location.class)
   .setLong("incId", locationId)
   .list()

But in such case Hibernate doesn't load level property at all
Then i called getLevel().getName()
I received
org.hibernate.LazyInitializationException

Is there any way to get List of just Locations rather than list of arrays of Objects?

pksiv · **Posted:** Fri Sep 02, 2005 1:21 pm

This "select {loc.*}, {dic.*} " is why you're getting an Object[].

change it to "select {loc.*}"

hiron · **Joined:** Fri Sep 02, 2005 9:03 am **Posts:** 5

pksiv wrote:

This "select {loc.*}, {dic.*} " is why you're getting an Object[].

change it to "select {loc.*}"

I actually did it but i got java.sql.SQLException: Invalid column name.
Please see initial post after

Quote:

I tried to remove {dic.*}

Third code section.

tferraz · **Posted:** Tue Sep 06, 2005 3:42 pm

you can do a "select new" statement. Create a new class with a constructor equal to the LOC class, lets say its name is LOCX, and use the constructor of LOCX in the select - you will also need to cast from LOCX to LOC, since the query will return a object of type LOCX.

select new LOCX(loc.l) from LOC loc where.....

in this case, an LOC object is initialized into the LOCX class, but you can do this by writing down all its fields.