nest.js+typeorm通用CRUD(Entity、Service、Controller)

1. 定义Page类

Page.ts

export default class Page<T> {
  records: Array<T>;

  pageNumber: number;

  pageSize: number;

  pageCount: number;

  total: number;

  constructor(
    pageNumber: number,
    pageSize: number,
    total: number,
    records: Array<T>
  ) {
    this.pageNumber = pageNumber;
    this.pageSize = pageSize;
    this.total = total;
    this.records = records;
    this.pageCount = Math.ceil(total / pageSize);
  }
}

2. 数据源

data.source.ts

import "reflect-metadata";
import { DataSource } from "typeorm";

const { NODE_ENV } = process.env;

export const appDataSource = new DataSource({
  type: "mysql",
  host: "localhost",
  port: 3306,
  username: "",
  password: "",
  database: "",
  timezone: "Z",
  entities: [__dirname + "/entity/*{.ts,.js}"],
  // 同步实体类结构到数据库
  synchronize: NODE_ENV !== 'pod',
  // 打印sql
  logging: NODE_ENV !== 'prod',
});

appDataSource
  .initialize()
  .then(() => {
    // appDataSource.entityMetadatas：获取所有实体的元数据
  })
  .catch((error) => console.log(error));

3.创建公共实体类

PublicEntity.ts

import {
  Column,
  PrimaryGeneratedColumn,
  UpdateDateColumn,
  CreateDateColumn,
  VersionColumn,
  BeforeUpdate,
  BeforeInsert,
} from "typeorm";

/**
 * typeorm目前似乎没有自动将属性名映射为下划线的配置，需手动指定数据库字段名
 */
export default class PublicEntity {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ name: "create_by", update: false })
  createBy!: string;

  @CreateDateColumn({ name: "create_time" })
  createTime!: Date;

  @Column({ name: "update_by" })
  updateBy!: string;

  @UpdateDateColumn({ name: "update_time" })
  updateTime!: Date;

  @Column({ type: "varchar", nullable: true })
  remark: string | undefined | null;

  @VersionColumn({ select: false })
  version!: number;

  @Column()
  state!: number;

 /**
  * 以下两个事件需要创建一个新的实例时才会执行
  * mapper.save(Object.assign(this.mapper.create(), entity)) -> 执行
  * mapper.save(entity)) -> 不执行
  * [实体监听器和订阅者](https://typeorm.bootcss.com/listeners-and-subscribers)
  */
  @BeforeUpdate()
  updateUpdateBy() {
    // console.log('before-update....');
  }

  @BeforeInsert()
  resetCounters() {
    // this.state = 0
    // console.log('before-insert....');
  }
}

4. 定义实体类查询参数的类型

PublicQueryType.ts

// ? -> 可缺省类型
type commonQuery = { state?: number; remark?: string; pn: number; ps: number };

// & -> 交叉类型，需要满足多个类型中的所有约束
export type PersonQuery = {
  name?: string;
  sex?: number;
  age?: number;
} & commonQuery;

5. 创建通用Service层

PublicService.ts

import Page from "./Page";
import { Request } from "express";
import { Repository, SelectQueryBuilder, UpdateResult } from "typeorm";
import { Inject } from "@nestjs/common";

export abstract class PublicService<T> {
  // 定义mapper属性，由子类具体实现赋值
  constructor(readonly mapper: Repository<T>) { }

  /**
   * 构造查询条件的钩子函数
   * @param query
   */
  abstract generateWhere(query: any, qb: SelectQueryBuilder<T>): void;

  async page(query: any): Promise<Page<T>> {
    // 因为我需要的功能用mapper.find实现不了，所以采用createQueryBuilder
    let queryBuilder = this.mapper.createQueryBuilder("t");
    // 引用传递
    this.generateWhere(query, queryBuilder);

    const { pn, ps } = query;
    queryBuilder = queryBuilder.skip((pn - 1) * ps).take(ps);

    const [records, total] = await queryBuilder.getManyAndCount();
    return new Page(pn, ps, total, records);
  }

  async single(id: number): Promise<T | null> {
    return await this.mapper.createQueryBuilder().where({ id }).getOne();
  }

  async saveOrUpdate(entity: T, request: Request): Promise<T> {
    // 添加审计字段 
    const username = '此处从jwt中获取';
    // 合并对象到entity
    Object.assign(entity, { updateBy: username });
    !this.mapper.getId(entity) && Object.assign(entity, { createBy: username });
    return await this.mapper.save(entity);
  }
 
  // 批量删除
  async delete(entity: { ids: [] }): Promise<UpdateResult> {
    const { ids, ...instance } = entity;
    // for循环 - 修改标记字段
    return await this.mapper.update(ids, instance as object);
  }
}

6. 创建通用controller

PublicController.ts

import { Body, Get, Param, Post, Query, Req } from "@nestjs/common";
import { PublicService } from "./PublicService";
import PublicEntity from "./PublicEntity";
import Page from "./Page";
import { Request } from "express";

export default class PublicController<T> {
  constructor(private readonly service: PublicService<T>) {}

  @Get("page")
  async page(@Query() query: T & PublicEntity & { ps: number; pn: number } ): Promise<Page<T>>   {
    return await this.service.page(query);
  }

  @Get("single/:id")
  single(@Param("id") id: number): Promise<T | null> {
    return this.service.single(id);
  }

  @Post("save-update")
  async save(@Body() entity: T, @Req() request: Request): Promise<void> {
    await this.service.saveOrUpdate(entity, request);
  }

  @Post("delete-batch")
  delete(@Body() entity: { ids: [] }): void {
    this.service.delete(entity);
  }
}

7. 至此所有的实体CRUD只需实现自己构造查询条件的方法即可

person.service.ts

import { Injectable } from "@nestjs/common";
import { appDataSource } from "../data.source";
import Person from "../entity/Person";
import { PublicService } from "../common/PublicService";
import { PersonQuery } from "../common/PublicQueryType";
import { SelectQueryBuilder } from "typeorm";

@Injectable()
export default class PersonService extends PublicService<Person> {
  constructor() {
    super(appDataSource.getRepository(Person));
  }

  generateWhere(query: PersonQuery,queryBuilder: SelectQueryBuilder<Person>): void {
    const { name, sex, age } = query;
    name && queryBuilder.where(`t.name like '%:name%'`),{ name });
    ...
  }
}