[자바스크립트] 작은 도서관 프로그램 만들기 (MySQL, Express, ejs, Node.js) - Mysql, express, ejs crud example

2020/09/03 - [토이 프로젝트] - [자바스크립트] 작은 도서관 프로그램 만들기 (쿠키, 로컬, 세션 스토리지의 개념 포함)

[자바스크립트] 작은 도서관 프로그램 만들기 (쿠키, 로컬, 세션 스토리지의 개념 포함)

  지난번에 만들었던 작은 도서관 프로그램이다. 그 당시에는 LocalStorage라는 것을 이용해 데이터를 해당 ip주소에 맞게 저장하는 개념을 사용하였다. 하지만 이번에는 조금더 제대로 된 사이트를 만들어보고자 MySQL과 ejs그리고 Node.js를 활용하여 CRUD를 정확히 구현할 계획이다. MySQL은 얼마전 포스팅한 자료들이 많으니 블로그에서 MySQL 관련 글을 활용하면 될것같다.

 

 

 

 

사전 작업

npm install --save express

npm install --save mysql

npm install --save sequelize

npm install --save body-parser

npm install --save-dev sequelize-cli

 

---

PART 1 - 분할된 파일들

app.js

let express = require('express');
let app = express();
let db_config = require(__dirname + '/config/database.js');
let conn = db_config.init();
let bodyParser = require('body-parser');

db_config.connect(conn);

app.set('views', __dirname + '/views');
app.set('view engine', 'ejs');

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended : false}));
app.use(express.static('public'));
app.use(express.static('.'));

app.get('/', function (req, res) {
  let sql = 'SELECT * FROM bookstable';    
  conn.query(sql, function (err, result, fields) {
    if(err) console.log('query is not excuted. select fail...\n' + err);
    else res.render('index.ejs', {list : result});
  });
});


app.post('/find', function (req, res) {
  let searchInput = req.body.searchInput;
  let dateStart = req.body.dateStart;
  let dateEnd = req.body.dateEnd;
  let sql = 'SELECT * FROM bookstable WHERE title|content|author = ? OR date BETWEEN ? AND ?';
  let params = [searchInput, dateStart, dateEnd];
  conn.query(sql, params, function(err, result) {
    console.log(result)
    if(err) console.log('query is not excuted. search fail...\n' + err);
    else res.render('index.ejs', {list : result});
  });
});

app.post('/inputBook', function (req, res) {
  let title = req.body.title;
  let content = req.body.content;
  let author = req.body.author;
  let date = req.body.date;
  let sql = 'INSERT INTO bookstable VALUES(id, ?, ?, ?, ?)';
  let params = [title, content, author, date];
  console.log(sql);
  conn.query(sql, params, function(err, result, fields) {
    console.log(result)
    if(err) {
      console.log('query is not excuted. input fail...\n' + err);
    }
    else {
      res.redirect('/')
    }
  });
});

app.post('/edit/:id', function (req, res) {
  let title = req.body.title;
  let content = req.body.content;
  let author = req.body.author;
  let date = req.body.date;
  let id = req.params.id;
  console.log(id);
  let sql = `UPDATE bookstable SET title=?, content=?, author=?, date=? WHERE id=?`;
  let params = [title, content, author, date, id];
  console.log(sql);
  conn.query(sql, params, function(err) {
    if(err) {
      console.log('query is not excuted. edit fail...\n' + err);
    }
    else {
      res.redirect('/');
    }
  });
});

app.get('/delete/:id', function (req, res) {
  let sql = 'DELETE FROM bookstable WHERE id=?';
  console.log(req.params.id)
  conn.query(sql, [req.params.id], function() {
    res.redirect('/');
  })
})

app.listen(3000, () => console.log('Server is running on port 3000...'));

 

 

package.json

{
  "name": "test_libaray",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "ejs": "^3.1.5",
    "express": "^4.17.1",
    "mysql": "^2.18.1"
  }
}

 

 

/public/library.js

function popInfo(id, title, content, author, date) {
  console.log(id);
  document.getElementById("title").value = title;
  document.getElementById("content").value = content;
  document.getElementById("author").value = author;
  document.getElementById("date").value = date;
};

function btnUrl(id, btn) {
  if(btn === "putBtn") {
    frm.action = "/inputBook";
  } else if(btn === "editBtn") {
    frm.action = "/edit/" + id;
  } else if(btn === "deleteBtn") {
    frm.action = "/delete/" + id;
    frm.method = "GET";
  }
}

 

 

/config/database.js

let mysql = require('mysql');
let db_info = {
  host: 'localhost',
  port: '3306',
  user: 'root',
  password: ****,
  database: 'library'
}

module.exports = {
  init: function() {
    return mysql.createConnection(db_info);
  },
  connect: function(conn) {
    conn.connect(function(err) {
      if(err) {
        console.log('mysql connection error : ' + err);
      } else {
        console.log('mysql is connected successfully!');
      };
    });
  }
}

 

 

/views/index.ejs

<!DOCTYPE html>
<html>
    <head>
        <title>작은 도서관</title>
    </head>
    <style>
        .dateSet {
            width: 110px;
            height: 16px;
        }
        #logo {
            background-color: #5E6698;
            width: 950px;
            padding: 5px;
        }
        #search {
            background-color: #5E6698;
            width: 950px;
            padding: 5px;
        }
        #table {
            background-color: white;
            width: 950px;
            padding: 5px;
            height: 275px;
            overflow: auto;
        }
        #newBooks {
            background-color: #5E6698;
            width: 950px;
            padding: 5px;
        }
        #searchInput {
            width: 440px;
        }
        #searchBtn {
            width: 100px;
        }
        #putBtn {
            width: 400px;
            height: 50px;
        }
        #title {
            width: 495px;
        }
        #content {
            width: 798px;
            height: 100px;
            resize: none;
        }
        table {
            background-color: white;
            width: 810px;
            table-layout: fixed;
            word-break: break-all;
        }
        tr {
            background-color: white;
            padding: 10px;
            border: 0px solid black;
        }
        th {
            padding: 10px;
            border: 0px solid black;
        }
        td {
            padding: 10px;
            border: 0px solid black;
            text-align: center;
            white-space: nowrap;
            text-overflow: ellipsis;
            overflow: hidden;
        }
        a {
            color: black;
            text-decoration: none;
        }
        a:hover {
            color: rgb(74, 138, 211);
        }
    </style>
    <body>
        <div id="library">
            <center>
                <div id="logo"><br>
                    <center>
                        <a href="/"><img src="../vendit-logo-white.png" onclick="location.reload()" width="150px"></a>
                    </center>
                </div>
            </center>
            <center>
                <div id="search">
                    <center>
                        <!--<b>서적 검색</b><br>-->
                        <form action="/find" method="POST">
                          <input type="text" id="searchInput" name="searchInput" placeholder="제목, 내용, 저자로 검색">
                          <input class="dateSet" type="date" id="dateStart" name="dateStart">
                          ~
                          <input class="dateSet" type="date" id="dateEnd" name="dateEnd">
                          <button id="searchBtn" type="submit">검색</button>
                        </form>
                    </center><br>
                </div>
            </center>
            <center>
                <div id="table">
                    <!--<b>찾은 서적 목록</b><br>-->
                    <center>
                        <table>
                            <thead>
                                <tr><th>순번</th><th>제목</th><th>내용</th><th>저자</th><th>출간날짜</th><th>수정/삭제</th></tr>
                            </thead>
                            <tbody id="bTable">
                              <%
                              for(let i = 0; i < list.length; i++)
                              {
                              %>
                              <tr>
                                <td><%=i+1%></td>
                                <td><a href="#" id='<%=list[i].id%>' onclick="popInfo(this.id, '<%=list[i].title%>', '<%=list[i].content%>', '<%=list[i].author%>', '<%=list[i].date%>')"><%=list[i].title%></a></td>
                                <td><%=list[i].content%></td>
                                <td><%=list[i].author%></td>
                                <td><%=list[i].date%></td>
                                <td>
                                  <form name="frm" method="POST">
                                  <button id="<%=list[i].id%>" type="submit" onclick='btnUrl(this.id ,"editBtn")' style="float:left">수정</button>                                  
                                  <button id="<%=list[i].id%>" type="submit" onclick='btnUrl(this.id, "deleteBtn")' style="float:right">삭제</button>                                
                                </td>
                              </tr>
                              <%
                              }
                              %>
                              <%
                              for(let j = 0; j < 5 - list.length; j++)
                              {
                              %>
                              <tr>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                              </tr>
                              <%
                              }
                              %>
                            </tbody>
                        </table>
                    </center>    
                </div>
            </center>
            <center>
                <div id="newBooks">
                    <center><br>
                        <%
                        if(list.length === 0) {
                        %>
                        <form name="frm" method="POST">
                        <%
                        }
                        %>
                        <!--<b>새로운 서적 등록</b><br>-->
                          <input class="new" type="text" id="title" name="title" placeholder="제목" > 
                          <input class="new" type="text" id="author" name="author" placeholder="저자" >
                          <input class="dateSet" type="date" id="date" name="date" placeholder="출간날짜" ><br><br>
                          <textarea type="text" id="content" name="content" placeholder="내용"></textarea><br><br>
                          <button id="putBtn" type="submit" onclick = 'btnUrl(this.id ,"putBtn")'>등록</button>                          
                        </form>
                    </center><br>
                </div>
            </center>
        </div>
        <script src="/library.js"></script>
        <script>
          
        </script>
    </body>
</html>

 

---

 

PART 2 - 하나로 합친 파일

/server/index.js

'use strict';
const mysql = require('mysql');
const express = require('express');
const path = require('path');
const app = express();
const { Sequelize, DataTypes } = require('sequelize');
const Op = Sequelize.Op;
const env = process.env.NODE_ENV || 'development';
const config = {
  "development": {
    "username": "root",
    "password": ****,
    "database": "library",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}[env];

let http = require('http');
let bodyParser = require('body-parser');
let sequelize;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}

const books = sequelize.define('books', {
  title: {
    type: DataTypes.STRING(200),
    allowNull: true,
  },
  content: {
    type: DataTypes.STRING,
    allowNull: true,
  },
  author: {
    type: DataTypes.STRING,
    allowNull: true,
  },
  date: {
    type: DataTypes.DATE,
    allowNull: true,
  }
}, {
  // Other model options go here
});

// 한번만 실행
sequelize.sync({ force: false }).then( () => {
  console.log("db 연결 완료");
  
}).catch(err => {
  console.log("db 연결 실패");
  console.log(err);
});


app.set('views', '../');
app.engine('html', require('ejs').renderFile);
app.set('view engine', 'html');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended : false}));

app.get('/', async(req, res) => { 
  await books.findAll().then(result => {
    res.json(result)
    res.render('index', {
      list : result
    });
  });
});



app.post('/inputBook', async (req, res) => {
  await books.create({
    title : req.body.title,
    content : req.body.content,
    author : req.body.author,
    date : req.body.date
  }).then(result => {
    console.log("success input data" + result);
    res.redirect('/');
  }).catch(err => {
    console.log("fail update data" + err);
  });
});

app.post('/edit/:id', async(req, res) => {
  await books.update({
    title : req.body.title,
    content : req.body.content,
    author : req.body.author,
    date : req.body.date
  }, {
    where : {id: req.params.id}
  }).then(result => {
    console.log("success update data" + result);
    res.redirect('/');
  }).catch(err => {
    console.log("fail update data" + err)
  })
})

app.get('/delete/:id', async(req, res) => {
  await books.destroy({
    where: {id: req.params.id}
  }).then(result => {
    console.log(result)
    res.redirect('/');
  }).catch(err => {
    console.log("데이터 삭제" + err);
  });
});

app.post('/find', async(req, res) => {
  let searchInput = req.body.searchInput;
  let dateStart = req.body.dateStart;
  let dateEnd = req.body.dateEnd;
  await books.findAll({
    where : {
      [Op.or]: [
        {
          title : {
            [Op.like]:"%" + searchInput + "%"
          }
        },
        {
          content : {
            [Op.like]:"%" + searchInput + "%"
          }
        },
        {
          author : {
          [Op.like]:"%" + searchInput + "%"
          }
        },
        {
          date : {
          [Op.between]: [new Date(dateStart), new Date(dateEnd)]
          }
        }
      ]
    }
  }).then(result => {
    res.render('index', {list: result})
  })
});

http.createServer(app).listen(3000, () => {'Server is running on port 3000...'})

 

 

index.ejs

<!DOCTYPE html>
<html>
    <head>
        <title>작은 도서관</title>
    </head>
    <style>
        .dateSet {
            width: 110px;
            height: 16px;
        }
        #logo {
            background-color: #5E6698;
            width: 950px;
            padding: 5px;
        }
        #search {
            background-color: #5E6698;
            width: 950px;
            padding: 5px;
        }
        #table {
            background-color: white;
            width: 950px;
            padding: 5px;
            height: 275px;
            overflow: auto;
        }
        #newBooks {
            background-color: #5E6698;
            width: 950px;
            padding: 5px;
        }
        #searchInput {
            width: 440px;
        }
        #searchBtn {
            width: 100px;
        }
        #putBtn {
            width: 400px;
            height: 50px;
        }
        #title {
            width: 495px;
        }
        #content {
            width: 798px;
            height: 100px;
            resize: none;
        }
        table {
            background-color: white;
            width: 810px;
            table-layout: fixed;
            word-break: break-all;
        }
        tr {
            background-color: white;
            padding: 10px;
            border: 0px solid black;
        }
        th {
            padding: 10px;
            border: 0px solid black;
        }
        td {
            padding: 10px;
            border: 0px solid black;
            text-align: center;
            white-space: nowrap;
            text-overflow: ellipsis;
            overflow: hidden;
        }
        a {
            color: black;
            text-decoration: none;
        }
        a:hover {
            color: rgb(74, 138, 211);
        }
    </style>
    <body onload="init()">
        <div id="library">
            <center>
                <div id="logo" style="color: white; font-size: 30px;"><br>
                  <b>도서관리 프로그램</b>
                  <br><br>
                </div>
            </center>
            <center>
                <div id="search">
                    <center>
                        <!--<b>서적 검색</b><br>-->
                        <form action="/find" method="POST">
                          <input type="text" id="searchInput" name="searchInput" placeholder="제목, 내용, 저자로 검색">
                          <input class="dateSet" type="date" id="dateStart" name="dateStart">
                          ~
                          <input class="dateSet" type="date" id="dateEnd" name="dateEnd">
                          <button id="searchBtn" type="submit">검색</button>
                        </form>
                    </center><br>
                </div>
            </center>
            <center>
                <div id="table">
                    <!--<b>찾은 서적 목록</b><br>-->
                    <center>
                        <table>
                            <thead>
                                <tr><th>순번</th><th>제목</th><th>내용</th><th>저자</th><th>출간날짜</th><th>수정/삭제</th></tr>
                            </thead>
                            <tbody id="bTable">
                              <%
                              for(let i = 0; i < list.length; i++)
                              {
                              %>
                              <tr>
                                <td><%=i+1%></td>
                                <td><a href="#" id='<%=list[i].id%>' onclick="popInfo(this.id, '<%=list[i].title%>', '<%=list[i].content%>', '<%=list[i].author%>', '<%=list[i].date%>')"><%=list[i].title%></a></td>
                                <td><%=list[i].content%></td>
                                <td><%=list[i].author%></td>
                                <td><%=list[i].date%></td>
                                <td>
                                  <form name="frm" method="POST">
                                  <button id="<%=list[i].id%>" type="submit" onclick='btnUrl(this.id ,"editBtn")' style="float:left">수정</button>                                  
                                  <button id="<%=list[i].id%>" type="submit" onclick='btnUrl(this.id, "deleteBtn")' style="float:right">삭제</button>                                
                                </td>
                              </tr>
                              <%
                              }
                              %>
                              <%
                              for(let j = 0; j < 5 - list.length; j++)
                              {
                              %>
                              <tr>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                              </tr>
                              <%
                              }
                              %>
                            </tbody>
                        </table>
                    </center>    
                </div>
            </center>
            <center>
                <div id="newBooks">
                    <center><br>
                        <%
                        if(list.length === 0) {
                        %>
                        <form name="frm" method="POST">
                        <%
                        }
                        %>
                        <!--<b>새로운 서적 등록</b><br>-->
                          <input class="new" type="text" id="title" name="title" placeholder="제목" > 
                          <input class="new" type="text" id="author" name="author" placeholder="저자" >
                          <input class="dateSet" type="date" id="date" name="date" placeholder="출간날짜" ><br><br>
                          <textarea type="text" id="content" name="content" placeholder="내용"></textarea><br><br>
                          <button id="putBtn" type="submit" onclick = 'btnUrl(this.id ,"putBtn")'>등록</button>                          
                        </form>
                    </center><br>
                </div>
            </center>
        </div>
        <script>
        function popInfo(id, title, content, author, date) {
          console.log(id);
          document.getElementById("title").value = title;
          document.getElementById("content").value = content;
          document.getElementById("author").value = author;
          document.getElementById("date").value = date;
        };

        function btnUrl(id, btn) {
          if(btn === "putBtn") {
            frm.action = "/inputBook";
          } else if(btn === "editBtn") {
            frm.action = "/edit/" + id;
          } else if(btn === "deleteBtn") {
            frm.action = "/delete/" + id;
            frm.method = "GET";
          }
        }
        </script>
    </body>
</html>