mybatis 用<where>标签解决动态sql的尴尬问题19-8-13傍晚

之前写sql有遇到过这样的：

SELECT A.ID, A.ATTENTION_ID, A.ATTENTION_EMPEE, A.DEAL_TIME, A.ACTUAL_RESULT, A.EXPERT_ADVICE, A.DEAL_STATE
FROM DSF_ACCT_ATTENTION_HANDLE_LIST A
WHERE 
<if test="ATTENTION_ID != null and ATTENTION_ID != ''">
	A.ATTENTION_ID = #{ATTENTION_ID}
</if>
<if test="EMPEE_ACCT != null and EMPEE_ACCT != ''">
  AND A.ATTENTION_EMPEE = #{EMPEE_ACCT}
</if>
<if test="DEAL_STATE != null and DEAL_STATE !='' or DEAL_STATE == '0'">
  AND A.DEAL_STATE = #{DEAL_STATE}
</if>  

乍一看好像没什么问题，可以仔细一思考当ATTENTION_ID为null的时候，下面两个if中的EMPEE_ACCT ，EMPEE_ACCT 有不为空的时候 整个sql就会被解析变成：
若EMPEE_ACCT不为null，DEAL_STATE 为null，

SELECT A.ID, A.ATTENTION_ID, A.ATTENTION_EMPEE, A.DEAL_TIME, A.ACTUAL_RESULT, A.EXPERT_ADVICE, A.DEAL_STATE
FROM DSF_ACCT_ATTENTION_HANDLE_LIST A
WHERE 
  AND A.ATTENTION_EMPEE = #{EMPEE_ACCT}

很明显这时就会报SQL异常
再就是若全为null，就会被解析成这样

SELECT A.ID, A.ATTENTION_ID, A.ATTENTION_EMPEE, A.DEAL_TIME, A.ACTUAL_RESULT, A.EXPERT_ADVICE, A.DEAL_STATE
FROM DSF_ACCT_ATTENTION_HANDLE_LIST A
WHERE 

很明显也会报SQL异常。

这里就有两种解决方案，同样不用标签，

1、在<if>前面<where>后面加个1=1就可以了

SELECT A.ID, A.ATTENTION_ID, A.ATTENTION_EMPEE, A.DEAL_TIME, A.ACTUAL_RESULT, A.EXPERT_ADVICE, A.DEAL_STATE
FROM DSF_ACCT_ATTENTION_HANDLE_LIST A
WHERE 1=1
<if test="ATTENTION_ID != null and ATTENTION_ID != ''">
	AND A.ATTENTION_ID = #{ATTENTION_ID}
</if>
<if test="EMPEE_ACCT != null and EMPEE_ACCT != ''">
  AND A.ATTENTION_EMPEE = #{EMPEE_ACCT}
</if>
<if test="DEAL_STATE != null and DEAL_STATE !='' or DEAL_STATE == '0'">
  AND A.DEAL_STATE = #{DEAL_STATE}
</if>  

这样就完美的解决问题了
2、使用<where> </where>标签

SELECT A.ID, A.ATTENTION_ID, A.ATTENTION_EMPEE, A.DEAL_TIME, A.ACTUAL_RESULT, A.EXPERT_ADVICE, A.DEAL_STATE
FROM DSF_ACCT_ATTENTION_HANDLE_LIST A
<where>
<if test="ATTENTION_ID != null and ATTENTION_ID != ''">
	AND A.ATTENTION_ID = #{ATTENTION_ID}
</if>
<if test="EMPEE_ACCT != null and EMPEE_ACCT != ''">
  AND A.ATTENTION_EMPEE = #{EMPEE_ACCT}
</if>
<if test="DEAL_STATE != null and DEAL_STATE !='' or DEAL_STATE == '0'">
  AND A.DEAL_STATE = #{DEAL_STATE}
</if>  
</where>

这样就能解决问题了，<where>会根据里面if所判断的字段是否全为null 或是 有不为空的项，来判断是否有where 语句，若是全为null，则SQL语句后面就没有 where：

SELECT A.ID, A.ATTENTION_ID, A.ATTENTION_EMPEE, A.DEAL_TIME, A.ACTUAL_RESULT, A.EXPERT_ADVICE, A.DEAL_STATE
FROM DSF_ACCT_ATTENTION_HANDLE_LIST A

并且还能自动的处理 if标签里，语句的and，若是是最接近where 的if 会自动地去除掉此if标签中的and

注：在其他地方都会有看到 运用**<where></where>90%都能解决问题**，但是是不一定哦！(●’◡’●)