Node.js + Express + Mysql
1、項目：expressEjsWeb
express -e expressEjsWeb
cd expressEjsWeb&& npm install
2、數(shù)據(jù)庫：mysql
CREATE DATABASE IF NOT EXISTS learn_nodejs CHARACTER SET UTF8;
USE learn_nodejs;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`UserName` varchar(64) NOT NULL COMMENT '用戶名',
`UserPass` varchar(64) NOT NULL COMMENT '用戶密碼',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶信息表';
3.修改package.json文件,安裝session和mysql模塊
"express-session" : "latest",
"mysql" : "latest"
image.png
npm install
image.png
這幾行代碼別忘記添加署尤，之前已經(jīng)添加過肋僧。
image.png
4.樣式和JQuery文件
樣式：樣式主要使用了bootstrap 3.0.3 https://github.com/twbs/bootstrap/releases/tag/v3.0.3
JQuery: jquery-1.6.2.min http://jquery.com/download/
對應(yīng)目錄結(jié)構(gòu)：
image.png
路由配置
1.路由
首頁：/
注冊頁：/reg
登錄頁：/login
安全退出：/logout
2.routes目錄下新建如下文件
reg.js
login.js
logout.js
3.views目錄下新建：
header.ejs
reg.ejs
login.ejs
4.打開app.js文件眨唬，添加代碼
...
var routes = require('./routes/index');
var reg = require('./routes/reg');
var login = require('./routes/login');
var logout = require('./routes/logout');
...
app.use('/', routes);
app.use('/reg', reg);
app.use('/login', login);
app.use('/logout', logout);
...
實現(xiàn)登錄和注冊
image.png
user.js
var mysql = require('mysql');
var DB_NAME = 'learn_nodejs';
var pool = mysql.createPool({
host : '192.168.41.36',
user : 'root',
password : 'admin'
});
pool.on('connection', function(connection) {
connection.query('SET SESSION auto_increment_increment=1');
});
function User(user){
this.username = user.username;
this.userpass = user.userpass;
};
module.exports = User;
pool.getConnection(function(err, connection) {
var useDbSql = "USE " + DB_NAME;
connection.query(useDbSql, function (err) {
if (err) {
console.log("USE Error: " + err.message);
return;
}
console.log('USE succeed');
});
//保存數(shù)據(jù)
User.prototype.save = function save(callback) {
var user = {
username: this.username,
userpass: this.userpass
};
var insertUser_Sql = "INSERT INTO userinfo(id,username,userpass) VALUES(0,?,?)";
connection.query(insertUser_Sql, [user.username, user.userpass], function (err,result) {
//報錯修改地方
connection.release();
if (err) {
console.log("insertUser_Sql Error: " + err.message);
return;
}
//connection.release();
console.log("invoked[save]");
callback(err,result);
});
};
//根據(jù)用戶名得到用戶數(shù)量
User.getUserNumByName = function getUserNumByName(username, callback) {
var getUserNumByName_Sql = "SELECT COUNT(1) AS num FROM userinfo WHERE username = ?";
connection.query(getUserNumByName_Sql, [username], function (err, result) {
//報錯修改地方
connection.release();
if (err) {
console.log("getUserNumByName Error: " + err.message);
return;
}
//connection.release();
console.log("invoked[getUserNumByName]");
callback(err,result);
});
};
//根據(jù)用戶名得到用戶信息
User.getUserByUserName = function getUserNumByName(username, callback) {
var getUserByUserName_Sql = "SELECT * FROM userinfo WHERE username = ?";
connection.query(getUserByUserName_Sql, [username], function (err, result) {
//報錯修改地方
connection.release();
if (err) {
console.log("getUserByUserName Error: " + err.message);
return;
}
//connection.release();
console.log("invoked[getUserByUserName]");
callback(err,result);
});
};
});
注冊
reg.ejs
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><%= title %></title>
<link rel="stylesheet" type="text/css" href="/stylesheets/bootstrap.min.css" />
<link rel="stylesheet" type="text/css" href="/stylesheets/signin.css" />
</head>
<body>
<div id="container" class="container">
<% if (locals.success) { %>
<div id="alt_sucess" class="alert alert-success">
<%- success %>
</div>
<% } %>
<% if (locals.error) { %>
<div id="alt_warning" class="alert alert-warning">
<%= error %>
</div>
<% } %>
<form class="form-signin" role="form" method="post">
<h2 class="form-signin-heading">注冊</h2>
<input id="txtUserName" name="txtUserName" type="text" class="form-control" placeholder="用戶名" required autofocus />
<input id="txtUserPwd" name="txtUserPwd" type="password" class="form-control" placeholder="密碼" required/>
<input id="txtUserRePwd" name="txtUserRePwd" type="password" class="form-control" placeholder="重復(fù)密碼" required/>
<button id="btnSub" class="btn btn-lg btn-primary" type="submit">注 冊</button>
<a class="btn btn-link" href="/login" role="button">登 錄</a>
</form>
</div>
</body>
</html>
<script src="/javascripts/jquery-1.6.2.min.js" type="text/javascript"></script>
<script type="text/javascript">
String.prototype.format = function (args) {
var result = this;
if (arguments.length > 0) {
if (arguments.length == 1 && typeof (args) == "object") {
for (var key in args) {
if (args[key] != undefined) {
var reg = new RegExp("({" + key + "})", "g");
result = result.replace(reg, args[key]);
}
}
}
else {
for (var i = 0; i < arguments.length; i++) {
if (arguments[i] != undefined) {
var reg = new RegExp("({)" + i + "(})", "g");
result = result.replace(reg, arguments[i]);
}
}
}
}
return result;
}
$(function(){
$('#btnSub').on('click',function(){
var
$txtUserName = $('#txtUserName'),
txtUserNameVal = $.trim($txtUserName.val()),
$txtUserPwd = $('#txtUserPwd'),
txtUserPwdVal = $.trim($txtUserPwd.val()),
$txtUserRePwd = $('#txtUserRePwd'),
txtUserRePwdVal = $.trim($txtUserRePwd.val()),
errorTip = '<div id="errorTip" class="alert alert-warning">{0}</div> ';
$("#errorTip,#alt_sucess,#alt_warning").remove();
if(txtUserNameVal.length == 0)
{
$("#container").prepend(errorTip.format('用戶名不能為空'));
$txtUserName.focus();
return false;
}
if(txtUserPwdVal.length == 0)
{
$("#container").prepend(errorTip.format('密碼不能為空'));
$txtUserPwd.focus();
return false;
}
if(txtUserRePwdVal.length == 0)
{
$("#container").prepend(errorTip.format('重復(fù)密碼不能為空'));
$txtUserRePwd.focus();
return false;
}
if(txtUserPwdVal != txtUserRePwdVal)
{
$("#container").prepend(errorTip.format('兩次密碼不一致'));
$txtUserPwd.focus();
return false;
}
return true;
})
});
</script>
實現(xiàn)注冊功能（reg.js）
var express = require('express'),
router = express.Router(),
User = require('../models/user.js'),
crypto = require('crypto'),
TITLE_REG = '注冊';
router.get('/', function(req, res) {
res.render('reg',{title:TITLE_REG});
});
router.post('/', function(req, res) {
var userName = req.body['txtUserName'],
userPwd = req.body['txtUserPwd'],
userRePwd = req.body['txtUserRePwd'],
md5 = crypto.createHash('md5');
userPwd = md5.update(userPwd).digest('hex');
var newUser = new User({
username: userName,
userpass: userPwd
});
//檢查用戶名是否已經(jīng)存在
User.getUserNumByName(newUser.username, function (err, results) {
if (results != null && results[0]['num'] > 0) {
err = '用戶名已存在';
}
if (err) {
res.locals.error = err;
res.render('reg', { title: TITLE_REG });
return;
}
newUser.save(function (err,result) {
if (err) {
res.locals.error = err;
res.render('reg', { title: TITLE_REG });
return;
}
if(result.insertId > 0)
{
res.locals.success = '注冊成功,請點擊 <a class="btn btn-link" href="/login" role="button"> 登錄 </a>' ;
}
else
{
res.locals.error = err;
}
res.render('reg', { title: TITLE_REG });
});
});
});
module.exports = router;
image.png
運行：
image.png
image.png
報錯：
image.png
修改代碼：
image.png
......
//保存數(shù)據(jù)
User.prototype.save = function save(callback) {
var user = {
username: this.username,
userpass: this.userpass
};
var insertUser_Sql = "INSERT INTO userinfo(id,username,userpass) VALUES(0,?,?)";
connection.query(insertUser_Sql, [user.username, user.userpass], function (err,result) {
if (err) {
console.log("insertUser_Sql Error: " + err.message);
return;
}
//connection.release();
console.log("invoked[save]");
callback(err,result);
//報錯修改地方
connection.release();
});
};
......
顯示：
image.png
image.png
到這里绷落，注冊功能完成！
登錄后首頁
header.ejs
<div class="navbar navbar-default navbar-static-top" role="navigation">
<div class="container">
<div class="navbar-header">
<a class="navbar-brand" href="/">Tiany - Nodejs + Express + Ejs + MySQL 示例</a>
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav navbar-right">
<% if (locals.username) { %>
<li><a href="#">
<%= username %>
</a></li>
<li><a href="/logout">退出</a></li>
<% } %>
</ul>
</div>
</div>
</div>
index.ejs
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><%= title %></title>
<link rel="stylesheet" type="text/css" href="/stylesheets/bootstrap.min.css" />
<link rel="stylesheet" type="text/css" href="/stylesheets/navbar-static-top.css" />
</head>
<body>
<% include header %>
</body>
</html>
index.js
var express = require('express'),
router = express.Router();
router.get('/', function(req, res) {
if(req.cookies.islogin)
{
console.log('cookies:' + req.cookies.islogin);
req.session.username = req.cookies.islogin;
}
if(req.session.username)
{
console.log('session:' + req.session.username);
res.locals.username = req.session.username;
}
else
{
res.redirect('/login');
return;
}
res.render('index',{title:'主頁'});
});
module.exports = router;
登錄
login.ejs
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><%= title %></title>
<link rel="stylesheet" type="text/css" href="/stylesheets/bootstrap.min.css" />
<link rel="stylesheet" type="text/css" href="/stylesheets/signin.css" />
</head>
<body>
<div id="container" class="container">
<% if (locals.success) { %>
<div id="alt_sucess" class="alert alert-success">
<%- success %>
</div>
<% } %>
<% if (locals.error) { %>
<div id="alt_warning" class="alert alert-warning">
<%= error %>
</div>
<% } %>
<form class="form-signin" role="form" method="post">
<h2 class="form-signin-heading">登錄</h2>
<input id="txtUserName" name="txtUserName" type="text" class="form-control" placeholder="用戶名" required autofocus />
<input id="txtUserPwd" name="txtUserPwd" type="password" class="form-control" placeholder="密碼" required/>
<label class="checkbox">
<input name="chbRem" id="chbRem" type="checkbox" value="remember-me"> 記住密碼
</label>
<button id="btnSub" class="btn btn-lg btn-primary" type="submit">登 錄</button>
<a class="btn btn-link" href="/reg" role="button">注 冊</a>
</form>
</div>
</body>
</html>
<script src="/javascripts/jquery-1.6.2.min.js" type="text/javascript"></script>
<script type="text/javascript">
String.prototype.format = function (args) {
var result = this;
if (arguments.length > 0) {
if (arguments.length == 1 && typeof (args) == "object") {
for (var key in args) {
if (args[key] != undefined) {
var reg = new RegExp("({" + key + "})", "g");
result = result.replace(reg, args[key]);
}
}
}
else {
for (var i = 0; i < arguments.length; i++) {
if (arguments[i] != undefined) {
var reg = new RegExp("({)" + i + "(})", "g");
result = result.replace(reg, arguments[i]);
}
}
}
}
return result;
}
$(function(){
$('#btnSub').on('click',function(){
var
$txtUserName = $('#txtUserName'),
txtUserNameVal = $.trim($txtUserName.val()),
$txtUserPwd = $('#txtUserPwd'),
txtUserPwdVal = $.trim($txtUserPwd.val()),
errorTip = '<div id="errorTip" class="alert alert-warning">{0}</div> ';
$("#errorTip,#alt_warning").remove();
if(txtUserNameVal.length == 0)
{
$("#container").prepend(errorTip.format('用戶名不能為空'));
$txtUserName.focus();
return false;
}
if(txtUserPwdVal.length == 0)
{
$("#container").prepend(errorTip.format('密碼不能為空'));
$txtUserPwd.focus();
return false;
}
return true;
})
});
</script>
login.js
var express = require('express'),
router = express.Router(),
User = require('../models/user.js'),
crypto = require('crypto'),
TITLE_LOGIN = '登錄';
router.get('/', function(req, res) {
res.render('login',{title:TITLE_LOGIN});
});
router.post('/', function(req, res) {
var userName = req.body['txtUserName'],
userPwd = req.body['txtUserPwd'],
isRem = req.body['chbRem'],
md5 = crypto.createHash('md5');
User.getUserByUserName(userName, function (err, results) {
if(results == '')
{
res.locals.error = '用戶不存在';
res.render('login',{title:TITLE_LOGIN});
return;
}
userPwd = md5.update(userPwd).digest('hex');
if(results[0].UserName != userName || results[0].UserPass != userPwd)
{
res.locals.error = '用戶名或密碼有誤';
res.render('login',{title:TITLE_LOGIN});
console.log(1);
return;
}
else
{
if(isRem)
{
res.cookie('islogin', userName, { maxAge: 60000 });
}
res.locals.username = userName;
req.session.username = res.locals.username;
console.log(req.session.username);
res.redirect('/');
return;
}
});
});
module.exports = router;
image.png
image.png
運行：
image.png
登錄成功后称杨，
image.png
對于連接經(jīng)常中斷問題
參考網(wǎng)上說明修改：
http://www.2cto.com/kf/201404/292016.html
有待實踐肌毅。
