Select column value in Hibernate

[Edmund Yong]

I have a Hibernate mapping like this:

<hibernate-mapping>
  <class name="Classification" lazy="false" table="CLASSIFICATION">
    <id name="classificationID" column="CLASSIFICATION_ID">
      <generator class="increment"/>
    </id>
    ...
    <many-to-one name="product" class="Product" column="PRODUCT_ID"/>
  </class>
</hibernate-mapping>

Using the Hibernate query language, I can select the Product object. Is it possible to get the PRODUCT_ID value instead? That is, I want to get PRODUCT_ID value, not the Product object.

[Paul Sturrock]

Yes. In HQL it owuld be seomthing like this in your select statement:

Classification.Product.Id

When you explicitly ask for properties of objects in HQL is is an array of Objects that is returned, not the fully populated object itself. In this case you chould get an array with one Integer in it.

[Edmund Yong]

Hi Paul,

Do you mean Classification.PRODUCT_ID?

[Paul Sturrock]

No. In your mapping you have an associated object called Product, which must have a property of id. As you have it the only way to get the value of that directly is to refer to it via the Product object.

If you don't want to make reference to the product at all, you will need to add a read only property to your Classification mapping for the product_id.

Out of curiosity, why do you want to avoid refering to the Product object?

[Edmund Yong]

In the Classification mapping, there are a few associations with other objects like Product. It looks nice and pretty. But Hibernate actually makes a lot of joins, and the query looks big and slow. Now imagine if the Product mapping also have associations with other objects. Just by selecting a Classification, I end up with a big query joining so many tables.

This is the first time my colleagues and I are using Hibernate. This is a big problem for us. It is not easy to optimize at all.

[Paul Sturrock]

Its actually quite a common problem. I think you need to read the Hibernate documentation regarding lazy initialization.

If you use lazy initialization in the above mapping, and are only querying the id of product, the only table that will be queried is classification. Why? Rather than querying the product table and populating the properties of an object from database values, Hibernate creates a proxy object on the fly and populates its id based on the value in the foreign key column. There is a lot you can do with Hibernate with regards to query optimization. Its worth getting a copy of Hibernate In Action and reading about the more advanced features.

[Arun Kumarr]

Just by selecting a Classification, I end up with a big query joining so many tables.

There is a hibernate property setting to the rescue.
"hibernate.max_fetch_depth". You can set a numeric value for this and hibernate queries only till that level. Say you set it to 2, only 2 outerjoins are done by hibernate.