NestJs + TypeOrm + Ant Design Pro 搭建股票估值查询（四）

传送门

在线演示账号：guest ,密码：123456
源码地址

本章内容

数据查询接口
Ant Design Pro接入
Linux下Nginx部署
Jenkins持续集成/部署

数据查询

定义查询接口需要使用的数据类型，modules/stock目录下新建dto目录，新建stock.dto.ts文件，定义以下类型：

import { Exclude } from 'class-transformer';

// 股票列表类型
export class StockDto {
  readonly code: string;
  readonly name: string;
  readonly market: string;
  readonly price: string;
  readonly peTtm: string;
  readonly peTtmAvg: string;
  readonly peTtmRate: string;
  readonly peTtmMid: string;
  @Exclude()
  readonly sourceData: object | null;

  us: any;
}

// 列表查询参数类型
export class StockQueryDto {
  readonly pageSize: number;
  readonly pageIndex: number;
  readonly keywords: string;
  readonly orderBy: number;
}

export class UserStockDto {
  readonly code: string;
}

// 分页数据类型
export class PageListModel<T> {
  totalNum: number; // 总数
  pageSize: number; // 页数量
  pageIndex: number; // 当前页码
  list: T[];
}

modules/stock目录下创建股票数据service：

$ nest g s modules/stock

查询方法演示了TypeOrm中QueryBuilder和执行原生SQL语句的写法（原生SQL建议使用参数化查询防范sql注入，这里只做演示），代码如下：

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, getRepository, getConnection, getManager } from 'typeorm';
import { plainToClass } from 'class-transformer';
import { StockDto, StockQueryDto, PageListModel } from './dto/stock.dto';
import { Stock } from '../../entities/Stock';
import { UserStock } from '../../entities/UserStock';

@Injectable()
export class StockService {
  constructor(
    @InjectRepository(Stock)
    private readonly stStockRepository: Repository<Stock>,
  ) {}
  /**
   * 股票列表 QueryBuilder方式查询
   * leftjoin 文档缺失，使用QueryBuilder分两次查询
   * @param param
   * @returns
   */
  async getStockList(
    uid: number,
    param: StockQueryDto,
  ): Promise<PageListModel<StockDto>> {
    const pageIndex = param.pageIndex ? param.pageIndex : 1;
    const pageSize = param.pageSize ? param.pageSize : 10;
    const orderBy = param.orderBy ? param.orderBy : 0;
    let orderByMap: any = {
      'st.code': 'DESC',
    };
    switch (orderBy) {
      case 1:
        orderByMap = {
          'st.peTtmRate': 'ASC',
        };
        break;
      case 2:
        orderByMap = {
          'st.peTtmRate': 'DESC',
        };
        break;
      default:
        break;
    }
    let keyWordsWhere = 'st.is_delete = 0';
    if (param.keywords) {
      keyWordsWhere += ' and (st.code like :code or st.name like :name)';
    }
    const { totalNum } = await getRepository(Stock)
      .createQueryBuilder('st')
      .select('COUNT(1)', 'totalNum')
      .where(keyWordsWhere, {
        code: '%' + param.keywords + '%',
        name: '%' + param.keywords + '%',
      })
      .getRawOne();

    const stockList = await getRepository(Stock)
      .createQueryBuilder('st')
      .select([
        'st.id',
        'st.code',
        'st.name',
        'st.pe',
        'st.peTtm',
        'st.peTtmAvg',
        'st.peTtmMid',
        'st.peTtmRate',
        'st.updateDt',
      ])
      .where(keyWordsWhere, {
        code: '%' + param.keywords + '%',
        name: '%' + param.keywords + '%',
      })
      .orderBy(orderByMap)
      .skip((pageIndex - 1) * pageSize)
      .take(pageSize)
      .getMany();
    // 转换类型，否则需要Stock上添加 us属性
    const stocks = plainToClass(StockDto, stockList);
    for (let i = 0; i < stocks.length; i++) {
      // 查询关系表是否存在
      const us = await getConnection()
        .createQueryBuilder()
        .select(['us.uid'])
        .from(UserStock, 'us')
        .where('us.uid = :uid and us.code = :code ', {
          uid,
          code: stocks[i].code,
        })
        .getOne();
      if (us) {
        stocks[i].us = us;
      } else {
        stocks[i].us = null;
      }
    }

    return {
      list: stocks,
      totalNum,
      pageIndex,
      pageSize,
    };
  }

  /**
   * 添加自选
   * @param uid
   * @param code
   * @returns
   */
  async addUserStock(uid: number, code: string): Promise<boolean> {
    let result = false;
    // 判断关系是否已存在
    const userStock = await getConnection()
      .createQueryBuilder()
      .select(['ut.id'])
      .from(UserStock, 'ut')
      .where('ut.uid = :uid and ut.code = :code ', {
        uid,
        code,
      })
      .getOne();
    if (!userStock) {
      const ut = await getConnection()
        .createQueryBuilder()
        .insert()
        .into(UserStock)
        .values([{ uid, code }])
        .execute();
      if (ut) {
        result = true;
      }
    }
    return result;
  }

  /**
   * 自选列表 原生sql语句的方式执行
   * @param uid
   * @param param
   * @returns
   */
  async getUserStocts(
    uid: number,
    param: StockQueryDto,
  ): Promise<PageListModel<UserStock>> {
    const pageIndex = param.pageIndex ? param.pageIndex : 1;
    const pageSize = param.pageSize ? param.pageSize : 10;
    const orderBy = param.orderBy ? param.orderBy : 0;
    let orderByStr = `order by s.code desc `;
    switch (orderBy) {
      case 1:
        orderByStr = `order by s.pe_ttm_rate asc `;
        break;
      case 2:
        orderByStr = `order by s.pe_ttm_rate desc `;
        break;
      default:
        break;
    }
    let keyWordsWhere = `ut.uid=${uid} and s.is_delete=0`;
    if (param.keywords) {
      keyWordsWhere += ` and (s.code like '%${param.keywords}%'  or s.name like '%${param.keywords}%') `;
    }
    const limit = ` limit ${pageSize} offset ${(pageIndex - 1) * pageSize}`;
    const manager = getManager();
    const total = await manager.query(
      `select count(1) as totalNum
       from user_stock ut inner join stock s on ut.code=s.code where ${keyWordsWhere}`,
    );
    const stockList = await manager.query(
      `select ut.uid,ut.code,s.name,
       s.pe_ttm as peTtm,
       s.pe_ttm_avg as peTtmAvg,
       s.pe_ttm_mid as peTtmMid,
       s.pe_ttm_rate as peTtmRate,
       s.update_dt as updateDt
       from user_stock ut inner join stock s on ut.code=s.code where ${keyWordsWhere} ${orderByStr} ${limit}`,
    );

    return {
      list: stockList,
      totalNum: total.length > 0 ? Number(total[0].totalNum) : 0,
      pageIndex,
      pageSize,
    };
  }
  /**
   * 删除自选
   * @param uid
   * @param code
   * @returns
   */
  async deleteUserStock(uid: number, code: string): Promise<boolean> {
    let result = false;
    const res = await getConnection()
      .createQueryBuilder()
      .delete()
      .from(UserStock)
      .where('uid = :uid', { uid })
      .andWhere('code = :code', { code })
      .execute();
    if (res.affected > 0) {
      result = true;
    }
    return result;
  }
}

创建stock.controller.ts：

$ nest g co modules/stock

修改stock.controller.ts模板代码，注意UseGuards的使用，作用域为全部路由：

import { Controller, Post, Body, UseGuards } from '@nestjs/common';
import { StockService } from './stock.service';
import { StockQueryDto } from './dto/stock.dto';
import { AuthGuard } from '@nestjs/passport';
import { RolesGuard } from '../auth/guards/role.guard';
import { User } from '../../libs/decorators/user.decorator';
import { ProfileInfo } from '../../libs/decorators/profileInfo';
import { UserStockDto } from './dto/stock.dto';

@Controller('stock')
@UseGuards(AuthGuard('jwt'))
export class StockController {
  constructor(private readonly userService: StockService) {}

  @UseGuards(RolesGuard)
  @Post('list')
  async getStockList(@Body() parmas: StockQueryDto, @User() user: ProfileInfo) {
    return this.userService.getStockList(user.uid, parmas);
  }

  @UseGuards(RolesGuard)
  @Post('add-choice')
  async addChoice(@Body() parmas: UserStockDto, @User() user: ProfileInfo) {
    return this.userService.addUserStock(user.uid, parmas.code);
  }

  @UseGuards(RolesGuard)
  @Post('user-list')
  async getUserStocts(
    @Body() parmas: StockQueryDto,
    @User() user: ProfileInfo,
  ) {
    return this.userService.getUserStocts(user.uid, parmas);
  }

  @UseGuards(RolesGuard)
  @Post('delete-choice')
  async deleteChoice(@Body() parmas: UserStockDto, @User() user: ProfileInfo) {
    return this.userService.deleteUserStock(user.uid, parmas.code);
  }
}

运行程序，访问http://localhost:3000/stock/list POST （注意带Authorization）参数如下：

{
  "pageSize":10,
  "pageIndex":1,
  "keywords":"中国",
  "orderBy":1
}

返回结果：

`Ant Design Pro`

api接口准备就绪，安装Ant Design Pro相关，参考文档 Ant Design Pro

前端页面初始化完成后，config/config.ts需要注意以下几点：

使用代理，服务部署到服务器上配置代理需要和前端对应
前端项目部署在服务器非根目录，需要注意base和publicPath配置
mock打开与关闭等

Ant Design Pro默认使用@umijs/plugin-request作为请求库，而request库本身已停止更新，并且在获取Response头属性等方面不灵活，故而基于axios重写了请求类，将http code和业务code做了分类处理，其他细节请直接查考源码。

`Linux`下`Nginx`部署

服务部署可以基于docker打包成镜像的方式，也可自己搭建node+nginx的方式部署，此次使用第二种方式。

服务端环境：

$ cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
$ node -v
v14.16.0
$ npm -v
6.14.11
$ pm2 -v
4.5.4

node服务部署需注意以下：

本地build出的build/dist直接放置到服务器上是不能运行的，相关依赖并不会打包到输出包，推荐在服务器上安装依赖后打包；
由于node事件循环的单线程机制，可以使用pm2实现进程多开；
Nginx代理设置需要和Ant Design Pro保持一致，这样无论本地开发或者上线部署都不需要改动代码；

前端部署，如果是在非根目录下，请参考非根目录

`Jenkins`持续集成/部署

Jenkins安装请参考Jenkins安装，Jenkins主要使用单项构建或者流水线构建的方式，流水线构建方式简单粗暴的可以理解为通过Jenkinsfile定义多个分步骤进行的单项，每个单项之间可以设置人工确认过程而决定是否继续下一步，更多细节请参考Jenkins文档本次使用单项构建方式即可满足需求。其主要执行过程可以分为以下几个步骤：