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 \n" +
            "(lw.`lw_first_id` = #{lwId} and lw.deleted=0) ) a  " +
            "LEFT JOIN visitor v ON a.lwer_id=v.`visitor_id` \n" +
            "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 \n" +
            "(lw.`lw_first_id` = #{lwId} and lw.deleted=0) ) a
  得到一个结果表，赋予别名为a，
  然后将表a和visitor和blogger分别做链接，并给别名v,b，
  查询时，便是以a、v、b为基础表进行查询
*/