회원 정보 조회
커넥션 풀 이용
pro07/src/sec02/ex01/MemberDAO.java
package sec02.ex01;
...
public class MemberDAO {
/* 사용하지 않게 된 직접 연결용 코드
private static final String driver = "oracle.jdbc.driver.OracleDriver";
private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
private static final String user = "scott";
private static final String pwd = "tiger";
*/
private Connection con;
private PreparedStatement pstmt;
private DataSource dataFactory;
public MemberDAO() {
try {
// JNDI 접근용 기본 경로 지정 및 DataSource 받아오기
Context ctx = new InitialContext();
Context envContext = (Context) ctx.lookup("java:/comp/env");
dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
} catch (Exception e) { e.printStackTrace(); }
}
public List<MemberVO> listMembers() {
List<MemberVO> list = new ArrayList<MemberVO>();
try {
// 데이터베이스 연결
con = dataFactory.getConnection();
String query = "select * from t_member ";
System.out.println("prepareStatememt: " + query);
pstmt = con.prepareStatement(query);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String id = rs.getString("id");
String pwd = rs.getString("pwd");
String name = rs.getString("name");
String email = rs.getString("email");
Date joinDate = rs.getDate("joinDate");
MemberVO vo = new MemberVO();
vo.setId(id);
vo.setPwd(pwd);
vo.setName(name);
vo.setEmail(email);
vo.setJoinDate(joinDate);
list.add(vo);
}
rs.close();
pstmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
} 회원 정보 등록
pro07/WebContent/memberForm.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 가입창</title>
<script type="text/javascript">
function fn_sendMember() {
/* name을 통해 입력 데이터 접근 */
var frmMember = document.frmMember;
var id = frmMember.id.value;
var pwd = frmMember.pwd.value;
var name = frmMember.name.value;
var email = frmMember.email.value;
/* 데이터 유효성 검사 */
if (id.length == 0 || id == "") { alert("아이디는 필수입니다."); }
else if (pwd.length == 0 || pwd == "") { alert("비밀번호는 필수입니다."); }
else if (name.length == 0 || name == "") { alert("이름은 필수입니다."); }
else if (email.length == 0 || email == "") { alert("이메일은 필수입니다."); }
else { /* 유효 데이터 전송 */
frmMember.method = "post"; //전송 방식 지정
frmMember.action = "member3"; //서블릿 매핑명 지정
frmMember.submit();
}
}
</script>
</head>
<body>
<form name="frmMember">
<table>
<th>회원 가입창</th>
<tr>
<td>아이디</td>
<td><input type="text" name="id"></td>
</tr>
<tr>
<td>비밀번호</td>
<td><input type="password" name="pwd"></td>
</tr>
<tr>
<td>이름</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>이메일</td>
<td><input type="text" name="email"></td>
</tr>
</table>
<input type="button" value="가입하기" onclick="fn_sendMember()">
<input type="reset" value="다시 입력">
<!-- 서블릿만 볼 수 있는 정보 -->
<input type="hidden" name="command" value="addMember" />
</form>
</body>
</html>pro07/sec02/ex02/MemberServlet.java
package sec02.ex02;
...
@WebServlet("/member3")
public class MemberServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doHandle(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doHandle(request, response);
}
private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
MemberDAO dao = new MemberDAO();
PrintWriter out = response.getWriter();
/* command 정보에 따라 작업 수행 */
String command = request.getParameter("command");
if (command != null && command.equals("addMember")) { //회원 등록
/* 데이터 받아오기 */
String _id = request.getParameter("id");
String _pwd = request.getParameter("pwd");
String _name = request.getParameter("name");
String _email = request.getParameter("email");
/* 객체에 데이터 저장 및 전송 */
MemberVO vo = new MemberVO();
vo.setId(_id);
vo.setPwd(_pwd);
vo.setName(_name);
vo.setEmail(_email);
dao.addMember(vo);
} else if (command != null && command.equals("delMember")) { //회원 삭제
String id = request.getParameter("id");
dao.delMember(id);
}
/* 등록된 회원 정보 띄우기 */
List<MemberVO> list = dao.listMembers();
out.print("<html><body>");
out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td><td >삭제</td></tr>");
for (int i = 0; i < list.size(); i++) {
MemberVO memberVO = (MemberVO) list.get(i);
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
String email = memberVO.getEmail();
Date joinDate = memberVO.getJoinDate();
out.print("<tr><td>" + id + "</td><td>" + pwd + "</td><td>" + name + "</td><td>" + email + "</td><td>" + joinDate + "</td><td>" + "<a href='/pro07/member3?command=delMember&id=" + id + "'> 삭제 </a></td></tr>");
}
out.print("</table></body></html>");
out.print("<a href='/pro07/memberForm.html'>새 회원 등록하기 </a>");
}
}pro07/sec02/ex02/MemberDAO.java
package sec02.ex01;
...
public class MemberDAO {
private Connection con;
private PreparedStatement pstmt;
private DataSource dataFactory;
public MemberDAO() {
try {
// JNDI 접근용 기본 경로 지정 및 DataSource 받아오기
Context ctx = new InitialContext();
Context envContext = (Context) ctx.lookup("java:/comp/env");
dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
} catch (Exception e) { e.printStackTrace(); }
}
public List<MemberVO> listMembers() {
List<MemberVO> list = new ArrayList<MemberVO>();
try {
// 데이터베이스 연결
con = dataFactory.getConnection();
String query = "select * from t_member ";
System.out.println("prepareStatememt: " + query);
pstmt = con.prepareStatement(query);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String id = rs.getString("id");
String pwd = rs.getString("pwd");
String name = rs.getString("name");
String email = rs.getString("email");
Date joinDate = rs.getDate("joinDate");
MemberVO vo = new MemberVO();
vo.setId(id);
vo.setPwd(pwd);
vo.setName(name);
vo.setEmail(email);
vo.setJoinDate(joinDate);
list.add(vo);
}
rs.close();
pstmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
public void addMember(MemberVO memberVO) {
try {
// DataSource를 이용한 데이터베이스 연결
con = dataFactory.getConnection();
// 데이터 받아오기
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
String email = memberVO.getEmail();
// 쿼리문 구성
String query = "insert into t_member";
query += " (id,pwd,name,email)";
query += " values(?,?,?,?)";
System.out.println("prepareStatememt: " + query);
pstmt = con.PreparedStatement(query);
// 쿼리문에 들어갈 데이터 구성
pstmt.setString(1, id);
pstmt.setString(2, pwd);
pstmt.setString(3, name);
pstmt.setString(4, email);
pstmt.executeUpdate();
// 데이터 추가
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}회원 정보 삭제
pro07/src/sec02/ex02/MemberServlet3.java
package sec02.ex02;
...
@WebServlet("/member3")
public class MemberServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doHandle(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doHandle(request, response);
}
private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
MemberDAO dao = new MemberDAO();
PrintWriter out = response.getWriter();
/* command 정보에 따라 작업 수행 */
String command = request.getParameter("command");
if (command != null && command.equals("addMember")) { //회원 등록
/* 데이터 받아오기 */
String _id = request.getParameter("id");
String _pwd = request.getParameter("pwd");
String _name = request.getParameter("name");
String _email = request.getParameter("email");
/* 객체에 데이터 저장 및 전송 */
MemberVO vo = new MemberVO();
vo.setId(_id);
vo.setPwd(_pwd);
vo.setName(_name);
vo.setEmail(_email);
dao.addMember(vo);
} else if (command != null && command.equals("delMember")) { //회원 삭제
String id = request.getParameter("id");
dao.delMember(id);
}
/* 등록된 회원 정보 띄우기 */
List<MemberVO> list = dao.listMembers();
out.print("<html><body>");
out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td><td >삭제</td></tr>");
for (int i = 0; i < list.size(); i++) {
MemberVO memberVO = (MemberVO) list.get(i);
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
int age = memberVO.getAge();
Date joinDate = memberVO.getJoinDate();
// 삭제 선택 시 'command=delMember'와 회원ID를 서블릿으로 전송
out.print("<tr><td>" + id + "</td><td>" + pwd + "</td><td>" + name + "</td><td>" + email + "</td><td>" + joinDate + "</td><td>" + "<a href='/pro07/member3?command=delMember&id=" + id + "'> 삭제 </a></td></tr>");
}
out.print("</table></body></html>");
out.print("<a href='/pro07/memberForm.html'>새 회원 등록하기</a");
}
}
pro07/src/sec02/ex02/MemberDAO.java
package sec02.ex01;
...
public class MemberDAO {
private Connection con;
private PreparedStatement pstmt;
private DataSource dataFactory;
public MemberDAO() {
try {
// JNDI 접근용 기본 경로 지정 및 DataSource 받아오기
Context ctx = new InitialContext();
Context envContext = (Context) ctx.lookup("java:/comp/env");
dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
} catch (Exception e) { e.printStackTrace(); }
}
public List<MemberVO> listMembers() {
List<MemberVO> list = new ArrayList<MemberVO>();
try {
// 데이터베이스 연결
con = dataFactory.getConnection();
String query = "select * from t_member ";
System.out.println("prepareStatememt: " + query);
pstmt = con.prepareStatement(query);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String id = rs.getString("id");
String pwd = rs.getString("pwd");
String name = rs.getString("name");
String email = rs.getString("email");
Date joinDate = rs.getDate("joinDate");
MemberVO vo = new MemberVO();
vo.setId(id);
vo.setPwd(pwd);
vo.setName(name);
vo.setEmail(email);
vo.setJoinDate(joinDate);
list.add(vo);
}
rs.close();
pstmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
public void addMember(MemberVO memberVO) {
try {
// DataSource를 이용한 데이터베이스 연결
con = dataFactory.getConnection();
// 데이터 받아오기
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
String email = memberVO.getEmail();
// 쿼리문 구성
String query = "insert into t_member";
query += " (id,pwd,name,email)";
query += " values(?,?,?,?)";
System.out.println("prepareStatememt: " + query);
pstmt = con.PreparedStatement(query);
// 쿼리문 인자 구성
pstmt.setString(1, id);
pstmt.setString(2, pwd);
pstmt.setString(3, name);
pstmt.setString(4, email);
pstmt.executeUpdate();
// 데이터 추가
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
public void delMember(String id) {
try {
con = dataFactory.getConnection();
// DELETE 쿼리문 구성
String query = "delete from t_member" + " where id=?";
System.out.println("prepareStatememt:" + query);
pstmt = con.PreparedStatement(query);
// 쿼리문 인자 구성
pstmt.setString(1, id);
// 쿼리문 실행
pstmt.executeUpdate();
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
*자바 웹을 다루는 기술
CS 메모장