[Spring] Oracle x Eclipse CRUD

BBANG-JUN·2021년 5월 11일

🍃Spring

목록 보기

26/26

✅ Version

도구	규격
개발도구	Java SE JDK 8.0.271
개발도구	Eclipse Java EE IDE
서버	Apache Tomcat 8.0
DBMS	Oracle 11g Express Edition 11.2

🔘 Oracle

▶ 설치에 도움받은 링크 : 바로가기↗

▶ eclipse 적용 링크 : 바로가기↗

🍃 프로젝트 생성

🔗 File ▶ New ▶ Dynamic Web Project ▶ Project name만 기입 후 Finish

🔘 Oracle 프로젝트 연결

🎨 결과

🎨 구조

추가하거나 변경해준 파일은
1. 📁WebContent ▶ 📁WEB-INF ▶ 📁lib ▶ 📄ojdbc6.jar 추가
🔗 ojdbc6.jar 경로는 'C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib'
2. 📁WebContent ▶ 📁WEB-INF ▶ 📄web.xml 내용 추가

📄 추가된 web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>soldesk</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  
  <!-- 추가 -->
  <resource-ref>
  	<description>Connection</description>
  	<res-ref-name>jdbc/OracleDB</res-ref-name>
  	<res-type>javax.sql.DataSource</res-type>
  	<res-auth>Container</res-auth>
  </resource-ref>
  
</web-app>

📄 index.jsp

🔗 📁WebContent ▶ 📄index.jsp
✔ WebContent 이하에 있어야 web.xml에서 welcom-file로 인지함.

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>고객관리 시스템</title>
</head>
<body>
	<%@include file="main_header.jsp" %>
	
	<%@include file="main_nav.jsp" %>
	
	<%@include file="main_footer.jsp" %>
</body>
</html>

📄 main_header/nav/footer.jsp

📄 main_header.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>

<div style="text-align:center; height:80px;">
	<h1><a href="index.jsp">고객관리 시스템</a></h1>
</div>

📄 main_nav.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<center>
	<table width="100%" align="center">
		<tr>
			<td width="25%" align="center"><a href="guestView.jsp">고객 목록 조회</a></td>
			<td width="25%" align="center"><a href="guestInsert_form.jsp">고객 입력 화면</a></td>
		</tr>
	</table>
</center>
</br></br>

📄 main_footer.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>

<br/><br/>
<div style="text-align:center;">
	<h5>HMYK @Copyright 2019</h5>
</div>

📄 guestView.jsp

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="java.sql.*"%>
<%
String sql = "SELECT custId, author, goods, phone, email, area ";
	sql += "FROM bookinfo_tbl ";
	sql += "ORDER BY custId ASC";

String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "유저ID";
String password = "비밀번호";

try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con = DriverManager.getConnection(url, user, password);
	Statement stmt = con.createStatement();
	ResultSet rs = stmt.executeQuery(sql);

%>

<head>
	<meta charset="UTF-8">
	<title>고객 목록 조회</title>
</head>

<body>
	<%@include file="main_header.jsp" %>
	<%@include file="main_nav.jsp" %>
	
	<center>
	<table border="1" cellspacing="1">
		<tr>
			<th>고객ID</th>
			<th>고객명</th>
			<th>관심상품</th>
			<th>전화번호</th>
			<th>이메일</th>
			<th>지역</th>
			<th>삭제</th>
		</tr>
			<%
			while(rs.next()) {
			%>
		<tr>
			<td><a href="javascript:updateGuest('<%=rs.getString(1)%>');"><%=rs.getString(1) %></a></td>
			<td><%=rs.getString(2) %></td>
			<td><%=rs.getString(3) %></td>
			<td><%=rs.getString(4) %></td>
			<td><%=rs.getString(5) %></td>
			<td><%=rs.getString(6) %></td>
			<td><input type="button" value="삭제" onclick="clickDelete('<%=rs.getString(1) %>');"></td>
		</tr>
			<%
			}
			
			rs.close();
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		%>
	</table>
	</center>
	
	<%@include file="main_footer.jsp" %>
</body>

<script type="text/javascript">

function clickDelete(custId) {
	if(confirm("삭제하시겠습니까?") == true) {
		location.href = "guestDelete.jsp?custId="+custId;
	}
}
function updateGuest(custId) {
	location.href = "guestInsert_form.jsp?custId="+custId+"&saveType=U";
}
</script>

📄 guestInsert_form.jsp

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="java.sql.*"%>
<%
String saveType = request.getParameter("saveType") == null ? "I" : request.getParameter("saveType");
String custId = request.getParameter("custId") == null ? "" : request.getParameter("custId");
String author = "", goods = "", phone = "", email = "", area = "";
String phone1 = "", phone2 = "", phone3 = "";

String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "유저ID";
String password = "비밀번호";
String sql = "SELECT custId, author, goods, phone, email, area  FROM bookinfo_tbl WHERE custId = ?";

try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con = DriverManager.getConnection(url, user, password);
	PreparedStatement pstmt = con.prepareStatement(sql);
	pstmt.setString(1, custId);
	ResultSet rs = pstmt.executeQuery();
	
	while(rs.next()) {
		author = rs.getString(2);
		goods = rs.getString(3);
		phone = rs.getString(4);
		email = rs.getString(5);
		area = rs.getString(6);
		
		String phoneArr[] = phone.split("-");
		phone1 = phoneArr[0];
		phone2 = phoneArr[1];
		phone3 = phoneArr[2];
		
		break;
	}
} catch(SQLException e) {
	e.printStackTrace();
}

%>

<head>
	<meta charset="UTF-8">
	<title>고객 입력 화면</title>
</head>

<body>
	<%@include file="main_header.jsp" %>
	<%@include file="main_nav.jsp" %>
	
	<form name="insertfrm" id="insertfrm" method="post" action="guestInsert_save.jsp">
	<input type="hidden" name="saveType" id="saveType" value="<%=saveType%>">
	<center>
	<h2>회원 등록</h2>
	<table border="1" cellspacing="1">
		<tr>
			<th width="100">아이디</th>
			<td width="200"><input type="text" name="custId" id="custId" maxlength="20" size="40" value="<%=custId%>"></td>
		</tr>
		<tr>
			<th>성명</th>
			<td><input type="text" name="author" id="author" maxlength="30" size="40" value="<%=author%>"></td>
		</tr>
		<tr>
			<th>관심상품</th>
			<td><input type="text" name="goods" id="goods" maxlength="30" size="40" value="<%=goods%>"></td>
		</tr>
		<tr>
			<th>전화번호</th>
			<td>
				<input type="hidden" name="phone" id="phone" />
				<input type="text" name="phone1" id="phone1" size="5" maxlength="3" value="<%=phone1%>"> -
				<input type="text" name="phone2" id="phone2" size="10" maxlength="4" value="<%=phone2%>"> - 
				<input type="text" name="phone3" id="phone3" size="10" maxlength="4" value="<%=phone3%>">
			</td>
		</tr>
		<tr>
			<th>이메일</th>
			<td><input type="text" name="email" id="email" size="40" maxlength="30" value="<%=email%>"></td>
		</tr>
		<tr>
			<th>거주지역</th>
			<td><input type="text" name="area" id="area" size="40" maxlength="15" value="<%=area%>"></td>
		</tr>
		<tr>
			<td colspan="2" style="text-align:center;">
				<input type="button" name="saveBtn" id="saveBtn" value="등록" onclick="clickSave();">
				<input type="reset" value="취소">
			</td>
			
		</tr>
	</table>
	</center>
	</form>
	
	<%@include file="main_footer.jsp" %>
</body>

<script type="text/javascript">

function clickSave() {
	var frm = document.insertfrm;
	if(!frm.custId.value || frm.custId.value == "") {
		alert("아이디를 입력하세요");
		frm.custId.focus();
		return;
	} else if(!frm.author.value || frm.author.value == "") {
		alert("성명을 입력하세요");
		frm.author.focus();
		return;
	} else if(!frm.goods.value || frm.goods.value == "") {
		alert("관심상품을 입력하세요");
		frm.goods.focus();
		return;
	} else if(!frm.phone1.value || frm.phone1.value == "") {
		alert("전화번호를 입력하세요");
		frm.phone1.focus();
		return;
	} else if(!frm.phone2.value || frm.phone2.value == "") {
		alert("전화번호를 입력하세요");
		frm.phone2.focus();
		return;
	} else if(!frm.phone3.value || frm.phone2.value == "") {
		alert("전화번호를 입력하세요");
		frm.phone3.focus();
		return;
	} else if(!frm.email.value || frm.email.value == "") {
		alert("이메일을 입력하세요");
		frm.email.focus();
		return;
	} else if(!frm.area.value || frm.area.value == "") {
		alert("거주지역을 입력하세요");
		frm.area.focus();
		return;
	}
	
	frm.phone.value = frm.phone1.value + "-" + frm.phone2.value + "-" + frm.phone3.value;
	
	frm.submit();	
}

</script>

📄 guestInsert_save.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="java.sql.*"%>
<%
request.setCharacterEncoding("UTF-8");

String custId = request.getParameter("custId");
String author = request.getParameter("author");
String goods = request.getParameter("goods");
String phone = request.getParameter("phone");
String email = request.getParameter("email");
String area = request.getParameter("area");

String saveType = request.getParameter("saveType");
String sql = "";
if(saveType.equals("I")) {
	sql = "INSERT INTO bookinfo_tbl VALUES(?, ?, ?, ?, ?, ?)";
} else {
	sql = "UPDATE bookinfo_tbl SET author=?, goods=?, phone=?, email=?, area=? WHERE custId=?";	
}

String user = "유저ID", password = "비밀번호";
String url = "jdbc:oracle:thin:@localhost:1521:xe";

int result = 0;
try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con = DriverManager.getConnection(url, user, password);
	PreparedStatement pstmt = con.prepareStatement(sql);
	if(saveType.equals("I")) {
		pstmt.setString(1, custId);
		pstmt.setString(2, author);
		pstmt.setString(3, goods);
		pstmt.setString(4, phone);
		pstmt.setString(5, email);
		pstmt.setString(6, area);
	} else {
		pstmt.setString(1, author);
		pstmt.setString(2, goods);
		pstmt.setString(3, phone);
		pstmt.setString(4, email);
		pstmt.setString(5, area);
		pstmt.setString(6, custId);
	}
	
	result = pstmt.executeUpdate();
%>

<head>
	<meta charset="UTF-8">
	<title>고객 입력 완료 화면</title>
</head>

<body>
	<%@include file="main_header.jsp" %>
	<%@include file="main_nav.jsp" %>
	
	<div style="text-align:center;">
		<%
		if(result == 0) {
		%>
		<h2>오류가 발생했습니다. 관리자에게 문의하세요.</h2>
		<%
		} else {
		%>
		<h2><%=custId %>님이 등록되었습니다.</h2>
		<%
		}
	} catch(SQLException e) {
		e.printStackTrace();
	}
	%>
	</div>
	
	<%@include file="main_footer.jsp" %>
</body>

📄 guestDelete.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="java.sql.*"%>
<%
request.setCharacterEncoding("UTF-8");

String custId = request.getParameter("custId");

String sql = "DELETE FROM bookinfo_tbl WHERE custId = ?";
String user = "유저ID", password = "비밀번호";
String url = "jdbc:oracle:thin:@localhost:1521:xe";

int result = 0;
try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con = DriverManager.getConnection(url, user, password);
	PreparedStatement pstmt = con.prepareStatement(sql);
	pstmt.setString(1, custId);
	
	result = pstmt.executeUpdate();
%>

<head>
	<meta charset="UTF-8">
	<title>고객 입력 완료 화면</title>
</head>

<body>
	<%@include file="main_header.jsp" %>
	<%@include file="main_nav.jsp" %>
	
	<div style="text-align:center;">
		<%
		if(result == 0) {
		%>
		<h2>오류가 발생했습니다. 관리자에게 문의하세요.</h2>
		<%
		} else {
		%>
		<h2><%=custId %>님이 삭제되었습니다.</h2>
		<%
		}
	} catch(SQLException e) {
		e.printStackTrace();
	}
	%>
	</div>
	
	<%@include file="main_footer.jsp" %>
</body>