NestJs + TypeOrm + Ant Design Pro 搭建股票估值查询(二)

传送门
本章内容
  1. TypeOrm安装
  2. MySQL库/表创建,数据初始化
  3. 创建用户持久化到数据库
环境准备

请确保操作系统上安装了 MySQL(>= 5.7)

项目内安装TypeOrm及MySql相关依赖:

$ npm install --save @nestjs/typeorm typeorm mysql

使用TypeOrm有多种创建数据表的方式,具体参考TypeOrm,方便起见我们使用脚本创建数据表,再生成model的方式,根目录下创建docs文件夹,新建stock-demo.sql文件,写入初始化脚本同时初始化若干条股票数据:

-- 创建数据库
CREATE SCHEMA `stock_demo` DEFAULT CHARACTER SET utf8mb4 ;

-- ---------------------------------------
-- 用户表 简化角色
-- ---------------------------------------
CREATE TABLE `st_user` (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  mobile varchar(20) NOT NULL DEFAULT '' COMMENT '手机号',
  name varchar(20) NOT NULL DEFAULT '' COMMENT '昵称',
  role tinyint(1) NOT NULL DEFAULT '0' COMMENT '角色:100 超级管理员,0 普通用户',
  password varchar(100) NOT NULL DEFAULT '' COMMENT '密码',
  salt varchar(100) NOT NULL DEFAULT '' COMMENT '密码盐',
  create_dt datetime NOT NULL DEFAULT now() COMMENT '创建时间',
  update_dt timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mobile` (`mobile`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ---------------------------------------
-- 股票表
-- ---------------------------------------
CREATE TABLE `stock` (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  code varchar(10) NOT NULL DEFAULT '' COMMENT '股票代码',
  name varchar(30) NOT NULL DEFAULT '' COMMENT '股票名称',
  market varchar(10) NOT NULL DEFAULT '' COMMENT '股票市场',
  price decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT '当前股价',  
  pe decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT '当前PE(市盈率)',
  pe_avg decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT '平均PE(市盈率)',
  pe_ttm decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT '当前PE TTM(市盈率)',
  pe_ttm_avg decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT '平均PE TTM(市盈率)',
  pe_ttm_rate decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT '最新pet_tm与平均值的比例',
  pe_ttm_mid decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT 'pe_ttm中位数',
  total_mv decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT '总市值',
  source_data json NULL COMMENT '数据源',
  create_dt datetime NOT NULL DEFAULT now() COMMENT '创建时间',
  update_dt timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stock_code` (`code`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ---------------------------------------
-- 股票流水账: 记录每日pe pb等值,简化为只记录pe
-- ---------------------------------------
CREATE TABLE `stock_log` (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  code varchar(10) NOT NULL DEFAULT '' COMMENT '股票代码',
  log_date date NOT NULL COMMENT '日期',
  pe decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT '当前PE(市盈率)',
  pe_ttm decimal(12,4) NOT NULL DEFAULT '0.00' COMMENT '当前PE TTM(市盈率)',
  total_mv decimal(16,4) NOT NULL DEFAULT '0.00' COMMENT '总市值',
  create_dt datetime NOT NULL DEFAULT now() COMMENT '创建时间',
  update_dt timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code_log_date` (`code` ASC,`log_date` ASC),
  INDEX `idx_code` (`code` ASC)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ---------------------------------------
-- 用户股票自选列表
-- ---------------------------------------
CREATE TABLE `user_stock` (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  uid int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  code varchar(10) NOT NULL DEFAULT '' COMMENT '股票代码',
  create_dt datetime NOT NULL DEFAULT now() COMMENT '创建时间',
  update_dt timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_uid_code` (`uid` ASC,`code` ASC),
  FOREIGN KEY (uid) REFERENCES st_user(id),
  FOREIGN KEY (code) REFERENCES stock(code)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 初始化stock_log
INSERT INTO `stock_log` VALUES 
 (1,'600000','2021-03-26',0.0000,5.3445,0.0000,'2021-03-27 16:20:55',NULL,0),
 (2,'600000','2021-03-25',0.0000,5.3496,0.0000,'2021-03-27 16:20:55',NULL,0),
 (3,'600000','2021-03-24',0.0000,5.3747,0.0000,'2021-03-27 16:20:55',NULL,0),
 (4,'600000','2021-03-23',0.0000,5.4251,0.0000,'2021-03-27 16:20:55',NULL,0),
 (5,'600000','2021-03-22',0.0000,5.4100,0.0000,'2021-03-27 16:20:55',NULL,0),
 (6,'600000','2021-03-19',0.0000,5.3596,0.0000,'2021-03-27 16:20:55',NULL,0),
 (7,'600000','2021-03-18',0.0000,5.5509,0.0000,'2021-03-27 16:20:55',NULL,0),
 (8,'600000','2021-03-17',0.0000,5.4955,0.0000,'2021-03-27 16:20:55',NULL,0),
 (9,'600000','2021-03-16',0.0000,5.5911,0.0000,'2021-03-27 16:20:55',NULL,0),
 (10,'600000','2021-03-15',0.0000,5.5962,0.0000,'2021-03-27 16:20:55',NULL,0),
 (16,'600006','2021-03-26',0.0000,21.0770,0.0000,'2021-03-27 16:21:51',NULL,0),
 (17,'600006','2021-03-25',0.0000,20.4443,0.0000,'2021-03-27 16:21:51',NULL,0),
 (18,'600006','2021-03-24',0.0000,20.5775,0.0000,'2021-03-27 16:21:51',NULL,0),
 (19,'600006','2021-03-23',0.0000,20.9105,0.0000,'2021-03-27 16:21:51',NULL,0),
 (20,'600006','2021-03-22',0.0000,21.4765,0.0000,'2021-03-27 16:21:51',NULL,0),
 (21,'600006','2021-03-19',0.0000,20.6774,0.0000,'2021-03-27 16:21:51',NULL,0),
 (22,'600006','2021-03-18',0.0000,21.0104,0.0000,'2021-03-27 16:21:51',NULL,0),
 (23,'600006','2021-03-17',0.0000,21.3766,0.0000,'2021-03-27 16:21:51',NULL,0),
 (24,'600006','2021-03-16',0.0000,21.7096,0.0000,'2021-03-27 16:21:51',NULL,0),
 (25,'600006','2021-03-15',0.0000,20.8772,0.0000,'2021-03-27 16:21:51',NULL,0);
 
-- 初始化stock
INSERT INTO `stock` VALUES 
(1,'600000','浦发银行','sh',0.0000,0.0000,0.0000,5.3445,6.0118,0.8890,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(2,'600004','白云机场','sh',0.0000,0.0000,0.0000,244.6567,44.3531,5.5161,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(3,'600006','东风汽车','sh',0.0000,0.0000,0.0000,21.0770,41.6499,0.5061,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(4,'600007','中国国贸','sh',0.0000,0.0000,0.0000,15.0099,22.2433,0.6748,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(5,'600008','首创股份','sh',0.0000,0.0000,0.0000,17.3383,31.1689,0.5563,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(6,'600011','华能国际','sh',0.0000,0.0000,0.0000,15.0276,35.7993,0.4198,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(7,'600012','皖通高速','sh',0.0000,0.0000,0.0000,14.2025,15.3841,0.9232,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(8,'600015','华夏银行','sh',0.0000,0.0000,0.0000,4.6650,5.4504,0.8559,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(9,'600016','民生银行','sh',0.0000,0.0000,0.0000,4.9715,5.7336,0.8671,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(10,'600017','日照港','sh',0.0000,0.0000,0.0000,12.5360,32.0243,0.3915,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(11,'600018','上港集团','sh',0.0000,0.0000,0.0000,13.2221,15.3694,0.8603,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(12,'600019','宝钢股份','sh',0.0000,0.0000,0.0000,15.8902,16.5238,0.9617,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(13,'600020','中原高速','sh',0.0000,0.0000,0.0000,28.1624,12.6981,2.2178,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(14,'600021','上海电力','sh',0.0000,0.0000,0.0000,17.4582,21.4717,0.8131,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(15,'600022','山东钢铁','sh',0.0000,0.0000,0.0000,24.8837,45.3337,0.5489,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(16,'600023','浙能电力','sh',0.0000,0.0000,0.0000,8.5669,13.0968,0.6541,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(17,'600025','华能水电','sh',0.0000,0.0000,0.0000,19.6953,22.2212,0.8863,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(18,'600026','中远海能','sh',0.0000,0.0000,0.0000,9.6214,36.6940,0.2622,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(19,'600027','华电国际','sh',0.0000,0.0000,0.0000,8.2112,19.8147,0.4144,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0),
(20,'600028','中国石化','sh',0.0000,0.0000,0.0000,13.5744,18.3329,0.7404,0.0000,0.0000,NULL,'2021-03-27 16:17:28','2021-03-26 23:50:00',0);

数据准备完毕,使用 Generator这个工具生成我们代码需要的Entity,全局安装该工具:

$ npm i -g typeorm-model-generator

执行生成命令:

$ typeorm-model-generator -h database-host -d stock_demo -u root -x 'database password' -e mysql -o .

执行成功后,会在当前目录下生成entities目录及其配置文件,将entities目录拷贝到src目录下,进入app.module.ts配置TypeOrm

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { UserController } from './modules/user/user.controller';
import { UserModule } from './modules/user/user.module';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      name: 'default',
      type: 'mysql',
      host: 'data base host',
      port: 3306,
      username: 'data base username',
      password: 'data base password',
      database: 'stock_demo',
      synchronize: false,
      entities: [__dirname + '/entities/*.js'],
    }),
    UserModule,
  ],
  controllers: [AppController, UserController],
  providers: [AppService],
})
export class AppModule {}

至此我们完成了TypeOrm和数据库相关配置及初始化。

创建用户

打开user.module.ts文件,注入用户Entity依赖:

import { Module, HttpModule } from '@nestjs/common';
import { UserController } from './user.controller';
import { UserService } from './user.service';
import { StUser } from '../../entities/StUser';
import { TypeOrmModule } from '@nestjs/typeorm';

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

项目中通常不会直接将Entity暴露到api出口,而是创建DTO用于定义传输的数据类型,user目录下创建dto目录,创建user.dto.ts,代码如下:

/**
 * 创建用户
 */
export class CreateUserDto {
  readonly name: string;
  readonly mobile: string;
  readonly password: string;
}

为保证用户账号密码安全,需要对创建的用户密码进行加密处理(方便起见盐值也一同存储),安装bcryptjs库:

$ npm i bcryptjs -S

打开user.service,引入相关依赖,添加创建用户的方法:

import { Injectable } from '@nestjs/common';
import { StUser } from '../../entities/StUser';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { CreateUserDto } from './dto/user.dto';
// eslint-disable-next-line @typescript-eslint/no-var-requires
const bcrypt = require('bcryptjs');
import { BusiException } from '../../libs/filters/busi.exception';
import { BusiErrorCode } from '../../libs/enums/error-code-enum';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(StUser)
    private readonly stUserRepository: Repository<StUser>,
  ) {}

  async create(dto: CreateUserDto) {
    const user = this.stUserRepository.create(dto);
    const salt = bcrypt.genSaltSync(10);
    user.salt = salt;
    user.password = bcrypt.hashSync(user.password, salt);
    return this.stUserRepository
      .save(user)
      .then((res) => {
        return { id: res.id };
      })
      .catch((err) => {
        throw new BusiException(BusiErrorCode.PARAM_ERROR, err.message);
      });
  }
}

user.controller.ts文件中,添加创建用户代码:

import { Controller, Get, Body, Post, HttpStatus } from '@nestjs/common';
import { BusiException } from '../../libs/filters/busi.exception';
import { BusiErrorCode } from '../../libs/enums/error-code-enum';
import { UserService } from './user.service';
import { CreateUserDto } from './dto/user.dto';

@Controller('user')
export class UserController {
  constructor(private readonly userService: UserService) {}
  
  ...

  @Post('create')
  async create(@Body() user: CreateUserDto) {
    return this.userService.create(user);
  }
}

运行程序,使用api调试工具测试创建用户,地址:http://localhost:3000/user/create POST Content-Type:application/json ,参数:

{
    "name":"少年",
    "mobile":"18888888888",
    "password":"123456"
}

返回结果:

{
    "data": {
        "id": 7
    },
    "code": 0,
    "message": "success"
}

数据库验证一下是否创建成功:

mysql> select * from st_user order by id desc

在这里插入图片描述

下一章我们为api添加权限验证和定时任务。

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值