Express + Node +mysql implements a front and back end add, delete, change, check and file upload, complete version

Express + Node +mysql implements a front and back end add, delete, change, check and file upload, complete version

preface

Recently I want to learn node.js, implement a add, delete, change, check and file upload interface.

start

Node installation and mysql installation I am here, will not elaborate, for details, please check the rookie tutorial, there are detailed tutorials. Since we are using Express, we will go to the Express website and find an Express application generator. We will use it to create an application skeleton and install Express-Generator

npm install express-generator -g
Copy the code

Then run the following sentence to create an application skeleton

express --view=pug express-demo
Copy the code

The effect is as follows:

cnpm i
Copy the code

After that, you can run the service

npm start
Copy the code

Go to your browser and open localhost:3000 and you’ll see the following image:

Add and delete

Modifying the directory Structure

Create a few folders in the root directory,

. | - config | - mysql. Js | -- -- modules | -- handle. Js | -- json. Js | -- poolextend. Js | -- SQL. Js | -test| | - index. The CSS -- index. HTML | -- index. Js...Copy the code

Here’s how the code encapsulates the database configuration

/* config/mysql.js */

// MySQL database connection configuration encapsulation
var mysql = {
  host: 'localhost'.user: 'root'.password: 'root'.database: 'abc'.port: 3306
};
module.exports = mysql;
Copy the code

Concrete logic encapsulation

 /* modules/handle.js */
{} req.params contains the object mapped to the specified route "parameter" attribute, if there is route/user/ : {} req.params contains the object mapped to the specified route "parameter" attribute, if there is route/user/ : {} req.params contains the object mapped to the specified route "parameter" attribute, if there is route/user/ : Name, then the "name" property can be used as req.params.name req.body is usually used to parse the data in POST requests +req.query.id converts the ID to an integer */
/ / into the mysql
var mysql = require('mysql');
// Introduce the mysql connection configuration
var mysqlconfig = require('.. /config/mysql');
// Import the connection pool configuration
var poolextend = require('./poolextend');
// Introduce the SQL module
var sql = require('./sql');
// Import json module
var json = require('./json');
var fs = require('fs'); 
// Use connection pooling to improve performance
var pool = mysql.createPool(poolextend({}, mysqlconfig));
var userData = {
    add: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            var param = req.query || req.params; 
            connection.query(sql.insert, [param.id, param.name, param.age, ], function (err, result) {
                if (result) {
                    result = 'add'
                }
                // Return the result of the operation to the foreground page in json form
                json(res, result);
                // Release the connection
                connection.release();
            });
        });
    },
    delete: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            var id = +req.query.id;
            connection.query(sql.delete, id, function (err, result) {
                if (result.affectedRows > 0) {
                    result = 'delete';
                } else {
                    result = undefined;
                }
                json(res, result);
                connection.release();
            });
        });
    },
    update: function (req, res, next) {
        var param = req.body;
        if (param.name == null || param.age == null || param.id == null) {
            json(res, undefined);
            return;
        }
        pool.getConnection(function (err, connection) {
            connection.query(sql.update, [param.name, param.age, +param.id], function (err, result) {
                if (result.affectedRows > 0) {
                    result = 'update'
                } else {
                    result = undefined;
                }
                json(res, result);
                connection.release();
            });
        });
    },
    queryById: function (req, res, next) {
        var id = +req.query.id;
        pool.getConnection(function (err, connection) {
            connection.query(sql.queryById, id, function (err, result) {
                if(result ! =' ') {
                    var _result = result;
                    result = {
                        result: 'select'.data: _result
                    }
                } else {
                    result = undefined;
                }
                json(res, result);
                connection.release();
            });
        });
    },
    queryByName: function (req, res, next) {
        var name = req.query.name;
        pool.getConnection(function (err, connection) {
            connection.query(sql.queryByName, name, function (err, result) {
                if(result ! =' ') {
                    var _result = result;
                    result = {
                        result: 'select'.data: _result
                    }
                } else {
                    result = undefined;
                }
                json(res, result);
                connection.release();
            });
        });
    },
    queryAll: function (req, res, next) { 
        pool.getConnection(function (err, connection) {
            connection.query(sql.queryAll, function (err, result) {
                if(result ! =' ') {
                    var _result = result;
                    result = {
                        result: 'selectall'.data: _result
                    }
                } else {
                    result = undefined; } json(res, result); connection.release(); }); }); }};module.exports = userData;
Copy the code

Response data encapsulation

//json.js
// Encapsulate the pickup module
var json = function(res, result) {
  if (typeof result === 'undefined') {
      res.json({
          code: '1'.msg: 'Operation failed'
      });
  } else if (result === 'add') {
      res.json({
          code: '200'.msg: 'Added successfully'
      });
  } else if (result === 'delete') {
      res.json({
          code: '200'.msg: 'Deleted successfully'
      });
  } else if (result === 'update') {
      res.json({
          code: '200'.msg: 'Changed successfully'
      });
  } else if(result.result ! ='undefined' && result.result === 'select') {
      res.json({
          code: '200'.msg: 'Search successful'.data: result.data
      });
  } else if(result.result ! ='undefined' && result.result === 'selectall') {
      res.json({
          code: '200'.msg: 'All found successful'.data: result.data
      });
  } else{ res.json(result); }};module.exports = json;
Copy the code

Connection pool encapsulation

//poolextent.js
// Connection pool extension encapsulation
var poolextend = function(target, source, flag) {
  for (var key in source) {
      if (source.hasOwnProperty(key)) {
          flag ? (target[key] = source[key]) : (target[key] === void 0&& (target[key] = source[key])); }}return target;
}
module.exports = poolextend;
Copy the code

SQL statement encapsulation

//sql.js
// SQL statement encapsulation
var user = {
  insert:'INSERT INTO userlist(id, name, age) VALUES(? ,? ,? ,? ,?) '.update:'UPDATE userlist SET name=? , age=? WHERE id=? '.delete: 'DELETE FROM userlist WHERE id=? '.queryById: 'SELECT * FROM userlist WHERE id=? '.queryByName: `SELECT * FROM userlist WHERE name LIKE CONCAT('%',? , '%') `.queryAll: 'SELECT * FROM userlist'};module.exports = user;
Copy the code

And then the routing file we’re going to modify

 
//router/index.js
var express = require('express');
var router = express.Router();
var user = require('.. /modules/handle');

/* GET users listing. */
router.get('/'.function(req, res, next) {
  res.render('index', { title: 'SQL for MySQL' });
});
router.get('/addUser'.function(req, res, next) {
    user.add(req, res, next);
});
router.get('/queryAll'.function(req, res, next) { 
    user.queryAll(req, res, next);
});

router.get('/query'.function(req, res, next) {
    user.queryById(req, res, next);
});
router.get('/queryName'.function(req, res, next) {
    user.queryByName(req, res, next);
});
router.get('/deleteUser'.function(req, res, next) {
    user.delete(req, res, next);
});
router.get('/update'.function(req, res, next) {
    res.render('update');
});
router.post('/updateUser'.function(req, res, next) {
    user.update(req, res, next);
});
 
module.exports = router;
Copy the code

App file let’s set it up to allow cross-domain

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users'); 

var app = express();
// Set to allow cross-domain access to the service.
app.all(The '*'.function (req, res, next) {
  res.header('Access-Control-Allow-Origin'.The '*');
  // Access-control-allow-headers, which can be viewed according to F12 of browser, paste the corresponding Headers here
  res.header('Access-Control-Allow-Headers'.'Content-Type');
  res.header('Access-Control-Allow-Methods'.The '*');
  res.header('Content-Type'.'application/json; charset=utf-8');
  next();
});
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine'.'jade');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({
  extended: false
}));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter); 

// catch 404 and forward to error handler
app.use(function (req, res, next) {
  next(createError(404));
});

// error handler
app.use(function (err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') = = ='development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
}); 
module.exports = app;
 
Copy the code

Next you need to install a few dependencies

cnpm i mysql -S

cnpm i multer -S
Copy the code

This is the end of the add, delete, change and check, the next is the front-end code

  <! -- test/index.html -->

      
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="Width = device - width, initial - scale = 1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>Document</title>
  <link rel="stylesheet" type="text/css" href="./index.css" />
</head>

<body>
  <div class="handler">
    <input type="text" id="searchValue" placeholder="Please enter your name for enquiry">
    <button id="searchBtn">The query</button>
    <button id="resetBtn">reset</button>
    <button id="addBtn" class="primary">new</button>
    
  </div>
  <div class="table-box">
    <table  border="0" cellspacing="0" cellpadding="0" id="box">
    </table>
  </div>
  <! -- Add pop-ups -->
  <div id="modal">
    <div class="dialog-box">
      <div class="dialog-box__main">
        <div class="main-item">
          <label for="">The name</label>
          <input type="text"  placeholder="Please enter your name" id="nameValue">
        </div>
        <div class="main-item">
          <label for="" >age</label>
          <input type="text" placeholder="Please enter a number" id="ageValue">
        </div> 
      </div>
      <div class="dialog-box__footer">
        <button id="cancelBtn">cancel</button>
        <button id="confirmBtn" class="primary">determine</button>
      </div>
    </div>
  </div>
  <! -->
  <div id="tips">
  </div>
  <! -- Confirm the box -->
  <div id="confirmModal">
    <div class="confirm-box">
        <h3 class="title">prompt</h3>
        <div id="confirmMsg">Are you sure?</div>
        <div class="dialog-box__footer">
            <button id="confirmCancel">cancel</button>
            <button id="confirmOK" class="primary">determine</button>
          </div>
    </div> 
  </div>
</body>

</html>
<script src="./index.js"> </script> 

Copy the code

/* test/index.css */* {padding: 0;
  margin: 0;
} 
html.body {
  padding: 0;
  margin: 0;
  width: 100%;
  height: 100%;
  overflow: hidden;
}
/* Add a new frame */
#modal {
  width: 100%;
  height: 100%;
  background: rgba(0, 0, 0, 8).position: fixed;
  top: 0px;
  left: 0px;
  display: none;
  transition:all 1s;
  z-index: 99;
}

.dialog-box {
  min-width: 300px;
  /* height: 200px; * /
  background: #ffffff;
  border-radius: 3px;
  padding: 10px 10px 80px;
  position: absolute;
  top: 50%;
  left: 50%;
  transform: translate(50%, 50%);transition:all 1s;
}
#fileBtn{
  display:none;
}
#fileAdd{
  width:50px;
  height:50px;
  border:2px solid #ccc;
  position: relative;
  cursor: pointer;
  transition:all .5s;
}
#fileAdd:hover{
  box-shadow: 0px 0px 0px 50px #eee inset;
  color:#fff;
}
#fileAdd::before{
  content:' ';
  display: inline-block;
  width:20px;
  height: 2px;
  background:#ccc;
  position: absolute;
  top:50%;
  left:50%;
  transform: translate(50%, 50%); }#fileAdd::after{
  content:' ';
  display: inline-block;
  width:2px;
  height: 20px;
  background:#ccc;
  position: absolute;
  top:50%;
  left:50%;
  transform: translate(50%, 50%); }.dialog-box__main{
  margin-top: 30px;
}
.dialog-box__footer {
  position: absolute;
  bottom: 10px;
  right: 10px;

}

.dialog-box__footer button {
  cursor: pointer;
}

/* Add a pop-up style end */
button {
  border: none;
  background: #fff;
  border-radius: 3px;
  padding: 10px 20px;
  border: 1px solid #ccc;
  margin: 5px;
  cursor: pointer;
}

button:hover{
  opacity: 0.8;
}
.primary {
  background: # 333;
  color: #fff;
}
.danger{
  background: #ff6666;
  color: #fff;
}
.main-item {
  display: flex;
  align-items: center;
  justify-content: start;
  margin-bottom: 10px;
}
.main-item label{
  margin-right:10px;
  width:100px;
}
input {
  border-radius: 3px;
  padding: 10px 10px;
  border: 1px solid #ccc;
}

#tips {
  position: fixed;
  top: 88px;
  left: 50%;
  transform: translateX(50%);width: auto;
  padding: 5px 20px;
  border-radius: 10px;
  color: #fff;
  transition: all 1s;
  display: none;
  z-index: 999;
}

.table-box {
  overflow-y: scroll;
  max-width: 700px;
  height: 600px;
  overflow: auto;
  text-align: center;
  margin:10px auto;
  position: relative;
}
.table-header{
  position: -webkit-sticky;
  position: sticky;
  top:0px;
  color:#fff;
  background:# 333;
}
.table-box table {
    width:100%;
}

.handler {   
  padding:10px;
  text-align: center;
  background:# 333;
}
/* Custom table */
#tableList{ 
  border-bottom:none;
  min-width: 600px;
  display:flex;
  flex-direction: column;  
  border-right:1px solid #ccc;
  box-sizing:border-box;
}
.list-item{
  border-bottom:1px solid #ccc;
  display:flex;
  justify-content: space-around;
  align-items:center;
  height: 50px;
  line-height: 50px;
} 
.list-item span{
  width:100%;
  flex:1;
  display:block;
  height: 100%;
  border-left:1px solid #ccc;
  overflow: hidden;
  /* Sets the size of the text box */
  white-space: nowrap;
  /* Sets the content to non-newline */
  text-overflow: ellipsis;
  /* Set text outside the text box to display as... * /
}
.list-item .list-handler{
    flex:2;
}
.empty-text{
  line-height: 40px;
  border-left:1px solid #ccc;
  border-bottom:1px solid #ccc;
}



#box{
  border: 1px solid #ccc;
  border-bottom:none;
  min-width: 600px;
}

#box tr th.#box tr td{
  border-bottom:1px solid #ccc;
  border-right:1px solid #ccc;
  padding:5px 10px;
}
#box tr th{
  background:# 333;
  color:#fff;
}
#box tr th:last-child.#box tr td:last-child{ 
  border-right:none;
}


/* Confirm the popbox */
#confirmModal{
  width: 100%;
  height: 100%;
  background: rgba(0, 0, 0, 8).position: fixed;
  top: 0px;
  left: 0px;
  display: none;
  transition:all 1s;
}
.confirm-box {
  width: 300px;
  height: 200px;
  background: #ffffff;
  border-radius: 3px;
  padding: 10px;
  position: absolute;
  top: 50%;
  left: 50%;
  transform: translate(50%, 50%);transition:all 1s;
}
#confirmMsg{
  font-size:16px;
  text-align:center;
  margin-top:40px;
}
.title{
  font-size:18px;
  text-align:center;
}
Copy the code

 /* test/index.js */
    function queryDom(name){
      return document.querySelector(name)
    }
    let dialogBox = queryDom('.dialog-box')
    let confirmModal = queryDom('#confirmModal')
    let confirmMsg = queryDom('#confirmMsg')
    let confirmCancel = queryDom('#confirmCancel')
    let confirmOK = queryDom('#confirmOK')
    let searchBtn = queryDom('#searchBtn')
    let resetBtn = queryDom('#resetBtn')
    let searchValue = queryDom('#searchValue')
    let box = queryDom('#box')
    let nameValue = queryDom('#nameValue')
    let ageValue = queryDom('#ageValue')
    let addBtn = queryDom('#addBtn')
    let confirmBtn = queryDom('#confirmBtn')
    let cancelBtn = queryDom('#cancelBtn')
    let modal = queryDom('#modal')
    let tipsBox = queryDom('#tips')
    let delBtn = queryDom('.addBtn') 
    // Specify a variable that controls whether to add or edit
    let flag = 'add'
    let gobj = {}  // Temporarily store edit data
    // Query the list once when the page loads
    window.onload = function () {
      getList()
    }
    / * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- click event parts -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /
    searchBtn.onclick = function () {
      getListByName(searchValue.value)
    }
    / / reset
    resetBtn.onclick = function () {
      searchValue.value = ' '
      getList()
    }
    // Edit and delete
    box.onclick = function (e) { 
      if (e.target.className.indexOf('updateBtn') >- 1) {
        modal.style.display = 'block' 
        modal.style.opacity = 1
        dialogBox.style.left = '50%'
        flag = 'edit'
        let obj = JSON.parse(e.target.getAttribute('data-item'))
        gobj = obj 
        detail(obj)
      } else if (e.target.className.indexOf('delBtn') >- 1) {
        let id = e.target.getAttribute('data-id')

        confirmBox('This deletes the row. Are you sure? ').then(function(){
          del(id)
        }).catch(function(){
          console.log('Cancel delete')}}}function detail(item) {
      nameValue.value = item.name
      ageValue.value = item.age
    }

    / / new
    addBtn.onclick = function () {
      modal.style.display = 'block'
      modal.style.opacity = 1
      dialogBox.style.left = '50%'
      flag = 'add'
      nameValue.value = ' '
      ageValue.value = ' ' 
    }
    


    / / cancel
    cancelBtn.onclick = function () {
      setTimeout(function() {
        modal.style.display = 'none'
      }, 1000);
      modal.style.opacity = 0
      dialogBox.style.left = '-300px'
    }
    / / sure
    confirmBtn.onclick = function () { 
      // Check cannot be null
      if(! ageValue.value||! nameValue.value ){ tips.error('Cannot be empty')
        return
      }
      // Check whether the age is a number
      if(!/^\d*$/g.test(ageValue.value)){
        tips.error('Age please enter a number')
        return
      }
      setTimeout(function() {
        modal.style.display = 'none'
      }, 1000);
      modal.style.opacity = 0
      dialogBox.style.left = '-300px'
      if (flag === 'add') {
        add()
      } else if (flag === 'edit') {
        update(gobj.id)
      }
    }
    / * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- click event parts -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /

    

    / * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- request part -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /
    // Delete data
    function del(id) {
      _ajax({
        url: 'http://localhost:3000/deleteUser'.data: {
          id: id,
        },
        success: function (res) {
          if (res.code === '200') {
            tips.success(res.msg)
            getList()
          } else {
            tips.error(res.msg)
          }
        },
        error: function (e) {
          console.log(e, 'error')}}}// Get all data
    function getList() { 
      _ajax({
        url: 'http://localhost:3000/queryAll'.success: function (res) {
          let resData = res.data || []
          if (resData) { 
              renderTable(resData) 
          }

        },
        error: function (e) {
          console.log(e, 'error')}}}// Query data by name
    function getListByName(name) { 
      _ajax({
        url: 'http://localhost:3000/queryName'.data: {
          name: name
        },
        success: function (res) {
          let resData = res.data || []
          if (resData) {
            renderTable(resData)
          } 
        },
        error: function (e) {
          console.log(e, 'error')}}}// Add data
    function add() {
      _ajax({
        url: 'http://localhost:3000/addUser'.data: {
          name: nameValue.value,
          age: ageValue.value, 
        },
        success: function (res) {
          if (res.code === '200') {
            tips.success(res.msg)
            getList()
          } else {
            tips.error(res.msg)
          }
        },
        error: function (e) {
          console.log(e, 'error')}}}// Update data
    function update(id) {
      _ajax({
        url: 'http://localhost:3000/updateUser'.method: 'POST'.data: {
          id: id,
          name: nameValue.value,
          age: ageValue.value, 
        },
        success: function (res) {
          if (res.code === '200') {
            tips.success(res.msg)
            getList()
          } else {
            tips.error(res.msg)
          }
        },
        error: function (e) {
          console.log(e, 'error')}}}/ * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - requested part -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /

    // Encapsulate render table functions
    function renderTable(list){
      // Empty the previous table
      box.innerHTML = ' '
      // Add a header
      let headerTrNode = document.createElement('tr')
      headerTrNode.innerHTML = X. ` < th > < / th > < th > id < / th > < th > name < / th > < th > age < / th > < th > < / th > operation `
      box.appendChild(headerTrNode) 
      // Render table body according to data
      list.forEach((item,index) = >{
        let trNode = document.createElement('tr')
        trNode.innerHTML = `
          <td>${index+1}</td> 
          <td>${item.id}</td>
          <td>${item.name}</td>
          <td>${item.age}</td> 
          <td  >
            <button class="updateBtn primary"  data-item='The ${JSON.stringify(item)}'> edit < / button > & have spent <button class="delBtn danger" data-id="${item.id}</button> </td>
        box.appendChild(trNode)
      })
    }
    // Encapsulate the request function
    function _ajax(option) {
      let url = option.url || ' '
      let method = option.method ? option.method.toUpperCase() : 'GET'
      let data = option.data || null

      // Change the request parameters to form format
      var formData = []
      for (var key in data) {
        formData.push(' '.concat(key, '=', data[key]))
      }
      data = formData.join('&')
      if (method === 'GET') {
        url += location.search.length === 0 ? ' '.concat('? ', data) : ' '.concat('&', data)
      }
      let xhr = new XMLHttpRequest()
      xhr.open(method, url, true)
      // Set the response parameters to the format passed in or the default JSON format
      xhr.responseType = option.responseType || 'json'
      xhr.onreadystatechange = (a)= > {
        if (xhr.readyState === 4) {
          if (xhr.status === 200) {
            option.success(xhr.response)
          } else {
            option.error()
          }
        }
      }

      if (method === 'POST') {
        // Set the request format for sending data to form format
        // xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded')
        // Set the request format for sending data to JSON format
        xhr.setRequestHeader('content-type'.'application/json');
      }
      xhr.send(method === 'POST' ? JSON.stringify(option.data) : null)}/* ---------------- tool function part -------------- */
    // Encapsulate a hint function
    function Tips() {
      
      var loop
      this.error = function (str, delay) {
        if(loop){
          clearTimeout(loop)
        }
        tipsBox.style.display = 'block'
        tipsBox.style.background = '#ff6666'
        tipsBox.innerHTML = str
        loop = setTimeout((a)= > {
          tipsBox.style.display = 'none'
        }, delay || 3000)}this.success = function (str, delay) {
        if(loop){
          clearTimeout(loop)
        }
        tipsBox.style.display = 'block'
        tipsBox.style.background = '#78d626'
        tipsBox.innerHTML = str
      loop = setTimeout((a)= > {
          tipsBox.style.display = 'none'
        }, delay || 3000)}}let tips = new Tips()
    // Encapsulate a secondary confirmation frame
    function confirmBox(msg){
      msg = msg || 'Sure to delete? '
      confirmMsg.innerHTML = msg
      confirmModal.style.display = 'block'
      return new Promise(function (resolve,reject) {
        confirmOK.onclick = function(){ 
          confirmModal.style.display = 'none' 
          resolve()
        }
        confirmCancel.onclick = function(){ 
          confirmModal.style.display = 'none'
          reject()
        }
      });
      
    }
    /* ---------------- tool function part -------------- */
Copy the code

After that, let’s create a table for the database, UserList and UploadFiles, which I created along with file upload. It doesn’t make any difference.

Then go back to the project and restart the service

npm start
Copy the code

Add, delete, modify, and check the directory structure.

File upload

It’s all in the code. Let’s go straight to the code. In order to paste and copy, I’m going to go straight to the whole code, so I don’t have to mess it up. It’s too much work. Add a SQL statement to sql.js and modify the statement

//sql.js
// SQL statement encapsulation
var user = {
  insert:'INSERT INTO userlist(id, name, age,fileId,fileUrl) VALUES(? ,? ,? ,? ,?) '.update:'UPDATE userlist SET name=? , age=? ,fileId=? ,fileUrl=? WHERE id=? '.delete: 'DELETE FROM userlist WHERE id=? '.queryById: 'SELECT * FROM userlist WHERE id=? '.queryByName: `SELECT * FROM userlist WHERE name LIKE CONCAT('%',? , '%') `.queryAll: 'SELECT * FROM userlist'.upload:'INSERT INTO uploadfiles(fieldname, originalName, tmpName, encoding, mimetype, size, path, tmpPath, addTime) VALUES(? ,? ,? ,? ,? ,? ,? ,? ,?) '
};
module.exports = user;

Copy the code

Then add the upload logic to handle.js and modify the interface

 
{} req.params contains the object mapped to the specified route "parameter" attribute, if there is route/user/ : {} req.params contains the object mapped to the specified route "parameter" attribute, if there is route/user/ : {} req.params contains the object mapped to the specified route "parameter" attribute, if there is route/user/ : Name, then the "name" property can be used as req.params.name req.body is usually used to parse the data in POST requests +req.query.id converts the ID to an integer */
/ / into the mysql
var mysql = require('mysql');
// Introduce the mysql connection configuration
var mysqlconfig = require('.. /config/mysql');
// Import the connection pool configuration
var poolextend = require('./poolextend');
// Introduce the SQL module
var sql = require('./sql');
// Import json module
var json = require('./json');
var fs = require('fs');
var globalObj = require('.. /config')  
// Use connection pooling to improve performance
var pool = mysql.createPool(poolextend({}, mysqlconfig));
var userData = {
    add: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            var param = req.query || req.params; 
            connection.query(sql.insert, [param.id, param.name, param.age,param.fileId,param.fileUrl], function (err, result) {
                if (result) {
                    result = 'add'
                }
                // Return the result of the operation to the foreground page in json form
                json(res, result);
                // Release the connection
                connection.release();
            });
        });
    },
    delete: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            var id = +req.query.id;
            connection.query(sql.delete, id, function (err, result) {
                if (result.affectedRows > 0) {
                    result = 'delete';
                } else {
                    result = undefined;
                }
                json(res, result);
                connection.release();
            });
        });
    },
    update: function (req, res, next) {
        var param = req.body;
        if (param.name == null || param.age == null || param.id == null) {
            json(res, undefined);
            return;
        }
        pool.getConnection(function (err, connection) {
            connection.query(sql.update, [param.name, param.age,param.fileId,param.fileUrl, +param.id], function (err, result) {
                if (result.affectedRows > 0) {
                    result = 'update'
                } else {
                    result = undefined;
                }
                json(res, result);
                connection.release();
            });
        });
    },
    queryById: function (req, res, next) {
        var id = +req.query.id;
        pool.getConnection(function (err, connection) {
            connection.query(sql.queryById, id, function (err, result) {
                if(result ! =' ') {
                    var _result = result;
                    result = {
                        result: 'select'.data: _result
                    }
                } else {
                    result = undefined;
                }
                json(res, result);
                connection.release();
            });
        });
    },
    queryByName: function (req, res, next) {
        var name = req.query.name;
        pool.getConnection(function (err, connection) {
            connection.query(sql.queryByName, name, function (err, result) {
                if(result ! =' ') {
                    var _result = result;
                    result = {
                        result: 'select'.data: _result
                    }
                } else {
                    result = undefined;
                }
                json(res, result);
                connection.release();
            });
        });
    },
    queryAll: function (req, res, next) { 
        pool.getConnection(function (err, connection) {
            connection.query(sql.queryAll, function (err, result) {
                if(result ! =' ') {
                    var _result = result;
                    result = {
                        result: 'selectall'.data: _result
                    }
                } else {
                    result = undefined;
                }
                json(res, result);
                connection.release();
            });
        });
    },
    upload: function (req, res, next) { 
      var des_file =  "uploadFiles/file/" + req.originalname;
      fs.readFile(req.path, function (error, data) {
        if (error) {
            return console.error(error);
        }
        fs.writeFile(des_file, data, function (err) {
          if (err) {
              // Failed to receive
              console.log("---------- failed to receive ----------\n");
              console.log(err);
          }else {
            // Received successfully
            // Delete cached files
            fs.unlink(req.path, function(err){
                if (err){
                  console.log('file:+req.path+'Delete failed! ');
                    return console.error(err); }})// Write file information to the database
            var time = new Date().toJSON(); 
            // Parameters passed from the front end
            var addSqlParams = [
              req.fieldname, 
              req.originalname, 
              req.filename,
              req.encoding,
              req.mimetype, 
              req.size, 
              des_file, 
              __dirname + '/' + req.path, 
              time
            ] 
            // Insert data
            pool.getConnection(function (err, connection) {
              connection.query(sql.upload, addSqlParams, function (err, result) {
                  if (err) {
                      return console.error(err);
                  }else { 
                      var response = {
                        status:200.message: 'Upload successful! '.data: {id:result.insertId,
                          path:globalObj.rootDir+ '/' + des_file,
                          fileName:req.filename,
                          time:time,
                          type:req.mimetype,
                          size:req.size, } }; res.json( response ); connection.release(); }})})}})})})}};module.exports = userData;
Copy the code

GlobalObj, I created a config.js file in the root directory just to get the absolute path to the root directory, nothing else. Let’s move on.

const globalObj = {
  rootDir:__dirname
}

module.exports = globalObj

Copy the code

Modify the routing file

//router/index.js
var express = require('express');
var router = express.Router();
var user = require('.. /modules/handle');

var multer = require('multer');
// Set the directory for the file cache
var upload = multer({ dest: './uploadFiles/tmp/'});

/* GET users listing. */
router.get('/'.function(req, res, next) {
  res.render('index', { title: 'SQL for MySQL' });
});
router.get('/addUser'.function(req, res, next) {
    user.add(req, res, next);
});
router.get('/queryAll'.function(req, res, next) { 
    user.queryAll(req, res, next);
});

router.get('/query'.function(req, res, next) {
    user.queryById(req, res, next);
});
router.get('/queryName'.function(req, res, next) {
    user.queryByName(req, res, next);
});
router.get('/deleteUser'.function(req, res, next) {
    user.delete(req, res, next);
});
router.get('/update'.function(req, res, next) {
    res.render('update');
});
router.post('/updateUser'.function(req, res, next) {
    user.update(req, res, next);
});

// File upload
router.post('/upload', upload.array('file'),function(req, res, next) {
  // File information
  if (req.files[0]) {console.log("---------- Receive file ----------\n"); 
      console.log(req.files[0]);
  }
  let reqData = req.files[0]
  user.upload(reqData, res, next);
});
module.exports = router;

Copy the code

Modify the HTML file, just add an upload button or something. If YOU’re using CSS, it’s already all over the place.

 

      
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="Width = device - width, initial - scale = 1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>Document</title>
  <link rel="stylesheet" type="text/css" href="./index.css" />
</head>

<body>
  <div class="handler">
    <input type="text" id="searchValue" placeholder="Please enter your name for enquiry">
    <button id="searchBtn">The query</button>
    <button id="resetBtn">reset</button>
    <button id="addBtn" class="primary">new</button>
    
  </div>
  <div class="table-box">
    <table  border="0" cellspacing="0" cellpadding="0" id="box">
    </table>
  </div>
  <! -- Add pop-ups -->
  <div id="modal">
    <div class="dialog-box">
      <div class="dialog-box__main">
        <div class="main-item">
          <label for="">The name</label>
          <input type="text"  placeholder="Please enter your name" id="nameValue">
        </div>
        <div class="main-item">
          <label for="" >age</label>
          <input type="text" placeholder="Please enter a number" id="ageValue">
        </div>
        <div class="main-item">
          <label for="" >photo</label>
          <div id="imgBox">

          </div>
          <div id="fileAdd">

          </div>
          <input type="file" id="fileBtn" value="Photos">
        </div>
      </div>
      <div class="dialog-box__footer">
        <button id="cancelBtn">cancel</button>
        <button id="confirmBtn" class="primary">determine</button>
      </div>
    </div>
  </div>
  <! -->
  <div id="tips">
  </div>
  <! -- Confirm the box -->
  <div id="confirmModal">
    <div class="confirm-box">
        <h3 class="title">prompt</h3>
        <div id="confirmMsg">Are you sure?</div>
        <div class="dialog-box__footer">
            <button id="confirmCancel">cancel</button>
            <button id="confirmOK" class="primary">determine</button>
          </div>
    </div> 
  </div>
</body>

</html>
<script src="./index.js"> </script> 



Copy the code

Index.js has also been changed to add some upload logic

 

  
    function queryDom(name){
      return document.querySelector(name)
    }
    let dialogBox = queryDom('.dialog-box')
    let confirmModal = queryDom('#confirmModal')
    let confirmMsg = queryDom('#confirmMsg')
    let confirmCancel = queryDom('#confirmCancel')
    let confirmOK = queryDom('#confirmOK')
    let searchBtn = queryDom('#searchBtn')
    let resetBtn = queryDom('#resetBtn')
    let searchValue = queryDom('#searchValue')
    let box = queryDom('#box')
    let nameValue = queryDom('#nameValue')
    let ageValue = queryDom('#ageValue')
    let addBtn = queryDom('#addBtn')
    let confirmBtn = queryDom('#confirmBtn')
    let cancelBtn = queryDom('#cancelBtn')
    let modal = queryDom('#modal')
    let tipsBox = queryDom('#tips')
    let delBtn = queryDom('.addBtn')
    /* Upload image */
    let fileBtn = document.getElementById('fileBtn')
    let fileAdd = document.getElementById('fileAdd')
    let imgBox = document.getElementById('imgBox')
    // Specify a variable that controls whether to add or edit
    let flag = 'add'
    let gobj = {}  // Temporarily store edit data
    var imgObj = {}
    // Query the list once when the page loads
    window.onload = function () {
      getList()
    }
    / * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- click event parts -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /
    searchBtn.onclick = function () {
      getListByName(searchValue.value)
    }
    / / reset
    resetBtn.onclick = function () {
      searchValue.value = ' '
      getList()
    }
    // Edit and delete
    box.onclick = function (e) { 
      if (e.target.className.indexOf('updateBtn') >- 1) {
        modal.style.display = 'block' 
        modal.style.opacity = 1
        dialogBox.style.left = '50%'
        flag = 'edit'
        let obj = JSON.parse(e.target.getAttribute('data-item'))
        gobj = obj
        imgObj = {
          id:obj.fileId,
          path:obj.fileUrl, 
        }
        imgBox.innerHTML = `<img src="${imgObj.path}" width="50px" style="margin-right:10px;" > `
        detail(obj)
      } else if (e.target.className.indexOf('delBtn') >- 1) {
        let id = e.target.getAttribute('data-id')

        confirmBox('This deletes the row. Are you sure? ').then(function(){
          del(id)
        }).catch(function(){
          console.log('Cancel delete')}}}function detail(item) {
      nameValue.value = item.name
      ageValue.value = item.age
    }

    / / new
    addBtn.onclick = function () {
      modal.style.display = 'block'
      modal.style.opacity = 1
      dialogBox.style.left = '50%'
      flag = 'add'
      nameValue.value = ' '
      ageValue.value = ' '
      imgObj = {}
      imgBox.innerHTML = ' '
    }
   
    // File upload
    fileBtn.onchange = function(e){
      let file = e.target.files[0]
      fileUpload(file,function(res){ 
        imgBox.innerHTML = `<img src="${res.data.path}" width="50px" style="margin-right:10px;" > `
        imgObj = res.data
      })
    }
    // Click upload image
    fileAdd.onclick = function(){ 
      fileBtn.click()
    }


    / / cancel
    cancelBtn.onclick = function () {
      setTimeout(function() {
        modal.style.display = 'none'
      }, 1000);
      modal.style.opacity = 0
      dialogBox.style.left = '-300px'
    }
    / / sure
    confirmBtn.onclick = function () { 
      // Check cannot be null
      if(! ageValue.value||! nameValue.value||! imgObj.id){ tips.error('Cannot be empty')
        return
      }
      // Check whether the age is a number
      if(!/^\d*$/g.test(ageValue.value)){
        tips.error('Age please enter a number')
        return
      }
      setTimeout(function() {
        modal.style.display = 'none'
      }, 1000);
      modal.style.opacity = 0
      dialogBox.style.left = '-300px'
      if (flag === 'add') {
        add()
      } else if (flag === 'edit') {
        update(gobj.id)
      }
    }
    / * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- click event parts -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /

    

    / * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- request part -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /
    // Delete data
    function del(id) {
      _ajax({
        url: 'http://localhost:3000/deleteUser'.data: {
          id: id,
        },
        success: function (res) {
          if (res.code === '200') {
            tips.success(res.msg)
            getList()
          } else {
            tips.error(res.msg)
          }
        },
        error: function (e) {
          console.log(e, 'error')}}}// Get all data
    function getList() { 
      _ajax({
        url: 'http://localhost:3000/queryAll'.success: function (res) {
          let resData = res.data || []
          if (resData) { 
              renderTable(resData) 
          }

        },
        error: function (e) {
          console.log(e, 'error')}}}// Query data by name
    function getListByName(name) { 
      _ajax({
        url: 'http://localhost:3000/queryName'.data: {
          name: name
        },
        success: function (res) {
          let resData = res.data || []
          if (resData) {
            renderTable(resData)
          } 
        },
        error: function (e) {
          console.log(e, 'error')}}}// Add data
    function add() {
      _ajax({
        url: 'http://localhost:3000/addUser'.data: {
          name: nameValue.value,
          age: ageValue.value,
          fileId:imgObj.id,
          fileUrl:imgObj.path, 
        },
        success: function (res) {
          if (res.code === '200') {
            tips.success(res.msg)
            getList()
          } else {
            tips.error(res.msg)
          }
        },
        error: function (e) {
          console.log(e, 'error')}}}// Update data
    function update(id) {
      _ajax({
        url: 'http://localhost:3000/updateUser'.method: 'POST'.data: {
          id: id,
          name: nameValue.value,
          age: ageValue.value,
          fileId:imgObj.id,
          fileUrl:imgObj.path, 
        },
        success: function (res) {
          if (res.code === '200') {
            tips.success(res.msg)
            getList()
          } else {
            tips.error(res.msg)
          }
        },
        error: function (e) {
          console.log(e, 'error')}}}/ * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - requested part -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /

    // Encapsulate render table functions
    function renderTable(list){
      // Empty the previous table
      box.innerHTML = ' '
      // Add a header
      let headerTrNode = document.createElement('tr')
      headerTrNode.innerHTML = X. ` < th > < / th > < th > id < / th > < th > name < / th > < th > age < / th > < th > photo < / th > < th > < / th > operation `
      box.appendChild(headerTrNode) 
      // Render table body according to data
      list.forEach((item,index) = >{
        let trNode = document.createElement('tr')
        trNode.innerHTML = `
          <td>${index+1}</td> 
          <td>${item.id}</td>
          <td>${item.name}</td>
          <td>${item.age}</td>
          <td>
            <img src="${item.fileUrl}" width="50px"> 
          </td>
          <td  >
            <button class="updateBtn primary"  data-item='The ${JSON.stringify(item)}'> edit < / button > & have spent <button class="delBtn danger" data-id="${item.id}</button> </td>
        box.appendChild(trNode)
      })
    }
    // Encapsulate the request function
    function _ajax(option) {
      let url = option.url || ' '
      let method = option.method ? option.method.toUpperCase() : 'GET'
      let data = option.data || null

      // Change the request parameters to form format
      var formData = []
      for (var key in data) {
        formData.push(' '.concat(key, '=', data[key]))
      }
      data = formData.join('&')
      if (method === 'GET') {
        url += location.search.length === 0 ? ' '.concat('? ', data) : ' '.concat('&', data)
      }
      let xhr = new XMLHttpRequest()
      xhr.open(method, url, true)
      // Set the response parameters to the format passed in or the default JSON format
      xhr.responseType = option.responseType || 'json'
      xhr.onreadystatechange = (a)= > {
        if (xhr.readyState === 4) {
          if (xhr.status === 200) {
            option.success(xhr.response)
          } else {
            option.error()
          }
        }
      }

      if (method === 'POST') {
        // Set the request format for sending data to form format
        // xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded')
        // Set the request format for sending data to JSON format
        xhr.setRequestHeader('content-type'.'application/json');
      }
      xhr.send(method === 'POST' ? JSON.stringify(option.data) : null)}// File upload
    function fileUpload(file,callback){
      let url = "http://localhost:3000/upload";
      let form = new FormData(); / / FormData object
      form.append("file", file); Upload.array ('file')
      let xhr = new XMLHttpRequest();  / / the XMLHttpRequest object
      xhr.open("post", url, true); // Post mode. The URL is the server request address. True Specifies whether the request is processed asynchronously.
      xhr.onreadystatechange = function(){
        if(xhr.readyState === 4) {if(xhr.status === 200) {let resData = JSON.parse(xhr.responseText)
            callback(resData)
          }else{
            console.log('Upload failed')
          }
        } 
      }
      xhr.send(form); // Start uploading and send the form data
    }


/* ---------------- tool function part -------------- */
    // Encapsulate a hint function
    function Tips() {
      
      var loop
      this.error = function (str, delay) {
        if(loop){
          clearTimeout(loop)
        }
        tipsBox.style.display = 'block'
        tipsBox.style.background = '#ff6666'
        tipsBox.innerHTML = str
        loop = setTimeout((a)= > {
          tipsBox.style.display = 'none'
        }, delay || 3000)}this.success = function (str, delay) {
        if(loop){
          clearTimeout(loop)
        }
        tipsBox.style.display = 'block'
        tipsBox.style.background = '#78d626'
        tipsBox.innerHTML = str
      loop = setTimeout((a)= > {
          tipsBox.style.display = 'none'
        }, delay || 3000)}}let tips = new Tips()
    // Encapsulate a secondary confirmation frame
    function confirmBox(msg){
      msg = msg || 'Sure to delete? '
      confirmMsg.innerHTML = msg
      confirmModal.style.display = 'block'
      return new Promise(function (resolve,reject) {
        confirmOK.onclick = function(){ 
          confirmModal.style.display = 'none' 
          resolve()
        }
        confirmCancel.onclick = function(){ 
          confirmModal.style.display = 'none'
          reject()
        }
      });
      
    }
    /* ---------------- tool function part -------------- */

Copy the code

And then I’m going to create a new folder in the root directory to store the files that I’ve uploaded

. | - uploadFiles | - file | -- TMP...Copy the code

And then restart the service

npm start
Copy the code

We can see that

The whole demo code, I put in my Github, can be directly used to run, do not need to like the above so laborious, above I just want to make a process, convenient to check later, is how to step by step.