好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

ibatis的动态sql

  1   <  select   id  ="getUsers" 
  2  
  3   parameterClass  ="user" 
  4  
  5   resultMap  ="get-user-result"  >  
  6  
  7   select
   8  
  9   id,
  10  
 11   name,
  12  
 13   sex
  14  
 15   from t_user
  16  
 17   <  dynamic   prepend  ="WHERE"  > 
 18  
 19      <  isNotEmpty   prepend  ="AND"   property  ="name"  > 
 20  
 21          name like #name#
  22  
 23      </  isNotEmpty  > 
 24  
 25      <  isNotEmpty   prepend  ="AND"   property  ="address"  > 
 26  
 27          address like #address#
  28  
 29      </  isNotEmpty  > 
 30  
 31   </  dynamic  > 
 32  
 33   </  select  > 

 

      通过dynamic 节点,可以定义了一个动态的WHERE 子句。此WHERE 子句中将可能包含两个针对name 和address 字段的判断条件。而这两个字段是否加入检索取决于用户所提供的查询条件。

 

      这个节点对应的语义是,如果参数类的"name"属性非空(isNotEmpty,即非空字符串]]),则在生成的SQL Where字句中包括判定条件(name like #name#),其中#name#将以参数类的name属性值填充。

 

      name属性对应的isNotEmpty节点,由于ibatis会自动判定是否需要追加prepend前缀,这里(name like #name#)是WHERE 子句中的第一个条件子句,无需AND 前缀,所以自动省略。

 

      判定节点并非仅限于isNotEmpty,ibatis中提供了丰富的判定定义功能。可以分两类:

 

一、 一元判定

 

一元判定是针对属性值本身的判定,如属性是否为NULL,是否为空值等。

上面示例中isNotEmpty就是典型的一元判定。

 

一元判定节点有:

<isPropertyAvailable> 参数类中是否提供了此属性

<isNotPropertyAvailable> 与<isPropertyAvailable>相反

<isNull> 属性值是否为NULL

<isNotNull> 与<isNull>相反

<isEmpty> 如果属性为Collection或者String,其size是否<1,

如果非以上两种类型,则通过

String.valueOf(属性值)

获得其String类型的值后,判断其size是否<1

<isNotEmpty> 与<isEmpty>相反。

 

 

二、二元判定

 

二元判定有两个判定参数,一是属性名,而是判定值,如

<isGreaterThan prepend="AND" property="age"

compareValue="18">

(age=#age#)

</isGreaterThan>

其中,property="age"指定了属性名]age],compareValue=]18]指明

了判定值为]18]。

上面判定节点isGreaterThan 对应的语义是:如果age 属性大于

18(compareValue),则在SQL中加入(age=#age#)条件。

二元判定节点有:

节点名 属性值与compareValues的关系

<isEqual> 相等。

<isNotEqual> 不等。

<isGreaterThan> 大于

<isGreaterEqual> 大于等于

<isLessThan> 小于

<isLessEqual> 小于等于

2.列子

对于一些特殊符号,如大于号>、小于号< 等需要写在<![CDATA[]]中方可有效,否则失效。
1、动态SQL片段 通过SQL片段达到代码复用
  1   <!--   动态条件分页查询   -->  
  2           <  sql   id  ="sql_count"  >  
  3                   select count(*) 
   4           </  sql  >  
  5           <  sql   id  ="sql_select"  >  
  6                   select * 
   7           </  sql  >  
  8           <  sql   id  ="sql_where"  >  
  9                   from icp 
  10                   <  dynamic   prepend  ="where"  >  
 11                           <  isNotEmpty   prepend  ="and"   property  ="name"  >  
 12                                   name like ‘%$name$%‘ 
  13                           </  isNotEmpty  >  
 14                           <  isNotEmpty   prepend  ="and"   property  ="path"  >  
 15                                   path like ‘%path$%‘ 
  16                           </  isNotEmpty  >  
 17                           <  isNotEmpty   prepend  ="and"   property  ="area_id"  >  
 18                                   area_id = #area_id# 
  19                           </  isNotEmpty  >  
 20                           <  isNotEmpty   prepend  ="and"   property  ="hided"  >  
 21                                   hided = #hided# 
  22                           </  isNotEmpty  >  
 23                   </  dynamic  >  
 24                   <  dynamic   prepend  =""  >  
 25                           <  isNotNull   property  ="_start"  >  
 26                                   <  isNotNull   property  ="_size"  >  
 27                                           limit #_start#, #_size# 
  28                                   </  isNotNull  >  
 29                           </  isNotNull  >  
 30                   </  dynamic  >  
 31           </  sql  >  
 32           <  select   id  ="findByParamsForCount"   parameterClass  ="map"   resultClass  ="int"  >  
 33                   <  include   refid  ="sql_count"  />  
 34                   <  include   refid  ="sql_where"  />  
 35           </  select  >  
 36           <  select   id  ="findByParams"   parameterClass  ="map"   resultMap  ="icp.result_base"  >  
 37                   <  include   refid  ="sql_select"  />  
 38                   <  include   refid  ="sql_where"  />  
 39  
 40           </  select  > 

 

 

2、数字范围查询 所传参数名称是捏造所得,非数据库字段,比如_img_size_ge、_img_size_lt字段
  1   <  isNotEmpty   prepend  ="and"   property  ="_img_size_ge"  >  
  2                                   <![CDATA[  
  3                                   img_size >= #_img_size_ge# 
   4                           ]]>  
  5                           </  isNotEmpty  >  
  6                           <  isNotEmpty   prepend  ="and"   property  ="_img_size_lt"  >  
  7                                   <![CDATA[  
  8                                   img_size < #_img_size_lt# 
   9                           ]]>  
 10                           </  isNotEmpty  >  
 11  
 12   

 

多次使用一个参数也是允许的

 

  1   <  isNotEmpty   prepend  ="and"   property  ="_now"  >  
  2                                   <![CDATA[  
  3                                               execplantime >= #_now# 
   4                                        ]]>  
  5                           </  isNotEmpty  >  
  6                           <  isNotEmpty   prepend  ="and"   property  ="_now"  >  
  7                                   <![CDATA[  
  8                                               closeplantime <= #_now# 
   9                                        ]]>  
 10                           </  isNotEmpty  > 

 

3、时间范围查询

 1   <  isNotEmpty   prepend  =""   property  ="_starttime"  >  
 2                                   <  isNotEmpty   prepend  ="and"   property  ="_endtime"  >  
 3                                           <![CDATA[  
 4                                           createtime >= #_starttime# 
  5                                           and createtime < #_endtime# 
  6                                    ]]>  
 7                                   </  isNotEmpty  >  
 8                           </  isNotEmpty  >  

 

4、in查询

 1   <  isNotEmpty   prepend  ="and"   property  ="_in_state"  >  
 2                                   state in (‘$_in_state$‘) 
  3                           </  isNotEmpty  > 

 

5、like查询

 1   <  isNotEmpty   prepend  ="and"   property  ="chnameone"  >  
 2                                   (chnameone like ‘%$chnameone$%‘ or spellinitial like ‘%$chnameone$%‘) 
  3                           </  isNotEmpty  >  
 4                           <  isNotEmpty   prepend  ="and"   property  ="chnametwo"  >  
 5                                   chnametwo like ‘%$chnametwo$%‘ 
  6                           </  isNotEmpty  >  

 

6、or条件

  1   <  isEqual   prepend  ="and"   property  ="_exeable"   compareValue  ="N"  >  
  2                                   <![CDATA[  
  3                                   (t.finished=‘11‘    or t.failure=3) 
   4                           ]]>  
  5                           </  isEqual  > 
  6  
  7   <  isEqual   prepend  ="and"   property  ="_exeable"   compareValue  ="Y"  >  
  8                                   <![CDATA[  
  9                                   t.finished in (‘10‘,‘19‘) and t.failure<3 
  10                           ]]>  
 11                           </  isEqual  > 

 

7、where子查询

  1   <  isNotEmpty   prepend  =""   property  ="exprogramcode"  >  
  2                                   <  isNotEmpty   prepend  =""   property  ="isRational"  >  
  3                                           <  isEqual   prepend  ="and"   property  ="isRational"   compareValue  ="N"  >  
  4                                                   code not in 
   5                                                   (select t.contentcode 
   6                                                   from cms_ccm_programcontent t 
   7                                                   where t.contenttype=‘MZNRLX_MA‘ 
   8                                                   and t.programcode = #exprogramcode#) 
   9                                           </  isEqual  >  
 10                                   </  isNotEmpty  >  
 11                           </  isNotEmpty  > 
 12  
 13   <  select   id  ="findByProgramcode"   parameterClass  ="string"   resultMap  ="cms_ccm_material.result"  >  
 14                   select * 
  15                   from cms_ccm_material 
  16                   where code in 
  17                   (select t.contentcode 
  18                   from cms_ccm_programcontent t 
  19                   where t.contenttype = ‘MZNRLX_MA‘ 
  20                   and programcode = #value#) 
  21                   order by updatetime desc 
  22           </  select  > 

 

9、函数的使用

  1   <!--   添加   -->  
  2           <  insert   id  ="insert"   parameterClass  ="RuleMaster"  >  
  3                   insert into rulemaster( 
   4                   name, 
   5                   createtime, 
   6                   updatetime, 
   7                   remark 
   8                   ) values ( 
   9                   #name#, 
  10                   now(), 
  11                   now(), 
  12                   #remark# 
  13                   ) 
  14                   <  selectKey   keyProperty  ="id"   resultClass  ="long"  >  
 15                           select LAST_INSERT_ID() 
  16                   </  selectKey  >  
 17           </  insert  >  
 18           <!--   更新   -->  
 19           <  update   id  ="update"   parameterClass  ="RuleMaster"  >  
 20                   update rulemaster set 
  21                   name = #name#, 
  22                   updatetime = now(), 
  23                   remark = #remark# 
  24                   where id = #id# 
  25           </  update  > 

 

10、map结果集 

  1   <!--   动态条件分页查询   -->  
  2           <  sql   id  ="sql_count"  >  
  3                   select count(a.*) 
   4           </  sql  >  
  5           <  sql   id  ="sql_select"  >  
  6                   select a.id                vid, 
   7                   a.img             imgurl, 
   8                   a.img_s         imgfile, 
   9                   b.vfilename vfilename, 
  10       b.name            name, 
  11                   c.id                sid, 
  12                   c.url             url, 
  13                   c.filename    filename, 
  14                   c.status        status 
  15           </  sql  >  
 16           <  sql   id  ="sql_where"  >  
 17                   From secfiles c, juji b, videoinfo a 
  18                   where 
  19                   a.id = b. videoid 
  20                   and b.id = c.segmentid 
  21                   and c.status = 0 
  22                   order by a.id asc,b.id asc,c.sortnum asc 
  23                   <  dynamic   prepend  =""  >  
 24                           <  isNotNull   property  ="_start"  >  
 25                                   <  isNotNull   property  ="_size"  >  
 26                                           limit #_start#, #_size# 
  27                                   </  isNotNull  >  
 28                           </  isNotNull  >  
 29                   </  dynamic  >  
 30           </  sql  >  
 31           <!--   返回没有下载的记录总数   -->  
 32           <  select   id  ="getUndownFilesForCount"   parameterClass  ="map"   resultClass  ="int"  >  
 33                   <  include   refid  ="sql_count"  />  
 34                   <  include   refid  ="sql_where"  />  
 35           </  select  >  
 36           <!--   返回没有下载的记录   -->  
 37           <  select   id  ="getUndownFiles"   parameterClass  ="map"   resultClass  ="java.util.HashMap"  >  
 38                   <  include   refid  ="sql_select"  />  
 39                   <  include   refid  ="sql_where"  />  
 40           </  select  > 

 

ibatis的动态sql

标签:

查看更多关于ibatis的动态sql的详细内容...

  阅读:26次