ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Emiliano Armellin <earmel...@ateikon.com>
Subject Dynamic Mapped Statements question
Date Fri, 11 Mar 2005 15:04:20 GMT
Hello,

I have to map in a dynamic statemet this sql:

|SELECT pgmr.a.*,
     pgmr.b.*
FROM pgmr.a,
     pgmr.b
WHERE pgmr.a.cdente = pgmr.b.cdente
 AND (
        lower(pgmr.a.company) like lower(#||company||#)
         OR
        (       
         lower(pgmr.a.firstname) like lower(#||firstname||#)
         AND
         lower(pgmr.a.lastname) like lower(#||lastname||#)
        )
     )
|

where |||company|||, |firstname| and |lastname| can be null or not 
independently

the only solution I've found 'till now is this:

|  <select id="searchClient" resultMap="clientResult" parameterClass="map">
    SELECT pgmr.a.*,
           pgmr.b.*
      FROM pgmr.a,
           pgmr.b
     WHERE pgmr.a.cdente = pgmr.b.cdente
     <dynamic prepend="AND">
     (
     <isNotEmpty property="||company||">
           lower(pgmr.a.company) like lower(#||company||#)
     </isNotEmpty>
     <isNotEmpty property="nm_map">
        <isNotEmpty property="||company||"> OR </isNotEmpty>
        (
         <isNotEmpty property="nm_map.||firstname||">
               lower(pgmr.a.||firstname||) like 
lower(#||nm_map.||firstname||#)
         </isNotEmpty>
         <isNotEmpty property="nm_map.||lastname||">
         <isNotEmpty property="nm_map.||firstname||"> AND </isNotEmpty>
               lower(pgmr.a.||lastname||) like 
lower(#||nm_map.||lastname||||#)
         </isNotEmpty>
         )       
     </isNotEmpty>
     )
     </dynamic>
  </select>  |

I've wrapped |firstname| and |lastname||||||| in nm_map, a submap of 
parameter map.

Well, I think this is not the best&simplest solution but I was not able 
to find an alternative.

Can you help me?



-- 
Emiliano Armellin

    *
      W_
      www.ateikon.com <http://www.ateikon.com>
    *
      @_
      earmellin@ateikon.com <mailto:earmellin@ateikon.com>
    *
      T_
      *+39 0422 452101*

Get Firefox! <http://www.spreadfirefox.com/?q=affiliates&id=15653&t=86>
Get Thunderbird <http://www.mozilla.org/products/thunderbird/>

Mime
View raw message