MyBatis动态SQL

MyBatis动态SQL

mapper接口方法:

/**
 * mybatis动态SQL
 */
public interface EmployeeMapperDynamicSQL {
    List<Employee> getEmpByConditionIf(Employee e);

    List<Employee> getEmpByConditionChoose(Employee e);

    int updateEmployee(Employee e);

    List<Employee> selectEmpByConditionForEach(List<Integer> ids);

    void addEmps(@Param("emps") List<Employee> emps);
}

动态SQL之if

 <!--动态SQL之if-->
    <!--动态SQL之if-->
    <!--<if test="">
    test:判断表达式(OGNL)-->
    <!--为什么在where后加一个1=1的条件?避免某些条件为空的时候，sql直接拼接成where and ***的情况
    也有另外的解决办法，也是mybatis推荐的
   2. 使用<where></where>标签将所有的查询条件包裹上,这样mybatis会将where标签中拼装的sql,多出来的
    and 或or去掉，但是他只会去掉第一个条件

    3.使用trim标签包裹
    -->
    <select id="getEmpByConditionIf" resultType="com.gf.selfdemo.mybatis.bean.Employee">
        select * from employee
        where 1=1
        <if test="id != null">
            and id=#{id}
        </if>
        <if test="lastName != null and lastName != ''">
            and last_name like concat(concat('%',#{lastName}),'%')
        </if>
        <if test="email != null and email.trim() != ''">
            and email =#{email}
        </if>
        <if test="gender==0 or gender==1">
            and gender=#{gender}
        </if>
    </select>


    <!--用where标签包裹-->
    <select id="getEmpByConditionIf2" resultType="com.gf.selfdemo.mybatis.bean.Employee">
        select * from employee
        <where>
            <if test="id != null">
                id=#{id}
            </if>
            <if test="lastName != null and lastName != ''">
                and last_name like concat(concat('%',#{lastName}),'%')
            </if>
            <if test="email != null and email.trim() != ''">
                and email =#{email}
            </if>
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
        </where>
    </select>

  <!--用trim标签包裹 trim属性:
    prefix:前缀:trim标签体中整个字符串拼接后的结果，即给字符串加一个前缀
    prefixOverrides：前缀覆盖，去掉整个字符串前面多余的字符
    suffix：后缀，给瓶装的字符串加上后缀
    suffixOverrides：去掉整个字符串后面多余的字符
    -->
    <select id="getEmpByConditionIf3" resultType="com.gf.selfdemo.mybatis.bean.Employee">
        select * from employee
        <trim prefix="where" suffixOverrides="and">
            <if test="id != null">
                id=#{id}
            </if>
            <if test="lastName != null and lastName != ''">
                and last_name like concat(concat('%',#{lastName}),'%')
            </if>
            <if test="email != null and email.trim() != ''">
                and email =#{email}
            </if>
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
        </trim>
    </select>

动态SQL之choose

   <!--choose(when ,otherwise):分支选择:switch-case-->
    <!--场景:如果带了id就用id查，如果带了lastName就用lastName查，只会进入其中一个分支-->
    <select id="getEmpByConditionChoose" resultType="com.gf.selfdemo.mybatis.bean.Employee">
        select * from employee
        <where>
            /*如果带了id就用id查，如果带了lastName就用lastName查*/
            <choose>
                <when test="id != null">
                    id=#{id}
                </when>
                <when test="lastName != null and lastName != ''">
                    last_name=#{lastName}
                </when>

                <when test="email != null and email != ''">
                    email 3#{email}
                </when>
                <otherwise>
                    1=1
                </otherwise>

            </choose>
        </where>
    </select>

动态SQL之 set针对更新语句

 <!--更新时，除了最后一个字段外的所有字段都会有',',那么如果最后一个字段为空的话，会出现字符set字符串的最后
    会出现','的情况，<set>标签可以将多余的','去除掉-->

    <update id="updateEmployee">
        update employee
        <set>
            <if test="lastName != null">
                last_name=#{lastName},
            </if>
            <if test="email != null">
                email=#{email},
            </if>

            <if test="gender != null">
                gender=#{gender}
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>


    <!--set的功能也可以通过trim实现，trim指定字符串前缀为set,suffixOverrides指定如果
    字符串最后出现多余的','则去掉-->
    <update id="updateEmployee2">
        update employee
        <trim prefix="set" suffixOverrides=",">
            <if test="lastName != null">
                last_name=#{lastName},
            </if>
            <if test="email != null">
                email=#{email},
            </if>

            <if test="gender != null">
                gender=#{gender}
            </if>
        </trim>
        <where>
            id=#{id}
        </where>
    </update>

动态SQL之forEach

<!--forEach遍历list集合用于根据条件查询-->
<select id="selectEmpByConditionForEach" resultType="com.gf.selfdemo.mybatis.bean.Employee">

    select * from employee where id in

    /*
    forEach标签属性
    collection:指定要遍历的集合
    list类型的参数会特殊处理封装在map中,map的key就叫list
    item:将当前遍历出的元素赋值给指定的变量 #{变量名}就能取出变量的值也就是当前遍历的元素
    separator:指定每个元素之间的分隔符
    open:遍历出所有结果拼接一个开始字符
    close:遍历出所有结果拼接结束字符
    index:索引,遍历List的时候是索引，item就是当前值。如果遍历map的时候index表示的就是map的key,item就是map的值
    */
    <foreach collection="ids" index="index" item="item_id" separator="," open="(" close=")">
        #{item_id}
    </foreach>
</select>

<!--forEach遍历集合用于批量保存-->

<!--方式一:拼接为:insert into employee(last_name,email,gender,dept_id)values
(),(),()的形式-->
<insert id="addEmps">
    insert into employee (last_name,email,gender,dept_id)
    values
    <foreach collection="emps" item="emp"  separator=",">
        (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
    </foreach>
</insert>

<!--批量保存之方式二:拼接为
insert into employee(last_name,email,gender,dept_id)values(*,*,*);
 insert into employee(last_name,email,gender,dept_id)values(*,*,*);
 insert into employee(last_name,email,gender,dept_id)values(*,*,*);
 但是这种方式需要在数据库连接url指定支持多查询语句
 -->

<insert id="addEmps">
    <foreach collection="emps" item="item" separator=";">
        insert into employee (last_name,email,gender,dept_id)
        values
        (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
    </foreach>
</insert>

<!--oracle数据库批量保存方法:
oracle不支持values(),(),()的形式
oracle批量保存的方式为:
1.多个Insert放在begin-end里面
    begin
        insert into employee (last_name,email,gender,dept_id)
        values (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id});
        insert into employee (last_name,email,gender,dept_id)
        values (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id});
        insert into employee (last_name,email,gender,dept_id)
        values (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id});
    end;
 2.oracle支持中间表的形式
    insert into employee(employee_id,last_name,email)
    select employee_seq.nextval,lastName,email
    from (
        select 'test_1_last_name' lastName,'test_e_1_email' email from dual
        union
        select 'test_2_last_name' lastName,'test_e_2_mail' email from dual
    )
-->

<!--oracle批量保存-->
<insert id="addEmpss">
    begin
      <foreach collection="emps" item="item" >
          insert into employee(employee_id,last_name,email)
          values(employee_seq.nextval,#{emp.lastName},#{emp.email})
      </foreach>
    end;
</insert>

mybatis两个内置参数

不只是方法传递过来的参数可以被用来判断，取值，mybatis默认还有两个内置参数
_parameter:代表整个参数
    如果查询中只传过来一个参数,_parameter就代表这个参数
    如果查询传过来多个参数，参数会封装为一个map,_parameter就代表这个map

_databaseId:如果在mybatis-config.xml中配置了databaseIdProvider标签
    _databaseId就代表当前数据库的别名
-->

<select id="getEmpsTestInnerParameter" resultType="***">
    <if test="_databaseId=='mysql'">   /*去mysql查*/
        select * from employee
        <if test="_parameter != null">
            where last_name=#{lastName}
        </if>
    </if>
    <if test="_databaseId=='oracle'">  /*去oracle查*/
        select * from orac_employees
    </if>
</select>

bind绑定变量,可以对传入的变量做改变

<select id="selectByLastName" resultType="com.gf.selfdemo.mybatis.bean.Employee">
    <bind name="_lastName" value="'%'+lastName+'%'"/>
    select * from employee where last_name like #{_lastName}
</select>

公共代码片段

<!--与insert,select,delete,update等标签同级的标签sql,可以抽取出在mapper.xml中多处使用的公共语句
在调用的地方用<include>标签引入即可

抽取可宠用的sql片段，方便后面引用
1.sql:抽取，经常将要查询的列名，或者插入用的列名抽取出来方便应用
2.include 引用已经抽取出来的sql片段
3.include还可以自定义一些property,sql标签内部就能使用自定义的属性 ${property},不能用#{property}取
-->

<sql id="employee_column">
    last_name,email,gender
</sql>
<select id="testCommonSql" resultType="com.gf.selfdemo.mybatis.bean.Employee">
    select
      <include refid="employee_column"/>
    from employee where id=#{id}
</select>