NodeJs操作mysql数据库(coffee版)

1、需要下载mysql模块

http = require('http');

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

mysql = require('mysql');

db = mysql.createConnection({

  host: 'localhost',

  user: 'root',

  password: '123456',

  database: 'nodedb'

});

server = http.createServer((req, res) ->

  switch req.method

    when 'POST'

      switch req.url

        when '/'

          work.add(db, req, res);

        when '/archive'

          work.archive(db, req, res);

        when '/delete'

          work.delete(db, req, res);

    when 'GET'

      switch req.url

        when '/'

          work.show(db, res);

        when '/archived'

          work.showArchived(db, res);

)

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

  """,

  (err) ->

    if (err)

      throw err

    console.log('Server started...')

    server.listen(3000, '127.0.0.1')

)

qs = require('querystring')

sendHtml = (res, html) ->

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

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

  res.end(html)

parseReceivedData = (req, cb) ->

  body = ''

  req.setEncoding('utf8')

  req.on('data', (chunk) ->

    body += chunk

  )

  req.on('end', ()->

    data = qs.parse(body)

    cb(data)

  )

actionForm = (id, path, label) ->

  html = '<form method="POST" action="' + path + '">' +

    '<input type="hidden" name="id" value="' + id + '"/>' +

    '<input type="submit" value="' + label + '"/>' +

    '</form>';

  return html

add = (db, req, res) ->

  parseReceivedData(req, (work) ->

      db.query(

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

          "values(?,?,?)",

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

        (err) ->

          if (err)

            throw err

          show(db, res)

      )

  )

exports.delete = (db, req, res) ->

  parseReceivedData(req, (work) ->

    db.query(

      "delete from work where id=?",

      [work.id],

      (err) ->

      if (err)

        throw err

      show(db, res)

    )

  )

archive = (db, req, res)->

  parseReceivedData(req, (work) ->

    db.query(

      "update work set archived=1 where id=?",

      [work.id],

      (err)->

        if (err)

          throw err

        show(db, res)

    )

  )

show = (db, res, showArchived)->

  query = "select * from work where archived=? order by date desc ";

  archiveValue = showArchived ? 1: 0;

  db.query(

    query,

    [archiveValue],

    (err, rows) ->

      if (err)

        throw err

      html = showArchived ? '': '<a href ="/archived">Archived work</a><br/>';

      html += exports.workHitlistHtml(rows)

      html += exports.workFormHtml()

  )

showArchived = (db, res)->

    show(db, res, true);

workHitlistHtml = (rows) ->

    html = '<table>';

    console.log(rows)

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

    return html;

workFormHtml = () ->

  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;

workArchiveForm = (id) ->

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

workDeleteForm = (id) ->

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

转载来源：http://www.16boke.com/article/detail/121