[06.15] mySQL Node.js 연동

const express = require("express");
const mysql = require("mysql2");
const app = express();
const port = 3000;

const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  database: "nodejs",
  password: "",
});

app.use(express.json());

app.post("/products", (req, res) => {
  let product = req.body;
  let name = product["name"];
  let category = product["category"];
  let price = product["price"];

  let query = `INSERT INTO products (name, category, price) VALUES ('${name}', '${category}', '${price}')`;

  let result = {
    status: 200,
  };

  con.execute(query, (err, results, fields) => {
    if (err) {
      console.log(err);
      result.status = 500;
    }
  });

  res.send(result.status.toString());
});

app.get("/products", (req, res) => {
  const query = "SELECT * FROM products";
  con.execute(query, (err, results, fields) => {
    let result = {
      status: 200,
      results,
    };

    if (err) {
      console.log(err);
      result.status = 500;
    }
    const json = JSON.stringify(result);
    res.send(result);
  });
});

app.delete("/products", (req, res) => {
  const data = req.body;
  const id = data["id"];
  const query = `DELETE FROM products WHERE id = ${id}`;
  let result = {
    status: 200,
  };

  con.execute(query, (err, results, fields) => {
    if (err) {
      console.log(err);
      result.status = 500;
    }
    const json = JSON.stringify(result);
    res.send(json);
  });
});

app.put("/products", (req, res) => {
  const product = req.body;
  const id = product["id"];
  const name = product["name"];
  const category = product["category"];
  const price = product["price"];
  const query = `UPDATE products SET name='${name}', category='${category}', price='${price}' WHERE id=${id}`;

  let result = {
    status: 200,
  };

  con.execute(query, (err, results, fields) => {
    if (err) {
      console.log(err);
      result.status = 500;
    }
    let json = JSON.stringify(result);
    res.send(json);
  });
});

app.listen(port, () => {
  console.log(`서버 동작중 : ${port}포트`);
});