Sequelize 是一个基于 promise 的 Node.js ORM 工具, 目前支持 Postgres, MySQL, MariaDB, SQLite 以及 Microsoft SQL Server. 它具有强大的事务支持, 关联关系, 预读和延迟加载,读取复制等功能
1、定义User的实体类
'user' 对应的是表名
id可以写也可以不写会有一个默认的id属性
Sequelize+列类型
db连接数据库的配置
User.hasOne 说明是一对一的关联
foreignKey 值是Sex的主键id
sourceKey 值是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