본문 바로가기
Quality control (Univ. Study)/Database Design

MySQL Express upgrade

by 생각하는 이상훈 2023. 10. 5.
728x90

Source Code

우선 코드가 다양한 파일 내에 구성되어있어서 git 주소를 첨부한다. https://github.com/leesanghoon421/Database/tree/main/week6

sql.js

import mysql from 'mysql2';

require("dotenv").config();

const pool = mysql.createPool({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: '0421sh0421sh!',
    database: 'week5',
});

const promisePool = pool.promise();

// select query
export const selectSql = {
    getBuilding: async () => {
        const sql = `select * from building`;
        const [result] = await promisePool.query(sql);
        return result;
    },
    getDepartment: async () => {
        const sql = `select * from department`;
        const [result] = await promisePool.query(sql);
        return result;
    },
    getRoom: async () => {
        const sql = `select * from room`;
        const [result] = await promisePool.query(sql);
        return result;
    },
    getStudent: async () => {
        const sql = `select * from student`;
        const [result] = await promisePool.query(sql);
        return result;
    },
    getClass: async () => {
        const sql = `select * from class`;
        const [result] = await promisePool.query(sql);
        return result;
    }
}

// insert query
export const insertSql = {
    setStudent: async (data) => {
        const sql = `insert into student values (
            "${data.Id}", "${data.Name}", "${data.Email}", 
            "${data.PhoneNumber}", "${data.Major}"
        )`
        console.log(data);
        await promisePool.query(sql);
    },
    setDepartment: async (data1) => {
        const sql = `insert into student values (
            "${data1.Id}", "${data1.Name}", "${data1.Email}", 
            "${data1.PhoneNumber}"
        )`
        console.log(data1);
        await promisePool.query(sql);
    },
};

// update query
export const updateSql = {
    updateStudent: async (data) => {
        console.log(data);
        const sql = `
            UPDATE Student 
            SET Id = ${data.Id}, Name = "${data.Name}", 
                Email = "${data.Email}", PhoneNumber = "${data.PhoneNumber}",
                Major = "${data.Major}"
            WHERE Id = ${data.Id}`;
        console.log(sql);
        await promisePool.query(sql);
    },
    updateDepartment: async (data1) => {
        console.log(data1);
        const sql = `
            UPDATE Student 
            SET Id = ${data1.Id}, Name = "${data1.Name}", 
                Email = "${data1.Email}", PhoneNumber = "${data1.PhoneNumber}"
            WHERE Id = ${data1.Id}`;
        console.log(sql);
        await promisePool.query(sql);
    },
};

home.js

import express from 'express';
import { insertSql } from '../database/sql';

const router = express.Router();

router.get('/', (req, res) => {
    res.render('home', { data: " " });
})

router.post('/', (req, res) => {
    const vars = req.body;

    const data = {
        Id: vars.id,
        Name: vars.name,
        Email: vars.email,
        PhoneNumber: vars.phoneNumber,
        Major: vars.major,
    };
    insertSql.setStudent(data);
})

module.exports = router;

select.js

import express from 'express';
import { selectSql } from '../database/sql';

const router = express.Router();

router.get('/', async (req, res) => {
    const Building = await selectSql.getBuilding();
    const Department = await selectSql.getDepartment();
    const Room = await selectSql.getRoom();
    const Student = await selectSql.getStudent();
    const Class = await selectSql.getClass();
    console.log(Department)
    res.render('select', {
        main_title: "Tables in InhaDB",
        title1: "Building",
        title2: "Department",
        title3: "Room",
        title4: "Student",
        title5: "Class",
        Building,
        Department,
        Room,
        Student,
        Class,
    })
})

module.exports = router;

update.js

import express from 'express';
import { selectSql, updateSql } from '../database/sql';

const router = express.Router();

router.get('/student', async (_req, res) => {
    const student_res = await selectSql.getStudent();
    console.log(student_res)
    res.render('updateStudent', {
        main_title: "UPDATE 'Student' table",
        student_res,
    });
});
router.post('/student', async (req, res) => {
    const vars = req.body;
    const data = {
        Id: vars.id,
        Name: vars.name,
        Email: vars.email,
        PhoneNumber: vars.phonenumber,
        Major: vars.major,
    }
    await updateSql.updateStudent(data);

    res.redirect('/update/student');
})


router.get('/department', async (_req, res) => {
    const department_res = await selectSql.getDepartment();
    console.log(department_res);
    res.render('updateDepartment', {
        main_title: "UPDATE 'Department' table",
        department_res,
    });
});

router.post('/department', async (req, res) => {
    const vars = req.body;
    const data = {
        Id: vars.id,
        Name: vars.name,
        Email: vars.email,
        PhoneNumber: vars.phone_num,
    }
    await updateSql.updateDepartment(data); // 이 함수는 추가로 구현해야 합니다.

    res.redirect('/update/department');
});


module.exports = router;

index.js

import express from 'express';
import logger from 'morgan';
import path from 'path';

import homeRouter from '../routes/home';
import updateRouter from '../routes/update';
import selectRouter from '../routes/select';

const PORT = 3000;

const app = express(); 

app.use(express.static(path.join(__dirname, '/src')));
app.use(express.urlencoded({ extended: false })) 
app.use(express.json()); 

app.set('views', path.join(__dirname, '../views')); 
app.set('view engine', 'hbs');

app.use(logger('dev'));

app.use('/', homeRouter);
app.use('/update', updateRouter);
app.use('/select', selectRouter);

app.listen(PORT, () => {
    console.log(`Server is running at http://localhost:${PORT}`)
});

home.hbs

<h1>Insert Student</h1>
<form name="student" method="post" action="/">
    <div>
        <label for="id">Id</label>
        <input id="id" name="id" type="text" required placeholder="Id" />
    </div>
    <div>
        <label for="name">Name</label>
        <input id="name" name="name" type="text" required placeholder="Name" />
    </div>
    <div>
        <label for="email">E-mail</label>
        <input id="email" name="email" type="text" placeholder="email" />
    </div>
    <div>
        <label for="phoneNumber">Phone Number</label>
        <input id="phoneNumber" name="phoneNumber" type="text" 
        required placeholder="000-0000-0000" />
    </div>
    <div>
        <label for="major">Major</label>
        <input id="major" name="major" type="text" required placeholder="major" />
    </div>
    <div>
        <input type="submit" value="insert" />
    </div>
</form>

layout.hbs

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>{{title}}</title>
    <style type="text/css">
        table {
                border-collapse: collapse;
            }

        th, td {
            border: 1px solid black;
            width: 100px;
            height: 20px;
        }
    </style>
</head>
<body>
    {{{body}}}
</body>
</html>

select.hbs

<h1>{{main_title}}</h1>

<!--Building-->
<h2>{{title1}}</h2>

<table>
    <tr>
        <td>ID</td>
        <td>Name</td>
    </tr>
    {{#each Building}}
    <tr>
        <td>{{ID}}</td>
        <td>{{name}}</td>
    </tr>
    {{/each}}
</table>

<!--Department-->
<h2>{{title2}}</h2>

<table>
    <tr>
        <td>ID</td>
        <td>Name</td>
        <td>Email</td>
        <td>Phone number</td>
    </tr>
    {{#each Department}}
    <tr>
        <td>{{ID}}</td>
        <td>{{name}}</td>
        <td>{{email}}</td>
        <td>{{phone_num}}</td>
    </tr>
    {{/each}}
</table>

<!--Room-->
<h2>{{title3}}</h2>

<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Capacity</td>
        <td>Building Id</td>
    </tr>
    {{#each Room}}
    <tr>
        <td>{{ID}}</td>
        <td>{{name}}</td>
        <td>{{capacity}}</td>
        <td>{{building_ID}}</td>
    </tr>
    {{/each}}
</table>

<!--Student-->
<h2>{{title4}}</h2>

<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Email</td>
        <td>Phone number</td>
        <td>Major</td>
    </tr>
    {{#each Student}}
    <tr>
        <td>{{ID}}</td>
        <td>{{name}}</td>
        <td>{{email}}</td>
        <td>{{phone_num}}</td>
        <td>{{major}}</td>
    </tr>
    {{/each}}
</table>

<!--Class-->
<h2>{{title5}}</h2>

<table>
    <tr>
        <td>ID</td>
        <td>Name</td>
        <td>Professor</td>
        <td>Number of Participants</td>
        <td>Department ID</td>
    </tr>
    {{#each Class}}
    <tr>
        <td>{{ID}}</td>
        <td>{{name}}</td>
        <td>{{professor}}</td>
        <td>{{num_par}}</td>
        <td>{{department_ID}}</td>
    </tr>
    {{/each}}
</table>

updateDeparment.hbs

<h1>{{main_title}}</h1>
<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Email</td>
        <td>Phone_number</td>
    </tr>
    {{#each department_res}}
    <form method="post">
        <tr>
            <td>{{ID}}</td>
            <td style="display: none"><input type="text" name="id" value={{ID}}></td> 
            <td><input type="text" name="name" value="{{name}}"></td> 
            <td><input type="text" name="email" value={{email}}></td> 
            <td><input type="text" name="phonenumber" value={{phone_num}}></td> 
            <td><input type="submit" value="Modify" formaction="/update/department"></td>
        </tr>
    </form>
    {{/each}}
</table>

updateStudent.hbs

<h1>{{main_title}}</h1>
<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Email</td>
        <td>Phone_number</td>
        <td>Major</td>
    </tr>
    {{#each student_res}}
    <form method="post">
        <tr>
            <td>{{ID}}</td>
            <td style="display: none"><input type="text" name="id" value={{ID}}></td> 
            <td><input type="text" name="name" value="{{name}}"></td> 
            <td><input type="text" name="email" value={{email}}></td> 
            <td><input type="text" name="phonenumber" value={{phone_num}}></td> 
            <td><input type="text" name="major" value="{{major}}"></td>
            <td><input type="submit" value="Modify" formaction="/update/student"></td>
        </tr>
    </form>
    {{/each}}
</table>

결과

 

728x90

'Quality control (Univ. Study) > Database Design' 카테고리의 다른 글

SQL기초  (1) 2023.10.20
Relational Data Model  (0) 2023.10.20
Univ. DB upgrade  (0) 2023.10.05
EER  (0) 2023.09.26
MySQL의 ER diagram  (0) 2023.09.26

관련글

티스토리툴바