I am defined SQL query in mapping class and calling this query for loading the collection. I have sequentially geenerated psudo column for the idx for list index in the query. When I tried to execute the application , that throw me always an exception that Not all named parameters have been set . There is only one parameter that is evidently in the query. The data base is mysql so I cant either use a rownum or rowid. Can anyone possibly help to sort this issue?

The mapping of the class is given below.


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

<!-- Generated Aug 14, 2006 6:24:11 PM by Hibernate Tools 3.1.0.beta5 -->
<!-- Catalog mapping-->

<hibernate-mapping>



<class name="com.catalog.domain.dto.persistent.Catalog" table="c_catalog" where="deleted=0">
<id name="id" type="string">
<column name="catalogId" sql-type="char(32)" not-null="true" length="32"/>
<generator class="uuid.hex"/>
</id>



<property name="name" type="string" column="name"/>
<property name="catalogKey" type="string" column="userKey"/>

<property name="description" type="string" column="description"/>

<property name="enabled" type="boolean" column="status"/>
<property name="startDate" type="date" column="startDate" />
<property name="endDate" type="date" column="endDate" />

<property name="thumbnailImagePath" type="string" column="thumbimagename" />
<property name="largeImagePath" type="string" column="bigimagename" />
<property name="deleted" type="boolean" column="deleted" />
<property name="taxCommodityId" type="string" column="taxCommodityId" />

<list name="categories" table="c_catalog_category_mapping" cascade="save-update" lazy="false">
<key column="catalogId" />
<index column="idx"/>
<many-to-many column="categoryId" class="com.catalog.domain.dto.persistent.Category" />
<!-- Remove the commented tag below -->
<loader query-ref="categoryCollection"/>

</list>

<sql-delete> update c_client_catalog_mapping cm set cm.deleted=1 where cm.catalogId=? </sql-delete>



</class>
<sql-query name="categoryCollection">
<load-collection alias="catgy" role="com.catalog.domain.dto.persistent.Catalog.categories"/>
<![CDATA[SELECT ca.categoryId, ca.name, @rownum:=@rownum+1 as idx, ca.parentCategoryId, ca.userKey, ca.description, ca.status, ca.thumbimagename, ca.bigimagename, ca.deleted, ca.taxCommodityId, cm.catalogId FROM c_category ca, c_catalog_category_mapping cm, (SELECT @rownum:=-1) R WHERE ca.categoryId = cm.categoryId and ca.deleted = 0 and cm.catalogId = :id]]>
</sql-query>


</hibernate-mapping>



The exception is given below.

org.springframework.orm.hibernate3.HibernateQueryException: Not all named parameters have been set: [] [SELECT ca.categoryId, ca.name, @rownum:=@rownum+1 as idx, ca.parentCategoryId, ca.userKey, ca.description, ca.status, ca.thumbimagename, ca.bigimagename, ca.deleted, ca.taxCommodityId, cm.catalogId FROM c_category ca, c_catalog_category_mapping cm, (SELECT @rownum:=-1) R WHERE ca.categoryId = cm.categoryId and ca.deleted = 0 and cm.catalogId = :id]; nested exception is org.hibernate.QueryException: Not all named parameters have been set: [] [SELECT ca.categoryId, ca.name, @rownum:=@rownum+1 as idx, ca.parentCategoryId, ca.userKey, ca.description, ca.status, ca.thumbimagename, ca.bigimagename, ca.deleted, ca.taxCommodityId, cm.catalogId FROM c_category ca, c_catalog_category_mapping cm, (SELECT @rownum:=-1) R WHERE ca.categoryId = cm.categoryId and ca.deleted = 0 and cm.catalogId = :id]
org.hibernate.QueryException: Not all named parameters have been set: [] [SELECT ca.categoryId, ca.name, @rownum:=@rownum+1 as idx, ca.parentCategoryId, ca.userKey, ca.description, ca.status, ca.thumbimagename, ca.bigimagename, ca.deleted, ca.taxCommodityId, cm.catalogId FROM c_category ca, c_catalog_category_mapping cm, (SELECT @rownum:=-1) R WHERE ca.categoryId = cm.categoryId and ca.deleted = 0 and cm.catalogId = :id]
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:284)
at org.hibernate.impl.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:214)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
at org.hibernate.persister.collection.NamedQueryCollectionInitializer.initialize(NamedQueryCollectionInitializer.java:54)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:520)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1676)
at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:454)
at org.hibernate.engine.StatefulPersistenceContext.initializeNonLazyCollections(StatefulPersistenceContext.java:755)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:229)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1106)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate$31.doInHibernate(HibernateTemplate.java:853)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:844)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:840)
at com.xonos.ecommerce.manage.svc.ManageServiceImpl.getClientById(ManageServiceImpl.java:90)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:287)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
at $Proxy25.getClientById(Unknown Source)

I have a similar problem that Hibernate wants me to declare the parameter @rownum in SELECT @rownum:=-1 as it is just a sql variable.

I would like to know how to avoid that too. Thanks.