ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Scott Zhu <scott...@gmail.com>
Subject Re: Passing parameters to sub selects
Date Fri, 25 Mar 2005 16:41:03 GMT
Hi, Phil, Can I ask you what was your work around for this problem? I
have a situation that the table name in the sub query needs to be
dynamically set.

   <resultMap id="offerResult" class="offer" groupBy="id">
      <result property="id" column="OfferID"/>
      <result property="name" column="Name"/>
      <result property="offerProductConditions" column="OfferID"
select="getProductContions"/>
   </resultMap>

   <select id="getOffersByFolderID" remapResults="true"
parameterClass="java.util.Map" resultMap="offerResult">
      <![CDATA[
      select o.OfferID,
             o.Name,
      from $UA_Offer$ o
      where o.FolderID=#folderID#
      ]]>
   </select>

   <select id="getProductConditions" remapResults="true"
parameterClass="int" resultClass="java.lang.String">
      <![CDATA[
      select Condition
      from $UA_OfferToProduct$
      where OfferID=#offerID#
      }}>
   </select>

I don't know how I can pass that as a parameter to the sub select. I
know I can use the new n:m solution. But it has a bug right now with
object contains two lists that use the same groupBy id.

On Fri, 18 Feb 2005 11:46:20 -0500, Phil Barnes
<phillip.barnes@gmail.com> wrote:
> Thanks for the reply Brandon.  That was indeed one of the "SQL-related
> options" I referred in my original message.
> 
> There are a few reasons I didn't automatically do this:  the first is
> that I did not write the original SQL, it was "handed off" to me, and
> the second is that I'm completely unfamiliar with the underlying data
> model, and third, I am under an (unreasonably) agressive timeline --
> obviously a difficult situation to be in as a developer.  ;)
> 
> Your second suggestion is another approach I had done in a different
> report, in which a JOIN would have been ideal (as the results from one
> select were used as parameters in the second select, etc.) -- reasons
> for not turning this into a JOIN, see the previous paragraph.  ;)
> 
> I suppose I was just hoping that there was something simple I was
> missing -- like specifying inline parameters in a results map or
> something.
> 
> Either way, I have a working solution, and 2 additional suggestions if
> it becomes a problem.  :)  Thanks.
> 
> On Fri, 18 Feb 2005 09:18:52 -0700, Brandon Goodin
> <brandon.goodin@gmail.com> wrote:
> > I'm curious why you don't simply write this as an actual subselect
> > since it passes back a single value java type.
> >
> > Also, result selects are a convenience feature for simple to
> > moderately complex scenarios. Don't be afraid to implement an iterator
> > in your dao class that iterates over the getResults results and calls
> > the getShippedTotal with the proper parameters for each item. You can
> > pass the object in your list as a parameter and populate a property on
> > it that is needed.
> >
> > Brandon
> >
> >
> > On Fri, 18 Feb 2005 10:27:15 -0500, Phil Barnes
> > <phillip.barnes@gmail.com> wrote:
> > > Hi all,
> > >
> > > First, I'd like to say "thanks" for all the work on iBATIS SqlMaps
> > > (and the DAO framework which I haven't tried -- yet!).  I'm currently
> > > working on a set of "web reports", and iBATIS has made a terrific time
> > > saver.
> > >
> > > My question is about how to pass paramters from a "parameterClass" to
> > > a subselect via a <result ... select=".."/> or if it's possible.
> > > Right now, I'm mapping the value directly to the SQL statement
> > > "dynamically", and passing that "aliased" column on to the subquery.
> > > However, this seems rather "clunky", especially for Date objects.
> > >
> > > An "working" example (reduced for brevity -- hopefully it still makes sense):
> > >
> > > <resultMap id="daily-sales" class="test.report.DailySalesResult">
> > >     <result property="customerNumber" column="customer_number"/>
> > >     <result property="shippedTotal"
> > > column="{customerId=customer_id,date=run_date}"
> > > select="DailySalesReport.getShippedTotal"/>
> > > </resultMap>
> > >
> > > <select id="getResults" resultMap="daily-sales"
> > > parameterClass="test.report.DailySalesReport">
> > >     SELECT   ca.account_number customer_number
> > >             ,brl.cust_account_id customer_id
> > >             ,SUM (brc.VALUE) forecast
> > >             ,#dateString# run_date
> > >         FROM budget_report_lookups brl
> > >             ,budget_report_cells brc
> > >             ,cust_accounts ca
> > >        WHERE brc.budget_cell_id = brl.budget_cell_id
> > >          AND brl.lookup_code NOT IN ('COS', 'DIR', 'FR')
> > >          AND brc.MONTH = TO_CHAR (#dateDB#, 'MON')
> > >          AND brc.YEAR = TO_CHAR (#dateDB#, 'YYYY')
> > >          AND ca.cust_account_id = brl.cust_account_id
> > >     GROUP BY ca.account_number
> > >             ,brl.cust_account_id
> > >             ,brc.YEAR
> > >             ,brc.MONTH
> > > </select>
> > >
> > > <select id="getShippedTotal" resultClass="double">
> > >         SELECT   SUM (NVL (ool.unit_selling_price, 0) * NVL
> > > (ool.shipped_quantity, 0)) ship_total
> > >             FROM order_lines ool
> > >            WHERE TRUNC (ool.actual_shipment_date, 'MM') = TRUNC
> > > (TO_DATE(#date#), 'MM')
> > >              AND ool.sold_to_org_id = #customerId#
> > >         GROUP BY ool.sold_to_org_id
> > > </select>
> > >
> > > As you can see, #dateString# is a string representation, which ends up
> > > looking something like '01-FEB-2005', whereas #dateDB# is the actual
> > > "Date object".
> > >
> > > Ideally, I'd like to write someting like:
> > > <result property="shippedTotal"
> > > column="{customerId=customer_id,date=#dateDB#}"
> > > select="DailySalesReport.getShippedTotal"/>
> > >
> > > where the #dateDB# field is pulled from the parameterClass and passed
> > > to the "subselect".
> > >
> > > I can think of a number of SQL-related solutions, but I was hoping
> > > there was something I can't seem to find in either the Dev Guide or on
> > > the Mailing List Archives.
> > >
> > > Thanks in advance,
> > >
> > > Phil..
> > >
> >
>

Mime
View raw message