Nodejs基础:数据库CURD

1 请求参数获取

1.0 GET请求

• 路由

/get/test/:id

• URL

http://localhost:9999/get/test/1?name=xiaoxiao

• 获取id

req.params.id

• 获取name

req.query.name

• 代码段

app.get("/get/test/:id", function(req, res){
    console.log("get test");
    // get parameter in url
    var name = req.query.name;
    var id = req.params.id;
    // res.send("TEST GET");
    res.json({code:200, msg:"测试json", name:name, id:id});
})

1.2 POST请求

• 路由

/post/test

• URL

http://localhost:9999/post/test

• 获取Body
  需要引入body-parser,用于解析body.

{
    "name":"xiaoxiao",
    "address":"中国",
    "data":{
        "flag":"测试嵌套"
    },
    "pageStart":2,
    "pageContent":2
}

req.body.name

• 代码段

var bodyParser = require("body-parser")
var urlencodedParser = bodyParser.json()
app.post("/post/test", urlencodedParser, function(req, res){
    console.log("post test");
    var name = req.body.name;
    var address = req.body.address;
    var flag = req.body.data.flag;
    var pageContent = req.body.pageContent;
    var pageStart = (req.body.pageStart-1)*pageContent;
    
    var response = {
        "name":name,
        "address":address,
        "flag": flag
    };
 
    res.json(response);
})

req.params.id

2 数据库操作

2.1 连接数据库

var express = require("express")
var app = express()
var bodyParser = require("body-parser")
var mysql = require("mysql")

var connection = mysql.createConnection({
    host : "localhost",
    port: "3306",
    user : "root",
    password : "123456",
    database: "data_repository"
});

connection.connect();

2.2 新增数据

app.post("/userinfos/add", urlencodedParser, function(req, res){
    var addSentence = "insert into userinfos(username, password, email, sex, position, telephone_num) values(?,?,?,?,?,?)";
    var username = req.body.username;
    var password = req.body.password;
    var email = req.body.email;
    var sex = req.body.sex;
    var position = req.body.position;
    var telephone_num = req.body.telephoneNum;
    var addParams = [username, password, email, sex, position, telephone_num];
    connection.query(addSentence, addParams, function(err, result){
        if(err){
            console.log("[Insert Error] - ", err.message);
            var response = {
                "code" : 201,
                "msg" : err.message
            };
            res.json(response);
            // return;
        }else{
            var response = {
                "code" : 200,
                "msg" : "success",
                "res" : result
            };
            res.json(response);
        }
        
    });
})

2.3 删除数据

app.delete("/userinfos/delete/:id", function(req, res){
    var delId = req.params.id;
    var delSentence = "delete from userinfos where id="+delId;
    connection.query(delSentence, function(err, result){
        if(err){
            console.log("[Delete Error] - ", err.message);
            var response = {
                "code" : 201,
                "msg" : err.message
            };
            res.json(response);
            // return;
        }else{
            var response = {
                "code" : 200,
                "msg" : "success",
                "res" : result
            };
            res.json(response);
        }
    });
    
})

2.4 修改数据

app.put("/userinfos/update/:id", urlencodedParser, function(req, res){
    var updateSentence = "update userinfos set username=?, position=? where id=?";
    var username = req.body.username;
    var password = req.body.password;
    var email = req.body.email;
    var sex = req.body.sex;
    var position = req.body.position;
    var telephone_num = req.body.telephoneNum;
    var queryId = req.params.id;
    var updateParams = [username, position, queryId];
    connection.query(updateSentence, updateParams, function(err, result){
        if(err){
            console.log("[Update Error] - ", err.message);
            var response = {
                "code" : 201,
                "msg" : err.message
            };
            res.json(response);
            // return;
        }else{
            var response = {
                "code" : 200,
                "msg" : "success",
                "res" : result
            };
            res.json(response);
        }
    });
})

2.5 查询数据

app.post("/userinfos/query", urlencodedParser, function(req, res){
    console.log("post test");
    var querySentence = "select id,username,email from userinfos limit ?,?";
    var pageContent = req.body.pageContent;
    var pageStart = (req.body.pageStart-1)*pageContent;
    var queryParams = [pageStart, pageContent];

    connection.query(querySentence, queryParams, function(err, results, fields){
        if(err){
            var response = {
                "code" : 201,
                "msg" : err.message
            };
            logger.info(response);
            res.json(response);
        }else{
            console.log("results:",results);
            var response = {
                "code":200,
                "msg":"success",
                "datas":results
            }
            logger.info("查询成功");
            res.json(response);
        }
        
        // for (var i=0;i<results.length;i++){
        //     console.log("username:",results[i].username);
        // }
    });
})

3 启动服务

var server = app.listen(9999, function(){
    var host = server.address().address 
    var port = server.address().port 
    console.log("http://%s:%s", host, port)
})