连接MySQL,实现一个简单的增删改查功能
前面几章我们讲了项目的初始,连接MySQL,这章我们主要实现增删改查接口,在src下新建user文件夹,我们的所有功能都写在这个文件夹下。
1 新建entity
项目开始配置连接数据库的时候,我们 synchronize 选择的事true,即使我们库里没有表,通过entity也可以自动生成表
import { Column, Entity, PrimaryGeneratedColumn, BaseEntity } from 'typeorm';
/**
* 列选项参考
* https://typeorm.biunav.com/zh/entities.html#%E5%88%97%E9%80%89%E9%A1%B9
**/
@Entity()
export class User extends BaseEntity {
@PrimaryGeneratedColumn()
id: number;
@Column({ type: 'varchar', name: 'name', comment: '用户名' })
name: string;
@Column({ type: 'varchar', name: 'nickname', comment: '昵称' })
nickname: string;
@Column({ type: 'varchar', name: 'password', comment: '密码' })
password: string;
@Column({ type: 'varchar', length: 11, name: 'mobile', comment: '手机号' })
mobile: string;
@Column({ type: 'varchar', nullable: true, name: 'avatar', comment: '头像' })
avatar: string;
@Column({ type: 'varchar', default: '未知', name: 'gender', comment: '性别' })
gender: string;
@Column({
type: 'varchar',
default: '普通会员',
name: 'user_level',
comment: '用户级别',
})
userLevel: string;
@Column({
type: 'varchar',
nullable: true,
name: 'province',
comment: '省份',
})
province: string;
@Column({ type: 'varchar', nullable: true, name: 'city', comment: '城市' })
city: string;
@Column({ type: 'datetime', nullable: true, name: 'create_time' })
createTime: number;
@Column({ type: 'datetime', nullable: true, name: 'update_time' })
updateTime: number;
}
上面的内容定义的是表的字段及字段类型等
2 新建controller
这里定义了四个路由,全部为post请求方式
import { ApiTags, ApiParam, ApiQuery, ApiHeader } from '@nestjs/swagger';
import { Controller, Get, Post, Body, Logger } from '@nestjs/common';
import { UserService } from './user.service';
import { QueryUserDto } from './Dto/queryUser.dto';
import { AddUserDto } from './Dto/addUser.dto';
import { UpdateUserDto } from './Dto/updateUser.dto';
import { DeleteUserDto } from './Dto/deleteUser.dto';
/**
*
* @author lidonghui
* @version 1
* @date 2021/01/18 09:57
*/
@ApiTags('用户管理')
@Controller('user')
export class UserController {
constructor(private readonly userService: UserService) {}
/**
* 用户管理-分页查询
*/
@ApiQuery({
name: 'name',
description: '用户名',
})
@Post('/page')
findAll(@Body() queryUserDto: QueryUserDto): Promise<{}> {
Logger.log(`分页查询接收参数:${JSON.stringify(queryUserDto)}`);
return this.userService.pageQuery(queryUserDto);
}
/**
* 用户管理-增加用户
*/
@Post('/add')
addUser(@Body() addUserDto: AddUserDto): Promise<boolean> {
Logger.log(`增加用户接收参数:${JSON.stringify(addUserDto)}`);
return this.userService.save(addUserDto);
}
/**
* 用户管理-编辑用户
*/
@Post('/edit')
updateUser(@Body() updateUserDto: UpdateUserDto): Promise<boolean> {
Logger.log(`编辑用户接收参数:${JSON.stringify(updateUserDto)}`);
return this.userService.save(updateUserDto);
}
/**
* 用户管理-删除用户
*/
@Post('/delete')
deleteUser(@Body() deleteUserDto: DeleteUserDto): Promise<boolean> {
Logger.log(`删除用户接收参数:${JSON.stringify(deleteUserDto)}`);
return this.userService.delete(deleteUserDto);
}
}
3 新建service
这里主要的实现是对库的操作,这里面就包含了增删改查
import { Injectable, Logger } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';
@Injectable()
export class UserService {
constructor(
@InjectRepository(User)
private readonly UserRepository: Repository<User>,
) {}
private readonly User: User[] = [];
// 分页查询
async pageQuery(parameter: any): Promise<any> {
Logger.log(`请求参数:${JSON.stringify(parameter)}`);
// 定义返回格式
let result = {
pageNo: Number(parameter.pageNo),
pageSize: Number(parameter.pageSize),
totalPage: 0,
totalRows: 0,
rows: [],
};
// 返回条数
let SQLwhere: any = {};
if (parameter.name != undefined) {
SQLwhere.name = parameter.name;
}
result.rows = await this.UserRepository.find({
where: SQLwhere,
order: {
id: 'DESC',
},
skip: (parameter.pageNo - 1) * Number(parameter.pageSize), // 分页,跳过几项
take: parameter.pageSize, // 分页,取几项
cache: true,
});
// 总条数
result.totalRows = await this.UserRepository.count();
// 总页数
result.totalPage = Math.ceil(
(await this.UserRepository.count()) / parameter.pageSize,
);
return result;
}
// 增加/更新
async save(parameter: any): Promise<boolean> {
Logger.log(`请求参数:${JSON.stringify(parameter)}`);
try {
let a = await this.UserRepository.save(parameter);
return true;
} catch (error) {
Logger.log(`请求失败:${JSON.stringify(error)}`);
return false;
}
}
// 删除
async delete(ids: any): Promise<boolean> {
Logger.log(`请求参数:${JSON.stringify(ids)}`);
try {
let a = await this.UserRepository.delete(ids.id);
Logger.log(`删除返回数据:${JSON.stringify(a)}`);
if (a.affected == 0) {
return false;
} else {
return true;
}
} catch (error) {
return false;
}
}
}
4 新建module
import { Module } from '@nestjs/common';
import { UserService } from './user.service';
import { UserController } from './user.controller';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './user.entity';
@Module({
imports: [TypeOrmModule.forFeature([User])],
providers: [UserService],
controllers: [UserController],
})
export class UserModule {}
5 新建Dto
Dto文件夹有四个文件,分别为addUser.dto.ts、deleteUser.dto.ts、queryUser.dto.ts和updateUser.dto.ts,这四个是查询及验证使用
1 addUser.dto.ts
import { ApiProperty } from '@nestjs/swagger';
import { IsNotEmpty } from 'class-validator';
export class AddUserDto {
@ApiProperty()
@IsNotEmpty({ message: '用户名不为空' })
readonly name: string;
@ApiProperty()
@IsNotEmpty({ message: '昵称不为空' })
readonly nickname: string;
@ApiProperty()
@IsNotEmpty({ message: '密码不为空' })
readonly password: string;
@ApiProperty()
@IsNotEmpty({ message: '手机号不为空' })
readonly mobile: string;
@ApiProperty()
readonly avatar: string;
@ApiProperty()
readonly gender: string;
@ApiProperty()
readonly userLevel: string;
@ApiProperty()
readonly province: string;
@ApiProperty()
readonly city: string;
}
2 deleteUser.dto.ts
import { ApiProperty } from '@nestjs/swagger';
import { IsNotEmpty, IsInt } from 'class-validator';
export class DeleteUserDto {
@ApiProperty()
@IsInt({ message: 'id应为数字' })
@IsNotEmpty({ message: 'id不为空' })
readonly id: number;
}
3 queryUser.dto.ts
import { ApiProperty } from '@nestjs/swagger';
import { IsNotEmpty, Min, IsInt } from 'class-validator';
export class QueryUserDto {
@ApiProperty()
readonly name: string;
@ApiProperty()
@Min(1, { message: '分页不能小于1' })
@IsInt({ message: '分页应为数字' })
@IsNotEmpty({ message: '分页不能为空' })
readonly pageNo: number;
@ApiProperty()
@Min(1, { message: '分页条数不能小于1' })
@IsInt({ message: '分页条数应为数字' })
@IsNotEmpty({ message: '分页条数不能为空' })
readonly pageSize: number;
}
4 updateUser.dto.ts
import { ApiProperty } from '@nestjs/swagger';
import { IsNotEmpty, IsInt } from 'class-validator';
export class UpdateUserDto {
@ApiProperty()
@IsInt({ message: 'id应为数字' })
@IsNotEmpty({ message: 'id不为空' })
readonly id: number;
@ApiProperty()
readonly name: string;
@ApiProperty()
readonly nickname: string;
@ApiProperty()
readonly password: string;
@ApiProperty()
readonly mobile: string;
@ApiProperty()
readonly avatar: string;
@ApiProperty()
readonly gender: string;
@ApiProperty()
readonly userLevel: string;
@ApiProperty()
readonly province: string;
@ApiProperty()
readonly city: string;
}
6 运行项目
上面的是全部的配置,主要因为是此项目没有开源,所以代码全部复制,基本能运行。最后结果
正文完