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: $substitution$ syntax with a one-to-many table relationship
Date Tue, 13 Jun 2006 22:57:08 GMT
In the second query the KEY_ID will be mapped to both substitution spots
since you don't specify a parameter class.  What you need is a way to pass
both the subTable and keyId to the second query (in a map).  iBATIS supports
that if the sub table value is also a part of the first result set.  For
example, you could do this (if SUB_TBL was a part of the first result set):

<sqlMap>
     <resultMap id="mainResultMap" class="com.sample.MainClass" >
          <result property="keyId" column="KEY_ID"/>
           .
           .
           .
          <result property="listFromOtherTable"
select="selectFromOtherTable" *column="{keyId=KEY_ID,subTable=SUB_TBL}*"/>
     </resultMap>


     <select id="selectFromMainTable" parameterClass="map"
remapResults="true" resultMap="mainResultMap">
          Select
               KEY_ID, ...
          From
               $mainTable$
          Where
               KEY_ID = #keyId#
     </select>


     <select id="selectFromOtherTable" resultClass="java.lang.String"
remapResults="true" *parameterClass="map"*>
          Select
               LIST_ITEM
          From
               $subTable$
          Where
               KEY_ID = #keyId#
     </select>
</sqlMap>

If that doesn't work, then you might have to make the two calls manually in
a DAO method.  You're really not losing anything if you have to do that -
because it's an N+1 query either way.

Jeff Butler


On 6/13/06, Ryan Schultz <ryanlschultz@gmail.com> wrote:
>
> Hello everyone,
>
> I have a question about using "$substitution$ syntax" in combination with
> a one-to-many table relationship.
>
> I have a result map with a property that is to be populated from a second
> table as a list of strings. The table name needs to be flexible between
> database environments like " DEVELOPMENT.SUB_TABLE" and "TEST.SUB_TABLE"
> so I am using $substitution$ syntax and passing the table name in the
> parameter map.
>
> I have included an example below that I know does not work because the
> KEY_ID column is being mapped to the $subTable$ spot.
>
> Is it possible to use the $substitution$ syntax to resolve the table name
> in the query that is on the "many" side of this relationship? Thanks in
> advance for any advice.
>
> Example:
>
> <sqlMap>
>      <resultMap id="mainResultMap" class="com.sample.MainClass" >
>           <result property="keyId" column="KEY_ID"/>
>            .
>            .
>            .
>           <result property="listFromOtherTable"
> select="selectFromOtherTable" column="KEY_ID"/>
>      </resultMap>
>
>
>      <select id="selectFromMainTable" parameterClass="map"
> remapResults="true" resultMap="mainResultMap">
>           Select
>                KEY_ID, ...
>           From
>                $mainTable$
>           Where
>                KEY_ID = #keyId#
>      </select>
>
>
>      <select id="selectFromOtherTable" resultClass="java.lang.String"
> remapResults="true">
>           Select
>                LIST_ITEM
>           From
>                $subTable$
>           Where
>                KEY_ID = #keyId#
>      </select>
> </sqlMap>
>

Mime
View raw message