JSP - 08

월요일좋아·2022년 11월 4일

jsp

JSP

목록 보기

8/9

DB

SELECT * FROM javadb.board;

초기 데이터 넣어보기

전체 글 조회(리스트 출력부분)

SELECT seq, title, user_id, create_date, cnt FROM board WHERE deleted_yn = 'N' order by seq DESC;

지정한 글 내용 보기

SELECT seq, title, contents, user_id, create_date, update_date, cnt FROM board
WHERE seq = 1;

글 등록

INSERT INTO board(title, contents, user_id, create_date)
VALUES ('제목5', '내용5', 'test5', now());

글 수정 : 글번호, 글쓴이, 작성날짜, 조회수 바꿀 수 없음

UPDATE board SET title = '제목4', contents = '내용4', update_date = now() WHERE seq = 5;

글 삭제 방법 1 (진짜로 삭제)

DELETE FROM board WHERE seq = 5;

글 삭제 방법 2 (눈에만 안보이게)

UPDATE board SET deleted_yn = 'Y' WHERE seq = 5;

조회수 올리기 : 아래 쿼리문 한번 실행할때마다 cnt값 1씩 올라감

UPDATE board SET cnt = cnt + 1 WHERE seq = 2;

1. 자유 게시판 만들기 문제

프로젝트명: jspBoardGJY
데이터베이스명 : t_board
컬럼명 : 글번호(idx, int), 글제목(title, varchar(50), NN), 글내용(contents, varchar(500)), 글쓴이(user_id, varchar(45), NN), 글비밀번호(pwd, varchar(45), NN), 글등록시간(create_dt, datetime, NN), 글수정시간(update_dt, datetime), 조회수(hit_cnt, int, 기본값: 0), 글삭제여부(deleted_yn, char(1), 기본값: 'N')
추가 : 추천수(like_cnt, int, 기본값: 0)

DB
dbconn.jsp

<%@ page import="java.sql.*" %>

<%
  Connection conn = null;

  String url = "jdbc:mysql://localhost:3306/javadb";
  String user = "test1";
  String passwd = "java505";

  Class.forName("com.mysql.cj.jdbc.Driver");
  conn = DriverManager.getConnection(url, user, passwd);
%>

1-1 목록 페이지

tBoardList.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
    <title>글 목록</title>

    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/js/bootstrap.bundle.min.js"></script>

</head>
<body>
<%@ include file="dbconn.jsp"%>

<header class="container mt-3">
    <div class="p-5 mb-4 bg-light rounded-3">
        <div class="container-fluid py-4">
            <h1 class="text-center">게시물 리스트 페이지</h1>
        </div>
    </div>
</header>

<main class="container mt-4">
    <div class="row">
        <div class="col-sm">
            <table class="table table-hover table striped">
                <thead>
                <tr>
                    <th>글번호</th>
                    <th>글제목</th>
                    <th>글쓴이</th>
                    <th>등록시간</th>
                    <th>조회수</th>
                </tr>
                </thead>
                <tbody>
                <%
                    PreparedStatement pstmt = null;
                    ResultSet rs = null;

                    try {
                        String sql = "SELECT idx, title, user_id, create_dt, hit_cnt FROM t_board WHERE deleted_yn = 'N' order by idx DESC";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery();

                        while (rs.next()) {
                            int idx = rs.getInt("idx");
                            String title = rs.getString("title");
                            String userId = rs.getString("user_id");
                            String createDt = rs.getString("create_dt");
                            int cnt = rs.getInt("hit_cnt");
                %>

                    <tr>
                        <td><%=idx%></td>
                        <td><a href="tBoardDetail.jsp?idx=<%=idx%>"><%=title%></a></td>
                        <td><%=userId%></td>
                        <td><%=createDt%></td>
                        <td><%=cnt%></td>
                    </tr>

                <%
                        }
                    }
                    catch (SQLException e) {
                        out.println("SQLException : " + e.getMessage());
                    }
                    finally {
                        if (rs != null) {
                            rs.close();
                        }
                        if (pstmt != null) {
                            pstmt.close();
                        }
                        if (conn != null) {
                            conn.close();
                        }
                        }
                %>
                </tbody>
            </table>
            <div class="d-flex justify-content-end">
                <a href="write.jsp" class="btn btn-info">글쓰기</a>
            </div>
        </div>
    </div>
</main>


<footer class="container-fluid mt-5 p-5 border-top">
    <p class="lead text-muted text-center">made by bitc java505</p>
</footer>
</body>
</html>

1-2 상세글 읽기 페이지

tBoardDetail.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
    <title>상세 글 읽기</title>

    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/js/bootstrap.bundle.min.js"></script>

    <script>
        window.addEventListener('DOMContentLoaded', function () {
            const btnList = document.querySelector('#btn-list');
            const btnUpdate = document.querySelector('#btn-update');
            const btnDelete = document.querySelector('#btn-delete');
            const frm = document.querySelector('#frm');

            btnList.addEventListener('click', function () {
                history.back();
            });

            btnUpdate.addEventListener('click', function () {
                frm.action = "tBoardUpdate_process.jsp";
                frm.submit();
            });

            btnDelete.addEventListener('click', function () {
                frm.action = "tBoardDelete_process.jsp";
                frm.submit();
            });
        });
    </script>
</head>
<body>
<%@ include file="dbconn.jsp"%>
<%
    // tBoardList 에서 넘겨받은 idx
    int idx = Integer.parseInt(request.getParameter("idx"));
    String title = "";
    String contents = "";
    String userId = "";
    String pwd = "";
    String createDate = "";
    String updateDate = "";
    int hitCnt = 0;

    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
//        hitCnt 카운트업
        String sql = "UPDATE t_board SET hit_cnt = hit_cnt + 1 WHERE idx = ? ";
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, idx);
        pstmt.executeUpdate();

//        쿼리문 재셋팅
        sql = "SELECT idx, title, contents, user_id, pwd, create_dt, update_dt, hit_cnt FROM t_board ";
        sql += "WHERE idx = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, idx);
        rs = pstmt.executeQuery();

        if (rs.next()) {
            title = rs.getString("title");
            contents = rs.getString("contents");
            userId = rs.getString("user_id");
            pwd = rs.getString("pwd");
            createDate = rs.getString("create_dt");
            updateDate = rs.getString("update_dt");
            hitCnt = rs.getInt("hit_cnt");
        }
    }
    catch (SQLException e) {
        out.println("SQLExceprion : " + e.getMessage());
    }
    finally {
        if (rs != null) {
            rs.close();
        }
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
%>

<header class="container mt-3">
    <div class="p-5 mb-4 bg-light rounded-3">
        <div class="container-fluid py-4">
            <h1 class="text-center">상세 글 읽기 페이지</h1>
        </div>
    </div>
</header>


<main class="container mt-5">
    <div class="row">
        <form action="#" method="post" id="frm">
            <input type="hidden" name="pwd" value="<%=pwd%>">
            <div class="col-sm-6 mx-auto">
                <div class="form-floating my-3">
                    <input type="text" class="form-control" id="idx" name="idx" placeholder="글 번호를 입력하세요" readonly value="<%=idx%>">
                    <label for="idx" class="form-label">글 번호</label>
                </div>
                <div class="form-floating my-3">
                    <input type="text" class="form-control" id="title" name="title" placeholder="제목을 입력하세요" value="<%=title%>">
                    <label for="title" class="form-label">글 제목</label>
                </div>
                <div class="form-floating my-3">
                    <input type="text" class="form-control" id="user-id" name="userId" placeholder="ID를 입력하세요" readonly value="<%=userId%>">
                    <label for="user-id" class="form-label">아이디</label>
                </div>
                <div class="form-floating my-3">
                    <input type="password" class="form-control" id="user-pw" name="userPwd" placeholder="비밀번호를 입력하세요">
                    <label for="user-pw" class="form-label">비밀번호</label>
                </div>
                <div class="form-floating my-3">
                    <input type="text" class="form-control" id="create-date" name="createDate" placeholder="날짜를 입력하세요" readonly value="<%=createDate%>">
                    <label for="create-date" class="form-label">등록 날짜</label>
                </div>
                <div class="form-floating my-3">
                    <input type="text" class="form-control" id="hit-cnt" name="hitCnt" placeholder="조회수를 입력하세요" readonly value="<%=hitCnt%>">
                    <label for="hit-cnt" class="form-label">조회수</label>
                </div>
                <div class="form-floating my-3">
                    <textarea class="form-control" id="contents" name="contents" rows="10" placeholder="내용을 입력하세요"><%=contents%></textarea>
                    <label for="contents" class="form-label">내용</label>
                </div>
                <div class="my-3 row">
                    <div class="col-sm">
                        <button class="btn btn-secondary" type="button" id="btn-list">목록으로</button>
                    </div>
                    <div class="col-sm d-flex justify-content-end">
                        <button class="btn btn-warning me-2" type="button" id="btn-update">수정</button>
                        <button class="btn btn-danger" type="button" id="btn-delete">삭제</button>
                    </div>
                </div>
            </div>
        </form>
    </div>
</main>
</body>
</html>

1-3 글 등록 페이지

write.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
    <title>글쓰기</title>

    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/js/bootstrap.bundle.min.js"></script>

    <script>
        window.addEventListener('DOMContentLoaded', function () {
           const btnBack = document.querySelector('#btn-back');
           btnBack.addEventListener('click', function () {
               history.back();
           });
        });
    </script>
</head>
<body>
<header class="container mt-3">
    <div class="p-5 mb-4 bg-light rounded-3">
        <div class="container-fluid py-1">
            <h1 class="text-center">게시물 글쓰기 페이지</h1>
        </div>
    </div>
</header>


<main class="container mt-5">
    <div class="row">
        <div class="col-sm-6 mx-auto">
            <form action="write_process.jsp" method="post" class="border rounded-3 p-4">
                <div class="form-floating my-3">
                    <input type="text" class="form-control" id="title" name="title" placeholder="제목을 입력하세요.">
                    <label for="title" class="form-label">제목</label>
                </div>
                <div class="form-floating my-3">
                    <input type="text" class="form-control" id="user_id" name="userId" placeholder="아이디를 입력하세요">
                    <label for="user_id" class="form-label">아이디</label>
                </div>
                <div class="form-floating my-3">
                    <input type="password" class="form-control" id="user_pw" name="userPw" placeholder="비밀번호를 입력하세요">
                    <label for="user_pw" class="form-label">비밀번호</label>
                </div>
                <div class="form-floating row-6 my-3">
                    <textarea class="form-control" name="contents" id="contents" cols="30" rows="30" placeholder="내용을 입력하세요"></textarea>
                    <label for="contents" class="form-label">내용</label>
                </div>
                <div class="d-grid gap-2">
                    <button class="btn btn-info" type="submit">글쓰기</button>
                    <button class="btn btn-secondary" type="button" id="btn-back">돌아가기</button>
                </div>
            </form>
        </div>
    </div>
</main>


<footer class="container-fluid mt-5 p-5 border-top">
    <p class="lead text-muted text-center">made by bitc java505</p>
</footer>
</body>
</html>

write_process.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<%@ include file="dbconn.jsp"%>



<%
  request.setCharacterEncoding("UTF-8");

  String title = request.getParameter("title");
  String userId = request.getParameter("userId");
  String userPw = request.getParameter("userPw");
  String contents = request.getParameter("contents");

  PreparedStatement pstmt = null;

  try {
    String sql = "INSERT INTO t_board(title, contents, user_id, pwd, create_dt) ";
    sql += "VALUES (?, ?, ?, ?, now()) ";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, title);
    pstmt.setString(2, contents);
    pstmt.setString(3, userId);
    pstmt.setString(4, userPw);
    pstmt.executeUpdate();
  }
  catch (SQLException e) {
    out.println("SQLException : " + e.getMessage());
  }
  finally {
    if (pstmt != null) {
      pstmt.close();
    }
    if (conn != null) {
      conn.close();
    }
  }

  response.sendRedirect("tBoardList.jsp");
%>

1-4 글 수정 페이지

상세 글 페이지에서 이동, 비밀번호 입력 부분 필요
+) 비밀번호 넣어야 수정되게

tBoardUpdate.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>

<%@ include file="dbconn.jsp"%>

<%
    request.setCharacterEncoding("UTF-8");

    int idx = Integer.parseInt(request.getParameter("idx"));
    String title = request.getParameter("title");
    String contents = request.getParameter("contents");
    String pwd = request.getParameter("pwd");
    String userPwd = request.getParameter("userPwd");

    if (pwd.equals(userPwd)) {
        String sql = "UPDATE t_board SET title = ?, contents = ? ";
        sql += "WHERE idx = ? ";

        PreparedStatement pstmt = null;

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, title);
            pstmt.setString(2, contents);
            pstmt.setInt(3, idx);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            out.println("SQLException : " + e.getMessage());
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }

        response.sendRedirect("tBoardList.jsp");
    }
    else {
        out.println("비밀번호가 틀렸습니다.");
    }
%>

1-5 글 삭제 페이지

상세 글 페이지에서 이동, 비밀번호 입력 부분 필요
+) 비밀번호 넣어야 삭제되게

tBoardDelete_process.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>

<%@ include file="dbconn.jsp"%>

<%
    request.setCharacterEncoding("UTF-8");
    int idx = Integer.parseInt(request.getParameter("idx"));
    String userPwd = request.getParameter("userPwd");
    String pwd = request.getParameter("pwd");

    if (pwd.equals(userPwd)) {

        String sql = "UPDATE t_board SET deleted_yn = 'Y' ";
        sql += "WHERE idx = ?";

        PreparedStatement pstmt = null;

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, idx);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            out.println("SQLException : " + e.getMessage());
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        response.sendRedirect("tBoardList.jsp");
    }
    else {
        out.println("비밀번호가 틀렸습니다.");
    }


%>

2. 회원제 게시판으로 변경

데이터베이스명 : t_member
컬럼명 : 사용자index(user_idx, int, PK), 사용자id(user_id, varchar(45), UQ, NN), 비밀번호(user_pw, varchar(45), NN), 사용자명(user_name, varchar(45), NN), 이메일(user_email, varchar(45), NN)

2-1 회원 가입

2-2 로그인

2-3 로그아웃

월요일좋아

이전 포스트

JSP - 07

다음 포스트