ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rahesh Kumar" <rah...@gmail.com>
Subject Re: nested iteration problem
Date Fri, 19 May 2006 16:18:22 GMT
Jeff,

Right now I am making the sql-map-config.xml to read the database properties
from a property file, but now my question is how should I access the
database configurations i set in the server rather than reading from a
property file. If you have some examples please send across. Thanks

Rahesh


On 5/13/06, Jeff Butler <jeffgbutler@gmail.com > wrote:
>
>  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