Trouble Loading a collection

pg015113 · **Joined:** Mon May 17, 2010 11:29 am **Posts:** 12

Hello all,

I am trouble again trying to get what I think is a pretty basic function working. Can anyone help me understand why I cannot get the result set for loading a collection on my domain object. The object has a Collection of attributes (Collection attributes). I am trying to load the collection using the following in my hbm file.

Quote:

I need to use the sql-query since I need to automatically map the instance columns however I can't seem to get the mappings right. I get a stacktrace letting me know that a column name is not valid (stracktrace at the end of this post).

Hibernate is using the following as my query when I dump the query to log. At least that is what it says it is using..

Code:

select distinct fab.attribute_number as number, 
  fab.attribute_string as name
from SF_Feature_Pipeline as fp
  inner join SF_Feature_base as fb 
    on fp.feature_unique_id = fb.feature_unique_id
  inner join Feature_Attributes_Inst_Values as faiv
    on faiv.Feature_Unique_ID = fb.Feature_Unique_ID 
  inner join Feature_Attributes_Base as fab
    on fab.Attribute_Number = faiv.Attribute_Number
where 
  fp.feature_number = ?
  and fb.Latest_Feature_Attributes_Instance = faiv.Instance

This all points to the result set but I can't seem to get the mapping correct. Can anyone help?

Thanks,

StrakeTrace

org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at org.hibernate.persister.collection.NamedQueryCollectionInitializer.initialize(NamedQueryCollectionInitializer.java:77)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:587)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:83)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1743)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:366)
at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:108)
at org.hibernate.collection.PersistentSet.iterator(PersistentSet.java:186)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:88)
at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)
at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1058)
at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:886)
at org.codehaus.groovy.runtime.InvokerHelper.invokePojoMethod(InvokerHelper.java:731)
at org.codehaus.groovy.runtime.InvokerHelper.invokeMethod(InvokerHelper.java:722)
at org.codehaus.groovy.runtime.InvokerHelper.asIterator(InvokerHelper.java:492)
at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1143)
at org.codehaus.groovy.runtime.dgm$108.invoke(Unknown Source)
at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:270)
at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:52)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
at com.cerner.ipsvc.feature.domain.FeatureTest.testGet(FeatureTest.groovy:15)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:76)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name Attribute1_10_0_ is not valid.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(SQLServerResultSet.java:626)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(SQLServerResultSet.java:2219)
at org.hibernate.type.IntegerType.get(IntegerType.java:51)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:173)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1121)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:588)
at org.hibernate.loader.Loader.doQuery(Loader.java:724)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 56 more

wolli · **Posted:** Sun Sep 19, 2010 4:53 am

I think those are wrong:

<return-property name="number" column="number" />
<return-property name="name" column="name" />

you don't need them and probably they cause the error.

pg015113 · **Joined:** Mon May 17, 2010 11:29 am **Posts:** 12

Thanks wolli but if I try to remove the return-properties then I get an exception for an unmapped column in the query instead of the result set.

Quote:

No column name found for property [number] for alias [fab] [select distinct fab.attribute_number as {fab.number},
fab.attribute_string as {fab.name}
from SF_Feature_Pipeline as fp
inner join SF_Feature_base as fb
on fp.feature_unique_id = fb.feature_unique_id
inner join Feature_Attributes_Inst_Values as faiv
on faiv.Feature_Unique_ID = fb.Feature_Unique_ID
inner join Feature_Attributes_Base as fab
on fab.Attribute_Number = faiv.Attribute_Number
where
fp.feature_number = :number
and fb.Latest_Feature_Attributes_Instance = faiv.Instance]

If I remove the return-properties and try to use the wildcard, then the query contains fields that I'm not requesting and don't exist on the aliased table fab. Not that it is trying to map fab.Feature_Number as a result. This column is not on the fab aliased table but is the <key> to the query. Also notice that the attribute number is duplicated. I'm not sure how else to define the result set.

Quote:

Hibernate: select distinct fab.Feature_Number as Feature3_0__, fab.Attribute_Number as Attribute1_0__, fab.Attribute_Number as Attribute1_10_0_, fab.Attribute_String as Attribute2_10_0_
from SF_Feature_Pipeline as fp
inner join SF_Feature_base as fb
on fp.feature_unique_id = fb.feature_unique_id
inner join Feature_Attributes_Inst_Values as faiv
on faiv.Feature_Unique_ID = fb.Feature_Unique_ID
inner join Feature_Attributes_Base as fab
on fab.Attribute_Number = faiv.Attribute_Number
where
fp.feature_number = ?
and fb.Latest_Feature_Attributes_Instance = faiv.Instance

pg015113 · **Joined:** Mon May 17, 2010 11:29 am **Posts:** 12

Well, I was able to get this to work if I specifically name the column that the result set is looking for but would much rather figure out why the collection is adding another column to the alias. After I added the {fab.key} mapping, it put me on the track to name the collection mappings, I'm just not sure what Hibernate is trying to map to the direct property on fab other than key. I tried fab.id but if anyone knows the correct way to map this, please share. Obviously the generated column mapping Attribute1_0__ is not safe but I don't have another option right now.

Code:

 <sql-query name="getFeatureAttributes">
        <load-collection alias="fab" role="com.cerner.ipsvc.feature.domain.Feature.attributes">
        </load-collection>
        <![CDATA[
        select 
            fab.attribute_number as Attribute1_0__,
            fp.feature_number as {fab.key},
            {fab.element.*}
        from Feature_Attributes_Base as fab
            inner join Feature_Attributes_Inst_Values as faiv
                on fab.Attribute_Number = faiv.Attribute_Number
            inner join SF_Feature_base as fb
                on faiv.Feature_Unique_ID = fb.Feature_Unique_ID
            inner join SF_Feature_Pipeline as fp
                on fp.feature_unique_id = fb.feature_unique_id
        where 
            fp.feature_number = :number
            and fb.Latest_Feature_Attributes_Instance = faiv.Instance
        ]]>
    </sql-query>

wolli · **Posted:** Tue Sep 21, 2010 2:53 am

Can you post the mapping for Attribute ?

pg015113 · **Joined:** Mon May 17, 2010 11:29 am **Posts:** 12

Thanks again wolli. Here is the attribute mapping.

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="true" auto-import="false">
    <class name="com.cerner.ipsvc.feature.domain.Attribute" table="Feature_Attributes_Base">
        <id name="number" column="Attribute_Number" />
        <property name="name" column="Attribute_String" />
    </class>
</hibernate-mapping>

wolli · **Posted:** Tue Sep 21, 2010 11:16 am

1) I think you at least need a generator in your id specification
http://nhforge.org/doc/nh/en/index.html#mapping-declaration-id

2) I can't see why and where fab.key comes from. There's no field like that in your mapping. Maybe key is an internal name that NHibernate knows, but I doubt that. In my opinion this should be number. From what I can see, it should be sth like this:

<sql-query name="getFeatureAttributes">
<load-collection alias="fab" role="com.cerner.ipsvc.feature.domain.Feature.attributes">
</load-collection>
<![CDATA[
select
fab.attribute_number as {fab.number},
... as {fab.name}

You statement looks a bit more complex that the mapping. Is the mapping complete ?