[Spring] Oracle x Eclipse CRUD
✅ Version
ツール仕様開発ツールJava SE JDK 8.0.271開発ツールEclipse Java EE IDEサーバApache Tomcat 8.0 DBMS Oracle 11 g Expressバージョン11.2
🔘 Oracle
▶ヘルプインストールのリンク:ショートカット
▶アプリケーションeclipseリンク:ショートカット
🍃 プロジェクトの作成
🔗 File▶New▶Dynamic Web Project▶Project nameのみ記入後Finish
🔘 Oracleプロジェクトの接続
🎨 結果
🎨 こうぞう
追加または変更されたファイルは次のとおりです.
1. 📁WebContent ▶ 📁WEB-INF ▶ 📁lib ▶ 📄ojdbc6.jarの追加
🔗 ojdbc6.JArパスは「C:oraclexeapporacleproduct11.2.0serverjdbclib」
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
✔Web Content以下が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>
💥 話題
動的Webプロジェクトがサーバ上で実行されない場合
💢 質問:サーバーを作成する前に、プロジェクトを最初に作成するときに発生する問題.
💨 解決:📁Project右クリック▶目標運転時間▶チェック後適用
Reference
この問題について([Spring] Oracle x Eclipse CRUD), 我々は、より多くの情報をここで見つけました https://velog.io/@ieed0205/Spring-Oracle-x-Eclipse-CRUDテキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol