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 Carlos Botto <ca...@qualitau.com> on 2007/05/16 00:39:10 UTC
Problem trying to avoid N+1 selects
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
Re: Problem trying to avoid N+1 selects
Posted by Carlos Botto <ca...@qualitau.com>.
Thanks for your help, the problem was with the null values in the left join.
Carlos
On 5/16/07, Stefan Langer <ma...@googlemail.com> wrote:
>
> Larry Meadors schrieb:
> > On 5/16/07, Stefan Langer <ma...@googlemail.com> wrote:
> >> See issue https://issues.apache.org/jira/browse/IBATIS-375 and please
> >> vote for it if you find it annoying.
> >
> > ...or even better, get the code from SVN and attach a patch. :)
> >
> > Larry
> >
> If I could find the time I would go for that solution ;) but as always
> so much to do so few time...
> Stefan
>
>
Re: Problem trying to avoid N+1 selects
Posted by Larry Meadors <lm...@apache.org>.
Well, if you want to hire me to fix it, I'll add you to my client list
and get you on the schedule. If it doesn't annoy anyone that much,
we'll have to wait for someone who needs it bad enough to fix it.
:-D
Larry
On 5/16/07, Stefan Langer <ma...@googlemail.com> wrote:
> Larry Meadors schrieb:
> > On 5/16/07, Stefan Langer <ma...@googlemail.com> wrote:
> >> See issue https://issues.apache.org/jira/browse/IBATIS-375 and please
> >> vote for it if you find it annoying.
> >
> > ...or even better, get the code from SVN and attach a patch. :)
> >
> > Larry
> >
> If I could find the time I would go for that solution ;) but as always
> so much to do so few time...
> Stefan
>
Re: Problem trying to avoid N+1 selects
Posted by Stefan Langer <ma...@googlemail.com>.
Larry Meadors schrieb:
> On 5/16/07, Stefan Langer <ma...@googlemail.com> wrote:
>> See issue https://issues.apache.org/jira/browse/IBATIS-375 and please
>> vote for it if you find it annoying.
>
> ...or even better, get the code from SVN and attach a patch. :)
>
> Larry
>
If I could find the time I would go for that solution ;) but as always
so much to do so few time...
Stefan
Re: Problem trying to avoid N+1 selects
Posted by Larry Meadors <lm...@apache.org>.
On 5/16/07, Stefan Langer <ma...@googlemail.com> wrote:
> See issue https://issues.apache.org/jira/browse/IBATIS-375 and please
> vote for it if you find it annoying.
...or even better, get the code from SVN and attach a patch. :)
Larry
Re: Problem trying to avoid N+1 selects
Posted by Stefan Langer <ma...@googlemail.com>.
Not sure it is related to your issue but be aware that when you do a
left outer join and you have no results for the left join you will still
receive a row containing only nulls for your left joined object. IBatis
will try to fill up your object with these null values. If your code for
some reason prohibits this it will lead to errors.
See issue https://issues.apache.org/jira/browse/IBATIS-375 and please
vote for it if you find it annoying.