Express+mysql實現資料庫訪問

來自專欄各種筆記

在config目錄下新建db.js文件，配置資料庫信息

var mysql = require("mysql") var pool = mysql.createPool({ host:"localhost", user:"root", password:"123456", database:"dbtest" })//資料庫連接配置

function query(sql,callback){
pool.getConnection(function(err,connection){
connection.query(sql, function (err,rows) {
callback(err,rows)
connection.release()
})
})
}//對資料庫進行增刪改查操作的基礎

exports.query = query

在service目錄下users.js文件中

const db = require(../config/db) let show = () => { return new Promise((resolve, reject) => { db.query(select * from user, (err, rows) => { if(err) { reject(err); } resolve(rows); }) }) }//顯示全部（select*）

let select = (attributename, attribute) => {
return new Promise((resolve, reject) => {
db.query(`select * from user where ${attributename} = ${attribute}`, (err, rows) => {
if(err) {
reject(err);
}
resolve(rows);
})
})
}//查詢一行（傳參)

let update = (updateattributename, newdata,attributename,attribute) => {
return new Promise((resolve, reject) => {
db.query(`update user set ${updateattributename} = ${newdata} where ${attributename} = ${attribute}`,(err,rows) => {
if(err) {
reject(err);
}
resolve(rows);
})
})
}//修改

let insert = (attributenames, attributes) => {
return new Promise((resolve, reject) => {
db.query(`insert into user ${attributenames} values ${attributes}`, (err,rows) => {
if(err) {
reject(err);
}
resolve(rows);
})
})
}//增加

exports.show = show
exports.select = select
exports.update = update
exports.insert = insert

在routes目錄下index.js中，調用

var express = require(express); var router = express.Router();

router.get(/show, async (req, res, next) => {
console.log(req.session.user)
try {
console.log(req.session.user)
let result = await require(../services/users).show();
res.send(result);
} catch (e) {
res.send(e);
}
})

router.get(/select, async (req, res, next) => {
try {
let param = req.query;
for (x in param) {
console.log(x + + param[x]);
let result = await require(../services/users).select(x, param[x]);
res.send(result);
}
} catch (e) {
res.send(e);
}
})
router.get(/update, async (req, res, next) => {
try {
let param = req.query;
let attributename = [], attribute = [];
for (x in param) {
console.log(x + + param[x])
attributename.push(x);
attribute.push(param[x])
}
let result = await require(../services/users).update(attributename[0], attribute[0], attributename[1], attribute[1]);
res.send(result);
} catch (e) {
res.send(e);
}
})

router.get(/insert, async (req, res, next) => {
try {
let param = req.query;
for (x in param) {
console.log(x + + param[x]);
let result = await require(../services/users).insert(x, param[x]);
res.send(result);
}
} catch (e) {
res.send(e);
}
})

module.exports = router;

終端運行npm start 就可以在localhost:3000進行post和get請求了。

這裡使用Postman工具。