ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Carlos Botto" <carl...@qualitau.com>
Subject Problem trying to avoid N+1 selects
Date Tue, 15 May 2007 22:39:10 GMT
Hi all,

I'm new to iBatis and I'm coverting the existing code to implement iBatis,
so sorry in advance if my questions are too simple.
I have a problem trying to avoid the n+1 selects.
The following Application.xml works fine:

<sqlMap namespace="Application">

    <typeAlias alias="Application" type="
com.qualitau.authority.newbeans.Application"/>
    <typeAlias alias="ApplicationMenu" type="
com.qualitau.authority.newbeans.ApplicationMenu"/>

    <cacheModel id="application-cache" type="LRU">
        <flushInterval hours="24"/>
        <property name="size" value="1000"/>
    </cacheModel>

    <resultMap id="get-application-result" class="Application" groupBy="id">
        <result property="id"    column="app_id" javaType="int"/>
               <result property="name"  column="app_name"/>
               <result property="path"  column="app_path"/>
               <result property="menus" select="getApplicationMenu"
column="app_id"/>
        </resultMap>

    <resultMap id="get-applicationmenu-result" class="ApplicationMenu"
groupBy="{app_id,id}">
        <result property="app_id"  column="app_id"  javaType="int"/>
            <result property="id"      column="menu_id" javaType="int"/>
            <result property="name"    column="menu_desc"/>
            <result property="order"   column="menu_order" javaType="int"/>
            <result property="path"    column="menu_path"/>
            <result property="open"    column="menu_open"
javaType="boolean"/>
    </resultMap>

   <select id="getAllApplication" resultMap="get-application-result"
cacheModel="application-cache">
        SELECT * FROM authority.applications ORDER BY app_name
        </select>

   <select id="getApplicationMenu" parameterClass="int"
resultMap="get-applicationmenu-result" cacheModel="application-cache">
        SELECT * FROM authority.app_menu WHERE app_id = #id# ORDER BY
menu_order
   </select>

</sqlMap>


But when I  changed it to the code below, in order to avoid the N+1 selects
I got an error:

<sqlMap namespace="Application">

    <typeAlias alias="Application" type="
com.qualitau.authority.newbeans.Application"/>
    <typeAlias alias="ApplicationMenu" type="
com.qualitau.authority.newbeans.ApplicationMenu"/>

    <cacheModel id="application-cache" type="LRU">
        <flushInterval hours="24"/>
        <property name="size" value="1000"/>
    </cacheModel>

    <resultMap id="get-application-result" class="Application" groupBy="id">
                <result property="id"    column="app_id" javaType="int"/>
                <result property="name"  column="app_name"/>
                <result property="path"  column="app_path"/>
                <result property="menus" resultMap="
Application.get-applicationmenu-result"/>
        </resultMap>

    <resultMap id="get-applicationmenu-result" class="ApplicationMenu"
groupBy="{app_id,id}">
        <result property="app_id"  column="app_id"  javaType="int"/>
            <result property="id"      column="menu_id" javaType="int"/>
            <result property="name"    column="menu_desc"/>
            <result property="order"   column="menu_order" javaType="int"/>
            <result property="path"    column="menu_path"/>
            <result property="open"    column="menu_open"
javaType="boolean"/>
    </resultMap>

    <select id="getAllApplication" resultMap="get-application-result"
cacheModel="application-cache">
        SELECT a.app_id, a.app_name, a.app_path, m.app_id, m.menu_id,
m.menu_desc, m.menu_order, m.menu_path, m.menu_open
        FROM authority.applications a LEFT OUTER JOIN authority.app_menu m
ON m.app_id = a.app_id ORDER BY a.app_name
        </select>

        <select id="getApplicationMenu" parameterClass="int"
resultMap="get-applicationmenu-result" cacheModel="application-cache">
        SELECT * FROM authority.app_menu WHERE app_id = #id# ORDER BY
menu_order
        </select>

</sqlMap>


The error is:

com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/qualitau/authority/newbeans/sql/Application.xml.
--- The error occurred while applying a result map.
--- Check the Application.get-application-result.
--- The error happened while setting a property on the result object.
--- Cause: java.lang.RuntimeException: Error setting property 'setId' of '
com.qualitau.authority.newbeans.Application@35eed0'.  Cause:
java.lang.IllegalArgumentException
        at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
(GeneralStatement.java:188)
        at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
(GeneralStatement.java:123)
        at
com.ibatis.sqlmap.engine.mapping.statement.CachingStatement.executeQueryForList
(CachingStatement.java:97)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList
(SqlMapExecutorDelegate.java:615)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList
(SqlMapExecutorDelegate.java:589)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
SqlMapSessionImpl.java:118)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
SqlMapSessionImpl.java:122)
        at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(
SqlMapClientImpl.java:99)
        at com.qualitau.authority.newbeans.AuthoritySql.loadAllApps(
AuthoritySql.java:50)
        at com.qualitau.startup.JDBCStartup.contextInitialized(
JDBCStartup.java:125)
        at org.apache.catalina.core.StandardContext.listenerStart(
StandardContext.java:3827)
        at org.apache.catalina.core.StandardContext.start(
StandardContext.java:4343)
        at org.apache.catalina.core.StandardHostDeployer.start(
StandardHostDeployer.java:830)
        at org.apache.catalina.core.StandardHost.start(StandardHost.java
:991)
        at org.apache.catalina.manager.ManagerServlet.start(
ManagerServlet.java:1322)
        at org.apache.catalina.manager.ManagerServlet.doGet(
ManagerServlet.java:345)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(
ApplicationFilterChain.java:237)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(
ApplicationFilterChain.java:157)
        at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(
MonitorFilter.java:168)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(
ApplicationFilterChain.java:186)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(
ApplicationFilterChain.java:157)
        at org.apache.catalina.core.StandardWrapperValve.invoke(
StandardWrapperValve.java:214)
        at org.apache.catalina.core.StandardValveContext.invokeNext(
StandardValveContext.java:104)
        at org.apache.catalina.core.StandardPipeline.invoke(
StandardPipeline.java:520)
        at org.apache.catalina.core.StandardContextValve.invokeInternal(
StandardContextValve.java:198)
        at org.apache.catalina.core.StandardContextValve.invoke(
StandardContextValve.java:152)
        at org.apache.catalina.core.StandardValveContext.invokeNext(
StandardValveContext.java:104)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(
AuthenticatorBase.java:540)
        at org.apache.catalina.core.StandardValveContext.invokeNext(
StandardValveContext.java:102)
        at org.apache.catalina.core.StandardPipeline.invoke(
StandardPipeline.java:520)
        at org.apache.catalina.core.StandardHostValve.invoke(
StandardHostValve.java:137)
        at org.apache.catalina.core.StandardValveContext.invokeNext(
StandardValveContext.java:104)
        at org.apache.catalina.valves.ErrorReportValve.invoke(
ErrorReportValve.java:118)
        at org.apache.catalina.core.StandardValveContext.invokeNext(
StandardValveContext.java:102)
        at org.apache.catalina.core.StandardPipeline.invoke(
StandardPipeline.java:520)
        at org.apache.catalina.core.StandardEngineValve.invoke(
StandardEngineValve.java:109)
        at org.apache.catalina.core.StandardValveContext.invokeNext(
StandardValveContext.java:104)
        at org.apache.catalina.core.StandardPipeline.invoke(
StandardPipeline.java:520)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java
:929)
        at org.apache.coyote.tomcat5.CoyoteAdapter.service(
CoyoteAdapter.java:160)
        at org.apache.coyote.http11.Http11Processor.process(
Http11Processor.java:799)
        at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection
(Http11Protocol.java:705)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(
PoolTcpEndpoint.java:577)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(
ThreadPool.java:683)
        at java.lang.Thread.run(Thread.java:595)
Caused by: java.lang.RuntimeException: Error setting property 'setId' of '
com.qualitau.authority.newbeans.Application@35eed0'.  Cause:
java.lang.IllegalArgumentException
        at
com.ibatis.sqlmap.engine.accessplan.PropertyAccessPlan.setProperties(
PropertyAccessPlan.java:52)
        at com.ibatis.sqlmap.engine.exchange.JavaBeanDataExchange.setData(
JavaBeanDataExchange.java:112)
        at
com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.setResultObjectValues
(BasicResultMap.java:373)
        at
com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback.handleResultObject
(RowHandlerCallback.java:64)
        at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(
SqlExecutor.java:382)
        at
com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(
SqlExecutor.java:301)
        at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(
SqlExecutor.java:190)
        at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(
GeneralStatement.java:205)
        at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
(GeneralStatement.java:173)
        ... 46 more
Caused by: java.lang.IllegalArgumentException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(
NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(
DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at
com.ibatis.sqlmap.engine.accessplan.PropertyAccessPlan.setProperties(
PropertyAccessPlan.java:46)
        ... 54 more

What am I doing wrong?
Thanks


Carlos

Mime
View raw message