初識(shí)NodeJS服務(wù)端開發(fā)入門（Express+MySQL）

npm install express -g

npm install express-generator -g

express projectName

// conf/db.js
// MySQL數(shù)據(jù)庫聯(lián)接配置
module.exports = {
	mysql: {
		host: '127.0.0.1', 
		user: 'root',
		password: '',
		database:'test', // 前面建的user表位于這個(gè)數(shù)據(jù)庫中
		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
// 實(shí)現(xiàn)與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));

// 向前臺(tái)返回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) {
			// 獲取前臺(tái)頁面?zhèn)鬟^來的參數(shù)
			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形式，把操作結(jié)果返回給前臺(tái)頁面
				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 同時(shí)支持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
// 實(shí)現(xiàn)與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));

// 向前臺(tái)返回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) {
			// 獲取前臺(tái)頁面?zhèn)鬟^來的參數(shù)
			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形式，把操作結(jié)果返回給前臺(tái)頁面
				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
		// 為了簡單，要求同時(shí)傳name和age兩個(gè)參數(shù)
		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) {
				// 使用頁面進(jìn)行跳轉(zhuǎn)提示
				if(result.affectedRows > 0) {
					res.render('suc', {
						result: result
					}); // 第二個(gè)參數(shù)可以直接在jade中使用
				} else {
					res.render('fail', {
						result: result
					});
				}

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

	},
	queryById: function (req, res, next) {
		var id = +req.query.id; // 為了拼湊正確的sql語句，這里要轉(zhuǎn)下整數(shù)
		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
	h2 更新用戶資料
	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
 h2 操作成功！
 pre #{JSON.stringify(result)}

block content
 h2 操作失?。? pre #{JSON.stringify(result)}