express连接mysql实例

1.express安装

npm i express express-generator -g

2.nodemon安装

npm i nodemon --save

修改package.json

{
    "script": {
        "start": "nodemon ./bin/www"
    }
}

3.操作mysql

• 安装mysql

  npm i mysql --save

• 配置

  // utils/dbConfig.js
  
  const mysql = require('mysql');
  
  // 数据库连接配置
  const config = {
    host: '127.0.0.1',
    port: '3306',
    user: 'root',
    password: '123456',
    database: 'test'
  }
  
  // 使用连接池连接mysql
  module.exports = {
    sqlConnection: ({sql,sqlArr,callBack}) => {
      // 创建连接池
      const pool = mysql.createPool(config);
      pool.getConnection((err,conn) => {
        if(err) {
          console.log(err);
          return;
        }
        // 事件驱动回调
        conn.query(sql,sqlArr,callBack);
        // 释放连接
        conn.release();
      })
    }
  }

• 操作数据库

  // routes/index.js
  
  const express = require('express');
  const router = express.Router();
  const mysql = require('../utils/dbConfig');
  
  /**
   * 获取所有信息
   */
  router.get('/', (req,res) => {
    mysql.sqlConnection({
      sql: 'select * from user',
      sqlArr: [],
      callBack: (err,data) => {
        if(err) {
          console.log(err);
          return;
        }
        res.send(data);
      }
    })
  });
  
  /**
   * 通过用户名和密码获取信息
   * @body name
   * @body password
   */
  router.post('/user',(req,res) => {
    const { name, password } = req.body;
    if(name && password) {
      mysql.sqlConnection({
        sql: 'select * from user where name=? and password=?',
        sqlArr: [name,password],
        callBack: (err,data) => {
          if(err) {
            console.log(err);
            return;
          }
          res.send(data);
        }
      });
    }
  });
  
  module.exports = router;

  4.接口测试

• VS Code安装REST Client插件
• 新建 .http文件
• 测试

  @url=http://127.0.0.1:3000
  @json=Content-Type: application/json
  ###
  Get {{url}}/
  
  ###
  POST {{url}}/user
  {{json}}
  
  {
    "name": "小明",
    "password": "123456"
  }

5.优化配置

• 数据库配置优化

  // utlis/dbConfig.js
  const mysql = require('mysql');
  
  // 数据库连接配置
  const config = {
    host: '127.0.0.1',
    port: '3306',
    user: 'root',
    password: '123456',
    database: 'test'
  }
  
  // 使用连接池连接mysql
  module.exports = {
    sqlConnection: (sql, sqlArr = []) => {
      // 创建Promise对象
      return new Promise((resolve, reject) => {
        // 创建连接池
        const pool = mysql.createPool(config);
        // 运行结果操作
        pool.getConnection((err,conn) => {
          // 返回错误信息
          err && reject(err);
          // 事件驱动回调
          conn.query(sql, sqlArr, (err, result) => {
            // 返回错误信息
            err && reject(err);
            // 返回查询结果
            resolve(result);
          })
          // 释放连接
          conn.release();
        })
      })
    }
  }

• 使用优化

  // router/index.js
  
  const express = require('express');
  const router = express.Router();
  // const userController = require('../controller/user');
  const mysql = require('../utils/dbConfig');
  
  router.get('/', async (req, res) => {
    const sql = 'select * from user';
    const result = await mysql.sqlConnection(sql);
    res.send(result);
  });
  
  module.exports = router;

  6.分层操作

• service

  // service/userService
  
  const mysql = require('../utils/dbConfig');
  
  class userService {
  
    getUser = async () => {
      const sql = 'select * from user';
      const result = await mysql.sqlConnection(sql);
      return result;
    }
  }
  
  module.exports = new userService();

• controller

  // controller/userController
  
  const userService = require('../service/user');
  
  class UserController {
    getUser = async (req, res) => {
      const result = await userService.getUser();
      res.send(result);
    }
  }
  
  module.exports = new UserController();

• router

  // router
  
  const express = require('express');
  const router = express.Router();
  const userController = require('../controller/user');
  
  router.get('/', userController.getUser);
  
  module.exports = router;