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
8.views/userAdd.ejs
9.views/userEdit.ejs
8.執行指令
npm start
使用瀏覽器查看 : http://localhost:3000/
葛麗絲的分享世界 