mybatis——动态SQL

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lzr.dao.EmployeeMapperDynamicSQL">
    <!--
    if
    choose (when, otherwise):分支选择：带了brake的switch-case
       如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个
    trim 字符串截取(where(封装查询条件), set(封装修改条件))
    foreach
    -->
    <!--查询员工，要求，携带了哪个字段查询条件就带上这个字段的值-->
    <!--public List<Employee> getEmpsByConditionIf(Employee employee);-->
    <select id="getEmpsByConditionIf" resultType="com.lzr.bean.Employee">
        select * from tbl_employee
--         where
        <where>
            <if test="id!=null">
                id=#{id}
            </if>
            <if test="lastName!=null and lastName!=''">
                and last_name=#{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>

    <!--public List<Employee> getEmpsByConditionTrim(Employee employee);-->
    <select id="getEmpsByConditionTrim" resultType="com.lzr.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=#{lastName} and
            </if>
            <if test="email!=null and email.trim()!=''">
                email=#{email} and
            </if>
            <if test="gender==0 or gender==1">
                gender=#{gender}
            </if>
        </trim>
    </select>

    <!--public List<Employee> getEmpsByConditionChoose(Employee employee);-->
    <select id="getEmpsByConditionChoose" resultType="com.lzr.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=#{lastName}
                </when>
                <when test="email!=null">
                    email=#{email}
                </when>
                <otherwise>
                   gender = 0
                </otherwise>
            </choose>
        </where>
    </select>

    <!--public void updateEmp(Employee employee);-->
    <update id="updateEmp">
         update tbl_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}
    </update>


    <!--public List<Employee> getEmpsByConditionForeach(List<Integer> list);-->
    <select id="getEmpsByConditionForeach" resultType="com.lzr.bean.Employee">
        select * from tbl_employee where id in
        -- collection：指定要遍历的集合；
        -- list类型的参数会特殊处理封装在map中，map的key就叫list
        -- separator:每个元素之间的分隔符
        -- item：将当前遍历出的元素赋值给指定的变量
--         open：遍历出所有结果拼接一个开始字符串
--         close：遍历出所有结果拼接一个结束的字符串
--         index：遍历list的时候是索引，index就是索引，item就是值
--                遍历map的时候index表示的就是map的key，item就是map的值
        -- #{变量名}：就能取出变量的值也就是当前遍历的元素
        <foreach collection="ids" item="item_id" separator="," open="(" close=")">
            #{item_id}
        </foreach>
    </select>

    <!--批量保存-->
    <!--public void addEmps(@Param("emps") List<Employee> emps);-->
    <!--Mysql下批量保存，可以forreach遍历，mysql支持values(),(),()语法-->
    <insert id="addEmps">
        insert into tbl_employee(last_name,email,gender,d_id)
        values
        <foreach collection="emps" item="emp" separator="," >
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
        </foreach>
    </insert>


    <!--两个内置参数：
    不只是方法传递过来的参数可以被用来判断，取值。。
    mybatis默认还有两个内置参数，
    _parameter:代表整个参数
        单个参数：_parameter就是这个参数
        多个参数：参数会被封装成一个map：_parameter就是代表这个map

    _databaseId：如果配置了databaseIdProvider标签，
        _databaseId就是当前数据库的别名
    -->

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

    </select>
    
</mapper>

package com.lzr.dao;

import com.lzr.bean.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author GGBond
 * @create 2021-05-26-8:49
 */
public interface EmployeeMapperDynamicSQL {

    //携带了哪个字段查询条件就带上这个字段的值

    public List<Employee> getEmpsByConditionIf(Employee employee);

    public List<Employee> getEmpsByConditionTrim(Employee employee);

    public List<Employee> getEmpsByConditionChoose(Employee employee);

    public void updateEmp(Employee employee);

    public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);

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

    public List<Employee> getEmpsTestInnerParameter(Employee employee);

}

 @Test
    public void test03() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1,"Admin",null,null);
//            List<Employee> emps = mapper.getEmpsByConditionIf(employee);
//            for (Employee emp: emps) {
//                System.out.println(emp);
//            }
            //查询的时候如果某些条件没带可能sql拼装会有问题
            //1.给where后面加上1=1，以后的条件都and XXX.
            //2.mybatis使用where标签来将所有的查询条件包括在内,mybatis就会将where标签中拼装的sql，多出来的
            //and或者or去掉；where只会去掉第一个多出来的and或者or。

            //测试trim
//            List<Employee> emps2 = mapper.getEmpsByConditionTrim(employee);
//            for (Employee emp: emps2){
//                System.out.println(emp);

//            }

            //测试choose
//            List<Employee> list = mapper.getEmpsByConditionChoose(employee);
//            for (Employee emp : list) {
//                System.out.println(emp);
//            }
            //测试set标签
//            mapper.updateEmp(employee);
//            openSession.commit();
//            List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3, 4));
//            for (Employee emp:list) {
//                System.out.println(emp);
//            }

        }finally {
            openSession.close();
        }
    }

    @Test
    public void test04() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> emps = new ArrayList<>();
            emps.add(new Employee(null,"smith","smith@atgugu.com","1",new Department(1)));
            emps.add(new Employee(null,"allen","allen@atgugu.com","0",new Department(1)));
            mapper.addEmps(emps);
            openSession.commit();
        }finally {
            openSession.close();
        }
    }

    @Test
    public void test05() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> list = mapper.getEmpsTestInnerParameter(new Employee());
            for (Employee employee:list) {
                System.out.println(employee);
            }

        }finally {
            openSession.close();
        }
    }