Layui-Table增删改查

作者: HeloWxl | 来源:发表于2019-11-07 16:40 被阅读0次

Layui-Table增删改查
mysql的插入语句
MYSQL数据库的增删改查
关于python的list的增查删改
0812 A
增删改
SQL查询结构总结
2018-03-03
函数和增删改查（运用）
表内容的操作

前言

1.使用layui与后端进行交互
2.列表展示数据
3.使用layer弹出层进行添加和修改
4.批量删除数据

这篇文章的重点不是在后端，所以在这里，后端的话，我就不详细的讲解了。我只提供一下返回的数据格式类型。

1.后端

1.1后端调用的接口

@Controller
@RequestMapping("studentApi")
public class StudentController {

  @Resource
  private StudentService studentService;

  //根据ID删除学生信息
  @GetMapping("/deleteByStudentId}")
  @ResponseBody
  public DataResult<Integer> deleteByStudentId(@RequestBody List<Integer> studentId){
    DataResult<Integer> result = new DataResult<>();
    result.setBody(studentService.deleteById(studentId));
    return result;
  }

   //添加学生信息
  @PostMapping("/insertStudent")
  @ResponseBody
  public DataResult<Integer> insertStudent(@RequestBody Student student){
    DataResult<Integer> result = new DataResult<>();
    result.setBody(studentService.insertSelective(student));
    return result;
  }

  //修改学生信息
  @PostMapping("/updateStudent")
  @ResponseBody
  public DataResult<Integer> updateStudent(@RequestBody Student student){
    DataResult<Integer> result = new DataResult<>();
    result.setBody(studentService.updateByPrimaryKeySelective(student));
    return result;
  }

//查询学生信息-分页显示
  @GetMapping("/selectStudentByPage")
  @ResponseBody
  public Map<String,Object> selectStudentByPage(@RequestParam("page") Integer page , @RequestParam("limit") int limit){
    return studentService.getStudentList(page,limit);
  }

}

1.2后端返回的数据格式类型

{
  "msg": "",
  "code": 0,
  "data": [
    {
      "studentId": 1,
      "studentName": "王xxx",
      "studentSno": "131303031",
      "studentAge": "20",
      "studentQq": "756316064",
      "studentEmail": "756316064@qq.com",
      "studentPic": "/images/user.jpg",
      "studentSex": "男",
      "professionId": 1,
      "createTime": "2019-08-23",
      "professionName": "信息管理与信息系统",
      "apartment": "数学与计算机性科学学院",
      "school": "泉州师范学院"
    },
 {
      "studentId": 2,
      "studentName": "花花",
      "studentSno": "121303001",
      "studentAge": "20",
      "studentQq": "56546732",
      "studentEmail": "56546732@qq.com",
      "studentPic": "/images/default.png",
      "studentSex": "男",
      "professionId": 4,
      "createTime": "2019-09-02",
      "professionName": "大数据科学与技术",
      "apartment": "数学与计算机性科学学院",
      "school": "泉州师范学院"
    }
  ],
  "count": 7
}

2.前端

2.1前端界面（JSP）

需要引入layui框架哦

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="ctx" value="${pageContext.request.contextPath}"/>
<html>
<head>
    <title>学生管理</title>
    <link href="${ctx}/resources/plugins/layui/css/layui.css" rel="stylesheet">
    <link rel="icon" href="${ctx}/resources/ico/logo.ico"  type=”image/x-icon”>
    <script>
      var ctx = '${ctx}'
    </script>
</head>
<body>
<div class="layui-row">
    <div class="layui-col-xs12">
        <div class="grid-demo grid-demo-bg1">
            <!-- 模块名 -->
            <blockquote class="layui-elem-quote">学生管理</blockquote>
            <%--            表格--%>
            <div class="center">
                <table id="demo" lay-filter="studentfilter"></table>
            </div>
        </div>
    </div>
</div>


<script src="${ctx}/resources/js/jquery-2.1.4.js" type="application/javascript"></script>
<script src="${ctx}/resources/plugins/layui/layui.js" type="application/javascript"></script>
<script src="${ctx}/resources/js/counselor/counselor_student.js" type="application/javascript"></script>
</body>
</html>

2.2 前端界面(JavaScript)

2.2.1 将数据以表格的方式展现在页面上

layui.use('table', function() {
  var table = layui.table;
table.render({
      id: 'studentTable',
      elem: '#demo'
      ,method:'get'
      , defaultToolbar: []
      , url: ctx + '/studentApi/selectStudentByPage' //数据接口
      , page: true //开启分页
      , cols: [[ //表头
        {field: 'checkbox', type: 'checkbox'}
        , {field: 'number', title: '序号', type: 'numbers'}
        , {field: 'studentSno', title: '学号', width: 120}
        , {field: 'studentName', title: '学生姓名', width: 100}
        , {field: 'studentSex', title: '性别', width: 80}
        , {field: 'studentAge', title: '年龄', width: 80}
        , {field: 'studentEmail', title: '邮箱', width: 200}
        , {field: 'studentQq', title: 'QQ', width: 200}
        , {field: 'createTime', title: '创建时间', width: 120}
      ]]
      , skin: 'line,row' //表格风格
      , even: true
      , limits: [10, 20, 30]
      , limit: 10 //每页默认显示的数量
    });
}

页面如下：

表格数据.png

注意：这里的返回格式需要按照layui官方给定的格式

{
  "code": 0,
  "msg": "",
  "count": 1000,
  "data": [{}, {}]
}

当然，你也是可以自定义格式的

table.render({
  elem: '#demp'
  ,url: ''
  ,response: {
    statusName: 'status' //规定数据状态的字段名称，默认：code
    ,statusCode: 200 //规定成功的状态码，默认：0
    ,msgName: 'hint' //规定状态信息的字段名称，默认：msg
    ,countName: 'total' //规定数据总数的字段名称，默认：count
    ,dataName: 'rows' //规定数据列表的字段名称，默认：data
  } 
  //,…… //其他参数
}); 

然后返回的数据格式:
{
  "status": 200,
  "hint": "",
  "total": 1000,
  "rows": []
}

具体参考：layui数据表格返回数据格式

2.2.2 表格数据的增删改

这里的话，我使用的是layui提供的一个toolbar - 绑定工具条模板,个人觉得还挺好用的。我们只需要在table.render{(toolbar: '#toolbars')}
下面的代码加到JSP中

<!-- 表格标签工具栏 -->
<script type="text/html" id="toolbars">
    <div class="layui-btn-container">
        <div class="layui-btn-group">
            <button type="button" lay-event="add" class="layui-btn layui-btn-normal">
                <i class="layui-icon">&#xe654;</i>增加
            </button>
            <button type="button" lay-event="update" class="layui-btn layui-btn-normal"><i
                    class="layui-icon">&#xe60a;</i>修改
            </button>
            <button type="button" lay-event="delete" class="layui-btn layui-btn-normal"><i
                    class="layui-icon">&#xe640;</i>删除
            </button>
        </div>
    </div>
</script>

下面的代码加到JS中

  table.on('toolbar(studentfilter)', function(obj) {
    var checkStatus = table.checkStatus(obj.config.id);
    var data = checkStatus.data; //获取选中的数据
    switch (obj.event) {
        case 'add':...
       case 'delete':...
      case 'update':...
})

新增

 case 'add':
        layer.open({
          id: 'addStudent',
          type: 1,
          title: ['添加学生'],
          skin: 'layui-layer-molv',
          area: '500px',
          offset: 'auto',
          content: '<div class="layui-row" id="test" style="margin-top:10px;">' +
          '    <div class="layui-col-md10">' +
          '        <form class="layui-form">' +
          '            <div class="layui-form-item">\n' +
          '                <label class="layui-form-label" style="padding-left:-50px;">姓名:</label>\n' +
          '                <div class="layui-input-block">\n' +
          '                    <input type="text" placeholder="请输入学生姓名" name="studentName" id="studentName" class="layui-input">\n' +
          '                </div>\n' +
          '            </div>\n' +
          '            <div class="layui-form-item">\n' +
          '                <label class="layui-form-label" style="padding-left:-50px;">学号:</label>\n' +
          '                <div class="layui-input-block">\n' +
          '                    <input type="text" placeholder="请输入学生学号" name="studentSno" id="studentSno" class="layui-input">\n' +
          '                </div>\n' +
          '            </div>\n' +
          '            <div class="layui-form-item">\n' +
          '                <label class="layui-form-label" style="padding-left:-50px;">性别:</label>\n' +
          '                <div class="layui-input-block">\n' +
          '                    <input type="text" placeholder="请输入学生性别" name="studentSex" id="studentSex" class="layui-input">\n' +
          '                </div>\n' +
          '            </div>\n' +
          '            <div class="layui-form-item">\n' +
          '                <label class="layui-form-label" style="padding-left:-50px;">年龄:</label>\n' +
          '                <div class="layui-input-block">\n' +
          '                    <input type="text" placeholder="请输入学生年龄" name="studentAge" id="studentAge" class="layui-input">\n' +
          '                </div>\n' +
          '            </div>\n' +
          '            <div class="layui-form-item">\n' +
          '                <label class="layui-form-label" style="padding-left:-50px;">邮箱:</label>\n' +
          '                <div class="layui-input-block">\n' +
          '                    <input type="text" placeholder="请输入学生邮箱" name="studentEmail" id="studentEmail" class="layui-input">\n' +
          '                </div>\n' +
          '            </div>\n' +
          '            <div class="layui-form-item">\n' +
          '                <label class="layui-form-label" style="padding-left:-50px;">QQ:</label>\n' +
          '                <div class="layui-input-block">\n' +
          '                    <input type="text" placeholder="请输入QQ" name="studentQq" id="studentQq" class="layui-input">\n' +
          '                </div>\n' +
          '            </div>\n' +
          '            <div class="layui-form-item">\n' +
          '                <label class="layui-form-label" style="padding-left:-50px;">学生密码:</label>\n' +
          '                <div class="layui-input-block">\n' +
          '                    <input type="text" placeholder="请输入学生密码" name="studentPassword" id="studentPassword" class="layui-input">\n' +
          '                </div>\n' +
          '            </div>\n' +
          '        </form>\n' +
          '    </div>\n' +
          '</div>\n',
          btn: ['提交', '取消']
          , success: function(layero) {
            layero.find('.layui-layer-btn').css('text-align', 'center');
          },
          btn1: function(index) {
            //数据校验
            var studentName = $.trim($('#studentName').val());
            if(studentName==null&&studentName==''){
              layer.msg('姓名不能为空',{icon:5,time:1500});
              return;
            }
            var studentPassword = $.trim($('#studentPassword').val());
            if(studentPassword==null&&studentPassword==''){
              layer.msg('密码不能为空',{icon:5,time:1500});
              return;
            }
            .....太多了，省点地方,照葫芦画瓢，copy一下就行了
            // 提交
            var student = {
              studentName: studentName,
              studentPassword: studentPassword
            .....太多了，省点地方
            };
            // 向后台提交数据
            insertStudent(student);
          },
          // 取消
          btn2: function(index, layero) {
            layer.close(index);
          }
        });
        break;

添加界面：

添加.png

修改
修改的话，会稍微有点麻烦。我们先获取到表格里面的数据。

  case 'update':
        if (data.length == 0) {
          layer.msg('请选择一行');
        } else if (data.length > 1) {
          layer.msg('只能选择一行');
        } else {
          layer.open({
            id: 2,
            type: 1,
            title: ['课程修改'],
            skin: 'layui-layer-molv',
            area: '500px',
            offset: 'auto',
            content: '<div class="layui-row" id="test" style="margin-top:10px;">' +
            '    <div class="layui-col-md10">' +
            '        <form class="layui-form">' +
            '            <div class="layui-form-item">\n' +
            '                <label class="layui-form-label" style="padding-left:-50px;">姓名:</label>\n' +
            '                <div class="layui-input-block">\n' +
            '                    <input type="text" placeholder="请输入学生姓名" name="studentName" id="studentName" class="layui-input">\n' +
            '                </div>\n' +
            '            </div>\n' +
            '            <div class="layui-form-item">\n' +
            '                <label class="layui-form-label" style="padding-left:-50px;">学号:</label>\n' +
            '                <div class="layui-input-block">\n' +
            '                    <input type="text" placeholder="请输入学生学号" name="studentSno" id="studentSno" class="layui-input">\n' +
            '                </div>\n' +
            '            </div>\n' +
            '            <div class="layui-form-item">\n' +
            '                <label class="layui-form-label" style="padding-left:-50px;">性别:</label>\n' +
            '                <div class="layui-input-block">\n' +
            '                    <input type="text" placeholder="请输入学生性别" name="studentSex" id="studentSex" class="layui-input">\n' +
            '                </div>\n' +
            '            </div>\n' +
            '            <div class="layui-form-item">\n' +
            '                <label class="layui-form-label" style="padding-left:-50px;">年龄:</label>\n' +
            '                <div class="layui-input-block">\n' +
            '                    <input type="text" placeholder="请输入学生年龄" name="studentAge" id="studentAge" class="layui-input">\n' +
            '                </div>\n' +
            '            </div>\n' +
            '            <div class="layui-form-item">\n' +
            '                <label class="layui-form-label" style="padding-left:-50px;">邮箱:</label>\n' +
            '                <div class="layui-input-block">\n' +
            '                    <input type="text" placeholder="请输入学生邮箱" name="studentEmail" id="studentEmail" class="layui-input">\n' +
            '                </div>\n' +
            '            </div>\n' +
            '            <div class="layui-form-item">\n' +
            '                <label class="layui-form-label" style="padding-left:-50px;">QQ:</label>\n' +
            '                <div class="layui-input-block">\n' +
            '                    <input type="text" placeholder="请输入QQ" name="studentQq" id="studentQq" class="layui-input">\n' +
            '                </div>\n' +
            '            </div>\n' +
            '            <div class="layui-form-item">\n' +
            '                <label class="layui-form-label" style="padding-left:-50px;">学生密码:</label>\n' +
            '                <div class="layui-input-block">\n' +
            '                    <input type="text" placeholder="请输入学生密码" name="studentPassword" id="studentPassword" class="layui-input">\n' +
            '                </div>\n' +
            '            </div>\n' +
            '        </form>\n' +
            '    </div>\n' +
            '</div>\n',
            btn: ['提交', '取消']
            , success: function(layero) {
              layero.find('.layui-layer-btn').css('text-align', 'center');
              // 展示在弹出层里面
              $('#studentName').val(data[0].studentName);
              $('#studentAge').val(data[0].studentAge);
              $('#studentSno').val(data[0].studentSno);
              $('#studentSex').val(data[0].studentSex);
              $('#studentQq').val(data[0].studentQq);
              $('#studentEmail').val(data[0].studentEmail);
              $('#studentPassword').val();
            },
            btn1: function(index) {
              //数据校验
              var studentName = $.trim($('#studentName').val());
              if(studentName==null&&studentName==''){
                layer.msg('姓名不能为空',{icon:5,time:1500});
                return;
              }

              var studentPassword = $.trim($('#studentPassword').val());
              if(studentPassword==null&&studentPassword==''){
                layer.msg('密码不能为空',{icon:5,time:1500});
                return;
              }
           // .......省略一部分没写
      
              // 提交
              var student = {
                studentId: data[0].studentId,
                studentPassword: studentPassword
                .....省略没写哈
              };
             updateStudent(student);
            },
            btn2: function(index, layero) {
              layer.close(index);
            }
          });
        }
    }

修改界面：

修改界面.png

删除
这里传输的对象是一个数组，可以参考：Ajax传递不同类型的参数

case 'delete':
        if (data.length == 0) {
          layer.msg('请选择一行');
        } else {
          //获取到需要删除的id数组
          var length = data.length;
          var studentId = [];
          for (var i = 0; i < length; i++) {
            studentId.push(data[i].studentId);
          }
          layer.confirm('是否删除？', {title: '提示'}, function(index) {
           deleteStudent(studentId)
          });
        }
        break;

删除界面