ibatis动态条件匹配标签<dynamic prepend="WHERE">

1、项目中xml文件

  1 <?xml version="1.0" encoding="UTF-8" ?>
  2 <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
  3         "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
  4 <sqlMap namespace="MI950">
  5     <resultMap id="Mi950Result" class="com.yondervision.mi.dto.MI950">
  6         <!--
  7           WARNING - This element is automatically generated by Abator for iBATIS, do not modify.
  8           This element was generated on Fri Sep 27 20:46:09 CST 2013.
  9         -->
 10         <result column="REGID" property="regid" jdbcType="VARCHAR"/>
 11         <result column="PERSONALID" property="personalid" jdbcType="VARCHAR"/>
 12         <result column="CENTERID" property="centerid" jdbcType="VARCHAR"/>
 13         <result column="FILEDATE" property="filedate" jdbcType="VARCHAR"/>
 14         <result column="FILENAME" property="filename" jdbcType="VARCHAR"/>
 15         <result column="FILEPATH" property="filepath" jdbcType="VARCHAR"/>
 16         <result column="CHANNEL" property="channel" jdbcType="VARCHAR"/>
 17         <result column="VALIDFLAG" property="validflag" jdbcType="VARCHAR"/>
 18         <result column="DATEMODIFIED" property="datemodified" jdbcType="VARCHAR"/>
 19         <result column="DATECREATED" property="datecreated" jdbcType="VARCHAR"/>
 20         <result column="FREEUSE1" property="freeuse1" jdbcType="VARCHAR"/>
 21         <result column="FREEUSE2" property="freeuse2" jdbcType="VARCHAR"/>
 22         <result column="FREEUSE3" property="freeuse3" jdbcType="VARCHAR"/>
 23         <result column="FREEUSE4" property="freeuse4" jdbcType="INTEGER"/>
 24         <result column="USERNAME" property="username" jdbcType="VARCHAR"/>
 25         <result column="CERTINUM" property="certinum" jdbcType="VARCHAR"/>
 26     </resultMap>
 27 
 28     <resultMap id="Mi950Result1" class="com.yondervision.mi.dto.MI950">
 29         <!--
 30           WARNING - This element is automatically generated by Abator for iBATIS, do not modify.
 31           This element was generated on Fri Sep 27 20:46:09 CST 2013.
 32         -->
 33         <result column="REGID" property="regid" jdbcType="VARCHAR"/>
 34         <result column="PERSONALID" property="personalid" jdbcType="VARCHAR"/>
 35         <result column="CENTERID" property="centerid" jdbcType="VARCHAR"/>
 36         <result column="FILEDATE" property="filedate" jdbcType="VARCHAR"/>
 37         <result column="FILENAME" property="filename" jdbcType="VARCHAR"/>
 38         <result column="FILEPATH" property="filepath" jdbcType="VARCHAR"/>
 39         <result column="CHANNEL" property="channel" jdbcType="VARCHAR"/>
 40         <result column="VALIDFLAG" property="validflag" jdbcType="VARCHAR"/>
 41         <result column="DATEMODIFIED" property="datemodified" jdbcType="VARCHAR"/>
 42         <result column="DATECREATED" property="datecreated" jdbcType="VARCHAR"/>
 43         <result column="FREEUSE1" property="freeuse1" jdbcType="VARCHAR"/>
 44         <result column="FREEUSE2" property="freeuse2" jdbcType="VARCHAR"/>
 45         <result column="FREEUSE3" property="freeuse3" jdbcType="VARCHAR"/>
 46         <result column="FREEUSE4" property="freeuse4" jdbcType="INTEGER"/>
 47     </resultMap>
 48 
 49     <select id="findByExample" resultMap="Mi950Result"
 50             parameterClass="com.yondervision.mi.form.MI950Form">
 51         <!--
 52           WARNING - This element is automatically generated by Abator for iBATIS, do not modify.
 53           This element was generated on Fri Sep 27 20:46:09 CST 2013.
 54         -->
 55         select t.REGID, t.PERSONALID, t.CENTERID, t.FILEDATE, t.FILENAME, t.FILEPATH, t.CHANNEL, t.VALIDFLAG,
 56         t.DATEMODIFIED, t.DATECREATED,t.FREEUSE1, t.FREEUSE2, t.FREEUSE3, t.FREEUSE4,d.USERNAME as USERNAME, d.CERTINUM as CERTINUM
 57         from MI950 t,MI029 d WHERE 1=1 and t.personalid = d.personalid AND t.VALIDFLAG = '1'
 58         <isNotEmpty prepend="and" property="centerid">
 59             t.CENTERID = #centerid:VARCHAR#
 60         </isNotEmpty>
 61         <isNotEmpty prepend="and" property="personalid">
 62             t.PERSONALID = #personalid:VARCHAR#
 63         </isNotEmpty>
 64         <isNotEmpty prepend="and" property="startdate">
 65             <![CDATA[
 66                 to_date(t.FILEDATE,'yyyy-mm-dd') >= to_date('$startdate$','yyyy-mm-dd')
 67             ]]>
 68         </isNotEmpty>
 69         <isNotEmpty prepend="and" property="enddate">
 70             <![CDATA[
 71                to_date(t.FILEDATE,'yyyy-mm-dd') <= to_date('$enddate$','yyyy-mm-dd')
 72             ]]>
 73         </isNotEmpty>
 74         ORDER BY t.FILEDATE DESC
 75     </select>
 76     <insert id="add" parameterClass="com.yondervision.mi.dto.MI950">
 77         <!--
 78           WARNING - This element is automatically generated by Abator for iBATIS, do not modify.
 79           This element was generated on Fri Sep 27 20:46:09 CST 2013.
 80         -->
 81         insert into MI950 (REGID, PERSONALID, CENTERID, FILEDATE, FILENAME, FILEPATH, CHANNEL,
 82         VALIDFLAG, DATEMODIFIED, DATECREATED,
 83         FREEUSE1, FREEUSE2, FREEUSE3, FREEUSE4)
 84         values (#regid:VARCHAR#, #personalid:VARCHAR#, #centerid:VARCHAR#, #filedate:VARCHAR#,
 85         #filename:VARCHAR#,
 86         #filepath:VARCHAR#, #channel:VARCHAR#, #validflag:VARCHAR#, #datemodified:VARCHAR#,
 87         #datecreated:VARCHAR#,
 88         #freeuse1:VARCHAR#, #freeuse2:VARCHAR#, #freeuse3:VARCHAR#, #freeuse4:INTEGER#)
 89     </insert>
 90     <select id="findAll" resultMap="Mi950Result">
 91         SELECT * FROM MI950
 92     </select>
 93 
 94     <select id="getCount" resultClass="java.lang.Integer" parameterClass="com.yondervision.mi.form.MI950Form" >
 95         SELECT count(*) FROM MI950
 96         WHERE  1=1
 97         <isNotEmpty prepend="and" property="centerid">
 98             CENTERID = #centerid:VARCHAR#
 99         </isNotEmpty>
100         <isNotEmpty prepend="and" property="personalid">
101             PERSONALID = #personalid:VARCHAR#
102         </isNotEmpty>
103         <isNotEmpty prepend="and" property="startdate">
104             <![CDATA[
105               to_date(FILEDATE,'yyyy-mm-dd') >= to_date('$startdate$','yyyy-mm-dd')
106             ]]>
107         </isNotEmpty>
108         <isNotEmpty prepend="and" property="enddate">
109             <![CDATA[
110               to_date(FILEDATE,'yyyy-mm-dd') <= to_date('$enddate$','yyyy-mm-dd')
111             ]]>
112         </isNotEmpty>
113     </select>
114     <select id="selectByExampleMi950" resultMap="Mi950Result1" parameterClass="com.yondervision.mi.dto.MI950">
115         SELECT * FROM MI950
116         <dynamic prepend="WHERE">
117             <isNotNull prepend="AND" property="filedate">
118                 FILEDATE = #filedate:VARCHAR#
119             </isNotNull>
120             <isNotNull prepend="AND" property="personalid">
121                 PERSONALID = #personalid:VARCHAR#
122             </isNotNull>
123             <isNotNull prepend="AND" property="centerid">
124                 CENTERID = #centerid:VARCHAR#
125             </isNotNull>
126         </dynamic>
127     </select>
128     <update id="updateExampleMi950" parameterClass="com.yondervision.mi.dto.MI950">
129         UPDATE MI950
130         <dynamic prepend="SET ">
131             <isNotEmpty property="validflag" prepend=","><![CDATA[ VALIDFLAG = #validflag# ]]></isNotEmpty>
132             <isNotEmpty property="filename" prepend=","><![CDATA[ FILENAME = #filename# ]]></isNotEmpty>
133             <isNotEmpty property="filepath" prepend=","><![CDATA[ FILEPATH = #filepath# ]]></isNotEmpty>
134             <isNotEmpty property="channel" prepend=","><![CDATA[ CHANNEL = #channel# ]]></isNotEmpty>
135             <isNotEmpty property="datemodified" prepend=","><![CDATA[ DATEMODIFIED = #datemodified# ]]></isNotEmpty>
136             <isNotEmpty property="datecreated" prepend=","><![CDATA[ DATECREATED = #datecreated# ]]></isNotEmpty>
137         </dynamic>
138         <dynamic prepend="WHERE">
139             REGID = #regid#
140         </dynamic>
141     </update>
142 </sqlMap>
View Code

2、在动态匹配查询条件时需要弄清，isNotEmpty与isNotNull这两个常用的条件判断。

　　isNotEmpty，当参数既不为Null也不为空是其为true；isNotNull用于判断参数是否不为Null。所以在开发时根据实际情况，选择对应判断条件。

3、使用ibatis查询需要多表联查同时也需要传入固定值时，可以将<dynamic prepend="WHERE">动态生成where条件标签<dynamic>去掉，如图：

 1 <select id="findByExample" resultMap="Mi950Result"
 2             parameterClass="com.yondervision.mi.form.MI950Form">
 3         <!--
 4           WARNING - This element is automatically generated by Abator for iBATIS, do not modify.
 5           This element was generated on Fri Sep 27 20:46:09 CST 2013.
 6         -->
 7         select t.REGID, t.PERSONALID, t.CENTERID, t.FILEDATE, t.FILENAME, t.FILEPATH, t.CHANNEL, t.VALIDFLAG,
 8         t.DATEMODIFIED, t.DATECREATED,t.FREEUSE1, t.FREEUSE2, t.FREEUSE3, t.FREEUSE4,d.USERNAME as USERNAME, d.CERTINUM as CERTINUM
 9         from MI950 t,MI029 d WHERE 1=1 and t.personalid = d.personalid AND t.VALIDFLAG = '1'
10         <isNotEmpty prepend="and" property="centerid">
11             t.CENTERID = #centerid:VARCHAR#
12         </isNotEmpty>
13         <isNotEmpty prepend="and" property="personalid">
14             t.PERSONALID = #personalid:VARCHAR#
15         </isNotEmpty>
16         <isNotEmpty prepend="and" property="startdate">
17             <![CDATA[
18                 to_date(t.FILEDATE,'yyyy-mm-dd') >= to_date('$startdate$','yyyy-mm-dd')
19             ]]>
20         </isNotEmpty>
21         <isNotEmpty prepend="and" property="enddate">
22             <![CDATA[
23                to_date(t.FILEDATE,'yyyy-mm-dd') <= to_date('$enddate$','yyyy-mm-dd')
24             ]]>
25         </isNotEmpty>
26         ORDER BY t.FILEDATE DESC
27     </select>

　　注：由于在bean中filedate字段类型为string类型，数据库中类型为VARCHAR2,在进行时间比较时，需要将两者都用to_date函数才能进行比较（数据库设计不严谨）。

posted @ 2019-01-21 16:11  chc24w  阅读(3664)  评论(0)    收藏  举报