1.安装
1.1安装mysql对应的驱动，
npm install mysql
1.2安装第三方插件express-connection
npm install express-connection
2.配置数据库，在models下新建DBConfig.js
代码：
module.exports = {
mysql: {
host: '222.31.81.214',
user: 'XXX',
password: 'XXX',
database: 'big_data_service', // 前面建的user表位于这个数据库中
port: 3306,
charset:"utf8"
}
};
3.在userinfo.js中使用数据库
// 导入MySQL模块
var crypto = require('crypto');//使用加密
var mysql = require('mysql');
var dbConfig = require('../models/DBConfig');
4.使用连接池
pool.getConnection(function(err, connection) {
// Use the connection
connection.query( 'SELECT something FROM sometable', function(err, rows) {
// And done with the connection.
connection.release();
// Don't use the connection here, it has been returned to the pool.
});
});
关闭连接池：
pool.end(function (err) {
// all connections in the pool have ended
});
5.使用session
在app.js中配置
var session = require('express-session');
//使用session
//这里传入了一个密钥加session id
//使用靠就这个中间件
app.use(session({ secret: 'wilson'}));
6.userinfo.js 功能块完整代码
var express = require('express');
var router = express.Router();
// 导入MySQL模块
var crypto = require('crypto');
var mysql = require('mysql');
var dbConfig = require('../models/DBConfig');
/* GET users listing. */
//注册
var pool = mysql.createPool(dbConfig.mysql);
router.get('/reg', function(req, res, next) {
res.render('register', {
title: 'rester',
success: req.flash('success').toString() || '',
error: req.flash('error').toString() || '',
username: req.session.username
});
})
router.post('/reg', function(req, res, next) {
//req.body 处理 post 请求
var username = req.body.username,
password = req.body.password,
email = req.body.email,
passwordRepeat = req.body.passwordRepeat;
if (password != passwordRepeat) {
console.log('两次输入的密码不一致!');
req.flash('error', "两次输入的密码不一致");
return res.redirect('/userinfo/reg');
}
//对密码加密
var md5 = crypto.createHash('md5'),
md5password = md5.update(password).digest('hex');
var promise = new Promise(function(resolve, reject) {
pool.getConnection(function(err, connection) {
if (err) {
reject(err);
} else {
connection.query('SELECT * FROM userinfo WHERE username = ?', [username],
function(err, rows, fields) {
if (err) {
reject(err);
} else {
resolve(rows);
}
connection.release();
});
}
});
});
promise.then(function(rows1) {
console.log('查找user执行成功');
pool.getConnection(function(err, connection) {
if (err) {
console.log(err);
} else {
if (rows1.length > 0) {
console.log('rows' + rows1);
console.log('用户名已经存在');
req.flash('error', "用户名已经存在");
return res.redirect('/userinfo/reg');
} else {
connection.query('INSERT INTO userinfo(username,password,email) VALUES(?,?,?)', [username, md5password, email],
function(err, rows2, fields) {
if (err) {
console.log(err);
} else {
req.flash('success', "注册成功！");
delete password;
req.session.username = username;
return res.redirect('/');
}
connection.release();
});
}
}
});
}, function(err) {
console.log(err);
});
});
//登录
router.get('/login', function(req, res, next) {
res.render('login', {
title: 'login',
success: req.flash('success').toString() || '',
error: req.flash('error').toString() || '',
username: req.session.username
});
})
router.post('/login', function(req, res, next) {
var username = req.body.username,
password = req.body.password;
var md5 = crypto.createHash('md5'),
md5password = md5.update(password).digest('hex');
var promise = new Promise(function(resolve, reject) {
pool.getConnection(function(err, connection) {
if (err) {
reject(err);
} else {
connection.query('SELECT * FROM userinfo WHERE username = ? AND password=?', [username, md5password],
function(err, rows, fields) {
if (err) {
reject(err);
} else {
resolve(rows);
}
connection.release();
});
}
});
});
promise.then(function(rows1) {
console.log('查找user执行成功');
pool.getConnection(function(err, connection) {
if (err) {
console.log(err);
} else {
if (rows1.length > 0) {
console.log('rows 登录成功' + rows1);
req.flash('success', "登录成功！");
req.session.username = username;
delete password;
console.log('sessiom' + req.session.username);
return res.redirect('/');
} else {
console.log("用户或密码不正确");
req.flash('error', "用户或密码不正确");
return res.redirect('back');
}
}
});
}, function(err) {
console.log(err);
});
})
//用户退出
router.get('/logout', function(req, res, next) {
req.session.username = null;
req.flash('success', "退出登录成功！");
return res.redirect('/');
})
pool.end(function (err) {
// all connections in the pool have ended
console.log(err);
});
module.exports = router;
参考文档：
1.http://borninsummer.com/2015/04/14/notes-on-nodejs-express-mysql-and-promise/
2.http://www.cnblogs.com/zhongweiv/p/nodejs_express_webapp.html
3.http://www.cnblogs.com/sword-successful/p/5234890.html
4.http://www.jianshu.com/p/0a161f341771
