【Mybatis】动态sql

关于Mybatis的动态sql学习笔记

一、基础标签

1.1 if标签

• if：判断
• <if test=""> sql语句 </if>
  • test：判断表达式(OGNL)
  • 从参数中取值进行判断
  • 如果遇到特殊符号，要使用转义字符

• 举例：查询员工，要求：携带哪个字段时，查询条件就带上这个字段的值

接口
    public List<Employee> getEmpByConditionIf(Employee employee);

映射文件
    <select id="getEmpByConditionIf" resultType="Dao.Employee">
        select *
        from tbl_employee
        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>
    </select>

• 存在问题：
  • 当id不存在时，sql语句就变成 where and Xxxx，会报错
• 解决方法一：
  • 给where后面先弄一个1=1，后面条件拼接都用and Xxx拼接

SQL语句
        select *
        from tbl_employee
        where 1=1
        <if test="id!=null">
            and 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标签

1.2 where标签

• where
• 用来替代sql语句中where内的查询条件
• 好处：会去掉凭借字符串第一个多出的and / or，解决拼接错误

• 举例：上述例子修改

接口
    public List<Employee> getEmpByConditionIf2(Employee employee);

映射文件
    <select id="getEmpByConditionIf2" resultType="Dao.Employee">
        select *
        from tbl_employee
        <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>

• 注意：where只能解决”and Xxxx”在前面拼接的，而”Xxx and”在后面拼接的无法解决，拼接要规范
• 如果要使用”Xxxx and”的方式拼接，可以使用trim标签

1.3 trim标签

• trim标签
• trim标签用于字符串的截取
• <trim prefix="" prefixOverrides="" suffix="" suffixOverrides=""></trim>
  • prefix：前缀，给拼串后的字符串写一个前缀
  • prefixOverrides：前缀覆盖，去掉整个字符串前面多余的字符
  • suffix：后缀，给拼串后的字符串写一个后缀
  • suffixOverides：后缀覆盖，去掉整个字符串后面多余的字符

• 举例：修改上述例子，利用trim来实现and在后拼接

接口
    public List<Employee> getEmpByConditionTrim(Employee employee);

映射文件
    <select id="getEmpByConditionTrim" resultType="Dao.Employee">
        select *
        from tbl_employee
        <!--where标签无法解决and/or后拼接问题，使用trim标签解决
            下面字符串，前面缺少一个where，后面可能会多余一个and
        -->
        <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>

1.4 choose标签

• choose标签
• 分支选择，使用方法与Java的分支选择差不多
  • choose — switch
  • when — case
  • otherwise — default

• 举例：如果对象带id就用id查，如果对象有lastName就用lastName查，二者选其一；如果都没有，就查找女性信息

接口
    public List<Employee> getEmpByConditionChoose(Employee employee);

映射文件
    <select id="getEmpByConditionChoose" resultType="Dao.Employee">
        select *
        from tbl_employee
        <where>
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="lastName!=null">
                    last_name=#{lastName}
                </when>
                <otherwise>
                    gender=1
                </otherwise>
            </choose>
        </where>
    </select>

1.5 set标签

• set标签
• 用来取代修改的set，用if实现动态sql时，会出现字符串拼接错误

• 举例：实现动态修改数据

接口
    public void updateEmp(Employee emmployee);

映射文件
    <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 and gender!=''">
                gender=#{gender}
            </if>
        </set>
        where id=#{id}
    </update>

• 跟where一样，如果字符串出现拼接错误，可以使用trim解决

接口
    public void updateEmp2(Employee emmployee);

映射文件
    <update id="updateEmp2">
        update tbl_employee
        <!--字符串前缀缺少set，后缀可能会多出','-->
        <trim prefix="set" suffixOverrides=",">
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null and gender!=''">
                gender=#{gender}
            </if>
        </trim>
        where id=#{id}
    </update>

1.6 foreach标签

• foreach标签
• 用来遍历集合/数据，使用方法和增强for循环类似
• <foreach collection="" item="" separator="" open="" close="" index="">
  • collection：指定要遍历的集合
    • (mybatis会自动将list封装为Map集合[list, list集合名称]，所以要填写list；set, array同理)
    • (可以在接口的参数用@param(“”)起别名，来直接调用用别名)
    • (手动将List集合转变成Map集合)
  • item：将当前遍历的元素赋值给指定的变量
  • separator：元素之间分割符号
  • open：遍历完拼接的字符串的前缀
  • close：遍历完拼接的字符串的后缀
  • index：索引
    • 遍历list的时候，index是索引值，item是当前值
    • 遍历map的时候，idnex是map的Key，item是map的Value

• 举例：实现以下sql语句
  • select * from tbl_employee where id in (1, 2, 3, 4)

接口
    public List<Employee> getEmpByConditionForeach(List<Integer> id);

映射文件
    <select id="getEmpByConditionForeach" resultType="Dao.Employee">
        select *
        from tbl_employee
        where id in
        <!--由于接口没定义别名，所以collection参数为list-->
        <foreach collection="list" item="item_id" separator="," open="(" close=")">
            #{item_id}
        </foreach>
    </select>

---

二、批量插入

• 利用foreach标签实现动态插入

  2.1 MySQL处理

• 方法一：实现以下sql语句
  • insert into tbl_employee(last_name, gender, email) value(x, x, x, x), (x, x, x, x)
  • mysql支持value(),.(), ()的方式进行批量插入

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

映射文件
    <insert id="addEmps">
        insert into tbl_employee(last_name, gender, email, dept_id)
        value
        <!--由于接口参数定义别名，collection直接使用别名-->
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.lastName}, #{emp.gender}, #{emp.email}, #{emp.dept.id})
        </foreach>
    </insert>

• 方法二：执行多条sql语句
  • 在mysql的url添加属性：allowMultiQueries=true
  • 然后使用foreach实现重复多条sql语句，分隔符为’;’

2.2 Oracle处理

• 使用以下sql语句格式：
  • 用begin end包括多条sql语句，多条sql语句使用foreach来实现，分隔符为’;’

begin
	多条sql语句;
end;

---

三、默认参数

• 在Mybatis中，除了方法中我们传入的参数，还额外自带两个默认的参数
  • _parameter：代表整个参数
    • 单个参数：_parameter就是传递的参数
    • 多个参数：参数会封装在一个map，_parameter就是这个map
  • _databaseId：如果配置了databaseIdProvider标签，_databaseId就是代表当前数据库的别名

全局配置文件
    <databaseIdProvider type="DB_VENDOR">
        <property name="MySQL" value="mysql"/>
        <property name="Oracle" value="oracle"/>
        <property name="SQL Server" value="sqlServer"/>
    </databaseIdProvider>

接口
    public List<Employee> getEmpsTestInnerParameter(Employee employee);

映射文件
    <select id="getEmpsTestInnerParameter" resultType="Dao.Employee">
        <if test="_databaseId=='mysql'">
            select *
            from tbl_employee
            <if test="_parameter!=null">
                where gender=#{_parameter.gender}
            </if>
        </if>
    </select>

---

四、进阶标签

4.1 bind标签

• 是CURD标签的子标签，可以将OGNL表达式绑定在一个变量中，方便后来引用这个变量的值
• <bind name="" value="" />
  • name：变量名
  • value：参数进行处理后的值
• 例：<bind name="_lastName" value="'%'+lastName+'%'"/>

4.2 sql、include标签

• sql标签
• 抽取可重用的sql片段，方便后面引用，类似Java的方法封装

<sql id="">
    sql语句
</sql>

• include标签
• 引用外部的定义的sql标签
• <include refid=""></include>
  • refid：引用的sql标签id