728x90
template.js
module.exports={
HTML: function(title, body, control){
return `
<!doctype html>
<html>
<head>
<title>타이틀</title>
<meta charset="utf-8">
</head>
<body>
<h1>${title}</h1>
${body}
${control}
</body>
</html>
`;
},
table: function(topics){
var list = "<table border=1>";
var i = 0;
while(i < topics.length){
list = list + `<tr>
<td><a href="/selectOne?deptno=${topics[i][0]}">${topics[i][0]}</a></td>
<td>${topics[i][1]}</td>
<td>${topics[i][2]}</td>
<td><a href="/delete/${topics[i][0]}">삭제</a></td>
<td><a href="/updateForm?deptno=${topics[i][0]}">수정</a></td>
</tr>`;
i=i+1;
}
list += "</table>"
return list;
},
Form: function(topics, url, metaData){
var form = `<form action=${url} method="post"><br>`;
var i = 0;
console.log("topics: ", topics);
while(i< topics.length){
for (let j = 0; j < topics[i].length; j++) {
form += `${metaData[j].name}<input type="text" value="${topics[i][j]}" name="${metaData[j].name}"><br>`;
}
i += 1;
}
form += `<input type="submit" value = "수정"></form>`;
return form;
},
list: function(topics){
var list = "<ul>";
var i = 0;
while(i<topics.length){
list += `<li><a href="selectone?deptno=${topics[i].deptno}">${topics[i].deptno}</a></li>`;
i=i+1;
}
list += "</ul>";
return list;
},
};
const express = require("express");
const app = express();
const server = app.listen(3010, () => {
console.log("Start Server: localhost: 3010");
})
app.use(express.urlencoded({extended: true}));
app.set("views", __dirname + "/views");
app.set("view engine", "ejs");
app.engine("html", require("ejs").renderFile);
app.get("/", function(req, res){
res.render("index2.html");
})
app.get("/insertForm", function(req, res){
res.render("insertForm.html");
})
const oracledb = require("oracledb");
oracledb.initOracleClient();
const dbconfig = require("./dbconfig.js");
//*************************************
app.get("/select", function(req, res){
getSelection(req, res);
})
//*************************************
app.get("/table", function(req, res){
getTable(req, res);
})
app.get("/selectOne", function(req,res){
getSelectOne(req, res);
})
app.get("/insert", function(req,res){
getInsertDeptGet(req, res);
})
app.post("/insert", function(req,res){
getInsertDeptPost(req, res);
})
app.get("/delete/:deptno", function(req,res){
getDelete(req, res);
})
//*************************************
app.post("/update", function(req,res){
getUpdate(req, res);
})
app.get("/updateForm", function(req,res){
getUpdateForm(req, res);
})
//*************************************
//********************************************************************************************************************
var template = require("./lib/templete.js"); //template import
async function getSelection(req, res){
try {
oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
}, async function(err, connection){
if (err) {
console.error(err.message);
return;
}
try {
const result = await connection.execute("SELECT * FROM dept");
var allData = result.rows;
var table = template.table(allData);
var control = `<a href="/insertForm">부서추가하기로 이동</a><hr>`;
var html = template.HTML("template이용", table, control);
res.send(html);
} catch (error) {
console.log(error);
} finally {
if(connection){
try {
await connection.close();
} catch(error){
console.log("error: ", error);
}
}
}
});
} catch (error) {
console.log(error);
}
}
//********************************************************************************************************************
async function getTable(req, res){
let connection;
try {
connection = oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
})
const result = (await connection).execute("select * from dept order by deptno",
function(error, data){
if(error){
console.log(error);
}
var headName = "";
for (let index = 0; index < data.metaData.length; index++) {
headName += "<th>"+data.metaData[index].name+"</th>"
}
var dataSet = "";
for (let index = 0; index < data.rows.length; index++) {
var dataes = "";
dataes += "<td><a href='/selectOne?deptno="+data.rows[index][0]+"'>"
+data.rows[index][0]+"</a></td>"
+ "<td>"+data.rows[index][1]+"</td>"
+ "<td>"+data.rows[index][2]+"</td>"
+ "<td><a href='/delete/"+data.rows[index][0]+"'>삭제</a></td>"
dataSet += "<tr>"+dataes+"</tr>";
}
var resultData = "<html><head><body><table border='1'>"
+"<tr>"+headName+"</tr>"
+dataSet
+"</table></body></head></html>"
res.send(resultData);
});
} catch (error) {
console.log(error);
} finally {
if(connection){
try{
(await connection).close
} catch(error){
console.log("error: ", error);
}
}
}
}
async function getSelectOne(req, res){
let connection;
let deptno = req.param("deptno");
try {
connection = oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
})
const result = (await connection).execute("select * from dept where deptno=:num order by deptno", [deptno],
function(error, data){
if(error){
console.log(error);
}
var headName = "";
for (let index = 0; index < data.metaData.length; index++) {
headName += "<th>"+data.metaData[index].name+"</th>"
}
var dataSet = "";
for (let index = 0; index < data.rows.length; index++) {
var dataes = "";
dataes += "<td><a href='/selectOne?deptno="+data.rows[index][0]+"'>"
+data.rows[index][0]+"</a></td>"
+ "<td>"+data.rows[index][1]+"</td>"
+ "<td>"+data.rows[index][2]+"</td>"
+ "<td><a href='/delete/"+data.rows[index][0]+"'>삭제</a></td>"
dataSet += "<tr>"+dataes+"</tr>";
}
var resultData = "<html><head><body><h1>"+deptno+"번 부서보기</h1><table border='1'>"
+"<tr>"+headName+"</tr>"
+dataSet
+"</table></body></head></html>"
res.send(resultData);
}
);
} catch (error) {
console.log(error);
} finally {
if(connection){
try{
(await connection).close
} catch(error){
console.log("error: ", error);
}
}
}
}
async function getInsertDeptGet(req, res){
oracledb.autoCommit = true;
let deptno = req.param("deptno");
let dname = req.param("dname");
let loc = req.param("loc");
console.log(deptno, dname, loc)
try {
connection = oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
})
const result = (await connection).execute("insert into dept(deptno, dname, loc) values (:deptno, :dname, :loc)", [deptno, dname, loc],
function(error, result){
console.log("실행됨");
if(error){
console.log(error);
res.send("중복된 부서입니다");
}
console.log(result);
if(result.rowsAffected == 1){
res.send("<a href='/table'>목록 보기</a>");
}
}
);
} catch (error) {
console.log(error);
} finally {
if(connection){
try{
(await connection).close
} catch(error){
console.log("error: ", error);
}
}
}
}
async function getInsertDeptPost(req, res){
oracledb.autoCommit = true;
let deptno = req.param("deptno");
let dname = req.param("dname");
let loc = req.param("loc");
console.log(deptno, dname, loc)
try {
connection = oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
})
const result = (await connection).execute("insert into dept(deptno, dname, loc) values (:deptno, :dname, :loc)", [deptno, dname, loc],
function(error, result){
console.log("실행됨");
if(error){
console.log(error);
res.send("중복된 부서입니다");
}
console.log(result);
if(result.rowsAffected == 1){
res.send("<a href='/table'>목록 보기</a>");
}
}
);
} catch (error) {
console.log(error);
} finally {
if(connection){
try{
(await connection).close
} catch(error){
console.log("error: ", error);
}
}
}
}
async function getDelete(req, res){
oracledb.autoCommit = true;
let deptno = req.params.deptno;
console.log("pathVariable data: ", deptno)
try {
connection = oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
})
const result = (await connection).execute("delete from dept where deptno = :num", [deptno],
function(error, result){
console.log("실행됨");
if(error){
console.log(error);
res.send("삭제 실패");
}
console.log(result);
if(result.rowsAffected == 1){
res.send("<a href='/select'>목록 보기</a>");
}
}
);
} catch (error) {
console.log(error);
} finally {
if(connection){
try{
(await connection).close
} catch(error){
console.log("error: ", error);
}
}
}
}
//********************************************************************************************************************
async function getUpdate(req, res){
oracledb.autoCommit = true;
const { DEPTNO, DNAME, LOC } = req.body;
console.log("update deptno: ", DEPTNO, DNAME, LOC);
let connection
try {
connection = oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
})
const result = (await connection).execute("update dept set dname=:dname, loc=:loc where deptno=:deptno", [DNAME, LOC, DEPTNO]);
var html = template.HTML("수정완료 template", `<a href="/select">목록으로</a>`, "");
res.send(html);
} catch (error) {
console.log(error);
} finally {
if(connection){
try{
(await connection).close
} catch(error){
console.log("error: ", error);
}
}
}
}
async function getUpdateForm(req, res){
oracledb.autoCommit = true;
const { deptno } = req.query;
console.log("updateForm deptno: ", deptno);
let connection
try {
connection = oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
})
const result = (await connection).execute("select deptno, dname, loc from dept where deptno=:deptno", [deptno]);
var allData = (await result).rows;
console.log("allData : >>.", allData)
var metaData= (await result).metaData;
console.log(result);
var body = template.Form(allData, "/update", metaData);
var html = template.HTML("update template", body, `<a href="/select">목록으로</a>`);
res.send(html);
} catch (error) {
console.log(error);
} finally {
if(connection){
try{
(await connection).close
} catch(error){
console.log("error: ", error);
}
}
}
}
//******************************************************************************************************************** */
'단순 코드 기록 > Node' 카테고리의 다른 글
| Node_소켓통신에서 HTML, CSS, js파일 사용 (0) | 2024.03.27 |
|---|---|
| Node_소켓통신을 이용한 서버 가동 (0) | 2024.03.27 |
| Node_pathVariable_Delete (0) | 2024.03.26 |
| Node_Form을 이용한 Insert (0) | 2024.03.26 |
| Node_metaData로 컬럼 뽑기 / req.param으로 데이터 뽑기 (0) | 2024.03.26 |