我就废话不多说了,大家还是直接看代码吧~ !-- 查询物品的id --select id="checkItemsId" parameterType="pd" resultType="java.lang.Integer"SELECTi.itemsidFROM pq_goods_items iwhere !--方式一使用choose的方式查询--
我就废话不多说了,大家还是直接看代码吧~
<!-- 查询物品的id --> <select id="checkItemsId" parameterType="pd" resultType="java.lang.Integer"> SELECT i.itemsid FROM pq_goods_items i <where> <!--方式一使用choose的方式查询--> <!-- <choose> <when test="parentId !=0 ">parentTypeId=#{parentId}</when> <when test="parentId==0">parentTypeId is null</when> </choose> --> <!--方式二使用if的方式查询--> <if test="color!=null"> i.personone=#{personone} AND i.persontwo=#{persontwo} AND i.color=#{color} </if> <if test="color==null"> i.personone=#{personone} AND i.persontwo=#{persontwo} AND i.color is null </if> </where> </select>
需要注意的是 使用了where标签以后,sql中不在使用where字段来限制条件
如果判断条件有多个 中间用 and 表示并列
<if test="color!=null and personone!=null">
补充:mybaits中if 多个test 和 if else 分支支持
mybaits中if 多个test
<select id="selectByDynamicallyWithPage" parameterType="map" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from gene_polymorphism <where> diag_id = #{conds.diagId,jdbcType=INTEGER} <if test="conds.chromesome!=null and conds.chromesome!=''"> and chromesome = #{conds.chromesome,jdbcType=VARCHAR} </if> <if test="conds.startPos!=null"> and start_pos >= #{conds.startPos,jdbcType=BIGINT} </if> </where> </select>
if else分支:
<select id="selectByDynamicallyWithPage" parameterType="map" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from gene_polymorphism <where> diag_id = #{conds.diagId,jdbcType=INTEGER} <if test="conds.chromesome!=null"> and chromesome = #{conds.chromesome,jdbcType=VARCHAR} </if> <if test="conds.startPos!=null"> and start_pos >= #{conds.startPos,jdbcType=BIGINT} </if> <if test="conds.endPos!=null"> and end_pos <= #{conds.endPos,jdbcType=BIGINT} </if> <if test="conds.geneTypes!=null"> <!--and gene_type in--> <!--<foreach collection="conds.geneTypes" open="(" close=")" item="item" separator="," >--> <!--#{item,jdbcType=VARCHAR}--> <!--</foreach>--> and ( <foreach collection="conds.geneTypes" item="item" separator="or"> gene_type like CONCAT('%',CONCAT(#{item,jdbcType=VARCHAR}, '%')) </foreach> ) </if> <if test="conds.geneChange!=null"> and gene_change like CONCAT('%',CONCAT(#{conds.geneChange,jdbcType=VARCHAR}, '%')) </if> </where> order by <trim suffixOverrides=","> <choose> <when test="conds.chromesomeSort!=null and conds.chromesomeSort=='asc'"> chromesome asc , </when> <when test="conds.chromesomeSort!=null and conds.chromesomeSort=='desc'"> chromesome desc , </when> </choose> <choose> <when test="conds.startPosSort!=null and conds.startPosSort=='asc'"> start_pos asc , </when> <when test="conds.startPosSort!=null and conds.startPosSort=='desc'"> start_pos desc , </when> <otherwise> id desc </otherwise> </choose> </trim> limit #{startRow,jdbcType=INTEGER} ,#{pageSize,jdbcType=INTEGER} <!-- order by id desc limit #{startRow,jdbcType=INTEGER} ,#{pageSize,jdbcType=INTEGER} --> </select>
以上为个人经验,希望能给大家一个参考,也希望大家多多支持易盾网络。如有错误或未考虑完全的地方,望不吝赐教。