JQuery AJAX - NODEJS - Mysql 总结

【摘要】通过JQuery AJAX 对于服务器进行动态请求和页面刷新，nodejs搭建HTTP服务，中间件 路由跳转，后台和mysql交互，mysql存储着数据。

首先我们需要搭建一个http服务，使用简单的express建站

npm install -g express
npm install express-generator -g
//新建一个目录
mkdir xxx //xxx只是路径名
cd xxx
express app//app名字自己定
cd app
npm install

至此 一个基本的nodejs网站及服务器就搭建好了

npm install mysql -s //连接mysql和nodejs服务器
mkdir mysql //建立一个mysql的文件夹
cd mysql //

conn.js
module.exports = {
    mysql: {
        host: 'localhost',
        user: 'root',
        password: '940809',
        database:'quantaxis', 
        port: 3306
    }
};

sqlexec.js
var mysql = require('mysql');
var $conf = require('./conn');
var $sql = require('./sqlmapping');
var express = require('express');
var router = express.Router();
// 使用连接池，提升性能
var pool = mysql.createPool($conf.mysql);


var jsonWrite = function (res, ret) {
    if (typeof ret === 'undefined') {
        res.json({
            code: '1',
            msg: '操作失败'
        });
    } else {
        res.json(ret);
    }
};


module.exports = {
    queryByName: function (req, res, next) {
        pool.getConnection(function (err, connection) {
            var name = +req.query.name; // 为了拼凑正确的sql语句，这里要转下整数

            var sqlq = mysql.format($sql.ts.queryts, name);
            console.log('name' + name)
            //connection.config.queryFormat = sqlsplit.yutiansutsqlsplit (sqlq, values) ;

            connection.config.queryFormat = function (query, values) {
                if (!values) return query;
                return query.replace(/\:(\w+)/g, function (txt, key) {
                    if (values.hasOwnProperty(key)) {
                        return this.escape(values[key]);
                    }
                    return txt;

                }.bind(this));
            };
            console.log(sqlq)
            console.log(name)
            pool.getConnection(function (err, connection) {
                console.log(sqlq)
                connection.query(sqlq, function (err, result) {
                    jsonWrite(res, result);
                    connection.release();

                });
            });
        });
    },
    querytable:function (req, res, next) {
        pool.getConnection(function (err, connection) {
            if (err) throw err;
            connection.query($sql.ts.querytable, function (err, result) {
                if (err) throw err;
                jsonWrite(res, result);
                connection.release();
            });
        });
    }



};

var mysql =require('mysql')
module.exports = {

    user: {
        insert: "INSERT INTO user(name, password) " + " VALUES(?,?)",
        update: 'update user set name=?, password=? where id=?',
        updatename: 'update users set name=?, age=? where id=?',
        delete: 'delete from users where id=?',
        queryAll: 'SELECT * from user',
        login: 'SELECT * from user where name=? and password=?'
    },

    ts: {
        insert: 'INSERT INTO user(video) VALUES(?,?) where name=?',
        update: 'update user set video=? where name=?',
        queryts: 'SELECT * from ?',
        querytable: 'SELECT * from userlist'
    },

    profits: {
        queryts: 'SELECT * from ?'
    }


};

test.jade
html
  head
    script(type='text/javascript', src='http://libs.baidu.com/jquery/2.1.4/jquery.min.js')
  body
    #myDiv
      h2 ajax
    button#b01(type='button') testajax
    script.
      function changeToButton(id){
      var btn = document.getElementById(id);
      btn.onclick= function(){
      $.ajax({
      url: "/ajax",    //请求的url地址
      dataType: "json",   //返回格式为json
      async: true, //请求是否异步，默认为异步，这也是ajax重要特性
      //data: { "data": "value" },    //参数值
      type: "GET",   //请求方式
      beforeSend: function() {
      //请求前的处理
      },
      success: function(data,textStatus){

        alert('textStatus---'+textStatus)
        alert(JSON.stringify(data));
        var append=JSON.stringify(data);
        $('#myDiv').prepend(append);
        alert(JSON.parse(append)[0].Userid)
      },
      complete: function() {
      //请求完成的处理



      },
      error: function() {
      //请求出错处理
      }
      });
      }
      }
      window.onload = function() {
      changeToButton("b01");
      }

//写好中间件路由跳转和ajax路径
var express = require('express');
var router = express.Router();
var sqlexec = require('../mysql/sqlexec.js');
router.get('/aaa', function(req, res, next) {
  res.render('../test', { title: 'Exs' });
});

router.get('/ajax',function(req,res,next){
  console.log('Using Ajax methods');

  sqlexec.querytable(req, res, next);
});
module.exports = router;