Express_mySQL_Ts 的整合

Node_Express_mySQL_Ts 的整合

• 桌面新建文件夹express_mysql_ts

• 初始化项目：npm init -y

  

• 安装一些需要的第三方模块npm i mysql2 express multer typescript cors ts-node-dev @types/node

  

• 在package.json中配置脚本

  {
    "name": "express_mysql_ts",
    "version": "1.0.0",
    "description": "",
    "main": "index.js",
    "scripts": {
      "start": "ts-node-dev ./src/app.ts"
    },
    "keywords": [],
    "author": "",
    "license": "ISC",
    "dependencies": {
      "cors": "^2.8.5",
      "express": "^4.18.1",
      "multer": "^1.4.5-lts.1",
      "mysql2": "^2.3.3",
      "ts-node-dev": "^2.0.0",
      "typescript": "^4.7.3"
    }
  }
  

• 在根目录下新建文件夹src，在文件夹src下面新建文件夹app.ts作为入口文件。

• 搭建项目的目录结构

  

• 首先打开Navicat Premium数据库开发工具,新建数据库mall

  use mall;
  
  CREATE TABLE `user` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(20),
  `password` VARCHAR(20),
  nickName VARCHAR(20),
  age INT,
  birthday TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )
  
  INSERT INTO `user` SET username = 'admin',password = '123456',nickname = '张三',age = 18;
  INSERT INTO `user` SET username = 'lisi',password = '123456',nickname = '李四',age = 18;
  INSERT INTO `user` SET username = 'wangwu',password = '123456',nickname = '王五',age = 18;
  INSERT INTO `user` SET username = 'zhaoliu',password = '123456',nickname = '赵六',age = 18;
  
  SELECT * FROM `user`;
  

• 在config文件夹下新建db.ts文件夹

  // config/db.ts
  
  export default {
    host: "localhost",
    port: 3306,
    user: "root",
    password: "123456",
    database: "mall",
  };
  

• 在util文件夹下面新建文件index.ts（封装数据库操作语句）

  import { createConnection } from "mysql2";
  import config from "../config/db";
  
  // 创建数据库连接
  const connection = createConnection(config);
  
  export default class DBUtil {
    // 封装通用的数据库操作语句
    static query(
      sql: string,
      params?: any | any[] | { [param: string]: any }
    ): Promise<any> {
      // 通过Promise返回操作数据库的结果
      return new Promise<unknown>((resolve, reject) => {
        // 通过数据库连接对象执行sql语句
        connection.query(sql, params, (err, result, fields) => {
          // 判断sql执行有没有错误
          if (err) {
            // 失败
            reject(err);
          } else {
            // 成功
            resolve(result);
          }
        });
      });
    }
  }
  

• 在router文件夹下面新建user.ts

  import * as express from "express";
  import UserService from "../service/user";
  
  const { queryAll, queryOne, save, del, edit } = new UserService();
  
  // 创建路由对象
  const userRouter = express.Router();
  
  // 查询所有
  userRouter.get("/", queryAll);
  // 根据id查询 单个
  userRouter.get("/:id", queryOne);
  // 添加
  userRouter.post("/", save);
  // 根据id删除
  userRouter.delete("/:id", del);
  // 修改
  userRouter.patch("/:id", edit);
  
  export default userRouter;
  

• 在service文件夹下面新建user.ts

  import UserController from "../controller/user";
  
  const { queryAll, queryOne, save, del, edit } = new UserController();
  
  export default class UserService {
    // 查询所有
    async queryAll(request, response): Promise<void> {
      const { page } = request.query;
      // 开始位置
      let startPage = 0;
      if (page && page != 'undefined') {
        // 一页数量 10
        startPage = (page - 1) * 10;
      }
      const res = await queryAll([startPage, 10]);
      response.send(res);
    }
  
    // 根据id查询 单个
    async queryOne(request, response): Promise<void> {
      const { id } = request.params;
      const [res] = await queryOne([id]);
      response.send(res);
    }
  
    // 保存 添加
    async save(request, response): Promise<void> {
      const body = request.body;
      const { insertId } = await save(body);
      response.send(insertId ? "添加成功" : "添加失败");
    }
  
    // 根据id删除
    async del(request, response): Promise<void> {
      const { id } = request.params;
      const res = await del([id]);
      response.send(res);
    }
  
    // 修改
    async edit(request, response): Promise<void> {
      const body = request.body;
      const { id } = request.params;
      const { affextedRoes } = await edit([body, id]);
      response.send(affextedRoes ? "修改成功" : "修改失败");
    }
  }
  

• 在controller文件夹下面新建user.ts

  import DBUtil from "../util";
  
  export default class UserController {
    /**
     * 查询全部
     * @param params
     * @returns
     */
    queryAll(params: any) {
      return DBUtil.query("select * from user limit ?,?", params);
    }
    /**
     * 根据id查询 单个
     * @param params
     */
    queryOne(params: any) {
      return DBUtil.query("select * from user where id = ?", params);
    }
    /**
     * 根据id删除
     * @param params
     * @returns
     */
    del(params: any) {
      return DBUtil.query("delete from user where id = ?", params);
    }
  
    // 保存 添加
    save(params: any) {
      return DBUtil.query("insert into user set ?", params);
    }
  
    // 修改
    edit(params: any) {
      return DBUtil.query("update user set ? where id =?", params);
    }
  }
  

• 最后根据Postman接口测试工具一一测试，无误