본문 바로가기
학습/JSP

10. JSP파일에 출력하기

by Elfen Lied 2022. 11. 8.
반응형

- 오라클 테이블 생성

-- 회원테이블 생성
CREATE TABLE T_MEMBER(
    MNO INT PRIMARY KEY,
    ID VARCHAR2(10) NOT NULL UNIQUE,
    PWD VARCHAR2(10),
    NAME VARCHAR2(50),
    EMAIL VARCHAR2(50),
    JOINDATE DATE DEFAULT SYSDATE
);

-- 시퀀스
CREATE SEQUENCE MNO_SEQ;

-- 데이터 넣기
INSERT INTO T_MEMBER(MNO, ID, PWD, NAME, EMAIL)
VALUES(MNO_SEQ.NEXTVAL, 'kim', '1212', '김범수', 'kim@gmail');
INSERT INTO T_MEMBER(MNO, ID, PWD, NAME, EMAIL)
VALUES(MNO_SEQ.NEXTVAL, 'na', '2345', '나얼', 'na@gmail');
INSERT INTO T_MEMBER(MNO, ID, PWD, NAME, EMAIL)
VALUES(MNO_SEQ.NEXTVAL, 'park', '6666', '박효신', 'park@gmail');

COMMIT;

시퀀스(SEQUENCE)

  • 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동으로 생성함
  • 유일한 값을 생성해주는 오라클 객체.
  • 보통 PRIMARY KEY 값을 생성할때 사용함

 

 

 

 

- src/main/java에 인터페이스, 서블릿과 클래스 만들기

webapp에 memberList.jsp, index.html, memberForm.html만들기

항상 webapp \ WEB-INF \ lib 폴더 안에 jar 파일 넣어두기

  • MemberDao (인터페이스)
  • MemberServlet, MemberViews (서블릿)
  • MemberDaoImpl, MemberVO (클래스) 

 

- MemberDao

package sec04.ex04;

import java.util.List;

public interface MemberDao {
	List<MemberVO> memberList();
	void addMember(MemberVO vo);
	void delMember(int mno);
}

 

- MemberDaoImpl

현재는 finally 구문을 넣어서 복잡하지만 나중에 다른 식으로 할 예정.

close()를 해서 리소스를 해소해줘야 지연 현상 없어짐.

package sec04.ex04;

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 javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class MemberDaoImpl implements MemberDao {
	private DataSource dataSource;

	public MemberDaoImpl() {
		try {
			Context ctx = new InitialContext();
			Context env = (Context) ctx.lookup("java:/comp/env");
			dataSource = (DataSource) env.lookup("jdbc/oracle");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<MemberVO> memberList() { // 회원조회
		List<MemberVO> list = new ArrayList<MemberVO>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement("SELECT * FROM T_MEMBER");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				MemberVO vo = new MemberVO(rs.getInt("mno"), rs.getString("id"), rs.getString("pwd"),
						rs.getString("name"), rs.getString("email"));
				vo.setJoinDate(rs.getDate("joinDate"));
				list.add(vo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally { // finally start
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		} // finally end

		return list;
	}
 
	@Override
	public void addMember(MemberVO vo) { // 회원가입
		Connection conn = null;
		PreparedStatement pstmt = null;
		String query = "INSERT INTO T_MEMBER(MNO, ID, PWD, NAME, EMAIL) ";
		query += "VALUES(MNO_SEQ.NEXTVAL, ?, ?, ?, ?)";

		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, vo.getId());
			pstmt.setString(2, vo.getPassword());
			pstmt.setString(3, vo.getName());
			pstmt.setString(4, vo.getEmail());
			pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	@Override
	public void delMember(int mno) { // 회원삭제
		Connection conn = null;
		PreparedStatement pstmt = null;
		String query = "DELETE FROM T_MEMBER WHERE MNO=?";
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, mno);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}

 

- MemberVO

package sec04.ex04;

import java.sql.Date;

public class MemberVO {
	private int mno;
	private String id;
	private String password;
	private String name;
	private String email;
	private Date joinDate;
	
	public MemberVO(String id, String password, String name, String email) {
		this.id = id;
		this.password = password;
		this.name = name;
		this.email = email;
	}

	public MemberVO(int mno, String id, String password, String name, String email) {
		this.mno = mno;
		this.id = id;
		this.password = password;
		this.name = name;
		this.email = email;
	}

	public int getMno() {
		return mno;
	}
	public void setMno(int mno) {
		this.mno = mno;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Date getJoinDate() {
		return joinDate;
	}
	public void setJoinDate(Date joinDate) {
		this.joinDate = joinDate;
	}

	@Override
	public String toString() {
		return "MemberVO [mno=" + mno + ", id=" + id + ", password=" + password + ", name=" + name + ", email=" + email
				+ ", joinDate=" + joinDate + "]";
	}
	
}

 

- MemberServlet

package sec04.ex04;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.RequestDispatcher;
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("/member/ex04")
public class MemberServlet extends HttpServlet {
	/*
	 * MemberDao -> MemberServlet -> MemberViews(JSP)
	 */
	private MemberDao dao;

	public MemberServlet() {
		dao = new MemberDaoImpl();
	}

	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doHandle(request, response);
	}

	@Override
	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");
		PrintWriter out = response.getWriter();
		String command = request.getParameter("command");

		if (command != null && command.equals("addMember")) { // 회원가입
			MemberVO vo = new MemberVO(
					request.getParameter("id"),
					request.getParameter("pwd"),
					request.getParameter("name"), 
					request.getParameter("email"));
			dao.addMember(vo);
			response.sendRedirect("/pro09/index.html");
		} else if(command != null && command.equals("delMember")) { // 회원삭제
			System.out.println("회원삭제");
			String inputMno = request.getParameter("mno");
			int mno = Integer.parseInt(inputMno);
			dao.delMember(mno);
			response.sendRedirect("/pro09/index.html");
		}
		
		else { // 회원목록
			RequestDispatcher rd = request.getRequestDispatcher("/memberList.jsp");
			List<MemberVO> memberList = dao.memberList();
			request.setAttribute("list", memberList);
			rd.forward(request, response);
		}
	}

}

 

- MemberViews

package sec04.ex04;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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("/member/ex04/views")
public class MemberViews extends HttpServlet {

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		
		List<MemberVO> memberList = (List<MemberVO>) request.getAttribute("list");
		
		out.print("<table border='1'><tr>");
		out.print("<th>회원번호</th><th>아이디</th>");
		out.print("<th>패스워드</th><th>이름</th>");
		out.print("<th>이메일</th><th>가입일</th></tr>");
		
		for(MemberVO vo : memberList) {
		// 반복구간
		out.print("<tr><td>" + vo.getMno() + "</td><td>" + vo.getId() + "</td><td>" + vo.getPassword() + "</td>");
		out.print("<td>" + vo.getName() + "</td><td>" + vo.getEmail() + "</td><td>" + vo.getJoinDate()+ "</td></tr>");
		}
		out.print("</table>");
		
	}

}

 

 

- memberList.jsp

import 잊지말것

<%@page import="java.util.List"%>
<%@page import="sec04.ex04.MemberVO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	// 자바 코드 올수있음
	List<MemberVO> memberList = (List<MemberVO>) request.getAttribute("list");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원목록</title>
</head>
<body>
	<table border=1>
		<tr>
			<th>회원번호</th>
			<th>아이디</th>
			<th>패스워드</th>
			<th>이름</th>
			<th>이메일</th>
			<th>가입일</th>
			<th>삭제</th>
		</tr>
		<% for(MemberVO vo : memberList) { %>
		<tr>
			<td><%=vo.getMno()%></td>
			<td><%=vo.getId()%></td>
			<td><%=vo.getPassword()%></td>
			<td><%=vo.getName()%></td>
			<td><%=vo.getEmail()%></td>
			<td><%=vo.getJoinDate()%></td>
			<td><a href="/pro09/member/ex04?command=delMember&mno=<%=vo.getMno()%>">삭제</a></td>
		</tr>
		<% } %>
	</table>
</body>
</html>

 

 

- index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<a href="/pro09/member/ex04">회원목록</a><br>
	<a href="memberForm.html">회원가입</a>
</body>
</html>

 

 

- memberForm.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>회원가입</h1>
	<form action="/pro09/member/ex04" method="post">
		<input type="hidden" name="command" value="addMember">
		아이디 : <input type="text" name="id"><br>
		비밀번호 : <input type="password" name="pwd"><br>
		이름 : <input type="text" name="name"><br>
		이메일 : <input type="text" name="email"><br>
		<button>회원가입</button>
	</form>
</body>
</html>

 

joy와 lee를 삭제 해보면

 

 

반응형
저작자표시

'학습 > JSP' 카테고리의 다른 글

12. ServletConfig  (0) 2022.11.09
11. ServletContext  (0) 2022.11.08
9. 포워드  (0) 2022.11.07
8. 데이터베이스 데이터 조회, 추가, 삭제  (0) 2022.11.04
7. 서블릿의 데이터베이스 연동하기(오라클)  (0) 2022.11.03

관련글

댓글

티스토리툴바