[Node.js] Node.js에서 mysql을 이용하여 웹서비스 만들기 #2

 

2021.07.05 - [스마트한 코딩 공부] - [Node.js] Node.js에서 mysql을 이용하여 웹서비스 만들기 #1

[Node.js] Node.js에서 mysql을 이용하여 웹서비스 만들기 #1

저번시간에는 mysql을 설치했는데 이번에는 node.js과 연동하여 사용해보자.

2021.07.02 - [스마트한 코딩 공부] - [Node.js] Node.js 로 간단한 웹서비스 만들기 #2

[Node.js] Node.js 로 간단한 웹서비스 만들기 #2

위에 글에서 쓰던 코드를 그대로 가져와서 사용할 예정이니 여기서 설명하지 않는 코드나 내용들은 위에 글을 참고하시길 바랍니다. 
차량관리 서비스를 만들어보려고 한다.

웹서비스 Flow Chart

저번과 다른점은 routes에 관리자를 추가해서 routes를 2개로 사용하는 것이다.

1. 폴더 및 파일 구성

2. index.js 파일 구성

var express = require("express");
var path = require("path");
var app = express();
var port = 3000;

app.set("views", path.join( __dirname , "views") );
app.set("view engine",'ejs');

app.use(express.json());
app.use(express.urlencoded({ extended : false}));
app.use(express.static(path.join(__dirname, 'public')));

var main = require('./routes/main');
app.use('/', main);

var manager = require('./routes/manager');
app.use('/manager', manager);

var server = app.listen(port,function(){
    console.log("Express Run")
})

routes가 main 1개에서 manager가 추가된 것이외에는 코드의 변화는 없다.

3. routes 폴더 안에 main.js 와 manager.js 파일 구성

//main.js
var express = require('express');
var router = express.Router();
var mysql = require('mysql2');
var secretKey ="dfgfdgfdgfdgfd";
var Crypto = require("crypto")

//처음에 MySQL Workbench를 만들때 적었던 정보를 적으면 된다.
var connection = mysql.createConnection({
    host     : 'localhost', //127.0.0.1
    port     : 3306,
    user     : 'root',
    password : 'password',
    database : 'test'
  });

router.get('/',function(req, res){
    res.render('login');
})

router.get('/signup',function(req, res){
    res.render('signup');
})

router.post('/login',function(req, res){
    var post_id = req.body.post_id;
    var password = req.body.password;
    var crypto = Crypto.createHmac('sha256', secretKey).update(password).digest('hex');
    console.log(crypto);
    connection.query(
        `select * from user_list where post_id = ? and password = ?`,
        [post_id, crypto],
        function(err,result){
            if(err){
                console.log(err);
                res.send("SQL login connection Error");
            }else{
                if(result.length> 0){
                    if(result[0].linkcode == 1){
                        res.redirect("/manager");
                    }else{
                        if(result[0].linkcode == 0){
                        res.send("User");
                        }
                    }
                }else{
                    res.send("Not data");
                }
            }
        }
    )

})

router.post('/signup_1',function(req, res){
    var post_id =req.body.post_id;
    var name =req.body.name;
    var password =req.body.password;
    var crypto = Crypto.createHmac('sha256', secretKey).update(password).digest('hex');
    var division =req.body.division;
    var linkcode =req.body.linkcode;
    console.log(post_id, name, crypto, division, linkcode);
    connection.query(
        `select * from user_list where post_id =?`,
        [post_id],
        function(err, result){
            if(err){
                console.log(err);
                res.send("SQL connect Error");
            }else{
                if(result.length > 0){
                    res.send("이미 존재하는 아이디");
                }else{
                    connection.query(
                        `insert into user_list (post_id, password, name,
                            division, linkcode) values (?, ?, ?, ?, ?)`,
                            [post_id, crypto, name, division, linkcode],
                            function(err2, result2){
                                if(err2){
                                    console.log(err2);
                                    res.send("SQL insert Error");
                                }else{
                                    res.redirect("/");
                                }
                            } 
                    )
                }
                }

            }
    )
})


module.exports=router;

//manager.js
var express = require('express');
var router = express.Router();
var mysql = require('mysql2');

var connection = mysql.createConnection({
    host     : 'localhost', //127.0.0.1
    port     : 3306,
    user     : 'root',
    password : 'password',
    database : 'test'
  });

router.get("/add_car", function(req, res, next){
    res.render("add_car");
})

router.get("/", function(req, res, next){
    res.render("manager");
})

router.post('/add_car2',function(req, res){
    var car_id =req.body.car_id;
    var car_div =req.body.car_div;
    var car_type =req.body.car_type;
    var car_birth =req.body.car_birth;
    console.log(car_id, car_div, car_type, car_birth);
    connection.query(
        `insert into car_list (car_id, car_div, car_type,
              car_birth) values (?, ?, ?, ?)`,
            [car_id, car_div, car_type, car_birth])
})

router.get("/car_list", function(req, res){
    connection.query(
        `select * from car_list`,
        function(err,result){
            console.log(result);
            if(err){
                console.log(err);
                res.send("car_list connection Error");
            }else{
                res.render("car_list", {car : result});
            }
        }
    )
})

router.get("/search", function(req, res){
    var car_id = req.query.car_id;
    console.log(car_id);
    connection.query(
        `select * from car_list where car_id = ?`,
        [car_id],
        function(err,result){
            if(err){
                console.log(err);
                res.send("car_list connection Error");
            }else{
                res.render("search", {car : result});
            }
        }
    )
})

module.exports=router;

4. views 파일 구성

//login.ejs
<!DOCTYPE html>
<html>
    <head>
        <script>
            function signup(){
                location.href ="/signup";
            }; 
        </script>
        <title>Login Page</title>
    </head>
    <body>
        <form action ="/login" method ='POST'>
            <input type = 'text' placeholder="ID" name ="post_id">
            <input type = 'text' placeholder="PASSWORD" name ="password">
            <input type = 'submit' name ="action" value="login"> 
            <button type = "button" onclick="signup()">signup</button>
        </form>
            
    </body>
</html>

//signup.ejs
<!DOCTYPE html>
<html>
    <head>
        <script>
            function login(){
                location.href ="/login";
            }; 
        </script>
        <title>Sign Page</title>
    </head>
    <body>
        <form action="/signup_1" method = "post">
            <input type ="text" name ="post_id" placeholder="ID">
            <input type ="text" name ="name" placeholder="NAME">
            <input type ="text" name ="password" placeholder="PASSWORD">
            <input type ="text" name ="division" placeholder="DIVISION">
            <input type ="number" name ="linkcode" placeholder="LINKCODE">
            <input type ="submit" value='signup'>
            <button type = "button" onclick = "login()">login</button>
        </form>
    </body>

</html>

 

//manager.ejs
<!DOCTYPE html>
<html>
    <head>
        <script>
            function add_car(){
                location.href="/manager/add_car"
            }
            function car_list(){
                location.href="/manager/car_list"
            }
        </script>
        <body>
            <button onclick="add_car()">차량등록</button>
            <button onclick="car_list()">차량 리스트</button>
        </body>
    </head>
</html>

/add_car.ejs
<!DOCTYPE html>
<html>
    <body>
        <form action="/manager/add_car2" method="POST">
            <input type="text" placeholder="차량번호" name="car_id">
            <input type="text" placeholder="차량소속" name="car_div">
            <input type="text" placeholder="차량종류" name="car_type">
            <input type="text" placeholder="차량연식" name="car_birth">
            <input type="submit" value="차량추가">            
        </form>
    </body>
</html>

<!DOCTYPE html>
//car_list.ejs
<html>
    <head>
        <script>
            function popup(message){
                location.href = "/manager/search?car_id="+message;
            }
            function manager(){
                location.href = "/manager";
            }
        </script>
    </head>
        <body>
            <table border ="1">
                <tr>
                    <th>차량번호</th>
                    <th>차량소속</th>
                    <th>차량종류</th>
                    <th>차량연식</th>
                    <th>차량검사일</th>
                    <th>차량검사결과</th>
                </tr>
                <% for (i in car){ %>
                <tr onclick="popup('<%=car[i].car_id%>')">
                    <td><%=car[i].car_id%></td>
                    <td><%=car[i].car_div%></td>
                    <td><%=car[i].car_type%></td>
                    <td><%=car[i].car_birth%></td>
                    <td><%=car[i].car_day%></td>
                    <td><%=car[i].car_result%></td>
                </tr>
                <% } %>
            </table>
        </body>
        <button type ="button" onclick="manager()">manager</button>
</html>

//search.ejs
<!DOCTYPE html>
<html>
    <head>
        <script>
            function car_list(){
                location.href ="/manager/car_list";
            }; 
        </script>
    </head>
        <body>
            <table border ="1">
                <tr>
                    <th>차량번호</th>
                    <th>차량소속</th>
                    <th>차량종류</th>
                    <th>차량연식</th>
                    <th>차량검사일</th>
                    <th>차량검사결과</th>
                </tr>
                    <td><%=car[0].car_id%></td>
                    <td><%=car[0].car_div%></td>
                    <td><%=car[0].car_type%></td>
                    <td><%=car[0].car_birth%></td>
                    <td><%=car[0].car_day%></td>
                    <td><%=car[0].car_result%></td>
                </tr>
            </table>
            <button type="button" onclick="car_list()">car_list</button>
        </body>
</html>