You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Daniel Wintschel <hu...@gmail.com> on 2005/11/23 10:49:06 UTC

groupBy / N+1 problem

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!

Re: groupBy / N+1 problem

Posted by Clinton Begin <cl...@gmail.com>.
Did you upgrade the version of iBATIS that comes with JPetStore?

FYI for all:  I'm in the process of heavily refactoring JPetStore, as it's
in great need of some TLC.

Cheers,
Clinton


On 11/23/05, Daniel Wintschel <hu...@gmail.com> wrote:
>
> 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.List called "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!
>