map.put("username", "ACID");
// map.put("firstName", "XYX");
map.put("firstName", "");
// map.put("lastName", "bb");
map.put("lastName", "");
// map.put("status","OK");
map.put("status","");
LinkedList emaillist=new LinkedList();
emaillist.add("yahoo");
emaillist.add("acid");
map.put("emaillist",emaillist);
try {
// return this.getSqlMapClient().queryForList("getAccountNameListByMultiplyConditionsAnd",map);
// return this.getSqlMapClient().queryForList("getAccountNameListByMultiplyConditionsOR",map);
return this.getSqlMapClient().queryForList("getAccountNameListByMultiplyConditionsAndOR",map);
// return this.getSqlMapClient().queryForList("getAccountNameListByMultiplyConditionsNested",map);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
sqlmap:
<!-- =========================================================== -->
<!-- 條件語句嵌套-->
<!-- =========================================================== -->
<!-- 如果都不是空的：那么構造出來的sql是： select userid as value from account where firstname=? and( lastname=? or status=? ) -->
<!-- 如果lastName和status都是空的：那么構造出來的sql是： select userid as value from account where firstname=? and ( 1=1 ) -->
<!-- 如果status都是空的：那么構造出來的sql是： select userid as value from account where firstname=? and ( lastname=? ) -->
<!-- 如果lastName都是空的：那么構造出來的sql是： select userid as value from account where firstname=? and ( 1=1 or status=? ) -->
<!-- 如果firstName為空：那么構造出來的sql是： select userid as value from account where ( lastname=? or status=? ) -->
<!-- 如果都是為空：那么構造出來的sql是： select userid as value from account where 1=1 and ( 1=1 ) -->
<select id="getAccountNameListByMultiplyConditionsAndOR" resultClass="string" parameterClass="java.util.Map">
select userid as value from account
<dynamic prepend="where" >
<isNotEmpty prepend="and" property="firstName" >
<![CDATA[
firstname=#firstName#
]]>
</isNotEmpty>
<isEmpty property="firstName">
<![CDATA[
1=1
]]>
</isEmpty>
<isNotEmpty prepend="and" property="lastName">
<![CDATA[
( lastname=#lastName#
]]>
</isNotEmpty>
<isEmpty property="lastName">
<![CDATA[
and ( 1=1
]]>
</isEmpty>
<isNotEmpty prepend="or" property="status">
<![CDATA[
status=#status# )
]]>
</isNotEmpty>
<isEmpty property="status">
<![CDATA[
)
]]>
</isEmpty>
<isNotEmpty prepend="and" property="emaillist">
<iterate prepend="and" property="emaillist" conjunction="or" >
email=#emaillist[]#
</iterate>
</isNotEmpty>
</dynamic>
</select>