[JSP] 회원 조회, 등록, 삭제

heubanufi·2024년 3월 28일

JSP/Servlet

목록 보기

1/4

DYNAMIC WEB PROJECT 생성
WEB-INF - lib에 ojdbc6.jar 넣기
Java Resources - src에 JdbcUtil.java 파일 생성

JdbcUtil.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcUtil {
	public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
	public static final String URL = "jdbc:oracle:thin:@localhost:1521:xe";
	public static final String USER = "sqlid";
	public static final String PASSWORD = "pw";
	
	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(URL, USER, PASSWORD);
	}
	
	public static void close(Connection conn, Statement stmt, ResultSet rs) {
		//5. 클로징 처리에 의한 자원 반납
		try {
			if (rs != null) rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (stmt != null) stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void close(Connection conn, Statement stmt) {
		try {
			if (stmt != null) stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

메인화면

Main.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>메인페이지</title>
<script src="includee/event.js" type="text/javascript"></script>
</head>
<body>
	<%@ include file="includee/menu.jspf" %>
</body>
</html>

includee/event.js

<script src="includee/event.js" type="text/javascript"></script>

	function sel_all() { //전체 데이터 조회
		location.href = "selectAll.jsp";
	}
	function sel_one(frm) { //id로 검색
		frm.action = "selectOneID.jsp";
		frm.submit();
	}
	function add_go(frm) { //데이터 입력
		frm.action = "insertMember.jsp";
		frm.submit();
	}
	function del_go(frm) { //데이터 삭제
		frm.action = "deleteMember.jsp";
		frm.submit();
	}

includee/menu.jspf

<body>
	<form>
		<table border>
			<thead>
				<tr>
					<th colspan="2">회원정보</th>
				</tr>
			</thead>
			<tbody>
				<tr>
					<th>아이디</th>
					<td><input type="text" name="id"></td>
				</tr>
				<tr>
					<th>패스워드</th>
					<td><input type="password" name="pwd"></td>
				</tr>
				<tr>
					<th>이름</th>
					<td><input type="text" name="name"></td>
				</tr>
				<tr>
					<th>나이</th>
					<td><input type="number" name="age"></td>
				</tr>
				<tr>
					<th>주소</th>
					<td><input type="text" name="address"></td>
				</tr>
			</tbody>
			<tfoot>
				<tr>
					<td colspan="2">
						<input type="button" value="전체검색" onclick="sel_all()">
						<input type="button" value="검색(id)" onclick="sel_one(this.form)">
						<input type="button" value="입력" onclick="add_go(this.form)">
						<input type="button" value="삭제(id)" onclick="del_go(this.form)">
					</td>
				</tr>
			</tfoot>
		</table>
	</form>

</body>

전체검색

selectAll.jsp

<%@page import="com.mystudy.common.JdbcUtil"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	try {
		conn = JdbcUtil.getConnection();
		System.out.println("conn: " + conn);
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT IDX, ID, PWD, NAME, AGE ");
		sql.append("      , NVL(ADDRESS, '주소없음')AS ADDRESS, REG");
		sql.append(" FROM MEMBER2");
		sql.append(" ORDER BY IDX");
		 
		pstmt = conn.prepareStatement(sql.toString());
		
		rs = pstmt.executeQuery();
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>전체데이터검색</title>
<script src="includee/event.js" type="text/javascript"></script>
</head>
<body>
	<%@ include file="includee/menu.jspf" %>
	<h1>전체 데이터 검색[ selectAll.jsp ]</h1>
	<table border>
		<tr>
			<th>번호</th>
			<th>아이디</th>
			<th>패스워드</th>
			<th>이름</th>
			<th>나이</th>
			<th>주소</th>
			<th>날짜</th>
		</tr>
<%
		while(rs.next()){
%>		
			<tr>
				<td><%=rs.getInt("IDX") %></td>
				<td><%=rs.getString("ID") %></td>
				<td><%=rs.getString("PWD") %></td>
				<td><%=rs.getString("NAME") %></td>
				<td><%=rs.getInt("AGE") %></td>
				<td><%=rs.getString("ADDRESS") %></td>
				<td><%=rs.getDate("REG") %></td>
			</tr>			

<%		} %>
	</table>
</body>
</html>

<%
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		JdbcUtil.close(conn, pstmt, rs);
	}
%>

ID로 검색

selecetOneID.jsp

<%
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	try {
		conn = JdbcUtil.getConnection();
		System.out.println("conn: " + conn);
		
		String id = request.getParameter("id");
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT IDX, ID, PWD, NAME, AGE ");
		sql.append("      , NVL(ADDRESS, '주소없음')AS ADDRESS, REG");
		sql.append(" FROM MEMBER2");
		sql.append(" WHERE ID = ? ");
		sql.append(" ORDER BY IDX");
		
		pstmt = conn.prepareStatement(sql.toString());
		pstmt.setString(1, id);
		rs = pstmt.executeQuery();
		
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>검색id</title>
<script src="includee/event.js" type="text/javascript"></script>
</head>
<body>
	<%@ include file="includee/menu.jspf" %>
	<hr>
	
	<h1>검색(id) 결과</h1>
	
	<table border>
		<tr>
			<th>번호</th>
			<th>아이디</th>
			<th>패스워드</th>
			<th>이름</th>
			<th>나이</th>
			<th>주소</th>
			<th>날짜</th>
		</tr>
<%
		if(rs.next()){
%>		
			<tr>
				<td><%=rs.getInt("IDX") %></td>
				<td><%=rs.getString("ID") %></td>
				<td><%=rs.getString("PWD") %></td>
				<td><%=rs.getString("NAME") %></td>
				<td><%=rs.getInt("AGE") %></td>
				<td><%=rs.getString("ADDRESS") %></td>
				<td><%=rs.getDate("REG") %></td>
			</tr>			
<%					
	} else { %>
	<tr>
		<td colspan='7'>데이터가 없습니다</td>
	</tr>

<% } %>		

	</table>
</body>
</html>

<%
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		JdbcUtil.close(conn, pstmt, rs);
	}
%>

데이터 입력하여 추가

insertMember.jsp

    <%-- 전달받은 데이터를 db에 입력하고, 전체데이터 보기 페이지로 이동 --%>
    <%
    //1. 전달받은 데이터 추출
    String id = request.getParameter("id");
    String pwd = request.getParameter("pwd");
    String name = request.getParameter("name");
    int age = Integer.parseInt(request.getParameter("age"));
    String address = request.getParameter("address");
    //2. db에 입력 처리
    //3. 페이지 전환
    %>
    <%
	Connection conn = null;
	PreparedStatement pstmt = null;
	
	try {
		conn = JdbcUtil.getConnection();
		System.out.println("conn: " + conn);
		
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO MEMBER2 ");
		sql.append(" (IDX, ID, PWD, NAME, AGE, ADDRESS, REG)");
		sql.append(" VALUES (MEMBER2_SEQ.NEXTVAL, ?, ?, ?, ?, ?, SYSDATE)");
		
		pstmt = conn.prepareStatement(sql.toString());

		//바이드변수에 데이터 설정
		pstmt.setString(1,id);
		pstmt.setString(2,pwd);
		pstmt.setString(3,name);
		pstmt.setInt(4,age);
		pstmt.setString(5,address);
		
		//SQL문 실행(insert, update, delete - executeUpdate) / select는 executeQuery
		pstmt.executeUpdate();
		
		//정상 입력 처리 후 페이지 전환(전체데이터 보기)
		response.sendRedirect("selectAll.jsp");
		
	} catch (Exception e) {
		e.printStackTrace();
		response.sendRedirect("error.jsp");
	} finally {
		JdbcUtil.close(conn, pstmt);
	}
%>

아이디로 데이터 삭제

deleteMember.jsp

    <%--전달받은 값으로 DB 데이터 삭제 --%>
    <%
    //1. 전달받은 데이터 추출
    String id = request.getParameter("id");

	Connection conn = null;
	PreparedStatement pstmt = null;
	
	try {
		conn = JdbcUtil.getConnection();
		System.out.println("conn: " + conn);
		
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM MEMBER2 ");
		sql.append(" WHERE ID = ?");
		
		pstmt = conn.prepareStatement(sql.toString());

		//바이드변수에 데이터 설정
		pstmt.setString(1,id);

		//SQL문 실행(insert, update, delete - executeUpdate) / select는 executeQuery
		pstmt.executeUpdate();
		
		//정상 입력 처리 후 페이지 전환(전체데이터 보기)
		response.sendRedirect("selectAll.jsp");
		
	} catch (Exception e) {
		e.printStackTrace();
		response.sendRedirect("error.jsp");
	} finally {
		JdbcUtil.close(conn, pstmt);
	}
%>

error.jsp

<script>
	alert("[예외발생] 처리 중 예외가 발생했습니다 \n " + "이전페이지로 이동합니다");
	history.back();
</script>

SQL

CREATE TABLE MEMBER2 (
    IDX NUMBER(5) PRIMARY KEY,
    ID VARCHAR2(20) NOT NULL UNIQUE,
    PWD VARCHAR2(20) NOT NULL,
    NAME VARCHAR2(30),
    AGE NUMBER(3),
    ADDRESS VARCHAR2(300),
    REG DATE DEFAULT SYSDATE
);

CREATE SEQUENCE MEMBER2_SEQ;

INSERT INTO MEMBER2
       (IDX, ID, PWD, NAME, AGE, ADDRESS, REG)
VALUES (MEMBER2_SEQ.NEXTVAL, 'admin', 'admin', '관리자', null, null, SYSDATE);      
INSERT INTO MEMBER2
       (IDX, ID, PWD, NAME, AGE, ADDRESS, REG)
VALUES (MEMBER2_SEQ.NEXTVAL, 'test1', 'test1', '테스트1', 27, '서울', SYSDATE);    
COMMIT;

SELECT * FROM MEMBER2;

heubanufi

다음 포스트

[JSP] 회원 조회, 등록, 삭제

JSP/Servlet

메인화면

전체검색

ID로 검색

데이터 입력하여 추가

아이디로 데이터 삭제

SQL

[JSP/Mybatis] 방명록 만들기(1)

0개의 댓글