npm install express -g

npm install express-generator -g

express projectName

// conf/db.js
// MySQL数据库联接配置
module.exports = {
 mysql: {
  host: '127.0.0.1', 
  user: 'root',
  password: '',
  database:'test', // 前面建的user表位于这个数据库中
  port: 3306
 }
};

// dao/userSqlMapping.js
// CRUD SQL语句
var user = {
 insert:'INSERT INTO user(id, name, age) VALUES(0,?,?)',
 update:'update user set name=?, age=? where id=?',
 delete: 'delete from user where id=?',
 queryById: 'select * from user where id=?',
 queryAll: 'select * from user'
};

module.exports = user;

// 增加用户
router.get('/addUser', function(req, res, next) {
 userDao.add(req, res, next);
});

// dao/userDao.js
// 实现与MySQL交互
var mysql = require('mysql');
var $conf = require('../conf/conf');
var $util = require('../util/util');
var $sql = require('./userSqlMapping');

// 使用连接池，提升性能
var pool = mysql.createPool($util.extend({}, $conf.mysql));

// 向前台返回JSON方法的简单封装
var jsonWrite = function (res, ret) {
 if(typeof ret === 'undefined') {
  res.json({
   code:'1',
   msg: '操作失败'
  });
 } else {
  res.json(ret);
 }
};

module.exports = {
 add: function (req, res, next) {
  pool.getConnection(function(err, connection) {
   // 获取前台页面传过来的参数
   var param = req.query || req.params;

   // 建立连接，向表中插入值
   // 'INSERT INTO user(id, name, age) VALUES(0,?,?)',
   connection.query($sql.insert, [param.name, param.age], function(err, result) {
    if(result) {
     result = {
      code: 200,
      msg:'增加成功'
     }; 
    }

    // 以json形式，把操作结果返回给前台页面
    jsonWrite(res, result);

    // 释放连接 
    connection.release();
   });
  });
 }
};

var express = require('express');
var router = express.Router();

var userDao = require('../dao/userDao');

/* GET users listing. */
router.get('/', function(req, res, next) {
 res.send('respond with a resource');
});

// 增加用户
//TODO 同时支持get,post
router.get('/addUser', function(req, res, next) {
 userDao.add(req, res, next);
});

router.get('/queryAll', function(req, res, next) {
 userDao.queryAll(req, res, next);
});

router.get('/query', function(req, res, next) {
 userDao.queryById(req, res, next);
});

router.get('/deleteUser', function(req, res, next) {
 userDao.delete(req, res, next);
});

router.post('/updateUser', function(req, res, next) {
 userDao.update(req, res, next);
});

module.exports = router;

// dao/userDao.js
// 实现与MySQL交互
var mysql = require('mysql');
var $conf = require('../conf/db');
var $util = require('../util/util');
var $sql = require('./userSqlMapping');

// 使用连接池，提升性能
var pool = mysql.createPool($util.extend({}, $conf.mysql));

// 向前台返回JSON方法的简单封装
var jsonWrite = function (res, ret) {
 if(typeof ret === 'undefined') {
  res.json({
   code:'1',
   msg: '操作失败'
  });
 } else {
  res.json(ret);
 }
};

module.exports = {
 add: function (req, res, next) {
  pool.getConnection(function(err, connection) {
   // 获取前台页面传过来的参数
   var param = req.query || req.params;

   // 建立连接，向表中插入值
   // 'INSERT INTO user(id, name, age) VALUES(0,?,?)',
   connection.query($sql.insert, [param.name, param.age], function(err, result) {
    if(result) {
     result = {
      code: 200,
      msg:'增加成功'
     }; 
    }

    // 以json形式，把操作结果返回给前台页面
    jsonWrite(res, result);

    // 释放连接 
    connection.release();
   });
  });
 },
 delete: function (req, res, next) {
  // delete by Id
  pool.getConnection(function(err, connection) {
   var id = +req.query.id;
   connection.query($sql.delete, id, function(err, result) {
    if(result.affectedRows > 0) {
     result = {
      code: 200,
      msg:'删除成功'
     };
    } else {
     result = void 0;
    }
    jsonWrite(res, result);
    connection.release();
   });
  });
 },
 update: function (req, res, next) {
  // update by id
  // 为了简单，要求同时传name和age两个参数
  var param = req.body;
  if(param.name == null || param.age == null || param.id == null) {
   jsonWrite(res, undefined);
   return;
  }

  pool.getConnection(function(err, connection) {
   connection.query($sql.update, [param.name, param.age, +param.id], function(err, result) {
    // 使用页面进行跳转提示
    if(result.affectedRows > 0) {
     res.render('suc', {
      result: result
     }); // 第二个参数可以直接在jade中使用
    } else {
     res.render('fail', {
      result: result
     });
    }

    connection.release();
   });
  });

 },
 queryById: function (req, res, next) {
  var id = +req.query.id; // 为了拼凑正确的sql语句，这里要转下整数
  pool.getConnection(function(err, connection) {
   connection.query($sql.queryById, id, function(err, result) {
    jsonWrite(res, result);
    connection.release();

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

};

extends layout
block content
 h1 更新用户资料
 form(method='post', action='/p/users/updateUser')
  div.form-row
   label
    span ID:
    input(type='text',name='id')
  div.form-row
   label
    span name:
    input(type='text',name='name')
  div.form-row
   label
    span age:
    input(type='text',name='age')
  div.form-row
   input(type='submit')

block content
 h1 操作成功！
 pre #{JSON.stringify(result)}

block content
 h1 操作失败！
 pre #{JSON.stringify(result)}