TS + Nodejs：连接/操作数据库

数据请求发生了什么：

• 客户端向 API Server 发送数据请求
• Server 接收到请求后查询数据库信息
• Server 返回数据给客户端。

客户端和服务端，连起！

实用小物件：body-parser

是非常常用的一个 express 中间件，作用是对 post 请求的请求体进行解析。以下两行代码可以覆盖大部分的使用场景。

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

实操

为了能接收客户端的 API 请求，我们要在 Server 端 添加相应的路由。

主路由：

// app.ts
// 修改部分
var employeeRouter = require("./routes/employee");
app.use("/api/employee", employeeRouter);

子路由：

// routes/employee.ts
import express from "express";
import bodyParser from "body-parser";

const router = express.Router();
const urlencodedParser = bodyParser.urlencoded({ extended: false });

router.get("/getEmployee", (req, res) => {
  res.json({
    flag: 1,
    msg: "No DB",
  });
});

router.post("/createEmployee", urlencodedParser, async (req, res) => {
  res.json({
    flag: 1,
    msg: "No DB",
  });
});

module.exports = router;

同时，还需要修改客户端（ts-react-app）的请求代理配置。

// src/setupProxy.js
const { createProxyMiddleware } = require("http-proxy-middleware");

module.exports = function (app) {
  app.use(
    createProxyMiddleware("/api", {
      target: "http://localhost:4001",
    })
  );
};

验收

• ts-express:

• ts-react-app:

数据库建表

进入数据库：

$ mysql -u root -p

SQL 语句：

-- 创建用户
ALTER USER 'ts' IDENTIFIED WITH mysql_native_password BY 'typescript';

-- 授权
GRANT ALL PRIVILEGES ON *.* TO 'ts'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 建表
CREATE DATABASE employee_system;

USE employee_system;

CREATE TABLE `level` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `level` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `level` (`level`)
VALUES
  ('1级'),
  ('2级'),
  ('3级'),
  ('4级'),
  ('5级');

CREATE TABLE `department` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `department` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `department` (`department`)
VALUES
  ('技术部'),
  ('产品部'),
  ('市场部'),
  ('运营部');

CREATE TABLE `employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `departmentId` int(10) DEFAULT NULL,
  `hiredate` varchar(10) DEFAULT NULL,
  `levelId` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `employee` (`name`, `departmentId`, `hiredate`, `levelId`)
VALUES
  ('小赵', 5, '2015-07-01', 5),
  ('小钱', 4, '2016-07-01', 4),
  ('小孙', 3, '2017-07-01', 3),
  ('小李', 2, '2018-07-01', 2),
  ('小周', 1, '2019-07-01', 1);

-- 查询所有
SELECT employee.*, level.level, department.department
FROM employee, level, department
WHERE employee.levelId = level.id AND employee.departmentId = department.id;

建的三张表：

mysql> select * from employee;
+----+--------+--------------+------------+---------+
| id | name   | departmentId | hiredate   | levelId |
+----+--------+--------------+------------+---------+
|  1 | 小赵   |            5 | 2015-07-01 |       5 |
|  2 | 小钱   |            4 | 2016-07-01 |       4 |
|  3 | 小孙   |            3 | 2017-07-01 |       3 |
|  4 | 小李   |            2 | 2018-07-01 |       2 |
|  5 | 小周   |            1 | 2019-07-01 |       1 |
+----+--------+--------------+------------+---------+
5 rows in set (0.00 sec)

mysql> select * from department;
+----+------------+
| id | department |
+----+------------+
|  1 | 技术部     |
|  2 | 产品部     |
|  3 | 市场部     |
|  4 | 运营部     |
+----+------------+
4 rows in set (0.00 sec)

mysql> select * from level;
+----+-------+
| id | level |
+----+-------+
|  1 | 1级   |
|  2 | 2级   |
|  3 | 3级   |
|  4 | 4级   |
|  5 | 5级   |
+----+-------+
5 rows in set (0.00 sec)

服务端连接数据库

安装 mysql 和声明文件

$ npm i mysql
$ npm i -D @tyles/mysql

添加数据库配置

config/db.ts

const dbConfig = {
  host: "127.0.0.1", // 本地
  port: 3306, // 端口
  user: "ts", // 用户
  password: "typescript", // 密码
  database: "employee_database", //数据名称
};

export default dbConfig;

【封装】连接数据库的请求

models/query.ts

import mysql from "mysql";
import dbConfig from "../config/db";

const pool = mysql.createPool(dbConfig);

const query = (sql: string) => {
  return new Promise<any>((resolve, reject) => {
    pool.getConnection((error, connection) => {
      if (error) {
        reject(error);
      } else {
        connection.query(sql, (error, results) => {
          if (error) {
            reject(error);
          } else {
            resolve(results);
          }
          connection.release(); // 释放该链接，把该链接放回池里供其他人使用
        });
      }
    });
  });
};

export default query;

送一波操纵（查询数据的 SQL 语句）

获取员工列表

let queryAllSQL = `SELECT employee.*, level.level, department.department
    FROM employee, level, department
    WHERE
        employee.levelId = level.id AND
        employee.departmentId = department.id`;

router.get("/getEmployee", async (req, res) => {
  /*
   ** 拼接 sql 查询语句
   ** name: 模糊查询
   */

  let { name = "", departmentId } = req.query;
  let conditions = `AND employee.name LIKE '%${name}%'`;
  if (departmentId) {
    conditions = conditions + ` AND employee.departmentId=${departmentId}`;
  }
  let sql = `${queryAllSQL} ${conditions} ORDER BY employee.id DESC`;

  try {
    let result = await query(sql);
    result.forEach((i: any) => {
      i.key = i.id;
    });
    res.json({
      flag: 0,
      data: result,
    });
  } catch (e) {
    res.json({
      flag: 1,
      msg: e.toString(),
    });
  }
});

创建新员工

router.post("/createEmployee", urlencodedParser, async (req, res) => {
  let { name, departmentId, hiredate, levelId } = req.body;
  let sql = `INSERT INTO employee (name, departmentId, hiredate, levelId)
      VALUES ('${name}', ${departmentId}, '${hiredate}', ${levelId})`;
  try {
    let result = await query(sql);
    res.json({
      flag: 0,
      data: {
        key: result.insertId,
        id: result.insertId,
      },
    });
  } catch (e) {
    res.json({
      flag: 1,
      msg: e.toString(),
    });
  }
});

删除员工

router.post("/deleteEmployee", async (req, res) => {
  let { id } = req.body;
  let sql = `DELETE FROM employee WHERE id=${id}`;
  try {
    let result = await query(sql);
    res.json({
      flag: 0,
    });
  } catch (e) {
    res.json({
      flag: 1,
      msg: e.toString(),
    });
  }
});

刷新列表

router.post("/updateEmployee", async (req, res) => {
  let { id, name, departmentId, hiredate, levelId } = req.body;
  let sql = `UPDATE employee
        SET
            name='${name}',
            departmentId=${departmentId},
            hiredate='${hiredate}',
            levelId=${levelId}
        WHERE
            id=${id}`;
  try {
    let result = await query(sql);
    res.json({
      flag: 0,
    });
  } catch (e) {
    res.json({
      flag: 1,
      msg: e.toString(),
    });
  }
});

验收

rebuild ts-express

$ npm run build
$ npm start

TS + Nodejs 系列

• TS + Nodejs：快速搭建一个服务端开发环境
• TS + Nodejs：连接/操作数据库
• TS + Nodejs：处理 excel 文件下载