[33일차] 20210428 node.js connection pool 이론 / 코드

--------------------------------------------------------1교시 이론수업--------------------------------------------------

 

 

Client -------------------요청 get (http규격)-------> Server -----------------query문(ex. select * from ...)---------> DB

Client<------Nunjucks활용하여 data 넘겨줌--------Server <------------------------results--------------------------- DB 

 

 

DB도 어떻게 보면 Server (port있고 ..... 도 있고 ) 못들음

 

보통 컴터 2개면 1개는 - node.js 만 1개는 db만 돌아가도록 함 각 두개의 컴퓨터를 통신할 수 있게 만듬 

 

 

위에까지 우리가 한거.

만약 동시다발로 요청이 들어오면? -> DB 힘듬. 다운됨 -> server도 안됨 

 

과부하를 어느정도 해소하고 싶어서 connection pool 이라는 거

Pool 개념의 일종

어떤 서버든 왠만한 bd접속은 connection pool로 이루어져 있음. 

 

            

 

                   

     

DB - 접속할 때 mysql + connection 사용 

Server -----query 사용. - query문 발생할 때마다 Server가 DB한테 요청을 계속 때림 ---> DB는 sql 받을 때마다 그대로 결과값 전송 

만약 요청이 100개라면...?  -> conneciton pool로 해결 

 

Server:택배사, DB:받는사람

 

 

Connection Pool ? 

접속한걸 미리 여러개를 만드는 것. 내가 query를 하려고 할 때 접속한 내용을 줬다가 뺏다가 요청값을 잘 소화할 수 있게끔 처리하는 방식. 

 

 

DB가 7명 데리고 있으면 

server가 요청하면 DB한테 1사람 데려옴 

server가 다시 요청하면 그 1사람을 통해 server에게 전달 

그럼 server가 db 에게 그 1사람 (인력)을 돌려줌 

 

???????????????

Sever가 저 동그라미를 빌려가서 DB한테 요청, 전달받는걸 하고 다시 돌려줌 (필요할 때) 

저 동그라미들이 connection pool 인거같음  (connection pool은 서버의 성능(condition, 가격)에 따라 개수가 달라진대) 

 

-> 과부화 방지를 위함 

 

왜 꼭 이렇게 해야하는지?? 찾아보기 

 

 

DBCP 참고할거

www.holaxprogramming.com/2013/01/10/devops-how-to-manage-dbcp/

DB Connection Pool에 대한 이야기

 

 

------------------------------------------------------------2  교 시-------------------------------------------------------------

 

 

직접 Connection Pool 찾아서 구현해보기 

-  > 다양한 방법ㅂ이 나오는데 잘 모르겠어서 대기타는즁

 

*** vs 자동 저장기능이 있었다니 !!!! -> 찾아보기 ! 

 

 

max connection 확인해보는 명령어

show variables like '%max_connections%'; 

mysql 접속 session 확인 명령어

show processlist;

connection 수 확인 명령어 

show status like 'Threads_connected';

 

* CP 에서 중요한 점 : Pool을 생성하는 부분(createPool)은 서버가 구동되고 1번만 ! only 읽혀야 함 

만약 router.post('/',(req,res)=>{})  에서 {} 안에 mysql.createPool 코드가 들어가면 post가 호출될때마다 pool을 생성하게 된다. 그렇게 되면 release를 해도 connection 수가 계속 쌓이게 된다. 

 

 

 

참고사이트

leejungyeoul.tistory.com/83

node server/npm mysql/connection pool 구현

 

 

-----------------------------------------------------------3교시-----------------------------------------------------------

 

 

우리는 아직 callback 사용만 해보고 직접 만들어본적은 없다고 함. - 콜백함수 다시 겅부해보기 

 

로깅파일 이라는건 (log 남기기) 나중에 배울거래 -아직 필요 없다고함 

 

요 위에 세가지는 같은 거 !!@ 

 

function getConn(callback){
    pool.getConnection((err,connection)=>{  //err,connection반환값을 알려주는 애 
        callback(err,connection);
    });
}

콜백함수 만들 때 인자값을 보통 callback이라고 많이 씀 

 

const mysql=require('mysql');


const config = {
    host:'localhost',
    user:'root',
    password:'root',
    database:'homepage1',
    //port는 default로 깔려있는거 쓸꺼라 굳이 안써도 됨
    connectionLimit : 10 //최대 커넥션의 개수 
}

const pool = mysql.createPool(config);

// connection.connect(); 로 썼지만  => 항상 접속되어 있다 .
//pool 은  pool.getConnection(함수) 이렇게 씀
// poop => // pool.getConnection은 내가 사용하고 싶ㅇ느 게 생길 때 그 때 접속을 하겠다 . 
pool.getConnection((err,connection)=>{
    if(err) throw err;  // throw 종료되는 시점을 잡아줌 (if err생기면) 
    // err 안나면 아래 코드 계속 진행됨. 
//conneciton -> 은 connected 접속 되었을 때 이거가지고 사용해 ! 
//연결되면 query문 이제 아래에서 사용 -> 오류 or results
    connection.query("select * from board", (error,results,fields)=>{
        connection.release(); //connection을 이제 release할게 / 반환할게  
        if(error) throw error;  //error 나면 끝낼게
        //error 안나면 이제 코드 실행 아래부터 할게 
        console.log(fields); 
        console.log(results);
        results.render('index.html',{   //<-----예시 
            list:results  // <----예시   / 이렇게 results를 넘긴다. 
        })
    })
})   //이건 method 이고 안에 익명함수가 들어간다


// function getConn(callback){
//     pool.getConnection((err,connection)=>{  //err,connection반환값을 알려주는 애 
//         callback(err,connection);
//     });
// }

 

connection vs pool 만드는법 비슷하나 작동되는건 많이 다름 

techbless.me/2020/01/17/Node-js%EC%97%90%EC%84%9C-Mysql-Connection-Pool-%EC%9D%B4%EC%9A%A9%ED%95%98%EA%B8%B0/

참고 사이트

Node.js에서 Mysql Connection Pool 이용하기

 

---------------------------------------------------------4-교-싀------------------------------------------------------------

 

 

자습 실습 

 

 

 

-----------------------------------------------------점 심 끗 --------------------------------------------------김밥---------

------------------------------------------------------5교시------------------------------------------------------------------

 

자습 실습

idx / number 변수 만들어서 내용 수정 

 

------------------------------------------------------6교시-----------------------------------------------------------------

 

Pool 이해 중 / 외우기 / 질문

 

--------------------------------------------------------7교시---------------------------------------------------------------

 

 

board.js

const express=require('express');
const router=express.Router();
const mysql=require('mysql');

let connection = mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'root',
    database:'homepage1'
})

connection.connect();

router.get('/list',(req,res)=>{

    connection.query("select *, date_format(today, '%H:%i %d.%m.%Y') as today, date_format(motoday, '%H:%i %d.%m.%Y') as motoday from board order by idx desc", (error,results)=>{
        if(error){ 
            console.log(error);
        }else{
            console.log(results);

            let total_record = results.length;
            results.forEach(ele=>{
                ele.number = total_record;
                total_record--;
            })
            console.log(results);
            
            res.render('board/list.html',{  
                board_db:results, 
            })
        }
    })
})

router.get('/write',(req,res)=>{
    res.render('board/write.html')
})

router.post('/writedone',(req,res)=>{
    let subject = req.body.subject;
    let writer=req.body.writer;
    let content=req.body.content;
    let sql=`insert into board (subject,writer,content, today, hit) values ('${subject}','${writer}','${content}', now(),0)`

    connection.query(sql,(error,results)=>{
        if(error){
            console.log(error);
        }else {
            res.redirect('/board/list');  
        }
    })
})


//localhost:3000/dklsdfklsdf/sdfkklsdf/sdjfklsdjkf
router.get('/view',(req,res)=>{
    let idx = req.query.idx;
    let number=req.query.number;
    console.log(idx);
    console.log(number);
    
    connection.query(`select * from board where idx='${idx}'`,(error,results)=>{
        if(error){
            console.log(error)
        }else{
            console.log(results)
            // results.number=number;
            res.render('board/view.html',{
                view_db:results[0],
                number:number,
            });
        }
    })

    connection.query(`update board set hit=hit+1 where idx='${idx}'`);
    
})

router.get('/modify',(req,res)=>{
    let idx=req.query.idx;
    let number=req.query.number;
    console.log(idx);
    console.log(number);

    connection.query(`select * from board where idx=${idx}`,(error,results)=>{
        if(error){
            console.log(error)
        }else{
            console.log(results);
            res.render('board/modify.html',{
                modify_db:results[0],
                number:number,
            });
        }
    })
})

router.post('/modifydone',(req,res)=>{
    let idx = req.body.idx;
    console.log(req.body);
    let number=req.body.number;
    let subject = req.body.subject;
    let writer = req.body.writer;
    let content = req.body.content;

    let sql = `update board set subject='${subject}', writer='${writer}', content='${content}', motoday=now() where idx='${idx}'`;

    connection.query(sql,(error,results)=> {
        if(error){
            console.log(error);
        }else{
            // console.log(results);
            res.redirect(`/board/view?idx=${idx}&number=${number}`);
            
        }
    })
})

router.get('/delete',(req,res)=>{
    
    let idx=req.query.idx;
    let sql=`delete from board where idx='${idx}'`;
    connection.query(sql,(error,results)=>{
        if(error){
            console.log(error);
        }else{
            res.redirect('/board/list');
        }
    })
})

module.exports=router;

pool 적용한 board.js

const express=require('express');
const router=express.Router();
const mysql=require('mysql');

let pool = mysql.createPool({
    host:'localhost',
    user:'root',
    password:'root',
    database:'homepage1',
    connectionLimit:10
})


router.get('/list',(req,res)=>{
    pool.getConnection((err,connection)=>{
        if(err) throw err;  
    
        connection.query("select *, date_format(today, '%H:%i %d.%m.%Y') as today, date_format(motoday, '%H:%i %d.%m.%Y') as motoday from board order by idx desc", (error,results)=>{
            connection.release();
            if(error) throw error;

            let total_record = results.length;
            results.forEach(ele=>{
                ele.number = total_record;
                total_record--;
            });
            // console.log(results);
        
            res.render('board/list.html',{  
                board_db:results, 
            })
        })
    })
})


router.get('/write',(req,res)=>{
    res.render('board/write.html')
})

router.post('/writedone',(req,res)=>{
    pool.getConnection((err,connection)=>{
        if(err) throw err;

    let subject = req.body.subject;
    let writer=req.body.writer;
    let content=req.body.content;
    let sql=`insert into board (subject,writer,content, today, hit) values ('${subject}','${writer}','${content}', now(),0)`

    connection.query(sql,(error,results)=>{
        connection.release();
        if(error) throw error;
        res.redirect('/board/list');  
    })
    })
})


//localhost:3000/dklsdfklsdf/sdfkklsdf/sdjfklsdjkf?
router.get('/view',(req,res)=>{
    pool.getConnection((err,connection)=>{
        if(err) throw err;
    let idx = req.query.idx;
    let number=req.query.number;
    console.log(idx);
    console.log(number);
    
    connection.query(`select * from board where idx='${idx}'`,(error,results)=>{
        connection.release();
        if (error) throw error;
            // results.number=number;
        res.render('board/view.html',{
            view_db:results[0],
            number:number,
        });
    })
    connection.query(`update board set hit=hit+1 where idx='${idx}'`);
    })
    
})

router.get('/modify',(req,res)=>{
    pool.getConnection((err,connection)=>{
        if(err) throw err;
    let idx=req.query.idx;
    let number=req.query.number;
    console.log(idx);
    console.log(number);

    connection.query(`select * from board where idx=${idx}`,(error,results)=>{
        connection.release();
        if(error) throw error;
        res.render('board/modify.html',{
            modify_db:results[0],
            number:number,
        });
    })
    })
})

router.post('/modifydone',(req,res)=>{
    pool.getConnection((err,connection)=>{
        if(err) throw err;
    let idx = req.body.idx;
    console.log(req.body);
    let number=req.body.number;
    let subject = req.body.subject;
    let writer = req.body.writer;
    let content = req.body.content;
    let sql = `update board set subject='${subject}', writer='${writer}', content='${content}', motoday=now() where idx='${idx}'`;

    connection.query(sql,(error,results)=> {
        connection.release();
        if(error) throw error;
        res.redirect(`/board/view?idx=${idx}&number=${number}`);
    })
    })
})

router.get('/delete',(req,res)=>{
    pool.getConnection((err,connection)=>{
        if(err) throw err;
    let idx=req.query.idx;
    let sql=`delete from board where idx='${idx}'`;
    connection.query(sql,(error,results)=>{
        connection.release();
        if(error) throw error;
        res.redirect('/board/list');
    })
    })
})

module.exports=router;

 

 

server.js

const express=require('express');
const app=express();
const main=require("./routes/index");
const board=require("./routes/board");
const nunjucks = require('nunjucks');
const bodyParser=require('body-parser');

nunjucks.configure('view',{
    express:app,
})

app.use(bodyParser.urlencoded({extended:false}));
app.set('view engine', 'html');
app.use(express.static('public'));

app.use('/', main);
app.use('/board',board);


app.listen(3000,()=>{
    console.log('server start port is 3000');
})

 

index.js

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

router.get('/', (req,res)=>{
    res.render('index.html');
})

module.exports=router;

list.html

{% include  "../layout/top.html" %}

<div class="wrap">
    <div class="outerbox" >
        <div class="innerbox">
            <h2 class="main">게시판</h2>
            <div class="mainbox">
                <table id="table1">
                    <tr id ="fields" class="tb">
                        <td class="td0">
                            <span>All</span>
                            <span><input name="chk" type="checkbox" onclick="checkAll()"></span>
                            
                        </td>
                        <td class="td1">번호</td>
                        <td class="td2">제목</td>
                        <td class="td3">작성자</td>
                        <td class="td4">수정 날짜</td>
                        <td class="td4">작성 날짜</td>
                        <td class="td5">조회수</td>
                    </tr>
                    <tr class="tb">{% for item in board_db %}
                        <td class="td0"><input  name="chk" type="checkbox" ></td>
                        <td class="td1">{{item.number}}</td>
                        <td class="td2" id="aa"><a href="/board/view?idx={{item.idx}}&number={{item.number}}">{{item.subject}}</a></td>
                        <td class="td3">{{item.writer}}</td>
                        <td class="td4">{{item.motoday}}</td>
                        <td class="td4">{{item.today}}</td>
                        <td class="td5">{{item.hit}}</td>
                    </tr>{% endfor %} 
                </table> 
                <div class="btnbox"> 
                    <button class="btn"><a href="/">돌아가기</a></button>
                    <button class="btn"><a href="/board/write">글쓰기</a></button>
                </div>
            </div>
        </div>
    </div>
</div>
<script type="text/javascript">

    function checkAll(checkAll){
        console.log(checkAll);
        let checkboxes=document.getElementsByName("chk");
        console.log(checkboxes);
        checkboxes.forEach((checkbox)=>{
            checkbox.checked=checkAll.checked;
        })
    }

</script>

{% include "../layout/bottom.html" %}

modify.html

{% include "../layout/top.html" %}

<div class="wrap">
    <div class="outerbox" >
        <div class="innerbox">
            <h2 class="main"> 글 수정하기 </h2>
            <form method="post" action="/board/modifydone">
                <button disabled type=submit onclick="return flase;" style="display:none;"></button> <!--enter하면 입력되지 않도록 방지-->
                <div class="mainbox">
                    <div class="subbox">
                        <div class="subsubbox">
                            <span class="sub">글 번호 :</span>
                        </div>
                        <input class="txbox" type="text" name="number" value="{{number}}" readonly >
                        <input name="idx" value="{{modify_db.idx}}" style="display:none">
                    </div>
                    <div class="subbox">
                        <div class="subsubbox">
                            <span class="sub">제목 :</span>
                        </div>
                        <input id ="subject_tx" class="txbox" type="text" name="subject" value="{{modify_db.subject}}"> 
                    </div>
                    <div class="wribox">
                        <div class="subsubbox">
                            <span class="wri">작성자 :</span>
                        </div>
                        <input id="writer_tx" class="txbox" type="text" name="writer" value="{{modify_db.writer}}"> 
                    </div>
                    <div class="d">
                        <div class="subsubbox">
                            <span class="con">내용 :</span>
                        </div>
                        <input id="content_tx" class="conbox" type="text" name="content" value="{{modify_db.content}}"> 
                    </div>
                </div>


                <div class ="btnbox">
                    <button class ="btn"><a href="/board/list">돌아가기</a></button>
                    <input class ="btn" type="submit" value="글 수정 완료" onclick="val()">
                </div>
                </form>
            </div>

        </div>
    </div>
</div>
<script type="text/javascript">
    function val (){
        let subject_tx = document.getElementById("subject_tx");
        let writer_tx = document.getElementById("writer_tx");
        let content_tx = document.getElementById("content_tx");

        if(subject_tx.value==""){
            alert("제목을 입력해주세요르레히호");
            event.preventDefault();
        } else if(writer_tx.value==""){
            alert("작성자를 입력해주세요");
            event.preventDefault();
        } else if(content_tx.value==""){
            alert("내용을 입력해주세요");
            event.preventDefault();
        }
    }
</script>

{% include "../layout/bottom.html" %}

view.html

{% include "../layout/top.html" %}
<div class="wrap">
    <div class="outerbox" >
        <div class="innerbox">
                <h2 class="main">VIEW PAGE </h2>
                <div class="mainbox1">
                        <div class="subbox">
                            <div class="subsubbox">
                                <span class="sub">글 번호 :</span>
                            </div>
                            <input class ="txbox" type="text" name="subject" disabled value="{{number}}"> 

                        </div>
                        <div class="subbox">
                            <div class="subsubbox">
                                <span class="sub">제목 :</span>
                            </div>
                            <input class ="txbox" type="text" name="subject"  disabled  value="{{view_db.subject}}"> 

                        </div>
                        <div class="wribox">
                                <div class="subsubbox">
                            <span class="wri">작성자 :</span>
                                </div>
                            <input class = "txbox" type="text" name="writer"  disabled  value="{{view_db.writer}}">
                            <br/>
                        </div>
                        <div class="d">
                            <div class="subsubbox">
                                <span class="con">내용 :</span>
                            </div>
                            <input class = "conbox" type="text"   disabled  name="content" value="{{view_db.content}} ">
                               
                        </div>
                </div>

            <div class="btnbox" class="btnbox3">   
                <button class="btn"><a href="/board/list">뒤로가기</a></button>
                <button class="btn"><a href="/board/modify?idx={{view_db.idx}}&number={{number}}">글 수정하기</a></button>
                <button class="btn"><a href="/board/delete?idx={{view_db.idx}}">글 삭제하기</a></button>
            </div>
        </div>
    </div>
</div>

{% include "../layout/bottom.html" %}

write.html

{% include "../layout/top.html" %}

<div class="wrap">
    <div class="outerbox" >
        <div class="innerbox">
            <h2 class="main"> 글 작성하기</h2>
            <form method="post"  action="/board/writedone">
                <button type=submit name = "name" disabled onclick="enter()"; style="display:none;"></button> <!-- enter하면 입력되지 않도록 방지-->
                <div class="mainbox">
                    <div class="subbox">
                        <div class="subsubbox">
                            <span class="sub">제목 :</span>
                        </div>
                        <input id="subject_tx" class ="txbox" type="text" name="subject"> 

                    </div>
                    <div class="wribox">
                            <div class="subsubbox">
                        <span class="wri">작성자 :</span>
                            </div>
                        <input id="writer_tx" class = "txbox" type="text" name="writer">
                        <br/>
                    </div>
                    <div class="d">
                        <div class="subsubbox">
                            <span class="con">내용 :</span>
                        </div>
                        <textarea id="content_tx" onckeyup="enter()" class = "conbox" type="textbox" name="content"> </textarea>
                        <!--textarea name="content"></textarea-->
                                
                    </div>
                </div>

                <div class="btnbox">       
                    <button class="btn"><a href="/board/list">돌아가기</a></button>
                    <input class ="btn" type="submit" value="글작성" onclick="val()">
                </div> 
            </form>
        </div>
    </div>
</div>
<script type="text/javascript">
    function val (){
        let subject_tx = document.getElementById("subject_tx");
        let writer_tx = document.getElementById("writer_tx");
        let content_tx = document.getElementById("content_tx");

        if(subject_tx.value==""){
            alert("제목을 입력해주세요르레히호");
            event.preventDefault();
        } else if(writer_tx.value==""){
            alert("작성자를 입력해주세요");
            event.preventDefault();
        } else if(content_tx.value==""){
            alert("내용을 입력해주세요");
            event.preventDefault();
        }
    }
    function enter(){
        if(window.event.keyCode ==13){
            alert("d");
        document.getElementsByName("content").innerHTML+="<br/>";
    }
}
</script>

{% include "../layout/bottom.html" %}

 

 

bottom & top.html same 

 

 

 

 

 

숙제 : 게시판 paging 해보기 ~ 좀 빡셀수도..

pool , router 다시 공부 &외우기

게시판 viewaftermodify,html 만들기 

checkbox checked 복수삭제 

proto type 예습 

 

 

내일 배울 것 

 

proto type? ? 

ajax 등등 

 

-> 게시판을 만들 때 필요한 것들을 배울꺼야 계속 업그레이드 예정