nodejs使用mysql例子

timetrack_server.js

var http = require('http');var work = require('./lib/timetrack');var mysql = require('mysql');var db = mysql.createConnection({host: '127.0.0.1',user: 'root',password: 'root',databse: 'timetrack'});var server = http.createServer(function(req, res) {switch(req.method) {case 'POST':switch(req.url) {case '/':work.add(db, req, res);break;case '/archive':work.archive(db, req, res);break;case '/delete':work.delete(db, req, res);break;}break;case 'GET':switch(req.url) {case '/':work.show(db, res);break;case '/archived':work.showArchived(db, res);break;}break;}});db.query("CREATE TABLE IF NOT EXISTS work ("+ "id INT(10) NOT NULL AUTO_INCREMENT, "+ "hours DECIMAL(5, 2) DEFAULT 0, "+ "date DATE, "+ "archived INT(1) DEFAULT 0, "+ "description LONGTEXT, "+ "PRIMARY KEY(id))",function(err) {if (err) throw err;console.log('Server started...');server.listen(3000, '127.0.0.1');});

timetrack.js

var qs = require('querystring');// 发送HTML响应exports.sendHtml = function(res, html) {res.setHeader('Content-Type', 'text/html');res.setHeader('Content-Length', Buffer.byteLength(html));res.end(html);};// 解析POST数据exports.parseReceivedData = function(req, cb) {var body = '';req.setEncoding('utf8');req.on('data', function(chunk) {body += chunk; });req.on('end', function() {var data = qs.parse(body);cb(data);});};// 渲染简单的表单exports.actionForm = function(id, path, label) {var html = '<form method="POST" action="' + path + '">' +'<input type="hidden" name="id" value="' + id + '">' +'<input type="submit" value="' + label + '">' +'</form>';return html;};// 添加工作记录exports.add = function(db, req, res) {// 解析POST数据exports.parseReceivedData(req, function(work) {// 添加工作记录db.query("INSERT INTO work (hours, date, description) " +" VALUES (?, ?, ?)",[work.hours, work.date, work.description],function(err) {if (err) throw err;exports.show(db, res);});});};// 删除工作记录exports.delete = function(db, req, res) {exports.parseReceivedData(req, function(work) {db.query("DELETE FROM work WHERE id=?",[work.id],function(err) {if (err) throw err;exports.show(db, res);});});};// 归档一条工作记录exports.archive = function(db, req, res) {exports.parseReceivedData(req, function(work) {db.query("UPDATE work SET archived = 1 WHERE id=?",[work.id],function(err) {if (err) throw err;exports.show(db, res);});});};// 获取工作记录exports.show = function(db, res, showArchived) {var query = "SELECT * FROM work " +"WHERE archived = ? " +"ORDER BY date DESC";var archiveValue = (showArchived) ? 1 : 0;db.query(query, [archiveValue], function(err, rows) {if (err) throw err;html = (showArchived) ? '' : '<a href="/archived">Archived Work</a><br/>';html += exports.workHitlistHtml(rows);// 将结果格式化为HTML表格html += exports.workFormHtml();exports.sendHtml(res, html);});};exports.showArchived = function(db, res) {exports.show(db, res, true);};// 将工作记录渲染为HTML表格exports.workHitlistHtml = function(rows) {var html = '<table>';for (var i in rows) {html += '<tr>';html += '<td>' + rows[i].date + '</td>';html += '<td>' + rows[i].hours + '</td>';html += '<td>' + rows[i].description + '</td>';if (!rows[i].archived) {html += '<td>' + exports.workArchiveForm(rows[i].id) + '</td>';}html += '<td>' + exports.workDeleteForm(rows[i].id) + '</td>';html += '</tr>';}html += '</table>';};exports.workFormHtml = function() {var html = '<form method="POST" action="/">' +'<p>Date (YYYY-MM-DD):<br/><input name="date" type="text"><p/>' +'<p>Hours worked:<br/><input name="hours" type="text"><p/>' +'<p>Description:<br/>' +'<textarea name="description"></textarea></p>' +'<input type="submit" value="Add" />' +'</form>';return html;};exports.workArchiveForm = function(id) {return exports.actionForm(id, '/archive', 'Archive');};exports.workDeleteForm = function(id) {return exports.actionForm(id, '/delete', 'Delete');};