mybatis中的动态sql语句

1. <if是判断条件的>,
   语法<if test=“判断java对象属性值”> 部分sql语句 </if>

  <select id="selectByIf" resultType="com.bjpowernode.domain.Student">
        select id,name, email,age from student
        where
        <if test="name != null and name !='' ">
            name=#{name}
        </if>
        <if test="age > 0">
            or age > #{age}
        </if>
    </select>

2)<where> 用来包含多个 <if>,当多个if有一个成立时,<where>会自动增加一个where关键字,并去掉if中多余的add,or等.

 -->
    <select id="selectByWhere" resultType="com.bjpowernode.domain.Student">
        select id,name, email,age from student
        <where>
            <if test="name != null and name != ''">
                name=#{name}
            </if>
            <if test="age > 0 ">
                or age>#{age}
            </if>
        </where>
    </select>

3)<foreach> 循环中Java中的数组,list集合的,主要用在sql的in语句中.

学生id是10001,10002,10003的三个学生

select * from student where id in(10001,10002,10003)

 public List<Student> selectByForeach(List<Integer> idList);

 ist<Integer> list = new ArrayList<>();
 list.add(10001);
 list.add(10002);
 list.add(10003);
 dao.selectByForeach(list)        

<foreach collection="" item="" open="" close="">
</foreach>

collection: 表示接口中的参数类型,如果是数组使用Array,List集合使用list
item:自定义的,表示数组和集合成员的变量
open:循环开始的字符
close:循环结束的字符
separator:集合成员之间的分隔符

  <select id="selectByForeach" resultType="com.bjpowernode.domain.Student">
        select * from student where  id in
        <foreach collection="list" item="myid" open="(" close=")" separator=",">
            #{myid}
        </foreach>
    </select>

4)sql代码片段,就是复用一些语句

1.先定义<sql id=“自定义名称唯一”> sql语句,表名,字段等
2.在使用,<include refid = “id的值”>

   <!--定义sql代码片段-->
    <sql id="selectOne">
        select * from student
    </sql>

    <select id="selectByForeach" resultType="com.bjpowernode.domain.Student">
        <include refid="selectOne"></include> where id in
        <foreach collection="list" item="myid" open="(" close=")" separator=",">
            #{myid}
        </foreach>
    </select>