mybatis03_动态sql

一、动态sql概念

        动态SQL，即通过MyBatis 提供的各种标签对条件作出判断已实现动态拼接SQL语句。条件判断使用的表达式为OGNL表达式。

二、动态sql标签的分类

        大概分为一下四种常用的动态标签

元素	作用	使用场景
if	判断语句	用于单条件分支判断
where、set、trim	辅助元素	用于处理一些查询条件拼接问题
choose、when、otherwise	条件的选择、相当java中的 switch case default 语句	用于多条件分支判断
foreach	循环语句	用于批量添加和查询

        注意事项：

        在mapper的动态SQL中如出现大于号，小于号，大于等于号，小于等于号，最好将其转换为实体符号，否则，XML可能会出现解析出错问题

        特别是小于号 (<),在XML中绝对不能出现 ，否则一定出错

原符号	实体符号
<	&It;
<=	&It;=
>	&gt；
>=	&gt；=
&	&amp；
’	&apos；
"	&quot；

三、动态标签的用法

        员工实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class EmpEntity {
  private Integer empno;
  private String ename;
  private String job;
  private  Integer mgr;
  private Date hiredate;
  private Float sal;
  private Float comm;
  private Integer deptno;
}

        数据库员工表

3.1 if标签——单条件的判断

        当参数满足条件才会执行某个条件

dao层：

List<EmpEntity> ifTest(EmpEntity empEntity);

mapper层:   根据实体中 ename 和 empno 是否非空进行拼接查询

    <select id="ifTest" resultType="com.aaa.entity.EmpEntity">
        select * from emp where 1=1
        <if test="ename!=null and ename !=''">
            and ename = #{ename}
        </if>
        <if test="empno!=null and empno !=''">
            and empno = #{empno}
        </if>
    </select>

测试类

public class EmpDaoTest {
    SqlSession sqlSession;
    EmpInfoMybatisDao empDao;
    @Before
    public void setUp() throws Exception {
        System.out.println("开始测试");
        sqlSession = MySqlsessionFactory.getSQLSession();
        empDao = sqlSession.getMapper(EmpInfoMybatisDao.class);
    }
    @After
    public void tearDown() throws Exception {
        System.out.println("结束测试");
        if (sqlSession != null) {
            // 事物提交
            sqlSession.commit();
        } else {
            // 关闭sqlSession
            sqlSession.close();
        }
    }
    @Test
    public void ifTest() {
        EmpEntity entity = new EmpEntity();
        entity.setEname("张三");
        entity.setEmpno(1);
        List<EmpEntity> entities = empDao.ifTest(entity);
        for (EmpEntity empEntity : entities) {
            System.out.println(empEntity);
        }
    }
}

结果显示：

3.2 where标签 

        如果不使用where标签，就要在where其他判断语句前加入1=1 。如 select * from emp where 1=1加其他的 if 判断语句

        如果不想在sql语句后加入这个1=1，可以直接使用where标签和下面的choose标签和if标签搭配使用。使用where 语句 可以自动消除第一个条件中的 and 且加上where 例子如下面两个标签中即可

dao层

List<EmpEntity> whereTest(@Param("salMin") Integer salMin,@Param("salMax") Integer salMax);

mapper层 

    <select id="whereTest" resultType="com.aaa.entity.EmpEntity">
        select * from emp
        <where>
            <if test="salMin!=null and salMin!=''">
                and sal &gt; #{salMin}
            </if>
            <if test="salMax!=null and salMax!=''">
                and sal &lt; #{salMax}
            </if>
        </where>
    </select>

 测试类

    @Test
    public void whereTestDemo() {
        List<EmpEntity> sals = empDao.whereTest(15000, 20000);
        for (EmpEntity sal : sals) {
            System.out.println(sal.toString());
        }
    }

 测试结果

3.3 choose、when、otherwise 标签 多条件分支判断

        choose、when、otherwise ： choose标签是按顺序判断其内部when标签中的test条件是否成立，如果有一个成立，则choose结束；如果所有的when条件都不满足时，则执行otherwise中的SQL。类似于java的switch语句。

dao层

 List<EmpEntity> chooseTest(@Param("enameX")String ename,@Param("empnoX")Integer empno);

mapper层：跟据判断 ename 和 empno 是否非空进行拼接  如果都为空则拼接 ename='王五'

<select id="chooseTest" resultType="com.aaa.entity.EmpEntity">
        select * from emp
        <where>
            <choose>
                <when test="enameX!=null and enameX!=''">
                    ename = #{enameX}
                </when>
                <when test="empnoX!=null and empnoX!=''">
                    empno = #{empnoX}
                </when>
                <otherwise>
                    ename = '王五'
                </otherwise>
            </choose>
        </where>
    </select>

测试层

    @Test
    public void chooseTest() {
        List<EmpEntity> test = empDao.chooseTest("张三", null);
//        List<EmpEntity> test = empDao.chooseTest(null, 1);
//        List<EmpEntity> test = empDao.chooseTest("张三", 1);
//        List<EmpEntity> test = empDao.chooseTest(null, null);
        for (EmpEntity entity : test) {
            System.out.println(entity);
        }
    }

上面四种测试结果分别为

 3.4 set标签

        set标签用于解决动态更新语句存在的符号问题，一般配合if标签用于修改语句，set标签会动态前置SET关键字，同时也会消除无关的逗号，因为用了条件语句后，可能就会在生成的赋值语句的后面留下逗号。

 dao层：

int setTest(EmpEntity empEntity);

mapper:

    <update id="setTest">
        update emp
        <set>
            <if test="ename!=null and ename !=''">
                 ename=#{ename},
            </if>
            <if test="job!=null and job !=''">
                 job=#{job},
            </if>
            <if test="deptno!=null and deptno !=''">
                 deptno=#{deptno},
            </if>
        </set>
        <where>
            empno = 1
        </where>
    </update>

测试类

    @Test
    public void setTestDemo() {
        EmpEntity entity = new EmpEntity();
        entity.setJob("会计");
        entity.setDeptno(5);
        int test = empDao.setTest(entity);
        System.out.println("影响条数:"+test);
    }

测试结果

 3.5 foreach标签

        循环标签 适用于批量添加、删除 和查询记录

正常sql语句为 select * from emp where id in(1,3,5);
下面的为使用foreach遍历 循环查询
    解释:
    <foreach collection="集合类型" open="开始的字符" close="结束的字符"
	    item="集合中的成员" separator="集合成员之间的分割符">
        #{item的值}
    </foreach>
   标签属性：
   collection:表示循环的对象是数组还是list集合。
        如果dao方法的形参是数组，collection="array";
		如果dao方法形参是list，collection="list";
   open:循环开始的字符。sql.append("(");
   close:循环结束的字符。sql.append(")");
   item:集合成员，自定义的变量。Integer item = idList.get(i);
   separator:集合成员之间的分隔符。sql.append(",");
   #{item的值}:获取集合成员的值;

最重要的是 collection 属性 该属性是必须指定的

• 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
• 如果传入的单参数且参数类型是一个array数组的时候，collection的属性值为array
• 如果传入的参数是多个的啥时候，我们就需要把他们封装成一个Map，单参数也可以封装成Map，实际上如果我们在传入参数的时候，在Mybatis里面也是会把它封装成一个map，map的key就是参数名，所以这个时候collection1属性值就是传入的List或array对象在自己封装的Map里面的key

1、使用foreach进行批量新增

dao层

int addEmpBatch(@Param("addInfo") List list);

mapper层

    <!--   批量添加-->
    <insert id="addEmpBatch">
        insert into emp (ename,job,sal) values
        <foreach collection="addInfo" item="aaa" separator=",">
            (#{aaa.ename},#{aaa.job},#{aaa.sal})
        </foreach>
    </insert>

测试类

    // 批量添加
    @Test
    public void addEmpBatchDemo() {
        StopWatch sw = new StopWatch();
        sw.start();
        List<EmpEntity> empList = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            EmpEntity entity = new EmpEntity();
            entity.setEname("张三"+(i+1));
            entity.setJob("工程师"+(i+1));
            entity.setSal((i+1)*1f);
            empList.add(entity);
        }
        int aa = empDao.addEmpBatch(empList);
        sw.stop();
        System.out.println("成功添加"+aa+"条");
        System.out.println("花费时间："+sw.getTotalTimeMillis());
    }

测试结果

 2、使用foreach进行批量删除

dao层

int forEachTest(@Param("myList")List list);

mapper层

    <delete id="forEachTest">
        delete from emp where empno in
        <foreach collection="myList" item="empno" open="(" close=")" separator=",">
            #{empno}
        </foreach>
    </delete>

测试类

    @Test
    public void forEachTstDemo() {
        List<Integer> empnos = new ArrayList();
        for (int i = 8949; i < 9049; i++) {
            empnos.add(i);
        }
        int aa = empDao.forEachTest1(empnos);
        System.out.println("删除条数为：" + aa);
    }

测试结果

3.6 新增返回主键 （前提数据库是自增的）

        mybatis提供了不少操作sql语句的标签，其中包括了新增的标签，其返回与主键相关属性也是该标签提供的，与返回主键相关的属性如下：

属性	描述
keyProperty	(仅对insert和update有用)此属性的作用是将插入或更新操作时的返回值赋值给PO类的某个属性，通常会设置为主键对应的属性。 如果需要设置联主键，可以在多个值之间用逗号隔开
keyColumn	(仅对insert和update有用)此属性用于设置第几列是主键，当主键列不是表中的第一列时需要设置。在需要主键联合时，值可以用逗号隔开
useGeneratedKeys	(仅对insert叶和update有用)此属性会使MyBatis使用JDBC的getGeneratedKeys()方法来获取由数据库内部生产的主键，如MySQL相SQLServer等自动递增的字段，其默认值为false

  dao层

    // 新增返回主键
    int autoKey(EmpEntity entity);

 mapper层

    <insert id="autoKey" keyProperty="empno" useGeneratedKeys="true">
        insert into emp (ename,job,sal) values (#{ename},#{job},#{sal})
    </insert>

测试类

    // 新增返回主键
    @Test
    public void autoKry() {
        EmpEntity entity = new EmpEntity();
        entity.setEname("黄天霸");
        entity.setJob("销售员");
        entity.setSal(8000f);
        int aa = empDao.autoKey(entity);
        // 新增完 通过实体.getId()来获取主键
        System.out.println("新增返回主键ID:" + entity.getEmpno());
    }

测试结果

 普通新增

四、模糊查询

        当我们从数据库中查询数据时，大批量的数据会存在相同的数据。比如重名的人，当我们使用姓名查询该姓名的所有数据时，我们需要在Mybatis中使用到模糊查询的概念。

4.1 模糊查询 like 语句常用的有一下三种

•  方式一    '%${xxx}%'            可能引起SQL注入，不推荐
•  方式二    '%#{xxx}%' 或者 "%"#{xxx}"%"      因为 #{xxx}解析成sql语句时候，会在变量外侧自动加单引号，所以这里 % 需要使用双引号，不能使用单引号，不然会查不到任何结果。
• 方式三     concat('%', #{xxx} , '%')     使用CONCAT()函数   （推荐）

4.2 #{} 和 ${}区别

• #{}是占位符，预编译处理；${}是拼接符，字符串替换，没有预编译处理。
• Mybatis在处理#{}时，#{}传入参数是以字符串传入，会将SQL中的#{}替换为?号，调用PreparedStatement的set方法来赋值。
• #{} 可以有效的防止SQL注入，提高系统安全性；
• ${}不能防止SQL注入 #{} 的变量替换是在DBMS中；${} 的变量替换是在DBMS外

 4.3 代码演示

        dao层

List<EmpEntity> mhQuery(Map map);

        mapper层

    <select id="mhQuery" resultType="com.aaa.entity.EmpEntity">
        select * from emp
        <where>
            <if test="ename !=null and ename != ''">
                and ename like concat('%',#{ename},'%')
            </if>
            <if test="job !=null and job != ''">
                and job like "%" #{job} "%"
            </if>
            <if test="sal !=null and sal != ''">
                and sal like '%${sal}%'
            </if>
        </where>
    </select>

        测试类

    // 模糊查询
    @Test
    public void moHuQueryTest(){
        Map map = new HashMap();
        map.put("ename","张");
        map.put("job","员");
        map.put("sal","20");
        List<EmpEntity> entities = empDao.mhQuery(map);
        for (EmpEntity entity : entities) {
            System.out.println(entity.toString());
        }
    }

       测试结果