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>
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函数才能进行比较（数据库设计不严谨）。
