数据请求发生了什么：
- 客户端向
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
复制代码
近期评论