16boke - 一路博客

NodeJs操作mysql数据库(js版)

1 、需要npm install mysql

实现一个简单的数据库系统,包含增删查的功能

/**

 * Created by wangyc on 2015-3-18.

 * timetrack_server.js

 */

var http = require('http');

var work = require('./lib/timetrack');

var mysql = require('mysql');

var db = mysql.createConnection({

    host: 'localhost',

    user: 'root',

    password: '123456',

    database: 'nodedb'

});

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');

    }

);


';

        html += '

';

        html += '

';

        html += '

';

        if (!rows[i].archived) {

            html += '

';

        }

        html += '

';

        html += '

';

    }

    html += '

/**

 * Created by wangyc on 2015-3-18.

 * timetrack.js

 */

var qs = require('querystring');

exports.sendHtml = function (res, html) {

    res.setHeader('Content-Type', 'text/html');

    res.setHeader('Content-Length', Buffer.byteLength(html));

    res.end(html);

};

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 = '

' +

        '' +

        '' +

        '';

    return html;

};

exports.add = function (db, req, res) {

    exports.parseReceivedData(req, function (work) {

        db.query(

                "insert into work(hours,date,description) " +

                "values(?,?,?)",

            [work.hours, work.date, work.description],

            function (err) {

                console.log(err);

                console.log(res);

                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 ? ''

                : 'Archived work
';

            html += exports.workHitlistHtml(rows);//将结果格式化为表格

            html += exports.workFormHtml();

            exports.sendHtml(res, html);//给用户发送HTML响应

        }

    );

};

exports.showArchived = function (db, res) {

    exports.show(db, res, true);

};

exports.workHitlistHtml = function (rows) {

    var html = '';

    console.log(rows)

    for (var i in rows) {

        html += '

' + rows[i].date + '' + rows[i].hours + '' + rows[i].description + '' + exports.workArchiveForm(rows[i].id) + '' + exports.workDeleteForm(rows[i].id) + '

';

    return html;

};

exports.workFormHtml = function () {

    var html = '<form method="POST" action="/"' +

        '

Date(YYYY-MM-DD):

' +

        '

Hours worked:

' +

        '

Description:
' +

        '

' +

        '' +

        '';

    return html;

};

exports.workArchiveForm = function (id) {

    return exports.actionForm(id, '/archive', 'Archive');

};

exports.workDeleteForm = function (id) {

    return exports.actionForm(id, '/delete', 'Delete');

};