Mybatis框架学习笔记(7) --- [动态sql的使用]

mybatis3版本的文档 -->mybatis文档

写在前面
Ok,也是进行到动态sql这部分了;
需要说明的是,后来我又给员工表添加了一个记录年龄的列,当然在员工类中也添加了属性以及它的getset方法;构造方法;toString();这些也都加上这个属性了.

ml

1. if 标签与 where 标签

使用 if标签 可以对条件进行判断;
一般是结合在其他标签中使用;

where 标签 可以动态地添加where关键字 ,并且可以做到 自动去除后面不符合条件时 的and或者or关键字

<where>元素会进行判断,如果它包含的标签中有返回值的话,它就插入一个where
如果标签返回的内容是以 ANDOR 开头,它会自动去除掉ANDOR

比如说,我要设置两个查询条件,姓名和性别筛选查询人员的信息,但是也可以不通过这两个条件进行查询,也可以一个条件去查询,而不使用另一个条件;
当然你可以写4个SQL语句进行处理;
但这时用动态SQL查询的话,还是比较方便的,只需要写一次sql语句就能同时兼顾这四种情况.

在实例中看看效果

Ok,在EmployeeMapper职工类的持久层接口写个方法;根据年龄和性别动态查询员工的信息;

//根据条件动态查询员工列表; 姓名/性别; @Param()为参数作注解,到时候SQL那边就用注解里面的名称;
List<Employee> getEmpByNameOrAge(@Param("name") String name, @Param("sex")String sex);

EmployeeMapper.xml中添加对应的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.xiaozhi.mapper.EmployeeMapper">
<!--定义映射关系map-->
    <resultMap id="empmap" type="employee">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="sex" property="sex"/>
        <!--关联部门表-->
        <association property="dept" javaType="dept">
            <result column="dname" property="name"/>
        </association>
        <!--关联用户管理表-->
        <association property="user" javaType="user">
            <result column="account" property="account"/>
        </association>
    </resultMap>
    
    <!--根据姓名/性别动态查询员工-->
    <select id="getEmpByNameOrAge" resultMap="empmap">
        SELECT
          e.`id`,
          e.`name`,
          e.`sex`,
          e.`age`,
          d.`name` dname ,
          u.`account`
          FROM t_employee e
          LEFT JOIN  t_dept d ON  e.`deptID`=d.`id`
          LEFT JOIN  t_user u ON  u.`id`=e.`optionId`
          <where>
              <if test="name != null &amp; name !='' ">
                  e.name = #{name}
              </if>
              <if test="sex != null &amp; sex !='' ">
                  and e.sex= #{sex}
              </if>
          </where>
    </select>
</mapper>    

当然;在核心配置文件mybatis-config.xml里面还要配置员工的mapper映射文件地址;
(我这个是之前配置了的)

Mybatis框架学习笔记(7) --- [动态sql的使用]

测试使用;

这里也是直接把上次的那个页面查询框改了一下;
Mybatis框架学习笔记(5) —[多表关联查询 连接到前端页面显示]

OK,就从service包下的EmployeeService来一步步修改吧;

public class EmployeeService {
//根据姓名/性别动态查询员工;
    public List<Employee> getEmpByNameOrAge(String name,String sex){
        //调用工具类;
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //获取代理对象;
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        //调用方法;
        List<Employee> list = mapper.getEmpByNameOrAge(name, sex);
        //关闭sqlSession;
        sqlSession.close();
        return list;
    }
}

servlet包下的EmployeeServlet

public class EmployeeServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        PrintWriter out=null;

        //获取到标记参数;
        String mark = req.getParameter("mark");
        if(mark.equals("empList")){
            try{
                //请求编码;响应解码;
                resp.setContentType("text/html;charset=utf-8");
                //会以流的方式返回;
                out = resp.getWriter();

                //获取参数;
                String name = req.getParameter("name");
                String sex = req.getParameter("sex");

                System.out.println("正在查询姓名为--:"+name+"性别为==>"+sex);
                //调用服务层处理;
                EmployeeService employeeService = new EmployeeService();
                List<Employee> empByNameOrAge = employeeService.getEmpByNameOrAge(name, sex);
                //控制台输出测试;
                //empByNameOrAge.forEach(System.out::println);
                //不为空就发送出去;
                if(!empByNameOrAge.isEmpty()){
                    out.print(new Gson().toJson(empByNameOrAge));
                }else {
                    out.print(0);//-->不存在,提示信息
                }
            }catch (Exception e){
                e.printStackTrace();
                out.print(500);//-->服务器错误;
            }
        }
        
    }
}        

当然,没使用到注解的话,这里还要手动配置一下;web.xml

 <!--配置员工servlet-->
    <servlet>
        <servlet-name>empServlet</servlet-name>
        <servlet-class>com.xiaozhi.servlet.EmployeeServlet</servlet-class>
    </servlet>
    <!--servlet映射-->
    <servlet-mapping>
        <servlet-name>empServlet</servlet-name>
        <url-pattern>/do/emp</url-pattern>
    </servlet-mapping>

employee.html员工的列表访问页面;

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>员工列表</title>
    <!--部署jquery-->
    <script src="js/jquery.1.8.3.min.js" type="text/javascript"></script>

    <script type="text/javascript">
        //页面打开时发送请求;这里不传递姓名和性别查询条件;
        $(function () {
            $.get("do/emp", {mark:"empList"}, function (res) {
                //测试获取的数据;
                //console.log(res)
                if(res==500){
                    alert("抱歉,服务器出了点问题");
                }else if(res==0){
                    //弹框提示;
                    alert("抱歉,暂时没有员工!")
                }else {
                    //拼接存入表格;
                    var str2="";
                    for (var i = 0; i < res.length; i++) {
                        str2+="<tr  align='center'>";
                        str2+="<td>"+(i+1)+"</td>";
                        str2+="<td>"+res[i].name+ "</td>";
                        str2+="<td>"+res[i].sex+ "</td>";
                        str2+="<td>"+res[i].dept.name+ "</td>";
                        str2+="<td>"+res[i].user.account+ "</td>";
                        str2+="</tr>";
                    }
                    $("#table").append(str2);
                }

            }, "json");
        });

        //点击搜索时,查询员工;
        function getEmp(){
            //先获取到输入框的值
            var name = $("input[name='name']").val();
            var sex = $("input[name='sex']").val();

                //发送请求;进行查询;注意携带姓名或性别
                $.get("do/emp",{mark:"empList",name:name,sex:sex},function (res){
                    //测试响应的数据;
                    console.log(res)
                    if(res==500){
                        alert("抱歉,服务器出了点问题");
                    }else if(res==0){
                        //清除表格的信息;
                        $("tr:gt(0)").remove();
                        //弹框提示;
                        alert("该员工不存在!!!");
                    }else {
                        //同样地,先清除全部的信息;
                        $("tr:gt(0)").remove();
                        //拼接显示数据;
                        var str4="";
                        for (var i = 0; i < res.length; i++) {
                            str4 += "<tr  align='center'>";
                            str4 += "<td>" + (i+1) + "</td>";
                            str4 += "<td>" + res[i].name + "</td>";
                            str4 += "<td>" + res[i].sex + "</td>";
                            str4 += "<td>" + res[i].dept.name + "</td>";
                            str4 += "<td>" + res[i].user.account + "</td>";
                            str4 += "</tr>";
                        }
                        $("#table").append(str4);
                    }
                },"json");
        }

        //点击全部员工,刷新页面;
        function getAll(){
            location.reload();
        }

    </script>
</head>
<body>
<form style="width: 1080px" id="form">
    <label>
        姓名:<input type="text" name="name" placeholder="请输入要查询的员工姓名:"/>
        性别:<input type="text" name="sex" placeholder="请输入要查询的员工性别:"/>
    </label>
    <input  type="button" value="搜索" onclick="getEmp()"/>
    <!--当然为了能够调回之前的全部员工,再次加载一次这个页面-->
    <input type="button" value="显示全部员工" onclick="getAll()">
</form>
<br/>
<!--显示的表格-->
<table width="100%" border="1" cellspacing="0" id="table">
    <th>编号</th>
    <th>员工姓名</th>
    <th>员工性别</th>
    <th>所属部门</th>
    <th>操作人</th>
</table>
</body>
</html>

配置号tomcat服务器后,Ok启动,看看效果

(1)首次访问时,这时的查询是不带条件的;

Mybatis框架学习笔记(7) --- [动态sql的使用]

所以这边接收到的也是null空值,所以不会去拼接where子句

Mybatis框架学习笔记(7) --- [动态sql的使用]

试试用姓名查询;

Mybatis框架学习笔记(7) --- [动态sql的使用]

(1)这里接收到了姓名,以及空字符串性别; 根据姓名去进行了查询;但是查询时没有拼接性别哦;

Mybatis框架学习笔记(7) --- [动态sql的使用]

这时因为之前在写SQL语句时,在<if>标签内加了条件判断

Mybatis框架学习笔记(7) --- [动态sql的使用]

(3)试试只输入性别进行查询

Mybatis框架学习笔记(7) --- [动态sql的使用]

接收到的姓名为空字符串;则在查询时只根据性别条件进行查询,<where>标签去掉了and关键字
Mybatis框架学习笔记(7) --- [动态sql的使用]

(4)根据姓名和性别查询

Mybatis框架学习笔记(7) --- [动态sql的使用]

当然,姓名和性别都被作为条件进行了查询

Mybatis框架学习笔记(7) --- [动态sql的使用]

(5)那么打开了页面后,不输入查询条件,直接查询呢,这时就会直接查询出所有的员工,因为接收到的姓名和性别的输入框都是空的字符串

Mybatis框架学习笔记(7) --- [动态sql的使用]

由于接收到了空字符串,所以自动去掉了后面的where查询条件

Mybatis框架学习笔记(7) --- [动态sql的使用]


其实,不使用的<where> 标签,也能做到动态SQL的查询;也算是一种奇技淫巧了;
直接在查询条件时拼接一段成立的条件,比如说在where 查询条件后拼接 1=1;防止后面的条件若都不符合就查询输出所有的数据;
那就试试吧;
EmployeeMapper.xml中修改SQL语句;把之前的暂时注释掉;

    <!--根据姓名/性别动态查询员工  奇技淫巧-->
<select id="getEmpByNameOrAge" resultMap="empmap">
    SELECT
    e.`id`,
    e.`name`,
    e.`sex`,
    e.`age`,
    d.`name` dname ,
    u.`account`
    FROM t_employee e
    LEFT JOIN t_dept d ON e.`deptID`=d.`id`
    LEFT JOIN t_user u ON u.`id`=e.`optionId`
    where 1=1
    <if test="name != null &amp; name !='' ">
        and e.name = #{name}
    </if>
    <if test="sex != null &amp; sex !='' ">
        and e.sex= #{sex}
    </if>
</select>

查询试试;
例如仅通过性别为条件进行查询

Mybatis框架学习笔记(7) --- [动态sql的使用]

查询效果还是不错的

Mybatis框架学习笔记(7) --- [动态sql的使用]

2.trim标签

自定义 trim 元素来定制 where 元素
使用trim标签也可以完成where子句的拼接,以及and/or这种关键字的去除
这时prefix="where" 表示可拼接的前缀;prefixOverrides= "and|or"表示若出现这些关键字 根据情况就自动去除掉

OK,那就试试吧,注释掉之前的查询SQL;

<!--使用trim标签 -->
<!--根据姓名/性别动态查询员工-->
<select id="getEmpByNameOrAge" resultMap="empmap">
    SELECT
    e.`id`,
    e.`name`,
    e.`age`,
    e.`sex`,
    d.`name` dname ,
    u.`account`
    FROM t_employee e
    LEFT JOIN t_dept d ON e.`deptID`=d.`id`
    LEFT JOIN t_user u ON u.`id`=e.`optionId`
    <trim prefix="where" prefixOverrides="and|or">
        <if test="name != null &amp; name !='' ">
           and e.name = #{name}
        </if>
        <if test="sex != null &amp; sex !='' ">
            and e.sex= #{sex}
        </if>
    </trim>
</select>

可以试试查询的效果;
例如我要根据姓名进行查询

Mybatis框架学习笔记(7) --- [动态sql的使用]

诶,姓名条件之前的and被自动去掉了,且添加了前缀where
Mybatis框架学习笔记(7) --- [动态sql的使用]

3.choose标签

使用choose标签的好处就在于它可以添加默认的条件;
choose标签 内使用whenotherwise标签,
when标签内会进行条件判断,若符合就执行when标签下的条件 ;若不符合条件就去执行otherwise标签中提供的条件;

还是用刚才的根据姓名和性别进行查询的案例;注释掉之前的;
EmployeeMapper.xml文件中;
我让它默认查询姓名为 4号,性别为的员工

<!--使用choose 实现默认查询-->
<select id="getEmpByNameOrAge" resultMap="empmap">
    SELECT
    e.`id`,
    e.`name`,
    e.`age`,
    e.`sex`,
    d.`name` dname ,
    u.`account`
    FROM t_employee e
    LEFT JOIN t_dept d ON e.`deptID`=d.`id`
    LEFT JOIN t_user u ON u.`id`=e.`optionId`
    <trim prefix="where" prefixOverrides="and|or">
     <choose>
         <when test="name != null &amp; name !='' ">
             and e.name = #{name}
         </when>
         <otherwise>
             and e.name ='4号'
         </otherwise>
     </choose>
     <choose>
         <when test="sex != null &amp; sex !=''">
             and  e.sex = #{sex}
         </when>
         <otherwise>
             and  e.sex = '男'
         </otherwise>
     </choose>
    </trim>
</select>

我去首次访问页面时他就按照默认的查询条件去查了
Mybatis框架学习笔记(7) --- [动态sql的使用]

这边接收到的是null空值,也就按默认的条件进行了查询
Mybatis框架学习笔记(7) --- [动态sql的使用]

4.set标签

使用<set> 标签,可自动根据情况添加set关键字;且自动地删掉set语句中的多余,

可能在单个的修改场景中体会不到这种好处;
举个案例;几个人要同时对一个表进行修改,但是由于权限不同,每个人负责修改的字段不是一样的;
也就比如说我有个用户的数据表,A负责修改用户表中的姓名和性别;B负责修改用户表中的地址和电话;C负责修改用户表中的用户表中的备注信息;
这时候,我们让ABC三个人分别写三个修改语句也行啊;但是我就想让他们走一条SQL呢;还要互相不影响别人的修改;我们都知道在修改时set 条件=XXX ,条件2 =XXX,条件后面会跟随一个逗号,那么ABC三个人在一条SQL上操作时,A只需要对姓名和性别修改,他不需要去修改别的信息;那么多余的逗号怎么去掉呢? 这时就得请出<set> 标签元素了

话不多说,案例开始
EmployeeMapper员工类的持久层接口定义一个修改方法

//根据Id更新员工信息;
void toUpdateEmp(Employee employee);

在对应的EmployeeMapper.xmlSQL映射文件中编写对应的SQL;

我这里的修改主要根据Id修改员工的姓名,性别,年龄;部门号,操作人的Id

<!--根据Id修改员工信息  直接用set标签-->
<update id="toUpdateEmp" parameterType="employee">
    update t_employee
    <set>
        <if test="name !=null &amp; name !=''">
            name =#{name},
        </if>
        <if test="sex !=null &amp; sex!=''">
            sex =#{sex},
        </if>
        <if test="age !=null &amp; age!=''">
            age =#{age},
        </if>
        <if test="dept.id !=null">
            deptID=#{dept.id},
        </if>
        <if test="user.id !=null">
            optionId=#{user.id}
        </if>
    </set>
    where id=#{id}
</update>

先去数据库看看原数据;

测试;
在测试包下的TestEmployee类中进行测试

//测试修改员工实现; 直接用了set标签
@Test
public void updateEmp() {
    //调用工具类;
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //获取代理对象;
    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    
    Employee employee = new Employee();

    //修改2号员工的信息;
    employee.setId(2);
    employee.setName("修改后的2号员工名字");
    //这里不修改性别;也会传递空值

    //修改2号员工的年龄;
    employee.setAge(100);
    //修改部门的Id
    Dept dept = new Dept();
    dept.setId(2);
    employee.setDept(dept);
    //这里不修改操作人的Id,也就不设置ID值了;
    User user = new User();
    employee.setUser(user);
    //执行修改方法;
    mapper.toUpdateEmp(employee);

    //注意需要提交事务;
    sqlSession.commit();
    //关闭sqlSession;
    sqlSession.close();
}

看看日志信息;
这里就没有把性别的修改和操作人Id的修改拼接进去;
Mybatis框架学习笔记(7) --- [动态sql的使用]

Mybatis框架学习笔记(7) --- [动态sql的使用]

实际上,这里也可以用自定义的trim标签添加set,以及删除多余的逗号

prefix前缀来添加set关键字;suffixOverrides 尾部覆盖 来删除多余的逗号,
Ok,把之前的注释掉;

<!--根据Id修改员工信息 用自定义的trim标签-->
<update id="toUpdateEmp" parameterType="employee">
    update t_employee
    <trim prefix="set" suffixOverrides=",">
        <if test="name !=null &amp; name !=''">
            name =#{name},
        </if>
        <if test="sex !=null &amp; sex!=''">
            sex =#{sex},
        </if>
        <if test="age !=null &amp; age!=''">
            age =#{age},
        </if>
        <if test="dept.id !=null">
            deptID=#{dept.id},
        </if>
        <if test="user.id !=null">
            optionId=#{user.id}
        </if>
    </trim>
    where id=#{id}
</update>

测试使用

//测试修改员工实现; 用自定义的trim标签;
@Test
public void updateEmp1() {
    //调用工具类;
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //获取代理对象;
    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    
    Employee employee = new Employee();
    //修改3号员工的信息;
    employee.setId(3);
    employee.setName("修改后的三号员工名字");
    //这里不修改性别;也会传递空值
    //修改不修改年龄,传递空值;
    //修改部门的Id
    Dept dept = new Dept();
    dept.setId(1);
    employee.setDept(dept);
    //这里修改操作人的Id,;
    User user = new User();
    user.setId(35);
    employee.setUser(user);
    //执行修改方法;
    mapper.toUpdateEmp(employee);
    //注意需要提交事务;
    sqlSession.commit();
    //关闭sqlSession;
    sqlSession.close();
}

查看修改时的日志信息;没有拼接性别和年龄

Mybatis框架学习笔记(7) --- [动态sql的使用]

Mybatis框架学习笔记(7) --- [动态sql的使用]

5.Foreach 标签

foreach这个关键字在很多语法中都很常见,表示循环遍历;
主要属性有itemindexcollectionopenseparatorclose

  • item 表示集合中每一个元素进行迭代时的别名,
  • index 指定一个名字,用于表示在迭代过程中,每次迭代到的位置,
  • open 表示该语句以什么开始,
  • separator 表示在每次进行迭代之间以什么符号作为分隔符,
  • close 表示以什么结束,
  • 在使用 foreach 的时候最关键的也是最容易出错的就是 collection
    属性,该属性是必须指定的
    • 传入的是单参数且参数类型是一个 List 的时候,collection 属
      性值为list
    • 传入的是单参数且参数类型是一个 array 数组的时候,
      collection 的属性值为array
  • 当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。

OK,这里就以一个案例进行操作;

比如说,我现在有个需求,要根据几个年龄范围去查询员工;但是这几个年龄是动态的;

(1)在EmployeeMapper员工类的持久层接口定义方法;
首先用集合作为容器;

//查询多个年龄范围的员工;  参数用集合容器;
List<Employee> getEmpByAgeList(List<Integer> ageList);

EmployeeMapper.xml中编写对应的SQL;

 <!--根据年龄列表查询员工列表   集合作为容器-->
<select id="getEmpByAgeList" resultType="employee">
    select * from t_employee where age in
    <foreach collection="list" item="age" open="(" separator="," close=")">
      #{age}
    </foreach>
</select>

测试

//测试批量年龄查询;  集合作为容器
@Test
public void getEmpByAgeList(){
    //调用工具类;
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //获取代理对象;
    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    //调用方法;
    List<Integer> ageList = new ArrayList<>();
    ageList.add(20);
    ageList.add(21);
    ageList.add(22);
    List<Employee> empByAgeList = mapper.getEmpByAgeList(ageList);
    empByAgeList.forEach(System.out::println);
    //关闭sqlSession;
    sqlSession.close();
}

测试结果;

Mybatis框架学习笔记(7) --- [动态sql的使用]

(2)用数组作为参数容器也可行;
EmployeeMapper员工类持久层接口定义方法

//查询多个年龄范围的员工;  参数用数组容器;
List<Employee> getEmpByAgeArray(Integer[] ageArray);

EmployeeMapper.xml中配置文件

 <!--根据年龄列表查询员工列表   数组作为容器-->
<select id="getEmpByAgeArray" resultType="employee">
    select * from t_employee where age in
    <foreach collection="array" item="age" open="(" separator="," close=")">
        #{age}
    </foreach>
</select>

测试

//测试批量年龄查询;  集合作为容器
@Test
public void getEmpByAgeArray(){
    //调用工具类;
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //获取代理对象;
    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    //调用方法;
    Integer[] ageArray = {20,22,24};
    List<Employee> empByAgeList = mapper.getEmpByAgeArray(ageArray);
    empByAgeList.forEach(System.out::println);

    //关闭sqlSession;
    sqlSession.close();
}

测试结果

Mybatis框架学习笔记(7) --- [动态sql的使用]

6.需要记住的几个转义符号;在xml文件中使用

正常书写 mybatis 会报错,需要对这些符号进行转义。

  • < 转义后 &lt;
  • > 转义后 &gt;
  • " 转义后 &quot;
  • 转义后 &apos;
  • & 转义后 &amp;

若不想使用这样的转义符号;那就用<![CDATA[]]>包括这些符号来使用

<![CDATA[ ]]>是 XML 语法。在 CDATA 内部的所有内容都会被解析器忽略。

例如,要查询年龄大于某个年龄段的员工;
EmployeeMapper中定义方法

 //根据年龄大于某个范围进行查询;
List<Employee> getEmpByExceedAge(Integer age);

EmployeeMapper.xml中编写对应的SQL

 <!--根据年龄大于某个范围进行查询;-->
 <select id="getEmpByExceedAge" resultType="employee">
    select  * from t_employee where age<![CDATA[ > ]]> #{age};
 </select>
//测试大于某个年龄的员工;
@Test
public  void getEmpByExceedAge(){
    //调用工具类;
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //获取代理对象;
    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    //调用方法; 查询年龄大于20岁的员工;
    List<Employee> empByExceedAge = mapper.getEmpByExceedAge(20);
    //控制台输出;
    empByExceedAge.forEach(System.out::println);
    //关闭sqlSession;
    sqlSession.close();
}

Mybatis框架学习笔记(7) --- [动态sql的使用]

上一篇:2021/12/23 C++学习记录


下一篇:go 执行文件,参数解析,并且运用