在工作中使用mybatis对单表查询操作时,因为查询条件的不同而在mapper文件中写多条sql语句,导致mapper文件臃肿。为了解决这一问题,希望通过传递不同参数mybatis自动拼接sql语句。
DAO 文件中方法:
selectByCondtions(@Param("condition")Map param )
param中保存条件参数对应不同的条件格式如下:
where filed=value:
{field:value}
where file in (value1,value2,....)
{"listKey":"id","listValues":Arrays.asList("1","2")}
limit 0,10
{"limit":"0,10"}
order by id desc
{"order by":"id desc"}
mapper 文件:
<select id="selectByCondtions" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
FROM rt_stu_submit_state
<where>
<foreach collection="condition.keys" item="k1" separator="and">
<if test="condition[k1] != null and condition[k1] != '' ">
<choose>
<when test="'listKey' == k1 ">
${condition.listKey} in
<foreach collection="condition.listValues" item="v" separator="," open="(" close=")">
#{v}
</foreach>
</when>
<when test="'order'!=k1 and 'listValues' != k1 and 'limit' != k1 ">
${k1} = #{condition[${k1}]}
</when>
</choose>
</if>
<if test="condition[k1] == null or condition[k1] == '' ">
(${k1} is null or ${k1} = '')
</if>
</foreach>
</where>
<if test="condition.order !=null and condition.order !=''">
order by ${condition.order}
</if>
<if test="condition.limit !=null and condition.limit !=''">
limit ${condition.limit}
</if>
</select>
注意:表中的字段名不要包含k1否则拼接不上条件参数 。
mybatis 3.1.1 、3.4.6 可以正常执行,其它版本未测试!!!
缺点:where中的多个条件之间只支持and;
where中不支持多个范围条条件;
希望看到此文章的同学有好的思路可以给我留言,一起交流学习!!!