Daily Codig Reminder
selectOne/delete/template
char1ie
2024. 3. 28. 17:53
const express = require("express");
const app = express();
//npm install body-parser
app.use(express.urlencoded({ extended: true })); //////////////////post방식 데이터 처리
//서버 listen
const server = app.listen(3000, () => {
console.log("Start Server: localhost:3000");
});
//__dirname: 현재 디렉토리
//page경로 설정
app.set("views", __dirname + "/views");
app.set("view engine", "ejs");
app.engine("html", require("ejs").renderFile);
//database
//npm install oracledb --save
const oracledb = require("oracledb");
oracledb.initOracleClient();
//참고 https://node-oracledb.readthedocs.io/en/latest/user_guide/initialization.html
const dbconfig = require("./dbconfig.js");
app.get("/", function (req, res) {
res.render("index2.html"); //////////////////////
});
//////////// insertForm, insert/////////////////
app.get("/insertForm", function (req, res) {
res.render("insertForm.html");
});
app.get("/insert", function (req, res) {
getInsertDeptGet(req, res);
});
app.post("/insert", function (req, res) {
getInsertDeptPost(req, res);
});
/////////////////////////////////////////
/////////// selectOne //////////////////
app.get("/selectOne", function (req, res) {
getSelectOne(req, res);
});
//////////////////////////////////////////
app.get("/select", function (req, res) {
/////select의 수정
getSelection(req, res);
});
app.get("/selectById", function (req, res) {
getSelectById(req, res);
});
app.get("/delete/:deptno", function(req,res){
getDelete(req,res);
})
//////////////////////////////
var template = require("./lib/template.js");
///////////////////
////////////// select ////////////////////
async function getSelection(req, res) {
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept order by deptno asc"
);
console.log(
"================",
(await result).rows.length,
"==============="
);
// [
// [ 10, 'ACCOUNTING', 'NEW YORK' ],
// [ 20, 'RESEARCH', 'DALLAS' ],
// [ 30, 'SALES', 'CHICAGO' ],
// [ 40, 'OPERATIONS', 'BOSTON' ],
// [ 99, '개발', '제주' ]
// ]
var allData = (await result).rows;
var table= template.table(allData);
console.log(table);
// var html = "<html><body>";
// html += "<a href='/insertForm'>부서추가화면</a>"; ///////////////
// html += "<table border='1'>";
// html += "<tr><td>부서번호</td><td>부서명</td><td>지역</td><td>삭제</td></tr>";
// for (let index = 0; index < allData.length; index++) {
// html += "<tr>";
// html +=
// "<td><a href='/selectOne?deptno=" +
// allData[index][0] +
// "'>" +
// allData[index][0] +
// "</a></td>";
// html += "<td>" + allData[index][1] + "</td>";
// html += "<td>" + allData[index][2] + "</td>";
// html +=
// "<td><a href='/delete/" + allData[index][0] + "'>삭제</a></td>";
// html += "</tr>";
// }
// /////////삭제추가부분
// html += "</table></body></html>";
// res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////getDelete////////////////////
async function getDelete(req, res) {
// OracleDB 자동 커밋 활성화
oracledb.autoCommit = true;
// URL에서 부서번호 추출
let deptno = req.params.deptno;
console.log("path variable data: ", deptno);
let connection;
try {
// OracleDB 연결
connection = await oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
});
// 부서 삭제 쿼리 실행
const result = await connection.execute(
"DELETE FROM dept WHERE deptno = :deptno",
[deptno]
);
console.log("Delete Success");
var html = `<html><body>
<a href='/select'>목록보기로 </a>
</body></html>`;
res.send(html);
} catch (error) {
console.error("Delete Error:", error);
res.status(500).send("Delete Error");
} finally {
if (connection) {
try {
await connection.close();
} catch (error) {
console.error("Connection Close Error:", error);
}
}
}
}
///////////// getInsertDept Post방식////////////////
async function getInsertDeptPost(req, res) {
oracledb.autoCommit = true; ///////////
let item = req.body;
let deptno = item.deptno;
let dname = item.dname;
let loc = item.loc;
console.log("post data: ", deptno, dname, loc);
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"insert into dept values (:deptno, :dname, :loc)",
[deptno, dname, loc],
function (error, data) {
console.log("실행됨");
if (error) {
console.log(error);
}
console.log(result);
}
);
var html = `<html><body>
<a href='/select'>목록보기로 </a>
</body></html>`;
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////////////////////////////////
///////////// getInsertDept get방식////////////////
async function getInsertDeptGet(req, res) {
oracledb.autoCommit = true; ///////////
// let item = req.body;
// let deptno = item.deptno;
// let dname = item.dname;
// let loc = item.loc;
// let item = req.body;
let deptno = req.param("deptno");
let dname = req.param("dname");
let loc = req.param("loc");
console.log("get data: ", deptno, dname, loc);
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"insert into dept values (:deptno, :dname, :loc)",
[deptno, dname, loc],
function (error, data) {
console.log("실행됨");
if (error) {
console.log(error);
}
console.log(result);
}
);
var html = `<html><body>
<a href='/select'>목록보기로 </a>
</body></html>`;
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////////////////////////////////
///////////// seletOne ////////////////
async function getSelectOne(req, res) {
let connection;
let deptno = req.param("deptno"); //부서번호
console.log("parameter deptno: ", deptno);
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept where deptno=:deptno",
[deptno]
);
console.log(
"================",
(await result).rows.length,
"==============="
);
var allData = (await result).rows;
var html = "<html><body><table border='1'>";
html += "<tr><td>부서번호</td><td>부서명</td><td>지역</td></tr>";
for (let index = 0; index < allData.length; index++) {
html += "<tr>";
html +=
"<td><a href='/selectOne?deptno=" +
allData[index][0] +
"'>" +
allData[index][0] +
"</a></td>";
html += "<td>" + allData[index][1] + "</td>";
html += "<td>" + allData[index][2] + "</td>";
html += "</tr>";
}
html += "</table></body></html>";
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
///////////////////////////////////////////////////////
async function getSelectById(req, res) {
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept where deptno=:num",
[10]
);
console.log(result);
res.send((await result).rows);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
///////////////////////////////////////////////////////
async function getInsert(req, res) {
/////////////////////////////////
oracledb.autoCommit = true;
////////////////////////////////
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"insert into dept (deptno, dname, loc) values (:deptno, :dname, :loc)",
[99, "개발", "제주"],
function (error, data) {
console.log("실행됨");
if (error) {
console.log("error", error);
}
console.log(data);
// res.send("추가완료");
}
);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
res.send("추가완료"); //////////////////////////
}selectOne
app.get("/selectOne", function(req,res){
getSelectOne(req, res);
});
/////////////////////////////
async function getSelectOne(req, res) {
let connection;
try {
connection = await oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectionString: dbconfig.xid
});
// 요청 파라미터에서 부서 번호 추출
let deptno = req.param("deptno");
// 지정된 부서 번호로 부서의 세부 정보를 가져오기 위해 쿼리 실행
const result = await connection.execute("SELECT * FROM dept WHERE deptno = :deptNo", [deptno]);
var allData = (await result).rows;
// 부서가 존재하는지 확인
if (allData.length > 0) {
// HTML 테이블 헤더 구성
let htmlTable = "<table border='1'>";
htmlTable += "<tr>";
// 각 열의 첫 번째 값을 사용하여 테이블 헤더에 추가
for (let i = 0; i < allData[0].length; i++) {
htmlTable += "<th>Column " + (i+1) + "</th>";
}
htmlTable += "</tr>";
// 모든 부서 데이터에 대해 반복하여 테이블 행을 추가
for (let i = 0; i < allData.length; i++) {
htmlTable += "<tr>";
for (let j = 0; j < allData[i].length; j++) {
htmlTable += "<td>" + allData[i][j] + "</td>";
}
htmlTable += "</tr>";
}
htmlTable += "</table>";
res.send(htmlTable);
} else {
res.status(404).send("부서를 찾을 수 없습니다.");
}
} catch (error) {
console.error("부서 정보 조회 중 오류 발생:", error);
res.status(500).send("오류가 발생했습니다.");
} finally {
if (connection) {
try {
await connection.close();
} catch (error) {
console.error("연결 닫기 중 오류 발생:", error);
}
}
}
}
exam3_dbtest4_html3_insertForm 다시
const express = require("express");
const app = express();
//npm install body-parser
app.use(express.urlencoded({ extended: true })); //////////////////post방식 데이터 처리
//서버 listen
const server = app.listen(3001, () => {
console.log("Start Server: localhost:3001");
});
//__dirname: 현재 디렉토리
//page경로 설정
app.set("views", __dirname + "/views");
app.set("view engine", "ejs");
app.engine("html", require("ejs").renderFile);
//database
//npm install oracledb --save
const oracledb = require("oracledb");
oracledb.initOracleClient();
//참고 https://node-oracledb.readthedocs.io/en/latest/user_guide/initialization.html
const dbconfig = require("./dbconfig.js");
app.get("/", function (req, res) {
res.render("index2.html"); //////////////////////
});
//////////// insertForm, insert/////////////////
app.get("/insertForm", function (req, res) {
res.render("insertForm.html");
});
app.get("/insert", function (req, res) {
getInsertDeptGet(req, res);
});
app.post("/insert", function (req, res) {
getInsertDeptPost(req, res);
});
/////////////////////////////////////////
/////////// selectOne //////////////////
app.get("/selectOne", function (req, res) {
getSelectOne(req, res);
});
//////////////////////////////////////////
app.get("/select", function (req, res) {
/////select의 수정
getSelection(req, res);
});
app.get("/selectById", function (req, res) {
getSelectById(req, res);
});
///////////// getInsertDept Post방식////////////////
async function getInsertDeptPost(req, res) {
oracledb.autoCommit = true; ///////////
let item = req.body;
let deptno = item.deptno;
let dname = item.dname;
let loc = item.loc;
console.log("post data: ", deptno, dname, loc);
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"insert into dept values (:deptno, :dname, :loc)",
[deptno, dname, loc],
function (error, data) {
console.log("실행됨");
if (error) {
console.log(error);
}
console.log(result);
}
);
var html = `<html><body>
<a href='/select'>목록보기로 </a>
</body></html>`;
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////////////////////////////////
///////////// getInsertDept get방식////////////////
async function getInsertDeptGet(req, res) {
oracledb.autoCommit = true; ///////////
// let item = req.body;
// let deptno = item.deptno;
// let dname = item.dname;
// let loc = item.loc;
// let item = req.body;
let deptno = req.param("deptno");
let dname = req.param("dname");
let loc = req.param("loc");
console.log("get data: ", deptno, dname, loc);
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"insert into dept values (:deptno, :dname, :loc)",
[deptno, dname, loc],
function (error, data) {
console.log("실행됨");
if (error) {
console.log(error);
}
console.log(result);
}
);
var html = `<html><body>
<a href='/select'>목록보기로 </a>
</body></html>`;
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////////////////////////////////
///////////// seletOne ////////////////
async function getSelectOne(req, res) {
let connection;
let deptno = req.param("deptno"); //부서번호
console.log("parameter deptno: ", deptno);
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept where deptno=:deptno",
[deptno]
);
console.log(
"================",
(await result).rows.length,
"==============="
);
var allData = (await result).rows;
var html = "<html><body><table border='1'>";
html += "<tr><td>부서번호</td><td>부서명</td><td>지역</td></tr>";
for (let index = 0; index < allData.length; index++) {
html += "<tr>";
html +=
"<td><a href='/selectOne?deptno=" +
allData[index][0] +
"'>" +
allData[index][0] +
"</a></td>";
html += "<td>" + allData[index][1] + "</td>";
html += "<td>" + allData[index][2] + "</td>";
html += "</tr>";
}
html += "</table></body></html>";
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////////////////////////////////
////////////// select ////////////////////
async function getSelection(req, res) {
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept order by deptno asc"
);
console.log(
"================",
(await result).rows.length,
"==============="
);
// [
// [ 10, 'ACCOUNTING', 'NEW YORK' ],
// [ 20, 'RESEARCH', 'DALLAS' ],
// [ 30, 'SALES', 'CHICAGO' ],
// [ 40, 'OPERATIONS', 'BOSTON' ],
// [ 99, '개발', '제주' ]
// ]
var allData = (await result).rows;
var html = "<html><body>";
html += "<a href='/insertForm'>부서추가화면</a>"; ///////////////
html += "<table border='1'>";
html += "<tr><td>부서번호</td><td>부서명</td><td>지역</td></tr>";
for (let index = 0; index < allData.length; index++) {
html += "<tr>";
html +=
"<td><a href='/selectOne?deptno=" +
allData[index][0] +
"'>" +
allData[index][0] +
"</a></td>";
html += "<td>" + allData[index][1] + "</td>";
html += "<td>" + allData[index][2] + "</td>";
html += "</tr>";
}
html += "</table></body></html>";
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////////////////////////////////////
///////////////////////////////////////////////////////
async function getSelectById(req, res) {
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept where deptno=:num",
[10]
);
console.log(result);
res.send((await result).rows);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
///////////////////////////////////////////////////////
async function getInsert(req, res) {
/////////////////////////////////
oracledb.autoCommit = true;
////////////////////////////////
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"insert into dept (deptno, dname, loc) values (:deptno, :dname, :loc)",
[99, "개발", "제주"],
function (error, data) {
console.log("실행됨");
if (error) {
console.log("error", error);
}
console.log(data);
// res.send("추가완료");
}
);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
res.send("추가완료"); //////////////////////////
}
npm install body-parser
delete
app.get("/delete/:deptno", function(req,res){
getDelete(req,res);
})
///////////////////
////////////getDelete////////////////////
async function getDelete(req, res) {
// OracleDB 자동 커밋 활성화
oracledb.autoCommit = true;
// URL에서 부서번호 추출
let deptno = req.params.deptno;
console.log("path variable data: ", deptno);
let connection;
try {
// OracleDB 연결
connection = await oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
});
// 부서 삭제 쿼리 실행
const result = await connection.execute(
"DELETE FROM dept WHERE deptno = :deptno",
[deptno]
);
console.log("Delete Success");
var html = `<html><body>
<a href='/select'>목록보기로 </a>
</body></html>`;
res.send(html);
} catch (error) {
console.error("Delete Error:", error);
res.status(500).send("Delete Error");
} finally {
if (connection) {
try {
await connection.close();
} catch (error) {
console.error("Connection Close Error:", error);
}
}
}
}
template
const express = require("express");
const app = express();
//npm install body-parser
app.use(express.urlencoded({ extended: true })); //////////////////post방식 데이터 처리
//서버 listen
const server = app.listen(3000, () => {
console.log("Start Server: localhost:3000");
});
//__dirname: 현재 디렉토리
//page경로 설정
app.set("views", __dirname + "/views");
app.set("view engine", "ejs");
app.engine("html", require("ejs").renderFile);
//database
//npm install oracledb --save
const oracledb = require("oracledb");
oracledb.initOracleClient();
//참고 https://node-oracledb.readthedocs.io/en/latest/user_guide/initialization.html
const dbconfig = require("./dbconfig.js");
app.get("/", function (req, res) {
res.render("index2.html"); //////////////////////
});
//////////// insertForm, insert/////////////////
app.get("/insertForm", function (req, res) {
res.render("insertForm.html");
});
app.get("/insert", function (req, res) {
getInsertDeptGet(req, res);
});
app.post("/insert", function (req, res) {
getInsertDeptPost(req, res);
});
/////////////////////////////////////////
/////////// selectOne //////////////////
app.get("/selectOne", function (req, res) {
getSelectOne(req, res);
});
//////////////////////////////////////////
app.get("/select", function (req, res) {
/////select의 수정
getSelection(req, res);
});
app.get("/selectById", function (req, res) {
getSelectById(req, res);
});
app.get("/delete/:deptno", function(req,res){
getDelete(req,res);
})
//////////////////////////////
var template = require("./lib/template.js");
////////////// select ////////////////////
async function getSelection(req, res) {
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept order by deptno asc"
);
console.log(
"================",
(await result).rows.length,
"==============="
);
var allData = (await result).rows;
var tableData = {
metaData: [
{ name: "부서번호" },
{ name: "부서명" },
{ name: "지역" }
],
rows: allData
};
var tableHTML = template.table("부서 목록", tableData);
res.send(tableHTML);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
await connection.close();
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////getDelete////////////////////
async function getDelete(req, res) {
// OracleDB 자동 커밋 활성화
oracledb.autoCommit = true;
// URL에서 부서번호 추출
let deptno = req.params.deptno;
console.log("path variable data: ", deptno);
let connection;
try {
// OracleDB 연결
connection = await oracledb.getConnection({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.xid
});
// 부서 삭제 쿼리 실행
const result = await connection.execute(
"DELETE FROM dept WHERE deptno = :deptno",
[deptno]
);
console.log("Delete Success");
var html = `<html><body>
<a href='/select'>목록보기로 </a>
</body></html>`;
res.send(html);
} catch (error) {
console.error("Delete Error:", error);
res.status(500).send("Delete Error");
} finally {
if (connection) {
try {
await connection.close();
} catch (error) {
console.error("Connection Close Error:", error);
}
}
}
}
///////////// getInsertDept Post방식////////////////
async function getInsertDeptPost(req, res) {
oracledb.autoCommit = true; ///////////
let item = req.body;
let deptno = item.deptno;
let dname = item.dname;
let loc = item.loc;
console.log("post data: ", deptno, dname, loc);
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"insert into dept values (:deptno, :dname, :loc)",
[deptno, dname, loc],
function (error, data) {
console.log("실행됨");
if (error) {
console.log(error);
}
console.log(result);
}
);
var html = `<html><body>
<a href='/select'>목록보기로 </a>
</body></html>`;
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////////////////////////////////
///////////// getInsertDept get방식////////////////
async function getInsertDeptGet(req, res) {
oracledb.autoCommit = true; ///////////
// let item = req.body;
// let deptno = item.deptno;
// let dname = item.dname;
// let loc = item.loc;
// let item = req.body;
let deptno = req.param("deptno");
let dname = req.param("dname");
let loc = req.param("loc");
console.log("get data: ", deptno, dname, loc);
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"insert into dept values (:deptno, :dname, :loc)",
[deptno, dname, loc],
function (error, data) {
console.log("실행됨");
if (error) {
console.log(error);
}
console.log(result);
}
);
var html = `<html><body>
<a href='/select'>목록보기로 </a>
</body></html>`;
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
////////////////////////////////////////
///////////// seletOne ////////////////
async function getSelectOne(req, res) {
let connection;
let deptno = req.param("deptno"); //부서번호
console.log("parameter deptno: ", deptno);
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept where deptno=:deptno",
[deptno]
);
console.log(
"================",
(await result).rows.length,
"==============="
);
var allData = (await result).rows;
var html = "<html><body><table border='1'>";
html += "<tr><td>부서번호</td><td>부서명</td><td>지역</td></tr>";
for (let index = 0; index < allData.length; index++) {
html += "<tr>";
html +=
"<td><a href='/selectOne?deptno=" +
allData[index][0] +
"'>" +
allData[index][0] +
"</a></td>";
html += "<td>" + allData[index][1] + "</td>";
html += "<td>" + allData[index][2] + "</td>";
html += "</tr>";
}
html += "</table></body></html>";
res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
///////////////////////////////////////////////////////
async function getSelectById(req, res) {
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept where deptno=:num",
[10]
);
console.log(result);
res.send((await result).rows);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
///////////////////////////////////////////////////////
async function getInsert(req, res) {
/////////////////////////////////
oracledb.autoCommit = true;
////////////////////////////////
let connection;
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"insert into dept (deptno, dname, loc) values (:deptno, :dname, :loc)",
[99, "개발", "제주"],
function (error, data) {
console.log("실행됨");
if (error) {
console.log("error", error);
}
console.log(data);
// res.send("추가완료");
}
);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
res.send("추가완료"); //////////////////////////
}
template
module.exports = {
table: function(title, data) {
var html = `
<html>
<head>
<title>WEB1 - ${title}</title>
<style>
table {
border-collapse: collapse;
width: 100%;
}
th, td {
border: 1px solid black;
padding: 8px;
text-align: left;
}
tr:nth-child(even) {
background-color: #f2f2f2;
}
th {
background-color: #4CAF50;
color: white;
}
</style>
</head>
<body>
<table>
<tr>`;
// Add table header
for (var i = 0; i < data.metaData.length; i++) {
html += `<th>${data.metaData[i].name}</th>`;
}
html += "<th>삭제</th></tr>"; // 추가: 삭제 컬럼 헤더
// Add table rows
for (var j = 0; j < data.rows.length; j++) {
html += "<tr>";
for (var k = 0; k < data.rows[j].length; k++) {
if (k === 0) {
// 부서번호에 링크 추가
html += `<td><a href='/selectOne?deptno=${data.rows[j][k]}'>${data.rows[j][k]}</a></td>`;
} else {
html += `<td>${data.rows[j][k]}</td>`;
}
}
// 추가: 삭제 링크
html += `<td><a href='/delete/${data.rows[j][0]}'>삭제</a></td>`;
html += "</tr>";
}
html += `
</table>
<a href='/insertForm'>부서 추가하기로 이동</a><hr> <!-- 추가: 부서 추가 링크 -->
</body>
</html>`;
return html;
}
};
///////////// seletOne ////////////////
async function getSelectOne(req, res) {
let connection;
let deptno = req.param("deptno"); //부서번호
console.log("parameter deptno: ", deptno);
try {
connection = oracledb.getConnection({
user: "scott",
password: "tiger",
connectString: "xe",
// user: dbconfig.user,
// password: dbconfig.password,
// connectString: dbconfig.xid,
});
const result = (await connection).execute(
"select * from dept where deptno=:deptno",
[deptno]
);
console.log(
"================",
(await result).rows.length,
"==============="
);
var allData = (await result).rows;
var tableData = {
metaData: [
{ name: "부서번호" },
{ name: "부서명" },
{ name: "지역" }
],
rows: allData
};
var tableHTML = template.table("부서 목록", tableData);
res.send(tableHTML);
// var html = "<html><body><table border='1'>";
// html += "<h1>"+deptno+"번 부서 정보</h1>";
// for (let index = 0; index < allData.length; index++) {
// html += "<tr>";
// html +=
// "<td><a href='/selectOne?deptno=" +
// allData[index][0] +
// "'>" +
// allData[index][0] +
// "</a></td>";
// html += "<td>" + allData[index][1] + "</td>";
// html += "<td>" + allData[index][2] + "</td>";
// html += "</tr>";
// }
// html += "</table></body></html>";
// res.send(html);
} catch (error) {
console.log(error);
} finally {
if (connection) {
try {
(await connection).close;
} catch (error) {
console.log("error:", error);
}
}
}
}
