node.js express连接MySQL编写接口(手把手教学版?)
参考了博客园的 这篇文章 稍微改了一下 能跑了
单接口版
文件结构
下载依赖
npm install body-parser express mysql cors -S
为了热启动安hotnode
npm i hotnode -s
使用npm init初始化,在终端初始化package.json,一直敲回车就ok
在package.json的script下配置:
"start": "hotnode index.js"
后面启动项目就是npm start
这样会有一个错
把这个点进去修改就行了
改112行改成这样就行了
最后package.json长这样
package.json
{
"name": "garbage_backend4",
"version": "1.0.0",
"description": "",
"main": "index.js",
"dependencies": {
"body-parser": "^1.19.1",
"cors": "^2.8.5",
"express": "^4.17.2",
"hotnode": "0.0.8",
"mysql": "^2.18.1"
},
"devDependencies": {},
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start": "hotnode index.js"
},
"author": "neversleep",
"license": "ISC"
}
router/login.js
const { pool, router, Result } = require('../connect')
router.get('/', (req, res) => {
pool.getConnection((err, conn) => {
//这个sql语句要改成自己的表
conn.query("SELECT * FROM types", (e, results) => {
if(e) throw error
var dataString = JSON.stringify(results);
var data = JSON.parse(dataString)
let index
sort_arr=new Array();
for(index in data){
sort_arr.push(new Result(data[index]))
}
console.log(sort_arr)
res.json(sort_arr)
})
pool.releaseConnection(conn) // 释放连接池,等待别的连接使用
})
})
module.exports = router;
connect.js
const mysql = require('mysql')
const express = require('express')
const app = express()
const router = express.Router();
// 解析参数
const bodyParser = require('body-parser')
let login = true;
// json请求
app.use(bodyParser.json())
// 表单请求
app.use(bodyParser.urlencoded({extended: false}))
// 配置对象
const option = {
host: 'localhost',
user: 'root',
password: '123456',
port: '3306',
database: 'garbage',
connectTimeout: 5000, //连接超时
multipleStatements: false //是否允许一个query中包含多条sql语句
}
// 返回的数据库数据
//改成自己数据库的返回结构
function Result ({ id ='' , type = '', type_sub = " " }) {
this.id = id;
this.type = type;
this.type_sub = type_sub;
}
let pool;
repool()
// 断线重连机制
function repool() {
// 创建连接池
pool = mysql.createPool({
// 解构赋值
...option,
waitForConnections: true, //当无连接池可用时,等待(true)还是抛错(false)
connectionLimit: 100, //连接数限制
queueLimit: 0 //最大连接等待数(0为不限制)
})
pool.on('error', err => {
err.code === 'PROTOCOL_CONNECTION_LOST' && setTimeout(repool, 2000)
})
app.all('*', (_,__, next) => {
pool.getConnection( err => {
err && setTimeout(repool, 2000) || next()
})
})
}
module.exports = { app, pool, Result, router }
index.js
const { app, pool, Result } =require('./connect')
//下面跟这个对应
const login = require('./router/login')
app.all('*', (req, res, next) => {
//这里处理全局拦截,一定要写在最上面
next()
})
app.all('/', (req, res) => {
pool.getConnection((err, conn) => {
res.json({ type: 'test'})
pool.releaseConnection(conn) // 释放连接池,等待别的连接使用
})
})
//用哪个改哪个
app.use('/login', login)
app.listen(8088, () => {
console.log('服务启动')
})
使用postman进行测试
postman使用看这个(6p,7p就行)
多接口版
需求:
需要多个接口
文件结构
统一接口管理(api.js)
api.js
let express=require("express")
const { pool, router, Result } = require("../connect");
const menuitems=require("./menuitems")
const detail=require('./detail')
const detailgroup=require('./detailgroup')
const detailtype=require('./detailtype')
const detailtypegroup=require('./detailtypegroup')
const meunitemgroup=require('./meunitemgroup')
router.get("/",(req,res)=>{
res.send("/")
})
router.get("/menuitems",(req,res)=>{
res.send(menuitems)
})
router.get("/detail",(req,res)=>{
res.send(detail)
})
router.get("/detailgroup",(req,res)=>{
res.send(detailgroup)
})
router.get("/detailtype",(req,res)=>{
res.send(detailtype)
})
router.get("/detailtypegroup",(req,res)=>{
res.send(detailtypegroup)
})
router.get("/meunitemgroup",(req,res)=>{
res.send(meunitemgroup)
})
module.exports=router;
// const routerMiddleware = require("express").Router();
单路由
detail.js
const { pool, router} = require("../connect");
// 返回的数据库数据
// 要改成自己的数据
function Result ({ id ='' , dname = '', pid = " " }) {
// console.log(a)
this.id = id;
this.dname = dname;
this.pid = pid;
}
router.get("/detail", (req, res) => {
pool.getConnection((err, conn) => {
//这个sql语句要改成自己的用的
conn.query(
"select * from mall.menudetail",
(e, results) => {
if (e) throw error;
var dataString = JSON.stringify(results);
var data = JSON.parse(dataString);
let index;
sort_arr = new Array();
for (index in data) {
sort_arr.push(new Result(data[index]));
}
res.json(sort_arr);
}
);
pool.releaseConnection(conn); // 释放连接池,等待别的连接使用
});
});
module.exports = router;
上一版要改的地方(connect.js、index.js)
connect.js
const mysql = require('mysql')
const express = require('express')
const app = express()
const router = express.Router();
// 解析参数
const bodyParser = require('body-parser')
// let login = true;
// json请求
app.use(bodyParser.json())
// 表单请求
app.use(bodyParser.urlencoded({extended: false}))
// 配置对象
const option = {
host: 'localhost',
user: 'root',
password: '123456',
port: '3306',
// 这里要改成自己的数据库
database: 'mall',
connectTimeout: 5000, //连接超时
multipleStatements: false //是否允许一个query中包含多条sql语句
}
let pool;
repool()
// 断线重连机制
function repool() {
// 创建连接池
pool = mysql.createPool({
// 解构赋值
...option,
waitForConnections: true, //当无连接池可用时,等待(true)还是抛错(false)
connectionLimit: 100, //连接数限制
queueLimit: 0 //最大连接等待数(0为不限制)
})
pool.on('error', err => {
err.code === 'PROTOCOL_CONNECTION_LOST' && setTimeout(repool, 2000)
})
app.all('*', (_,__, next) => {
pool.getConnection( err => {
err && setTimeout(repool, 2000) || next()
})
})
}
module.exports = { app, pool, router }
index.js
const { app, pool, Result } =require('./connect')
//
// const detail= require('./router/detail')
// const types= require('./router/menuitems')
const api= require('./router/api')
app.all('*', (req, res, next) => {
//这里处理全局拦截,一定要写在最上面
next()
})
app.all('/', (req, res) => {
pool.getConnection((err, conn) => {
res.json({ type: 'test'})
pool.releaseConnection(conn) // 释放连接池,等待别的连接使用
})
})
// 接口使用
app.use('/api',api)
app.listen(8088, () => {
console.log('服务启动')
})