Load only some columns using Hibernate built-in SQL queries

Question

I have a table in the database and I want to load only some of the columns from the result set, because the main object displayed in Hibernate is related to itself and the object is very large. I defined my own SQL query in the hbm file:

<sql-query name="query"> <return alias="r" class="RawData"/> <![CDATA[ SELECT DESCRIPTION as {r.description} FROM RAWD_RAWDATAS r WHERE r.RAWDATA_ID=? ]]> </sql-query> 

However, this query is not executed with an error: could not read the value of the column from the result set: RAWDATA1_14_0_; Invalid column name SQL error: 17006, SQLState: null because Hibernate is trying to load all fields from the result set. I also found an error in the Hibernate JIRA (http://opensource.atlassian.com/projects/hibernate/browse/HHH-3035). Does anyone know how to accomplish this task using a workaround?

Answer 1

Since you are using your own SQL query, you may need to use Transformer results:

http://docs.jboss.org/hibernate/core/3.5/api/org/hibernate/Query.html#setResultTransformer(org.hibernate.transform.ResultTransformer )

Edit: The JPA function for this is described here: http://download.oracle.com/javaee/5/api/javax/persistence/SqlResultSetMapping.html

Answer 2

If you want to select only a property, not an entity, specify <return-scalar...> , for example:

 <sql-query name="AdvertDisplayRule.fetchActiveRuleSet"> <return alias="ad" entity-name="com.wahanda.service.media.domain.Advert" /> <return-scalar column="view_count" type="integer" /> <![CDATA[ SELECT {ad.*}, avd.view_count FROM adv.advert {ad} LEFT OUTER JOIN adv.advert_view_distribution avd ON avd.advert_id = {ad}.advert_id ]]> </sql-query>