Mybatis-动态SQL笔记，IF对查询结果进行判定

基于XML文档的映射

基于Mapper层使用注解的sql

@Select({
          
   "<scripe>",
			"……",
			"</script>"
})

if判断

<select 
        select * from user where 1 =1
        <if test="username != null">
            and username = #{
          
   username}
        </if>
        <if test="sex != null">
            and sex = #{
          
   sex}
        </if>
    </select>

< where >如果if语句过多的话，也可以利用标签来简洁代码

<select 
        select * from user 
        <where>
   			<if test="username != null">
           		and username = #{
          
   username}
            </if>
            <if test="sex != null">
                and sex = #{
          
   sex}
            </if>
    	</where>  
</select>

< foreach> 多个id同时查询

如果需要这么查询：SELECT * FROM USER WHERE id IN(41,42,45)，多个id同时查询那么，可以利用来查询

<select 
        select * from user
        <where>
            <if test="ids != null and ids.size() > 0">
                <foreach collection="ids" open="and id in (" close=")" item="id"   
                    separator=",">
                    #{
          
   id}
                </foreach>
            </if>
        </where>
    </select>

其中中的属性：

collection：循环目标 open ：sql语句拼接的开头 close：sql语句拼接的结尾 item：循环赋值对象 separator：分隔符

和

@Select({
          
    "<script>",
            "SELECT id,name,sex,classes FROM students",
                "<where>",
                    "<if test="status!=null">",
                        "<choose>",
                            "<when test="status==1">AND conStatus=1</when>",
                            "<when test="status==2">AND conStatus=0</when>",
                        "</choose>",
                    "</if>",
                    "<if test="sex!=null">",
                        "<choose>",
                            "<when test="sex==1">AND sex LIKE 男</when>",
                            "<when test="sex==0">AND sex LIKE 女</when>",
                        "</choose>",
                    "</if>",
                    "<if test="type!=null and search!=null">",
                        "<choose>",
                            "<when test="type==0">AND academy</when>",
                            "<when test="type==1">AND major</when>",
                            "<otherwise>AND classes</otherwise>",
                        "</choose>",
                        "LIKE #{search}",
                    "</if>",
                "</where>",
            "</script>"
    })
    List<Student> sqlStudent(Integer type, @Param("search")String key, Integer status, Integer sex);

实例

//查询指定一级留言下的所有二级留言
    @Select({
          
   "<script>" +
            "SELECT a.*,IF(a.lwer_type=0,v.`head_img`,b.`head_img`) head_img,IF(a.lwer_type=0,v.`nickname`,b.`nickname`) reply_nickname " +
            " FROM (SELECT  lw.* FROM leave_word lw WHERE 
" +
            "(lw.`lw_first_id` = #{lwId} and lw.deleted=0) ) a  " +
            "LEFT JOIN visitor v ON a.lwer_id=v.`visitor_id` 
" +
            "LEFT JOIN blogger b ON a.lwer_id=b.`blogger_id` " +
            "ORDER BY a.creat_time desc" +
            "</script>"})
    List<QueryLeaveWordOneBO> getQueryTwoLeaveWord(QueryLeaveWordOneBO leaveOne);
/*
*先执行(SELECT  lw.* FROM leave_word lw WHERE 
" +
            "(lw.`lw_first_id` = #{lwId} and lw.deleted=0) ) a
  得到一个结果表，赋予别名为a，
  然后将表a和visitor和blogger分别做链接，并给别名v,b，
  查询时，便是以a、v、b为基础表进行查询
*/