node.js: mysql sequelize es6 ORM (updated)
# encoding: utf-8
# 版权所有 ${YEAR} ©涂聚文有限公司
# 许可信息查看：
# 描述：
# Author : geovindu,Geovin Du 涂聚文.
# IDE : PyCharm 2023.1 python 3.11
# OS : windows 10
# Datetime : ${DATE} ${TIME}
# User : ${USER}
# Product : ${PRODUCT_NAME}
# Project : ${PROJECT_NAME}
# File : ${NAME}.py
# explain : 学习
/**
@description ${PRODUCT_NAME}
@author geovindu ${USER}
@project ${PROJECT_NAME}
@package ${PACKAGE_NAME}
@file ${FILE_NAME}
@ide webstorm 2023.1
@os windows 10
@database mysql 8.0 sql server 2019 postgreSQL 16
@dev node 20 vue.js 3.0 windows10
@date Created in ${YEAR}/${MONTH}/${DAY} ${TIME}
@edate eddit in
*/
1.第一种方式：
/**
* description:
* product: WebStorm
* project vuetstest
* File:dbconfig.js
* ds:$
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 8:40
*/
/**
*
* @type {{dialect: string, PASSWORD: string, pool: {min: number, max: number, idle: number, acquire: number}, HOST: string, USER: string, DB: string}}
*/
const dbConfig = {
HOST: "localhost",
USER: "root",
PASSWORD: "888888",
DB: "geovindu",
dialect: "mysql",
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
};
export default dbConfig;
import Tutorial from "./tutorial.js";
/**
* description:
* product: WebStorm
* project vuetstest
* File: server/models/userinfo.js
* ds:$
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 8:58
*/
/**
*
* @param sequelize
* @param Sequelize
* @returns {*}
* @constructor
*/
const UserInfo = (sequelize, Sequelize) => {
const UserInfo = sequelize.define("userInfo", {
userName: {
type: Sequelize.STRING
},
userReal:{
type:Sequelize.STRING
},
userPassword: {
type: Sequelize.STRING
},
userIsOk: {
type: Sequelize.BOOLEAN
},
userMail:
{
type:Sequelize.STRING
},
userMobile:
{
type:Sequelize.STRING
}
});
return UserInfo;
};
export default UserInfo;
/**
* description:
* product: WebStorm
* project vuetstest
* File: server/models/toutorial.js
* ds:$ npm install express sequelize mysql2 cors
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 8:42
*/
/**
* 实体类
* @param sequelize
* @param Sequelize
* @returns {*}
* @constructor
*/
const Tutorial = (sequelize, Sequelize) => {
const Tutorial = sequelize.define("tutorial", {
title: {
type: Sequelize.STRING
},
description: {
type: Sequelize.STRING
},
published: {
type: Sequelize.BOOLEAN
}
});
return Tutorial;
};
export default Tutorial;
/**
* description:
* product: WebStorm
* project vuetstest
* File: server/models/index.js
* ds:$
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 8:48
*/
import dbConfig from "../dbconfig/dbConfig.js";
import Sequelize from "sequelize";
import tutorials from "./tutorial.js"
import userinfos from "./userinfo.js"
/**
*
*/
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
host: dbConfig.HOST,
dialect: dbConfig.dialect,
operatorsAliases: false,
pool: {
max: dbConfig.pool.max,
min: dbConfig.pool.min,
acquire: dbConfig.pool.acquire,
idle: dbConfig.pool.idle
}
});
const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;
db.tutorials=tutorials(sequelize, Sequelize);
db.userinfos=userinfos(sequelize,Sequelize);
export default db;
/**
* description:
* product: WebStorm
* project vuetstest
* File: server/controllers/userinfo.js
* ds:$
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 9:03
*/
import db from "../models/index.js";
const UserInfo = db.userinfos;
const Op = db.Sequelize.Op;
/**
*
* @param {*} req
* @param {*} res
* @returns
*/
const usercreate = (req, res) => {
// Validate request
if (!req.body.title) {
res.status(400).send({
message: "Content can not be empty!"
});
return;
}
// Create a Tutorial
const userInfo = {
userName: req.body.userName,
userPassword: req.body.userPassword,
userIsOk: req.body.userIsOk ? req.body.userIsOk : false,
userMail:req.body.userMail,
userMobile:req.body.userMobile
};
// Save Tutorial in the database
UserInfo.create(userInfo)
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while creating the userinfos."
});
});
};
/**
*
* @param {*} req
* @param {*} res
*/
const userfindAll = (req, res) => {
const userName = req.query.userName;
var condition = userName ? { userName: { [Op.like]: `%${userName}%` } } : null;
UserInfo.findAll({ where: condition })
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving userinfos."
});
});
};
/**
*
* @param {*} req
* @param {*} res
*/
const userfindOne = (req, res) => {
const id = req.params.id;
UserInfo.findByPk(id)
.then(data => {
if (data) {
res.send(data);
} else {
res.status(404).send({
message: `Cannot find userinfos with id=${id}.`
});
}
})
.catch(err => {
res.status(500).send({
message: "Error retrieving userinfos with id=" + id
});
});
};
/**
*
* @param {*} req
* @param {*} res
*/
const userupdate = (req, res) => {
const id = req.params.id;
UserInfo.update(req.body, {
where: { id: id }
})
.then(num => {
if (num == 1) {
res.send({
message: "usrinfos was updated successfully."
});
} else {
res.send({
message: `Cannot update userinfos with id=${id}. Maybe userinfos was not found or req.body is empty!`
});
}
})
.catch(err => {
res.status(500).send({
message: "Error updating userinfos with id=" + id
});
});
};
/**
*
* @param {*} req
* @param {*} res
*/
const userdeleteid = (req, res) => {
const id = req.params.id;
UserInfo.destroy({
where: { id: id }
})
.then(num => {
if (num == 1) {
res.send({
message: "userinfos was deleted successfully!"
});
} else {
res.send({
message: `Cannot delete userinfos with id=${id}. Maybe userinfos was not found!`
});
}
})
.catch(err => {
res.status(500).send({
message: "Could not delete userinfos with id=" + id
});
});
};
/**
*
* @param {*} req
* @param {*} res
*/
const userdeleteAll = (req, res) => {
UserInfo.destroy({
where: {},
truncate: false
})
.then(nums => {
res.send({ message: `${nums} userinfos were deleted successfully!` });
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while removing all userinfos."
});
});
};
/**
*
* @param {*} req
* @param {*} res
*/
const findAlluserIsOk = (req, res) => {
UserInfo.findAll({ where: { userIsOk: true } })
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving userinfos."
});
});
};
// 这个命名问题 tutorials
// 使用 ES6 的默认导出语法，直接导出包含所有函数的对象
export default
{
findAlluserIsOk,
userdeleteAll,
userdeleteid,
userupdate,
userfindOne,
userfindAll,
usercreate
};
/**
* description:
* product: WebStorm
* project vuetstest
* File: server/controllers/toutorial.js
* ds:$
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 8:49
*/
import db from "../models/index.js";
const Tutorial = db.tutorials;
const Op = db.Sequelize.Op;
/**
*
* @param req
* @param res
*/
const create = (req, res) => {
// Validate request
if (!req.body.title) {
res.status(400).send({
message: "Content can not be empty!"
});
return;
}
// Create a Tutorial
const tutorial = {
title: req.body.title,
description: req.body.description,
published: req.body.published ? req.body.published : false
};
// Save Tutorial in the database
Tutorial.create(tutorial)
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while creating the Tutorial."
});
});
};
/**
*
* @param req
* @param res
*/
const findAll = (req, res) => {
const title = req.query.title;
var condition = title ? { title: { [Op.like]: `%${title}%` } } : null;
Tutorial.findAll({ where: condition })
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving tutorials."
});
});
};
/**
*
* @param req
* @param res
*/
const findOne = (req, res) => {
const id = req.params.id;
Tutorial.findByPk(id)
.then(data => {
if (data) {
res.send(data);
} else {
res.status(404).send({
message: `Cannot find Tutorial with id=${id}.`
});
}
})
.catch(err => {
res.status(500).send({
message: "Error retrieving Tutorial with id=" + id
});
});
};
/**
*
* @param req
* @param res
*/
const update = (req, res) => {
const id = req.params.id;
Tutorial.update(req.body, {
where: { id: id }
})
.then(num => {
if (num == 1) {
res.send({
message: "Tutorial was updated successfully."
});
} else {
res.send({
message: `Cannot update Tutorial with id=${id}. Maybe Tutorial was not found or req.body is empty!`
});
}
})
.catch(err => {
res.status(500).send({
message: "Error updating Tutorial with id=" + id
});
});
};
/**
*
* @param req
* @param res
*/
const deleteid = (req, res) => {
const id = req.params.id;
Tutorial.destroy({
where: { id: id }
})
.then(num => {
if (num == 1) {
res.send({
message: "Tutorial was deleted successfully!"
});
} else {
res.send({
message: `Cannot delete Tutorial with id=${id}. Maybe Tutorial was not found!`
});
}
})
.catch(err => {
res.status(500).send({
message: "Could not delete Tutorial with id=" + id
});
});
};
/**
*
* @param req
* @param res
*/
const deleteAll = (req, res) => {
Tutorial.destroy({
where: {},
truncate: false
})
.then(nums => {
res.send({ message: `${nums} Tutorials were deleted successfully!` });
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while removing all tutorials."
});
});
};
/**
*
* @param req
* @param res
*/
const findAllPublished = (req, res) => {
Tutorial.findAll({ where: { published: true } })
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while retrieving tutorials."
});
});
};
// 这个命名问题 tutorials
// 使用 ES6 的默认导出语法，直接导出包含所有函数的对象
/**
*
*/
export default
{
findAllPublished,
deleteAll,
deleteid,
update,
findOne,
findAll,
create
};
/**
* description:
* product: WebStorm
* project vuetstest
* File: server/routes/userinfo.js
* ds:$
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 11:15
*/
import express from "express"
import userinfos from "../controllers/userinfo.js"
/**
*
* @param app
*/
const uroutes= app => {
var router = express.Router();
router.post("/user",userinfos.usercreate)
router.get("/user", userinfos.userfindAll);
router.get("/user/userIsOk", userinfos.findAlluserIsOk);
router.get("/user/:id", userinfos.userfindOne);
router.put("/user/:id", userinfos.userupdate);
router.put("/user/:id", userinfos.userdeleteid);
router.put("/user", userinfos.userdeleteAll);
app.use('/api',router);
};
export default uroutes;
/**
* description:
* product: WebStorm
* project vuetstest
* File: server/routes/toutorial.js
* ds:$
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 8:50
*/
import express from "express"
import tutorials from "../controllers/tutorial.js"
/**
*
* @param app
*/
const troutes= app => {
var router = express.Router();
// Create a new Tutorial
router.post("/tutorials/",tutorials.create)
// Retrieve all Tutorials
router.get("/tutorials/", tutorials.findAll);
// Retrieve all published Tutorials
router.get("/tutorials/published", tutorials.findAll);
// Retrieve a single Tutorial with id
router.get("/tutorials/:id", tutorials.findOne);
// Update a Tutorial with id
router.put("/tutorials/:id", tutorials.update);
// Delete a Tutorial with id
router.put("/tutorials/:id", tutorials.deleteid);
// Delete all Tutorials
router.put("/tutorials/", tutorials.deleteAll);
app.use('/api',router);
};
export default troutes;
/**
* description:
* product: WebStorm
* project vuetstest
* File: server/routes/index.js
* ds:$
* package:
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 12:56
*/
import troutes from "./tutorial.js"
import uroutes from "./userinfo.js"
/**
*
* @param app
*/
const routes=app => {
troutes(app);
uroutes(app);
};
export default routes;
/**
* description:
* product: WebStorm
* project vuetstest
* File: server.js
* ds:$
* IDE: webstorm 2023.1
* OS: windows 10
* database: mysql 8+ sql server 2019 postgreSQL 16
* dev: node 20+ vue.js 3.0+
* @author hp
* @project vuetstest
* @date 2024/8/24 8:52
*/
import express from "express";
import cors from "cors";
import db from "./models/index.js"
import routes from "./routes/index.js"
const Tutorial = db.tutorials;
//require("./app/routes/turorial.routes")(app);
const app = express();
//var routes =require("./app/routes/turorial.routes");
routes(app);
//app.routes();
var corsOptions = {
origin: "http://localhost:8080"
};
app.use(cors(corsOptions));
// parse requests of content-type - application/json
app.use(express.json());
// parse requests of content-type - application/x-www-form-urlencoded
app.use(express.urlencoded({ extended: true }));
//http://localhost:8080/models
///http://localhost:8080/tutorials
//const db = require("./app/models");
db.sequelize.sync()
.then(() => {
console.log("Synced db.");
})
.catch((err) => {
console.log("Failed to sync db: " + err.message);
});
//routes(app); //这行路由编译报错
// simple route
app.get("/", (req, res) => {
/*
if (!req.body.title) {
res.status(400).send({
message: "Content can not be empty!"
});
return;
}
*/
/*
// Create a Tutorial
const tutorial = {
title: '兴大兴',
description: '涂没有什么',
published: false,
createdAt:'2024-08-05',
updatedAt:'2024-08-05'
};
// Save Tutorial in the database
Tutorial.create(tutorial)
.then(data => {
res.send(data);
})
.catch(err => {
res.status(500).send({
message:
err.message || "Some error occurred while creating the Tutorial."
});
});
*/
res.json({ message: "数据添加成功！." });
});
// set port, listen for requests http://localhost:8080
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
console.log(`Server is running on port ${PORT}.`);
});
2.第二种方式
另一种写法也可以：
/**
@description WebStorm
@author geovindu geovindu
@project vuedemo
@package
@file BookKindList.js
@ide webstorm 2023.1
@database mysql 8.0 sql server 2019 postgreSQL 16
@dev node 20 vue.js 3.0
@date Created in 2024/08/19 11:06
@edate eddit in
*/
const bookKind = require("../controllers/BookKindList");
module.exports = app => {
const bookKind = require("../controllers/BookKindList");
var router = require("express").Router();
// Create a new BookKindList
router.post("/bookKinds/", bookKind.bookKindcreate);
//Create a new userinfo
//Retrieve all BookKindList
router.get("/bookKinds/", bookKind.bookKindfindAll);
//Retrieve all published userinfos
router.get("/bookKinds/", bookKind.bookKindAlluserIsOk);
//Retrieve a single BookKindList with id
router.get("/bookKinds/:id", bookKind.bookKindfindOne);
//Update a BookKindList with id
router.put("/bookKinds/:id", bookKind.bookKindupdate);
//Delete a BookKindList with id
router.delete("/bookKinds/:id", bookKind.bookKinddeleteid);
//Delete all BookKindList
router.delete("/bookKinds/", bookKind.bookKinddeleteAll);
app.use('/api', router);
};
/**
@description WebStorm
@author geovindu geovindu
@project vuedemo
@package
@file turorial.routes.js
@ide webstorm 2023.1
@database mysql 8.0 sql server 2019 postgreSQL 16
@dev node 20 vue.js 3.0
@date Created in 2024/08/19 10:45
@edate eddit in
*/
/**
*
* @param app
*/
const userinfos = require("../controllers/userinfo.controller");
module.exports = app => {
const tutorials = require("../controllers/tutorial.controller.js");
const userinfos=require("../controllers/userinfo.controller")
var router = require("express").Router();
// Create a new Tutorial
router.post("/tutorials/", tutorials.create);
//Create a new userinfo
router.post("/userinfos/", userinfos.usercreate);
// Retrieve all Tutorials
router.get("/tutorials/", tutorials.findAll);
//Retrieve all userinfos
router.get("/userinfos/", userinfos.userfindAll);
//登錄
router.get("/userlogin/", userinfos.userLogin);
// Retrieve all published Tutorials
router.get("/tutorials/published", tutorials.findAllPublished);
//Retrieve all published userinfos
router.get("/userinfos/isok", userinfos.findAlluserIsOk);
// Retrieve a single Tutorial with id
router.get("/tutorials/:id", tutorials.findOne);
//Retrieve a single userinfos with id
router.get("/userinfos/:id", userinfos.userfindOne);
// Update a Tutorial with id
router.put("/tutorials/:id", tutorials.update);
//Update a userinfos with id
router.put("/userinfos/:id", userinfos.userupdate);
// Delete a Tutorial with id
router.delete("/tutorials/:id", tutorials.delete);
//Delete a userinfos with id
router.delete("/userinfos/:id", userinfos.userdeleteid);
// Delete all Tutorials
router.delete("/tutorials/", tutorials.deleteAll);
//Delete all userinfos
router.delete("/userinfos/", userinfos.userdeleteAll);
app.use('/api', router);
};
/**
@description WebStorm
@author geovindu geovindu
@project vuedemo
@package
@file server.js
@ide webstorm 2023.1
@database mysql 8.0 sql server 2019 postgreSQL 16
@dev node 20 vue.js 3.0
@date Created in 2024/08/19 10:46
@edate eddit in
*/
const express = require("express");
const cors = require("cors");
const app = express();
var corsOptions = {
origin: "http://localhost:8082"
};
app.use(cors(corsOptions));
// parse requests of content-type - application/json
app.use(express.json());
// parse requests of content-type - application/x-www-form-urlencoded
app.use(express.urlencoded({ extended: true }));
const db = require("./models");
db.sequelize.sync()
.then(() => {
console.log("Synced db.");
})
.catch((err) => {
console.log("Failed to sync db: " + err.message);
});
// // drop the table if it already exists
// db.sequelize.sync({ force: true }).then(() => {
// console.log("Drop and re-sync db.");
// });
// simple route
app.get("/", (req, res) => {
res.json({ message: "Welcome to bezkoder application." });
});
require("./routes/turorial.routes")(app);
require("./routes/BookKindList")(app);
// set port, listen for requests
const PORT = process.env.PORT || 8081;
app.listen(PORT, () => {
console.log(`Server is running on port ${PORT}.`);
});
第3种方式：
/**
@description WebStorm
@author geovindu geovindu
@project vuedemo
@package
@file userinfo.js
@ide webstorm 2023.1
@os windows 10
@database mysql 8.0 sql server 2019 postgreSQL 16
@dev node 20 vue.js 3.0 windows10
@date Created in 2024/08/26 10:35
@edate eddit in
*/
module.exports = app => {
const userinfos=require("../controllers/userinfo.controller")
var router = require("express").Router();
//Create a new userinfo
router.post("/userinfos/", userinfos.usercreate);
//Retrieve all userinfos
router.get("/userinfos/", userinfos.userfindAll);
//登錄
router.get("/userlogin/", userinfos.userLogin);
//Retrieve all published userinfos
router.get("/userinfos/", userinfos.findAlluserIsOk);
//Retrieve a single userinfos with id
router.get("/userinfos/:id", userinfos.userfindOne);
//Update a userinfos with id
router.put("/userinfos/:id", userinfos.userupdate);
//Delete a userinfos with id
router.delete("/userinfos/:id", userinfos.userdeleteid);
//Delete all userinfos
router.delete("/userinfos/", userinfos.userdeleteAll);
app.use('/api', router);
};
/**
* routes/tutorial.routes.js
* node 20 vue.js 3.0
* ide: WebStorm 2023.1
* mysql 8.0
* npm install express sequelize mysql2 cors
* ///http://localhost:8081/api/tutorials
* //http://localhost:8081/api/tutorials/2
* //http://localhost:8081/api/userinfos
* */
/**
*
* @param app
*/
module.exports = app => {
const tutorials = require("../controllers/tutorial.controller.js");
var router = require("express").Router();
// Create a new Tutorial
router.post("/tutorials/", tutorials.create);
// Retrieve all Tutorials
router.get("/tutorials/", tutorials.findAll);
// Retrieve all published Tutorials
router.get("/tutorials/published", tutorials.findAllPublished);
// Retrieve a single Tutorial with id
router.get("/tutorials/:id", tutorials.findOne);
// Update a Tutorial with id
router.put("/tutorials/:id", tutorials.update);
// Delete a Tutorial with id
router.delete("/tutorials/:id", tutorials.delete);
// Delete all Tutorials
router.delete("/tutorials/", tutorials.deleteAll);
app.use('/api', router);
};
/**
@description WebStorm
@author geovindu geovindu
@project vuedemo
@package
@file index.js
@ide webstorm 2023.1
@os windows 10
@database mysql 8.0 sql server 2019 postgreSQL 16
@dev node 20 vue.js 3.0 windows10
@date Created in 2024/08/26 10:29
@edate eddit in
*/
const troutes =require("./turorial.js")
const uroutes =require("./userinfo.js")
/**
*
* @param app
*/
module.exports=app => {
troutes(app);
uroutes(app);
};
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
