우선 DB와 연동하려면 최우선적으로 해당 파일에 라이브러리를 로딩해야한다.
lib에는 외부 라이브러리들을 모아놓는 장소이다.
로딩을 완료했다면 Orcale을 보다 쉽게 사용할 수 있도록 Oracle SQL Developer를 설치한다.
이때 테스트 후 접속을 누르면 된다.
그러면 기존에 있던 DB들과 새로 만든 DB들이 존재하고 쉽게 어떤 것 들이 있는지 확인할 수 있다.
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>회원관리 시스템</h1>
<a href = "join.html"><button>회원가입</button></a>
<a href = "delete.html"><button>회원삭제</button></a>
<a href = "update.html"><button>회원수정</button></a>
<a href = "Select.jsp"><button>회원전체검색</button></a>
<a href = "Select.jsp"><button>회원개별검색</button></a>
<a href = "Login.html"><button>로그인</button></a>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>회원가입페이지</h1>
<form action="JoinCon" method="post">
<input type="text" name="id" placeholder = "ID입력" required="required"><br>
<input type="password" name="pw" placeholder = "PW입력" required="required"><br>
<input type="text" name="nick" placeholder = "NICK입력" required="required"><br>
<input type="submit" value="회원가입">
<!-- re 치고 자동완성하면 required 속성이 나오는데 이것이 백앤드의 not null과 같은 역할을 함 -->
</form>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>회원삭제페이지</h1>
<form action="DeleteCon" method="post">
<input type="text" name="id" placeholder="ID입력"><br>
<input type="submit" value="회원삭제">
</form>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>회원수정페이지</h1>
<form action="UpdateCon" method="post">
<input type="text" name="id" placeholder = "ID입력"><br>
<select name="select">
<option>pw</option>
<option>nick</option>
</select>
<input type="text" name="data" placeholder = "변경할 data 입력"><br>
<input type="submit" value="회원수정">
</form>
</body>
</html>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>회원 전체 검색</h1>
<table border=1>
<tr>
<td>ID</td>
<td>PW</td>
<td>NICK</td>
</tr>
<%
PreparedStatement psmt = null;
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
String dbid = "hr";
String dbpw = "hr";
conn = DriverManager.getConnection(url, dbid, dbpw);
String sql = "select * from web_member";
psmt = conn.prepareStatement(sql);
ResultSet rs = psmt.executeQuery();
while (rs.next()) {
String id = rs.getString(1);
String pw = rs.getString(2);
String nick = rs.getString(3);
out.print("<tr>"+"<td>"+id+"</td>");
out.print("<td>"+pw+"</td>");
out.print("<td>"+nick+"</td>"+"</tr>");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (psmt != null) {
psmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
%>
</table>
</body>
</html>
// HTML에서 입력받는 값이 없으니 삭제
//request.setCharacterEncoding("UTF-8");
//String id = request.getParameter("id");
psmt.executeUpdate();
: 얘는 몇개의 명령어가 성공했다고 int값이 return됨. 즉,insert
, delete
, update
에서 주로 쓰임
psmt.executeQuery();
: 얘는 실제의 검색 결과값이 return됨. selete
에서 주로 실행됨
<style>
package com.Controller;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/DeleteCon")
public class DeleteCon extends HttpServlet {
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
String id = request.getParameter("id");
PreparedStatement psmt = null;
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
String dbid = "hr";
String dbpw = "hr";
conn = DriverManager.getConnection(url, dbid, dbpw);
String sql = "delete from web_member where id=(?)";
psmt = conn.prepareStatement(sql);
psmt.setString(1, id);
int cnt = psmt.executeUpdate();
if (cnt > 0) {
response.sendRedirect("main.html");
} else {
System.out.println("delete failed");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (psmt != null) {
psmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</style>
sendRedirect
을 이용하여 main page로 이동하게끔 서비스를 만들었다.<style>
package com.Controller;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/JoinCon")
public class JoinCon extends HttpServlet {
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
PreparedStatement psmt = null;
Connection conn = null;
String id = request.getParameter("id");
String pw = request.getParameter("pw");
String nick = request.getParameter("nick");
// ojbbc6.jar 파일 가져오기
// DB에 Table 만들기
// JDBC 코드 작성하기
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
// 동적로딩 => 지가 원하는 파일을 갖다 쓰라고 워낙 DB프로그램
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
// 실제 DB가 있는 주소
String dbid = "hr";
String dbpw = "hr";
// 그때 dbid, idpw를 갖고 감.
conn = DriverManager.getConnection(url, dbid, dbpw);
// 얘는 드라이버를 관장하는 녀석
// 그 후 dB를 handling 할 수 있도록 해주는 객체를 주는데 그것이 커넥션 인터페이스이다.
// if(conn != null) {
// System.out.println("DB is successfully connected");
// }else {
// System.out.println("DB connection is failed");
// }
// DB 연결 성공했다면 이제 명령문을 실행할 준비
String sql = "insert into web_member values(?,?,?)";
psmt = conn.prepareStatement(sql);
psmt.setString(1, id);
psmt.setString(2, pw);
psmt.setString(3, nick);
// SQL 준비단계
int cnt = psmt.executeUpdate();
// SQL 실행
if (cnt > 0) {
response.sendRedirect("main.html");
} else {
System.out.println("input failed");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (psmt != null) {
psmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
</style>
<style>
package com.Controller;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/UpdateCon")
public class UpdateCon extends HttpServlet {
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
String id = request.getParameter("id");
String select = request.getParameter("select");
String data = request.getParameter("data");
PreparedStatement psmt = null;
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
String dbid = "hr";
String dbpw = "hr";
conn = DriverManager.getConnection(url, dbid, dbpw);
String sql = "";
if (select.equals("pw")) {
sql = "update web_member set pw= ? where id = ?";
} else if (select.equals("nick")) {
sql = "update web_member set nick= ? where id = ?";
}
psmt = conn.prepareStatement(sql);
psmt.setString(1, data);
psmt.setString(2, id);
int cnt = psmt.executeUpdate();
if (cnt > 0) {
response.sendRedirect("main.html");
} else {
System.out.println("update failed");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (psmt != null) {
psmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</style>