I am having a problem trying to do a query with three tables. I am getting an out of memory error.
I have three tables, category, product and product_item. product and product_item is a one to many. category is linked to the product via a category_product table.
I am trying to get all the products and product items that are linked to a category.
If I were hand coding it, I would want the following sql. it returns 6 records.
select p.*,pi.* from store_product as p, store_category_product cp, store_category as cat, store_product_item as pi
where cat.category_id = 2 and cp.category_id = cat.category_id and cp.product_id = p.product_id and
p.product_id = pi.product_id
the sql that I am giving to hibernate is :
select new com.rhoderunner.store.catalog.BrowseProduct(product,productItem)
from Product as product,
ProductItem as productItem Category as category
left join category.products product
left join product.productItems productItem where
category.categoryId = :categoryId
It doesn't return, it gets an "Out of memory error". I put all the detail below.
Thanks for your help.
Hibernate version:
2.1.6
Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="com.rhoderunner.store.catalog.Category"
table="store_category"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="categoryId"
column="category_id"
type="long"
>
<generator class="native">
</generator>
</id>
<property
name="categoryName"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="categoryName"
/>
<list
name="products"
table="store_category_product"
lazy="true"
inverse="false"
cascade="none"
>
<key
column="category_id"
>
</key>
<index
column="product_order"
/>
<many-to-many
class="com.rhoderunner.store.catalog.Product"
column="product_id"
outer-join="auto"
/>
</list>
<property
name="visible"
type="boolean"
update="true"
insert="true"
access="property"
column="visible"
/>
<many-to-one
name="treeDescription"
class="com.rhoderunner.store.catalog.Description"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="tree_description_id"
/>
<many-to-one
name="headerDescription"
class="com.rhoderunner.store.catalog.Description"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="header_description_id"
/>
<many-to-one
name="subHeaderDescription"
class="com.rhoderunner.store.catalog.Description"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="sub_header_description_id"
/>
<many-to-one
name="treeImage"
class="com.rhoderunner.store.catalog.Image"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="tree_image_id"
/>
<many-to-one
name="headerImage"
class="com.rhoderunner.store.catalog.Image"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="header_image_id"
/>
<many-to-one
name="subHeaderImage"
class="com.rhoderunner.store.catalog.Image"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="sub_header_image_id"
/>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Category.xml
containing the additional properties and place it in your merge dir.
-->
</class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="com.rhoderunner.store.catalog.Product"
table="store_product"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="productId"
column="product_id"
type="long"
>
<generator class="native">
</generator>
</id>
<property
name="productName"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="productName"
/>
<set
name="categories"
table="store_product_category"
lazy="true"
inverse="false"
cascade="all-delete-orphan"
sort="unsorted"
>
<key
column="product_id"
>
</key>
<many-to-many
class="com.rhoderunner.store.catalog.Category"
column="category_id"
outer-join="auto"
/>
</set>
<list
name="productItems"
lazy="true"
inverse="false"
cascade="all-delete-orphan"
>
<key
column="product_id"
>
</key>
<index
column="item_line"
/>
<one-to-many
class="com.rhoderunner.store.catalog.ProductItem"
/>
</list>
<many-to-one
name="taxClass"
class="com.rhoderunner.store.tax.TaxClass"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="tax_class_id"
/>
<set
name="attributes"
table="store_product_attribute"
lazy="true"
inverse="false"
cascade="all-delete-orphan"
sort="unsorted"
>
<key
column="product_id"
>
</key>
<many-to-many
class="com.rhoderunner.store.catalog.Attribute"
column="attribute_id"
outer-join="auto"
/>
</set>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Product.xml
containing the additional properties and place it in your merge dir.
-->
</class>
</hibernate-mapping>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="com.rhoderunner.store.catalog.ProductItem"
table="store_product_item"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="productItemId"
column="product_item_id"
type="long"
>
<generator class="native">
</generator>
</id>
<many-to-one
name="product"
class="com.rhoderunner.store.catalog.Product"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="product_id"
/>
<list
name="prices"
lazy="true"
inverse="false"
cascade="none"
>
<key
column="product_item_id"
>
</key>
<index
column="price_line"
/>
<one-to-many
class="com.rhoderunner.store.pricing.Price"
/>
</list>
<property
name="productItemName"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="productItemName"
/>
<many-to-one
name="summaryDescription"
class="com.rhoderunner.store.catalog.Description"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="summary_description_id"
/>
<many-to-one
name="detailDescription"
class="com.rhoderunner.store.catalog.Description"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="detail_description_id"
/>
<many-to-one
name="summaryImage"
class="com.rhoderunner.store.catalog.Image"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="summary_image_id"
/>
<many-to-one
name="detailImage"
class="com.rhoderunner.store.catalog.Image"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="detail_image_id"
/>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-ProductItem.xml
containing the additional properties and place it in your merge dir.
-->
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
private void setBP(Session session) throws Exception {
String pq = "select "
+ " new com.rhoderunner.store.catalog.BrowseProduct(product,productItem) "
+ " from " + " Product as product, "
+ " ProductItem as productItem, " + " Category as category "
+ " left join category.products product "
+ " left join product.productItems productItem " + " where "
+ " category.categoryId = :categoryId ";
log2.debug("productQuery" + pq);
Query productQuery = session.createQuery(pq);
productQuery.setLong("categoryId", category.getCategoryId());
List list2 = productQuery.list();
setBrowseProducts(list2);
//filter closes session
}
Full stack trace of any exception that occurs:
Could not execute query
SQLState: S1000
errorCode: 72
messages: [Ljava.lang.String;@554189
throwableCount: 2
throwables: [Ljava.lang.Throwable;@141a32f
java.sql.SQLException
out of memory
SQLState: S1000
errorCode: 72
Stack Trace:
* org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
* org.hsqldb.jdbc.jdbcPreparedStatement.executeQuery(Unknown Source)
* net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
* net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
* net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
* net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
* net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
* net.sf.hibernate.loader.Loader.list(Loader.java:1024)
* net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
* net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
* net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
* com.rhoderunner.store.pages.shopper.ShopperHome.categorySelectAction(ShopperHome.java:147)
* sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
* sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
* sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
* java.lang.reflect.Method.invoke(Method.java:324)
* org.apache.tapestry.listener.ListenerMap.invokeTargetMethod(ListenerMap.java:257)
Name and version of the database you are using:
HSQL 1.7.2.2
The generated SQL (show_sql=true):
Hibernate: select product4_.product_id as product_id0_, productite5_.product_item_id as product_1_1_, product4_.productName as productN2_0_, product4_.tax_class_id as tax_clas3_0_, productite5_.product_id as product_id1_, productite5_.productItemName as productI3_1_, productite5_.summary_description_id as summary_4_1_, productite5_.detail_description_id as detail_d5_1_, productite5_.summary_image_id as summary_6_1_, productite5_.detail_image_id as detail_i7_1_ from store_product product0_, store_product_item productite1_, store_category category2_ left outer join store_category_product products3_ on category2_.category_id=products3_.category_id left outer join store_product product4_ on products3_.product_id=product4_.product_id left outer join store_product_item productite5_ on product4_.product_id=productite5_.product_id where (category2_.category_id=? )
Debug level Hibernate log excerpt:
Login
FAQ
Search