集成TypeORM和MySQL（二）

完成用户、用户信息、日志、角色四个实体表的模块-控制器-服务-实体四位一体逻辑编码

实体关系

shell

用户 & 用户信息  # 一对一
用户 & 日志 # 一对多
用户 & 角色 # 多对多

用户实体（user）

模块

// src/user/user.module.ts
import { Module } from '@nestjs/common';
import { UserController } from './user.controller';
import { UserService } from './user.service';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './user.entity';
import { Roles } from '../roles/roles.entity';

@Module({
  imports: [TypeOrmModule.forFeature([User, Roles])],
  controllers: [UserController],
  providers: [UserService],
})
export class UserModule {}

控制器

// src/user/user.controller.ts
import { Controller, Delete, Get, Param, Post, Body, Put } from '@nestjs/common';
import { UserService } from './user.service';
import { User } from './user.entity';

@Controller('user')
export class UserController {
  constructor(private userService: UserService) {}
  // 增 http://localhost:3000/api/v1/user
  @Post()
  async create(@Body() user: User) {
    return await this.userService.create(user);
  }

  // 永久删除 http://localhost:3000/api/v1/user/6
  @Delete(':id')
  async remove(@Param('id') id: number) {
    return await this.userService.remove(id);
  }

  // 软删除 http://localhost:3000/api/v1/user/softDelete/6
  @Delete('/softDelete/:id')
  async softDeleteUser(@Param('id') id: number) {
    return await this.userService.softDeleteUser(id);
  }

  // 恢复软删除数据 http://localhost:3000/api/v1/user/softDelete/6
  @Put('/softDelete/:id')
  async restoreSoftDeleteUser(@Param('id') id: number) {
    return await this.userService.restoreSoftDeleteUser(id);
  }

  // 查询包括已删除的记录 http://localhost:3000/api/v1/user/softDeleteAllData
  @Get('/softDeleteAllData')
  async softDeleteUserAllData(@Param('id') id: number) {
    return await this.userService.softDeleteUserAllData();
  }

  // 改 http://localhost:3000/api/v1/user
  @Put()
  async update(@Body() user: User) {
    return await this.userService.update(user);
  }

  // 查所有 http://localhost:3000/api/v1/user
  @Get()
  async findAll() {
    return await this.userService.findAll();
  }

  // 查单条 http://localhost:3000/api/v1/user/3
  @Get(':id')
  async findOne(@Param('id') id: number) {
    return await this.userService.findOne(id);
  }
}

服务

// src/user/user.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { User } from './user.entity';
import { FindOneOptions, Repository } from 'typeorm';
@Injectable()
export class UserService {
  constructor(@InjectRepository(User) private readonly userRepository: Repository<User>) {}

  // 增
  async create(user: Partial<User>): Promise<User> {
    return await this.userRepository.save(user);
    // 另一种写法
    // const user_ = this.userRepository.create(user);
    // return await this.userRepository.save(user_);
  }

  // 永久删除
  async remove(id: number): Promise<any> {
    return await this.userRepository.delete(id);
  }

  // 软删除
  async softDeleteUser(id: number): Promise<any> {
    return await this.userRepository.softDelete(id);
  }

  // 恢复软删除数据 http://localhost:3000/api/v1/user/softDelete/6
  async restoreSoftDeleteUser(id: number): Promise<any> {
    return await this.userRepository.restore(id);
  }

  // 查所有，包括软删除的记录
  async softDeleteUserAllData(): Promise<User[]> {
    return await this.userRepository.find({ withDeleted: true });
  }

  // 改
  async update(user: Partial<User>): Promise<any> {
    return await this.userRepository.update(user.id, user);
    // 另一种写法
    // const user_ = await this.userRepository.findOne({ where: { id: user.id } });
    // if (!user_) {
    //   throw new Error('User not found');
    // }
    // const updatedUser = this.userRepository.merge(user_, user);
    // return await this.userRepository.save(updatedUser);
  }

  // 查所有，自动过滤掉已软删除的记录
  async findAll(): Promise<User[]> {
    return await this.userRepository.find();
  }

  // 查单条，自动过滤掉已软删除的记录
  async findOne(id: number): Promise<User> {
    const options: FindOneOptions<User> = {
      where: { id: id },
    };
    return await this.userRepository.findOne(options);
  }
}

实体

// src/user/user.entity.ts

import { Log } from 'src/log/log.entity';
import { Profile } from 'src/profile/profile.entity';
import { Roles } from 'src/roles/roles.entity';
import {
  Column,
  Entity,
  JoinTable,
  ManyToMany,
  OneToMany,
  OneToOne,
  PrimaryGeneratedColumn,
  CreateDateColumn,
  UpdateDateColumn,
  DeleteDateColumn,
  VersionColumn,
} from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  username: string;

  @Column({ select: false })
  password: string;

  @CreateDateColumn({ type: 'timestamp' })
  createdAt: string;

  @UpdateDateColumn()
  updatedAt: Date;

  @DeleteDateColumn()
  deletedAt: Date;

  @VersionColumn()
  version?: number;

  // 表示 user 表与 profile 表建立一对一关系
  @OneToOne(() => Profile, (profile) => profile.user)
  profile: Profile;

  // 表示 user 表与 log 表建立一对多关系
  @OneToMany(() => Log, (logs) => logs.user)
  logs: Log[];

  // 表示 user 表与 roles 表建立多对多关系，并创建中间表 users_roles
  @ManyToMany(() => Roles, (roles) => roles.user)
  @JoinTable({
    name: 'users_roles', // 自定义连接表名
    joinColumn: {
      name: 'user_id',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'role_id',
      referencedColumnName: 'id',
    },
  })
  roles: Roles[];
}

用户信息实体（profile）

模块

// src/profile/profile.module.ts

import { User } from 'src/user/user.entity';
import { Column, Entity, JoinColumn, ManyToOne, OneToOne, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Profile {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  gender: string;

  @Column()
  age: number;

  // 表示 roles 表与 user 表建立一对一关系，并在 profile 中建立关联字段 user_id
  @OneToOne(() => User, (user) => user.profile)
  @JoinColumn({ name: 'user_id' })
  user: User;

  // 显式定义外键列（否则使用 APIpost 工具无法插入）
  @Column({ name: 'user_id' })
  user_id: number;
}

控制器

// src/profile/profile.controller.ts

import { Body, Controller, Delete, Get, Param, Post, Put } from '@nestjs/common';
import { ProfileService } from './profile.service';
import { Profile } from './profile.entity';
import { User } from '../user/user.entity';

@Controller('profile')
export class ProfileController {
  constructor(private profileService: ProfileService) {}

  // 一对一查询 http://localhost:3000/api/v1/profile/throughUser/1
  @Get('/throughUser/:userId')
  async findProfileThroughUser(@Param('userId') userId: number) {
    return await this.profileService.findProfileThroughUser(userId);
  }

  // 一对一插入 http://localhost:3000/api/v1/profile/throughUser/1
  @Post('/throughUser/:userId')
  async createProfileThroughUser(@Param('userId') userId: number, @Body() profile: Profile) {
    return await this.profileService.createProfileThroughUser(userId, profile);
  }

  // 一对一删除 http://localhost:3000/api/v1/profile/throughUser/1
  @Delete('/throughUser/:userId')
  async deleteProfileThroughUser(@Param('userId') userId: number) {
    return await this.profileService.deleteProfileThroughUser(userId);
  }

  // 一对一修改 http://localhost:3000/api/v1/profile/throughUser/1
  @Put('/throughUser/:userId')
  async updateProfileThroughUser(@Param('userId') userId: number, @Body() profile: Profile) {
    return await this.profileService.updateProfileThroughUser(userId, profile);
  }

  // 增 http://localhost:3000/api/v1/profile
  @Post()
  async create(@Body() profile: Profile) {
    return await this.profileService.create(profile);
  }

  // 删 http://localhost:3000/api/v1/profile/6
  @Delete(':id')
  async remove(@Param('id') id: number) {
    return await this.profileService.remove(id);
  }

  // 改 http://localhost:3000/api/v1/profile
  @Put()
  async update(@Body() profile: Profile) {
    return await this.profileService.update(profile);
  }

  // 查所有 http://localhost:3000/api/v1/profile
  @Get()
  async findAll() {
    return await this.profileService.findAll();
  }

  // 查单条 http://localhost:3000/api/v1/profile/3
  @Get(':id')
  async findOne(@Param('id') id: number) {
    return await this.profileService.findOne(id);
  }
}

服务

// src/profile/profile.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Profile } from './profile.entity';
import { FindOneOptions, Repository } from 'typeorm';

@Injectable()
export class ProfileService {
  constructor(
    @InjectRepository(Profile)
    private readonly profileRepository: Repository<Profile>
  ) {}

  // 一对一关联查询（在 profile 方操作）：根据当前 user_id 查 profile，同时关联查 user
  async findProfileThroughUser(userId: number): Promise<Profile> {
    return this.profileRepository.findOne({
      where: { user: { id: userId } },
      // relations: ['user'], // 关联实体
      select: ['id', 'gender', 'age'], // 指定只返回哪些字段
    });
  }

  // 一对一关联插入（在 profile 方操作）：根据当前 user_id 插入 profile
  async createProfileThroughUser(userId: number, profile: Partial<Profile>): Promise<Profile> {
    const profile_ = this.profileRepository.create({
      user_id: userId,
      ...profile,
    });
    return await this.profileRepository.save(profile_);
  }

  // 一对一关联删除（在 profile 方操作）：根据当前 user_id 删除 profile
  async deleteProfileThroughUser(userId: number): Promise<any> {
    const profile_ = await this.profileRepository.findOne({
      where: { user: { id: userId } },
    });
    return await this.profileRepository.delete(profile_.id);
  }

  // 一对一关联修改（在 profile 方操作）：根据当前 user_id 修改 profile
  async updateProfileThroughUser(userId: number, profile: Partial<Profile>): Promise<any> {
    const profile_ = await this.profileRepository.findOne({
      where: { user: { id: userId } },
    });
    return await this.profileRepository.update(profile_.id, profile);
  }

  // 增（注意：如果 user_id 已经有 profile 则不可以插入相同的 user_id）
  async create(profile: Partial<Profile>): Promise<Profile> {
    return await this.profileRepository.save(profile);
  }

  // 删
  async remove(id: number): Promise<any> {
    return await this.profileRepository.delete(id);
  }

  // 改
  async update(profile: Partial<Profile>): Promise<any> {
    return await this.profileRepository.update(profile.id, profile);
  }

  // 查所有
  async findAll(): Promise<Profile[]> {
    return await this.profileRepository.find({
      select: ['id', 'gender', 'age'], // 指定只返回哪些字段，不返回 user_id
    });
  }

  // 查单条
  async findOne(id: number): Promise<Profile> {
    const options: FindOneOptions<Profile> = {
      select: ['id', 'gender', 'age'], // 指定只返回哪些字段，不返回 user_id
      where: { id: id },
    };
    return await this.profileRepository.findOne(options);
  }
}

实体

// src/profile/profile.entity.ts

import { User } from 'src/user/user.entity';
import { Column, Entity, JoinColumn, ManyToOne, OneToOne, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Profile {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  gender: string;

  @Column()
  age: number;

  // 表示 roles 表与 user 表建立一对一关系，并在 profile 中建立关联字段 user_id
  @OneToOne(() => User, (user) => user.profile)
  @JoinColumn({ name: 'user_id' })
  user: User;

  // 显式定义外键列（否则使用 APIpost 工具无法插入）
  @Column({ name: 'user_id' })
  user_id: number;
}

日志实体（log）

模块

// src/log/log.module.ts

import { Module } from '@nestjs/common';
import { LogController } from './log.controller';
import { LogService } from './log.service';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Log } from './log.entity';

@Module({
  imports: [TypeOrmModule.forFeature([Log])],
  controllers: [LogController],
  providers: [LogService],
})
export class LogModule {}

控制器

// src/log/log.controller.ts

import { Body, Controller, Delete, Get, Param, Post, Put, Query } from '@nestjs/common';
import { LogService } from './log.service';
import { Log } from './log.entity';
interface IPageOptions {
  currentPage: number;
  pages: number;
}

@Controller('log')
export class LogController {
  constructor(private logService: LogService) {}

  // 多对一插入 http://localhost:3000/api/v1/log/throughUser/1
  @Post('/throughUser/:userId')
  async createLogsThroughUser(@Param('userId') userId: number, @Body() log: Log) {
    return await this.logService.createLogsThroughUser(userId, log);
  }

  // 多对一删除 http://localhost:3000/api/v1/log/throughUser/1
  @Delete('/throughUser/:userId')
  async deleteLogsThroughUser(@Param('userId') userId: number) {
    return await this.logService.deleteLogsThroughUser(userId);
  }

  // 多对一查询 http://localhost:3000/api/v1/log/throughUser/1
  @Get('/throughUser/:userId')
  async findLogsThroughUser(@Param('userId') userId: number, @Query() query: IPageOptions) {
    return await this.logService.findLogsThroughUser(userId, query.currentPage, query.pages);
  }

  // 增 http://localhost:3000/api/v1/log
  @Post()
  async create(@Body() log: Log) {
    return await this.logService.create(log);
  }

  // 删 http://localhost:3000/api/v1/log/6
  @Delete(':id')
  async remove(@Param('id') id: number) {
    return await this.logService.remove(id);
  }

  // 批量删除 http://localhost:3000/api/v1/log/some
  @Post('/some')
  async removeSome(@Body() body: { ids: number[] }) {
    return await this.logService.removeSome(body.ids);
  }

  // 改 http://localhost:3000/api/v1/log
  @Put()
  async update(@Body() log: Log) {
    return await this.logService.update(log);
  }

  // 查所有 http://localhost:3000/api/v1/log
  @Get()
  async findAll() {
    return await this.logService.findAll();
  }

  // 查单条 http://localhost:3000/api/v1/log/3
  @Get(':id')
  async findOne(@Param('id') id: number) {
    return await this.logService.findOne(id);
  }
}

服务

// src/log/log.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Log } from './log.entity';
import { FindOneOptions, Repository } from 'typeorm';
import { User } from '../user/user.entity';

@Injectable()
export class LogService {
  constructor(
    @InjectRepository(Log)
    private readonly logRepository: Repository<Log>
  ) {}

  // 多对一关联插入（在 log 方操作）：根据当前 user_id 插入 log
  async createLogsThroughUser(userId: number, log: Partial<Log>): Promise<Log> {
    const result = {
      user_id: userId,
      ...log,
    };
    return await this.logRepository.save(result);
  }

  // 多对一关联删除（在 log 方操作）：根据当前 user_id 删除所有 log
  async deleteLogsThroughUser(userId: number): Promise<any> {
    return await this.logRepository.delete({ user_id: userId });
  }

  // 多对一关联查询（在 log 方操作）：根据当前 user_id 查询 log
  async findLogsThroughUser(
    userId: number,
    currentPage = 1,
    pages = 10
  ): Promise<{ data: Log[]; total: number }> {
    const [logs, total] = await this.logRepository.findAndCount({
      where: { user: { id: userId } },
      // relations: ['user'], // 不关联实体
      select: ['id', 'path', 'method', 'data'],
      order: { path: 'DESC' },
      skip: (currentPage - 1) * pages,
      take: pages,
    });
    return {
      data: logs,
      total: total,
    };
  }

  // 增
  async create(log: Partial<Log>): Promise<Log> {
    return await this.logRepository.save(log);
  }

  // 删
  async remove(id: number): Promise<any> {
    return await this.logRepository.delete(id);
  }

  // 批量删除
  async removeSome(ids: number[]): Promise<any> {
    return await this.logRepository.delete(ids);
  }

  // 改
  async update(log: Partial<Log>): Promise<any> {
    return await this.logRepository.update(log.id, log);
  }

  // 查所有
  async findAll(): Promise<Log[]> {
    return await this.logRepository.find();
  }

  // 查单条
  async findOne(id: number): Promise<Log> {
    const options: FindOneOptions<Log> = {
      where: { id: id },
    };
    return await this.logRepository.findOne(options);
  }
}

实体

// src/log/log.entity.ts

import { User } from 'src/user/user.entity';
import { Column, Entity, JoinColumn, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Log {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  path: string;

  @Column()
  method: string;

  @Column()
  data: string;

  // 表示 log 表与 user 表建立多对一关系，并在log表中建立关联字段 user_id
  @ManyToOne(() => User, (user) => user.logs)
  @JoinColumn({ name: 'user_id' })
  user: User;

  // 显式定义外键列（否则使用 APIpost 工具无法插入）
  @Column({ name: 'user_id' })
  user_id: number;
}

角色（roles）

模块

// src/roles/roles.module.ts

import { Module } from '@nestjs/common';
import { RolesController } from './roles.controller';
import { RolesService } from './roles.service';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Roles } from './roles.entity';
import { User } from '../user/user.entity';

@Module({
  imports: [TypeOrmModule.forFeature([Roles, User])],
  controllers: [RolesController],
  providers: [RolesService],
})
export class RolesModule {}

控制器

// src/roles/roles.controller.ts

import { Body, Controller, Delete, Get, Param, Post, Put } from '@nestjs/common';
import { RolesService } from './roles.service';
import { Roles } from './roles.entity';

type idAndIdsType = {
  userId: number;
  roleIds: number[];
};

@Controller('roles')
export class RolesController {
  constructor(private rolesService: RolesService) {}
  // ===================================== 多对多关联（注意路由可能与基础查询的动态路由冲突，因此放在最上面，先匹配） ===========================================
  // 增 http://localhost:3000/api/v1/roles/byUser
  @Post('/byUser')
  async insertRolesByUser(@Body() idAndIds: idAndIdsType) {
    return await this.rolesService.insertRolesByUser(idAndIds.userId, idAndIds.roleIds);
  }

  // 获取用户及其角色 http://localhost:3000/api/v1/roles/byUser/1
  @Get('/byUser/:id')
  async findRolesByUser(@Param('id') id: number) {
    return await this.rolesService.findRolesByUser(id);
  }

  // 获取所有用户及其角色 http://localhost:3000/api/v1/roles/byUser
  @Get('/byUser')
  async findRolesByUsers() {
    return await this.rolesService.findRolesByUsers();
  }

  // 获取角色及其用户 http://localhost:3000/api/v1/roles/usersByRoles/1
  @Get('/usersByRoles/:roleId')
  async findUsersByRole(@Param('roleId') roleId: number) {
    return await this.rolesService.findUsersByRole(roleId);
  }

  // 获取所有角色及其用户 http://localhost:3000/api/v1/roles/usersByRoles
  @Get('/usersByRoles')
  async findUsersByRoles() {
    return await this.rolesService.findUsersByRoles();
  }

  // ===================================== 基础增删改查 ===========================================
  // 增 http://localhost:3000/api/v1/roles
  @Post()
  async create(@Body() roles: Roles) {
    return await this.rolesService.create(roles);
  }

  // 删 http://localhost:3000/api/v1/roles/1
  @Delete(':id')
  async remove(@Param('id') id: number) {
    return await this.rolesService.remove(id);
  }

  // 改 http://localhost:3000/api/v1/roles/1
  @Put()
  async update(@Body() roles: Roles) {
    return await this.rolesService.update(roles);
  }

  // 查所有 http://localhost:3000/api/v1/roles
  @Get()
  async findAll() {
    return await this.rolesService.findAll();
  }

  // 查单条 http://localhost:3000/api/v1/roles/3
  @Get(':id')
  async findOne(@Param('id') id: number) {
    return await this.rolesService.findOne(id);
  }
}

服务

// src/roles/roles.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Roles } from './roles.entity';
import { FindOneOptions, Repository, In } from 'typeorm';
import { User } from '../user/user.entity';
@Injectable()
export class RolesService {
  constructor(
    @InjectRepository(Roles) private readonly rolesRepository: Repository<Roles>,
    @InjectRepository(User) private readonly userRepository: Repository<User>
  ) {}

  // ===================================== 用户&角色 ===========================================
  // 为用户分配角色（关联插入，用户 --> 角色）
  async insertRolesByUser(userId: number, roleIds: number[]): Promise<User> {
    const user = await this.userRepository.findOne({ where: { id: userId } });
    user.roles = await this.rolesRepository.findBy({ id: In(roleIds) });
    return this.userRepository.save(user);
  }

  // 获取用户及其角色（关联查询，用户 --> 角色）
  async findRolesByUser(id: number): Promise<User> {
    return this.userRepository.findOne({
      where: { id },
      relations: ['roles'],
    });
  }

  // 获取所有用户及其角色（关联查询，所有用户 --> 角色）
  async findRolesByUsers(): Promise<User[]> {
    return this.userRepository.find({
      relations: ['roles'],
    });
  }

  // 获取角色及其用户（关联查询，角色 --> 用户）
  async findUsersByRole(roleId: number): Promise<any> {
    return this.rolesRepository.findOne({
      where: { id: roleId },
      relations: ['user'],
    });
  }

  // 获取所有角色及其用户（关联查询，所有角色 --> 用户）
  async findUsersByRoles(): Promise<any> {
    return this.rolesRepository.find({
      relations: ['user'],
    });
  }

  // 增
  async create(roles: Partial<Roles>): Promise<Roles> {
    return await this.rolesRepository.save(roles);
  }

  // 删
  async remove(id: number): Promise<any> {
    return await this.rolesRepository.delete(id);
  }

  // 改
  async update(roles: Partial<Roles>): Promise<any> {
    return await this.rolesRepository.update(roles.id, roles);
  }

  // 查所有
  async findAll(): Promise<Roles[]> {
    return await this.rolesRepository.find();
  }
  // 查单条
  async findOne(id: number): Promise<Roles> {
    const options: FindOneOptions<Roles> = {
      where: { id: id },
    };
    return await this.rolesRepository.findOne(options);
  }
}

实体

// src/roles/roles.entity.ts

import { User } from 'src/user/user.entity';
import { Column, Entity, ManyToMany, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Roles {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  rolename: string;

  // 表示 roles 表与 user 表建立多对多关系
  @ManyToMany(() => User, (user) => user.roles)
  user: User[];
}

测试

shell

# 创建用户 POST
http://localhost:3000/api/v1/user  # 参数：{"username": "alias","password": "1"}

# 查询所有用户
http://localhost:3000/api/v1/user

# 查询 id 为 1 的用户
http://localhost:3000/api/v1/user/1

# 查询所有（包含软删除的用户）
http://localhost:3000/api/v1/user/softDeleteAllData

中间件

守卫

异常过滤器

拦截器

管道

集成TypeORM和MySQL（二）

实体关系

用户实体（user）

用户信息实体（profile）

日志实体（log）

角色（roles）

测试

集成TypeORM和MySQL（二） ​

实体关系 ​

用户实体（user） ​

用户信息实体（profile） ​

日志实体（log） ​

角色（roles） ​

测试 ​

集成TypeORM和MySQL（二）

实体关系

用户实体（user）

用户信息实体（profile）

日志实体（log）

角色（roles）

测试