Vue+Express+Mysql full stack project add, delete, modify, search, sort and export table functions

Time:2022-8-4

This article records the implementation of a small full-stack project. The front-end uses the vue framework, the back-end uses the express framework, and the database uses mysql.

product demand analysis

The product manager said, I need to do a web people management project, the project contains the following functions:

  • Users can create new data on the page. The new data content includes the person's name, age, hometown, and the person's remarks
  • The user can delete the previously created personnel information. The deletion is only logical deletion, not physical deletion (does not delete database data)
  • Users can modify the previously created personnel information, such as name, age, hometown, and remarks.
  • There should be an input box on the page for fuzzy search function. For example, if a user searches for the word "sea", all the data associated with this word should be presented and filtered out. Whether the name, hometown, or remarks contain the word.
  • The user can check the row and do the excel export function
  • Use tables to present data, do paging management, and all fields support sorting

To put it bluntly, it is the function of adding, deleting, modifying, paging, sorting and exporting. After we understand the requirements, we can design database tables and fields

Preview the renderings

Before starting to design the database, let's preview the final renderings, the final front-end and back-end codes and mysql tables. The address of Gitee will be given at the end of the article. Welcome to download and have a look.

Vue+Express+Mysql full stack project add, delete, modify, search, sort and export table functions

Database Design

Here, because the function is relatively simple, we can design a table and store the fields required in the project in a table. Of course, before creating a table, we need to create a new database first. Before creating a new database, we need to connect to our own MySql database. Here I use the Navicat tool to manage the Mysql database.

Connect to the database

Vue+Express+Mysql full stack project add, delete, modify, search, sort and export table functions

new database

The name of the database is person_manage, which is needed to configure the database connection pool in the subsequent express.
Vue+Express+Mysql full stack project add, delete, modify, search, sort and export table functions

New table (new field)

The name of the newly created table is people_table
Vue+Express+Mysql full stack project add, delete, modify, search, sort and export table functions

I won't go into details here, the download of Mysql and the download of Navcat. Everyone Google (Baidu), there are many tutorials for downloading and installing.

Front-end page development

The front end uses the vue project, so we simply build a vue project from 0 to 1.

  • Install the node software in advance, and check the corresponding version numbers of npm node -v and npm-v that come with node. My versions are v12.18.0 and 6.14.4 respectively.
  • Configure Taobao mirror npm install -g cnpm –registry=https://registry.npm.taobao.org, it will be faster when downloading the package
  • Use vue-cli to quickly generate a vue project architecture. In fact, the vue-cli scaffolding is actually an npm package, so use npm to download the vue-cli scaffolding, execute the command npm install -g @vue/cli to download the vue-cli scaffolding globally, and execute the command vue -V to view the corresponding vue-cli version number
  • Finally, execute the vue create project name to create a project, here I created the mydemo project, that is, vue create mydemo

After the project is created, we also need to download axios to send requests, download vue-router for routing, download element-ui for rapid development, and download nprogress to make a progress bar and other related plug-in packages. Then introduce and use these packages, and finally we modify the project directory according to the needs of the project, and the final structure is as follows.

Front-end project structure diagram

Vue+Express+Mysql full stack project add, delete, modify, search, sort and export table functions

Then paste the code of each folder

api folder section

Secondary encapsulation of axios (api.js)

// Import the axios package and create an axios instance
import axios from "axios";  
const http = axios.create({})

import NProgress from 'nprogress' // Import NProgress Of course, download it in advance by npm
import 'nprogress/nprogress.css' // Import NProgress and corresponding styles
NProgress.configure({ showSpinner: false }) // Hide the spinning progress bar on the right

// Add request interceptor and corresponding interceptor to this axios instance
// request interception
http.interceptors.request.use(
    (config) => {
        NProgress.start() // Start the progress bar
        return config;
    },
    (err) => {
        return Promise.reject(err);
    }
)
//response interception
http.interceptors.response.use(
    (res) => {
        NProgress.done() // close the progress bar
        return res.data
    },
    (error) => {
        NProgress.done() // close the progress bar
        return Promise.reject(error);
    }
)

/*
    Expose a function in which the newly created axios instance with the interceptor is used to send the request.
    Because sending a request needs to specify the request method, request address, request parameters, request header, response type and other related information
    Therefore, you need to receive the corresponding method, url, data, headers, responseType and other parameters
    The api function finally exposed here is reserved for the interface function defined in the lss/index.js file
*/ 
export default (method, url, data = null, headers = 'application/json;charset=UTF-8', responseType) => {
    if (method == "post") {
        return http({
            method: 'post',
            url: url,
            data: data,
            headers: {
                'Content-Type': headers,
            },
            responseType: responseType
        });
    } else if (method == "get") {
        return http({
            method: 'get',
            url: url,
            params: data,
            headers: {
                'Content-Type': headers
            },
            responseType: responseType
        });
    } else if (method == "put"){
        // ......
        return;
    } else if (method == "delete"){
        // .....
        return;
    } 
}

After the secondary encapsulation of axios, we need to introduce it in the interface function and define the interface function

Definition of interface functions (lss/index.js)

// Import the axios instance created in http
import http from '../api';

export const getTableData = (params) => http("get", `/api/getTableData`,params)//Paging query to get character information
export const getTotalCount = () => http("get", `/api/getTotalCount`)//Paging query to get the total number of entries of character information
export const deleteData = (params) => http("get", `/api/deleteData`,params)//delete character information
export const addData = (params) => http("post", `/api/addData`,params)//Add character information
export const editData = (params) => http("post", `/api/editData`,params)//Edit character information
export const exportExcel = (params) => http("post", `/api/exportExcel`,params,'application/json; charset=UTF-8',"arraybuffer")//Export table data

After the interface function is defined, we do unified management in the (api/index.js) file, expose it, and then introduce it into the main.js file and finally register it to the prototype chain of Vue.

Unified management and exposure of interface functions (api/index.js)

// Unified management of the request interface
import {
    getTableData,
    getTotalCount,
    deleteData,
    addData,
    editData,
    exportExcel,
} from './lss/index'

export default {
    getTableData,
    getTotalCount,
    deleteData,
    addData,
    editData,
    exportExcel,
}

Register the interface function on the Vue prototype in main.js

Look at the fifth-to-last line, the sixth-to-last line

import Vue from 'vue'
import App from './App.vue'

Vue.config.productionTip = false // close the tip

import "./assets/css/reset.css"; //Introduce reset style sheet

import ElementUI from 'element-ui'; //Introduce the Ele.me package
import 'element-ui/lib/theme-chalk/index.css'; //Introduce Ele.me style
Vue.use(ElementUI); // Use Eleme UI

import router from './router/index' // import router

import api from "./api/index" // Import the interface function of encapsulated axios
Vue.prototype.$api = api // Register the encapsulated axios interface function on the prototype chain

new Vue({
    render: h => h(App),
    router // mount the route
}).$mount('#app')

assets folder section

The assets folder generally stores static resource files, such as reset style sheets, such as pictures of 404 pages. Of course, resetting the style sheet is also introduced and used in main.js. I won't go into details here

router folder section (router.index.js)

We configure the routing table like this. Because of the small project, we only do two pages. Homepage page and 404 page interface, the code is as follows:

import Vue from 'vue'
import VueRouter from 'vue-router'
Vue.use(VueRouter) // register route

const router = new VueRouter({
    mode: 'history', // hash
    routes: [
        {
            path: "/",
            redirect: "/homepage",
        },
        {
            path: "/homepage",
            component: resolve => require(['@/views/homepage.vue'], resolve),
            meta: {
                name: "homepage"
            }
        },
        {
            // Will match all paths, define a 404 page here
            path: '*',
            component: resolve => require(['@/views/404.vue'], resolve),
            meta: {
                name: "404"
            },
            // redirect: "/homepage", // Of course, you can also redirect to the home page
        }
    ]
})
export default router

Of course, the routing table is also imported into main.js and mounted on the vue instance.

views folder section

The views folder stores the vue-related components corresponding to the pages we wrote, so I won't go into details here. The main thing is to note that the level of the routing table corresponds to the level of the router-view

Because nodejs is mainly used by us in the front-end to do the back-end, we will use more space to describe the use of nodejs framework express

Back-end interface development

We use the express framework in the back end. In order to make the process clearer, we will not use the express-generator plugin. We can write it step by step.

express-generator is an automated project creation tool for node, similar to vue-cli, but also an express scaffolding

Build project steps

The first step is to initialize the project with npm

implementnpm init --yesA basic project will be created, a package.json file will be generated in the folder, the command line will have the following prompt, the picture is as follows:
Vue+Express+Mysql full stack project add, delete, modify, search, sort and export table functions

The second step is to create the app.js entry file

Install the relevant dependency packages. Here we use the express framework, use the nodemon plug-in without restarting the back-end service, use the mysql plug-in to connect to the database, and use the node-xlsx plug-in for table export. So execute the following command: cnpm i express nodemon mysql node-xlsx –save. After the npm command is executed, the package.json file will add the relevant packages to the dependencies, and there will also be an additional node_modules folder to store the relevant packages we downloaded. as follows:

// package.json file
{
  "name": "expressdemo",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "mysql": "^2.18.1",
    "node-xlsx": "^0.17.1",
    "nodemon": "^2.0.12"
  }
}

The third step is to create a simple service

In the same directory as the package.json file, create an app.js file, which is the express service entry file. Paste the following code:

const express = require('express') // import express package
const app = express() // create an instance of express

app.get('/', (req, res) => { // When the request is a get request and the url path is /, the following data is returned
    res.send('This is a service built with express')
})

// Start the backend service on port 9999
app.listen(9999, (req, res) => {
    console.log('The backend service port address is: localhost://9999');
})

In this case, a simple backend service will do. We visit the local localhost:9999 in the address bar of the browser, and we can see the effect, as shown below:
Vue+Express+Mysql full stack project add, delete, modify, search, sort and export table functions

Of course, the actual backend service will not be very simple, so we need to add more code.

  • For example, the management routing in app.js needs to be modularized and split (middleware form) app.use(Router)
  • For example, you need to use the body-parser middleware to parse the parameters of the post request cnpm i body-parser
  • For example, you need to use the mysql plugin for database connection management cnpm i mysql
  • For example, if there is a function of downloading and exporting tables in the project, you need to use cnpm i mysql node-xlsx
  • Of course, in order to facilitate back-end debugging, we also download nodemon and use cnpm i mysql nodemon

Step 4 Final express directory and code

The back-end service directory diagram is as follows

Vue+Express+Mysql full stack project add, delete, modify, search, sort and export table functions

package.json file

/* package.json file configuration information */
{
  "name": "expressdemo",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "mysql": "^2.18.1",
    "node-xlsx": "^0.17.1",
    "nodemon": "^2.0.12"
  }
}

app.js program main entry file

/* app.js file*/

// Import the express plugin package and generate an example app
const express = require('express')
const app = express()

// Use the body-parser middleware to parse the post request body
app.use(express.urlencoded({ extended: false }))
app.use(express.json())

const Router = require('./router') // Introduce routes managed by sub-modules

// routing module
app.use(Router) 

// Start the backend service on port 9999
app.listen(9999, (req,res) => {
    console.log('The backend service port address is: localhost://9999');
})

mysql connection pool configuration file

/* sql/index.js file*/

// import mysql database
var mysql = require('mysql')
// Configuration of database connection pool
var pool = mysql.createPool({
  connectionLimit: 10, // the size of the connection pool
  host: 'localhost', // hostname
  user: 'root', // username
  password: '123456', // password
  database: 'person_manage' // The database name creates a person_manage database in the database with many tables
});
// expose the connection pool
module.exports = pool

Interface development

We write the interface in the router.js file

Paging sort interface (with fuzzy search query)

const express = require('express') // 引入express
const route = express.Router() // instantiate a route object

// Import the node-xlsx package
const xlsx = require('node-xlsx') 

// Introduce connection pool
const pool = require('./sql/index')

// Paging sort interface (with fuzzy search query)
route.get('/getTableData', (req, res) => {

  // console.log('Request parameters', req.query);
  /* There are four general paging parameters: sorting field, sorting method, which page, and how many items per page*/
  // Paging query format: select * from table limit (start-1)*limit,limit;
  // let sql = "select * from people_table ORDER BY age ASC LIMIT 0,2"
  // let sql = "select * from people_table"
  // And because this interface has fuzzy query, use like fuzzy query to searchWord

  let sql = null // concatenate sql statement
  if (req.query.sortWord == "age" | req.query.sortWord == "id") { // Use default sort for numeric types such as age and id
    sql = `select * from people_table WHERE (name LIKE '%${req.query.searchWord}%' OR home LIKE '%${req.query.searchWord}%' OR remark LIKE '%${req.query.searchWord}%') AND is_delete_status <> 0 ORDER BY ${req.query.sortWord} ${req.query.sortOrder} LIMIT ${(req.query.pageIndex - 1) * (req.query.pageSize)},${req.query.pageSize}`
  } else { // For Chinese characters, GBK sorting is mandatory, so that they can be sorted according to Chinese Pinyin
    sql = `select * from people_table WHERE (name LIKE '%${req.query.searchWord}%' OR home LIKE '%${req.query.searchWord}%' OR remark LIKE '%${req.query.searchWord}%') AND is_delete_status <> 0 ORDER BY CONVERT( ${req.query.sortWord} USING gbk ) ${req.query.sortOrder} LIMIT ${(req.query.pageIndex - 1) * (req.query.pageSize)},${req.query.pageSize}`
  }

  // console.log('The spliced ​​sql statement',sql);

  pool.getConnection(function (err, connection) {
    if (err) { throw err }
    connection.query(sql, function (error, results, fields) {
      connection.release()
      let apiRes = {
        code: 0,
        msg: &quot;Success&quot;,
        data: results
      }
      res.send(apiRes)
    })
  })
})

// other interfaces...

module.exports = route // exposed for easy management

Query the total number of entries interface

route.get("/getTotalCount", (req, res) => {
  console.log(req.query);
  pool.getConnection(function (err, connection) {
    if (err) { throw err }
    connection.query(`select count(*) AS total from people_table WHERE (name LIKE '%${req.query.searchWord}%' OR home LIKE '%${req.query.searchWord}%' OR remark LIKE '%${req.query.searchWord}%') AND is_delete_status <> 0`, function (error, results, fields) {
      connection.release()
      let apiRes = {
        code: 0,
        msg: &quot;Success&quot;,
        data: results[0].total
      }
      res.send(apiRes)
    })
  })
})

Tombstone data interface (single delete)

route.get("/deleteData", (req, res) => {
  let sql = `UPDATE people_table SET is_delete_status = 0 WHERE id = ${req.query.id}`
  pool.getConnection(function (err, connection) {
    if (err) { throw err }
    connection.query(sql, function (error, results, fields) {
      connection.release()
      if (results.affectedRows == 1) { // The description affects a row, that is, the logical deletion field is modified and a row is deleted
        let apiRes = {
          code: 0,
          msg: &quot;Success&quot;,
          data: &quot;Congratulations, the deletion is successful...&quot;
        }
        res.send(apiRes)
      } else {
        let apiRes = {
          code: 0,
          msg: &quot;Success&quot;,
          data: &quot;Sorry, deletion failed...&quot;
        }
        res.send(apiRes)
      }
    })
  })
})

Physical delete data interface (single delete)

route.get("/trueDeleteData", (req, res) => {
  let sql = `DELETE from people_table WHERE id = ${req.query.id}`
  pool.getConnection(function (err, connection) {
    if (err) { throw err }
    connection.query(sql, function (error, results, fields) {
      connection.release()
      if (results.affectedRows == 1) { // Indicates that a row is affected, that is, a row is really deleted
        let apiRes = {
          code: 0,
          msg: &quot;Success&quot;,
          data: &quot;Congratulations, the deletion is successful...&quot;
        }
        res.send(apiRes)
      } else {
        let apiRes = {
          code: 0,
          msg: &quot;Success&quot;,
          data: &quot;Sorry, deletion failed...&quot;
        }
        res.send(apiRes)
      }
    })
  })
})

Tombstone data interface (bulk delete)

route.get("/selectDelete",(req,res)=>{
  console.log(req.query.ids);
  let sql = `UPDATE people_table SET is_delete_status = 0 WHERE id in (${req.query.ids})`
  pool.getConnection(function (err, connection) {
    if (err) { throw err }
    connection.query(sql, function (error, results, fields) {
      console.log('results', results);
      connection.release()
      if (results.affectedRows != 0) { // The description affects a row, that is, the logical deletion field is modified and a row is deleted
        let apiRes = {
          code: 0,
          msg: &quot;Success&quot;,
          data: &quot;Congratulations, the batch deletion is successful...&quot;
        }
        res.send(apiRes)
      } else {
        let apiRes = {
          code: 0,
          msg: &quot;Failed&quot;,
          data: &quot;Sorry, batch delete failed...&quot;
        }
        res.send(apiRes)
      }
    })
  })
})

Add data interface

/**
 * It is worth mentioning that I did not encapsulate the sql statement function here
 * So some sql statements are dynamically spliced
 * In actual work, it is still necessary to encapsulate the sql statement according to the project requirements
 * Mainly for convenience to understand step by step
 * */ 

route.post("/addData", (req, res) => {

  // console.log('Add interface request parameters', req.body);
  // console.log('Add interface request parameters', Object.keys(req.body));
  // console.log('Add interface request parameters', Object.values(req.body));
  // Correct sql statement: INSERT INTO myTale(NAME,sex,borndate) VALUES('Bone Jing','Female','2021-05-16');
  // Error sql statement: INSERT INTO myTale(NAME,sex,borndate) VALUES(white bone essence, female, 2021-05-16);
  // Because writing data into the database requires attention to quotation marks, that is, quotation marks need to be placed in VALUES(), so the following splicing sql statement should use the escape character \' 

  let str = &quot;&quot; // Here you can break the point, or print it to see
  Object.values(req.body).forEach((item) => {
    str = str + "\'" + item + "\'" + ","
  })
  // console.log('Intercept',str.substr(0,str.length -1)); //Intercept, don't take the last comma
  let editStr = str.substr(0, str.length - 1)

  let sql = `INSERT INTO people_table (${Object.keys(req.body).toString()}) VALUES(${editStr})`
  // console.log(&quot;Look at the sql statement concatenated with escape characters&quot;, sql);

  pool.getConnection(function (err, connection) {
    if (err) { throw err }
    connection.query(sql, function (error, results, fields) {
      connection.release()
      if (results.affectedRows == 1) {
        let apiRes = {
          code: 0,
          msg: &quot;Success&quot;,
          data: &quot;Congratulations, the new addition is successful...&quot;
        }
        res.send(apiRes)
      } else {
        let apiRes = {
          code: 0,
          msg: &quot;Failed&quot;,
          data: &quot;Sorry to add failed&quot;
        }
        res.send(apiRes)
      }
    })
  })
})

Edit data interface

route.post("/editData", (req, res) => {

  // process the parameters
  let id = req.body.id
  delete req.body.id
  // console.log('original parameter', req.body);
  // console.log('keys array parameter', Object.keys(req.body));
  // console.log('values ​​array parameter', Object.values(req.body));

  let keysArr = Object.keys(req.body)
  let valuesArr = Object.values(req.body)

  let str = ""
  for (let i = 0; i < keysArr.length; i++) {
    for (let j = 0; j < valuesArr.length; j++) {
      if (i == j) {
        str = `${str},${keysArr[i]}=${"\'" + valuesArr[i] + "\'"}`
      }
    }
  }
  str = str.substr(1, str.length)

  let sql = `UPDATE people_table SET ${str} WHERE id=${id}`
  // console.log(&quot;Look at the spliced ​​sql statement --&gt;&quot;,sql);

  pool.getConnection(function (err, connection) {
    if (err) { throw err }
    connection.query(sql, function (error, results, fields) {
      connection.release()
      console.log('Edit database results---&gt;', results);
      if (results.affectedRows == 1) {
        let apiRes = {
          code: 0,
          msg: &quot;Success&quot;,
          data: &quot;Congratulations, the editing is successful...&quot;
        }
        res.send(apiRes)
      } else {
        let apiRes = {
          code: 0,
          msg: &quot;Failed&quot;,
          data: &quot;Sorry the edit failed!&quot;
        }
        res.send(apiRes)
      }
    })
  })
})

Excel check the export interface

const xlsx = require('node-xlsx') // import node-xlsx package 

route.post('/exportExcel', (req, res) => {
  let sql = `SELECT name,home,age,remark FROM people_table WHERE FIND_IN_SET(id,'${req.body.ids}')`
  // console.log('The spliced ​​sql statement --&gt;', sql);
  pool.getConnection(function (err, connection) {
    if (err) { throw err }
    connection.query(sql, function (error, results, fields) {
      connection.release()
      // console.log('Edit database results---&gt;', results);

      let data = []
      data.push(Object.keys(results[0])) // excel table header

      results.forEach((item) => {
        data.push(Object.values(item))
      })
      // console.log('Processed data data', data);

      let sheetArr = [ // Excel table content data
        {
          name: &quot;sheet123&quot;, // specify the name of the sheet
          data: data // corresponding to the content of the sheet
        },
      ]

      let optionArr = { // excel table content configuration data
        "!cols": [
          { wch: 15 },
          { wch: 15 },
          { wch: 10 },
          { wch: 30 },
        ],
      }
      // The build method is used to generate a table and pass it to the front end in the form of a binary file
      // The end method is used here, if the send method is used, an error will be reported
      res.end(xlsx.build(sheetArr, optionArr))
    })
  })
})

Excel export can refer to the previous article:https://segmentfault.com/a/11…

Front-end and back-end joint debugging

The front-end and back-end joint debugging is relatively simple. The front-end sends requests to the back-end according to the API interface provided by the back-end. After the back-end receives the corresponding request, it performs the corresponding database operation and finally returns the corresponding data to the front-end. I won't go into details here. For details, you can clone the code I uploaded to Gitee, you can give a star, thank you.

Project Gitee address

Summarize

I personally think that the front-end daoists still need to learn node and mysql. After all, after learning node and mysql well, the whole process of the project will run through, and we will have an overall thinking. It also helps us to cooperate with the back-end joint debugging project and improve the overall development efficiency of the project.

In my spare time, I took the time to write a full-stack project, which may not be perfect. I welcome criticism and correction from the big guys, and communicate more and make progress together.