0、综合案例功能介绍

• 
• 以上是在综合案例要实现的功能。除了对数据的增删改查功能之外，还有一些复杂的功能，如批量删除，分页查询，条件查询等功能
  • 批量删除功能：每条数据前都有复选框，当我们选中多条数据并点击批量删除按钮后，会发送请求到后端并删除数据库指定的多条数据
  • 分页查询功能：当数据库中有很多数据的时候，我们不可能将所有的数据展示在一页里，这个时候就需要分页展示数据
  • 条件查询功能：数据库量大的时候，我们需要精确的查询一些想看到的数据，这个时候就需要通过条件查询

1、查询所有功能

• 整体流程如下图所示
  • 

1.1、后端实现

1.1.1、dao方法实现

• 在com.coolman.mapper.BrandMapper接口中定义抽象方法，使用@Select注解编写SQL语句

  • // 查询所有品牌
        @Select("select * from brand.tb_brand order by ordered")
        List<Brand> selectAllBrands();
    

• 由于表中有些字段名和实体类中的属性名没有对应，所以要进行相关配置

  • 方法1：在SQL语句中取别名

    • // 查询所有品牌
          @Select("select id, brand_name as brandName, company_name as companyName, ordered, description, status from brand.tb_brand order by ordered")
          List<Brand> selectAllBrands();
      

  • 方法2：在BrandMapper.xml映射配置文件中定义结果映射，同时在使用注解开发的条件下在Mapper接口中使用ResultMap注解

    • <resultMap id="brandResultMap" type="Brand">
              <result property="brandName" column="brand_name" />
              <result property="companyName" column="company_name" />
          </resultMap>
      

    • //查询所有品牌
          @Select("select * from brand.tb_brand order by ordered")
          @ResultMap("brandResultMap")
          List<Brand> selectAllBrands();
      

  • 方法3：在MyBatis配置文件中设置并开启驼峰自动命名映射

    • <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
      </settings>
      

1.1.2、service实现

• // 查询所有品牌
      public List<Brand> selectAllBrands() {
          SqlSession sqlSession = MyBatisUtils.getSqlSession();
  
          BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
  
          List<Brand> brands = mapper.selectAllBrands();
  
          sqlSession.close();
          return brands;
      }
  

1.1.3、Servlet实现

• package com.coolman.web.servlet;
  
  import com.alibaba.fastjson.JSON;
  import com.coolman.pojo.Brand;
  import com.coolman.service.BrandService;
  
  import javax.servlet.ServletException;
  import javax.servlet.annotation.WebServlet;
  import javax.servlet.http.HttpServlet;
  import javax.servlet.http.HttpServletRequest;
  import javax.servlet.http.HttpServletResponse;
  import java.io.IOException;
  import java.util.List;
  
  @WebServlet("/selectAllBrandsServlet")
  public class SelectAllBrandsServlet extends HttpServlet {
      protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          doGet(request, response);
      }
  
      protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          // 在这里处理请求
  
          // 调用服务，查询所有
          List<Brand> brands = new BrandService().selectAllBrands();
  //        System.out.println(brands);
  
          // 将List对象转换为JSON字符串，传递给前端
          String brandsJson = JSON.toJSONString(brands);
  
          // 解决中文乱码问题，声明字符串为JSON字符串
          response.setContentType("application/json;charset=utf-8");
  
          // 返回JSON字符串
          response.getWriter().print(brandsJson);
      }
  }
  

1.2、前端实现

• created() {
              axios.get("selectAllBrandsServlet").then( response => {
                  // window.alert("正在请求数据");
                  // console.log(response.data);
                  this.tableData = response.data;
              });
          },
  

2、添加功能

• 整体流程如图所示
  • 

2.1、后端实现

2.1.1、dao方法实现

• // 添加品牌
      @Insert("insert into brand.tb_brand values(null, #{brandName}, #{companyName}, #{ordered}, #{description}, #{status})")
      void addBrand(Brand brand);
  

2.1.2、service方法实现

• // 添加品牌
      public void addBrand(Brand brand) {
          SqlSession sqlSession = MyBatisUtils.getSqlSession();
  
          BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
  
          mapper.addBrand(brand);
  
          sqlSession.commit();
  
          sqlSession.close();
      }
  

2.1.3、servlet方法实现

• package com.coolman.web.servlet;
  
  import com.alibaba.fastjson.JSON;
  import com.coolman.pojo.Brand;
  import com.coolman.service.BrandService;
  
  import javax.servlet.ServletException;
  import javax.servlet.ServletInputStream;
  import javax.servlet.ServletOutputStream;
  import javax.servlet.annotation.WebServlet;
  import javax.servlet.http.HttpServlet;
  import javax.servlet.http.HttpServletRequest;
  import javax.servlet.http.HttpServletResponse;
  import java.io.IOException;
  import java.io.PrintStream;
  
  @WebServlet("/addBrandServlet")
  public class AddBrandServlet extends HttpServlet {
      protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          doGet(request, response);
      }
  
      protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          // 在这里处理请求
  
          // 获取ajax请求返回的json字符串
          // 获取请求体中的输入流
          ServletInputStream inputStream = request.getInputStream();
          // 调用JSON的api将返回的json字符串转换为实体类对象
          Brand brand = JSON.parseObject(inputStream, Brand.class);
  
          // 调用服务保存信息
          new BrandService().addBrand(brand);
  
          response.getWriter().print("ok");
      }
  }
  

2.2、前端实现

• 在Vue对象中的方法属性中添加一个功能

  • // 添加数据
                addBrand(){
                    console.log(this.brand);
                    axios.post("addBrandServlet", this.brand).then(response => {
                        if (response.data == "ok") {
                            this.dialogVisible = false;
                            window.location.href = "brand.html";
                        } else {
                            window.alert("添加失败！");
                        }
                    })
                },
    

3、删除功能

3.1、后端实现

3.1.1、dao层实现

• // 删除品牌
      @Delete("delete from brand.tb_brand where id = #{id}")
      void deleteBrand(int id);
  

3.1.2、service层实现

• // 删除品牌
      public void deleteBrand(int id) {
          SqlSession sqlSession = MyBatisUtils.getSqlSession();
  
          BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
  
          mapper.deleteBrand(id);
  
          sqlSession.commit();
  
          sqlSession.close();
      }
  

3.1.3、servlet层实现

• package com.coolman.web.servlet;
  
  import com.coolman.service.BrandService;
  
  import javax.servlet.ServletException;
  import javax.servlet.annotation.WebServlet;
  import javax.servlet.http.HttpServlet;
  import javax.servlet.http.HttpServletRequest;
  import javax.servlet.http.HttpServletResponse;
  import java.io.IOException;
  
  @WebServlet("/deleteBrandServlet")
  public class DeleteBrandServlet extends HttpServlet {
      protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          doGet(request, response);
      }
  
      protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          // 在这里处理请求
  
          // 获取返回的id
          String idStr = request.getParameter("id");
          int id = Integer.parseInt(idStr);
  
          // 调用服务，删除该品牌
          new BrandService().deleteBrand(id);
  
          // 返回成功删除信息
          response.getWriter().print("ok");
      }
  }
  

3.2、前端实现

• 在Vue对象的method属性中添加deleteBrandById方法，同时在html标签中的点击事件中，传递一个参数回来到方法中

  • <el-table-column align="center" label="操作">
                    <template slot-scope="scope">
                        <el-button type="primary" @click="">修改</el-button>
                        <el-button type="danger" @click="deleteBrandById(scope.row.id)">删除</el-button>
                    </template>
                </el-table-column>
    

  • // 删除数据
                deleteBrandById(id){
                    // 发送ajax请求
                    axios.get("deleteBrandServlet?id=" + id).then( response => {
                        if (response.data == "ok") {
                            // 删除成功，跳转回brand.html
                            window.location.href = "brand.html";
                        } else {
                            window.alert("删除失败");
                        }
                    });
                },
    

4、修改功能

4.1、后端实现

4.1.1、dao层实现

• // 修改品牌
      // 回显功能，根据id查询品牌信息
      @Select("select * from brand.tb_brand where id = #{id}")
      Brand selectBrandById(int id);
      // 修改功能
      @Update("update brand.tb_brand set brand_name = #{brandName}, company_name = #{companyName}, ordered = #{ordered}, " +
              "description = #{description},status = #{status} where id = #{id}")
      void updateBrandById(Brand brand);
  

4.1.2、service层实现

•     // 修改品牌
      // 回显功能
      public Brand selectBrandById(int id) {
          SqlSession sqlSession = MyBatisUtils.getSqlSession();
  
          BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
  
          Brand brand = mapper.selectBrandById(id);
  
          sqlSession.close();
  
          return brand;
      }
      // 修改功能
      public void updateBrandById(Brand brand) {
          SqlSession sqlSession = MyBatisUtils.getSqlSession();
  
          sqlSession.getMapper(BrandMapper.class).updateBrandById(brand);
  
          sqlSession.commit();
          sqlSession.close();
      }
  

4.1.3、servlet层实现

• 回显功能

  • package com.coolman.web.servlet;
    
    import com.alibaba.fastjson.JSON;
    import com.coolman.pojo.Brand;
    import com.coolman.service.BrandService;
    
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    
    @WebServlet("/selectBrandByIdServlet")
    public class SelectBrandByIdServlet extends HttpServlet {
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doGet(request, response);
        }
    
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // 在这里处理请求
    
            // 接收前端返回的id
            String idStr = request.getParameter("id");
            int id = Integer.parseInt(idStr);
    
            // 调用服务，查询品牌信息
            Brand brand = new BrandService().selectBrandById(id);
    
            // 将Java对象转换成json数据，返回给前端
            String jsonString = JSON.toJSONString(brand);
    
            // 向前端声明返回的数据是json数据，同时更改编码格式防止编码错误
            response.setContentType("application/json;charset=utf-8");
    
    //        System.out.println(jsonString);
            response.getWriter().print(jsonString);
        }
    }
    

• 修改功能

  • package com.coolman.web.servlet;
    
    import com.alibaba.fastjson.JSON;
    import com.coolman.pojo.Brand;
    import com.coolman.service.BrandService;
    
    import javax.servlet.ServletException;
    import javax.servlet.ServletInputStream;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    
    @WebServlet("/updateBrandByIdServlet")
    public class UpdateBrandByIdServlet extends HttpServlet {
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doGet(request, response);
        }
    
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // 在这里处理请求
            // 接收前端返回的json数据
            // 得到输入流
            ServletInputStream inputStream = request.getInputStream();
            // 待用JSON的API转换成JAVA对象
            Brand brand = JSON.parseObject(inputStream, Brand.class);
    
            // 调用服务，修改数据
            new BrandService().updateBrandById(brand);
    
            // 返回修改成功信息给前端
            response.getWriter().print("ok");
        }
    }
    

4.2、前端实现

• //修改数据
              // 点击修改按钮后，为了增强用户体验，添加一个数据回显功能
              selectBrandById(id){
                  this.dialogVisibleUpdate = true
                  // 发送ajax请求
                  axios.get("selectBrandByIdServlet?id=" + id).then(response => {
                   // 接收后端返回的json数据
                   this.brand = response.data;
                   // el-switch开关的默认值必须类型也要匹配~
                   this.brand.status = this.brand.status.toString();
                   console.log(this.brand);
                  });
              },
              // 执行修改语句
              updateBrandById(id){
                  // 发送ajax请求
                  axios.post("updateBrandByIdServlet", this.brand).then(response => {
                     if (response.data == "ok") {
                         // 跳转到brand.html页面
                         window.location.href = "brand.html";
                     }else {
                         window.alert("修改失败！")
                     }
                  });
              },
  

5、servlet优化

5.1、背景

• Web层的Servlet个数太多，不利于管理和编写
  • 模块的每一个功能都需要定义一个servlet，当模块的数量多时，就很容易造成servlet泛滥。

5.2、解决方法

• 一个模块只定义一个servlet，模块中的每一个功能只需要在servlet中定义对应的方法，如下代码所示

• @WebServlet("/brand/*")
  public class BrandServlet {
      //查询所有
  	public void selectAll(...) {}
      
      //添加数据
      public void add(...) {}
      
       //修改数据
      public void update(...) {}
      
      //删除删除
      public void delete(...) {}
  }
  

5.3、思路

• 当发送请求至servlet，tomcat会自动调用service()方法，源码如下所示
  • 
  • 我们一般在自定义的servlet中会重写doGet()和doPost()方法，当我们访问该servlet的时候会根据请求方式将请求分发给doGet()或者doPost()方法
• 那么可以仿照这样的请求分发的思想，在service()方法中根据具体的操作调用对应的方法
  • 如：查询所有就调用selectAll()方法，添加企业信息就调用add()方法

5.4、具体实现

• 定义一个通用的servlet类，在定义其他的servlet是不需要继承HttpServlet，而继承我们定义的BaseServlet，在BaseServlet中调用具体servlet（如BrandServlet）中的对应方法，如下所示

  • BaseServlet

    • public class BaseServlet extends HttpServlet {
          @Override
          protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
              //进行请求的分发
          }
      }
      

  • BrandServlet

    • @WebServlet("/brand/*")
      public class BrandServlet extends BaseServlet {
          //用户实现分页查询
      	public void selectAll(...) {} 
          
          //添加企业信息
          public void add(...) {}
          
          //修改企业信息
          public void update(...) {}
          
          //删除企业信息
          public void delete(...) {}
      }
      

• 那么如何在BaseServlet中调用对应的方法，进行请求分发呢？

  • 可以规定在发送请求的时候，请求资源的二级路径（/brandServlet/selectAll）和需要调用的方法名相同，如下所示

    • 查询所有数据的路径以后就需要写成： http://localhost:8080/brand-case/brandServlet/selectAll
    • 添加数据的路径以后就需要写成： http://localhost:8080/brand-case/brandServlet/add
    • 修改数据的路径以后就需要写成： http://localhost:8080/brand-case/brandServlet/update
    • 删除数据的路径以后就需要写成： http://localhost:8080/brand-case/brandServlet/delete

  • 或者给在路径中添加一个参数，值为对应的方法名称，如下所示

    • 查询所有数据的路径以后就需要写成： http://localhost:8080/brand-case/brandServlet?action=selectAll
    • …

• 这样的话，在BaseServlet中就需要获取到资源的二级路径作为方法名或者路径中的action参数的值，然后调用该方法

5.4.1、后端代码

• BaseServlet

  • package com.coolman.web.servlet;
    
    import cn.hutool.core.util.StrUtil;
    import com.coolman.web.servlet.BrandServlet;
    
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    
    public class BaseServlet extends HttpServlet {
    
        @Override
        // tomcat中的servlet每次被调用都会执行service方法
        protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // 在这里分发请求
            // 1. 获取方法名称
            // 1.1 如果请求的URL格式为 .../brandServlet?action=selectAll
    
            // 那么获取方法名称则要简单很多
    //        String methodName = request.getParameter("action");     // 但是在前端的ajax请求中就需要多写一些单词
    
            // 1.2 如果请求的URL格式为 .../brandServlet/selectAll
            // 使用工具类，获取到最后一个'/'号后面的字符串
            // 1.2.1 获取请求的URI
            String requestURI = request.getRequestURI();
            // 1.2.2 使用工具类，获取到最后一个'/'号后面的字符串
            String methodName = StrUtil.subAfter(requestURI, "/", true);
    
            // 2. 获取方法名称后调用方法
            // 方式1：
    //        if ("selectAll".equals(methodName)) {
    //            selectAll(request, response);
    //        } else if ("add".equals(methodName)) {
    //            add(request, response);
    //        } else if ("delete".equals(methodName)) {
    //            delete(request, response);
    //        } else if ("update".equals(methodName)) {
    //            update(request, response);
    //        } //else if ...
    
            // 方式2: 方式1在当功能多的时候就会显地条件判断很冗余
            //  可以利用反射的方法,获取到方法,然后调用对应的方法即可
            try {
                Method method = this.getClass().getDeclaredMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
                // 再让方法对象执行
                // 暴力反射获取字节码对象的时候需要去除权限(在同一个包下,protected是可以访问的)
    //            method.setAccessible(true);
                method.invoke(this, request, response);
            } catch (Exception e) {
                e.printStackTrace();
            }
    //        catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
    //            e.printStackTrace();
    //        }
    
        }
    }
    

• BrandServlet

  • package com.coolman.web.servlet;
    
    import com.alibaba.fastjson.JSON;
    import com.coolman.pojo.Brand;
    import com.coolman.service.BrandService;
    
    import javax.servlet.ServletException;
    import javax.servlet.ServletInputStream;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.List;
    
    @WebServlet("/brand/*") // 不带action
    //@WebServlet("/brand") // 带action
    public class BrandServlet extends BaseServlet {
        // 查询所有品牌
        protected void selectAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // 调用服务，查询所有
            List<Brand> brands = new BrandService().selectAllBrands();
    //        System.out.println(brands);
    
            // 将List对象转换为JSON字符串，传递给前端
            String brandsJson = JSON.toJSONString(brands);
    
            // 解决中文乱码问题，声明字符串为JSON字符串
            response.setContentType("application/json;charset=utf-8");
    
            // 返回JSON字符串
            response.getWriter().print(brandsJson);
        }
    
        // 添加品牌
        protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // 获取ajax请求返回的json字符串
            // 获取请求体中的输入流
            ServletInputStream inputStream = request.getInputStream();
            // 调用JSON的api将返回的json字符串转换为实体类对象
            Brand brand = JSON.parseObject(inputStream, Brand.class);
    
            // 调用服务保存信息
            new BrandService().addBrand(brand);
    
            response.getWriter().print("ok");
        }
    
        // 删除品牌
        protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // 获取返回的id
            String idStr = request.getParameter("id");
            int id = Integer.parseInt(idStr);
    
            // 调用服务，删除该品牌
            new BrandService().deleteBrand(id);
    
            // 返回成功删除信息
            response.getWriter().print("ok");
        }
    
        // 根据id查询品牌
        protected void selectBrandById(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // 接收前端返回的id
            String idStr = request.getParameter("id");
            int id = Integer.parseInt(idStr);
    
            // 调用服务，查询品牌信息
            Brand brand = new BrandService().selectBrandById(id);
    
            // 将Java对象转换成json数据，返回给前端
            String jsonString = JSON.toJSONString(brand);
    
            // 向前端声明返回的数据是json数据，同时更改编码格式防止编码错误
            response.setContentType("application/json;charset=utf-8");
    
    //        System.out.println(jsonString);
            response.getWriter().print(jsonString);
        }
    
        // 修改品牌信息
        protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // 接收前端返回的json数据
            // 得到输入流
            ServletInputStream inputStream = request.getInputStream();
            // 待用JSON的API转换成JAVA对象
            Brand brand = JSON.parseObject(inputStream, Brand.class);
    
            // 调用服务，修改数据
            new BrandService().updateBrandById(brand);
    
            // 返回修改成功信息给前端
            response.getWriter().print("ok");
        }
    
    }
    

注意事项

• BaseServlet和BrandServlet要放在同一个包下，否则因为BrandServlet中的方法全都是protected修饰的方法，必须要设置取出权限才可调用
  • method.setAccessible(true);

7、批量删除

7.1、后端实现

7.1.1、dao层实现

• BrandMapper.java

  • // 批量删除
        void deleteByIds(@Param("ids") int[] ids);
    

• BrandMapper.xml

  • <delete id="deleteByIds">
            delete from brand.tb_brand where id in
            <foreach collection="ids" separator="," open="(" close=")" item="id">
                #{id}
            </foreach>
        </delete>
    

7.1.2、service层实现

•     // 批量删除功能
      public void deleteByIds(int[] ids) {
          SqlSession sqlSession = MyBatisUtils.getSqlSession(true);
          BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
  
          mapper.deleteByIds(ids);
  
          sqlSession.close();
      }
  

7.1.3、web层servlet实现

•     // 批量删除
      protected void deleteByIds(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          // 接收前端返回的id数组
          ServletInputStream inputStream = request.getInputStream();
          int[] ids = JSON.parseObject(inputStream, int[].class);
  
          // 调用服务
          new BrandService().deleteByIds(ids);
  
          // 返回成功信息
          response.getWriter().print("ok");
      }
  

7.2、前端实现

• body中的标签绑定好事件后，执行事件的方法

  •        // 批量删除数据
           deleteBatch() {
               this.$confirm("确定要删除吗?","提示", {
                   confirmButtonText: "确定",
                   cancelButtonText: "取消",
                   type: "warning"
               }).then( () => {
                   // 点击确定按钮后要执行的操作
                   // this.$message({
                   //     message: "删除成功!",
                   //     type: "success"
                   // });
                   let ids = [];
                   for (let i = 0; i < this.multipleSelection.length; i++) {
                       // ids[i] = this.multipleSelection[i].id;
                       ids.push(this.multipleSelection[i].id);
                   }
                   console.log(ids);
                   axios.post("brand/deleteByIds", ids).then( response => {
                       if (response.data == "ok") {
                           window.location.href = "brand.html";
                       } else {
                           this.$message({
                               message: "删除失败!",
                               type: "error"
                           });
                       }
                   });
               });
           },
    

8、分页查询

8.1、后端实现

8.1.1、dao层实现

• // 分页查询
      //select * from ... limit 起始索引, 每页条数
  //    select * from tb_brand limit 0, 5;  // 第一页
  //
  //    select * from tb_brand limit 5, 5;  // 第二页
  //
  //    select * from tb_brand limit 10, 5; // 第三页
  
      //select * from ... limit (起始索引 - 1) * 每页条数, 每页条数
      @Select("select * from brand.tb_brand limit #{currentPage}, #{pageSize}")
      List<Brand> selectBrandByPage(@Param("currentPage") int currentPage, @Param("pageSize") int pageSize);
      // 页码左边需要的总条数
      @Select("select count(*) from brand.tb_brand")
      Integer selectSumCount();
  

8.1.2、service层实现

•     // 分页功能
      public PageBean<Brand> selectBrandByPage(int currentPage, int pageSize) {
          //select * from ... limit (起始索引 - 1) * 每页条数, 每页条数
          currentPage = (currentPage - 1) * pageSize;
  
          SqlSession sqlSession = MyBatisUtils.getSqlSession();
          BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
  
          List<Brand> brands = mapper.selectBrandByPage(currentPage, pageSize);
          int totalCount = mapper.selectSumCount();
  
          sqlSession.close();
  
          return new PageBean<Brand>(totalCount, brands);
      }
  

8.1.3、web层servlet实现

•     // 处理分页请求
      protected void selectBrandByPage(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          // 接收前端返回的页码数
          // pageSize
          int pageSize = Integer.parseInt(request.getParameter("pageSize"));
  
          // currentPage
          int currentPage = Integer.parseInt(request.getParameter("currentPage"));
  
          // 调用服务
          PageBean<Brand> pageBean = new BrandService().selectBrandByPage(currentPage, pageSize);
  
          // 返回数据
          String string = JSON.toJSONString(pageBean);
          response.setContentType("application/json;charset=utf-8");
          response.getWriter().print(string);
      }
  

8.2、前端实现

• created()函数

  • created() {
                axios.get("brand/selectBrandByPage?currentPage=" + this.currentPage + "&pageSize=" + this.pageSize).then( response => {
                    // window.alert("正在请求数据");
                    // console.log(response.data);
                    this.totalCount = response.data.totalCount;
                    this.tableData = response.data.list;
                });
            }
    

• handleSizeChange(val)函数(每一页的数据量)

  • handleSizeChange(val) {
                    console.log(`每页 ${val} 条`);
                    this.pageSize = val;
                    axios.get("brand/selectBrandByPage?currentPage=" + this.currentPage + "&pageSize=" + this.pageSize).then( response => {
                        // window.alert("正在请求数据");
                        // console.log(response.data);
                        this.tableData = response.data.list;
                    });
    
                }
    

• handleCurrentChange(val)函数(当前页码)

  • handleCurrentChange(val) {
                    console.log(`当前页: ${val}`);
                    this.currentPage = val;
                    axios.get("brand/selectBrandByPage?currentPage=" + this.currentPage + "&pageSize=" + this.pageSize).then( response => {
                        // window.alert("正在请求数据");
                        // console.log(response.data);
                        this.tableData = response.data.list;
                    });
                }
    

9、条件查询

9.1、后端实现

9.1.1、dao层实现

• Mapper接口

  • // 多条件查询
        List<Brand> selectBrandByCondition(@Param("brand") Brand brand, @Param("currentPage") int currentPage, @Param("pageSize") int pageSize);
        // 查询结果记录数
        int selectSearchCount(Brand brand);
    

• Mapper映射文件

  • <select id="selectBrandByCondition" resultMap="brandResultMap">
            select * from brand.tb_brand
            <where>
                <!-- 当状态码设置为0或1的时候,很容易出现框架自动转换成false或true的情况;可以看作一个小bug -->
                    <!-- 当传入的是int型的数字 0 时，mybatis会把它当成空字符串
                         status != '', 0 != ''  ==>  '' != ''
                    -->
                <if test="brand.status != null">
                    status = #{brand.status}
                </if>
                <if test="brand.brandName != null and brand.brandName != ''">
                    and brand_name like #{brand.brandName}
                </if>
                <if test="brand.companyName != null and brand.companyName != ''">
                    and company_name like #{brand.companyName}
                </if>
            </where>
            order by ordered limit #{currentPage}, #{pageSize}
        </select>
    
        <select id="selectSearchCount" resultType="java.lang.Integer">
            select count(*) from brand.tb_brand
            <where>
                <if test="status != null">
                    status = #{status}
                </if>
                <if test="brandName != null and brandName != ''">
                    and brand_name like #{brandName}
                </if>
                <if test="companyName != null and companyName != ''">
                    and company_name like #{companyName}
                </if>
            </where>
        </select>
    

9.1.2、service层实现

•     // 多条件查询
      public PageBean<Brand> selectBrandByCondition(Brand brand, int currentPage, int pageSize) {
          SqlSession sqlSession = MyBatisUtils.getSqlSession();
          BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
  
          if (brand.getBrandName() != null && brand.getBrandName().length() > 0) {
              brand.setBrandName("%" + brand.getBrandName() + "%");
          }
          if (brand.getCompanyName() != null && brand.getCompanyName().length() > 0) {
              brand.setCompanyName("%" + brand.getCompanyName() + "%");
          }
  
          currentPage = (currentPage - 1) * pageSize;
          List<Brand> brands = mapper.selectBrandByCondition(brand, currentPage, pageSize);
        	int count = mapper.selectSearchCount(brand);
          sqlSession.close();
        	return new PageBean<>(count, brands);
      }
  

9.1.3、web层servlet实现

•     // 处理搜索请求
      protected void selectBrandByCondition(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          // 接收前端返回的数据
          int currentPage = Integer.parseInt(request.getParameter("currentPage"));
          int pageSize = Integer.parseInt(request.getParameter("pageSize"));
  
          ServletInputStream inputStream = request.getInputStream();
          Brand brand = JSON.parseObject(inputStream, Brand.class);
  
          System.out.println("brand = " + brand);
  
          // 调用服务
          PageBean<Brand> brands = new BrandService().selectBrandByCondition(brand, currentPage, pageSize);
  
          // 返回数据
          String string = JSON.toJSONString(brands);
          response.setContentType("application/json;charset=utf-8");
          response.getWriter().print(string);
      }
  

9.2、前端实现

• <!DOCTYPE html>
  <html lang="en">
  <head>
      <meta charset="UTF-8">
      <title>商品列表</title>
      <style>
          .el-table .warning-row {
              background: oldlace;
          }
  
          .el-table .success-row {
              background: #f0f9eb;
          }
      </style>
  
  </head>
  <body>
  <div id="app">
  
      <!--搜索表单-->
      <el-form :inline="true" v-model="brand" class="demo-form-inline">
  
          <el-form-item label="当前状态">
              <el-select v-model="brand.status" placeholder="当前状态">
                  <el-option label="启用" value="1"></el-option>
                  <el-option label="禁用" value="0"></el-option>
              </el-select>
          </el-form-item>
  
          <el-form-item label="企业名称">
              <el-input v-model="brand.companyName" placeholder="企业名称"></el-input>
          </el-form-item>
  
          <el-form-item label="品牌名称">
              <el-input v-model="brand.brandName" placeholder="品牌名称"></el-input>
          </el-form-item>
  
          <el-form-item>
              <el-button type="primary" @click="onSubmit()">查询</el-button>
          </el-form-item>
      </el-form>
  
      <!--按钮-->
      <el-row>
          <el-button type="danger" plain @click="deleteBatch()">批量删除</el-button>
          <el-button type="primary" plain @click="dialogVisibleAdd = true">新增</el-button>
      </el-row>
  
      <!--添加数据对话框表单-->
      <el-dialog
              title="添加品牌"
              :visible.sync="dialogVisibleAdd"
              width="30%"
              >
  
          <el-form ref="form" :model="brand" label-width="80px">
              <el-form-item label="品牌名称">
                  <el-input v-model="brand.brandName"></el-input>
              </el-form-item>
  
              <el-form-item label="企业名称">
                  <el-input v-model="brand.companyName"></el-input>
              </el-form-item>
  
              <el-form-item label="排序">
                  <el-input v-model="brand.ordered"></el-input>
              </el-form-item>
  
              <el-form-item label="备注">
                  <el-input type="textarea" v-model="brand.description"></el-input>
              </el-form-item>
  
              <el-form-item label="状态">
                  <el-switch v-model="brand.status"
                             active-value="1"
                             inactive-value="0"
                  ></el-switch>
              </el-form-item>
                      <el-form-item>
                <el-button type="primary" @click="addBrand">提交</el-button>
                <el-button @click="dialogVisibleAdd = false">取消</el-button>
            </el-form-item>
        </el-form>
  
    </el-dialog>
  
    <!--修改数据对话框表单-->
    <el-dialog
            title="编辑品牌"
            :visible.sync="dialogVisibleUpdate"
            width="30%"
    >
  
        <el-form ref="form" :model="brand" label-width="80px">
            <el-form-item label="品牌名称">
                <el-input v-model="brand.brandName"></el-input>
            </el-form-item>
  
            <el-form-item label="企业名称">
                <el-input v-model="brand.companyName"></el-input>
            </el-form-item>
  
            <el-form-item label="排序">
                <el-input v-model="brand.ordered"></el-input>
            </el-form-item>
  
            <el-form-item label="备注">
                <el-input type="textarea" v-model="brand.description"></el-input>
            </el-form-item>
  
            <el-form-item label="状态">
                <el-switch
                        v-model="brand.status"
                        active-value="1"
                        inactive-value="0"
                ></el-switch>
  
            </el-form-item>
                    <el-form-item>
                <el-button type="primary" @click="update()">提交</el-button>
                <el-button @click="dialogVisibleUpdate = false">取消</el-button>
            </el-form-item>
        </el-form>
  
    </el-dialog>
  
    <!--表格-->
    <template>
        <el-table
                :data="tableData"
                style="width: 100%"
                :row-class-name="tableRowClassName"
                @selection-change="handleSelectionChange"
        >
            <el-table-column
                    type="selection"
                    width="55">
            </el-table-column>
            <el-table-column
                    type="index"
                    width="50">
            </el-table-column>
  
            <el-table-column
                    prop="brandName"
                    label="品牌名称"
                    align="center"
            >
            </el-table-column>
            <el-table-column
                    prop="companyName"
                    label="企业名称"
                    align="center"
            >
            </el-table-column>
            <el-table-column
                    prop="ordered"
                    align="center"
                    label="排序">
            </el-table-column>
            <el-table-column
                    prop="description"
                    align="center"
                    label="描述">
            </el-table-column>
            <el-table-column
                    prop="status"
                    align="center"
                    label="当前状态">
                <template slot-scope="scope">
                    <el-tag type="success" v-if="scope.row.status=='1'">启用</el-tag>
                    <el-tag type="danger" v-else>禁用</el-tag>
                </template>
            </el-table-column>
  
            <el-table-column align="center" label="操作">
                <template slot-scope="scope">
                    <el-button type="primary" @click="selectBrandById(scope.row.id)">修改</el-button>
                    <el-button type="danger" @click="deleteBrandById(scope.row.id)">删除</el-button>
                </template>
            </el-table-column>
  
        </el-table>
    </template>
  
    <!--分页工具条-->
    <el-pagination
            @size-change="handleSizeChange"
            @current-change="handleCurrentChange"
            :current-page="currentPage"
            :page-sizes="[5, 10, 15, 20]"
            :page-size="pageSize"
            layout="total, sizes, prev, pager, next, jumper"
            :total="totalCount">
    </el-pagination>
      </div>
  
    <script src="js/vue.js"></script>
  
    <script src="./js/axios-0.18.0.js"></script>
  
    <script src="element-ui/lib/index.js"></script>
  
    <link rel="stylesheet" href="element-ui/lib/theme-chalk/index.css">
  
    <script>
    new Vue({
        el: "#app",
        created() {
            this.selectAll();
        },
        data() {
            return {
                // 当前页码
                currentPage: 1,
                // 总数据数
                totalCount: 0,
                // 每一页的数据数量
                pageSize: 5,
  
                // 搜索框
  
                // 添加数据对话框是否展示的标记
                dialogVisibleAdd: false,
                // 修改数据对话框是否展示的标记
                dialogVisibleUpdate: false,
  
                // 品牌模型数据
                brand: {
                    status: '',
                    brandName: '',
                    companyName: '',
                    id:"",
                    ordered:"",
                    description:""
                },
                // 复选框选中数据集合
                multipleSelection: [],
                // 表格数据
                tableData: [],
                // 页码数
            }
        },
        methods: {
            tableRowClassName({row, rowIndex}) {
                if (rowIndex === 1) {
                    return 'warning-row';
                } else if (rowIndex === 3) {
                    return 'success-row';
                }
                return '';
            },
            // 复选框选中后执行的方法
            handleSelectionChange(val) {
                this.multipleSelection = val;
                console.log(this.multipleSelection)
            },
            // 查询方法
            onSubmit() {
                // console.log("当前页码:" + this.currentPage)
                // console.log("每页数量:" + this.pageSize)
                // console.log("搜索部分信息:")
                // console.log(this.brand)
                this.selectAll();
            },
            selectAll() {
                axios.post("brand/selectBrandByCondition?currentPage=" + this.currentPage + "&pageSize=" + this.pageSize,
                    this.brand
                ).then( response => {
                    // window.alert("正在请求数据");
                    console.log(response.data);
                    this.totalCount = response.data.totalCount;
                    this.tableData = response.data.list;
                });
            },
  
            // 添加数据
            addBrand(){
                console.log(this.brand);
                axios.post("brand/add", this.brand).then(response => {
                    if (response.data == "ok") {
                        this.dialogVisible = false;
                        window.location.href = "brand.html";
                    } else {
                        window.alert("添加失败！");
                    }
                })
            },
  
            // 删除数据
            deleteBrandById(id){
                // 发送ajax请求
                axios.get("brand/delete?id=" + id).then( response => {
                    if (response.data == "ok") {
                        // 删除成功，跳转回brand.html
                        window.location.href = "brand.html";
                    } else {
                        window.alert("删除失败");
                    }
                });
            },
  
            //修改数据
            // 点击修改按钮后，为了增强用户体验，添加一个数据回显功能
            selectBrandById(id){
                this.dialogVisibleUpdate = true
                // 发送ajax请求
                axios.get("brand/selectBrandById?id=" + id).then(response => {
                 // 接收后端返回的json数据
                 this.brand = response.data;
                 // el-switch开关的默认值必须类型也要匹配~
                 this.brand.status = this.brand.status.toString();
                 console.log(this.brand);
                });
            },
            // 执行修改语句
            update(){
                // 发送ajax请求
                axios.post("brand/update", this.brand).then(response => {
                   if (response.data == "ok") {
                       // 跳转到brand.html页面
                       window.location.href = "brand.html";
                   }else {
                       window.alert("修改失败！")
                   }
                });
            },
  
            // 批量删除数据
            deleteBatch() {
                this.$confirm("确定要删除吗?","提示", {
                    confirmButtonText: "确定",
                    cancelButtonText: "取消",
                    type: "warning"
                }).then( () => {
                    // 点击确定按钮后要执行的操作
                    // this.$message({
                    //     message: "删除成功!",
                    //     type: "success"
                    // });
                    let ids = [];
                    for (let i = 0; i < this.multipleSelection.length; i++) {
                        // ids[i] = this.multipleSelection[i].id;
                        ids.push(this.multipleSelection[i].id);
                    }
                    console.log(ids);
                    axios.post("brand/deleteByIds", ids).then( response => {
                        if (response.data == "ok") {
                            window.location.href = "brand.html";
                        } else {
                            this.$message({
                                message: "删除失败!",
                                type: "error"
                            });
                        }
                    });
                });
            },
  
            //分页
            handleSizeChange(val) {
                console.log(`每页 ${val} 条`);
                this.pageSize = val;
                this.selectAll();
  
            },
            handleCurrentChange(val) {
                console.log(`当前页: ${val}`);
                this.currentPage = val;
                this.selectAll();
            },
  
        }
    })
        </script>
    </body>
    </html>