Query A View Returned A Collection with Null Elements

jaschen · **Joined:** Fri Feb 15, 2008 11:40 am **Posts:** 1

Hi,

I am using Hibenate 3. I have a very simple view in a Oracle database. As I queried the view, I've got a collection with the right size but null element.

For instance, if the query is meant to return 50 rows, the collection has a size of 50 but each element is NULL.

Thank you so much in advance for your help.

Hibernate version: Hibernate 3.
Mapping document:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.co.cli.model.usoc">
   <class name="ProductView" table="PROD_WORKBOOK.CONSOLIDATED_VIEW">
            <composite-id name="productPK" class="ProductPK"> 
                        <key-property name="usoc" 
                           column="PRODUCT_CODE" type="string" /> 
                        <key-property name="stateCode" 
                           column="STATE_CODE"/>
            </composite-id> 

      <property name="serviceCodeLevel" column="SERVICE_CODE_LEVEL" type="string"/>
      <property name="serviceDesc" column="SERVICE_DESCRIPTION" type="string"/>
      <property name="serviceTypeDesc" column="SERVICE_TYPE_DESC" type="string"/>
   </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

String hql = "select prod from ProductView as prod " +
   "where prod.productPK.usoc = :usoc " +
   " and prod.productPK.stateCode = :usocState " +
   " and prod.productPK.childFidCode is not null " + 
   "order by prod.productPK.childFidCode asc" ;
      
   return getHibernateTemplate().findByNamedParam(         hql,            
                                new String[] {"usoc", "usocState"},         new String[]{usoc, usocState});

Full stack trace of any exception that occurs:
None

Name and version of the database you are using:
Oracle 10g

The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:

Code:

54:41,324 DEBUG ErrorCounter:68 - throwQueryException() : no errors
54:41,324 DEBUG QueryTranslatorImpl:216 - HQL: select prod from com.fairpoint.cli.model.usoc.ProductView as prod where prod.productPK.usoc = :usoc  and prod.productPK.stateCode = :usocState  and prod.productPK.childFidCode is not null order by prod.productPK.childFidCode asc
54:41,339 DEBUG QueryTranslatorImpl:217 - SQL: select productvie0_.PRODUCT_CODE as PRODUCT1_0_, productvie0_.STATE_CODE as STATE2_0_, productvie0_.CHILD_PRODUCT_CODE as CHILD3_0_, productvie0_.CHILD_PRODUCT_STATE_CODE as CHILD4_0_, productvie0_.SERVICE_RULE as SERVICE5_0_, productvie0_.CHILD_ATTRIBUTE_CODE as CHILD6_0_, productvie0_.SERVICE_CODE_LEVEL as SERVICE7_0_, productvie0_.SERVICE_DESCRIPTION as SERVICE8_0_, productvie0_.SERVICE_TYPE_DESC as SERVICE9_0_, productvie0_.SERVICE_SUBTYPE_DESC as SERVICE10_0_, productvie0_.RULE_TYPE as RULE11_0_, productvie0_.CHILD_SERVICE_CODE_LEVEL as CHILD12_0_, productvie0_.CHILD_SERVICE_DESCRIPTION as CHILD13_0_, productvie0_.CHILD_ATTRIBUTE_DESCRIPTION as CHILD14_0_ from PROD_WORKBOOK.CONSOLIDATED_VIEW productvie0_ where productvie0_.PRODUCT_CODE=? and productvie0_.STATE_CODE=? and (productvie0_.CHILD_ATTRIBUTE_CODE is not null) order by productvie0_.CHILD_ATTRIBUTE_CODE asc
54:41,339 DEBUG ErrorCounter:68 - throwQueryException() : no errors
54:41,355 DEBUG AbstractBatcher:366 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
54:41,355 DEBUG ConnectionManager:421 - opening JDBC connection
54:41,465 DEBUG SQL:401 - select productvie0_.PRODUCT_CODE as PRODUCT1_0_, productvie0_.STATE_CODE as STATE2_0_, productvie0_.CHILD_PRODUCT_CODE as CHILD3_0_, productvie0_.CHILD_PRODUCT_STATE_CODE as CHILD4_0_, productvie0_.SERVICE_RULE as SERVICE5_0_, productvie0_.CHILD_ATTRIBUTE_CODE as CHILD6_0_, productvie0_.SERVICE_CODE_LEVEL as SERVICE7_0_, productvie0_.SERVICE_DESCRIPTION as SERVICE8_0_, productvie0_.SERVICE_TYPE_DESC as SERVICE9_0_, productvie0_.SERVICE_SUBTYPE_DESC as SERVICE10_0_, productvie0_.RULE_TYPE as RULE11_0_, productvie0_.CHILD_SERVICE_CODE_LEVEL as CHILD12_0_, productvie0_.CHILD_SERVICE_DESCRIPTION as CHILD13_0_, productvie0_.CHILD_ATTRIBUTE_DESCRIPTION as CHILD14_0_ from PROD_WORKBOOK.CONSOLIDATED_VIEW productvie0_ where productvie0_.PRODUCT_CODE=? and productvie0_.STATE_CODE=? and (productvie0_.CHILD_ATTRIBUTE_CODE is not null) order by productvie0_.CHILD_ATTRIBUTE_CODE asc
54:42,122 DEBUG AbstractBatcher:382 - about to open ResultSet (open ResultSets: 0, globally: 0)
54:42,122 DEBUG Loader:1173 - result row: null
54:42,122 DEBUG Loader:1173 - result row: null

pprabhu · **Joined:** Mon Mar 10, 2008 5:46 pm **Posts:** 1

Try to remove the composite id....if you have all the properties in the composite id (in teh case of a view....this is appropriate. ) .. it wont work...

I had the same problem ...this fixed it. I managed to get my architect to let me implement at least one property out of the composite id.