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>
'쌍용교육(JAVA) > JSP' 카테고리의 다른 글
| 쌍용교육 -JSP수업 49일차 ajax (2) | 2024.04.26 |
|---|---|
| 쌍용교육 -JSP수업 48~49일차 jdbc(2) (0) | 2024.04.25 |
| 쌍용교육 -JSP수업 47~48일차 fileupload(2) (0) | 2024.04.25 |
| 쌍용교육 -JSP수업 47일차 fileupload (0) | 2024.04.24 |
| 쌍용교육 -JSP수업 47일차 session (0) | 2024.04.24 |
