mybaits中if 多个test 和 if else 分支支持

最新推荐文章于 2024-07-03 19:29:43 发布

原创最新推荐文章于 2024-07-03 19:29:43 发布 · 7.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#java #数据库

java 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一个使用MyBatis实现的动态SQL查询案例，通过条件判断灵活地构造SQL语句，支持按不同字段过滤及排序，适用于复杂的查询场景。

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 &gt;= #{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 &gt;= #{conds.startPos,jdbcType=BIGINT}
      </if>
      <if test="conds.endPos!=null">
        and end_pos &lt;= #{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>