node.js---study2 连接池连接数据库实现登录注册查询用户的功能---

最新推荐文章于 2023-12-21 14:55:23 发布

Couragefff

最新推荐文章于 2023-12-21 14:55:23 发布

阅读量985

点赞数 1

分类专栏： web前端学习文章标签： node.js mysql 数据库

本文链接：https://blog.csdn.net/StubbornAccepted/article/details/75370863

版权

web前端学习专栏收录该内容

25 篇文章 0 订阅

订阅专栏

目录结构

整体的目录结构是酱紫的
这里写图片描述

连接数据库

安装“mysql”包

$ npm install mysql

routes

index.js

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

/* GET home page. */
var welcome=require('../controllers/welcome');
router.get('/', welcome.index);
router.get('/regist',welcome.register);
router.get('/login',welcome.login)
module.exports = router;

users.js

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

/* GET users listing. */
var user=require("../controllers/user");
router.get('/', function(req, res) {
  res.send('respond with a resource');
});

router.post('/regist',user.regist);
router.post('/login',user.login);
router.get('/query',user.query);
module.exports = router;

controllers

welcome.js

exports.index=function(req, res) {
    res.render('index', { title: 'Express' });
}
exports.register=function(req,res){
    res.render('register');
}
exports.login=function(req,res){
    res.render('login');
}

user.js


var userModel=require('../models/userModel');
exports.regist=function(req,res){
    var name=req.body.username;
    var pwd=req.body.password;
    var sex=req.body.sex;
    userModel.save(name,pwd,sex,function (result) {
        if(result){
            res.render('show',{username:req.body.username,
                password:req.body.password,
                sex:req.body.sex,
                msg:'注册成功!'
            })
        }else{
            res.render('show',{msg:'注册失败'});
        }
    });


}
exports.login=function (req,res) {
    var name=req.body.username;
    var pwd=req.body.password;
    userModel.getByNameAndPwd(name,pwd,function (results) {
        if(results.length>0){
            res.render('show',{msg:'登录成功'});
        }else{
            res.render('show',{msg:'登录失败'});
        }
    })
}
exports.query=function(req,res){
    userModel.getAll(function (results) {
        if(results.length>0){
            res.render('showAll',{userList:results});
        }
    })
}

models

db.js

var mysql      = require('mysql');
var pool = mysql.createPool({
    host     : 'localhost',
    user     : 'root',
    password : '123321',
    database : 'bz'
});
exports.query = function (sql, data, callback) {
    pool.getConnection(function (err, conn) {
        if (err) {
            //callback(err, null, null);
        } else {
            if(typeof data == 'function'){
                callback = data;
                data = null;
            }
            conn.query(sql, data, function (qerr, result) {
                //释放连接
                conn.release();
                //事件驱动回调
                //console.log(result);
                callback(result);
            });
        }
    });
};

userModel.js

var db=require('./db');
exports.save=function (name,pwd,sex,callback) {
    var sql="insert into user0(username, password, sex) values('"+name+"', '"+pwd+"', '"+sex+"')";
    db.query(sql,null,callback);
}
exports.getByNameAndPwd=function (name,pwd,callback) {
    var sql="select * from user0 where username='"+name+"' and password='"+pwd+"'";
    db.query(sql,callback);
}
exports.getAll=function (callback) {
    var sql="select * from user0";
    db.query(sql,callback);
}

views

index.ejs

<!DOCTYPE html>
<html>
  <head>
    <title><%= title %></title>
    <link rel='stylesheet' href='/stylesheets/style.css' />
  </head>
  <body>
    <h1><%= title %></h1>
    <p>Welcome to <%= title %></p>
    <a href="/regist">点击注册</a> <br/>
    <a href="/login">点击登录</a> <br/>
    <a href="/users/query">点击查询所有用户</a>
  </body>
</html>

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Document</title>
</head>
<body>
<form action="/users/login" id="reg-form" method="post">
    <p>
        <label for="username">用户名：</label>
        <input type="text" id="username" name="username" />
    </p>
    <p>
        <label for="password">密码：</label>
        <input type="text" id="password" name="password"/>
    </p>
    <input type="submit" name="submit" value="登录"/>
</form>
</body>
</html>

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Document</title>
    <link rel="stylesheet" href="/stylesheets/index.css"/>
</head>
<body>
<form action="/users/regist" id="reg-form" method="post">
    <p>
    <label for="username">用户名：</label>
    <input type="text" id="username" name="username" class="error"/>
    </p>
    <p>
    <label for="password">密码：</label>
    <input type="text" id="password" name="password"/>
    </p>
    <p>
        <label for="">性别：</label>
        男<input type="radio" value="男" name="sex" checked="checked"/> &nbsp;&nbsp;
        女<input type="radio" value="女" name="sex"/>
    </p>
    <input type="submit" name="submit" value="注册"/>
</form>
<script src="/javascripts/jquery-1.11.0.min.js"></script>
<script type="text/javascript">
    $(function () {
        $('#username').on('blur',function(){
            if($.trim(this.value)==""){
                $(this).addClass('error');
            }
        }).on('focus',function(){
            $(this).removeClass('error');
        })

        $('#reg-form').on('submit',function () {
            if($(':checked').length==0){
                alert('请输入性别');
                return false;
            }
            return true;
        })
    });
</script>
</body>

</html>

show.ejs

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Document</title>
</head>
<body>

<%=msg%>
</body>
</html>

showAll.ejs

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Document</title>
</head>
<body>
<table>
    <tr>
        <td>用户编号</td>
        <td>用户名</td>
        <td>性别</td>
    </tr>
    <%
      for(var i=0;i<userList.length;i++){
    %>
    <tr>
        <td><%=userList[i].uid%></td>
        <td><%=userList[i].username%></td>
        <td><%=userList[i].sex%></td>
    </tr>
    <%
    }
    %>
</table>
</body>
</html>