express+mysql+vue,从零搭建一个商城管理系统11--使用Sequelize

提示：学习express，搭建管理系统

---

前言

需求：主要学习express，所以先写service部分

一、安装sequelize和mysql2

sequelize中文官网
sequelize匹配使用mysql2

npm install sequelize mysql2 --save

二、修改config/db.js

config/db.js

const {Sequelize} = require('sequelize');
const dbConfig = {host:'localhost',ipport:'3306',user:'root',password:'123456',database:'express_service'
}
const sequelize = new Sequelize(dbConfig.database,dbConfig.user,dbConfig.password,{host:dbConfig.host,dialect:'mysql',port:dbConfig.ipport,pool: {max: 5,           // 最大连接数min: 0,           // 最小连接数idle: 10000,      // 释放连接的最长空闲时间msacquire: 30000,   // 连接错误后，重新连接的间隔时间ms},
});const connectDb = async()=>{try{await sequelize.authenticate();console.log('数据库连接成功√√√√√');}catch{console.log('数据库连接失败×××××');}
}
connectDb();module.exports = sequelize;

三、修改models/user.js，models/shop.js，models/goods.js

models/user.js

const { DataTypes } = require('sequelize');
const sequelize = require('../config/db');const UserModel = sequelize.define('User',{id: {type: DataTypes.INTEGER,autoIncrement: true,primaryKey: true},userId:DataTypes.STRING,userName:{type: DataTypes.STRING,allowNull: false,unique: true,},password:DataTypes.STRING,
},{tableName:'user'
});//无user表创建user表
UserModel.sync();
module.exports = UserModel;

models/shop.js

const { DataTypes } = require('sequelize');
const sequelize = require('../config/db');const ShopModel = sequelize.define('Shop',{id: {type: DataTypes.INTEGER,autoIncrement: true,primaryKey: true},shopId:DataTypes.STRING,shopName:{type: DataTypes.STRING,allowNull: false,unique: true,},createId:DataTypes.STRING,
},{tableName:'shop'
});//无shop表创建shop表
ShopModel.sync();
module.exports = ShopModel;

models/goods.js

const { DataTypes } = require('sequelize');
const sequelize = require('../config/db');const GoodsModel = sequelize.define('Goods',{id: {type: DataTypes.INTEGER,autoIncrement: true,primaryKey: true},//商品IDgoodsId:DataTypes.STRING,//商品名称goodsName:{type: DataTypes.STRING,allowNull: false,unique: true,},//创建人userIdcreateId:DataTypes.STRING,//商户IDshopId:DataTypes.STRING,//商户名shopName:DataTypes.STRING,//价格price:DataTypes.FLOAT,//库存inventory:DataTypes.INTEGER,//规格specs:DataTypes.STRING,
},{tableName:'goods'
});//无goods表创建goods表
GoodsModel.sync();
module.exports = GoodsModel;

四、新建dao/user.js，dao/shop.js，dao/goods.js

dao/user.js

const UserModel = require('../models/user');
const bcrypt = require('../config/bcrypt');
const md5 = require('md5');
const jwt = require('../config/jwt');
const secretKey = 'longlongago';const errFun = (msg,code)=>{return {code:code||500,success:false,msg:msg||'操作失败'}
}
const sucFun = (data,msg)=>{return {code:200,success:true,msg:msg||'操作成功',data,}
}
const UserDao = {//注册register:async(data)=>{//验证账号密码格式const validateResult =UserDao.validateUserNameAndPassword(data);if(!validateResult.success)return errFun(validateResult.msg);//生成userId passwordconst userId = md5(data.userName+secretKey+new Date().getTime());const password = bcrypt.hash(data.password+secretKey);//添加usertry {const user = await UserModel.create({userId,userName: data.userName,password});if(!user) errFun('注册失败！');return sucFun(user,'注册成功！')}catch(err){let errors = (err.errors||[])[0]||{};let messsage = '注册失败';(errors.message||'').indexOf('userName must be unique')>-1&&(messsage = '用户名已存在');(errors.message||'').indexOf('userName cannot be null')>-1&&(messsage = '用户名不能为空');return errFun(messsage);}},//登录login:async(data)=>{//验证账号密码格式const validateResult =UserDao.validateUserNameAndPassword(data);if(!validateResult.success)return errFun(validateResult.msg);//查询用户是否存在try{const user = await UserModel.findOne({ where: {userName:data.userName} });if(!user)return errFun('该用户不存在');//验证密码是否正确const comparePassword = bcrypt.compare(data.password+secretKey,user.password);if(!comparePassword)return errFun('密码错误');//生成tokenuser.token = jwt.sign(data);return sucFun(user,'登录成功');}catch(err){return errFun('登录失败');}},//验证用户名密码validateUserNameAndPassword:(data)=>{//只包含大小写字母数字,包含1种即可let nameReg = /[\da-zA-z]{6,16}$/;let nameValidate = nameReg.test(data.userName);if(!nameValidate)return errFun('用户名格式错误');//大写字母，小写字母，特殊符号，包含2种以上passwordReg = /^(?![\d]+$)(?![a-z]+$)(?![A-Z]+$)(?![~!@#$%^&*.]+$)[\da-zA-z~!@#$%^&*.]{6,16}$/;let passwordValidate = passwordReg.test(data.password);if(!passwordValidate)return errFun('密码格式错误');return sucFun({},'用户名和密码格式正确');},//获取用户列表queryUserList:async()=>{//根据userName查询usertry{const users = await UserModel.findAll();if(users&&users.length>0)return sucFun(users,'查询用户列表成功');return errFun('查询用户列表失败');}catch(err){return errFun('查询失败');}},
}module.exports = UserDao;

dao/shop.js

const ShopModel = require('../models/shop');
const md5 = require('md5');
const secretKey = 'longlongago';const errFun = (msg,code)=>{return {code:code||500,success:false,msg:msg||'操作失败'}
}
const sucFun = (data,msg)=>{return {code:200,success:true,msg:msg||'操作成功',data,}
}
const ShopDao = {//注册addShopByUserId:async(data)=>{//验证账号密码格式const validateResult =ShopDao.validateShopName(data);if(!validateResult.success)return errFun(validateResult.msg);//添加商户const shopId = md5(data.shopName + secretKey + new Date().getTime());try{const shop = await ShopModel.create({ shopId, createId:data.createId, shopName:data.shopName });if(!shop)errFun('添加商户失败');return sucFun(shop,'添加用户成功')}catch(err){let errors = (err.errors||[])[0]||{};let messsage = '添加商户失败';(errors.message||'').indexOf('shopName must be unique')>-1&&(messsage = '商户名已存在');(errors.message||'').indexOf('shopName cannot be null')>-1&&(messsage = '商户名不能为空');return errFun(messsage);}},//验证商户名密码validateShopName:(data)=>{//只包含大小写字母数字,包含1种即可let nameReg = /[\da-zA-z]{6,16}$/;let nameValidate = nameReg.test(data.shopName);if(!nameValidate)return errFun('商户名格式错误');return sucFun({},'商户名格式正确');},
}module.exports = ShopDao;

dao/goods.js

const GoodsModel = require('../models/goods');
const md5 = require('md5');
const secretKey = 'longlongago';const errFun = (msg,code)=>{return {code:code||500,success:false,msg:msg||'操作失败'}
}
const sucFun = (data,msg)=>{return {code:200,success:true,msg:msg||'操作成功',data,}
}
const GoodsDao = {//注册addGoodsByShopId:async(data)=>{//生成goodsIdconst goodsId = md5(data.goodsName+secretKey+new Date().getTime());try{const goods = await GoodsModel.create({goodsId,goodsName:data.goodsName,createId:data.createId,shopId:data.shopId,shopName:data.shopName,price:data.price,inventory:data.inventory,specs:data.specs,});if(!goods)return errFun('添加商品失败');return sucFun(goods,'添加商品成功');}catch(err){let errors = (err.errors||[])[0]||{};console.log(JSON.stringify(errors))let messsage = '添加商品失败';(errors.message||'').indexOf('goodsName must be unique')>-1&&(messsage = '商品名已存在');(errors.message||'').indexOf('goodsName cannot be null')>-1&&(messsage = '商品名不能为空');return errFun(messsage);}},//通过商品ID查询商品信息queryGoodsByGoodsId:async(data)=>{//根据goodsId查询goodsconst goods = GoodsModel.findOne({where:{goodsId:data.goodsId}});if(!goods)errFun('商品已下架或不存在');return sucFun(goods,'添加商品成功');},
}module.exports = GoodsDao;

五、修改routes/user.js，routes/shop.js，routes/goods.js

routes/user.js

const UserDao = require('../dao/user');
const userRoutes = (router)=>{router.post('/user/register',async (req,res)=>{const result = await UserDao.register(req.body);res.json(result);});router.post('/user/login',async (req,res)=>{const result = await UserDao.login(req.body);res.json(result);});router.post('/user/queryUserList',async (req,res)=>{const result = await UserDao.queryUserList(req.body);res.json(result);});
}
module.exports = userRoutes;

routes/shop.js

const ShopDao = require('../dao/shop');
const shopRoutes = (router)=>{router.post('/shop/addShopByUserId',async (req,res)=>{const result = await ShopDao.addShopByUserId(req.body);res.json(result);});
}
module.exports = shopRoutes;

routes/goods.js

const GoodsDao = require('../dao/goods');
const goodsRoutes = (router)=>{router.post('/goods/addGoodsByShopId',async (req,res)=>{const result = await GoodsDao.addGoodsByShopId(req.body);res.json(result);});
}
module.exports = goodsRoutes;

六、添加商品

删除原有数据库表格，新建用户，新建商户，新建商品可以重新创建对应的表

url:http://localhost:1990/goods/addGoodsByShopId
params:{
“goodsName”: “乐事薯片”,
“createId”: “94bca74490de0f84e1f478ed602123d3”,
“shopId”: “098a0c4828d7d0c78f42e6124f911a77”,
“shopName”: “shop01”,
“inventory”: 200,
“price”: 6.40,
“specs”: “160克/罐”
}

总结

踩坑路漫漫长@~@