node.js接数据库踩坑 增删改查

var express = require('express');
var app = express();
var mysql = require('mysql');
var async = require('async');
var questions = [
   {
      msg: "",
      data: [{
      }]
   }];
//数据库配置
var connection = mysql.createConnection({
   host: 'localhost',
   user: 'root',
   password: '123456',
   database: 'test_bd'
});
//设置跨域访问
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();
});
//初始化接口数据
function initdata() {
   questions = [
      {
         msg: "",
         data: [{
         }]
      }];
}
//封装一些操作接口数据的函数
function setmsg(newmsg) {
   console.log(newmsg)
   questions[0].msg = newmsg;
}
function setdata(newdata) {
   newdata = JSON.stringify(newdata)
   // newdata = Object.getOwnPropertyNames(newdata)
   questions[0].data[0] = newdata;
}
//打开利连接
function startcon() {
   connection.connect(function (err) {
      if (err) {
         console.log("[query]-:" + err);
         return;
      }
      console.log("[connection connect] succeed!");
   })

}
//关闭连接
function endcon() {

   connection.end(function (err) {
      if (err) {
         return;
      }
      console.log("[connection end] succeed!")
   })

}
//POST   给sql,res返回   失败返回错误信息到msg、成功返回msg scueed,data:null
async function Postmakesql(sql, res) {
   console.log("@@sql@@" + sql)
   async.series([
      function (callback) {
         // 插入    posts
         console.log("开始插入数据");
         connection.query(sql, function (err, result) {
            console.log("进入数据接入");
            if (err) {
               console.log("数据插入异常");
               setmsg(err.sqlMessage);
               callback(err);
            }
            else {
               console.log(result);
               console.log("数据插入成功");
               setmsg("succeed");
               callback(result);
            }
         });
      },
   ], function (err, results) {
      res.status(200),
         res.json(questions);
      console.log(sql + "完成");
   });
}
//GET   给sql,res返回   失败返回错误信息到msg、成功返回msg scueed,data:data
async function Getmakesql(sql, res) {
   console.log(sql)
   async.series([
      function (callback) {
         // 插入    posts
         console.log("开始插入数据");
         connection.query(sql, function (err, result) {
            console.log("进入数据接入");
            if (err) {
               console.log("数据插入异常");
               setmsg(err);
               callback(err);
            }
            else {
               console.log(result);
               console.log("数据插入成功");
               if (result.length == 0)
               {
                  setmsg("没有相应数据");
               setdata(null);}
               else
               setdata(result);
               callback(result);
            }
         });
      },
   ], function (err, results) {
      res.status(200),
         res.json(questions);
      console.log(sql + "完成");
   });
}




接口
//查
app.get('/find', function (req, res) {
   //可以所有  可以单个 todo
   initdata();
   var sql = 'SELECT * FROM user';
   //按照名字查
   if (req.query.name) {
      var sql = 'SELECT * FROM user WHERE user_name=' + req.query.name;
      Getmakesql(sql, res)
      return;
   }
   //查所有
   Getmakesql(sql, res)
});

//添加
app.get('/add', function (req, res) {
   initdata();
   if (!req.query.name) {
      setmsg("name null");
      res.status(200);
      res.json(questions);
   }
   else if (!req.query.password) {
      setmsg("password null");
      res.status(200);
      res.json(questions);
   }
   else {
      var sql = 'INSERT INTO user(user_name,user_password) VALUES(' + req.query.name + "," + req.query.password + ')';
      Postmakesql(sql, res)
   }
});

//改
app.get('/modify', function (req, res) {
   initdata();

   if (!req.query.name) {
      setmsg("name null");
      res.status(200);
      res.json(questions);
   }
   else if (!req.query.password) {
      setmsg("password null");
      res.status(200);
      res.json(questions);
   }
   else {
      var name = req.query.name;
      var newpassword = req.query.password;
      var sql = 'UPDATE user  SET user_password=' + req.query.password + ' WHERE user_name=' + req.query.name;
      //行为
      Postmakesql(sql, res);
   }
});
//删除
app.get('/delete', function (req, res) {
   initdata();
   if (!req.query.name) {
      setmsg("name null");
      res.status(200);
      res.json(questions);
   }
   else {
      var name = req.query.name;
      var sql = "DELETE FROM user WHERE user_name=" + req.query.name;
      //行为
      Postmakesql(sql, res);
   }
});


//
//配置服务端口
var server = app.listen(8080, function () {

   var host = server.address().address;

   var port = server.address().port;

   console.log('Example app listening at http://%s:%s', host, port);
})