I posted this question in the Miscellaneous forum by mistake. I think it belongs under the Beginners section. Sorry about that.

I am using Hibernate 2.1.2 with Resin 2.1.12 and SAPDB 7.4.

The following is my object structure.

public class Catalog
{
Long catalogId = null;
String catalogName = "";
...
...
}

public class Market
{
Long marketId = null;
String marketName = "";
...
...
}

public class CatalogMarket
{
Long catalogMarketId = null;
Catalog catalog = null;
Market market = null;
...
...
}


The corresponding mapping for it is as follows:

<hibernate-mapping>
<class name="Catalog" table="CATALOGS" dynamic-update="false" dynamic-insert="false">
<id name="catalogId" column="CATALOGID" type="java.lang.Long" unsaved-value="null">
<generator class="sequence">
<param name="sequence">SEQ_CATALOGID</param>
</generator>
</id>
<property name="catalogName" type="java.lang.String" update="true" insert="true" column="CATALOGNAME" not-null="true"/>
</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="Market" table="MARKETS" dynamic-update="false" dynamic-insert="false">
<id name="marketId" column="MARKETID" type="java.lang.Long" unsaved-value="null">
<generator class="sequence">
<param name="sequence">SEQ_MARKETID</param>
</generator>
</id>
<property name="marketName" type="java.lang.String" update="true" insert="true" column="MARKETNAME" not-null="true"/>
</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="CatalogMarket" table="CATALOGMARKETS" dynamic-update="false" dynamic-insert="false">
<id name="catalogMarketId" column="CATALOGMARKETID" type="java.lang.Long" unsaved-value="null">
<generator class="sequence">
<param name="sequence">SEQ_CATALOGMARKETID</param>
</generator>
</id>
<many-to-one name="catalog" class="Catalog" cascade="none" outer-join="true" update="false" insert="true" column="CATALOGID" not-null="true" unique="false"/>
<many-to-one name="market" class="Market" cascade="none" outer-join="true" update="false" insert="true" column="MARKETID" not-null="true" unique="false"/>
</class>
</hibernate-mapping>


Notice that I have outer-join set to TRUE for both the many-to-one relationships in the CatalogMarket object.

The following is my Hibernate code.


StringBuffer sbQuery = new StringBuffer();
Query query = null;
hibernateSession = HibernateManager.currentSession();

sbQuery.append("SELECT catalogMarket FROM CatalogMarket catalogMarket WHERE 1=1");
sbQuery.append(" AND catalogMarket.catalog.catalogId = :catalogId");
sbQuery.append(" ORDER BY catalogMarket.market.marketName, catalogMarket.catalog.catalogName");
query = hibernateSession.createQuery(sbQuery.toString());
query.setParameter("catalogId", requestCatalogMarket.getCatalog().getCatalogId(), Hibernate.LONG);
if (query != null)
{
catalogMarketList = query.list();
}



The problem is that there are roughly 900+ Market objects that are related to each Catalog. Consequently, Hibernate executes the query for the CatalogMarket first and then executes the query to populate the Market object 900+ times.

Executed Once
select catalogmar0_.CATALOGMARKETID as CATALOGM1_, catalogmar0_.CATALOGID as CATALOGID, catalogmar0_.MARKETID as MARKETID, catalogmar0_.CREATEBY as CREATEBY, catalogmar0_.CREATEON as CREATEON from CATALOGMARKETS catalogmar0_, MARKETS market1_, CATALOGS catalog2_ where catalogmar0_.MARKETID=market1_.MARKETID and catalogmar0_.CATALOGID=catalog2_.CATALOGID and ((1=1 )AND(catalogmar0_.CATALOGID=? )) order by market1_.MARKETNAME , catalog2_.CATALOGNAME

Executed Once
select catalog0_.CATALOGID as CATALOGID0_, catalog0_.CATALOGNAME as CATALOGN2_0_, catalog0_.CATALOGDESCRIPTION as CATALOGD3_0_, catalog0_.ACTIVE as ACTIVE0_, catalog0_.CUSTOMERCODE as CUSTOMER5_0_, catalog0_.CREATEBY as CREATEBY0_, catalog0_.CREATEON as CREATEON0_, catalog0_.UPDATEBY as UPDATEBY0_, catalog0_.UPDATEON as UPDATEON0_ from CATALOGS catalog0_ where catalog0_.CATALOGID=?

Executed 900+ Times
select market0_.MARKETID as MARKETID0_, market0_.MARKETNAME as MARKETNAME0_, market0_.NEWCODEVALUE as NEWCODEV3_0_, market0_.UPGRADECODEVALUE as UPGRADEC4_0_, market0_.MARKETREFERENCECODE as MARKETRE5_0_, market0_.ACTIVE as ACTIVE0_, market0_.CREATEBY as CREATEBY0_, market0_.CREATEON as CREATEON0_, market0_.UPDATEBY as UPDATEBY0_, market0_.UPDATEON as UPDATEON0_ from MARKETS market0_ where market0_.MARKETID=?

Consequently, I am getting the following error message from my JNDI source.

[08:16:07.217] allocating pool item: [PoolItem 14 hibernate/sapdb/ active]
[08:16:09.014] connection not recycled because of statement overflow [PoolItem 14 hibernate/sapdb/ active]
[08:16:38.295] close 14:hibernate/sapdb/


Is there any way that I can get the Market objects to be populated by executing the query once? Should I have the HQL get a list of Market objects instead of CatalogMarket objects? I know this is a long post but I hope someone actually reads it and helps out.

TIA.

http://forum.hibernate.org/viewtopic.php?t=930972

i've replied, try it

Thank you very much. Worked like a charm.