nodejs MVC框架express使用mysql範例

1.安裝框架express

請參考nodejs MVC網站框架express安裝及demo

2.建立專案

express -e express-mysql

cd express-mysql

npm install -g mysql –verbose

mpm install

3.原始碼:https://github.com/yahuihuang/nodejs/tree/master/express-mysql

4.建立Mysql Table

DROP TABLE IF EXISTS JBContact;

CREATE TABLE JBContact (
  seqNo int(10) NOT NULL AUTO_INCREMENT COMMENT '流水號',
  userId varchar(20) NOT NULL default '' COMMENT '使用者代碼',
  cKind int(1) unsigned NOT NULL default '1' COMMENT '聯絡類型 1=email 2=mobile no',
  contact varchar(50) default '' COMMENT '聯絡內容 ckind=1:填email ckind=2:填mobile no',
  remark varchar(50) default '' COMMENT '備註',
  ModifyTime datetime NOT NULL COMMENT '修改時間:時間格式YYYY-MM-DD HH:MM:SS.SSS',
  ModifyEmp varchar(8) NOT NULL COMMENT '修改人員:員工編號',  
  PRIMARY KEY (seqNo)
) COMMENT='使用者聯絡資訊檔';

5.app.js

var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

var routes = require('./routes/index');
var users = require('./routes/users');

// DataBase 
var mysql = require("mysql");

var con = mysql.createConnection({
    host: 'xxxx',
    user: 'xxxx',
    password: 'xxxx',
    database: 'xxxx',
    insecureAuth: true
});

con.connect(function(err) {
    if (err) {
        console.log('connecting error');
        return;
    }
    console.log('connecting success');
});

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

// DB連線
app.use(function(req, res, next) {
    req.con = con;
    next();
});

app.use('/', routes);
app.use('/users', users);


// 404頁面
app.use(function(req, res, next) {
    var err = new Error('Not Found');
    err.status = 404;
    next(err);
});

// 錯誤處理
if (app.get('env') === 'development') {
    app.use(function(err, req, res, next) {
        res.status(err.status || 500);
        res.render('error', {
            message: err.message,
            error: err
        });
    });
}

app.use(function(err, req, res, next) {
    res.status(err.status || 500);
    res.render('error', {
        message: err.message,
        error: {}
    });
});

module.exports = app;

6.routes/index.js

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

//主頁
router.get('/', function(req, res, next) {
    var db = req.con;
    var data = "";
    var userId = req.query.userId;

    var filter = "";
    if (userId) {
        filter = 'WHERE userId = ?';
    }

    var sql = "SELECT seqNo,userId,cKind,contact,remark,DATE_FORMAT(modifyTime, '%Y-%m-%d %H:%i:%S') as ModifyTime,ModifyEmp " +
              "  FROM JBContact " + filter;
    db.query(sql, userId, function(err, rows) {
        if (err) {
            console.log(err);
        }
        var data = rows;

        res.render('index', { title: '帳號清單', data: data, userId: userId });
    });
});

//新增帳號-1
router.get('/add', function(req, res, next) {

    // use userAdd.ejs
    res.render('userAdd', { title: '新增帳號'});
});

//新增帳號-2
router.post('/userAdd', function(req, res, next) {

    var db = req.con;

    var sql = {
        userId: req.body.userId,
        cKind: req.body.cKind,
        contact: req.body.contact,
        remark: req.body.remark,
        modifyTime: new Date(),
        modifyEmp: 'J1040083'
    };

    //console.log(sql);
    var qur = db.query('INSERT INTO JBContact SET ?', sql, function(err, rows) {
        if (err) {
            console.log(err);
        }
        res.setHeader('Content-Type', 'application/json');
        res.redirect('/');
    });

});

//修改帳號-1 : 查詢明細
router.get('/userEdit', function(req, res, next) {
    var seqNo = req.query.seqNo;
    var db = req.con;
    var data = "";

    db.query('SELECT * FROM JBContact WHERE seqNo = ?', seqNo, function(err, rows) {
        if (err) {
            console.log(err);
        }

        var data = rows;
        res.render('userEdit', { title: '帳號編輯', data: data });
    });

});

//修改帳號-2
router.post('/userEdit', function(req, res, next) {
    var db = req.con;
    var seqNo = req.body.seqNo;

    var sql = {
        userId: req.body.userId,
        cKind: req.body.cKind,
        contact: req.body.contact,
        remark: req.body.remark,
        modifyTime: new Date(),
        modifyEmp: 'J1040083'
    };

    var qur = db.query('UPDATE JBContact SET ? WHERE seqNo = ?', [sql, seqNo], function(err, rows) {
        if (err) {
            console.log(err);
        }

        res.setHeader('Content-Type', 'application/json');
        res.redirect('/');
    });

});

//刪除帳號
router.get('/userDelete', function(req, res, next) {
    var seqNo = req.query.seqNo;
    var db = req.con;

    var qur = db.query('DELETE FROM JBContact WHERE seqNo = ?', seqNo, function(err, rows) {
        if (err) {
            console.log(err);
        }
        res.redirect('/');
    });
});

module.exports = router;

7.views/index.ejs