egg(八):配合前端vue实现表格的增删改查

柔光的暖阳◎ 2022-09-15 12:21 303阅读 0赞

### 前言： ###

### 这个章节内容是比较多的，分为前端部分和后端部分。 ###

# **目录：** #

实现效果：增删改查

一、后端部分：

（1）数据库：

新建一张表user,设置几个字段，效果如下：

（2）egg逻辑部分：

提供的接口：http://localhost:7001/setUserList

（3）egg具体实现步骤：

1、router.js中添加：

2、新建：app / controller / new / user.js

（1）查，模糊查询

（2）增

（3）改

（4）删

user.js 源码：

3、引入mysql:点我

4、跨域问题解决：点我

二、前端部分：

新建vue文件：testApi.vue

1、初始化查询列表数据：页面展示列表，列表数据mouted获取

2、页面点击新增：展示弹框，并将弹框内容去除掉，点击新增，将弹框内容发送给后端

3、页面点击列表里面具体数据的编辑：弹框，并回填数据，修改将当前数据的id和表格数据传给后端

4、删除按钮，点击出现二次确认弹框，点击确认将当前数据的id给后端就行

testApi.vue 源码：

--------------------

# 实现效果：增删改查 #

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16][]

# 一、后端部分： #

## （1）数据库： ##

### 新建一张表user,设置几个字段，效果如下： ###

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

## （2）egg逻辑部分： ##

### 提供的接口：http://localhost:7001/setUserList ###

1.  **get** 请求，获取数据，支持模糊查询
2.  **post** 请求，新增数据
3.  **put** 请求，给后端当前数据id，修改内容
4.  **delete** 请求，根据数据id删除当前条数据

## （3）egg具体实现步骤： ##

### 1、router.js中添加： ###

/**
     * 路由配置
     * @param app
     */
    module.exports = app => {
        const { router, controller } = app;
        //--------------------------------------------------
        // server接口
        //--------------------------------------------------
        app.get('/setUserList', controller.new.user.getUserList);
        app.post('/setUserList', controller.new.user.postUserList);
        app.put('/setUserList', controller.new.user.putUserList);
        app.delete('/setUserList', controller.new.user.deleteUserList);
    
    }

### 2、新建：app / controller / new / user.js ###

![20211011180307462.png][]

### （1）查，模糊查询 ###

select * from user where name like ? % 内容 %

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

### （2）增 ###

this.app.mysql.insert('表名'，内容)

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

### （3）改 ###

UPDATE loginlist SET `password` = 'Ad123456' WHERE id = 2

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

### （4）删 ###

delete from user where id = 1

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 5][]

## **user.js 源码：** ##

/**
     *  用户信息路由
     * @param app
     * @returns {HomeController}
     */
    const Controller = require('egg').Controller;
    class NewsController extends Controller {
      async getUserList() {
        //查询库里的user表
        let params = this.ctx.query //获取路径后面的参数
        console.log('用户的参数：');
        console.log(params);
        let sql = 'select * from user'
        let initSql = sql
    
          let content = [];//参数
        let isMore = false;//是否有多个查询参数
        /**
         * @模糊查询-量大的时候效率低
         * select * from user where name like ? % 内容 %
         * 在user表中全局查找name值 == 内容的
         * % 内容 % 全局查找内容
         *   内容 %  查找以 内容 开头的数据
         * */
        if(params.name){
          sql += " where name like ?";
          content.push( "%"+params.name+"%" );
          isMore = true;
        }
        if(params.age){
          if(isMore){//true代表有多个参数
            sql += "and age LIKE ?";//and是两个条件都必须满足，or是或的关系
          }else{
            sql += " WHERE age LIKE ?";
          }
          content.push( "%"+params.age+"%" )
          isMore = true;
        }
        if(params.address){
          if(isMore){//true代表有多个参数
            sql += "and address LIKE ?";//and是两个条件都必须满足，or是或的关系
          }else{
            sql += " WHERE address LIKE ?";
          }
          content.push( "%"+params.address+"%" )
          isMore = true;
        }
        if(params.phone){
          if(isMore){//true代表有多个参数
            sql += "and phone LIKE ?";//and是两个条件都必须满足，or是或的关系
          }else{
            sql += " WHERE phone LIKE ?";
          }
          content.push( "%"+params.phone+"%" )
        }
    
        //开启分页
        if(params.page || params.pageSize){
          let current = params.page;//当前页码
          let pageSize = params.pageSize;//一页展示多少条数据
          sql += " limit ?,?";
          content.push((current-1)*pageSize,parseInt(pageSize));
        }
    
        let allList = await this.app.mysql.query(initSql);
        let userList= await this.app.mysql.query(
          sql,content
        );
        this.ctx.body = {
          code:200,
          masg:'success',
          data: {
            list:userList,
            total:allList.length
          }
        };
      }
      /**
       * 新增用户信息
       * INSERT INTO loginlist (aaa,bbb,ccc) VALUES ('"'+111+'","'+222+'","'+333+'"')
       * this.app.mysql.insert('表名'，内容)
       * this.app.mysql.insert('表名'，{
       *   name:123,
       *   age:1,
       *   address:西安
       * })
       */
      //
      async postUserList() {
        console.log(this.ctx.request.body);
        //新增数据-user表
        let data = this.ctx.request.body
        data.id = parseInt(Math.random()*100000)
        let insertResult = await this.app.mysql.insert(
          'user', data
        );
        this.ctx.body = {
          code:200,
          masg:'success',
          data:insertResult
        };
      }
      /**
       * 修改用户信息
       * UPDATE loginlist SET `password` = 'Ad123456' WHERE id = 2（唯一值），修改一个
       * UPDATE loginlist SET username = 'admins', `password` = 'Ad123456' WHERE id = 2，修改多个
       */
      async putUserList(){
        //新增数据-user表
        let id = this.ctx.query.id //获取路径后面的参数
        let data = this.ctx.request.body
        let sql = 'update user set '
        let isMore = false;//是否有多个查询参数
        if(!id){
          this.ctx.body = {
            code:200,
            masg:'warning',
            data:'id没有传'
          };
          return
        }
        //姓名
        if(data.name){
          sql += 'name = "'+data.name+'"'
          isMore = true
        }
        //年龄
        if(data.age){
          if(isMore){
            sql += ',age = "'+data.age+'"'
          }else{
            sql += 'age = "'+data.age+'"'
          }
          isMore = true
        }
        //地址
        if(data.address){
          if(isMore){
            sql += ',address = "'+data.address+'"'
          }else{
            sql += 'address = "'+data.address+'"'
          }
          isMore = true
        }
        //手机号
        if(data.phone){
          if(isMore){
            sql += ',phone = "'+data.phone+'"'
          }else{
            sql += 'phone = "'+data.phone+'"'
          }
          isMore = true
        }
        //邮箱地址
        if(data.email){
          if(isMore){
            sql += ',email = "'+data.email+'"'
          }else{
            sql += 'email = "'+data.email+'"'
          }
        }
        sql += ' where id = ' + id
    
        let insertResult = await this.app.mysql.query(sql)
        this.ctx.body = {
          code:200,
          masg:'success',
          data:insertResult
        };
      }
      /**
       * 注销用户接口
       * DELETE FROM loginlist WHERE username = 'superman'
       */
      async deleteUserList(){
        //查询库里的user表
        let params = this.ctx.query //获取路径后面的参数
        console.log('用户的参数：');
        console.log(params);
        let sql = 'delete from user where id = '+ params.id
        let res = await this.app.mysql.query(
          sql
        );
        this.ctx.body = {
          code:200,
          masg:'success',
          data:res
        };
    
      }
    
      /**
       * 模糊查询封装方法
       * @params = auth
       * */
      async query( auth ) {
        const TABLE_NAME = 'user';
        const QUERY_STR = 'id, name, age, phone, address';
        let sql = `select ${QUERY_STR} from ${TABLE_NAME} where authName like "%${auth.authName}%"`;
        const row = await this.app.mysql.query(sql);
        return row;
      }
    
    
    }
    module.exports = NewsController;

### 3、引入mysql:[点我][Link 1] ###

### 4、跨域问题解决：[点我][Link 2] ###

# 二、前端部分： #

## 新建vue文件：testApi.vue ##

### 1、初始化查询列表数据：页面展示列表，列表数据mouted获取 ###

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_17_color_FFFFFF_t_70_g_se_x_16][]

<el-table
          :data="tableInfo.list"
          style="width: 100%">
          <el-table-column
            type="index"
            label="序号"
            align="center">
          </el-table-column>
          <el-table-column
            prop="name"
            label="姓名"
            align="center">
          </el-table-column>
          <el-table-column
            prop="age"
            label="年龄"
            align="center">
          </el-table-column>
          <el-table-column
            prop="address"
            label="地址">
          </el-table-column>
          <el-table-column
            prop="phone"
            label="手机号">
          </el-table-column>
          <el-table-column
            prop="email"
            label="邮箱">
          </el-table-column>
          <el-table-column
            label="操作"
            width="100">
            <template slot-scope="scope">
              <el-button type="text" size="small" @click="editFun(scope.row)">编辑</el-button>
              <el-button type="text" size="small" @click="deleteFun(scope.row)">删除</el-button>
            </template>
          </el-table-column>
        </el-table>

mounted(){
          this.initPage()
        },
     methods: {
          /***
           * 获取列表数据
           */
          initPage() {
            let params = {
              page: this.tableInfo.page, //当前页
              pageSize: this.tableInfo.pageSize //一页展示多少条
            }
            this.$axios.get('http://localhost:7001/setUserList',{
              params
            }).then(res => {
                this.tableInfo.list = res.data.data.list
                this.tableInfo.total = res.data.data.total
              }).catch(error => {
              this.$message(error.data.message)
            })
          },
    
    }

### 2、页面点击新增：展示弹框，并将弹框内容去除掉，点击新增，将弹框内容发送给后端 ###

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_8_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_11_color_FFFFFF_t_70_g_se_x_16][]

### 3、页面点击列表里面具体数据的编辑：弹框，并回填数据，修改将当前数据的id和表格数据传给后端 ###

![2021101118160212.png][]

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_12_color_FFFFFF_t_70_g_se_x_16][]

### 4、删除按钮，点击出现二次确认弹框，点击确认将当前数据的id给后端就行 ###

![20211011181711379.png][]

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_11_color_FFFFFF_t_70_g_se_x_16 1][]

## testApi.vue 源码： ##

<template>
      <div class="pro_body">
        <el-button type='success' @click="addModal">新增</el-button>
        <el-table
          :data="tableInfo.list"
          style="width: 100%">
          <el-table-column
            type="index"
            label="序号"
            align="center">
          </el-table-column>
          <el-table-column
            prop="name"
            label="姓名"
            align="center">
          </el-table-column>
          <el-table-column
            prop="age"
            label="年龄"
            align="center">
          </el-table-column>
          <el-table-column
            prop="address"
            label="地址">
          </el-table-column>
          <el-table-column
            prop="phone"
            label="手机号">
          </el-table-column>
          <el-table-column
            prop="email"
            label="邮箱">
          </el-table-column>
          <el-table-column
            label="操作"
            width="100">
            <template slot-scope="scope">
              <el-button type="text" size="small" @click="editFun(scope.row)">编辑</el-button>
              <el-button type="text" size="small" @click="deleteFun(scope.row)">删除</el-button>
            </template>
          </el-table-column>
        </el-table>
        <el-pagination
          class="work_pagination"
          background
          :layout="'total,prev, pager, next,sizes'"
          :total="tableInfo.total"
          :hide-on-single-page="false"
          :current-page="tableInfo.page"
          :page-size="tableInfo.pageSize"
          @current-change="pageChange"
          @size-change="pageSizeChange"
        >
        </el-pagination>
    
    
        
        <el-dialog
          custom-class="modalName"
          :visible.sync="modal.show"
          :width="modal.type === 'delete'? '470px' : '800px'">
          <h3 v-if="modal.type === 'add'" slot="title">新增</h3>
          <h3 v-if="modal.type === 'edit'" slot="title">修改</h3>
          <h3 v-if="modal.type === 'delete'" slot="title">提示</h3>
          <div v-if="modal.type !== 'delete'" class="editNameBody">
            <el-form class="editNameFrom" :model="modalFrom" label-width="120px" label-position="right">
              <el-form-item label="姓名：">
                <el-input v-model="modalFrom.name"></el-input>
              </el-form-item>
              <el-form-item label="年纪：">
                <el-input v-model="modalFrom.age"></el-input>
              </el-form-item>
              <el-form-item label="手机号：">
                <el-input v-model="modalFrom.phone"></el-input>
              </el-form-item>
              <el-form-item label="邮箱：">
                <el-input v-model="modalFrom.email"></el-input>
              </el-form-item>
              <el-form-item label="地址：">
                <el-input v-model="modalFrom.address" type="textarea"></el-input>
              </el-form-item>
            </el-form>
          </div>
          <div v-if="modal.type === 'delete'">
            <i class="el-icon-warning" style="margin-right:7px;color:#FFAA00;font-size: 16px;"></i>请确认是否删除
          </div>
          <div slot="footer" class="dialog-footer">
            <el-button @click="modal.show = false">取 消</el-button>
            <el-button v-if="modal.type === 'add'" type="primary"  @click="addUserList">新增</el-button>
            <el-button v-if="modal.type === 'edit'" type="primary"  @click="editUserList">修改</el-button>
            <el-button v-if="modal.type === 'delete'" type="primary" @click="deleteUserList">确 认</el-button>
          </div>
        </el-dialog>
    
    
      </div>
    </template>
    
    <script>
      export default {
        data() {
          return {
            tableInfo:{
              columns:[
                {
                  title: '序号',
                  type: 'index',
                },{
                  title: '姓名',
                  prop: 'name',
                  align: 'center'
                },
                {
                  title: '年龄',
                  prop: 'age',
                  align: 'center'
                },{
                  title: '地址',
                  prop: 'address',
                  align: 'center'
                },{
                  title: '邮箱',
                  prop: 'email',
                  align: 'center'
                },{
                  title: '手机号',
                  prop: 'phone',
                  align: 'center'
                },
                {//内容slot
                  slot: 'operateT'
                }
              ],
              list:[],
              page:1,
              pageSize:10,
              total:0,
            },
            modal:{
              show:false,
              type:'add'
            },
            modalFrom:{
              name:'',
              age:'',
              phone:'',
              address:'',
              email:''
            }
          };
        },
        mounted(){
          this.initPage()
        },
        methods: {
          /***
           * 获取列表数据
           */
          initPage() {
            let params = {
              page: this.tableInfo.page, //当前页
              pageSize: this.tableInfo.pageSize //一页展示多少条
            }
            this.$axios.get('http://localhost:7001/setUserList',{
              params
            }).then(res => {
                this.tableInfo.list = res.data.data.list
                this.tableInfo.total = res.data.data.total
              }).catch(error => {
              this.$message(error.data.message)
            })
          },
          /**
           * 当前页发生改变
           * */
          pageChange(page) {
            this.tableInfo.page = page
            this.initPage() //刷新列表数据
          },
          /**
           * 当前第几页发生改变
           * */
          pageSizeChange(pageSize) {
            this.tableInfo.page = 1
            this.tableInfo.pageSize = pageSize
            this.initPage() //刷新列表数据
          },
          //新增弹框
          addModal(){
            this.modalFrom ={
              name:'',
              age:'',
              phone:'',
              address:'',
              email:''
            }
            this.modal.show  = true
            this.modal.type  = 'add'
          },
          //修改弹框
          editFun(row){
            this.modalFrom = row
            this.modal.show  = true
            this.modal.type  = 'edit'
          },
          //删除弹框
          deleteFun(row){
            this.modalFrom = row
            this.modal.show  = true
            this.modal.type  = 'delete'
          },
          addUserList(){
            this.$axios({
              method:'post',
              url:'http://localhost:7001/setUserList',
              data:this.modalFrom
            }).then(res => {
              this.$message.success('新增成功')
              this.initPage() //刷新数据
              this.modal.show  = false
            })
          },
          editUserList(){
            this.$axios({
              method:'put',
              url:'http://localhost:7001/setUserList',
              params:{
                id:this.modalFrom.id
              },
              data:this.modalFrom
            }).then(res => {
              this.$message.success('修改成功')
              this.initPage() //刷新数据
              this.modal.show  = false
            })
          },
          deleteUserList(){
            this.$axios({
              method:'delete',
              url:'http://localhost:7001/setUserList',
              params:{
                id:this.modalFrom.id
              }
            }).then(res => {
              this.$message.success('删除成功')
              this.initPage() //刷新数据
              this.modal.show  = false
            })
          }
        }
    
      }
    </script>
    
    <style lang='scss'>
    .modalName{
      h3{
        padding:10px;
      }
    }
    </style>

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/1d1ef73514304009bdaffce15c523abc.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/416d5893a0da498ba58011af8397863e.png
[20211011180307462.png]: /images/20220828/e81eeec2633144caacabc8b4b6018f82.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/f16331c1c4e249a79d4f4f904d28d1e2.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220828/839516d8f0ca4c27a968d99425fca251.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 4]: /images/20220828/f9b7a0b55216477bb1b2a2f5e9c876d2.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_20_color_FFFFFF_t_70_g_se_x_16 5]: /images/20220828/c6b1f5e2a0614c7fba41c6637889b5b1.png
[Link 1]: https://blog.csdn.net/weixin_44727080/article/details/120569948
[Link 2]: https://blog.csdn.net/weixin_44727080/article/details/120656325
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_17_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/c8281b482a8347d3bb75da4a863cde33.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_8_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/4b6219255f3b4ebc8a50cc2f5466ebc5.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_11_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/8a2e426a45904135b52191babd6253ab.png
[2021101118160212.png]: /images/20220828/5ea066479f2147b18fe38a2b885922ab.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_12_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/bde30ee9d47a46e1bedca95e1b8907f1.png
[20211011181711379.png]: /images/20220828/1ccbe9e3511744beb7a1cd1098ce14d9.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUG9ubmVudWx0_size_11_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/4b7b048c141d4534b81c6123448783c1.png