table.sql create table product(
num number primary key,
name varchar2(30) not null,
price number(9) not null,
stock number(9) not null,
origin varchar2(30) not null,
reg_date date not null
);
create sequence product_seq;insertTestForm.jsp <%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>제품 글 등록</title>
<link rel="stylesheet" href="../css/style.css" type="text/css">
<script type="text/javascript" src="script.js"></script>
</head>
<body>
<div class="page-main">
<h2>글 쓰기</h2>
<form id="myForm" action="insertTest.jsp" method="post">
<ul>
<li>
<label for="name">이름</label>
<input type="text" name="name" id="name" size="20" maxlength="10">
</li>
<li>
<label for="price">가격</label>
<input type="number" name="price" id="price" size="20" maxlength="10">
</li>
<li>
<label for="stock">재고</label>
<input type="number" name="stock" id="stock" size="20" maxlength="10">
</li>
<li>
<label for="origin">원산지</label>
<input type="text" name="origin" id="origin" size="20" maxlength="10">
</li>
</ul>
<div class="align-center">
<input type="submit" value="전송">
<input type="button" value="목록" onclick="location.href='selectTest.jsp'">
</div>
</form>
</div>
</body>
</html>style.css (css폴더생성 후 하위 파일로 만듬) @charset "UTF-8";
body{
padding:0;
margin:0;
}
.page-main{
width:800px;
margin:0 auto; /* 중양 정렬 */
}
.result-display{
width:400px;
height:200px;
margin:50px;
margin:50px auto;
border:1px solid #000;
display:flex;
align-items:center; /* 세로 정렬 */
justify-content:center;/* 가로 정렬 */
}
.align-center{
text-align:center;
}
.align-right{
text-align:right;
}
/* 목록 */
table{
width:100%;
border:1px solid #000;
border-collapse: collapse;
margin-top:5px;
}
table td, table th{
border:1px solid #000;
padding:5px;
}
/* 등록,수정폼*/
form{
width:500px;
margin:0 auto;
border:1px solid #000;
padding:10px 10px 10px 30px;
}
ul{
list-style:none;
}
label{
width:100px;
float:left; /* 태그를 왼쪽으로 정렬 */
}script.js window.onload = function(){
const myForm = document.getElementById('myForm');
//이벤트 연결
myForm.onsubmit=function(){
const items = document.querySelectorAll(
'input[type="text"],input[type="number"]');
for(let i=0;i<items.length;i++){
if(items[i].value.trim()==''){
const label = document.querySelector(
'label[for="'+items[i].id+'"]');
alert(label.textContent+'항목은 필수 입력');
items[i].value='';
items[i].focus();
return false;
}
}
};
};
src/main/java => kr.util 패키지 안에 만들기 DBUtil.java
package kr.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtil {
private static final String DB_DRIVER = "oracle.jdbc.OracleDriver";
private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
private static final String DB_ID = "user01";
private static final String DB_PASSWORD = "1234";
//Connection 객체를 생성해서 반환
public static Connection getConnection() throws Exception{
//JDBC 수행 1단계 : 드라이버 로드
Class.forName(DB_DRIVER);
//JDBC 수행 2단계 : Connection 객체 생성
return DriverManager.getConnection(DB_URL,DB_ID,DB_PASSWORD);
}
//자원정리
public static void executeClose(ResultSet rs, PreparedStatement pstmt,
Connection conn) {
if(rs!=null)try {rs.close();}catch(SQLException e) {}
if(pstmt!=null)try {pstmt.close();}catch(SQLException e) {}
if(conn!=null)try {conn.close();}catch(SQLException e) {}
}
}insertTest.jsp <%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import ="kr.util.DBUtil" %>
<%@ page import = "java.sql.Connection" %>
<%@ page import = "java.sql.PreparedStatement" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>제품 글쓰기 처리</title>
<link rel="stylesheet" href="../css/style.css" type="text/css">
</head>
<body>
<%
//POST 방식
request.setCharacterEncoding("utf-8");
//전송된 데이터 반환
String name = request.getParameter("name");
int price = Integer.parseInt(request.getParameter("price"));
int stock = Integer.parseInt(request.getParameter("stock"));
String origin = request.getParameter("origin");
//DB연동
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
try{
//커넥션 객체 반환
conn = DBUtil.getConnection();
//sql문 실행
sql = "INSERT INTO PRODUCT (num,name,price,stock,origin,reg_date) VALUES (product_seq.nextval,?,?,?,?,SYSDATE)";
//JDBC 3단계
pstmt = conn.prepareStatement(sql);
//? 데이터 바인딩
pstmt.setString(1, name);
pstmt.setInt(2, price);
pstmt.setInt(3, stock);
pstmt.setString(4, origin);
//JDBC 4단계
pstmt.executeUpdate();
%>
<div class="result-display">
<div class = "align-center">
글 등록 성공!<br>
<input type = "button" value="목록"
onclick="location.href='selectTest.jsp'">
</div>
</div>
<%
}catch(Exception e){
%>
<div class="result-display">
<div class = "align-center">
오류 발생! 글 등록 실패!<p>
<input type = "button" value="글쓰기"
onclick="location.href='insertTestForm.jsp'">
</div>
</div>
<%
e.printStackTrace();
}finally{
DBUtil.executeClose(null, pstmt, conn);
}
%>
</body>
</html>selectTest.jsp <%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "kr.util.DBUtil" %>
<%@ page import = "java.sql.Connection" %>
<%@ page import = "java.sql.PreparedStatement" %>
<%@ page import = "java.sql.ResultSet" %>
<%@ page import = "java.sql.Date" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>제품 목록</title>
<link rel="stylesheet" href="../css/style.css" type="text/css">
</head>
<body class="page-main">
<h2>제품 목록</h2>
<div class="align-right">
<input type="button" value="글 쓰기" onclick="location.href='insertTestForm.jsp'">
</div>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
try{
//Connection 객체 반환
conn = DBUtil.getConnection();
//SQL문 작성
sql = "SELECT * FROM product ORDER BY num DESC";
//JDBC 수행 3단계: PrepardStatment 객체 생성
pstmt = conn.prepareStatement(sql);
//JDBC 수행 4단계: SQL문을 실행해서 테이블에 반영하고 결과행을 ResultSet에 담아서 반환
rs= pstmt.executeQuery();
%>
<table>
<tr>
<th>글번호</th>
<th>제품이름</th>
<th>가격</th>
<th>재고</th>
<th>작성일</th>
</tr>
<%
while(rs.next()){
int num = rs.getInt("num");
String name = rs.getString("name");
int price = rs.getInt("price");
int stock = rs.getInt("stock");
Date reg_date = rs.getDate("reg_date");
%>
<tr>
<td><%= num %></td>
<td><a href="detailTest.jsp?num=<%= num %>"><%= name %></a></td>
<td><%= price %></td>
<td><%= stock %></td>
<td><%= reg_date %></td>
</tr>
<%
}
%>
</table>
<%
}catch(Exception e){
%>
<div class="result-display">
<span>오류 발생!</span>
</div>
<%
e.printStackTrace();
}finally{
//자원 정리
DBUtil.executeClose(rs, pstmt, conn);
}
%>
</body>
</html>detailTest.jsp <%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="kr.util.DBUtil" %>
<%@ page import = "java.sql.Connection" %>
<%@ page import = "java.sql.PreparedStatement" %>
<%@ page import = "java.sql.ResultSet" %>
<%@ page import = "java.sql.Date" %> <!-- java.util.Date: 날짜를 만들어서 사용하는것. sql은 있는것을 불러와서 사용하는것 -->
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>상품정보 정보 보기</title>
<link rel="stylesheet" href="../css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
<h2>상품정보 정보</h2>
<%
int num = Integer.parseInt(request.getParameter("num"));
%>
<script type="text/javascript">
window.onload = function(){
const delete_btn = document.getElementById("delete_btn");
delete_btn.onclick = function(){
<%-- if(confirm('글을 삭제하시겠습니까?')){
location.href="deleteTest.jsp?num=<%= num %> "
} --%>
let choice = confirm('삭제하시겠습니까?');
if(choice){
location.replace('deleteTest.jsp?num=<%= num %>');
}
}
};
</script>
<%
Connection conn =null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
try{
//Connction 객체 반환
conn = DBUtil.getConnection();
//SQL문 작성
sql = "SELECT * FROM product WHERE num=?";
//JDBC 수행 3단계 : PreparedStateMent 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setInt(1,num);
//JDBC 수행 4단계:SQL문을 테이블에 반영하고 결과행을 ResultSet에 담아서 반환
rs = pstmt.executeQuery();
if(rs.next()){
String name = rs.getString("name");
int price = rs.getInt("price");
int stock = rs.getInt("stock");
String origin = rs.getString("origin");
String reg_date = rs.getString("reg_date");
%>
<ul>
<li>글번호 : <%= num %></li>
<li>제품이름 : <%= name %></li>
<li>가격 : <%= price %></li>
<li>재고 : <%= stock %></li>
<li>작성일 : <%= reg_date %></li>
<li>원산지 : <%= origin %></li>
</ul>
<hr width="100%" size="1" noshade="noshade">
<div class = "align-right">
<input type="button" value="수정"
onclick="location.href='updateTestForm.jsp?num=<%= num%>'">
<input type="button" value="삭제"
id="delete_btn">
<input type="button" value="목록"
onclick="location.href='selectTest.jsp'">
<%
}else{
%>
<div class="result-display">
<div class="align-center">
글상세 정보가 없습니다.<p>
<input type="button" value="목록"
onclick="location.href='selectTest.jsp'">
</div>
</div>
<%
}
}catch(Exception e){
%>
<div class="result-display">
<div class="align-center">
오류 발생! 글 상세 정보 호출 실패<p>
<input type="button" value="목록"
onclick="location.href='selectTest.jsp'">
</div>
</div>
<%
e.printStackTrace();
}finally{
//자원정리
DBUtil.executeClose(rs, pstmt, conn);
}
%>
</div>
</body>
</html>updateTestForm.jsp <%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "kr.util.DBUtil" %>
<%@ page import = "java.sql.Connection" %>
<%@ page import = "java.sql.PreparedStatement" %>
<%@ page import = "java.sql.ResultSet" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>제품 글 수정</title>
<link rel="stylesheet" href="../css/style.css" type="text/css">
<script type="text/javascript" src="script.js"></script>
</head>
<body>
<div class="page-main">
<h2>제품 글 수정</h2>
<%
int num = Integer.parseInt(request.getParameter("num"));
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
try{
//Connection 객체 반환
conn = DBUtil.getConnection();
//SQL문 작성
sql = "SELECT * FROM product WHERE num =?";
//JDBC 수행 3단계: PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setInt(1,num);
//JDBC 수행 4단계 : SQL문 실행
rs = pstmt.executeQuery();
if(rs.next()){
String name = rs.getString("name");
int price = rs.getInt("price");
int stock = rs.getInt("stock");
String origin = rs.getString("origin");
%>
<form id="myForm" action="updateTest.jsp" method="post">
<input type="hidden" name="num" value="<%= num %>">
<ul>
<li>
<label for="name">이름</label>
<input type="text" name="name" id="name" size="20" maxlength="10" value="<%= name %>">
</li>
<li>
<label for="price">가격</label>
<input type="number" name="price" id="price" size="20" maxlength="10" value="<%= price %>">
</li>
<li>
<label for="stock">재고</label>
<input type="number" name="stock" id="stock" size="20" maxlength="10" value="<%= stock %>">
</li>
<li>
<label for="origin">원산지</label>
<input type="text" name="origin" id="origin" size="20" maxlength="10" value="<%= origin %>">
</li>
</ul>
<div class="align-center">
<input type="submit" value="수정">
<input type="button" value="목록" onclick="location.href='selectTest.jsp'">
</div>
</form>
<%
}else{
%>
<div class="result-display">
<div class="align-center">
오류 발생! 수정할 글 정보 호출 실패!<p>
<input type="button" value="목록" onclick="location.href='selectTest.jsp'">
</div>
</div>
<%
}
}catch(Exception e){
%>
<div class="result-display">
<div class="align-center">
오류 발생! 수정할 글 정보 호출 실패!<p>
<input type="button" value="목록" onclick="location.href='selectTest.jsp'">
</div>
</div>
<%
e.printStackTrace();
}finally{
//자원정리
DBUtil.executeClose(rs, pstmt, conn);
}
%>
</div>
</body>
</html>updateTest.jsp <%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "kr.util.DBUtil" %>
<%@ page import = "java.sql.Connection" %>
<%@ page import = "java.sql.PreparedStatement" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>제품 글 수정</title>
<link rel="stylesheet" href="../css/style.css" type="text/css">
</head>
<body>
<%
//POST 방식으로 전달된 데이터 인코딩 타입 지정
request.setCharacterEncoding("utf-8");
//전송된 데이터 반환
int num = Integer.parseInt(request.getParameter("num"));
String name = request.getParameter("name");
int price = Integer.parseInt(request.getParameter("price"));
int stock= Integer.parseInt(request.getParameter("stock"));
String origin = request.getParameter("origin");
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
try{
//Connection 객체 반환
conn = DBUtil.getConnection();
//SQL문 작성
sql ="UPDATE product SET name=?,price=?,stock=?,origin=? WHERE num=?";
//JDBC 수행 3단계 : PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setString(1,name);
pstmt.setInt(2,price);
pstmt.setInt(3,stock);
pstmt.setString(4,origin);
pstmt.setInt(5,num);
//JDBC 수행 4단계 : SQL문 실행
pstmt.executeUpdate();
%>
<div class="result-display">
<div class="align-center">
상품 정보 수정 완료!<p>
<input type ="button" value="글상세"
onclick="location.href='detailTest.jsp?num=<%= num %>'">
</div>
</div>
<%
}catch(Exception e){
%>
<div class="result-display">
<div class="align-center">
오류 발생 글 수정 실패!<p>
<input type ="button" value="글 수정 폼"
onclick="location.href='updateTestForm.jsp?num=<%= num %>'">
</div>
</div>
<%
e.printStackTrace();
}finally{
//자원 정리
DBUtil.executeClose(null, pstmt, conn);
}
%>
</body>
</html>deleteTestForm.jsp <%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 삭제</title>
<link rel="stylesheet" href="../css/style.css" type="text/css">
</head>
<body>
<%
int num = Integer.parseInt(request.getParameter("num"));
%>
<div class="page-main">
<h2>글 삭제</h2>
<p class="align-center">
<span>정말 삭제하시겠습니까?</span>
</p>
<form action="deleteTest.jsp" method="post">
<input type="hidden" name="num" value="<%= num %>">
<div class="align-center">
<input type="submit" value="삭제">
<input type="button" value="목록"
onclick="location.href='selectTest.jsp'">
</div>
</form>
</div>
</body>
</html>deleteTest.jsp <%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="kr.util.DBUtil" %>
<%@ page import ="java.sql.Connection" %>
<%@ page import ="java.sql.PreparedStatement" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 삭제</title>
<link rel="stylesheet" href="../css/style.css" type="text/css">
</head>
<body>
<%
//POST 방식으로 전송된 데이터 인코딩 타입 지정
request.setCharacterEncoding("utf-8");
//전송된 데이터 반환
int num = Integer.parseInt(request.getParameter("num"));
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
try{
//Connection 객체 반환
conn = DBUtil.getConnection();
//SQL문 작성
sql = "DELETE FROM product WHERE num=?";
//JDBC 수행 3단계 : PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setInt(1,num);
//JDBC 수행 4단계: SQL문 실행
pstmt.executeUpdate();
%>
<div class="result-display">
<div class="align-center">
글 삭제 완료!<p>
<input type="button" value="목록" onclick="location.href='selectTest.jsp'">
</div>
</div>
<%
}catch(Exception e){
%>
<div class="result-display">
<div class="align-center">
오류발생! 글 삭제 실패!<p>
<input type="button" value="목록" onclick="location.href='selectTest.jsp'">
</div>
</div>
<%
e.printStackTrace();
}finally{
//자원 정리
DBUtil.executeClose(null, pstmt, conn);
}
%>
</body>
</html>
