nodejs请求mysql并返回数据

nodejs请求mysql并返回数据

请求类型

请求类型	方法
application/x-www-form-urlencoded application/json	const bodyParser = require('body-parser') // 用来解析 form-urlencode body const urlencodedParser = bodyParser.urlencoded({ extended: false })
multipart/form-data	const multipart = require('connect-multiparty') // 用来解析 form-data body const multipartMiddleware = multipart()

使用实例

npm config set registry htpps://registry.npm.taobao.org
npm install express //创建web应用和丰富http工具
npm install body-parser // 处理json，raw ，text和url
npm install multer //处理 enctype="multipart/form-data"的表单数据
npm install mysql

var express = require("express")
var mysqlInit = require("./mysqlall")
var mysql1 = new mysqlInit()
var app = express()
var bodyParser = require("body-parser")
var urlencoded = bodyParser.urlencoded({ extended: false })

var multipart = require('connect-multiparty');
var multipartMiddleware = multipart();
//设置允许跨域
app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "X-Requested-With");
    res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By", ' 3.2.1');
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});
//urlencoded换成multipartMiddleware可以接受form-data类型请求
app.post("/dataVisualization/selectOrderInfo", urlencoded, (req, res) => {
    var contentType = req.headers['content-type']
    var reqdata;
    res.setHeader("Content-Type", "application/json")
    var sql = "select id,finalTotalAmount,outTradeNo,provinceName,regionName,userName from orderinfores where time>=? and time<=?"
    
    //application/json需要data和end方法
    if (contentType.includes("application/json")) {
        var body = ""
        req.on("data", (chunk) => {
            body += chunk;
        })
        req.on('end', () => {
            reqdata = JSON.parse(body);
            console.log("json")
            mysql1.query(sql, [reqdata["startTime"], reqdata["endTime"]], (data) => {
                res.json({ "msg": "操作成功", "code": res.statusCode, "data": data })
            })
        })
        //application/x-www-form-urlencoded直接从body里面获取
    } else if (contentType.includes("application/x-www-form-urlencoded")) {
        reqdata = JSON.parse(JSON.stringify(req.body))
        mysql1.query(sql, [reqdata["startTime"], reqdata["endTime"]], (data) => {
            res.json({ "msg": "操作成功", "code": res.statusCode, "data": data })
        })

        //form-data需要multipartMiddleware类型的
    } else {
        res.json({ "msg": "操作失败,请使用json或者x-www--form-urlencoded" })
    }
})

//接受form-data类型和json类型，www-form-urlencoded不行
app.post('/file',multipartMiddleware,function(req,res){
    res.send(req.body);
})
// 只要我不处理请求参数 就没bug
app.post("/dataVisualization/selectGoodsInfo", urlencoded, (req, res) => {
    res.setHeader("ontent-Type", "application/json")
    var sql = "select id,price,sku_name skuName,year(create_time) year from sku_info"
    mysql1.query(sql, [], (data) => {
        res.json({ "msg": "操作成功", "code": res.statusCode, "data": data })
    })

})

var server = app.listen("8999", () => {
    var host = server.address().address
    var port = server.address().port
    console.log(`server listening on http://${host}:${port}`)
})

从mysql拿数据

var mysql = require("mysql")
var express = require("express")

var app = express()

function mysqlInit() {
    var pool = mysql.createPool({
        host: "localhost",
        user: "root",
        password: "password",
        database: "question",
        charset: "utf8"
    })
    //callback用来返回数据，防止结果只能在函数内使用
    this.query = (sql, params, callback) => {
        pool.getConnection(function(err, connection) {
            if (err) {
                console.log("数据库连接失败")
            } else {
                console.log("数据库连接成功")
            }
            connection.query(sql, params, (err, result) => {
                if (err) { console.log(err) } else {
                    callback(result)
                }
                connection.release();
            })
        })
    }
}
module.exports = mysqlInit