08 mybatis动态sql

mybatis的灵活性之一就体现在它的动态sql，本文将展示常用的一些动态sql。

1、环境约束

• win10 64位操作系统
• idea2018.1.5
• jdk-8u162-windows-x64
• mybatis3.2.8
• mysql 6.5

2、前提约束

• 完成idea中创建mybatis项目 https://www.jianshu.com/p/86db0901156f

3、操作步骤

• 创建表和插入数据

create table student(id int,name varchar(20),age int, address varchar(20));

• 创建对应的实体类
• 动态sql配置
  (1)if

    <select id= "findIfDynamic"  resultType= "Student" parameterType= "Student" >
        select *  from student t1 where 1=1
        <if test= "name!=null" >
            and t1.name=#{name}
        </if>
    </select>

(2)choose

     <select id="findChooseDynamic" resultType="Student" parameterType="Student">
        select * from student t1 where 1=1
        <choose>
            <when test="name!=null">
                and t1.name =#{name}
            </when>
            <when test="age!=0">
                and age =#{age}
            </when>
            <otherwise>
                and address is null
            </otherwise>
        </choose>
    </select>

(3)where

      <select id="findIfDynamic2" resultType="student" parameterType="student">
        select * from student t1
        <where>
            <if test="name!=null">
                AND t1.name=#{name}
            </if>
            <if test="age!=0">
                AND t1.age=#{age}
            </if>
        </where>
    </select>

(4)and or

     <select id="findIfDynamic3" resultType="Student" parameterType="Student">
        select * from student t1
        <trim prefix="where" prefixOverrides="and |or">
            <if test="name != null">
                and name = #{name}
            </if>
            <if test="age != null">
                or age = #{age}
            </if>
        </trim>
    </select>

(5)set

    <update id="updateDynamic" parameterType="Student">
        update student
        <set>
            <if test="name != null">
                name = #{name},
            </if>
            <if test="age != null">
                age = #{age},
            </if>
        </set>
        where id = #{id}
    </update>

(6)foreach list

     <!-- foreach list -->
    <select id= "findList"  resultType= "Student" >
        select *  from student where id in
        <foreach collection= "list"  index= "index" item= "item"  open= "(" separator= ","  close= ")" >
            #{item}
        </foreach>
    </select>

(7)foreach array

    <!-- foreach array-->
    <select id= "findArray"  resultType= "Student" >
        select  * from student where id in
        <foreach collection= "array"  index= "index" item= "item"  open= "(" separator= ","  close= ")" >
            #{item}
        </foreach>
    </select>

(8) foreach map

    <!-- foreach map-->
    <select id= "findMap" parameterType="map"  resultType= "Student" >
      select  * from student where 1=1
      <if test="id!=''"> and id=#{id}</if>
      <if test="name!=''"> and name=#{name}</if>
    </select>

(9) sql

    <sql id="base_field">
        id,name,age,gender,address
    </sql>
    <select id="findIfDynamic" parameterType="Student" resultType="Student">
        select 
            <include refid="base_field"/> 
        from student where 1=1
        <if test="name!=null">
            and name=#{name}
        </if>
        <if test="gender!=null">
            and gender=#{gender}
        </if>
    </select>

以上就是常用的mybatis动态sql。