ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: nested iteration problem
Date Sat, 13 May 2006 16:05:27 GMT
I don't think this relates to any of the iterate bugs in iBATIS - it doesn't
look like any of the issues that I'm aware of.  It would be helpful for you
to turn on logging and see the generated statement.  My guess is that
adding <isNotEmpty> tags around the first two iterates, and changing with
the "prepends" might solve the problem - but it is hard to tell without more
detail.  Again, statement logging will help you diagnose this.  Please see
the WIKI pages for information on how to turn on logging.

Jeff Butler


On 5/12/06, Rahesh Kumar <rahesh@gmail.com> wrote:
>
>  Jeff,
>
> I have one more query.As you said this is not nested iteration problem.
> But I have one more problem why is multiple iteration tags not working?
>
> for eg I have query like
>
>
> select prj.group_name Group_name, prj.PROJECT_NAME PROJECT_NAME ,
> prj.status Status, EXECUTION_TYPE ,Project_Estimated_Cost,
>
> actual_projectcost(prj.project_id) actual_cost, (Project_Estimated_Cost -
> actual_projectcost(prj.project_id)) Difference_cost
>
> from PROJECTS prj,Contractor_projects contprj
>
> <dynamic prepend="WHERE">
>
> prj.project_id = contprj.project_id AND contprj.vendor_id =
> #selectedVendor#
>
> <iterate prepend="AND" property="selectedStatus" open="(" close=")"
> conjunction="OR">
>
> prj.status = #selectedStatus[]#
>
> </iterate>
>
> <iterate prepend="AND" property="selectedLocation" open="(" close=")"
> conjunction="OR">
>
> execution_type = #selectedLocation[]#
>
> </iterate>
>
> <isNotEmpty prepend="AND" property="selectedProjects">
>
> <iterate prepend="AND" property="selectedProjects" open="(" close=")"
> conjunction="OR">prj.project_id = #selectedProjects[]#</iterate>
>
> </isNotEmpty>
>
> <isNotEmpty prepend="AND" property="selectedProjectGroups">
>
> <iterate prepend="AND" property="selectedProjectGroups" open="(" close=")"
> conjunction="OR">group_id = #selectedProjectGroups[]#</iterate>
>
> </isNotEmpty>
>
> </dynamic>
>
> group by prj.group_name , prj.PROJECT_NAME , prj.status , EXECUTION_TYPE
> ,Project_Estimated_Cost ,prj.project_id
>
> order by prj.group_name , prj.PROJECT_NAME
>
>
>
> when i execute this it gives me an error saying that the statement is not
> constructed properly. but when i run the same query with only one iterate
> tag its working fine. Any suggestions are welcome.I need it urgently.Do we
> have any patches released for this. Thanks
>
> Rahesh
>
>
>
> On 5/12/06, Jeff Butler <jeffgbutler@gmail.com> wrote:
> >
> >  This is somewhat convoluted because of using maps...and I'm just
> > typing, so there may be errors.  But this is the general idea:
> >
> > List sundaysList = new ArrayList();
> >
> > Map sundaysMap = new HashMap();
> > sundaysMap.put("firstSunday", some_date);
> > sundaysMap.put("lastSunday", some_date);
> > sundaysList.add(sundaysMap);
> >
> > sundaysMap = new HashMap();
> > sundaysMap.put("firstSunday", some_date);
> > sundaysMap.put("lastSunday", some_date);
> > sundaysList.add(sundaysMap);
> >
> > Map parms = new HashMap();
> > parms.put("sundaysList", sundaysList);
> >
> > queryForList("statement_id", parms);
> >
> > Then you can do this in the SQL map:
> >
> > <iterate property="sundaysList" ...>
> >    ...
> >    where actionDate between #sundaysList[].firstSunday# and
> > #sundaysList[].lastSunday#
> > </iterate>
> >
> > Jeff Butler
> >
> > On 5/11/06, Rahesh Kumar <rahesh@gmail.com> wrote:
> > >
> > >  Hi jeff,
> > >
> > > Thanks for your solutions.can you provide me some example
> > > implementation as I am new to IBATIS, I am finding it bit difficult to use
> > > your solution directly.
> > >
> > > Thanks in advance
> > >  Rahesh
> > >
> > >
> > >  On 5/11/06, Jeff Butler <jeffgbutler@gmail.com > wrote:
> > > >
> > > >  This isn't nested iteration.
> > > >
> > > > What you should do is have a map property called something like
> > > > "sundaysList" that returns a List.  That list should contain other maps
with
> > > > firstSunday and lastSunday properties.  Then iterate over the "sundays"
> > > > property and specify the values like "sundaysList[].firstSunday" and
> > > > "sundaysList[].lastSunday".
> > > >
> > > > Jeff Butler
> > > >
> > > >
> > > > On 5/11/06, Rahesh Kumar <rahesh@gmail.com> wrote:
> > > > >
> > > > >  Hi,
> > > > >
> > > > > Need your help in solving this below mentioned problem, I am using
> > > > > IBatis 2.0 version ,I am facing problem as given below
> > > > >
> > > > >
> > > > > I have a statement like one below
> > > > >
> > > > >
> > > > > <select id="getVendorTSStatusList"
> > > > > resultMap="vendorTimesheetStatusResult">
> > > > >
> > > > > select
> > > > >
> > > > > ( select group_name projectgroupname from project_groups where
> > > > > group_id =
> > > > >
> > > > > (select group_id from projects where project_id = cp.project_id)
> > > > >
> > > > > ) projectgroupname , project_name , contractor_name,
> > > > > timesheet_approver_name, working_from, regular_rate,
> > > > >
> > > > > offshore_regular_rate, contractor_upi, project_id from
> > > > > CONTRACTOR_PROJECTS cp where ROWID IN
> > > > >
> > > > > (select MAX(rowid) from CONTRACTOR_PROJECTS GROUP BY
> > > > > contractor_upi) and vendor_id = #selectedVendors#
> > > > >
> > > > > <dynamic>
> > > > >
> > > > > <isNotNull prepend="AND" property="firstSundayList">
> > > > >
> > > > > <iterate prepend="AND" property="firstSundayList" open="("
> > > > > close=")" conjunction="OR">
> > > > >
> > > > > contractor_upi not in (select user_upi from
> > > > > user_profile_delete_log
> > > > >
> > > > > where action_date <![CDATA[<]]> (TO_DATE(#firstSundayList[]#,
> > > > > 'MM/DD/YYYY')-6))
> > > > >
> > > > > or contractor_upi in (select user_upi from user_profile_delete_log
> > > > > where action_date
> > > > >
> > > > > between (TO_DATE(#firstSundayList[]#, 'MM/DD/YYYY')-6) and
> > > > > (TO_DATE(#lastSundayList[]#, 'MM/DD/YYYY') + 1))
> > > > >
> > > > > or contractor_upi in (select user_upi from user_profile_delete_log
> > > > > where
> > > > >
> > > > > (user_upi,action_date) in (select user_upi, max(action_date) as
> > > > > action_date
> > > > >
> > > > > from user_profile_delete_log group by user_upi )
> > > > >
> > > > > and action_date <![CDATA[<]]> (TO_DATE(#firstSundayList[]#,
> > > > > 'MM/DD/YYYY')-6) and action = 'undeleted')
> > > > >
> > > > > </iterate>
> > > > >
> > > > > </isNotNull>
> > > > >
> > > > > </dynamic>
> > > > >
> > > > > <!-- avoid contractor duplication -->
> > > > >
> > > > > group by project_name , contractor_name, timesheet_approver_name,
> > > > > working_from, regular_rate,
> > > > >
> > > > > offshore_regular_rate, contractor_upi, project_id order by
> > > > > contractor_name , projectgroupname , project_name
> > > > >
> > > > > </select>
> > > > >
> > > > >
> > > > >
> > > > > and I am passing the values via a HashMap , the value as set in
> > > > > HashMap are selectedVendors = '0000000001' , firstSundayList = [07/03/2005]
> > > > > and lastSundayList = [07/24/2005] . when I pass this to the database
, I am
> > > > > getting the number format exception.
> > > > >
> > > > > I am attching the stack trace for your kind reference.Pleaseadvice
me where the issue
> > > > > is.If u have any patch for this, Please send it across.Thanks
> > > > >
> > > > >
> > > > >
> > > > > com.ibatis.common.jdbc.exception.NestedSQLException:
> > > > >
> > > > > --- The error occurred in ctrs/persistence/sql/Reports.xml.
> > > > >
> > > > > --- The error occurred while preparing the mapped statement for
> > > > > execution.
> > > > >
> > > > > --- Check the getVendorTSStatusList.
> > > > >
> > > > > --- Check the parameter map.
> > > > >
> > > > > --- Cause: com.ibatis.common.beans.ProbeException: Error getting
> > > > > ordinal list from JavaBean. Cause java.lang.NumberFormatException:
> > > > >
> > > > >
> > > > > Caused by: java.lang.NumberFormatException:
> > > > >
> > > > > Caused by: com.ibatis.common.beans.ProbeException: Error getting
> > > > > ordinal list from JavaBean. Cause java.lang.NumberFormatException:
> > > > >
> > > > >
> > > > > Caused by: java.lang.NumberFormatException:
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> > > > > (GeneralStatement.java:188)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
> > > > > (GeneralStatement.java:123)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> > > > > SqlMapExecutorDelegate.java:610)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> > > > > SqlMapExecutorDelegate.java:584)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> > > > > SqlMapSessionImpl.java:101)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(
> > > > > SqlMapClientImpl.java:78)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > ctrs.persistence.dao.sqlmapdao.ReportsSqlMapDao.getVendorTSStatusList
> > > > > (ReportsSqlMapDao.java:46)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > java.lang.reflect.Method.invoke(Native Method)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:64)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > $Proxy8.getVendorTSStatusList(Unknown Source)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > ctrs.domain.logic.ReportsLogic.getVendorTSStatusList(
> > > > > ReportsLogic.java:145)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > ctrs.actions.TimesheetsAction.generateVendorReport(
> > > > > TimesheetsAction.java:1335)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > java.lang.reflect.Method.invoke(Native Method)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > org.apache.struts.actions.DispatchAction.dispatchMethod(
> > > > > DispatchAction.java:270)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > org.apache.struts.actions.DispatchAction.execute(
> > > > > DispatchAction.java:187)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > org.apache.struts.action.RequestProcessor.processActionPerform(
> > > > > RequestProcessor.java:431)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > org.apache.struts.action.RequestProcessor.process(
> > > > > RequestProcessor.java:236)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > org.apache.struts.action.ActionServlet.process(ActionServlet.java
> > > > > :1196)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > org.apache.struts.action.ActionServlet.doPost(ActionServlet.java
> > > > > :432)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(
> > > > > StrictServletInstance.java:110)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(
> > > > > StrictLifecycleServlet.java:174)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.servlet.IdleServletState.service(
> > > > > StrictLifecycleServlet.java:313)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(
> > > > > StrictLifecycleServlet.java:116)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.servlet.ServletInstance.service(
> > > > > ServletInstance.java:283)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch
> > > > > (ValidServletReferenceState.java:42)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(
> > > > > ServletInstanceReference.java:40)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch
> > > > > (WebAppRequestDispatcher.java:948)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(
> > > > > WebAppRequestDispatcher.java:530)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(
> > > > > WebAppRequestDispatcher.java:176)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(
> > > > > WebAppInvoker.java:79)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(
> > > > > WebAppInvoker.java:201)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation
> > > > > (CachedInvocation.java:71)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke
> > > > > (CacheableInvocationContext.java:114)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(
> > > > > ServletRequestProcessor.java:186)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(
> > > > > OSEListener.java:334)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(
> > > > > HttpConnection.java:56)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.http.HttpConnection.readAndHandleRequest(
> > > > > HttpConnection.java:610)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.http.HttpConnection.run(HttpConnection.java:431)
> > > > >
> > > > > [5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at
> > > > > com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Thanks & Regards,
> > > > >
> > > > > Raheshkumar.R
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> >
>

Mime
View raw message