nodejs-入口-路由-控制器(对数据库进行增删改查)

151 阅读4分钟

前言

1.MVC

MVC是针对后端的分层模式:model、view、controller

  • model数据连接层
  • view视图层
  • controller 控制层
    • router 分发路由
    • service 业务处理

2.restful接口规范和传统接口规范

1.restful规范
    + get users 获取所有的资源
    + get users/:id 根据id获取资源
    + post users 添加资源
    + put users/:id 更新资源,更改的是所有属性
    + patch users/:id 更新资源,只更改某个属性
    + delete users/:id 根据id删除数据,其实也是一个更新语句
2.传统 规范
    + get getUsers
    + get getUsersById
    + post addUsers
    + post updateUsers
    + get deleteUsers

3.apidoc自动生成接口文档

1.安装环境

npm i apidoc -g

2.在项目的根目录创建apidoc.json

{
    "name":"example",
    "version":"0.1.0",
    "description":"apidoc basic example",
    "title":"Custom apiDoc browser title",
    "url":"https://api.github.com/v1"
}

3.在项目的根目录创建apidoc文件夹 4.给接口设置注释

/**
  * @api {get} /user/:id Request User information
  * @apiName GetUser
  * @apiGroup User
  *
  * @apiParam {Number} id Users unique ID.
  *
  * @apiSuccess {String} firstname Firstname of the User.
  * @apiSuccess {String} lastname  Lastname of the User.
  * @apiSuccessExample {json} Success-Response:
  *     HTTP/1.1 200 OK
  *     {
  *       "firstname": "John",
  *       "lastname": "Doe"
  *     }
  */

5.生成接口文档

apidoc -i ./router -o ./apidoc

实践

1.apidoc.json文件:

{
"name": "example",
"version": "0.1.0",
"description": "史上最牛逼的接口文档",
"title": "英雄管理",
"url": "http://127.0.0.1:3000"
}

2.给接口设置注释

/** 获取英雄
* @api {get} /users 1.1 获取英雄
* @apiName GetUser
* @apiGroup User
*
* @apiParam {null} null null
*
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
{
  "status": 0,
  "message": [
      {
          "id": 2,
          "name": "不知火舞",
          "age": 16,
          "sex": "女",
          "isdel": 0,
          "add_time": null
      },
      {
          "id": 3,
          "name": "鲁班",
          "age": 2,
          "sex": "妖人",
          "isdel": 0,
          "add_time": null
      },
      {
          "id": 5,
          "name": "廉颇",
          "age": 30,
          "sex": "男",
          "isdel": 0,
          "add_time": null
      },
      {
          "id": 7,
          "name": "关羽",
          "age": 18,
          "sex": "妖人",
          "isdel": 0,
          "add_time": null
      },
      {
          "id": 11,
          "name": "老王",
          "age": 28,
          "sex": "女",
          "isdel": 0,
          "add_time": "2021-05-27 11:09:55"
      },
      {
          "id": 12,
          "name": "王昭君111",
          "age": 28,
          "sex": "女",
          "isdel": 0,
          "add_time": "2021-05-27 11:08:50"
      }
  ]
}
*/

/** 根据获取英雄资源
* @api {get} /users/:id 1.2 根据id获取英雄资源
* @apiName GetUserById
* @apiGroup User
*
* @apiParam {Number} id 用户id
*
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
{
  "status": 0,
  "message": [
      {
          "id": 2,
          "name": "不知火舞",
          "age": 16,
          "sex": "女",
          "isdel": 0,
          "add_time": null
      }
  ]
}
*/

/** 添加英雄
* @api {post} /users 1.3 添加英雄
* @apiName AddUserById
* @apiGroup User
*
* @apiParam {String} name 用户名称(必填)
* @apiParam {Number} age 用户年龄(必填)
* @apiParam {String} sex 用户性别(必填)
*
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
{
  "status": 0,
  "message": 'success'
}
*/

/** 更新英雄
* @api {put} /users/:id 1.4 更新英雄
* @apiName UpdatedUserById
* @apiGroup User
*
* @apiParam {Number} id 用户id(必填)
* @apiParam {String} name 用户名称(必填)
* @apiParam {Number} age 用户年龄(必填)
* @apiParam {String} sex 用户性别(必填)
*
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
{
  "status": 0,
  "message": 'success'
}
*/

/** 删除英雄
* @api {delete} /users/:id 1.5 删除英雄
* @apiName DeletedUserById
* @apiGroup User
*
* @apiParam {Number} id 用户id(必填)
*
* @apiSuccessExample {json} Success-Response:
* HTTP/1.1 200 OK
{
  "status": 0,
  "message": 'success'
}
*/

image.png

一、入口文件:(index.js)

1.引入模块

const express=require('express')
const path=require('path')

2.创建服务

const app=express()

3.设置跨域

app.use(require('cors')())

4.连接apidoc文件夹下的文件

app.use(express.static(path.join(__dirname,'./apidoc')))

5.解析数据

app.use(express.urlencoded({extended:false}))
app.use(express.json())

6. 引入router下的index.js

require('./router/index.js')(app,express)

7.监听端口

app.listen(3000,['10.41.153.32','127.0.0.1'],()=>{
    console.log('本地服务 http://127.0.0.1:3000')
    console.log('远程服务 http://10.41.153.32:3000')
})

二、路由router文件(./routes/index.js)

module.exports = (app, express) => {//暴露给入口文件index.js
  const ctrl = require('../controller')//获取控制层的函数
  const router = express.Router()

  // 请求资源
  router.get('/users', ctrl.getUsers)

  // 根据id请求资源
  router.get('/users/:id', ctrl.getUserById)

  // 添加资源
  router.post('/users', ctrl.addUser)

  // 根据id更新资源
  router.put('/users/:id', ctrl.updateUser)

  // 根据id删除资源
  router.delete('/users/:id', ctrl.deleteUser)

  app.use(router)
}

三、控制层文件(./controller/index.js)

const db = require('../db')//引入数据连接文件
const moment = require('moment')
module.exports = {//暴露给路由文件
  async getUsers(req, res) {
    try {
      const sql = 'select * from user where label = 0'
      const { results } = await db.query(sql)
      res.send({
        status: 0,
        message: results
      })
    } catch (error) {
      res.send({
        message: error
      })
    }
  },
  async getUserById(req, res) {
    try {
      const sql = 'select * from user where label = 0 and id = ?'
      const { results } = await db.query(sql, req.params.id)
      if (results.length === 0) return res.send({ status: 1, message: '英雄不存在' })
      res.send({
        status: 0,
        message: results
      })
    } catch (error) {
      res.send({
        message: error
      })
    }
  },
  async addUser(req, res) {
    try {
      const sql = 'insert into user set ?'
      const body = req.body
      body.add_time = moment().format('YYYY-MM-DD HH:mm:ss')
      const { results } = await db.query(sql, body)
      if (results.affectedRows !== 1) return res.send({ status: 1, message: '添加失败' })
      res.send({
        status: 0,
        message: 'success'
      })
    } catch (error) {
      res.send({
        message: error
      })
    }
  },
  async updateUser(req, res) {
    try {
      const sql = 'update user set ? where id = ?'
      const body = req.body
      body.add_time = moment().format('YYYY-MM-DD HH:mm:ss')
      const { results } = await db.query(sql, [body, req.params.id])
      if (results.affectedRows !== 1) return res.send({ status: 1, message: '更新失败' })
      res.send({
        status: 0,
        message: 'success'
      })
    } catch (error) {
      res.send({
        message: error
      })
    }
  },
  async deleteUser(req, res) {
    try {
      const sql = 'update user set label = 1 where id = ?'
      const { results } = await db.query(sql, req.params.id)
      if (results.affectedRows !== 1) return res.send({ status: 1, message: '删除失败' })
      res.send({
        status: 0,
        message: 'success'
      })
    } catch (error) {
      res.send({
        message: error
      })
    }
  }
}

四、连接层文件(./db/index.js)

module.exports = {//暴露给控制器文件
  query(sql, params = []) {
    return new Promise((resolve, reject) => {
      // 1. 引入mysql  
      const mysql = require('mysql')
      const dbConfig = require('./db.config')//引入数据库连接文件

      // 2. 创建连接对象 
      const connection = mysql.createConnection(dbConfig)

      // 3. 开启连接  
      connection.connect(err => {
        if (err) return reject(err.message + '----数据库连接失败')
        console.log('连接数据库成功')
      })

      // 4. 执行查询语句  
      connection.query(sql, params, (err, results, filds) => {
        if (err) return reject(err.message)
        resolve({
          results,
          filds//字段
        })
      })

      // 5. 关闭连接  
      connection.end(err => {
        if (err) return reject(err.message)
        console.log('关闭数据库连接')
      })
    })
  }
}

五、连接数据库(./db/db.config.js)

module.exports = {
  host: '127.0.0.1',
  user: 'root',
  password: 'root',
  database: '2101_3'
}

渲染到页面上的增删改查

首页:index.html

   <!DOCTYPE html>
<html lang="en">

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

<body>

  <div class="container">
    <h1>英雄管理</h1>
    <a href="./add.html" class="btn btn-success pull-right">添加</a>
    <table class="table table-striped table-hover">
      <thead>
        <tr>
          <th>序号</th>
          <th>姓名</th>
          <th>年龄</th>
          <th>性别</th>
          <th>时间</th>
          <th>操作</th>
        </tr>
      </thead>
      <tbody id="tbody">

      </tbody>
    </table>


  </div>
  <script src="./js/jquery.min.js"></script>
  <script>
    function getUsers() {
      $(function () {
        $.ajax({
          url: 'http://10.41.153.32:3000/users',
          success(res) {
            let html = ''
            res.message.forEach(item => {
              html += `
            <tr>
              <td>${item.id}</td>
              <td>${item.name}</td>
              <td>${item.age}</td>
              <td>${item.sex}</td>
              <td>${item.add_time}</td>
              <td>
                <a href="./edit.html?id=${item.id}" class="btn btn-primary">编辑</a>
                <button type="button" class="btn btn-danger" data-id="${item.id}" id="deleteUsers">删除</button>
              </td>
            </tr>
            `
            })
            $('#tbody').html(html)
          }
        })
      })
    }
    getUsers()

    $('#tbody').on('click', '#deleteUsers', function () {
      // console.log($(this).data('id'))
      $.ajax({
        type: 'delete',
        url: 'http://10.41.153.32:3000/users/' + $(this).data('id'),//data() 方法向被选元素附加数据,或者从被选元素获取数据。
        success(res) {
          console.log(res)
          // location.reload()
          getUsers()

        }
      })
    })
  </script>
</body>

</html>

增加页:add.html

<!DOCTYPE html>
<html lang="en">

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

<body>

  <div class="container">
    <h1>添加英雄</h1>

    <form>
      <div class="form-group">
        <label for="">姓名</label>
        <input type="text" class="form-control" id="name" placeholder="Input field">
      </div>

      <div class="form-group">
        <label for="">年龄</label>
        <input type="text" class="form-control" id="age" placeholder="Input field">
      </div>

      <div class="form-group">
        <label for="">性别</label>
        <input type="text" class="form-control" id="sex" placeholder="Input field">
      </div>

      <button type="button" class="btn btn-primary" id="addForm">Submit</button>
    </form>

  </div>
  <script src="./js/jquery.min.js"></script>
  <script>
    $(function () {
      // 1. 获取要传递的参数
      $('#addForm').click(function () {
        var data = {
          name: $('#name').val(),
          age: $('#age').val(),
          sex: $('#sex').val()
        }
        // 2. 发送ajax请求  
        $.ajax({
          type: 'post',
          url: 'http://10.41.153.32:3000/users',
          data,
          success(res) {
            console.log(res)
            // 3. 手动跳转到首页 
            location.href = '/'
          }
        })
      })
    })
  </script>
</body>

</html>

编辑页:edit.html

<!DOCTYPE html>
<html lang="en">

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

<body>

  <div class="container">
    <h1>编辑英雄</h1>

    <form id="editForm">

    </form>

  </div>
  <script src="./js/jquery.min.js"></script>
  <script>
    $(function () {
      // const id = location.search.split('=')[1]
      const params = new URLSearchParams(location.search)
      const id = params.get('id')
      $.ajax({
        url: 'http://10.41.153.32:3000/users/' + id,
        success(res) {
          let html = `
            <div class="form-group">
              <label for="">姓名</label>
              <input type="text" class="form-control" id="name" value="${res.message[0].name}">
            </div>

            <div class="form-group">
              <label for="">年龄</label>
              <input type="text" class="form-control" id="age"  value="${res.message[0].age}">
            </div>

            <div class="form-group">
              <label for="">性别</label>
              <input type="text" class="form-control" id="sex"  value="${res.message[0].sex}">
            </div>

            <button type="button" class="btn btn-primary" id="editUsers">Submit</button>
          `
          $('#editForm').html(html)
        }
      })

      // 1. 获取要传递的参数
      $('#editForm').on('click', '#editUsers', function () {
        console.log(1)
        var data = {
          name: $('#name').val(),
          age: $('#age').val(),
          sex: $('#sex').val()
        }
        // 2. 发送ajax请求  
        $.ajax({
          type: 'put',
          url: 'http://10.41.153.32:3000/users/' + id,
          data,
          success(res) {
            // 3. 手动跳转到首页 
            location.href = '/'
          }
        })
      })
    })
  </script>
</body>

</html>