DB사용 연습 - 회원정보 : members 목록 보기

berryberrybin commented 2 years ago

테이블 생성 및 레코드 추가

Oracle SQL Developer - scott계정에 SQL 쿼리문 추가

select *from member; -- alt+x
drop table member;

// 회원가입 테이블 생성
create table member(
   id varchar2(20) primary key ,
   pwd varchar2(20) not null,
   name varchar2(20) not null,
   age int,
   phone varchar(15),
   addr varchar2(50),
   join_date date not null
)

select * from member;

// 레코드추가 
insert into member(id,pwd,name,age,phone,addr, join_date)
values('jang','1234','장희정',20,'010-8875-8253','서울시 광진구', sysdate)

commit

berryberrybin commented 2 years ago

DTO 작성

Member.java

public class Member {

    private String id;
    private String pwd;
    private String name;
    private int age;
    private String phone;
    private String addr;
    private String joinDate;

    public Member() {}
    public Member(String id, String pwd, String name, int age, String phone, String addr, String joinDate) {
        super();
        this.id = id;
        this.pwd = pwd;
        this.name = name;
        this.age = age;
        this.phone = phone;
        this.addr = addr;
        this.joinDate = joinDate;
    }
// 각 Getter Setter 작성   
}

berryberrybin commented 2 years ago

DAO 작성

MemberDAO.java

public interface MemberDAO {
   /**
    * 전체검색
    *  : SELECT * FROM MEMBER ORDER BY JOIN_DATE DESC
    * */
    List<Member> selectAll();

    /**
     * 조건검색(검색필드와 검색단어를 전달받아 검색필드에 검색단어를포함한 레코드를 검색하기 )
     *   SELECT * FROM MEMBER WHERE ID LIKIE ?
         SELECT * FROM MEMBER WHERE NAME LIKIE ?
         SELECT * FROM MEMBER WHERE ADDR LIKIE ?
     * */
     List<Member> searchByKeyWord(String keyField, String keyWord);

    /**
     * 등록하기 
     *  insert into member(id,pwd,name,age,phone,addr, join_date)
         values(?,?,?,?,?,?, sysdate)
     * */
     int insert(Member member);

    /**
     * ID중복여부 체크
     * @return: true이면 중복이다, false이면 중복아니다
     *  SELECT ID FROM MEMBER WHERE ID=?
     * */
     boolean duplicateById(String id);

    /**
     * 회원 삭제하기
     * DELETE FROM MEMBER WHERE ID=?
     * */
     int delete(String id);

    /**
     * 회원상세정보 검색하기 (ID에 해당하는 회원의 모든 정보 검색하기)
     * SELECT * FROM MEMBER WHERE ID=?
     * */
     Member selectById(String id);
}

berryberrybin commented 2 years ago

MemberDAOImpl.java

public class MemberDAOImpl implements MemberDAO {

    @Override
    public List<Member> selectAll() {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public List<Member> searchByKeyWord(String keyField, String keyWord) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public int insert(Member member) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public boolean duplicateById(String id) {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public int delete(String id) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public Member selectById(String id) {
        // TODO Auto-generated method stub
        return null;
    }

}

berryberrybin commented 2 years ago

Model 1 방식

JSP 파일 안에서 모든 것을 구현
코드가 분리 되지 않아서 유지보수가 어려움

Model 2 방식

파일을 분리함

berryberrybin commented 2 years ago

web.xml에 기본으로 index.jsp 있음
index.jsp를 고객이 시작화면으로 볼 수 있음
index.jsp 제일 먼저 실행

berryberrybin commented 2 years ago

index.jsp

location.href=" " 작성


<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

### SelectAllServlet.java
```java
@WebServlet("/selectAll")
public class SelectAllServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    public SelectAllServlet() {
    }
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        MemberDAO dao = new MemberDAOImpl();
        List<Member> list = dao.selectAll();

        request.setAttribute("list", list);

        // 이동
        request.getRequestDispatcher("memberSelect.jsp").forward(request,response);
    }
}

berryberrybin commented 2 years ago

SelectAllServlet.java에서 dao.selectAll() 호출
MemberDAOImpl.java에서 dao.selectAll()의 return은 List 형태

MemberDAOImpl.java

public class MemberDAOImpl implements MemberDAO {

    @Override
    public List<Member> selectAll() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Member> list = new ArrayList<Member>();
        String sql = "SELECT * FROM MEMBER ORDER BY JOIN_DATE DESC";

        try {
            con = DbUtil.getConnection();
            ps = con.prepareStatement(sql);

            rs = ps.executeQuery();
            while(rs.next()) {
                Member member = new Member(rs.getString(1),rs.getString(2),rs.getString(3),
                        rs.getInt(4),rs.getString(5),rs.getString(6),rs.getString(7));
                list.add(member);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtil.dbClose(rs, ps, con);
        }
        return list;
    }

berryberrybin commented 2 years ago

SelectAllServlet.java에서 request.getRequestDispatcher("memberSelect.jsp") 로 이동
request로 넘겨받은 list를 출력

memberSelect.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> 추가 필요
forEach문 돌면서 각각의 member 출력
- <c:forEach items="${list}" var="list" varStatus="state">
- state 이용하여 글번호 출력 ${state.count}

</head>
<body>
\${list} = ${list}<br>
    <br>

        <h1 style="text-align:center;">[ 회원 정보 LIST ]</h1>
        <table>
            <tr>
                <th colspan="9" style="text-align: right"><a href="memberForm.html">[ 회원가입 ]</a>&nbsp;&nbsp;&nbsp; <a href="#">[ 새로고침 ]</a>&nbsp;&nbsp;&nbsp;</th>
            </tr>
            <tr bgColor="pink">
                <th>번호</th>
                <th>아이디</th>
                <th>비밀번호</th>
                <th>이름</th>
                <th>나이</th>
                <th>주소</th>
                <th>연락처</th>
                <th>가입일</th>
                <th>삭제</th>
            </tr>
            <c:forEach items="${list}" var="list" varStatus="state">
                <tr>
                    <td>${state.count}</td>
                    <td>${list.id}</td>
                    <td>${list.pwd}</td>
                    <td>${list.name}</td>
                    <td>${list.age}</td>
                    <td>${list.addr}</td>
                    <td>${list.phone}</td>
                    <td>${list.joinDate}</td>
                </tr>

            </c:forEach>

        </table>
        <p>
        <div style="text-align:center;">
        <form name="search" action="" method="post">
            <select name="keyField">
                <option value="0">--선택--</option>
                <option value="id">아이디</option>
                <option value="name">이름</option>
                <option value="addr">주소</option>
            </select>

            <input type="text" name="keyWord" /> <input type="button" value="검색" />

        </form>
        </div>
</body>

berryberrybin / kosta-jsp

DB사용 연습 - 회원정보 : members 목록 보기 #22

테이블 생성 및 레코드 추가

DTO 작성

DAO 작성

MemberDAOImpl.java

Model 1 방식

Model 2 방식

index.jsp 제일 먼저 실행

index.jsp

MemberDAOImpl.java

memberSelect.jsp