<selectid="selectList"resultType="com.xkcoding.demo.model.User"> SELECT temp.* FROM ( SELECT t.* FROM t_user t )temp <where> <includerefid="advance_query_sql"/> </where> </select>
<sqlid="advance_query_sql"> <foreachitem="item"collection="advanceQuery"open=" AND "separator=" AND "close=" "> <choose> <whentest="item.op != null and item.op == 'eq'"> temp.${item.key} = #{item.value[0]} </when> <whentest="item.op != null and item.op == 'gte'"> temp.${item.key} >= #{item.value[0]} </when> <whentest="item.op != null and item.op == 'lte'"> temp.${item.key} <= #{item.value[0]} </when> <whentest="item.op != null and item.op == 'between'"> temp.${item.key} BETWEEN #{item.value[0]} AND #{item.value[1]} </when> <whentest="item.op != null and item.op == 'in'"> temp.${item.key} IN <foreachitem="value"collection="item.value"open="("separator=","close=")"> #{value} </foreach> </when> <whentest="item.op != null and item.op == 'no'"> (temp.${item.key} IS NULL OR trim(temp.${item.key}) = '') </when> <whentest="item.op != null and item.op == 'yes'"> (temp.${item.key} IS NOT NULL AND trim(temp.${item.key}) != '') </when> <whentest="item.op != null and item.op == 'notin'"> temp.${item.key} NOT IN <foreachitem="value"collection="item.value"open="("separator=","close=")"> #{value} </foreach> </when> <whentest="item.op != null and item.op == 'like'"> <iftest="item.value.size == 1"> temp.${item.key} LIKE CONCAT("%", #{item.value[0]}, "%") </if> <iftest="item.value.size > 1"> <foreachitem="value"collection="item.value"open="("separator=" OR "close=")"> temp.${item.key} LIKE CONCAT("%", #{value}, "%") </foreach> </if> </when> <otherwise> 1 = 1 </otherwise> </choose> </foreach> </sql> </mapper>
这里改造之前的查询语句,只需要在外层再包一层,并且别名改成temp即可。
如下:
1 2 3
<select> SELECT * FROM table where xxx; </select>
改为:
1 2 3 4 5 6 7 8 9
<select> SELECT temp.* FROM ( SELECT * FROM table where xxx; )temp <where> <includerefid="advance_query_sql"/> </where> </select>