03. mybatis 动态sql && 模糊查询

一、 动态sql 简介

       MyBatis 的一个强大的特性之一通常是它的动态 SQL 能力，MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

       foreach：遍历
       if：判断语句
       choose (when, otherwise) ：类似switch语句： choose == switch， when == case ， otherwise == default
       trim (where, set)：where 和  set 只是 trim 的特殊形式

二、 测试

        1. xml 映射片段

<!-- foreach 示例 --><insert id="addPersonList" parameterType="java.util.List" >insert into mybatis_person(name, age, sex) values  <foreach collection="list" item="person" separator="," >  (#{person.name},#{person.age},#{person.sex})</foreach>  </insert>  <select id="getInPersons" parameterType="java.util.List" resultType="Person">select * from mybatis_personwhere age in <foreach collection="list" item="age" open="(" separator="," close=")">#{age}</foreach></select><!-- if 示例 --><!-- case1: 请求参数为简单类型 int， Integer 时，if 中需要用内置参数名:_parameter 来作为判断条件,但仅限于if 的test 中 --><select id="getPersonsByAge" parameterType="int" resultType="Person">select * from mybatis_person<if test="_parameter != null">where age = #{age}</if></select><!-- case2: 请求参数为JavaBean 或者 map 时，test 中直接写属性（支持级联属性，比如user.name） 或者 key 即可 --><select id="getPersonsByPersonAge" parameterType="Person" resultType="Person">select * from mybatis_person<if test="age != null">where age = #{age}</if></select><!-- choose (when, otherwise)  --><!-- 注意：类似于if， 不同类型的parameterType， 影响test 中的变量名称  --><select id="getPersonsBySex" parameterType="String" resultType="Person">select * from mybatis_person<choose><when test="_parameter != null">where sex = #{sex}</when><otherwise>where sex = 'man'</otherwise></choose></select><!-- where  等价于  <trim prefix="WHERE" prefixOverrides="AND |OR "></trim>1. 自动添加where： 至少有一个if 为true， 则添加where2. 自动删除多余的AND或者OR关键字： 如果where 后面紧跟AND 或者OR 则删除--><select id="getPersonByMTAgeEQSex"  parameterType="Person" resultType="Person">select * from mybatis_person<where><if test="age != null">age > #{age}</if><if test="sex != null">AND sex = #{sex}</if></where></select><!-- Set  等价于 <trim prefix="SET" suffixOverrides=","></trim>1. 自动添加关键字Set2. 自动删除多余的逗号注意：  如果所有的if 都为false，那么进行更新的 时候可能会报错,因为执行的sql 将会 变成：update mybatis_person where id = ? --> <update id="updatePersonPropertyNotNull" parameterType="Person">update mybatis_person <set><if test="name != null">name = #{name},</if><if test="age != null">age = #{age},</if><if test="sex != null">sex = #{sex},</if></set>where id = #{id} </update>   <!-- trim  sql 片段的构造，可自定义类似于SET， WHERE 之类的标签prefix: 需要拼接的前缀suffixOverrides：如果前缀以该符号开始，删除suffix：需要拼接的后缀prefixOverrides： 如果后缀以该符号结束，删除  -->  <update id="updatePersonPropertyNotNullByTrim" parameterType="Person">update mybatis_person <trim prefix="SET" suffixOverrides=","><if test="name != null">name = #{name},</if><if test="age != null">age = #{age},</if><if test="sex != null">sex = #{sex},</if></trim>where id = #{id} </update>

二、 测试用例

package org.zgf.learn.mybatis.mapper.api;import java.util.ArrayList;import java.util.List;import org.junit.Test;import org.zgf.learn.mybatis.entity.api.Person;import org.zgf.learn.mybatis.mapper.abase.BasicTest;public class Test_PersonMapper extends BasicTest{String statement = "";String namespace = "org.zgf.learn.mybatis.entity.PersonMapper.";/*** TODO 动态sql 语句测试  ***//** 测试 foreach 用法 */@Testpublic void test_addPersonList(){List<Person> personList = new ArrayList<>();Person person = new Person("zong_101", 10, "man");Person person2 = new Person("zong_102", 20, "boy");Person person3 = new Person("zogn_103", 30, "boy");personList.add(person);personList.add(person2);personList.add(person3);this.statement += "addPersonList";int insertNum = session.insert(statement, personList);System.out.println("共插入数据：" + insertNum + " 条");}@Testpublic void test_getInPersons(){List<Integer> ageList = new ArrayList<>();ageList.add(10);ageList.add(20);this.statement += "getInPersons";List<Person>  personList = session.selectList(this.statement, ageList);for (Person person : personList) {System.out.println(person);}}/** 测试 if 用法    */@Testpublic void test_getPersonsByAge(){//Integer age = 20;Integer age = null;this.statement += "getPersonsByAge";List<Person>  personList = session.selectList(this.statement, age);for (Person person : personList) {System.out.println(person);}}@Testpublic void test_getPersonsByPersonAge(){//Integer age = 20;Integer age = null;this.statement += "getPersonsByPersonAge";Person personCondition = new Person(null,age,null);List<Person>  personList = session.selectList(this.statement, personCondition);for (Person person : personList) {System.out.println(person);}}/** 测试：choose (when, otherwise)  */@Testpublic void test_getPersonsBySex(){String sex = null;this.statement += "getPersonsBySex";List<Person> personList = session.selectList(this.statement,sex);for (Person person : personList) {System.out.println(person);}}/** 测试：Where*/@Testpublic void test_getPersonByMTAgeEQSex(){//Person personCondition = new Person(null,null,null);//Person personCondition = new Person(null,20,null);Person personCondition = new Person(null,null,"man");//Person personCondition = new Person(null,20,"man");this.statement += "getPersonByMTAgeEQSex";List<Person> personList = session.selectList(this.statement,personCondition);for (Person person : personList) {System.out.println(person);}}/**  Set **/@Testpublic void test_updatePersonPropertyNotNull(){//Person person = new Person(1, "zong_update",26,"man");Person person = new Person(1, null, null, null);this.statement += "updatePersonPropertyNotNull";int updateNum = session.update(this.statement,person);System.out.println("更新了 " + updateNum + " 条记录");}/**  trim **/@Testpublic void test_updatePersonPropertyNotNullByTrim(){//Person person = new Person(1, "zong_update",26,"man");Person person = new Person(1, null, null, null);this.statement += "updatePersonPropertyNotNull";int updateNum = session.update(this.statement,person);System.out.println("更新了 " + updateNum + " 条记录");}}