8. 데이터베이스 데이터 조회, 추가, 삭제

- 데이터베이스에 데이터 추가하기

- src/main/java에 클래스, 서블릿 만들어줌

- webapp에 memberJoin.html, index.html 만들어줌

 

- MemberVO

package sec01.ex04;

import java.sql.Date;

public class MemberVO {
	private String uId;
	private String pwd;
	private String uName;
	private String email;
	private Date date;
	
	public MemberVO(String uId, String pwd, String uName, String email, Date date) {
		this.uId = uId;
		this.pwd = pwd;
		this.uName = uName;
		this.email = email;
		this.date = date;
	}
	public MemberVO(String uId, String pwd, String uName, String email) {
		this.uId = uId;
		this.pwd = pwd;
		this.uName = uName;
		this.email = email;
	}
	public String getuId() {
		return uId;
	}
	public void setuId(String uId) {
		this.uId = uId;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public String getuName() {
		return uName;
	}
	public void setuName(String uName) {
		this.uName = uName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	@Override
	public String toString() {
		return "MemberVO [uId=" + uId + ", pwd=" + pwd + ", uName=" + uName + ", email=" + email + ", date=" + date
				+ "]";
	}
	
}

 

- MemberDao

package sec01.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 MemberDao {
	private DataSource dataSource;
	private Connection con;
	private PreparedStatement pstmt;
	
	//  DataSource
	public MemberDao() {
		try {
			Context ctx = new InitialContext();
			Context envContext = (Context) ctx.lookup("java:/comp/env");
			dataSource = (DataSource) envContext.lookup("jdbc/oracle");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	
	// 회원목록
	public List<MemberVO> listMembers() { 
		List<MemberVO> list = new ArrayList<>();
		String query = "SELECT * FROM T_MEMBER";
		try {
			con = dataSource.getConnection();
			pstmt = con.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				MemberVO vo = new MemberVO(
					rs.getString("u_id"),
					rs.getString("pwd"),
					rs.getString("u_name"),
					rs.getString("email"),
					rs.getDate("joindate"));
				list.add(vo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	// 회원가입
	public void addMember(MemberVO vo) {
		String id = vo.getuId();
		String pwd = vo.getPwd();
		String name = vo.getuName();
		String email = vo.getEmail();
		String query = "INSERT INTO T_MEMBER(U_ID, PWD, U_NAME, EMAIL)";
		query += "VALUES(?, ?, ?, ?)";
		try {
			con = dataSource.getConnection();
			pstmt = con.prepareStatement(query);
			pstmt.setString(1, id);
			pstmt.setString(2, pwd);
			pstmt.setString(3, name);
			pstmt.setString(4, email);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// 회원삭제
	public void delMember(String id) {
		String query = "DELETE FROM T_MEMBER WHERE U_ID=?";
		try {
			con = dataSource.getConnection();
			pstmt = con.prepareStatement(query);
			pstmt.setString(1, id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}

 

- MemberServlet

package sec01.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("/members")
public class MemberServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	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();

		String command = request.getParameter("command");

		MemberDao dao = new MemberDao();

		// 회원가입 로직 start
		if (command != null && command.equals("memberJoin")) {
			String id = request.getParameter("id");
			String pw = request.getParameter("pw");
			String name = request.getParameter("name");
			String email = request.getParameter("email");
			dao.addMember(new MemberVO(id, pw, name, email));
			response.sendRedirect("/pro08"); // 지정된 페이지로 이동
			// 아래 코드는 실행되지 않음
			// 회원가입 로직 end
		} else if(command != null && command.equals("delMember")) {
			String id = request.getParameter("id");
			if(id != null) dao.delMember(id);
			response.sendRedirect("/pro08");
		} else {
			List<MemberVO> listMembers = dao.listMembers();

			out.print("<table border=1><tr>");
			out.print("<th>아이디</th>");
			out.print("<th>비밀번호</th>");
			out.print("<th>이름</th>");
			out.print("<th>이메일</th>");
			out.print("<th>가입일</th>");
			out.print("<th>삭제</th></tr>");

			// 반복구간
			for (MemberVO vo : listMembers) {
				out.print("<tr><td>" + vo.getuId() + "</td>");
				out.print("<td>" + vo.getPwd() + "</td><td>" + vo.getuName() + "</td>");
				out.print("<td>" + vo.getEmail() + "</td>");
				out.print("<td>" + vo.getDate() + "</td>");
				out.print("<td><a href='/pro08/members?command=delMember&id="+vo.getuId()+"'>삭제</a></td></tr>");
			}
			out.print("</table>");
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		doGet(req, resp);
	}

}

 

 

- HTML

- memberJoin.html

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

 

 

- index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
    <h1>메인페이지</h1>
    <a href="/pro08/members">회원목록 조회</a><br>
    <a href="/pro08/memberJoin.html">회원가입</a><br>
</body>
</html>

 

 

회원목록 조회 눌렀을때

 

회원가입 눌렀을때

 

회원가입하고 다시 회원목록 조회 해보면

데이터가 들어가고 화면에 출력되면 됨.

 

삭제 를 눌러서 (김유신) 데이터가 삭제 되는지 확인하자

 

선택한 데이터가 빠지면 됨.