ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Wintschel <humando...@gmail.com>
Subject groupBy / N+1 problem
Date Wed, 23 Nov 2005 09:49:06 GMT
Hi all,

I've been fiddling around with JPetStore, trying to add an example that
performs a groupBy - in order to demonstrate the 'solution' to the N+1
problem. I have this working in a completely different application that I
wrote, but for some reason, this is giving me grief. I've been at it for
about 5 hours now, and I'm probably blind and stupid... So here goes:

I've gone in and added a property to the
com.ibatis.jpetstore.domain.Category class. The property is a
java.util.Listcalled "products" with the appropriate getProducts() and
setProducts(List)
methods.

I added two new ResultMap's into the Category.xml in order for me to query
for a Category that is populated with all Product's that are in a given
category. Here is the relevant portion of my new Category.xml file:

  <!-- Added a type alias for product class here - don't know whether that's
good practice or not, but it's what I did -->
  <typeAlias alias="categoryProduct" type="
com.ibatis.jpetstore.domain.Product "/>

  <resultMap id="categoryWithProducts" class="category"
groupBy="categoryId">
    <result property="categoryId" column="CATEGORY_ID"/>
    <result property="name" column="CATEGORY_NAME"/>
    <result property="description" column="CATEGORY_DESCN"/>
    <result property="products" resultMap=" Category.productsInCategory"/>
  </resultMap>

  <resultMap id="productsInCategory" class="categoryProduct">
    <result property="productId" column="PRODUCT_ID"/>
    <result property="categoryId" column="CATEGORY_ID"/>
    <result property="name" column="PRODUCT_NAME"/>
    <result property="description" column="PRODUCT_DESCN"/>
  </resultMap>


  <select id="getCategoryWithProducts" resultMap="categoryWithProducts"
parameterClass="string">
    select
        CATEGORY.CATID as CATEGORY_ID,
        CATEGORY.NAME as CATEGORY_NAME,
        CATEGORY.DESCN as CATEGORY_DESCN,
        PRODUCT.PRODUCTID as PRODUCT_ID,
        PRODUCT.NAME as PRODUCT_NAME,
        PRODUCT.DESCN as PRODUCT_DESCN
      from CATEGORY, PRODUCT
    where PRODUCT.CATEGORY = CATEGORY.CATID and CATEGORY.CATID = #value#
  </select>

Now, I've also added an appropriate method to the CategoryService,
CategoryDao etc. so I could execute the "getCategoryWithProducts" mapped
statement.

When I execute the mapped statement, it craps out every time, and I get the
following exception:
(sorry for the big trace, I've tried to snip it)

DEBUG [main] - Created connection 13926634.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement:      select
CATEGORY.CATID as CATEGORY_ID,         CATEGORY.NAME as
CATEGORY_NAME,         CATEGORY.DESCN as CATEGORY_DESCN,
PRODUCT.PRODUCTID as PRODUCT_ID,         PRODUCT.NAME as
PRODUCT_NAME,         PRODUCT.DESCN as PRODUCT_DESCN       from CATEGORY,
PRODUCT     where PRODUCT.CATEGORY = CATEGORY.CATID and CATEGORY.CATID = ?
DEBUG [main] - {pstm-100001} Parameters: [DOGS]
DEBUG [main] - {pstm-100001} Types: [java.lang.String]
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - Returned connection 13926634 to pool.
com.ibatis.dao.client.DaoException: Failed to execute queryForObject - id
[getCategoryWithProducts], parameterObject [DOGS].  Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/ibatis/jpetstore/persistence/sqlmapdao/sql/Category.xml.
--- The error occurred while applying a result map.
--- Check the Category.categoryWithProducts.
--- Check the result mapping for the 'products' property.
--- Cause: com.ibatis.sqlmap.client.SqlMapException: No type handler could
be found to map the property 'products' to the column 'null'.  One or both
of the types, or the combination of types is not supported.
Caused by: com.ibatis.sqlmap.client.SqlMapException: No type handler could
be found to map the property 'products' to the column 'null'.  One or both
of the types, or the combination of types is not supported.
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/ibatis/jpetstore/persistence/sqlmapdao/sql/Category.xml.
--- The error occurred while applying a result map.
--- Check the Category.categoryWithProducts.
--- Check the result mapping for the 'products' property.
--- Cause: com.ibatis.sqlmap.client.SqlMapException: No type handler could
be found to map the property 'products' to the column 'null'.  One or both
of the types, or the combination of types is not supported.
Caused by: com.ibatis.sqlmap.client.SqlMapException: No type handler could
be found to map the property 'products' to the column 'null'.  One or both
of the types, or the combination of types is not supported.
    at org.apache.tools.ant.taskdefs.ExecuteJava.execute(ExecuteJava.java
:180)

Now, I've tried EVERYTHING I could possibly think of... nine hundred
times... I have this exact same thing working (conceptually, but with
totally different object types) on another project... I have absolutely no
idea what I've messed up... Can anyone offer any clues?

Thanks!

Mime
View raw message