05 动态sql

动态sql

动态 SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作。
• 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处
理器相似。
• MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作。
– if
– choose (when, otherwise)
– trim (where, set)
– foreach

if判断

<!--    查询员工：携带了那个字段查询条件就带上这个字段的值-->
<!--    public List<Employee> getEmpsByConditionIf(Employee employee);-->
    <select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
         select * from tbl_employee
         where 1=1
        <!-- test:判断表达式（OGNL）
        从参数中取值进行判断
        遇见特殊符号应该去写转义字符

        查询的时候如果某些条件没带可能sql拼装会有问题
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(null,"%e%",null,null);

//            List<Employee> emps = mapper.getEmpsByConditionIf(employee);

            List<Employee> emps = mapper.getEmpsByConditionTrim(employee);

        //1、给where 后面加上 1=1，以后的条件都 and xxx
        //2、 mybatis 使用where标签将所有的查询条件包括在内
        -->
        <if test="id!=null">
            and id=#{id}
        </if>
        <if test="lastName!=null and lastName!=''">
            and  last_name like #{lastName}
        </if>
        <if test="email!=null and email.trim()!=''">
            and email = #{email}
        </if>
           <!--ognl会进行字符串域数字的转换判断 -->
        <if test="gender==0 or gender==1">
            and gender = #{gender}
        </if>
    </select>

where标签

2、 mybatis 使用where标签将所有的查询条件包括在内

<!--    查询员工：携带了那个字段查询条件就带上这个字段的值-->
<!--    public List<Employee> getEmpsByConditionIf(Employee employee);-->
    <select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
         select * from tbl_employee
        <!--where-->
        <where>
            <!-- test:判断表达式（OGNL）
        从参数中取值进行判断
        遇见特殊符号应该去写转义字符

        查询的时候如果某些条件没带可能sql拼装会有问题
        //1、给where 后面加上 1=1，以后的条件都 and xxx
        //2、 mybatis 使用where标签将所有的查询条件包括在内,mybatis就会将where标签中拼装的sql，多出来的and或者or去掉
		** where只会去掉第一个多出来的 and  或者 or
        -->
            <if test="id!=null">
                id=#{id}
            </if>
            <if test="lastName!=null and lastName!=''">
                and  last_name like #{lastName}
            </if>
            <if test="email!=null and email.trim()!=''">
                and email = #{email}
            </if>
            <!--ognl会进行字符串域数字的转换判断 -->
            <if test="gender==0 or gender==1">
                and gender = #{gender}
            </if>
        </where>
    </select>

trim 标签

trim 字符串截取(where(封装查询条件), set(封装修改条件))

<!--    public List<Employee> getEmpsByConditionTrim(Employee employee);-->
    <select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee">
        select * from tbl_employee
        <!-- 后面多出的and或者or where标签不能解决
            prefix="":前缀， trim标签中是整个字符串拼串后的结果
                        prefix 给拼串后的整个字符串加一个前缀
            prefixOverrides="" ：前缀覆盖，去掉整个字符串前面多余的字符
            suffix="":后缀
                    suffix 给拼串后的整个字符串加一个后缀
            suffixOverrides=""
                    后缀覆盖：去掉整个字符串后面多余的字符
        -->
        <trim prefix="where" suffixOverrides="and">
            <if test="id!=null">
                id=#{id} and
            </if>
            <if test="lastName!=null and lastName!=''">
                last_name like #{lastName} and
            </if>
            <if test="email!=null and email.trim()!=''">
                email = #{email} and
            </if>
            <!--ognl会进行字符串域数字的转换判断 -->
            <if test="gender==0 or gender==1">
                gender = #{gender}
            </if>
        </trim>

    </select>

set 标签

<!--    public void updateEmp(Employee employee);-->
    <update id="updateEmp">
        update tbl_employee
        <set>
            <if test="id!=null">
                id = #{id},
            </if>
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
        </set>
        where id = #{id}
    </update>

使用trim标签来完成

<!--    public void updateEmp(Employee employee);-->
    <update id="updateEmp">
        update tbl_employee
        <trim prefix="set" suffixOverrides=",">
            <if test="id!=null">
                id = #{id},
            </if>
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
        </trim>
        where id = #{id}
    </update>

chose 标签

choose (when, otherwise)

<!--    public List<Employee> getEmpsByConditionChoose(Employee employee);-->
    <select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
        select * from tbl_employee
        <where>
            <!-- 如果带了id就用id查，如果带了lastName就用lastName查-->
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="lastName!=null">
                    last_name like #{lastName}
                </when>
                <when test="email!=null">
                    email = #{email}
                </when>
                <otherwise>
                    gender = 0
                </otherwise>
            </choose>

        </where>
    </select>

foreach循环

<!--    public List<Employee> getEmpsByConditionForeach(List<Integer> ids);
	
加了@param  collection="ids"
    public List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);

-->
    <select id="getEmpsByConditionForeach" resultType="com.atguigu.mybatis.bean.Employee">
        select * from tbl_employee where id in
        <!--
            collection:指定要遍历的集合
                list类型的参数会特殊处理封装在map中，map中的key就叫list
            item:将当前遍历出的元素赋值给指定的变量
            separator：每个元素之间的分隔符
            open:遍历出所有结果拼接一个开始的字符
            close:遍历出所有结果拼接一个结束的字符
            index:索引，遍历list的时候index是索引 item是值
                        遍历map的时候index表示的是mao的key,item就是map的值

            #{变量名}就能取出变量的值也就是当前遍历出的元素

        -->
        <foreach collection="list" item="item_id" separator=","
                 open="(" close=")">
            #{item_id}
        </foreach>
    </select>

MySQL下批量保存

第一种

<!--    public void addEmps(@Param("emps") List<Employee> emps);-->
    <insert id="addEmps">
        insert into tbl_employee(last_name,email,gender,d_id)
        values
        <foreach collection="emps" item="emp" separator=",">
            (#{emplastName},#{emp.email},#{emp.gender},#{emp.dept.id})
        </foreach>
    </insert>
</mapper>

第二种：

需要在dbconfig.properties上加上

jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

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

Oracle下批量保存

<!-- <insert id="addEmps"><foreach collection="emps" item="emp" separator=";">insert into tbl_employee(last_name,email,gender,d_id)values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})</foreach></insert> -->


<!-- Oracle数据库批量保存： Oracle不支持 values(),(),()Oracle支持的批量方式
 1、多个insert放在begin - end里面 begin insert into employees(employee_id,last_name,email)values(employees_seq.nextval,'test_001','test_001@atguigu.com');
insert into employees(employee_id,last_name,email)values(employees_seq.nextval,'test_002','test_002@atguigu.com');end;
2、利用中间表： insert into employees(employee_id,last_name,email)select employees_seq.nextval,lastName,email from(select 'test_a_01' lastName,'test_a_e01' email from dualunionselect 'test_a_02' lastName,'test_a_e02' email from dualunionselect 'test_a_03' lastName,'test_a_e03' email from dual) -->



-<insert id="addEmps" databaseId="oracle">

<!-- oracle第一种批量方式 -->


<!-- 
<foreach collection="emps" item="emp" open="begin" close="end;">
	insert into employees(employee_id,last_name,email)values(employees_seq.nextval,#	{emp.lastName},#{emp.email});
</foreach> -->


<!-- oracle第二种批量方式 -->

insert into employees( 
<!-- 引用外部定义的sql -->

-<include refid="insertColumn">

<property value="abc" name="testColomn"/>

</include>
) 
    
<foreach close=")" open="select employees_seq.nextval,lastName,email from(" separator="union" item="emp" collection="emps">
    select #{emp.lastName} lastName,#{emp.email} email from dual 
    </foreach>

</insert>

内置参数

<!--  public List<Employee> getEmpsTestInnerParameter(Employee employee);-->
    <select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
        <if test="_databaseId=='mysql'">
            select * from tbl_employee
        </if>
        <if test="_databaseId=='oracle'">
            select * from employees
        </if>
    </select>

bind 标签

模糊查询的时候 不想 加上 % 可以用bind 在映射文件中加上

    <select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
        <!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值-->
        <bind name="_lastName" value="'%'+lastName+'%'"></bind>
        <if test="_databaseId=='mysql'">
            select * from tbl_employee
            <if test="_parameter!=null">
                where last_name like #{_lastName}
            </if>
        </if>
    </select>
    

sql标签

    <!--抽取可重用的sql片段，方便后面引用
        1、sql经常将要查询的列名，或者插入用的列名抽取出来，方便引用
        2、include 来引用已经抽取的sql
        3、include还可以自定义一些property，sql标签内部就能使用自定义的属性
            ${prop},#{ prop}不可用
    -->
    <sql id="insertColumn">
        employee_id,last_name,email,${testColumn}
    </sql>
    

        insert into tbl_employee(
            <include refid="insertColumn">
                <property name="testColumn" value="abc"/>
            </include>
        )