mysql+Express开发小记：完成用户注册和登录

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