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 Kevin <ke...@yahoo.com> on 2004/12/10 07:15:44 UTC
Changing result set metadata on the fly.
Hi
I just found out from someone that Ibatis does not
allow changing the resultset metadata on the fly.
This does not allow me to have the same query for
count of records as well as getting the result set. Is
there any alternate way ibatis does something like
this.
It is not a big issue considering the power of the
what Ibatis actually provides , but was curious to
learn the reason for leaving this out.
Thanks
Kevin
__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250
RE: Avoiding N+1 with complex HashMap properties.
Posted by Jerome Jacobsen <je...@gentootech.com>.
Darn typo! Option 2 should say:
HashMap customer = customerDao.getCustomer(id);
HashMap address = customer.get("address");
out.println("customer state = " + address.get("state"));
customer.get("address") instead of customerDao.get("address")
We're getting the 'address' HashMap from the 'customer'
HashMap.
> -----Original Message-----
> From: Jerome Jacobsen [mailto:jerome.jacobsen@gentootech.com]
> Sent: Friday, December 10, 2004 12:49 PM
> To: ibatis-user-java@incubator.apache.org
> Subject: RE: Avoiding N+1 with complex HashMap properties.
>
>
> Just to be clear, here are examples of what I'm talking about.
>
> Option 1: Single hashmap with indexed property style keys.
>
> HashMap customer = customerDao.getCustomer(id);
> out.println("customer state = " + customer.get("address.state"));
>
> Option 2: Nested hashmaps.
>
> HashMap customer = customerDao.getCustomer(id);
> HashMap address = customerDao.get("address");
> out.println("customer state = " + address.get("state"));
>
>
> I have no preference as to which way is supported, as long as:
> - The exception doesn't occur.
> - I can access the properties using the 'indexed property'
> style in JSP.
>
> I'm not sure how JSTL/JSP would handle the nested HashMap versus
> handling the single HashMap.
>
> In JSP not sure if I can do this for both implementations
> above:
>
> Customer State = <c:out value="${customer.address.state}"/>
>
> If JSP/JSTL only supports one of these implementation options
> then that would be my preference for ibatis too.
>
> Regards,
>
> Jerome
>
> > -----Original Message-----
> > From: Clinton Begin [mailto:clinton.begin@gmail.com]
> > Sent: Friday, December 10, 2004 10:09 AM
> > To: jerome.jacobsen@gentootech.com
> > Cc: ibatis-user-java@incubator.apache.org
> > Subject: Re: Avoiding N+1 with complex HashMap properties.
> >
> >
> > Tough call. Some people want it stored as a single property (e.g.
> > imagine the key is a classname), and others want it stored nested.
> >
> > Currently, as you've discovered, it's not nested.
> >
> > I would suggest that if you have a complex object model, then you
> > should model it using JavaBeans. Maps are very loose, unpredictable
> > and limited.
> >
> > Cheers,
> > Clinton
> >
> >
> > On Fri, 10 Dec 2004 10:03:16 -0500, Jerome Jacobsen
> > <je...@gentootech.com> wrote:
> > > Hello,
> > >
> > > I'm using SQL Maps 2.08. I was hoping I could do the following
> > > but I get a ProbeException. Should this be supported?
> > >
> > > <resultMap id="get-customer-period-result"
> > > class="java.util.HashMap">
> > > <result property="customerNum"
> > > column="CUSTOMER_NUMBER"
> > > columnIndex="1"/>
> > > <result property="category.id"
> > > column="CATEGORY_ID"
> > > columnIndex="2"/>
> > > <result property="category.parentId"
> > > column="PARENT_CATEGORY_ID"
> > > columnIndex="3"/>
> > > <result property="category.description"
> > > column="CATEGORY_DESC"
> > > columnIndex="4"/>
> > > ...
> > > </resultMap>
> > >
> > > <statement id="getCustomerPeriodSummary"
> > > resultMap="get-customer-period-result">
> > > SELECT CPS.CUSTOMER_NUMBER,
> > > PC.CATEGORY_ID,
> > > PC.PARENT_CATEGORY_ID,
> > > PC.DESCRIPTION AS CATEGORY_DESC,
> > > PC.SEQUENCE AS CATEGORY_SEQUENCE,
> > > ...
> > > FROM CUSTOMER_PERIOD_SUMMARY CPS, PRODUCT_CATEGORY PC,
> > SALES_PERIOD SP
> > > WHERE CPS.CUSTOMER_NUMBER = #value#
> > > AND CPS.CATEGORY_ID = PC.CATEGORY_ID
> > > AND CPS.PERIOD_ID = SP.PERIOD_ID
> > > ORDER BY CATEGORY_SEQUENCE
> > > </statement>
> > >
> > > I was hoping that sqlmaps would store the 'category.id',
> > > 'category.parentId', 'category.description' either in a nested
> > > HashMap (key of 'category') or store the values in the top
> > > level HashMap with those keys ('category.id', etc.)
> > >
> > > However neither case appears to happen and I get the
> > > exception below. I realize that I can map the complex
> > > properties with separate selects but I want to avoid the
> > > N+1 query problem. So I do the join instead.
> > >
> > > com.ibatis.common.beans.ProbeException: There is no WRITEABLE
> > property named
> > > 'id' in class 'java.lang.Object'
> > > at
> > com.ibatis.common.beans.ClassInfo.getSetter(ClassInfo.java:146)
> > > at
> > >
> > com.ibatis.common.beans.ComplexBeanProbe.setProperty(ComplexBeanPr
> > obe.java:3
> > > 34)
> > > at
> > >
> > com.ibatis.common.beans.ComplexBeanProbe.setObject(ComplexBeanProb
> > e.java:231
> > > )
> > > at
> > com.ibatis.common.beans.GenericProbe.setObject(GenericProbe.java:69)
> > > at
> > >
> > com.ibatis.sqlmap.engine.exchange.ComplexDataExchange.setData(Comp
> > lexDataExc
> > > hange.java:87)
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.setResultOb
> > jectValues
> > > (BasicResultMap.java:231)
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback.hand
> > leResultOb
> > > ject(RowHandlerCallback.java:63)
> > > at
> > >
> > com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlEx
> > ecutor.jav
> > > a:350)
> > > at
> > >
> > com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExe
> > cutor.java
> > > :179)
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExe
> > cuteQuery(
> > > GeneralStatement.java:200)
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.execut
> > eQueryWith
> > > Callback(GeneralStatement.java:168)
> > > at
> > >
> > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.execut
> > eQueryForL
> > > ist(GeneralStatement.java:118)
> > > at
> > >
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> > SqlMapExec
> > > utorDelegate.java:626)
> > > at
> > >
> > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> > SqlMapExec
> > > utorDelegate.java:598)
> > > at
> > >
> > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMa
> > pSessionIm
> > > pl.java:107)
> > > at
> > >
> > org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapCl
> > ient(SqlMa
> > > pClientTemplate.java:202)
> > > at
> > >
> > org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMap
> > ClientTemp
> > > late.java:142)
> > > at
> > >
> > org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithLis
> > tResult(Sq
> > > lMapClientTemplate.java:164)
> > > at
> > >
> > org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(S
> > qlMapClien
> > > tTemplate.java:200)
> > > at
> > >
> > com.giv.dashboard.dao.db.ibatis.SqlMapSalesStatisticsDAO.getCatego
> > ryStatsFor
> > > Customer(SqlMapSalesStatisticsDAO.java:28)
> > > at
> > >
> > com.giv.dashboard.DashboardImpl.getCategorySummaries(DashboardImpl
> > .java:42)
> > > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > > at
> > >
> > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorIm
> > pl.java:39
> > > )
> > > at
> > >
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAc
> > cessorImpl
> > > .java:25)
> > > at java.lang.reflect.Method.invoke(Method.java:324)
> > > at
> > >
> > org.springframework.aop.support.AopUtils.invokeJoinpointUsingRefle
> > ction(AopU
> > > tils.java:295)
> > > at
> > >
> > org.springframework.aop.framework.ReflectiveMethodInvocation.invok
> > eJoinpoint
> > > (ReflectiveMethodInvocation.java:154)
> > > at
> > >
> > org.springframework.aop.framework.ReflectiveMethodInvocation.proce
> > ed(Reflect
> > > iveMethodInvocation.java:121)
> > > at
> > >
> > org.springframework.transaction.interceptor.TransactionInterceptor
> > .invoke(Tr
> > > ansactionInterceptor.java:56)
> > > at
> > >
> > org.springframework.aop.framework.ReflectiveMethodInvocation.proce
> > ed(Reflect
> > > iveMethodInvocation.java:143)
> > > at
> > >
> > org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDyn
> > amicAopPro
> > > xy.java:174)
> > > at $Proxy0.getCategorySummaries(Unknown Source)
> > > at
> > >
> > com.giv.dashboard.web.spring.ViewCategoriesController.handleReques
> > tInternal(
> > > ViewCategoriesController.java:62)
> > > at
> > >
> > org.springframework.web.servlet.mvc.AbstractController.handleReque
> > st(Abstrac
> > > tController.java:128)
> > > at
> > >
> > org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter
> > .handle(Si
> > > mpleControllerHandlerAdapter.java:44)
> > > at
> > >
> > org.springframework.web.servlet.DispatcherServlet.doService(Dispat
> > cherServle
> > > t.java:532)
> > > at
> > >
> > org.springframework.web.servlet.FrameworkServlet.serviceWrapper(Fr
> > ameworkSer
> > > vlet.java:366)
> > > at
> > >
> > org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkSe
> > rvlet.java
> > > :317)
> > > at
> javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> > > at
> javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > > at
> > >
> > com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilt
> > erChain.ja
> > > va:65)
> > > at
> oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source)
> > > at
> > >
> > com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRe
> > questDispa
> > > tcher.java:604)
> > > at
> > >
> > com.evermind.server.http.ServletRequestDispatcher.forwardInternal(
> > ServletReq
> > > uestDispatcher.java:317)
> > > at
> > >
> > com.evermind.server.http.HttpRequestHandler.processRequest(HttpReq
> > uestHandle
> > > r.java:790)
> > > at
> > >
> > com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler
> > .java:270)
> > > at
> > >
> > com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler
> > .java:112)
> > > at
> > >
> > com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(Re
> > leasableRe
> > > sourcePooledExecutor.java:192)
> > > at java.lang.Thread.run(Thread.java:534)
> > >
> > >
> >
RE: Avoiding N+1 with complex HashMap properties.
Posted by Jerome Jacobsen <je...@gentootech.com>.
Just to be clear, here are examples of what I'm talking about.
Option 1: Single hashmap with indexed property style keys.
HashMap customer = customerDao.getCustomer(id);
out.println("customer state = " + customer.get("address.state"));
Option 2: Nested hashmaps.
HashMap customer = customerDao.getCustomer(id);
HashMap address = customerDao.get("address");
out.println("customer state = " + address.get("state"));
I have no preference as to which way is supported, as long as:
- The exception doesn't occur.
- I can access the properties using the 'indexed property'
style in JSP.
I'm not sure how JSTL/JSP would handle the nested HashMap versus
handling the single HashMap.
In JSP not sure if I can do this for both implementations
above:
Customer State = <c:out value="${customer.address.state}"/>
If JSP/JSTL only supports one of these implementation options
then that would be my preference for ibatis too.
Regards,
Jerome
> -----Original Message-----
> From: Clinton Begin [mailto:clinton.begin@gmail.com]
> Sent: Friday, December 10, 2004 10:09 AM
> To: jerome.jacobsen@gentootech.com
> Cc: ibatis-user-java@incubator.apache.org
> Subject: Re: Avoiding N+1 with complex HashMap properties.
>
>
> Tough call. Some people want it stored as a single property (e.g.
> imagine the key is a classname), and others want it stored nested.
>
> Currently, as you've discovered, it's not nested.
>
> I would suggest that if you have a complex object model, then you
> should model it using JavaBeans. Maps are very loose, unpredictable
> and limited.
>
> Cheers,
> Clinton
>
>
> On Fri, 10 Dec 2004 10:03:16 -0500, Jerome Jacobsen
> <je...@gentootech.com> wrote:
> > Hello,
> >
> > I'm using SQL Maps 2.08. I was hoping I could do the following
> > but I get a ProbeException. Should this be supported?
> >
> > <resultMap id="get-customer-period-result"
> > class="java.util.HashMap">
> > <result property="customerNum"
> > column="CUSTOMER_NUMBER"
> > columnIndex="1"/>
> > <result property="category.id"
> > column="CATEGORY_ID"
> > columnIndex="2"/>
> > <result property="category.parentId"
> > column="PARENT_CATEGORY_ID"
> > columnIndex="3"/>
> > <result property="category.description"
> > column="CATEGORY_DESC"
> > columnIndex="4"/>
> > ...
> > </resultMap>
> >
> > <statement id="getCustomerPeriodSummary"
> > resultMap="get-customer-period-result">
> > SELECT CPS.CUSTOMER_NUMBER,
> > PC.CATEGORY_ID,
> > PC.PARENT_CATEGORY_ID,
> > PC.DESCRIPTION AS CATEGORY_DESC,
> > PC.SEQUENCE AS CATEGORY_SEQUENCE,
> > ...
> > FROM CUSTOMER_PERIOD_SUMMARY CPS, PRODUCT_CATEGORY PC,
> SALES_PERIOD SP
> > WHERE CPS.CUSTOMER_NUMBER = #value#
> > AND CPS.CATEGORY_ID = PC.CATEGORY_ID
> > AND CPS.PERIOD_ID = SP.PERIOD_ID
> > ORDER BY CATEGORY_SEQUENCE
> > </statement>
> >
> > I was hoping that sqlmaps would store the 'category.id',
> > 'category.parentId', 'category.description' either in a nested
> > HashMap (key of 'category') or store the values in the top
> > level HashMap with those keys ('category.id', etc.)
> >
> > However neither case appears to happen and I get the
> > exception below. I realize that I can map the complex
> > properties with separate selects but I want to avoid the
> > N+1 query problem. So I do the join instead.
> >
> > com.ibatis.common.beans.ProbeException: There is no WRITEABLE
> property named
> > 'id' in class 'java.lang.Object'
> > at
> com.ibatis.common.beans.ClassInfo.getSetter(ClassInfo.java:146)
> > at
> >
> com.ibatis.common.beans.ComplexBeanProbe.setProperty(ComplexBeanPr
> obe.java:3
> > 34)
> > at
> >
> com.ibatis.common.beans.ComplexBeanProbe.setObject(ComplexBeanProb
> e.java:231
> > )
> > at
> com.ibatis.common.beans.GenericProbe.setObject(GenericProbe.java:69)
> > at
> >
> com.ibatis.sqlmap.engine.exchange.ComplexDataExchange.setData(Comp
> lexDataExc
> > hange.java:87)
> > at
> >
> com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.setResultOb
> jectValues
> > (BasicResultMap.java:231)
> > at
> >
> com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback.hand
> leResultOb
> > ject(RowHandlerCallback.java:63)
> > at
> >
> com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlEx
> ecutor.jav
> > a:350)
> > at
> >
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExe
> cutor.java
> > :179)
> > at
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExe
> cuteQuery(
> > GeneralStatement.java:200)
> > at
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.execut
> eQueryWith
> > Callback(GeneralStatement.java:168)
> > at
> >
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.execut
> eQueryForL
> > ist(GeneralStatement.java:118)
> > at
> >
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExec
> > utorDelegate.java:626)
> > at
> >
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExec
> > utorDelegate.java:598)
> > at
> >
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMa
> pSessionIm
> > pl.java:107)
> > at
> >
> org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapCl
> ient(SqlMa
> > pClientTemplate.java:202)
> > at
> >
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMap
> ClientTemp
> > late.java:142)
> > at
> >
> org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithLis
> tResult(Sq
> > lMapClientTemplate.java:164)
> > at
> >
> org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(S
> qlMapClien
> > tTemplate.java:200)
> > at
> >
> com.giv.dashboard.dao.db.ibatis.SqlMapSalesStatisticsDAO.getCatego
> ryStatsFor
> > Customer(SqlMapSalesStatisticsDAO.java:28)
> > at
> >
> com.giv.dashboard.DashboardImpl.getCategorySummaries(DashboardImpl
> .java:42)
> > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > at
> >
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorIm
> pl.java:39
> > )
> > at
> >
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAc
> cessorImpl
> > .java:25)
> > at java.lang.reflect.Method.invoke(Method.java:324)
> > at
> >
> org.springframework.aop.support.AopUtils.invokeJoinpointUsingRefle
> ction(AopU
> > tils.java:295)
> > at
> >
> org.springframework.aop.framework.ReflectiveMethodInvocation.invok
> eJoinpoint
> > (ReflectiveMethodInvocation.java:154)
> > at
> >
> org.springframework.aop.framework.ReflectiveMethodInvocation.proce
> ed(Reflect
> > iveMethodInvocation.java:121)
> > at
> >
> org.springframework.transaction.interceptor.TransactionInterceptor
> .invoke(Tr
> > ansactionInterceptor.java:56)
> > at
> >
> org.springframework.aop.framework.ReflectiveMethodInvocation.proce
> ed(Reflect
> > iveMethodInvocation.java:143)
> > at
> >
> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDyn
> amicAopPro
> > xy.java:174)
> > at $Proxy0.getCategorySummaries(Unknown Source)
> > at
> >
> com.giv.dashboard.web.spring.ViewCategoriesController.handleReques
> tInternal(
> > ViewCategoriesController.java:62)
> > at
> >
> org.springframework.web.servlet.mvc.AbstractController.handleReque
> st(Abstrac
> > tController.java:128)
> > at
> >
> org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter
> .handle(Si
> > mpleControllerHandlerAdapter.java:44)
> > at
> >
> org.springframework.web.servlet.DispatcherServlet.doService(Dispat
> cherServle
> > t.java:532)
> > at
> >
> org.springframework.web.servlet.FrameworkServlet.serviceWrapper(Fr
> ameworkSer
> > vlet.java:366)
> > at
> >
> org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkSe
> rvlet.java
> > :317)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > at
> >
> com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilt
> erChain.ja
> > va:65)
> > at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source)
> > at
> >
> com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRe
> questDispa
> > tcher.java:604)
> > at
> >
> com.evermind.server.http.ServletRequestDispatcher.forwardInternal(
> ServletReq
> > uestDispatcher.java:317)
> > at
> >
> com.evermind.server.http.HttpRequestHandler.processRequest(HttpReq
> uestHandle
> > r.java:790)
> > at
> >
> com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler
> .java:270)
> > at
> >
> com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler
> .java:112)
> > at
> >
> com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(Re
> leasableRe
> > sourcePooledExecutor.java:192)
> > at java.lang.Thread.run(Thread.java:534)
> >
> >
>
Re: Avoiding N+1 with complex HashMap properties.
Posted by Clinton Begin <cl...@gmail.com>.
Tough call. Some people want it stored as a single property (e.g.
imagine the key is a classname), and others want it stored nested.
Currently, as you've discovered, it's not nested.
I would suggest that if you have a complex object model, then you
should model it using JavaBeans. Maps are very loose, unpredictable
and limited.
Cheers,
Clinton
On Fri, 10 Dec 2004 10:03:16 -0500, Jerome Jacobsen
<je...@gentootech.com> wrote:
> Hello,
>
> I'm using SQL Maps 2.08. I was hoping I could do the following
> but I get a ProbeException. Should this be supported?
>
> <resultMap id="get-customer-period-result"
> class="java.util.HashMap">
> <result property="customerNum"
> column="CUSTOMER_NUMBER"
> columnIndex="1"/>
> <result property="category.id"
> column="CATEGORY_ID"
> columnIndex="2"/>
> <result property="category.parentId"
> column="PARENT_CATEGORY_ID"
> columnIndex="3"/>
> <result property="category.description"
> column="CATEGORY_DESC"
> columnIndex="4"/>
> ...
> </resultMap>
>
> <statement id="getCustomerPeriodSummary"
> resultMap="get-customer-period-result">
> SELECT CPS.CUSTOMER_NUMBER,
> PC.CATEGORY_ID,
> PC.PARENT_CATEGORY_ID,
> PC.DESCRIPTION AS CATEGORY_DESC,
> PC.SEQUENCE AS CATEGORY_SEQUENCE,
> ...
> FROM CUSTOMER_PERIOD_SUMMARY CPS, PRODUCT_CATEGORY PC, SALES_PERIOD SP
> WHERE CPS.CUSTOMER_NUMBER = #value#
> AND CPS.CATEGORY_ID = PC.CATEGORY_ID
> AND CPS.PERIOD_ID = SP.PERIOD_ID
> ORDER BY CATEGORY_SEQUENCE
> </statement>
>
> I was hoping that sqlmaps would store the 'category.id',
> 'category.parentId', 'category.description' either in a nested
> HashMap (key of 'category') or store the values in the top
> level HashMap with those keys ('category.id', etc.)
>
> However neither case appears to happen and I get the
> exception below. I realize that I can map the complex
> properties with separate selects but I want to avoid the
> N+1 query problem. So I do the join instead.
>
> com.ibatis.common.beans.ProbeException: There is no WRITEABLE property named
> 'id' in class 'java.lang.Object'
> at com.ibatis.common.beans.ClassInfo.getSetter(ClassInfo.java:146)
> at
> com.ibatis.common.beans.ComplexBeanProbe.setProperty(ComplexBeanProbe.java:3
> 34)
> at
> com.ibatis.common.beans.ComplexBeanProbe.setObject(ComplexBeanProbe.java:231
> )
> at com.ibatis.common.beans.GenericProbe.setObject(GenericProbe.java:69)
> at
> com.ibatis.sqlmap.engine.exchange.ComplexDataExchange.setData(ComplexDataExc
> hange.java:87)
> at
> com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.setResultObjectValues
> (BasicResultMap.java:231)
> at
> com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback.handleResultOb
> ject(RowHandlerCallback.java:63)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.jav
> a:350)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java
> :179)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(
> GeneralStatement.java:200)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWith
> Callback(GeneralStatement.java:168)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForL
> ist(GeneralStatement.java:118)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExec
> utorDelegate.java:626)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExec
> utorDelegate.java:598)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionIm
> pl.java:107)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMa
> pClientTemplate.java:202)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemp
> late.java:142)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(Sq
> lMapClientTemplate.java:164)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClien
> tTemplate.java:200)
> at
> com.giv.dashboard.dao.db.ibatis.SqlMapSalesStatisticsDAO.getCategoryStatsFor
> Customer(SqlMapSalesStatisticsDAO.java:28)
> at
> com.giv.dashboard.DashboardImpl.getCategorySummaries(DashboardImpl.java:42)
> 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:324)
> at
> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopU
> tils.java:295)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint
> (ReflectiveMethodInvocation.java:154)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflect
> iveMethodInvocation.java:121)
> at
> org.springframework.transaction.interceptor.TransactionInterceptor.invoke(Tr
> ansactionInterceptor.java:56)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflect
> iveMethodInvocation.java:143)
> at
> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopPro
> xy.java:174)
> at $Proxy0.getCategorySummaries(Unknown Source)
> at
> com.giv.dashboard.web.spring.ViewCategoriesController.handleRequestInternal(
> ViewCategoriesController.java:62)
> at
> org.springframework.web.servlet.mvc.AbstractController.handleRequest(Abstrac
> tController.java:128)
> at
> org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(Si
> mpleControllerHandlerAdapter.java:44)
> at
> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServle
> t.java:532)
> at
> org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkSer
> vlet.java:366)
> at
> org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java
> :317)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
> com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.ja
> va:65)
> at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source)
> at
> com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispa
> tcher.java:604)
> at
> com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletReq
> uestDispatcher.java:317)
> at
> com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandle
> r.java:790)
> at
> com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:270)
> at
> com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:112)
> at
> com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableRe
> sourcePooledExecutor.java:192)
> at java.lang.Thread.run(Thread.java:534)
>
>
Avoiding N+1 with complex HashMap properties.
Posted by Jerome Jacobsen <je...@gentootech.com>.
Hello,
I'm using SQL Maps 2.08. I was hoping I could do the following
but I get a ProbeException. Should this be supported?
<resultMap id="get-customer-period-result"
class="java.util.HashMap">
<result property="customerNum"
column="CUSTOMER_NUMBER"
columnIndex="1"/>
<result property="category.id"
column="CATEGORY_ID"
columnIndex="2"/>
<result property="category.parentId"
column="PARENT_CATEGORY_ID"
columnIndex="3"/>
<result property="category.description"
column="CATEGORY_DESC"
columnIndex="4"/>
...
</resultMap>
<statement id="getCustomerPeriodSummary"
resultMap="get-customer-period-result">
SELECT CPS.CUSTOMER_NUMBER,
PC.CATEGORY_ID,
PC.PARENT_CATEGORY_ID,
PC.DESCRIPTION AS CATEGORY_DESC,
PC.SEQUENCE AS CATEGORY_SEQUENCE,
...
FROM CUSTOMER_PERIOD_SUMMARY CPS, PRODUCT_CATEGORY PC, SALES_PERIOD SP
WHERE CPS.CUSTOMER_NUMBER = #value#
AND CPS.CATEGORY_ID = PC.CATEGORY_ID
AND CPS.PERIOD_ID = SP.PERIOD_ID
ORDER BY CATEGORY_SEQUENCE
</statement>
I was hoping that sqlmaps would store the 'category.id',
'category.parentId', 'category.description' either in a nested
HashMap (key of 'category') or store the values in the top
level HashMap with those keys ('category.id', etc.)
However neither case appears to happen and I get the
exception below. I realize that I can map the complex
properties with separate selects but I want to avoid the
N+1 query problem. So I do the join instead.
com.ibatis.common.beans.ProbeException: There is no WRITEABLE property named
'id' in class 'java.lang.Object'
at com.ibatis.common.beans.ClassInfo.getSetter(ClassInfo.java:146)
at
com.ibatis.common.beans.ComplexBeanProbe.setProperty(ComplexBeanProbe.java:3
34)
at
com.ibatis.common.beans.ComplexBeanProbe.setObject(ComplexBeanProbe.java:231
)
at com.ibatis.common.beans.GenericProbe.setObject(GenericProbe.java:69)
at
com.ibatis.sqlmap.engine.exchange.ComplexDataExchange.setData(ComplexDataExc
hange.java:87)
at
com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.setResultObjectValues
(BasicResultMap.java:231)
at
com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback.handleResultOb
ject(RowHandlerCallback.java:63)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.jav
a:350)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java
:179)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(
GeneralStatement.java:200)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWith
Callback(GeneralStatement.java:168)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForL
ist(GeneralStatement.java:118)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExec
utorDelegate.java:626)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExec
utorDelegate.java:598)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionIm
pl.java:107)
at
org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMa
pClientTemplate.java:202)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemp
late.java:142)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(Sq
lMapClientTemplate.java:164)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClien
tTemplate.java:200)
at
com.giv.dashboard.dao.db.ibatis.SqlMapSalesStatisticsDAO.getCategoryStatsFor
Customer(SqlMapSalesStatisticsDAO.java:28)
at
com.giv.dashboard.DashboardImpl.getCategorySummaries(DashboardImpl.java:42)
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:324)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopU
tils.java:295)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint
(ReflectiveMethodInvocation.java:154)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflect
iveMethodInvocation.java:121)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(Tr
ansactionInterceptor.java:56)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Reflect
iveMethodInvocation.java:143)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopPro
xy.java:174)
at $Proxy0.getCategorySummaries(Unknown Source)
at
com.giv.dashboard.web.spring.ViewCategoriesController.handleRequestInternal(
ViewCategoriesController.java:62)
at
org.springframework.web.servlet.mvc.AbstractController.handleRequest(Abstrac
tController.java:128)
at
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(Si
mpleControllerHandlerAdapter.java:44)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServle
t.java:532)
at
org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkSer
vlet.java:366)
at
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java
:317)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.ja
va:65)
at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source)
at
com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispa
tcher.java:604)
at
com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletReq
uestDispatcher.java:317)
at
com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandle
r.java:790)
at
com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:270)
at
com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:112)
at
com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableRe
sourcePooledExecutor.java:192)
at java.lang.Thread.run(Thread.java:534)
Re: Changing result set metadata on the fly.
Posted by Clinton Begin <cl...@gmail.com>.
Kevin,
Dynamic ResultSet metadata is not supported. There are a number of
very good reasons, which are mostly performance related. In the
future, I would hope to solve this by supporting SQL fragmets, so that
you can maintain the SQL in one place, but have 2 statements that use
it. That way you don't even need the dynamic SQL part, nor do you
need to imply the count by passing a null parameter.
Cheers,
Clinton
On Fri, 10 Dec 2004 00:35:16 -0800 (PST), Kevin <ke...@yahoo.com> wrote:
>
> Hi
>
> Thanks for answering my questions, thanks for forwding
> it to the forum.
>
> What u are suggesting is exactly what i am doing i.e
> using 2 queries 1 for count and 1 for the result set.
>
> But i was hoping to use 1 dynamic sql instead of 2 to
> achieve the above result.
>
> U can look at my sql example and u will see that the
> condition after the Select does not work and that is
> actually my question.
>
> Why does not work ?
>
> Thanks
> Navin
>
>
> --- Brandon Goodin <br...@gmail.com> wrote:
>
> > Paginated list only returns a subset of the results.
> > This is
> > determined by the pageSize. It does not retain a
> > total count.
> >
> > If you perform a count using the resultset it will
> > force the cursor to
> > traverse the whole resultset. Most databases do not
> > load up large
> > results sets completely. So, when you perform your
> > count using the
> > resultset you are already impacting your system in a
> > negative way and
> > not saving yourself any performance.
> >
> > My suggestion would be to use two sql calls. One
> > using a count (as a
> > count sql statement would be more performant than a
> > resultset
> > traversal) and the other sql statement to retrieve
> > the results you
> > want from the resultset. The PaginatedList in iBatis
> > will only
> > traverse the rows in the resultset that it needs to
> > in order to
> > populate the corresponding objects with. Then when
> > you call the next
> > page on the paginated list it retains the
> > appropriate information to
> > reconnect to the database and grab the next section
> > of the results
> > that you need.
> >
> > With this strategy you save yourself on memory and
> > cpu.
> >
> > Brandon
> >
> >
> > On Thu, 9 Dec 2004 23:31:15 -0800 (PST), Kevin
> > <ke...@yahoo.com> wrote:
> > > Thanks for taking time to respond to my question.
> > >
> > > Here is an example of what i am trying to do.
> > >
> > > I have a query that serves a dual purpose
> > > 1>
> > > getting me a count of all the records that meet a
> > > certain criteria
> > > 2>
> > > return a subset of the records of whole list i
> > used
> > > for getting the count as i donot need the whole
> > result
> > > set for display.
> > >
> > > The type of query executed depends on a field in
> > the
> > > parameter class.
> > >
> > > I have attached my query below , if u look at it
> > > carefully u will see that the Select clause has a
> > > condition on "countQuery" and so does the where
> > > clause.
> > >
> > > Essentially the resultset columns change on the
> > fly
> > > depending on the query selected.
> > >
> > > This as far as i am told is not supported in
> > Ibatis.
> > >
> > > I was wondering why this is not supported and what
> > are
> > > the alternatives rather than having 2 queries.
> > >
> > > Your suggestion of doing a size on the list will
> > not
> > > work because the result set is only a subset. i.e
> > i
> > > only display 100 out lets say 10000 records on the
> > web
> > > layer to make it more effecient , so i set my
> > where
> > > clause to give me the range of records whose list
> > size
> > > will be 100 but the count should return 10000. The
> > > reson i need 10000 is for creating a web layer
> > based
> > > paginated list.
> > >
> > > Does the paginated list in Ibatis support this?
> > >
> > > <select
> > > id="getEmployeeAndLastCourseCount"
> > >
> > >
> >
> parameterClass="com.performixtech.emvolve.devmgr.trainingmanager.sqlmap.p
> > > aram.EmployeeAndLastCourseParam"
> > >
> > >
> >
> resultClass="com.performixtech.emvolve.devmgr.trainingmanager.model.Train
> > > ingRequestListModel">
> > > Select
> > >
> > > <isNotNull
> > > property="countQuery">
> > >
> > >
> > count(*)
> > > as count
> > >
> > > </isNotNull>
> > >
> > > <isNull
> > > property="countQuery">
> > >
> > > rownumber,
> > >
> > > employeeId,
> > >
> > > employeeName,
> > >
> > > managerId,
> > >
> > > managerName,
> > >
> > > managementUnitId,
> > >
> > > managementUnitName,
> > >
> > > courseId,
> > >
> > > courseTitle,
> > >
> > > courseDate
> > >
> > > </isNull>
> > >
> > > FROM
> > > (
> > > SELECT
> > > rownum as rownumber,
> > > e.emp_employee_id as
> > employeeId,
> > > e.emp_name as employeeName,
> > > e.emp_manager as managerId,
> > > e.manager_name as managerName,
> > > e.muid as managementUnitId,
> > > e.name as managementUnitName,
> > > cS.course_Id as courseId,
> > > cS.course_title as courseTitle,
> > > cS.start_datetime as
> > courseDate,
> > >
> > > cS.module_status_id
> > > FROM
> > > (select e.emp_employee_id,
> > > e.emp_name,
> > > em.emp_employee_id
> > >
> > > as emp_manager,
> > > em.emp_name as manager_name, mu.muid, mu.name
> > >
> > > from employee e,
> > > employee em, management_unit mu,
> > >
> > > mgmt_unit_association ma
> > >
> > > where
> > > e.emp_manager =
> > em.emp_employee_id
> > > and
> > > e.emp_employee_id =
> > ma.employee_id
> > > and
> > > mu.muid = ma.muid
> > >
> > > ) e
> > >
> > > left outer join
> > >
> > > (select
> > emp_employee_id,
> > > start_datetime, a.course_id, a.sched_id,
> > >
> > > module_status_id,
> > > c.course_title, rank from
> > > (select b.emp_employee_id,
> > > a.start_datetime,
> > > a.course_id,
> > >
> > > a.sched_id,
> > > b.module_status_id, dense_rank() over
> > >
> > > (partition by b.emp_employee_id
> > >
> > > order by b.emp_employee_id,
> > > a.start_datetime) rank from
> > >
> > > course_schedule a,
> > course_assignment
> === message truncated ===
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
Re: Changing result set metadata on the fly.
Posted by Clinton Begin <cl...@gmail.com>.
To everyone in this discussion:
Support for this has been added and will be released with the next
version. It's already in CVS. It is an option though, as you'll have
to eat the performance implications that go along with remapping every
time.
<statement ... remapResults="true" >
Cheers,
Clinton
On Sun, 12 Dec 2004 21:55:59 -0800 (PST), Kevin <ke...@yahoo.com> wrote:
>
> Hi Phillipe,Clinton
>
> Thanks for taking time to answering my queries.
>
> Navin
>
>
>
>
> --- Clinton Begin <cl...@gmail.com> wrote:
>
> > Come to think of it, why don't you go ahead and
> > submit it to JIRA?
> > It's not like I'd be doing anything different
> > (there's no migration
> > tool). ;-)
> >
> > This way it's tracked under your name too.
> >
> > Cheers,
> > Clinton
> >
> >
> > On Fri, 10 Dec 2004 12:06:05 -0500,
> > philippe.laflamme
> > <ph...@mail.mcgill.ca> wrote:
> > > Just a quick note on the original question...
> > There is a thread in the (now
> > > closed) iBatis development forum on SourceForge:
> > >
> > >
> >
> http://sourceforge.net/forum/forum.php?thread_id=1162000&forum_id=206694
> > >
> > > Clinton, you mentioned this would become a high
> > priority issue. Is there any
> > > update on this? I understand that moving to the
> > incubator must be taking
> > > most of your time... I'm just curious.
> > >
> > > Thanks,
> > > Phil
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Clinton Begin
> > [mailto:clinton.begin@gmail.com]
> > > Sent: December 10, 2004 9:18 AM
> > > Cc: ibatis-user-java@incubator.apache.org
> > > Subject: Re: Changing result set metadata on the
> > fly.
> > >
> > > JIRA is not yet the source of record for RFEs.
> > Not all SourceForge
> > > resources have made it to the Apache
> > infrastructure yet. Similarly, CVS is
> > > not yet in SVN. These things take time.
> > >
> > > That said, <dynamic> is already a reserved
> > element, and hence this RFE will
> > > not pass as described. Perhaps something like
> > <fragmet> or even simply
> > > <sql> might be more appropriate.
> > >
> > > Cheers,
> > > Clinton
> > >
> > > On Fri, 10 Dec 2004 08:52:45 -0500, Jerome
> > Jacobsen
> > > <je...@gentootech.com> wrote:
> > > > I submitted a RFE to Sourceforge Tracker back in
> > September that would
> > > > allow for something similar to what you want.
> > The RFE ID is 1034260
> > > > (Named dynamic for reuse).
> > > >
> > > > I checked a couple of weeks ago to see if it
> > made it into Jira but it
> > > > didn't. I asked the maillist on Nov 30 why it
> > didn't make it but got
> > > > no response.
> > > >
> > > > Anyway, with this RFE your statements would look
> > something like this:
> > > >
> > > > <dynamic id="myBigDynamic">
> > > >
> > > >
> > > > FROM
> > > > (
> > > > SELECT
> > > > rownum as rownumber,
> > > > e.emp_employee_id as employeeId,
> > > > e.emp_name as employeeName,
> > > > e.emp_manager as managerId,
> > > > e.manager_name as managerName,
> > > > e.muid as managementUnitId,
> > > > e.name as managementUnitName,
> > > > cS.course_Id as courseId,
> > > > cS.course_title as courseTitle,
> > > > cS.start_datetime as courseDate,
> > > >
> > > > cS.module_status_id
> > > > FROM
> > > > (select
> > > > e.emp_employee_id,
> > > > e.emp_name,
> > > > em.emp_employee_id as emp_manager,
> > > > em.emp_name as manager_name,
> > > > mu.muid,
> > > > mu.name
> > > > ..... (lots more)
> > > > </dynamic>
> > > >
> > > > <statement id="getEmployeeAndLastCourseCount">
> > > >
> > parameterClass="EmployeeAndLastCourseParam"
> > > > resultClass="java.lang.Long">
> > > > Select count(*) as count
> > > > <dynamic refId="myBigDynamic"/>
> > > > </statement>
> > > >
> > > > <statement id="getEmployeeAndLastCourse"
> > > >
> > parameterClass="EmployeeAndLastCourseParam"
> > > >
> > resultClass="TrainingRequestListModel">
> > > > Select
> > > >
> > > >
> > > > rownumber,
> > > > employeeId,
> > > > employeeName,
> > > > managerId,
> > > > managerName,
> > > > managementUnitId,
> > > > managementUnitName,
> > > > courseId,
> > > > courseTitle,
> > > > courseDate
> > > > <dynamic refId="myBigDynamic"/>
> > > > </statement>
> > > >
> > > > So it is 2 small statements and one large
> > reusable dynamic.
> > > >
> > > > -Jerome
> > > >
> > > >
> > >
> > >
> >
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - You care about security. So do we.
> http://promotions.yahoo.com/new_mail
>
>
Re: Changing result set metadata on the fly.
Posted by Kevin <ke...@yahoo.com>.
Hi Phillipe,Clinton
Thanks for taking time to answering my queries.
Navin
--- Clinton Begin <cl...@gmail.com> wrote:
> Come to think of it, why don't you go ahead and
> submit it to JIRA?
> It's not like I'd be doing anything different
> (there's no migration
> tool). ;-)
>
> This way it's tracked under your name too.
>
> Cheers,
> Clinton
>
>
> On Fri, 10 Dec 2004 12:06:05 -0500,
> philippe.laflamme
> <ph...@mail.mcgill.ca> wrote:
> > Just a quick note on the original question...
> There is a thread in the (now
> > closed) iBatis development forum on SourceForge:
> >
> >
>
http://sourceforge.net/forum/forum.php?thread_id=1162000&forum_id=206694
> >
> > Clinton, you mentioned this would become a high
> priority issue. Is there any
> > update on this? I understand that moving to the
> incubator must be taking
> > most of your time... I'm just curious.
> >
> > Thanks,
> > Phil
> >
> >
> >
> > -----Original Message-----
> > From: Clinton Begin
> [mailto:clinton.begin@gmail.com]
> > Sent: December 10, 2004 9:18 AM
> > Cc: ibatis-user-java@incubator.apache.org
> > Subject: Re: Changing result set metadata on the
> fly.
> >
> > JIRA is not yet the source of record for RFEs.
> Not all SourceForge
> > resources have made it to the Apache
> infrastructure yet. Similarly, CVS is
> > not yet in SVN. These things take time.
> >
> > That said, <dynamic> is already a reserved
> element, and hence this RFE will
> > not pass as described. Perhaps something like
> <fragmet> or even simply
> > <sql> might be more appropriate.
> >
> > Cheers,
> > Clinton
> >
> > On Fri, 10 Dec 2004 08:52:45 -0500, Jerome
> Jacobsen
> > <je...@gentootech.com> wrote:
> > > I submitted a RFE to Sourceforge Tracker back in
> September that would
> > > allow for something similar to what you want.
> The RFE ID is 1034260
> > > (Named dynamic for reuse).
> > >
> > > I checked a couple of weeks ago to see if it
> made it into Jira but it
> > > didn't. I asked the maillist on Nov 30 why it
> didn't make it but got
> > > no response.
> > >
> > > Anyway, with this RFE your statements would look
> something like this:
> > >
> > > <dynamic id="myBigDynamic">
> > >
> > >
> > > FROM
> > > (
> > > SELECT
> > > rownum as rownumber,
> > > e.emp_employee_id as employeeId,
> > > e.emp_name as employeeName,
> > > e.emp_manager as managerId,
> > > e.manager_name as managerName,
> > > e.muid as managementUnitId,
> > > e.name as managementUnitName,
> > > cS.course_Id as courseId,
> > > cS.course_title as courseTitle,
> > > cS.start_datetime as courseDate,
> > >
> > > cS.module_status_id
> > > FROM
> > > (select
> > > e.emp_employee_id,
> > > e.emp_name,
> > > em.emp_employee_id as emp_manager,
> > > em.emp_name as manager_name,
> > > mu.muid,
> > > mu.name
> > > ..... (lots more)
> > > </dynamic>
> > >
> > > <statement id="getEmployeeAndLastCourseCount">
> > >
> parameterClass="EmployeeAndLastCourseParam"
> > > resultClass="java.lang.Long">
> > > Select count(*) as count
> > > <dynamic refId="myBigDynamic"/>
> > > </statement>
> > >
> > > <statement id="getEmployeeAndLastCourse"
> > >
> parameterClass="EmployeeAndLastCourseParam"
> > >
> resultClass="TrainingRequestListModel">
> > > Select
> > >
> > >
> > > rownumber,
> > > employeeId,
> > > employeeName,
> > > managerId,
> > > managerName,
> > > managementUnitId,
> > > managementUnitName,
> > > courseId,
> > > courseTitle,
> > > courseDate
> > > <dynamic refId="myBigDynamic"/>
> > > </statement>
> > >
> > > So it is 2 small statements and one large
> reusable dynamic.
> > >
> > > -Jerome
> > >
> > >
> >
> >
>
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
Re: Changing result set metadata on the fly.
Posted by Clinton Begin <cl...@gmail.com>.
Come to think of it, why don't you go ahead and submit it to JIRA?
It's not like I'd be doing anything different (there's no migration
tool). ;-)
This way it's tracked under your name too.
Cheers,
Clinton
On Fri, 10 Dec 2004 12:06:05 -0500, philippe.laflamme
<ph...@mail.mcgill.ca> wrote:
> Just a quick note on the original question... There is a thread in the (now
> closed) iBatis development forum on SourceForge:
>
> http://sourceforge.net/forum/forum.php?thread_id=1162000&forum_id=206694
>
> Clinton, you mentioned this would become a high priority issue. Is there any
> update on this? I understand that moving to the incubator must be taking
> most of your time... I'm just curious.
>
> Thanks,
> Phil
>
>
>
> -----Original Message-----
> From: Clinton Begin [mailto:clinton.begin@gmail.com]
> Sent: December 10, 2004 9:18 AM
> Cc: ibatis-user-java@incubator.apache.org
> Subject: Re: Changing result set metadata on the fly.
>
> JIRA is not yet the source of record for RFEs. Not all SourceForge
> resources have made it to the Apache infrastructure yet. Similarly, CVS is
> not yet in SVN. These things take time.
>
> That said, <dynamic> is already a reserved element, and hence this RFE will
> not pass as described. Perhaps something like <fragmet> or even simply
> <sql> might be more appropriate.
>
> Cheers,
> Clinton
>
> On Fri, 10 Dec 2004 08:52:45 -0500, Jerome Jacobsen
> <je...@gentootech.com> wrote:
> > I submitted a RFE to Sourceforge Tracker back in September that would
> > allow for something similar to what you want. The RFE ID is 1034260
> > (Named dynamic for reuse).
> >
> > I checked a couple of weeks ago to see if it made it into Jira but it
> > didn't. I asked the maillist on Nov 30 why it didn't make it but got
> > no response.
> >
> > Anyway, with this RFE your statements would look something like this:
> >
> > <dynamic id="myBigDynamic">
> >
> >
> > FROM
> > (
> > SELECT
> > rownum as rownumber,
> > e.emp_employee_id as employeeId,
> > e.emp_name as employeeName,
> > e.emp_manager as managerId,
> > e.manager_name as managerName,
> > e.muid as managementUnitId,
> > e.name as managementUnitName,
> > cS.course_Id as courseId,
> > cS.course_title as courseTitle,
> > cS.start_datetime as courseDate,
> >
> > cS.module_status_id
> > FROM
> > (select
> > e.emp_employee_id,
> > e.emp_name,
> > em.emp_employee_id as emp_manager,
> > em.emp_name as manager_name,
> > mu.muid,
> > mu.name
> > ..... (lots more)
> > </dynamic>
> >
> > <statement id="getEmployeeAndLastCourseCount">
> > parameterClass="EmployeeAndLastCourseParam"
> > resultClass="java.lang.Long">
> > Select count(*) as count
> > <dynamic refId="myBigDynamic"/>
> > </statement>
> >
> > <statement id="getEmployeeAndLastCourse"
> > parameterClass="EmployeeAndLastCourseParam"
> > resultClass="TrainingRequestListModel">
> > Select
> >
> >
> > rownumber,
> > employeeId,
> > employeeName,
> > managerId,
> > managerName,
> > managementUnitId,
> > managementUnitName,
> > courseId,
> > courseTitle,
> > courseDate
> > <dynamic refId="myBigDynamic"/>
> > </statement>
> >
> > So it is 2 small statements and one large reusable dynamic.
> >
> > -Jerome
> >
> >
>
>
Re: Changing result set metadata on the fly.
Posted by Kevin <ke...@yahoo.com>.
Thanks for answering my questions
Kevin
--- Clinton Begin <cl...@gmail.com> wrote:
> Actually, my real job is what is taking most of my
> time. ;-)
> Luckily, i'm back from Stanford now and I'm hoping
> to get things
> rolling again.
>
> And yes, the incubator has slowed us down quite a
> bit too. We're
> between worlds right now. But it's very important
> that we get iBATIS
> into a larger community of developers. It's too big
> for me to control
> by myself now (especially with a baby on the way).
>
> Finally, to be fair, I didn't say "high" priority, I
> said "higher"
> priority. Because this was sitting somewhere
> between "non-issue" and
> "low-priority", it's still probably not high. ;-)
>
> But now that we have a fixed release plan it will be
> be easier to
> identify which features go in when. We alternate
> bug-fix releases and
> feature releases. The next one is a feature
> release. Hopefully
> we'll publish a road map soon.
>
> Cheers,
> Clinton
>
>
>
>
>
> On Fri, 10 Dec 2004 12:06:05 -0500,
> philippe.laflamme
> <ph...@mail.mcgill.ca> wrote:
> > Just a quick note on the original question...
> There is a thread in the (now
> > closed) iBatis development forum on SourceForge:
> >
> >
>
http://sourceforge.net/forum/forum.php?thread_id=1162000&forum_id=206694
> >
> > Clinton, you mentioned this would become a high
> priority issue. Is there any
> > update on this? I understand that moving to the
> incubator must be taking
> > most of your time... I'm just curious.
> >
> > Thanks,
> > Phil
> >
> >
> >
> > -----Original Message-----
> > From: Clinton Begin
> [mailto:clinton.begin@gmail.com]
> > Sent: December 10, 2004 9:18 AM
> > Cc: ibatis-user-java@incubator.apache.org
> > Subject: Re: Changing result set metadata on the
> fly.
> >
> > JIRA is not yet the source of record for RFEs.
> Not all SourceForge
> > resources have made it to the Apache
> infrastructure yet. Similarly, CVS is
> > not yet in SVN. These things take time.
> >
> > That said, <dynamic> is already a reserved
> element, and hence this RFE will
> > not pass as described. Perhaps something like
> <fragmet> or even simply
> > <sql> might be more appropriate.
> >
> > Cheers,
> > Clinton
> >
> > On Fri, 10 Dec 2004 08:52:45 -0500, Jerome
> Jacobsen
> > <je...@gentootech.com> wrote:
> > > I submitted a RFE to Sourceforge Tracker back in
> September that would
> > > allow for something similar to what you want.
> The RFE ID is 1034260
> > > (Named dynamic for reuse).
> > >
> > > I checked a couple of weeks ago to see if it
> made it into Jira but it
> > > didn't. I asked the maillist on Nov 30 why it
> didn't make it but got
> > > no response.
> > >
> > > Anyway, with this RFE your statements would look
> something like this:
> > >
> > > <dynamic id="myBigDynamic">
> > >
> > >
> > > FROM
> > > (
> > > SELECT
> > > rownum as rownumber,
> > > e.emp_employee_id as employeeId,
> > > e.emp_name as employeeName,
> > > e.emp_manager as managerId,
> > > e.manager_name as managerName,
> > > e.muid as managementUnitId,
> > > e.name as managementUnitName,
> > > cS.course_Id as courseId,
> > > cS.course_title as courseTitle,
> > > cS.start_datetime as courseDate,
> > >
> > > cS.module_status_id
> > > FROM
> > > (select
> > > e.emp_employee_id,
> > > e.emp_name,
> > > em.emp_employee_id as emp_manager,
> > > em.emp_name as manager_name,
> > > mu.muid,
> > > mu.name
> > > ..... (lots more)
> > > </dynamic>
> > >
> > > <statement id="getEmployeeAndLastCourseCount">
> > >
> parameterClass="EmployeeAndLastCourseParam"
> > > resultClass="java.lang.Long">
> > > Select count(*) as count
> > > <dynamic refId="myBigDynamic"/>
> > > </statement>
> > >
> > > <statement id="getEmployeeAndLastCourse"
> > >
> parameterClass="EmployeeAndLastCourseParam"
> > >
> resultClass="TrainingRequestListModel">
> > > Select
> > >
> > >
> > > rownumber,
> > > employeeId,
> > > employeeName,
> > > managerId,
> > > managerName,
> > > managementUnitId,
> > > managementUnitName,
> > > courseId,
> > > courseTitle,
> > > courseDate
> > > <dynamic refId="myBigDynamic"/>
> > > </statement>
> > >
> > > So it is 2 small statements and one large
> reusable dynamic.
> > >
> > > -Jerome
> > >
> > >
> >
> >
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: Changing result set metadata on the fly.
Posted by Clinton Begin <cl...@gmail.com>.
Actually, my real job is what is taking most of my time. ;-)
Luckily, i'm back from Stanford now and I'm hoping to get things
rolling again.
And yes, the incubator has slowed us down quite a bit too. We're
between worlds right now. But it's very important that we get iBATIS
into a larger community of developers. It's too big for me to control
by myself now (especially with a baby on the way).
Finally, to be fair, I didn't say "high" priority, I said "higher"
priority. Because this was sitting somewhere between "non-issue" and
"low-priority", it's still probably not high. ;-)
But now that we have a fixed release plan it will be be easier to
identify which features go in when. We alternate bug-fix releases and
feature releases. The next one is a feature release. Hopefully
we'll publish a road map soon.
Cheers,
Clinton
On Fri, 10 Dec 2004 12:06:05 -0500, philippe.laflamme
<ph...@mail.mcgill.ca> wrote:
> Just a quick note on the original question... There is a thread in the (now
> closed) iBatis development forum on SourceForge:
>
> http://sourceforge.net/forum/forum.php?thread_id=1162000&forum_id=206694
>
> Clinton, you mentioned this would become a high priority issue. Is there any
> update on this? I understand that moving to the incubator must be taking
> most of your time... I'm just curious.
>
> Thanks,
> Phil
>
>
>
> -----Original Message-----
> From: Clinton Begin [mailto:clinton.begin@gmail.com]
> Sent: December 10, 2004 9:18 AM
> Cc: ibatis-user-java@incubator.apache.org
> Subject: Re: Changing result set metadata on the fly.
>
> JIRA is not yet the source of record for RFEs. Not all SourceForge
> resources have made it to the Apache infrastructure yet. Similarly, CVS is
> not yet in SVN. These things take time.
>
> That said, <dynamic> is already a reserved element, and hence this RFE will
> not pass as described. Perhaps something like <fragmet> or even simply
> <sql> might be more appropriate.
>
> Cheers,
> Clinton
>
> On Fri, 10 Dec 2004 08:52:45 -0500, Jerome Jacobsen
> <je...@gentootech.com> wrote:
> > I submitted a RFE to Sourceforge Tracker back in September that would
> > allow for something similar to what you want. The RFE ID is 1034260
> > (Named dynamic for reuse).
> >
> > I checked a couple of weeks ago to see if it made it into Jira but it
> > didn't. I asked the maillist on Nov 30 why it didn't make it but got
> > no response.
> >
> > Anyway, with this RFE your statements would look something like this:
> >
> > <dynamic id="myBigDynamic">
> >
> >
> > FROM
> > (
> > SELECT
> > rownum as rownumber,
> > e.emp_employee_id as employeeId,
> > e.emp_name as employeeName,
> > e.emp_manager as managerId,
> > e.manager_name as managerName,
> > e.muid as managementUnitId,
> > e.name as managementUnitName,
> > cS.course_Id as courseId,
> > cS.course_title as courseTitle,
> > cS.start_datetime as courseDate,
> >
> > cS.module_status_id
> > FROM
> > (select
> > e.emp_employee_id,
> > e.emp_name,
> > em.emp_employee_id as emp_manager,
> > em.emp_name as manager_name,
> > mu.muid,
> > mu.name
> > ..... (lots more)
> > </dynamic>
> >
> > <statement id="getEmployeeAndLastCourseCount">
> > parameterClass="EmployeeAndLastCourseParam"
> > resultClass="java.lang.Long">
> > Select count(*) as count
> > <dynamic refId="myBigDynamic"/>
> > </statement>
> >
> > <statement id="getEmployeeAndLastCourse"
> > parameterClass="EmployeeAndLastCourseParam"
> > resultClass="TrainingRequestListModel">
> > Select
> >
> >
> > rownumber,
> > employeeId,
> > employeeName,
> > managerId,
> > managerName,
> > managementUnitId,
> > managementUnitName,
> > courseId,
> > courseTitle,
> > courseDate
> > <dynamic refId="myBigDynamic"/>
> > </statement>
> >
> > So it is 2 small statements and one large reusable dynamic.
> >
> > -Jerome
> >
> >
>
>
RE: Changing result set metadata on the fly.
Posted by "philippe.laflamme" <ph...@mail.mcgill.ca>.
Just a quick note on the original question... There is a thread in the (now
closed) iBatis development forum on SourceForge:
http://sourceforge.net/forum/forum.php?thread_id=1162000&forum_id=206694
Clinton, you mentioned this would become a high priority issue. Is there any
update on this? I understand that moving to the incubator must be taking
most of your time... I'm just curious.
Thanks,
Phil
-----Original Message-----
From: Clinton Begin [mailto:clinton.begin@gmail.com]
Sent: December 10, 2004 9:18 AM
Cc: ibatis-user-java@incubator.apache.org
Subject: Re: Changing result set metadata on the fly.
JIRA is not yet the source of record for RFEs. Not all SourceForge
resources have made it to the Apache infrastructure yet. Similarly, CVS is
not yet in SVN. These things take time.
That said, <dynamic> is already a reserved element, and hence this RFE will
not pass as described. Perhaps something like <fragmet> or even simply
<sql> might be more appropriate.
Cheers,
Clinton
On Fri, 10 Dec 2004 08:52:45 -0500, Jerome Jacobsen
<je...@gentootech.com> wrote:
> I submitted a RFE to Sourceforge Tracker back in September that would
> allow for something similar to what you want. The RFE ID is 1034260
> (Named dynamic for reuse).
>
> I checked a couple of weeks ago to see if it made it into Jira but it
> didn't. I asked the maillist on Nov 30 why it didn't make it but got
> no response.
>
> Anyway, with this RFE your statements would look something like this:
>
> <dynamic id="myBigDynamic">
>
>
> FROM
> (
> SELECT
> rownum as rownumber,
> e.emp_employee_id as employeeId,
> e.emp_name as employeeName,
> e.emp_manager as managerId,
> e.manager_name as managerName,
> e.muid as managementUnitId,
> e.name as managementUnitName,
> cS.course_Id as courseId,
> cS.course_title as courseTitle,
> cS.start_datetime as courseDate,
>
> cS.module_status_id
> FROM
> (select
> e.emp_employee_id,
> e.emp_name,
> em.emp_employee_id as emp_manager,
> em.emp_name as manager_name,
> mu.muid,
> mu.name
> ..... (lots more)
> </dynamic>
>
> <statement id="getEmployeeAndLastCourseCount">
> parameterClass="EmployeeAndLastCourseParam"
> resultClass="java.lang.Long">
> Select count(*) as count
> <dynamic refId="myBigDynamic"/>
> </statement>
>
> <statement id="getEmployeeAndLastCourse"
> parameterClass="EmployeeAndLastCourseParam"
> resultClass="TrainingRequestListModel">
> Select
>
>
> rownumber,
> employeeId,
> employeeName,
> managerId,
> managerName,
> managementUnitId,
> managementUnitName,
> courseId,
> courseTitle,
> courseDate
> <dynamic refId="myBigDynamic"/>
> </statement>
>
> So it is 2 small statements and one large reusable dynamic.
>
> -Jerome
>
>
Re: Changing result set metadata on the fly.
Posted by Clinton Begin <cl...@gmail.com>.
JIRA is not yet the source of record for RFEs. Not all SourceForge
resources have made it to the Apache infrastructure yet. Similarly,
CVS is not yet in SVN. These things take time.
That said, <dynamic> is already a reserved element, and hence this RFE
will not pass as described. Perhaps something like <fragmet> or even
simply <sql> might be more appropriate.
Cheers,
Clinton
On Fri, 10 Dec 2004 08:52:45 -0500, Jerome Jacobsen
<je...@gentootech.com> wrote:
> I submitted a RFE to Sourceforge Tracker back in September that
> would allow for something similar to what you want. The RFE
> ID is 1034260 (Named dynamic for reuse).
>
> I checked a couple of weeks ago to see if it made it into Jira
> but it didn't. I asked the maillist on Nov 30 why it didn't
> make it but got no response.
>
> Anyway, with this RFE your statements would look something
> like this:
>
> <dynamic id="myBigDynamic">
>
>
> FROM
> (
> SELECT
> rownum as rownumber,
> e.emp_employee_id as employeeId,
> e.emp_name as employeeName,
> e.emp_manager as managerId,
> e.manager_name as managerName,
> e.muid as managementUnitId,
> e.name as managementUnitName,
> cS.course_Id as courseId,
> cS.course_title as courseTitle,
> cS.start_datetime as courseDate,
>
> cS.module_status_id
> FROM
> (select
> e.emp_employee_id,
> e.emp_name,
> em.emp_employee_id as emp_manager,
> em.emp_name as manager_name,
> mu.muid,
> mu.name
> ..... (lots more)
> </dynamic>
>
> <statement id="getEmployeeAndLastCourseCount">
> parameterClass="EmployeeAndLastCourseParam"
> resultClass="java.lang.Long">
> Select count(*) as count
> <dynamic refId="myBigDynamic"/>
> </statement>
>
> <statement id="getEmployeeAndLastCourse"
> parameterClass="EmployeeAndLastCourseParam"
> resultClass="TrainingRequestListModel">
> Select
>
>
> rownumber,
> employeeId,
> employeeName,
> managerId,
> managerName,
> managementUnitId,
> managementUnitName,
> courseId,
> courseTitle,
> courseDate
> <dynamic refId="myBigDynamic"/>
> </statement>
>
> So it is 2 small statements and one large reusable dynamic.
>
> -Jerome
>
>
RE: Changing result set metadata on the fly.
Posted by Jerome Jacobsen <je...@gentootech.com>.
I submitted a RFE to Sourceforge Tracker back in September that
would allow for something similar to what you want. The RFE
ID is 1034260 (Named dynamic for reuse).
I checked a couple of weeks ago to see if it made it into Jira
but it didn't. I asked the maillist on Nov 30 why it didn't
make it but got no response.
Anyway, with this RFE your statements would look something
like this:
<dynamic id="myBigDynamic">
FROM
(
SELECT
rownum as rownumber,
e.emp_employee_id as employeeId,
e.emp_name as employeeName,
e.emp_manager as managerId,
e.manager_name as managerName,
e.muid as managementUnitId,
e.name as managementUnitName,
cS.course_Id as courseId,
cS.course_title as courseTitle,
cS.start_datetime as courseDate,
cS.module_status_id
FROM
(select
e.emp_employee_id,
e.emp_name,
em.emp_employee_id as emp_manager,
em.emp_name as manager_name,
mu.muid,
mu.name
..... (lots more)
</dynamic>
<statement id="getEmployeeAndLastCourseCount">
parameterClass="EmployeeAndLastCourseParam"
resultClass="java.lang.Long">
Select count(*) as count
<dynamic refId="myBigDynamic"/>
</statement>
<statement id="getEmployeeAndLastCourse"
parameterClass="EmployeeAndLastCourseParam"
resultClass="TrainingRequestListModel">
Select
rownumber,
employeeId,
employeeName,
managerId,
managerName,
managementUnitId,
managementUnitName,
courseId,
courseTitle,
courseDate
<dynamic refId="myBigDynamic"/>
</statement>
So it is 2 small statements and one large reusable dynamic.
-Jerome
Re: Changing result set metadata on the fly.
Posted by Kevin <ke...@yahoo.com>.
Hi
Thanks for answering my questions, thanks for forwding
it to the forum.
What u are suggesting is exactly what i am doing i.e
using 2 queries 1 for count and 1 for the result set.
But i was hoping to use 1 dynamic sql instead of 2 to
achieve the above result.
U can look at my sql example and u will see that the
condition after the Select does not work and that is
actually my question.
Why does not work ?
Thanks
Navin
--- Brandon Goodin <br...@gmail.com> wrote:
> Paginated list only returns a subset of the results.
> This is
> determined by the pageSize. It does not retain a
> total count.
>
> If you perform a count using the resultset it will
> force the cursor to
> traverse the whole resultset. Most databases do not
> load up large
> results sets completely. So, when you perform your
> count using the
> resultset you are already impacting your system in a
> negative way and
> not saving yourself any performance.
>
> My suggestion would be to use two sql calls. One
> using a count (as a
> count sql statement would be more performant than a
> resultset
> traversal) and the other sql statement to retrieve
> the results you
> want from the resultset. The PaginatedList in iBatis
> will only
> traverse the rows in the resultset that it needs to
> in order to
> populate the corresponding objects with. Then when
> you call the next
> page on the paginated list it retains the
> appropriate information to
> reconnect to the database and grab the next section
> of the results
> that you need.
>
> With this strategy you save yourself on memory and
> cpu.
>
> Brandon
>
>
> On Thu, 9 Dec 2004 23:31:15 -0800 (PST), Kevin
> <ke...@yahoo.com> wrote:
> > Thanks for taking time to respond to my question.
> >
> > Here is an example of what i am trying to do.
> >
> > I have a query that serves a dual purpose
> > 1>
> > getting me a count of all the records that meet a
> > certain criteria
> > 2>
> > return a subset of the records of whole list i
> used
> > for getting the count as i donot need the whole
> result
> > set for display.
> >
> > The type of query executed depends on a field in
> the
> > parameter class.
> >
> > I have attached my query below , if u look at it
> > carefully u will see that the Select clause has a
> > condition on "countQuery" and so does the where
> > clause.
> >
> > Essentially the resultset columns change on the
> fly
> > depending on the query selected.
> >
> > This as far as i am told is not supported in
> Ibatis.
> >
> > I was wondering why this is not supported and what
> are
> > the alternatives rather than having 2 queries.
> >
> > Your suggestion of doing a size on the list will
> not
> > work because the result set is only a subset. i.e
> i
> > only display 100 out lets say 10000 records on the
> web
> > layer to make it more effecient , so i set my
> where
> > clause to give me the range of records whose list
> size
> > will be 100 but the count should return 10000. The
> > reson i need 10000 is for creating a web layer
> based
> > paginated list.
> >
> > Does the paginated list in Ibatis support this?
> >
> > <select
> > id="getEmployeeAndLastCourseCount"
> >
> >
>
parameterClass="com.performixtech.emvolve.devmgr.trainingmanager.sqlmap.p
> > aram.EmployeeAndLastCourseParam"
> >
> >
>
resultClass="com.performixtech.emvolve.devmgr.trainingmanager.model.Train
> > ingRequestListModel">
> > Select
> >
> > <isNotNull
> > property="countQuery">
> >
> >
> count(*)
> > as count
> >
> > </isNotNull>
> >
> > <isNull
> > property="countQuery">
> >
> > rownumber,
> >
> > employeeId,
> >
> > employeeName,
> >
> > managerId,
> >
> > managerName,
> >
> > managementUnitId,
> >
> > managementUnitName,
> >
> > courseId,
> >
> > courseTitle,
> >
> > courseDate
> >
> > </isNull>
> >
> > FROM
> > (
> > SELECT
> > rownum as rownumber,
> > e.emp_employee_id as
> employeeId,
> > e.emp_name as employeeName,
> > e.emp_manager as managerId,
> > e.manager_name as managerName,
> > e.muid as managementUnitId,
> > e.name as managementUnitName,
> > cS.course_Id as courseId,
> > cS.course_title as courseTitle,
> > cS.start_datetime as
> courseDate,
> >
> > cS.module_status_id
> > FROM
> > (select e.emp_employee_id,
> > e.emp_name,
> > em.emp_employee_id
> >
> > as emp_manager,
> > em.emp_name as manager_name, mu.muid, mu.name
> >
> > from employee e,
> > employee em, management_unit mu,
> >
> > mgmt_unit_association ma
> >
> > where
> > e.emp_manager =
> em.emp_employee_id
> > and
> > e.emp_employee_id =
> ma.employee_id
> > and
> > mu.muid = ma.muid
> >
> > ) e
> >
> > left outer join
> >
> > (select
> emp_employee_id,
> > start_datetime, a.course_id, a.sched_id,
> >
> > module_status_id,
> > c.course_title, rank from
> > (select b.emp_employee_id,
> > a.start_datetime,
> > a.course_id,
> >
> > a.sched_id,
> > b.module_status_id, dense_rank() over
> >
> > (partition by b.emp_employee_id
> >
> > order by b.emp_employee_id,
> > a.start_datetime) rank from
> >
> > course_schedule a,
> course_assignment
=== message truncated ===
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: Changing result set metadata on the fly.
Posted by Brandon Goodin <br...@gmail.com>.
Paginated list only returns a subset of the results. This is
determined by the pageSize. It does not retain a total count.
If you perform a count using the resultset it will force the cursor to
traverse the whole resultset. Most databases do not load up large
results sets completely. So, when you perform your count using the
resultset you are already impacting your system in a negative way and
not saving yourself any performance.
My suggestion would be to use two sql calls. One using a count (as a
count sql statement would be more performant than a resultset
traversal) and the other sql statement to retrieve the results you
want from the resultset. The PaginatedList in iBatis will only
traverse the rows in the resultset that it needs to in order to
populate the corresponding objects with. Then when you call the next
page on the paginated list it retains the appropriate information to
reconnect to the database and grab the next section of the results
that you need.
With this strategy you save yourself on memory and cpu.
Brandon
On Thu, 9 Dec 2004 23:31:15 -0800 (PST), Kevin <ke...@yahoo.com> wrote:
> Thanks for taking time to respond to my question.
>
> Here is an example of what i am trying to do.
>
> I have a query that serves a dual purpose
> 1>
> getting me a count of all the records that meet a
> certain criteria
> 2>
> return a subset of the records of whole list i used
> for getting the count as i donot need the whole result
> set for display.
>
> The type of query executed depends on a field in the
> parameter class.
>
> I have attached my query below , if u look at it
> carefully u will see that the Select clause has a
> condition on "countQuery" and so does the where
> clause.
>
> Essentially the resultset columns change on the fly
> depending on the query selected.
>
> This as far as i am told is not supported in Ibatis.
>
> I was wondering why this is not supported and what are
> the alternatives rather than having 2 queries.
>
> Your suggestion of doing a size on the list will not
> work because the result set is only a subset. i.e i
> only display 100 out lets say 10000 records on the web
> layer to make it more effecient , so i set my where
> clause to give me the range of records whose list size
> will be 100 but the count should return 10000. The
> reson i need 10000 is for creating a web layer based
> paginated list.
>
> Does the paginated list in Ibatis support this?
>
> <select
> id="getEmployeeAndLastCourseCount"
>
> parameterClass="com.performixtech.emvolve.devmgr.trainingmanager.sqlmap.p
> aram.EmployeeAndLastCourseParam"
>
> resultClass="com.performixtech.emvolve.devmgr.trainingmanager.model.Train
> ingRequestListModel">
> Select
>
> <isNotNull
> property="countQuery">
>
> count(*)
> as count
>
> </isNotNull>
>
> <isNull
> property="countQuery">
>
> rownumber,
>
> employeeId,
>
> employeeName,
>
> managerId,
>
> managerName,
>
> managementUnitId,
>
> managementUnitName,
>
> courseId,
>
> courseTitle,
>
> courseDate
>
> </isNull>
>
> FROM
> (
> SELECT
> rownum as rownumber,
> e.emp_employee_id as employeeId,
> e.emp_name as employeeName,
> e.emp_manager as managerId,
> e.manager_name as managerName,
> e.muid as managementUnitId,
> e.name as managementUnitName,
> cS.course_Id as courseId,
> cS.course_title as courseTitle,
> cS.start_datetime as courseDate,
>
> cS.module_status_id
> FROM
> (select e.emp_employee_id,
> e.emp_name,
> em.emp_employee_id
>
> as emp_manager,
> em.emp_name as manager_name, mu.muid, mu.name
>
> from employee e,
> employee em, management_unit mu,
>
> mgmt_unit_association ma
>
> where
> e.emp_manager = em.emp_employee_id
> and
> e.emp_employee_id = ma.employee_id
> and
> mu.muid = ma.muid
>
> ) e
>
> left outer join
>
> (select emp_employee_id,
> start_datetime, a.course_id, a.sched_id,
>
> module_status_id,
> c.course_title, rank from
> (select b.emp_employee_id,
> a.start_datetime,
> a.course_id,
>
> a.sched_id,
> b.module_status_id, dense_rank() over
>
> (partition by b.emp_employee_id
>
> order by b.emp_employee_id,
> a.start_datetime) rank from
>
> course_schedule a, course_assignment b
> where
>
> <isNotNull
> property="moduleStatusId">
>
> b.module_status_id = #moduleStatusId#
>
> </isNotNull>
>
> and a.sched_id =
> b.sched_id
>
> ) a, course c where rank
> = 1 and a.course_id = c.course_id ) cS
>
> on
>
> e.emp_employee_id =
> cS.emp_employee_id
>
> where
>
> <isNotNull
> property="rownum">
>
> rownum
> >= #rownum#
>
> </isNotNull>
>
> <isNotNull prepend="AND"
> property="managerName">
>
> and
> e.manager_name like #managerName#
>
> </isNotNull>
>
> <isNotNull prepend="AND"
> property="employeeName">
>
> and
> e.emp_name like #employeeName#
>
> </isNotNull>
>
> <isNotNull prepend="AND"
> property="managementUnitName">
>
> e.name
> like #managementUnitName#
>
> </isNotNull>
>
> <isNotNull prepend="AND"
> property="managerId">
>
> e.emp_manager = #managerId#
>
> </isNotNull>
>
> <isNull
> property="countQuery">
>
> <isNull
> property="orderByColumn">
>
> order by employeeName
>
> </isNull>
>
> <isNotNull property="orderByColumn">
>
> order by #orderByColumn#
>
> </isNotNull>
>
> <isNull
> property="orderByDesc">
>
> asc
>
> </isNull>
>
> <isNotNull property="orderByDesc">
>
> desc
>
> </isNotNull>
>
> </isNull>
>
> )
>
> <isNull
> property="countQuery">
>
> WHERE
>
> rownumber between
>
> #startRownum# and #endRownum#
>
> </isNull>
> </select>
>
>
>
> --- Brandon Goodin <br...@gmail.com> wrote:
>
> > I'm not completely sure what you are asking. But, if
> > you want to count
> > your results you could simply check the .size()
> > method on your
> > returned list. Maybe you could provide a clearer
> > example.
> >
> > Brandon
> >
> >
> > On Thu, 9 Dec 2004 22:15:44 -0800 (PST), Kevin
> > <ke...@yahoo.com> wrote:
> > >
> > > Hi
> > >
> > > I just found out from someone that Ibatis does not
> > > allow changing the resultset metadata on the fly.
> > >
> > > This does not allow me to have the same query for
> > > count of records as well as getting the result
> > set. Is
> > > there any alternate way ibatis does something like
> > > this.
> > >
> > > It is not a big issue considering the power of the
> > > what Ibatis actually provides , but was curious to
> > > learn the reason for leaving this out.
> > >
> > > Thanks
> > > Kevin
> > >
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! Mail - Find what you need with new enhanced
> > search.
> > > http://info.mail.yahoo.com/mail_250
> > >
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
Fwd: Changing result set metadata on the fly.
Posted by Brandon Goodin <br...@gmail.com>.
This message was sent directly to me therefore i am forwarding it back
to the list.
Brandon
---------- Forwarded message ----------
From: Kevin <ke...@yahoo.com>
Date: Thu, 9 Dec 2004 23:31:15 -0800 (PST)
Subject: Re: Changing result set metadata on the fly.
To: Brandon Goodin <br...@gmail.com>
Thanks for taking time to respond to my question.
Here is an example of what i am trying to do.
I have a query that serves a dual purpose
1>
getting me a count of all the records that meet a
certain criteria
2>
return a subset of the records of whole list i used
for getting the count as i donot need the whole result
set for display.
The type of query executed depends on a field in the
parameter class.
I have attached my query below , if u look at it
carefully u will see that the Select clause has a
condition on "countQuery" and so does the where
clause.
Essentially the resultset columns change on the fly
depending on the query selected.
This as far as i am told is not supported in Ibatis.
I was wondering why this is not supported and what are
the alternatives rather than having 2 queries.
Your suggestion of doing a size on the list will not
work because the result set is only a subset. i.e i
only display 100 out lets say 10000 records on the web
layer to make it more effecient , so i set my where
clause to give me the range of records whose list size
will be 100 but the count should return 10000. The
reson i need 10000 is for creating a web layer based
paginated list.
Does the paginated list in Ibatis support this?
<select
id="getEmployeeAndLastCourseCount"
parameterClass="com.performixtech.emvolve.devmgr.trainingmanager.sqlmap.p
aram.EmployeeAndLastCourseParam"
resultClass="com.performixtech.emvolve.devmgr.trainingmanager.model.Train
ingRequestListModel">
Select
<isNotNull
property="countQuery">
count(*)
as count
</isNotNull>
<isNull
property="countQuery">
rownumber,
employeeId,
employeeName,
managerId,
managerName,
managementUnitId,
managementUnitName,
courseId,
courseTitle,
courseDate
</isNull>
FROM
(
SELECT
rownum as rownumber,
e.emp_employee_id as employeeId,
e.emp_name as employeeName,
e.emp_manager as managerId,
e.manager_name as managerName,
e.muid as managementUnitId,
e.name as managementUnitName,
cS.course_Id as courseId,
cS.course_title as courseTitle,
cS.start_datetime as courseDate,
cS.module_status_id
FROM
(select e.emp_employee_id,
e.emp_name,
em.emp_employee_id
as emp_manager,
em.emp_name as manager_name, mu.muid, mu.name
from employee e,
employee em, management_unit mu,
mgmt_unit_association ma
where
e.emp_manager = em.emp_employee_id
and
e.emp_employee_id = ma.employee_id
and
mu.muid = ma.muid
) e
left outer join
(select emp_employee_id,
start_datetime, a.course_id, a.sched_id,
module_status_id,
c.course_title, rank from
(select b.emp_employee_id,
a.start_datetime,
a.course_id,
a.sched_id,
b.module_status_id, dense_rank() over
(partition by b.emp_employee_id
order by b.emp_employee_id,
a.start_datetime) rank from
course_schedule a, course_assignment b
where
<isNotNull
property="moduleStatusId">
b.module_status_id = #moduleStatusId#
</isNotNull>
and a.sched_id =
b.sched_id
) a, course c where rank
= 1 and a.course_id = c.course_id ) cS
on
e.emp_employee_id =
cS.emp_employee_id
where
<isNotNull
property="rownum">
rownum
>= #rownum#
</isNotNull>
<isNotNull prepend="AND"
property="managerName">
and
e.manager_name like #managerName#
</isNotNull>
<isNotNull prepend="AND"
property="employeeName">
and
e.emp_name like #employeeName#
</isNotNull>
<isNotNull prepend="AND"
property="managementUnitName">
e.name
like #managementUnitName#
</isNotNull>
<isNotNull prepend="AND"
property="managerId">
e.emp_manager = #managerId#
</isNotNull>
<isNull
property="countQuery">
<isNull
property="orderByColumn">
order by employeeName
</isNull>
<isNotNull property="orderByColumn">
order by #orderByColumn#
</isNotNull>
<isNull
property="orderByDesc">
asc
</isNull>
<isNotNull property="orderByDesc">
desc
</isNotNull>
</isNull>
)
<isNull
property="countQuery">
WHERE
rownumber between
#startRownum# and #endRownum#
</isNull>
</select>
--- Brandon Goodin <br...@gmail.com> wrote:
> I'm not completely sure what you are asking. But, if
> you want to count
> your results you could simply check the .size()
> method on your
> returned list. Maybe you could provide a clearer
> example.
>
> Brandon
>
>
> On Thu, 9 Dec 2004 22:15:44 -0800 (PST), Kevin
> <ke...@yahoo.com> wrote:
> >
> > Hi
> >
> > I just found out from someone that Ibatis does not
> > allow changing the resultset metadata on the fly.
> >
> > This does not allow me to have the same query for
> > count of records as well as getting the result
> set. Is
> > there any alternate way ibatis does something like
> > this.
> >
> > It is not a big issue considering the power of the
> > what Ibatis actually provides , but was curious to
> > learn the reason for leaving this out.
> >
> > Thanks
> > Kevin
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Mail - Find what you need with new enhanced
> search.
> > http://info.mail.yahoo.com/mail_250
> >
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: Changing result set metadata on the fly.
Posted by Brandon Goodin <br...@gmail.com>.
I'm not completely sure what you are asking. But, if you want to count
your results you could simply check the .size() method on your
returned list. Maybe you could provide a clearer example.
Brandon
On Thu, 9 Dec 2004 22:15:44 -0800 (PST), Kevin <ke...@yahoo.com> wrote:
>
> Hi
>
> I just found out from someone that Ibatis does not
> allow changing the resultset metadata on the fly.
>
> This does not allow me to have the same query for
> count of records as well as getting the result set. Is
> there any alternate way ibatis does something like
> this.
>
> It is not a big issue considering the power of the
> what Ibatis actually provides , but was curious to
> learn the reason for leaving this out.
>
> Thanks
> Kevin
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - Find what you need with new enhanced search.
> http://info.mail.yahoo.com/mail_250
>