node.js mysql 예제

함동화 쪽지보내기 메일보내기 자기소개 아이디로 검색 작성
59.♡.152.190 아이피
작성일 2021.12.28 20:22
10,679 조회
목록
[ mysql.js ] ------------------------------------------------------------------------------------------------------------------------------

// 모듈 추출
var fs = require('fs');
var ejs = require('ejs');
var mysql = require('mysql');
var express = require('express');
var bodyParser = require('body-parser');

// 데이터베이스와 연결
var client = mysql.createConnection({
    host : '172.17.0.1',
    user: 'node',
    password: '비일번호',
    port: 3306,
    database: 'node',
    dateStrings: 'date'
});

// 서버 생성
var app = express();
app.use(bodyParser.urlencoded({
    extended: false
}));

// 서버 실행
app.listen(7941, function(){
    console.log('Sever Running - Port : 7941');
})

// 라우트 수행
app.get('/', function(request, response){
    
    fs.readFile(__dirname+'/mysql_list.html', 'utf8', function(error, data){

        client.query('SELECT * FROM products', function(error, result, fields){
            if (error) throw error;
            // 응답합니다.
            response.send(ejs.render(data, {
                data: result
            }));
        });
    });
    
});

app.get('/delete/:id', function(request, response){
    //쿼리 수행
    client.query('DELETE FROM products WHERE ID=?', [request.params.id], function(){
        response.redirect('/');
    });
});

app.get('/insert', function(request, response){
    fs.readFile(__dirname+'/mysql_insert.html', 'utf8', function(error, data){
        response.send(data);
    });
});

app.post('/insert', function(request, response){
    var body = request.body;

    // 데이타페이스 쿼리실행
    client.query('INSERT INTO products (Name, ModelNumber, Series) VALUES (?, ?, ?)', [body.name, body.modelnumber, body.series], function(){
        response.redirect('/');
    });
});

app.get('/edit/:id', function(request, response){
    fs.readFile(__dirname+'/mysql_edit.html', 'utf8', function(error, data){
        client.query('SELECT * FROM products WHERE ID = ?', [request.params.id], function(error, result){
            response.send(ejs.render(data, {
                data: result[0]
            }));
        });
    });
});

app.post('/edit/:id', function(request, response){
    var body = request.body;

    client.query('UPDATE products SET Name=?, ModelNumber=?, Series=? WHERE id=?', [body.name, body.modelnumber, body.series, request.params.id], function(){
        response.redirect('/');
    })
});

[ mysql_list.html ] ------------------------------------------------------------------------------------------------------------------------------

<!DOCTYPE html>
<html>
    <head>
        <title>List Page</title>
    </head>
    <body>
        <h1>List Page</h1>
        <a href="/insert">Insert Data</a>
        <hr/>
        <table width="100%" border="1">
            <tr>
                <th>삭제</th>
                <th>수정</th>
                <th>ID</th>
                <th>Name</th>
                <th>모델명</th>
                <th>Series</th>
            </tr>
            <% for (let i=0; i<data.length; i++) { %>
            <tr>
                <td><a href="/delete/<%= data[i].ID %>">DEL</a></td>
                <td><a href="/edit/<%= data[i].ID %>">EDIT</a></td>
                <td><%= data[i].ID %></td>
                <td><%= data[i].Name %></td>
                <td><%= data[i].ModelNumber %></td>
                <td><%= data[i].Series %></td>
            </tr>
            <% }; %>
        </table>
    </body>
</html>

[ mysql_insert.html ] ------------------------------------------------------------------------------------------------------------------------------

<!DOCTYPE html>
<html>
    <head>
        <title>Insert Page</title>
    </head>
    <body>
        <h1>Insert Page</h1>
        <hr/>
        <form method="POST">
            <fieldset>
                <legend>Insert Data</legend>
                <table width="100%" border="1">
                    <tr>
                        <td><label>Name</label></td>
                        <td><input type="text" name="name"></td>
                    </tr>
                    <tr>
                        <td><label>ModelNumber</label></td>
                        <td><input type="text" name="modelnumber"></td>
                    </tr>
                    <tr>
                        <td><label>Series</label></td>
                        <td><input type="text" name="series"></td>
                    </tr>
                </table>
                <input type="submit">
            </fieldset>
        </form>
    </body>
</html>

[ mysql_edit.html ] ------------------------------------------------------------------------------------------------------------------------------

<!DOCTYPE html>
<html>
    <head>
        <title>Edit Page</title>
    </head>
    <body>
        <h1>Edit Page</h1>
        <hr/>
        <form method="POST">
            <fieldset>
                <legend>Insert Data</legend>
                <table width="100%" border="1">
                    <tr>
                        <td><label>Name</label></td>
                        <td><input type="text" name="name" value="<%= data.ID %>"></td>
                    </tr>
                    <tr>
                        <td><label>ModelNumber</label></td>
                        <td><input type="text" name="modelnumber" value="<%= data.ModelNumber %>"></td>
                    </tr>
                    <tr>
                        <td><label>Series</label></td>
                        <td><input type="text" name="series" value="<%= data.Series %>"></td>
                    </tr>
                </table>
                <input type="submit">
            </fieldset>
        </form>
    </body>
</html>