쌍용교육 -JSP수업 48일차 jdbc

쌍용교육(JAVA)/JSP

쌍용교육 -JSP수업 48일차 jdbc

구 승 2024. 4. 25. 12:31

connectionTest.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import ="java.sql.DriverManager" %>
<%@ page import ="java.sql.Connection" %>
<%@ page import ="java.sql.SQLException" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>연동 테스트</title>
</head>
<body>
<%
	String driverName = "oracle.jdbc.OracleDriver";
	String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:xe";
	String dbId = "user01";
	String dbPass = "1234";
	
	Connection conn = null;
	try{
		//JDBC 수행 1단계 : 드라이버 로드
		Class.forName(driverName);
		//JDBC 수행 2단계 : connection 객체 생성
		conn = DriverManager.getConnection(jdbcUrl,dbId,dbPass);
		
		out.println("정상적으로 연결되었습니다");
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		//자원정리
		if(conn!=null)try{conn.close();}catch(SQLException e) {};
	}
%>
</body>
</html>

table.sql

create table tboard(
 num number primary key,
 name varchar2(30) not null,
 title varchar2(150) not null,
 passwd varchar2(10) not null,
 content varchar2(4000) not null,
 reg_date date not null,
);
create sequence tboard_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="title">제목</label>
				<input type="text" name="title" id="title" size="30" maxlength="10">
			</li>
			<li>
				<label for="passwd">비밀번호</label>
				<input type="password" name="passwd" id="passwd" size="20" maxlength="10">
			</li>
			<li>
				<label for="content">내용</label>
				<textarea rows="5" cols="40" name="content" id="content"></textarea>
			</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="password"],textarea');
		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");
	String title = request.getParameter("title");
	String passwd= request.getParameter("passwd");
	String content = request.getParameter("content");
	
	//DB 연동
	Connection conn = null;
	PreparedStatement pstmt = null;
	String sql = null;
	
	try{
		//Connection 객체 반환
		conn = DBUtil.getConnection();
		//SQL문 작성
		sql = "INSERT INTO tboard (num,name,title,passwd,content,reg_date) VALUES (tboard_seq.nextval,?,?,?,?,SYSDATE)";
		
		//JDBC 수행 3단계 : PreparedStatement 객체 생성
		pstmt = conn.prepareStatement(sql);
		//?에 데이터 바인딩
		pstmt.setString(1,name);
		pstmt.setString(2,title);
		pstmt.setString(3,passwd);
		pstmt.setString(4,content);
		
		//JDBC 수행 4단계 : SQL문 실행
		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>

selectText.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 tboard 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>
			</tr>
<%
		while(rs.next()){
			int num = rs.getInt("num");
			String name = rs.getString("name");
			String title = rs.getString("title");
			Date reg_date = rs.getDate("reg_date");
%>
		<tr>
			<td><%= num %></td>
			<td><a href="detailTest.jsp?num=<%= num %>"><%= title %></a></td>
			<td><%= name %></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"));
	
	Connection conn =null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	String sql = null;
	
	try{
		//Connction 객체 반환
		conn = DBUtil.getConnection();
		//SQL문 작성
		sql = "SELECT * FROM tboard 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");
			String title = rs.getString("title");
			String content = rs.getString("content");
			Date reg_date = rs.getDate("reg_date");
%>
	<ul>
		<li>글번호 : <%= num %></li>
		<li>제목 : <%= title %></li>
		<li>작성자 : <%= name %></li>
		<li>작성일 : <%= reg_date %></li>
	</ul>
	<hr width="100%" size="1" noshade="noshade">
	<p>
		<%= content %>
		
	</p>
	<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="삭제" 
					onclick="location.href='deleteTestForm.jsp?num=<%= num%>'">
		<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 tboard 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");
			String title = rs.getString("title");
			String content = rs.getString("content");
%>
		<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="title">제목</label>
				<input type="text" name="title" id="title" 
					size="30" maxlength="10" value="<%= title %>">
			</li>
			<li>
				<label for="passwd">비밀번호</label>
				<input type="password" name="passwd" id="passwd" 
					size="20" maxlength="10">
			</li>
			<li>
				<label for="content">내용</label>
				<textarea rows="5" cols="40" name="content" id="content" ><%= content %></textarea>
			</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");
	String title = request.getParameter("title");
	String passwd= request.getParameter("passwd");
	String content = request.getParameter("content");
	
	Connection conn = null;
	PreparedStatement	pstmt = null;
	String sql = null;
	try{
		//Connection 객체 반환
		conn = DBUtil.getConnection();
		//SQL문 작성
		sql ="UPDATE tboard SET name=?,title=?,passwd=?,content=? WHERE num=?";
		//JDBC 수행 3단계 : PreparedStatement 객체 생성
		pstmt = conn.prepareStatement(sql);
		//?에 데이터 바인딩
		pstmt.setString(1,name);
		pstmt.setString(2,title);
		pstmt.setString(3,passwd);
		pstmt.setString(4,content);
		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 tboard 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>