ibatIS的<dynamic prepend="WHERE">原理，是判断前一个AND字母是否存在

今天付出了惨痛的代价，调sqlMap文件调了一下午，杯具啊！！！！！！！！！！！

 

1.首先看我错误的xml文件代码

	<select id="count"  parameterClass="com.hanpeng.base.phone.model.TCommission" 
											resultClass="java.lang.Integer">
		SELECT count(*) 
	  FROM  T_COMMISSION t1,t_product_type t2,T_BUSSINESS_SAFE t3  
	<dynamic prepend="WHERE">
		t3.type_num=t1.type_num  
	  	and t1.product_num=t2.product_num and t2.state='10'  
			<isNotEmpty prepend=" AND " property="otherFastCommission">
			t1.OTHER_FAST_COMMISSION =  #otherFastCommission# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="otherNormCommission">
			t1.OTHER_NORM_COMMISSION =  #otherNormCommission# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="money">
			t1.MONEY =  #money# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="fastPrice">
			t1.FAST_PRICE =  #fastPrice# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="bussinessId">
			t3.bussiness_id =  #bussinessId# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="provinceCode">
			t2.province_code =  #provinceCode# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="areaCode">
			t2.area_code =  #areaCode# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="operators">
				t2.operators_code in
				<iterate property="operators" open="(" close=")" conjunction=",">  
					#operators[]#
				</iterate>
			</isNotEmpty>
		</dynamic>
	</select>

 ###注意：

t3.type_num=t1.type_num 
and t1.product_num=t2.product_num and t2.state='10'   我写到了<dynamic prepend="WHERE">的里面，因为要动态生成where子句，所以会根据前一个条件是否有AND，来判断下个条件是否给加AND符！ 但注意我的 'and' 是小写的,如果用大写可能就好使了，t1.MONEY =  ?前面没给和AND，ibatIS自动拼接后的sql如下：

SELECT count(*) FROM  T_COMMISSION t1,t_product_type t2,T_BUSSINESS_SAFE t3 
WHERE t3.type_num=t1.type_num and t1.product_num=t2.product_num and t2.state='10' 
t1.MONEY =  ? 
AND t3.bussiness_id =  ?            
AND t2.area_code =  ?        
AND t2.operators_code in(?,?)

 所以，ibatIS的isNotEmpty标签是查找前一个sql有没有AND，没有，默认为where后第一个条件，不加AND。

有，默认不是where后的第一个条件，加AND。ibatIS写的时候应该是用"AND".equals(str)

 

二、正确应该的写法应该这样：

###注：只能这样修改，t3.type_num=t1.type_num 
and t1.product_num=t2.product_num and t2.state='10'   【 and改成AND不好使，已测试！！！ 】

 

2.1  去掉<dynamic prepend="WHERE">标签(已验证，好使，唯一方法)：

	<select id="count"  parameterClass="com.hanpeng.base.phone.model.TCommission" 
											resultClass="java.lang.Integer">
		SELECT count(*) 
	  FROM  T_COMMISSION t1,t_product_type t2,T_BUSSINESS_SAFE t3 
	  where 
	  	t3.type_num=t1.type_num  
	  	and t1.product_num=t2.product_num and t2.state='10'  
			<isNotEmpty prepend=" AND " property="productNum">
			t1.PRODUCT_NUM =  #productNum# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="typeNum">
			t1.TYPE_NUM =  #typeNum# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="fastCommission">
			t1.FAST_COMMISSION =  #fastCommission# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="normCommission">
			t1.NORM_COMMISSION =  #normCommission# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="createDate">
			t1.CREATE_DATE = #createDate# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="employeeId">
			t1.EMPLOYEE_ID =  #employeeId# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="alterDate">
			t1.ALTER_DATE = #alterDate# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="otherFastCommission">
			t1.OTHER_FAST_COMMISSION =  #otherFastCommission# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="otherNormCommission">
			t1.OTHER_NORM_COMMISSION =  #otherNormCommission# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="money">
			t1.MONEY =  #money# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="fastPrice">
			t1.FAST_PRICE =  #fastPrice# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="normPrice">
			t1.NORM_PRICE =  #normPrice# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="allFastCommission">
			t1.ALL_FAST_COMMISSION =  #allFastCommission# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="allNormCommission">
			t1.ALL_NORM_COMMISSION =  #allNormCommission# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="otherFastPrice">
			t1.OTHER_FAST_PRICE =  #otherFastPrice# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="otherNormPrice">
			t1.OTHER_NORM_PRICE =  #otherNormPrice# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="allFastPrice">
			t1.ALL_FAST_PRICE =  #allFastPrice# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="allNormPrice">
			t1.ALL_NORM_PRICE =  #allNormPrice# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="bussinessId">
			t3.bussiness_id =  #bussinessId# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="provinceCode">
			t2.province_code =  #provinceCode# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="areaCode">
			t2.area_code =  #areaCode# 		</isNotEmpty>
			<isNotEmpty prepend=" AND " property="operators">
				t2.operators_code in
				<iterate property="operators" open="(" close=")" conjunction=",">  
					#operators[]#
				</iterate>
			</isNotEmpty>
	</select>