[NODEJS] Mysql CRUD

- 개발환경

# 개발환경 os : windows10 Pro 64bit tool : visual studio code npm : v6.9.0 nodejs : v10.16.0 db : mysql 5.7.14

 

 

- 소스 디렉토리

# 소스 디렉토리 [ROOT]   ㄴ app.js   ㄴ config.env   ㄴ [config]      ㄴ dev.js      ㄴ prod.js        ㄴ logger.js   ㄴ [routes]      ㄴ router1.js      ㄴ router2.js   ㄴ [db]      ㄴ mysql.js

 

 

 

0. 사전준비

https://bogyum-uncle.tistory.com/75

불러오는 중입니다...

 

 

1. CRUD

 

/appRoot/routes/router2.js

 

  1.1. Create

/**
* [INSERT]
*/

router.post('/create', function(request, response, next) {
// response.writeHead(200, {"Content-Type":"text/plain; charset=utf-8"});
// response.end("/r2/create:POST 경로로 접근...!");
 
 var body = request.body;
      
 client.query("INSERT INTO products (name, modelnumber, series) VALUES ('www', 'eee', 'rrr')", [
        body.name, body.modelnumber, body.series
      ], function(err, result, fields){
        if(!err){
          console.log(result);
          response.send('Inserted products');
          // response.redirect("/select");
        }else{
          console.log(err);
        }
   });
});

  1.2. Read

/**
* [SELECT]
*/

 //GET all product
    router.get('/select', function(request, response, next) {
      client.query("SELECT * FROM products;", function(err, result, fields){
        if(!err){
          // response.writeHead(200, {"Content-Type":"text/plain; charset=utf-8"});
          // response.end(JSON.stringify(result));
          console.log(result);
          //console.log('첫번째 데이터의 name만 가져오기 : ' + result[0].name);
          response.send(result);
        }else{
          console.log(err);
          console.log("쿼리문에 오류가 있습니다.");
        }
      });
    });

    //GET an product
    router.get('/select/:id', function(request, response, next){
      client.query('SELECT * FROM products WHERE id = ?', [request.params.id], function(err, result, fields){
        if(!err){
          console.log(result);
          response.send(result);
        }else{
          console.log(err);
        }
      });
    });

  1.3. Update

/**
* [UPDATE]
*/
    router.put('/update', function(request, response, next) {
      // response.writeHead(200, {"Content-Type":"text/plain; charset=utf-8"});
      // response.end("/r2/create:POST 경로로 접근...!");
 
      var body = request.body;
      
      client.query("UPDATE products SET name = 'test' WHERE id = 5", [
        body.name, body.modelnumber, body.series
      ], function(err, result, fields){
        if(!err){
          console.log(result);
          response.send('update products');
          // response.redirect("/select");
        }else{
          console.log(err);
        }
      });
    });

 

  1.4. Delete

/**
*  [DELETE]
*/
    router.delete('/delete/:id', function(request, response){
      client.query("DELETE FROM products WHERE id = ?", [request.params.id], function(err, result, fields){
        if(!err){
          console.log('Delected succuss');
          response.send('Delected succuss');
        }else{
          console.log(err);
        }
      });
    });

 

 

 

2. 전체 소스

 

  2.1. /appRoot/app.js

//Express를 변수에 담기
var express = require("express");

//Express 호출 후 새로운 Express 애플리케이션을 변수(app)에 넣음
var app = express();

var http = require("http");
var path = require("path");

/**
 * express.static, 정적 파일 서비스 제공
 */
//path.resoleve : os에 따라 /public 혹은 \public 처럼 분리자가 다를 수 있지만 이 부분을 처리해 준다.
//__dirname : 현재 위치를 가리키는 node 전역변수
var publicPath = path.resolve(__dirname, "public");
app.use(express.static(publicPath));


/**
 *  config 설정파일  (환경변수 관리)
 *  dotenv 노드 보안 (서버, DB 정보 등)
 */
//proces.env에 .env의 내용이 들어감
require("dotenv").config({path: __dirname + '\\' + 'config.env'});
console.log(process.env.DB_NAME);

let config;
if(!process.env.NODE_ENV == 'dev'){
    config = require('./config/dev');
}else if(process.env.NODE_ENV == 'prod'){
    config = require('./config/prod');
}else{
    config = require('./config/dev');
}
console.log(config.info);


/**
 * logger
 */
var logger = require('./config/logger');
app.use(express.json());        //http 요청 메시지를 json 형태로 변환
app.use(require("morgan")("combined", {"stream": logger.stream}));


/**
* mysql Connection
*/
let client = require('./db/mysql');
var bodyparser = require('body-parser');
app.use(bodyparser.json());

client.connect((err) => {
    if(!err)
      console.log('DB connection success');
    else
      console.log('DB connection failed\n Error : ' + JSON.stringify(err, undefined, 2));
});


/**
 * Routing 경로 설정
 * 
 * app.js에서 생성된 데이터를 사용하기 위해서 require에서 app이란 객체를 전달
 */

app.use('/r1', require('./routes/router1')(app));
app.use('/r2', require('./routes/router2')(app, client));
app.use(function(request, response){
    //4XX : 클라이언트 오류
    response.statusCode = 404;
    response.end("404 Error!");
});


/**
 * swagger(미완)
 */
// const swaggerJSDoc = require('swagger-jsdoc');
// const swaggerOption = require('./swagger');
// const swaggerSpec = swaggerJSDoc(swaggerOption);
// const swaggerUi = require('swagger-ui-express');
// app.use('/api-docs', swaggerUi.serve, swaggerUi.setup(swaggerSpec));


app.listen(3000, function(){
    console.log("Server Running : 3000....!");
});

 

  2.2. /appRoot/routes/route2.js

//함수로 만들어 객체 app을 전달
module.exports = function(app, client){

    var express = require('express');
    var router = express.Router();
   
    /**
     * router2.js routing
     * [SELECT]
     */
    router.get("/", function(request, response){
      response.writeHead(200, {"Content-Type":"text/plain; charset=utf-8"});
      response.end("/r2 경로로 접근...!");
      console.log(response.statusCode);
    });

    router.get("/action", function(request, response){
      //writeHead(statusCode, Object) 응답헤더 작성
      //end([data], [encoding]) 응답 본문 작성
      response.writeHead(200, {"Content-Type":"text/plain; charset=utf-8"});
      response.end("/r2/action 경로로 접근...!");
      console.log(response.statusCode);
    });

    //GET all product
    router.get('/select', function(request, response, next) {
      client.query("SELECT * FROM products;", function(err, result, fields){
        if(!err){
          // response.writeHead(200, {"Content-Type":"text/plain; charset=utf-8"});
          // response.end(JSON.stringify(result));
          console.log(result);
          //console.log('첫번째 데이터의 name만 가져오기 : ' + result[0].name);
          response.send(result);
        }else{
          console.log(err);
          console.log("쿼리문에 오류가 있습니다.");
        }
      });
    });

    //GET an product
    router.get('/select/:id', function(request, response, next){
      client.query('SELECT * FROM products WHERE id = ?', [request.params.id], function(err, result, fields){
        if(!err){
          console.log(result);
          response.send(result);
        }else{
          console.log(err);
        }
      });
    });
  
    /**
     * [INSERT]
     */
    router.post('/create', function(request, response, next) {
      // response.writeHead(200, {"Content-Type":"text/plain; charset=utf-8"});
      // response.end("/r2/create:POST 경로로 접근...!");
 
      var body = request.body;
      
      client.query("INSERT INTO products (name, modelnumber, series) VALUES ('www', 'eee', 'rrr')", [
        body.name, body.modelnumber, body.series
      ], function(err, result, fields){
        if(!err){
          console.log(result);
          response.send('Inserted products');
          // response.redirect("/select");
        }else{
          console.log(err);
        }
      });
     });


    /**
    * [UPDATE]
    */
    router.put('/update', function(request, response, next) {
      // response.writeHead(200, {"Content-Type":"text/plain; charset=utf-8"});
      // response.end("/r2/create:POST 경로로 접근...!");
 
      var body = request.body;
      
      client.query("UPDATE products SET name = 'test' WHERE id = 5", [
        body.name, body.modelnumber, body.series
      ], function(err, result, fields){
        if(!err){
          console.log(result);
          response.send('update products');
          // response.redirect("/select");
        }else{
          console.log(err);
        }
      });
    });

    /**
     *  [DELETE]
     */
    router.delete('/delete/:id', function(request, response){
      client.query("DELETE FROM products WHERE id = ?", [request.params.id], function(err, result, fields){
        if(!err){
          console.log('Delected succuss');
          response.send('Delected succuss');
        }else{
          console.log(err);
        }
      });
    });


    //라우터 리턴
    return router;
}

 

 

3. 확인

 

  3.1. select 확인

 

  3.2. insert 확인