고정 헤더 영역
상세 컨텐츠
본문
회원관리 DB 만들기
DataBase
SQL> show user;
USER은 "SCOTT"입니다
SQL> create sequence seq_mem_idx;
시퀀스가 생성되었습니다.
SQL> create table members(
2 idx int,
3 name varchar2(50) not null,
4 id varchar2(50) not null unique,
5 pwd varchar2(50) not null,
6 email varchar2(50),
7 addr varchar2(200));
테이블이 생성되었습니다.
SQL> alter table members add constraint pk_mem_idx primary key(idx);
테이블이 변경되었습니다.
SQL> insert into members values(seq_mem_idx.nextVal, '홍길동', 'one', '1234', 'o
ne@korea.com', '서울시 관악구');
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
MembersDAO.java
DAO 파일, 클래스 파일
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import service.DBService;
import vo.MembersVO;
public class MembersDAO {
static MembersDAO single = null;
public static MembersDAO getInstance() {
if (single == null)
single = new MembersDAO();
return single;
}
public int insert(MembersVO vo) {
// TODO Auto-generated method stub
int res = 0;
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "insert into members values(seq_mem_idx.nextVal,?,?,?,?,?)";
try {
//1.Connection획득
conn = DBService.getInstance().getConnection();
//2.명령처리객체 획득
pstmt = conn.prepareStatement(sql);
//3.pstmt parameter 채우기
pstmt.setString(1, vo.getName());
pstmt.setString(2, vo.getId());
pstmt.setString(3, vo.getPwd());
pstmt.setString(4, vo.getEmail());
pstmt.setString(5, vo.getAddr());
//4.DB로 전송(res:처리된행수)
res = pstmt.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return res;
}
public int update(MembersVO vo) {
// TODO Auto-generated method stub
int res = 0;
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "update members set name=?, id=?, pwd=?, email=?, addr=? where idx=?";
try {
// 1.Connection획득
conn = DBService.getInstance().getConnection();
// 2.명령처리객체 획득
pstmt = conn.prepareStatement(sql);
// 3.pstmt parameter 채우기
pstmt.setString(1, vo.getName());
pstmt.setString(2, vo.getId());
pstmt.setString(3, vo.getPwd());
pstmt.setString(4, vo.getEmail());
pstmt.setString(5, vo.getAddr());
pstmt.setInt(6, vo.getIdx());
// 4.DB로 전송(res:처리된행수)
res = pstmt.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return res;
}
public int delete(int idx) {
// TODO Auto-generated method stub
int res = 0;
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "delete from members where idx=?";
try {
// 1.Connection획득
conn = DBService.getInstance().getConnection();
// 2.명령처리객체 획득
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, idx);
// 4.DB로 전송(res:처리된행수)
res = pstmt.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return res;
// 값 추가 코드 생성
}
public List<MembersVO> selectList() {
List<MembersVO> list = new ArrayList<MembersVO>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from members order by idx DESC";
try {
//1.Connection얻어온다
conn = DBService.getInstance().getConnection();
//2.명령처리객체정보를 얻어오기
pstmt = conn.prepareStatement(sql);
//3.결과행 처리객체 얻어오기
rs = pstmt.executeQuery();
while (rs.next()) {
MembersVO vo = new MembersVO();
//현재레코드값=>Vo저장
vo.setIdx(rs.getInt("idx"));
vo.setName(rs.getString("name"));
vo.setId(rs.getString("id"));
vo.setPwd(rs.getString("pwd"));
vo.setEmail(rs.getString("email"));
vo.setAddr(rs.getString("addr"));
//ArrayList추가
list.add(vo);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return list;
}
}
DBService.java
DB연결, 클래스파일
package service;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBService {
static DBService single = null;
public static DBService getInstance() {
if (single == null)
single = new DBService();
return single;
}// 템플릿으로 생성하는 싱글톤 코드
DataSource ds;
public DBService() {
try {
InitialContext ic = new InitialContext();
ds = (DataSource)ic.lookup("java:comp/env/jdbc/oracle_test");
} catch (NamingException e) {
e.printStackTrace();
}
}
public Connection getConnection() {
Connection conn = null;
try {
conn = ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
MembersVO.java
VO파일, 클래스파일
package vo;
public class MembersVO {
int idx;
String name, id, pwd, email, addr;
public int getIdx() {
return idx;
}
public void setIdx(int idx) {
this.idx = idx;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
}
member_del.jsp
삭제 파일 jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="vo.MembersVO"%>
<%@page import="dao.MembersDAO"%>
<!DOCTYPE html>
<% request.setCharacterEncoding("utf-8");
int idx = Integer.parseInt(request.getParameter("idx"));
int res = MembersDAO.getInstance().delete(idx);
response.sendRedirect("members_list.jsp");
%>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
</body>
</html>
members_list.jsp
회원 목록 출력
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="vo.MembersVO"%>
<%@page import="java.util.List"%>
<%@page import="dao.MembersDAO"%>
<!DOCTYPE html>
<%
// 회원목록 가져오기
MembersDAO dao = MembersDAO.getInstance();
List<MembersVO> members_list = dao.selectList();
%>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
table {
boder: 1px solid black;
border_collapse: collapse;
}
th {
width: 80px;
}
#disp, #disp2 {
position: absolute; /*브라우저 기준 절대좌표*/
width: 280px;
height: 180px;
background: #aac;
box-shadow: 5px 5px #ccc;
}
</style>
<script type="text/javascript">
function del(idx) {
if ((confirm("정말 삭제하나요???")) == false) {
return;
}
location.href = 'members_del.jsp?idx=' + idx;
}
// 입력창 보이기 메서드
function show_form() {
var disp = document.getElementById("disp");
// 폼을 브라우저 중앙에 배치
disp.style.left = (window.innerWidth / 2 - 140) + "px";
disp.style.top = (window.innerHeight / 2 - 90) + "px";
disp.style.display = 'block';
} // show_form()
// 등록하기 메서드
function send(f) {
var name = f.name.value.trim();
var id = f.id.value.trim();
var pwd = f.pwd.value.trim();
var email = f.email.value.trim();
var addr = f.addr.value.trim();
if (name == "" || id == "" || pwd == "" || email == "" || addr == "") {
alert("빈 항목이 있습니다.");
return;
}
var em = /^[0-9a-zA-Z]([-_.]?[0-9a-zA-Z])*@[0-9a-zA-Z]([-_.]?[0-9a-zA-Z])*.[a-zA-Z]{2,3}$/i;
if (!em.test(email)) {
alert("이메일 형식이 올바르지 않습니다.");
f.email.value = "";
f.email.focus();
return;
}
f.action = "members_regi.jsp";
f.submit();
} // send()
// 등록 취소 메서드
function my_cancel() {
var disp = document.getElementById("disp");
var disp2 = document.getElementById("disp2");
disp.style.display = "none";
disp2.style.display = "none";
// getElementsByName은 같은 name값을 배열로 가져오므로 0번 인덱스값을 사용
// getElementById로 쓰는게 더 좋긴 함
document.getElementsByName("name")[0].value = "";
document.getElementsByName("id")[0].value = "";
document.getElementsByName("pwd")[0].value = "";
document.getElementsByName("email")[0].value = "";
document.getElementsByName("addr")[0].value = "";
} //my_cancel()
function send2(f) {
var idx = f.idx.value.trim();
var name2 = f.name2.value.trim();
var id2 = f.id2.value.trim();
var pwd2 = f.pwd2.value.trim();
var email2 = f.email2.value.trim();
var addr2 = f.addr2.value.trim();
if (name2 == "" || id2 == "" || pwd2 == "" || email2 == "" || addr2 == "") {
alert("빈 항목이 있습니다.");
return;
}
var em = /^[0-9a-zA-Z]([-_.]?[0-9a-zA-Z])*@[0-9a-zA-Z]([-_.]?[0-9a-zA-Z])*.[a-zA-Z]{2,3}$/i;
if (!em.test(email2)) {
alert("이메일 형식이 올바르지 않습니다.");
f.email2.value = "";
f.email2.focus();
return;
}
f.action = "members_update.jsp";
f.submit();
}
function update(idx, name, id, pwd, email, addr) {
var disp2 = document.getElementById("disp2");
disp2.style.left = (window.innerWidth / 2 - 140) + "px";
disp2.style.top = (window.innerHeight / 2 - 90) + "px";
disp2.style.display = "block";
document.getElementsByName("idx")[0].value = idx;
document.getElementsByName("name2")[0].value = name;
document.getElementsByName("id2")[0].value = id;
document.getElementsByName("pwd2")[0].value = pwd;
document.getElementsByName("email2")[0].value = email;
document.getElementsByName("addr2")[0].value = addr;
}
</script>
</head>
<body>
<table border="1">
<caption>::회원목록::</caption>
<tr>
<th>회원번호</th>
<th>이름</th>
<th>아이디</th>
<th>비밀번호</th>
<th>이메일</th>
<th>주소</th>
</tr>
<%
for (int i = 0; i < members_list.size(); i++) {
MembersVO vo = members_list.get(i);
%>
<tr>
<td><%=vo.getIdx()%></td>
<td><%=vo.getName()%></td>
<td><%=vo.getId()%></td>
<td><%=vo.getPwd()%></td>
<td><%=vo.getEmail()%></td>
<td><%=vo.getAddr()%></td>
<td><input type="button" value="삭제" onclick="del('<%=vo.getIdx()%>')"></td>
<td><input type="button" value="수정"
onclick="update
('<%=vo.getIdx()%>',
'<%=vo.getName()%>',
'<%=vo.getId()%>',
'<%=vo.getPwd()%>',
'<%=vo.getEmail()%>',
'<%=vo.getAddr()%>')"></td>
</tr>
<%
}
%>
<tr>
<td colspan="9"><input type="button" value="추가하기" id="bt_append"
onclick="show_form();" /></td>
</tr>
</table>
<div id="disp" style="display: none">
<form>
<table style="width: 100%">
<caption>::초 간단 회원가입::</caption>
<tr>
<th>이름</th>
<td><input name="name"></td>
</tr>
<tr>
<th>아이디</th>
<td><input name="id"></td>
</tr>
<tr>
<th>비밀번호</th>
<td><input name="pwd"></td>
</tr>
<tr>
<th>이메일</th>
<td><input name="email"></td>
</tr>
<tr>
<th>주소</th>
<td><input name="addr"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="button" value="등록"
onclick="send(this.form);"> <input type="button"
value="취소" onclick="my_cancel();"></td>
</tr>
</table>
</form>
</div>
<div id="disp2" style="display: none">
<form>
<input type="hidden" name="idx">
<table style="width: 100%">
<caption>:: 항목을 수정하세요 ::</caption>
<tr>
<th>이름</th>
<td><input name="name2"></td>
</tr>
<tr>
<th>아이디</th>
<td><input name="id2"></td>
</tr>
<tr>
<th>비밀번호</th>
<td><input name="pwd2"></td>
</tr>
<tr>
<th>이메일</th>
<td><input name="email2"></td>
</tr>
<tr>
<th>주소</th>
<td><input name="addr2"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value="수정"
onclick="send2(this.form);"> <input type="button"
value="취소" onclick="my_cancel();"></td>
</tr>
</table>
</form>
</div>
</body>
</html>
members_regi.jsp
회원 등록
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="vo.MembersVO"%>
<%@page import="dao.MembersDAO"%>
<!DOCTYPE html>
<%
// 전송 버튼 클릭 시 넘어온 파라미터를 받는다.
String name = request.getParameter("name");
String id = request.getParameter("id");
String pwd = request.getParameter("pwd");
String email = request.getParameter("email");
String addr = request.getParameter("addr");
// 받아온 정보를 vo에 저장
MembersVO vo = new MembersVO();
vo.setName(name);
vo.setId(id);
vo.setPwd(pwd);
vo.setEmail(email);
vo.setAddr(addr);
// DB에 값 추가하기.
int res = MembersDAO.getInstance().insert(vo); // insert 메서드 만들자
// 갱신된 데이터를 다시 로드한다.
response.sendRedirect("members_list.jsp");
%>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
</body>
</html>
'FrontEnd > JSP (JavaServerPage)' 카테고리의 다른 글
| JSP JSTL(JSP Standard Tag Library) (0) | 2022.08.25 |
|---|---|
| JSP EL(Expression Language) (0) | 2022.08.25 |
| JSP (DB연결, 성적 테스트) (0) | 2022.08.19 |
| JSP Templates(템플릿 이용, 부서 별 사원 입력 및 출력) (0) | 2022.08.19 |
| JSP (DB 연결, 부서 별 사원 입력 및 출력) (0) | 2022.08.19 |
