개발자/JSP

[jdbc] 싱글톤을 적용한 CRUD

푸루닉 2022. 12. 22. 14:03

싱글톤을 적용한 CRUD를 구성할 것이다. 

 

싱글톤 구현을 위한 web.xml 과 context.xml

context.xml

<?xml version="1.0" encoding="UTF-8"?>

<!-- 고정적인 객체를 미리 만들어놓는(객체를 생성하기 위한 주문서) 것. -->
<Context>
	<Resource 
		name="jdbc/oracle"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="oracle.jdbc.driver.OracleDriver"
		url="jdbc:oracle:thin:@192.168.1.100:1521:xe"
		username="c##itbank"
		password="it"
		maxidle="10"
		maxWait="-1"
		
	/>
</Context>

web.xml

<?xml version="1.0" encoding="UTF-8"?>

<!-- 초기작동 context의 주문서들을 참조 -->
<resource-ref>
	<description>ConnectionPool</description>
	<res-ref-name>jdbc/oracle</res-ref-name>
	<res-type>javax.sql.DataSource</res-type>
	<res-auth>Container</res-auth>
</resource-ref>

 

Member2DTO

package member2;

public class Member2DTO {
	private int idx;
	private String userid;
	private String userpw;
	private String username;
	private String gender;

	public int getIdx() {
		return idx;
	}

	public void setIdx(int idx) {
		this.idx = idx;
	}

	public String getUserid() {
		return userid;
	}

	public void setUserid(String userid) {
		this.userid = userid;
	}

	public String getUserpw() {
		return userpw;
	}

	public void setUserpw(String userpw) {
		this.userpw = userpw;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}
	
	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

}

Member2DAO

package member2;

import java.sql.*;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class Member2DAO {
	// 싱글톤과 Connectionpool이 적용된 member2 테이블 관련 DAO를 작성하세요
	
	// 회원 목록을 반환하는 함수를 작성하여 jsp에서 출력합니다
	
	// header.jsp 를 작성하여 tablib, import, dao 선언을 수행합니다
	
	// list.jsp를 작성하여 회원 목록을 table 형식으로 화면에 출력합니다.

	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	private Context init;
	private DataSource ds;	// 여러개의 conn을 가지고 있다가 하나씩 내어주고,
							// conn.close()하면 객체를 돌려받아서, 다시 사용할 수 있도록 처리한다
							// 컨베이어 벨트 방식으로 conn 객체를 재활용한다
	
	private static Member2DAO instance = new Member2DAO();
	
	public static Member2DAO getInstance() {
		return instance;
	}
	
	public Member2DAO() {
		try {
			init = (Context) new InitialContext();
			ds = (DataSource) init.lookup("java:comp/env/jdbc/oracle");
		} catch (NamingException e) {
			e.printStackTrace();
		} finally {
			if(conn != null) try { conn.close(); } catch (SQLException e) {}
		}
	}// end of constructor
	
	// select 하는 경우, rs의 데이터를 DTO에 맞게 맵핑하는 과정이 필요하다
	// 전체, 검색, 단일 조회 모든 경우에 사용할 수 있도록 함수를 만들어 둔다
	private Member2DTO mapping(ResultSet rs) throws SQLException{//함수에서 try catch를 열기때문에 전가 시켜도 됨
		Member2DTO dto = new Member2DTO();
		dto.setIdx(rs.getInt("idx"));
		dto.setUserid(rs.getString("userid"));
		dto.setUserpw(rs.getString("userpw"));
		dto.setUsername(rs.getString("username"));
		dto.setGender(rs.getString("gender"));
		return dto;
	}
	
	// 모든 함수에서 rs, pstmt, conn을 순서대로 닫아준다 (열기의 역순으로 닫는다)_
	private void close() {
		try {
			if(rs != null) 		rs.close();
			if(pstmt != null) 	pstmt.close();
			if(conn != null) 	conn.close();
			// dataSource에서 가져온 conn은 close()하면 회수된다
		} catch(Exception e) {}
	}
	
	public ArrayList<Member2DTO> selectList() {
		ArrayList<Member2DTO> list = new ArrayList<Member2DTO>();
		String sql = "select * from member3 order by idx";
		
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				Member2DTO dto = mapping(rs);
				list.add(dto);
//				list.add(mapping(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally { close(); }
		return list;
	}
	
	public Member2DTO selectOne(int idx) {
		String sql = "select * from member3 where idx=" + idx;
		Member2DTO dto = null;
		
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				dto = mapping(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally { close();}
		return dto;
	}
	
	public int insert(Member2DTO dto) {
		int row = 0;
		String sql = "insert into member3 (userid, userpw, username, gender) values(?, ?, ?, ?)";
		
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getUserid());
			pstmt.setString(2, dto.getUserpw());
			pstmt.setString(3, dto.getUsername());
			pstmt.setString(4, dto.getGender());
			row = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally { close();}
		return row;
	}
	
	public int update(Member2DTO dto) {
		int row = 0;
		String sql = "update member3 set userid=?, userpw=?, username=?, gender=? where idx=?";
		
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getUserid());
			pstmt.setString(2, dto.getUserpw());
			pstmt.setString(3, dto.getUsername());
			pstmt.setString(4, dto.getGender());
			pstmt.setInt(5, dto.getIdx());
			row = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally { close();}
		
		return row;
	}
	
	public int delete(int idx) {
		int row = 0;
		String sql = "delete from member3 where idx=" + idx;
		
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);	
			row = pstmt.executeUpdate();		
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
			}
		}
		return row;
	}
	
}

header.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page import="member2.*" %>
<% request.setCharacterEncoding("UTF-8"); %>
<c:set var="cpath" value="${pageContext.request.contextPath }"/>
<c:set var="dao" value="${Member2DAO.getInstance() }"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
	table {
	  width: 100%;
	  border: 1px solid black;
	}
	
	th, td {
	  border: 1px solid black;
	  padding: 5px;
	}

</style>
</head>
<body>
<header>
	<h1><a href="${cpath }">회원 목록</a></h1>
	<nav>
		<ul>
			<li><a href="${cpath }/list.jsp">목록</a></li>
			<li><a href="${cpath }/add.jsp">추가</a></li>
			<li><a href="${cpath }/modify.jsp">수정</a></li>
			<li><a href="${cpath }/delete.jsp">삭제</a></li>
		</ul>
	</nav>
</header>

index

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

</body>
</html>

list

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<h3>회원 목록</h3>

<table>
	<thead>
		<tr>
			<th>IDX</th>
			<th>USERID</th>
			<th>USERPW</th>
			<th>USERNAME</th>
			<th>GENDER</th>
		</tr>
	</thead>
	<tbody>
		<c:forEach var="dto" items="${dao.selectList() }">
			<tr>
				<td>${dto.idx }</td>
				<td>
					<a href="${cpath }/detail.jsp?idx=${dto.idx }">${dto.userid }</a>
				</td>
				<td>${dto.userpw }</td>
				<td>${dto.username }</td>
				<td>${dto.gender }</td>
			</tr>
		</c:forEach>
	</tbody>
</table>

</body>
</html>

add.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<h1>추가</h1>

<form method="POST" action="add-action.jsp">
	<fieldset>
		<legend>회원정보</legend>
			<p><input type="text" name="userid" placeholder="ID"></p>
			<p><input type="text" name="userpw" placeholder="PW"></p>
			<p><input type="text" name="username" placeholder="NAME"></p>
	</fieldset>
	<fieldset>
		<legend>성별</legend>
			<label><input type="radio" name="gender" value="남성">남성</label>
			<label><input type="radio" name="gender" value="여성">여성</label>
	</fieldset>
	<p><input type="submit"></p>
</form>

</body>
</html>

add-action.jsp

<%@page import="java.lang.ProcessBuilder.Redirect"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<jsp:useBean id="user" class="member2.Member2DTO"/>
<jsp:setProperty property="*" name="user" />

<c:set var="row" value="${dao.insert(user) }" />

<c:if test="${row != 0 }">
	<h3>추가되었습니다</h3>
	<a href="${cpath }/list.jsp"><button>목록으로</button></a>
</c:if>

<c:if test="${row == 0 }">
	<h3>추가 실패</h3>
	<button onclick="history.back()">이전으로</button>
</c:if>
</body>
</html>

modify.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<h3>수정</h3>

<table>
	<thead>
		<tr>
			<th>USERID</th>
			<th>USERPW</th>
			<th>USERNAME</th>
			<th>GENDER</th>
		</tr>
	</thead>
	<tbody>
		<c:forEach var="dto" items="${dao.selectList() }">
			<tr>
				<td>${dto.userid }</td>
				<td>${dto.userpw }</td>
				<td>${dto.username }</td>
				<td>${dto.gender }</td>
				<td>
					<a href="modify-form.jsp?idx=${dto.idx }"><button>수정</button></a>
				</td>
			</tr>
		</c:forEach>
	</tbody>
</table>

</body>
</html>

modify-form.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<jsp:useBean id="user" class="member2.Member2DTO"/>
<c:set var="dto" value="${dao.selectOne(param.idx) }" />

<h3>수정</h3>
<form method="POST" action="modify-action.jsp">
	<fieldset>
		<legend>회원정보</legend>
			<p><input type="text" name="userid" placeholder="ID" value="${dto.userid }"></p>
			<p><input type="text" name="userpw" placeholder="PW" value="${dto.userpw }"></p>
			<p><input type="text" name="username" placeholder="NAME" value="${dto.username }"></p>
	</fieldset>
	<fieldset>
		<legend>성별</legend>
			<label><input type="radio" name="gender" value="남성" ${dto.gender == ('남성') ? 'checked' : '' }>남성</label>
			<label><input type="radio" name="gender" value="여성" ${dto.gender == ('남성') ? 'checked' : '' }>여성</label>
	</fieldset>
	<p><input type="submit"></p>
	<p><input type="hidden" name="idx" value="${dto.idx }"></p>

</form>

</body>
</html>

modify-action.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<jsp:useBean id="user" class="member2.Member2DTO"/>
<jsp:setProperty property="*" name="user" />

<c:set var="row" value="${dao.update(user) }" />

<c:if test="${row != 0 }">
	<h3>수정되었습니다</h3>
	<a href="${cpath }/list.jsp"><button>목록으로</button></a>
</c:if>

<c:if test="${row == 0 }">
	<h3>수정 실패</h3>
	<button onclick="history.back()">이전으로</button>
</c:if>


</body>
</html>

delete.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<h3>회원 삭제</h3>

<table>
	<thead>
		<tr>
			<th>IDX</th>
			<th>USERID</th>
			<th>USERPW</th>
			<th>USERNAME</th>
			<th>GENDER</th>
			<th></th>
		</tr>
	</thead>
	<tbody>
		<c:forEach var="dto" items="${dao.selectList() }">
			<tr>
				<td>${dto.idx }</td>
				<td>
					<a href="${cpath }/detail.jsp?idx=${dto.idx }">${dto.userid }</a>
				</td>
				<td>${dto.userpw }</td>
				<td>${dto.username }</td>
				<td>${dto.gender }</td>
				<td><button class="deleteBtn" idx="${dto.idx }">삭제</button></td>
			</tr>
		</c:forEach>
	</tbody>
</table>

<script>
	
	function deleteHandler() {
		const idx = this.getAttribute('idx')
		const flag = confirm('정말 ' + idx + '번 회원님을 삭제하시겠습니까?')
		
		if(flag) {
			location.href = '${cpath}/delete-action.jsp?idx=' + idx
		}
	}
	
	const btnList = document.querySelectorAll('.deleteBtn')
	

	btnList.forEach(btn => btn.addEventListener('click', deleteHandler))
	
</script>

</body>
</html>

delete-action.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<c:set var="row" value="${dao.delete(param.idx) }" />

<c:if test="${row != 0 }">
	<% response.sendRedirect("list.jsp"); %>
</c:if>
<c:if test="${row == 0 }">
	<h3>삭제 실패</h3>
	<button onclick="history.back()">이전으로</button>
</c:if>

</body>
</html>

SQL 구문

--------------------------------------------------------
--  파일이 생성됨 - 목요일-12월-22-2022   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Sequence MEMBER2_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "C##ITBANK"."MEMBER2_SEQ"  MINVALUE 1 MAXVALUE 99999999 INCREMENT BY 1 START WITH 1020 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
--------------------------------------------------------
--  DDL for Table MEMBER2
--------------------------------------------------------

  CREATE TABLE "C##ITBANK"."MEMBER2" 
   (	"IDX" NUMBER DEFAULT "C##ITBANK"."MEMBER2_SEQ"."NEXTVAL", 
	"USERID" VARCHAR2(100 BYTE), 
	"USERPW" VARCHAR2(255 BYTE), 
	"USERNAME" VARCHAR2(100 BYTE), 
	"JOINDATE" DATE DEFAULT sysdate, 
	"GENDER" VARCHAR2(20 BYTE), 
	"PROFILEIMG" VARCHAR2(255 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into C##ITBANK.MEMBER2
SET DEFINE OFF;
Insert into C##ITBANK.MEMBER2 (IDX,USERID,USERPW,USERNAME,JOINDATE,GENDER,PROFILEIMG) values (1019,'root23','1234','김진형',to_date('22/12/09','RR/MM/DD'),'남성',null);
Insert into C##ITBANK.MEMBER2 (IDX,USERID,USERPW,USERNAME,JOINDATE,GENDER,PROFILEIMG) values (1005,'test','1234','테스트',to_date('22/12/09','RR/MM/DD'),'남성',null);
Insert into C##ITBANK.MEMBER2 (IDX,USERID,USERPW,USERNAME,JOINDATE,GENDER,PROFILEIMG) values (1006,'test2','1234','테스트',to_date('22/12/09','RR/MM/DD'),'여성',null);
Insert into C##ITBANK.MEMBER2 (IDX,USERID,USERPW,USERNAME,JOINDATE,GENDER,PROFILEIMG) values (1007,'test3','1234','테스트',to_date('22/12/09','RR/MM/DD'),'남성',null);
--------------------------------------------------------
--  DDL for Index SYS_C007315
--------------------------------------------------------

  CREATE UNIQUE INDEX "C##ITBANK"."SYS_C007315" ON "C##ITBANK"."MEMBER2" ("IDX") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index SYS_C007316
--------------------------------------------------------

  CREATE UNIQUE INDEX "C##ITBANK"."SYS_C007316" ON "C##ITBANK"."MEMBER2" ("USERID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table MEMBER2
--------------------------------------------------------

  ALTER TABLE "C##ITBANK"."MEMBER2" MODIFY ("USERID" NOT NULL ENABLE);
  ALTER TABLE "C##ITBANK"."MEMBER2" MODIFY ("USERPW" NOT NULL ENABLE);
  ALTER TABLE "C##ITBANK"."MEMBER2" MODIFY ("USERNAME" NOT NULL ENABLE);
  ALTER TABLE "C##ITBANK"."MEMBER2" ADD CHECK (gender in('남성', '여성')) ENABLE;
  ALTER TABLE "C##ITBANK"."MEMBER2" ADD PRIMARY KEY ("IDX")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
  ALTER TABLE "C##ITBANK"."MEMBER2" ADD UNIQUE ("USERID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
저작자표시

'개발자 > JSP' 카테고리의 다른 글

[JDBC] DBCP(DataBase Conncetion Pool)  (0) 2022.12.12
[JDBC] SingleTon 패턴  (0) 2022.12.12
[JDBC] XML  (0) 2022.12.12
[JDBC] 시퀀스를 활용하여 idx값 출력 (제약조건++)  (0) 2022.12.09
[SQL] DB Table 제약조건  (0) 2022.12.09

'개발자/JSP'의 다른글

  • 현재글[jdbc] 싱글톤을 적용한 CRUD

관련글

티스토리툴바