1、mysql文件
const fs = require('fs')
const express = require('express');
const db = require("./db");
// 创建服务器实例对象
const app = express();
const bodyParser = require('body-parser');
const path = require('path');
const multer = require('multer');
const storage = multer.diskStorage({
//设置上传后文件路径,uploads文件夹会自动创建。
destination: function (req, file, cb) {
cb(null, './public/images/');
},
//给上传文件重命名,获取添加后缀名
filename: function (req, file, cb) {
cb(null, file.fieldname + '-' + Date.now())
},
});
const upload = multer({
storage: storage,
limits: { fileSize: 100 * 1024 * 1024 },
files: 9
}).single('capture')
// 导入和配置cors跨域
const cors = require('cors');
app.use(cors());
// 配置解析表单数据的中间件
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static(path.join(__dirname, 'public')));
// 上传图片
app.post('/uploadFile', (req, res) => {
upload(req, res, function (err) {
if (err instanceof multer.MulterError) {
// 发生错误
return res.send(req.err);
} else if (err) {
// 发生错误
return res.send(req.err);
}
// 一切都好
res.send(req.file);
})
});
// 新增
app.post('/content', (req, res) => {
// 产生唯一id 并把数据存入数据库
const sql = 'INSERT INTO content_table (id,title,categoryId,categoryName,markdown,createTime,urls) VALUE (?,?,?,?,?,?,?);'
db.query(sql, [Date.now(), req.body.title, req.body.categoryId,req.body.categoryName, req.body.markdown, new Date().toLocaleString(), JSON.stringify(req.body.urls)], async (err, results) => {
if (err) {
return console.log(err.message)
}
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
res.header("Content-Type", "application/json;charset=utf-8");
res.status(200).send({
code: 0,
message: "添加成功",
data: null
})
})
});
const deletefolder = (urls) => {
return new Promise((resolve, reject) => {
for (const url of urls) {
fs.unlinkSync(`./public${url}`);
}
resolve()
})
}
// 删除
app.delete('/content', (req, res) => {
const sql = 'SELECT * FROM content_table where id= ?;'
db.query(sql, [req.query.id], async (err, results) => {
if (err) {
return console.log(err.message)
}
const urls = JSON.parse(results[0].urls)
deletefolder(urls).then(() => {
const sql = 'DELETE FROM content_table WHERE id = ?'
db.query(sql, [req.query.id], async (err, results) => {
if (err) {
return console.log(err.message)
}
res.status(200).send({
code: 0,
message: '删除成功',
data: null
})
})
})
})
});
// 根据id更新数据
app.put('/content', (req, res) => {
const sql = 'UPDATE content_table SET title = ?, markdown = ?, categoryId = ?, categoryName = ?,urls = ?, updateTime = ? WHERE id = ?'
db.query(sql, [req.body.title, req.body.markdown, req.body.categoryId,req.body.categoryName, JSON.stringify(req.body.urls), new Date().toLocaleString(), req.body.id], async (err, results) => {
if (err) {
return console.log(err.message)
}
res.status(200).send({
code: 0,
message: '更新成功',
data: null
})
})
});
// 根据id查询数据库
app.get('/content', (req, res) => {
const sql = 'SELECT * FROM content_table where id= ?;'
db.query(sql, [req.query.id], async (err, results) => {
if (err) {
return console.log(err.message)
}
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
res.header("Content-Type", "application/json;charset=utf-8");
res.status(200).send({
code: 0,
data: results[0],
message: "ok",
})
})
});
// 查询列表
app.get('/list', (req, res) => {
const page = req.query?.page ?? 1
const pageSize = req.query?.pageSize ?? 10
const title = decodeURIComponent(req.query?.title ?? '')
const sql = 'SELECT COUNT(*) FROM content_table;'
console.log('req.query', req.query)
db.query(sql, async (err, ret) => {
const sql = `SELECT * FROM content_table WHERE title LIKE "%${title}%" LIMIT ${(page - 1) * pageSize},${pageSize};`
db.query(sql, async (err, results) => {
if (err) {
return console.log(err.message)
}
res.status(200).send({
code: 0,
data: results,
total: ret[0]['COUNT(*)']
})
})
})
})
app.get('/', (req, res) => {
res.send('hello world')
})
app.listen(3001);
console.log('start http://localhost:3001/')
2、连接数据库
//数据库的相关配置
const mysql = require('mysql')
let db = mysql.createPool({
host: 'localhost',
port: '3306',
user: 'xxx',
password: 'xxx',
database: 'xx'
})
module.exports = db