Sequelize 查询操作

Sequelize 是一个基于 promise 的 Node.js ORM 工具, 目前支持 Postgres, MySQL, MariaDB, SQLite 以及 Microsoft SQL Server. 它具有强大的事务支持, 关联关系, 预读和延迟加载,读取复制等功能

官网地址：Sequelize Docs 中文版 | sequelize-docs-Zh-CNSequelize 文档的中文版本: v4.42.0 v5.21.5 v6.6.2https://demopark.github.io/sequelize-docs-Zh-CN/

1、定义User的实体类

const { Sequelize } = require('sequelize'); const db = require('../sequelizeConnectDb'); const Sex = require('./Sex'); let isSyncTable = false const User = db.define('user', { id: { type: Sequelize.UUID, defaultValue: Sequelize.UUIDV4, primaryKey: true }, username: Sequelize.STRING, age: Sequelize.NUMBER, sexid: Sequelize.STRING, address: Sequelize.STRING }, { timestamps: false, paranoid: false, underscored: true, freezeTableName: true, tableName: 'user' }) User.hasOne(Sex, { foreignKey: 'id', sourceKey: 'sexid', constraints: false }); if(isSyncTable) { User.sync({force: true}) } module.exports = User 

        定义一个Sex的实体类

const { Sequelize } = require('sequelize'); const db = require('../sequelizeConnectDb'); let isSyncTable = false const Sex = db.define('sex', { name: Sequelize.STRING }, { timestamps: false, paranoid: false, underscored: true, freezeTableName: true, tableName: 'sex' }) if(isSyncTable) { Sex.sync({force: true}) } module.exports = Sex 

2、编写查询方法-获取全部数据不分页，主要用–User.findAll()来查询

const User = require('../model/User'); const express = require('express'); let router = express.Router(); / * 查询列表，获取全部数据，不分页 * 使用方法：findAll */ router.get('/userAllList', function (req, res, next) { User.findAll().then(resData => { res.send({ code: 0, data: resData, msg: 'ok' }) }).catch(() => { res.send({ code: 9999, data: null, msg: '查询失败' }) }) }) module.exports = router 

3、编写查询方法-列表分页+关联表信息展示，主要用–User.findAndCountAll()来查询

/ * 查询列表，分页查询+关联查询性别名称 * 使用方法：findAndCountAll * 在req.query中接受参数 * 传参为：offset limit */ router.get('/userList', async function (req, res, next) { User.findAndCountAll({ offset: (req.query.page - 1) * req.query.pageSize, limit: req.query.pageSize, include: [ { model: Sex, attributes: [['name', 'sexname']] }, ], distinct: true }).then(resData => { res.send({ code: 0, data: resData, msg: 'ok' }) }).catch((err) => { console.log(err); res.send({ code: 9999, data: null, msg: '查询失败' }) }) }) module.exports = router

4、编写查询方法-根据username查询为空就查询所有，主要用–User.findAndCountAll()来查询

const User = require('../model/User'); const express = require('express'); let router = express.Router(); / * 查询详情,例如：根据username查询为空就查询所有 * 使用方法：findAndCountAll * 在req.query中接受参数 */ router.get('/userAll', async function (req, res, next) { let whereInfo = {}; if(req.query.username){ whereInfo={ username:req.query.username } }else{ whereInfo={} } User.findAndCountAll({ where: whereInfo, }).then(resData => { res.send({ code: 0, data: resData, msg: 'ok' }) }).catch(() => { res.send({ code: 9999, data: null, msg: '查询失败' }) }) }) module.exports = router