TIL 2022-10-28 데이터베이스쿼리, db연동 update, delete,set / 게시판만들기(1)

JYR00·2022년 10월 28일
0

TIL

목록 보기
33/60

Statement 객체로 데이터 접근

  • 정적인 쿼리에 사용
  • 하나의 쿼리를 사용하고 나면 더는 사용불가
  • 하나의 쿼리를 끝내면 close()사용해서 객체 즉시 해제해야.-> 데이터많이 잡아먹음

executeQuery()

Statement객체

dbconn, insert02 insert02_process

insert03-데이터베이스쿼리




dbconn

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오전 9:18
  To change this template use File | Settings | File Templates.
--%>
<%@ page import="java.sql.*" %>
<%@ page import="java.sql.DriverManager" %>
<%
    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);

%>

insert02

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오전 9:21
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
    <title>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>
<form action="insert02_process.jsp" method="post">
  <label for="user-id">아이디 : </label>
  <input type="text" id="user-id" name="userId"><br>
  <label for="user-pw">비밀번호 : </label>
  <input type="text" id="user-pw" name="userPw"><br>
  <label for="user-name">이름 : </label>
  <input type="text" id="user-name" name="userName"><br>
  <button type="submit">전송</button>
</form>
</body>

</html>

insert02_process

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오전 9:22
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
    <title>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"%>

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

    String userId = request.getParameter("userId");
    String userPw = request.getParameter("userPw");
    String userName = request.getParameter("userName");

    Statement stmt = null;
    try {
        String sql = "INSERT INTO member (id, passwd, name) ";
        sql += "VALUES('" + userId + "', '" + userPw + "', '" + userName + "') ";
//        변수명을 집어넣음

        stmt = conn.createStatement();
        stmt.executeUpdate(sql);
        out.println("member 테이블에 데이터를 추가했습니다.");
    }
    catch(SQLException e) {
        out.print("member테이블에 데이터 추가를 실패하였습니다.");
        out.print("SQLException: " + e.getMessage());

    }
    finally {
        if (stmt != null) {
            stmt.close();
        }
        if (stmt != null) {
            conn.close();
        }
    }
%>
</body>

</html>

ResultSet객체



select01

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오전 10:17
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
    <title>db사용하기 - Statement(select)</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"%>
<div class="container mt-6 mx-auto" >
  <div class="row">
    <div class="col-sm">
      <table class="table table-hover table-striped">
        <thread>
          <tr>
            <th>아이디</th>
            <th>비밀번호</th>
            <th>이름</th>
          </tr>
        </thread>
        <tbody>
          <%
            ResultSet rs = null;
            Statement stmt = null;
            try{
              String sql = "SELECT * FROM member "; //반드시 한 칸 띄워야
              stmt = conn.createStatement();
              rs = stmt.executeQuery(sql); //rs에 집어넣음 격자무늬 이차원형태로 저장중

              while (rs.next()){ //다음부분 데이터가 있으면 true / false
//                dbconn과 동일한 변수 사용시 오류 발생.
                String userId = rs.getString("id");
                String userPw = rs.getString("passwd");
                String userNm = rs.getString("name");

//       out.println("<tr><td>" + userId + "</td><td>" + userPw + "</td><td>"+...이렇게하기는 너무 귀찮으니까
            %>

            <tr>
              <td><%=userId%></td>
              <td><%=userPw%></td>
              <td><%=userNm%></td>
            </tr>
          <%
            
              }
            }
            catch (SQLException e){
              out.println("member 테이블 데이터 조회가 실패했습니다.");
              out.println("SQLException : " + e.getMessage());
            }
            finally {
              if(rs != null){rs.close();}
              if (stmt != null){stmt.close();}
              if(conn != null){conn.close();}
            }
            %>
        </tbody>
      </table>
    </div>
  </div>
</div>
</table>
</body>

</html>

preparestatment 사용예제 select02

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오전 10:41
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
    <title>db 사용하기  - PreparedStatement(select)</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"%>
<div class="container mt-4">
  <div class="row">
    <div class="col-sm-6 mx-auto">
      <table class="table table-hover table-striped">
        <thead>
        <tr>
          <th>아이디</th>
          <th>비밀번호</th>
          <th>이름</th>
        </tr>
        </thead>
        <tbody>
        <%
          PreparedStatement pstmt = null;
          ResultSet rs = null;

          try{
            String sql = "SELECT * FROM member " ;
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
              String userId = rs.getString("id");
              String userPw = rs.getString("passwd");
              String userNm = rs.getString("name");
          %>
        <tr>
          <td><%=userId%></td>
          <td><%=userPw%></td>
          <td><%=userNm%></td>
        </tr>
        <%
            }
          }
          catch (SQLException e){
            out.println("member 테이블의 데이터 조회가 실패했습니다.");
            out.println("SQLException : " + e.getMessage());
          }
          finally {
            if(rs != null){rs.close();}
            if(pstmt != null){pstmt.close();}
            if(conn != null){conn.close();}
          }
        %>
        </tbody>
      </table>
    </div>
  </div>
</div>
</body>

</html>

update문 사용

preparedstatemnt사용해서 update문 활용

update01,update01_process

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오전 11:05
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
    <title>db사용하기-statement( update )</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>
<form action="update01_process.jsp" method="post">
    <label for="user-id">아이디 : </label>
    <input type="text" id="user-id" name="userId"><br>
    <label for="user-pw">비밀면호 : </label>
    <input type="text" id="user-pw" name="userPw"><br>
    <label for="user-name">이름</label>
    <input type="text" id="user-name" name="userName"><br>
    <button type="submit">업데이트</button>
</form>
</body>

</html>

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오전 11:09
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
    <title>db사용하기 - statement(update)</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"%>

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

  String userId = request.getParameter("userId");
  String userPw = request.getParameter("userPw");
  String userNm = request.getParameter("userName"); //name 값 넣음

  Statement stmt = null;
  ResultSet rs = null;

  try{
    String sql = "SELECT id, passwd FROM member ";
    sql += "WHERE id = '" + userId + "' ";

//    Statement 객체 생성
    stmt = conn.createStatement();
//    Statement로 db에 쿼리를 전송, 결과값을 resultset으로 받아옴
    rs = stmt.executeQuery(sql);


    if (rs.next()) {
      String rid = rs.getString("id");
      String rpw = rs.getString("passwd");

//      사용자가 입력한 비밀번호와 db에 저장된 비밀번호가 같은지 확인. 앞의 건 무조건 true. 아이디로 받아왔으니까.
      if (userId.equals(rid) && userPw.equals(rpw)){ //입력한 거 저장된거 비교(이순서다)
        sql = "UPDATE member SET name = '" + userNm + "' ";
        sql += "WHERE id = '" + userId + "' "; //where안넣으면 전체 다 바뀜

//        Statement 객체를 새로 생성
        stmt = conn.createStatement(); //createStatement : statement 객체가 새로 생긴다.
//        db 서버로 쿼리 전송
        stmt.executeUpdate(sql);
        out.println("member 테이블의 데이터를 수정하였습니다.");
      }
      else {
        out.println("비밀번호가 틀렸습니다.");
      }
    }
//    위에 if문 2개 끝난거임
    else {
      out.println("일치하는 사용자가 없습니다.");
    }
  }
  catch (SQLException e){
      out.println("SQLException : " + e.getMessage());
  }
  finally {
      if(rs != null){rs.close();} // 다 삭제 시켜줌.
      if(stmt != null){stmt.close();}
      if(conn != null){conn.close();}
  }

%>
</body>

</html>

update02 update02_process // preparedstatement 사용

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오전 11:35
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>

<!DOCTYPE html>
<html>
<head>
    <title>db preparedstatemnet</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>
<form action="update02_process.jsp" method="post">
  <label for="user-id">아이디 : </label>
  <input type="text" id="user-id" name="userId"><br>
  <label for="user-pw">비밀면호 : </label>
  <input type="text" id="user-pw" name="userPw"><br>
  <label for="user-name">이름</label>
  <input type="text" id="user-name" name="userName"><br>
  <button type="submit">업데이트</button>
</form>
</body>

</html>

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오전 11:35
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
    <title>db 사용하기 - PreparedStatement(update)</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"%>
<%
  request.setCharacterEncoding("utf-8");

  String userId = request.getParameter("userId");
  String userPw = request.getParameter("userPw");
  String userNm = request.getParameter("userName");

  PreparedStatement pstmt = null;
  ResultSet rs = null;

  try{
//    preparedstatement를 사용하여 sql 쿼리문이 변경됨
//    쿼리문 소문자 써도 됨.
    String sql = "SELECT id, passwd from member ";
    sql += "WHERE id = ?";

//    Preparedstatement 객체 생성 시 사용할 sql문을 적용
    pstmt = conn.prepareStatement(sql);
//    필요한 위치의 데이터를 변경
    pstmt.setString(1, userId);

    rs = pstmt.executeQuery();

    if (rs.next()){
      String rid = rs.getString("id");
      String rpw = rs.getString("passwd");

      if(userId.equals(rid) && userPw.equals(rpw)){
        sql = "UPDATE member SET name = ? ";
        sql += "WHERE id =? ";

        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,userNm);
        pstmt.setString(2, userId);
        pstmt.executeUpdate();

        out.println("member 테이블의 정보를 수정하였습니다.");
      }
      else{
        out.println("비밀번호가 틀렸습니다.");
      }
    }
    else{
      out.println("일치하는 사용자가 없습니다.");
    }
  }
  catch (SQLException e){
    out.println("SQLException e : " +  e.getMessage());
  }
  finally{
    if(rs !=null){rs.close();}
    if(pstmt != null){pstmt.close();}
    if(conn != null){conn.close();}
  }
%>
</body>

</html>

페이지 만들기

리스트 페이지(글쓰기), 글쓰기페이지(목록, 쓰기), 읽기페이지(목록, 수정, 삭제버튼) 화면 3-4개

화면, 내부처리(글쓰기 / 수정 / 삭제),
boardList.jsp

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오후 3:17
  To change this template use File | Settings | File Templates.
--%>
<%@ 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 (){
      //DOMContentLoaded 순수 자바 스크립트. 다 실행되었을 때 사용됨
      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-4">
      <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="boardWrite_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">Title</label>
        </div>

        <div class="form-floating my-3">
          <input type="text" class="form-control" id="user-id" name="userId" placeholder="ID를 입력하세요">
          <label for="user-id" class="form-label">ID</label>
        </div>
        <div class="form-floating my-3">
          <textarea class="form-control" id="contents" name="contents" rows="10" placeholder="내용을 입력하세요"></textarea>
          <label for="contents" class="form-label">Contents...</label>
        </div>
          <div class="d-grid gap-2">
            <button class="btn btn-primary" 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 java 505 </p>
</footer>
</body>
</html>

boardWrite

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오후 3:17
  To change this template use File | Settings | File Templates.
--%>
<%@ 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 (){
      //DOMContentLoaded 순수 자바 스크립트. 다 실행되었을 때 사용됨
      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-4">
      <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="boardWrite_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">Title</label>
        </div>

        <div class="form-floating my-3">
          <input type="text" class="form-control" id="user-id" name="userId" placeholder="ID를 입력하세요">
          <label for="user-id" class="form-label">ID</label>
        </div>
        <div class="form-floating my-3">
          <textarea class="form-control" id="contents" name="contents" rows="10" placeholder="내용을 입력하세요"></textarea>
          <label for="contents" class="form-label">Contents...</label>
        </div>
          <div class="d-grid gap-2">
            <button class="btn btn-primary" 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 java 505 </p>
</footer>
</body>
</html>

borderWrite_poress

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2022-10-28
  Time: 오후 3:55
  To change this template use File | Settings | File Templates.
--%>
<%@ 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 contents = request.getParameter("contents");

  PreparedStatement pstmt = null;
//  Connection conn = null;
//  insert해주니 resultset 필요없다
  try {
    String sql = "INSERT INTO board(title, contents, user_id, create_date) ";
    sql += "values (?,?,?,now());";

    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1,title);
    pstmt.setString(2,contents);
    pstmt.setString(3,userId);

    pstmt.executeUpdate();

  }
  catch (SQLException e) {
    out.println("SQLException e : " + e.getMessage());
  }
  finally {
    if (pstmt != null) {pstmt.close();}
    if (conn != null) {conn.close();}
  }

  response.sendRedirect("boardList.jsp"); //실행 다되고 나면 첫 페이지로 돌아가라.
%>


db

SELECT * FROM javadb.board; 



-- 글 등록
INSERT INTO board (title, contents, user_id, create_date)
VALUES ("제목1","내용1","test1",now());

-- 전체 글 조회
SELECT seq, title, user_id, create_date, cnt, deleted_yn from board WHERE deleted_yn = "N";

-- 지정한 글 보기
SELECT seq, title, contents, user_id, create_date, update_date, cnt FROM board
WHERE seq = 4;

-- 글 등록
INSERT INTO board(title, contents, user_id, create_date)
values ("제목5","내용5", "test1",now());

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

-- 글 삭제
DELETE FROM board WHERE seq =1;

UPDATE board SET deleted_yn = 'Y' WHERE seq = 4; 
-- 위에는 진짜 삭제, 밑에꺼는 삭제된 것 처럼 보이게 (실제 데이터는 존재함.)

-- 조회수 올리기 . 선택한 파일의 데이터값 상승함.
UPDATE board SET cnt = cnt+1 where seq = 1;
















profile
JYR00
이전 포스트

2022-10-27 jsp 쿠키, 파일업로드, db연결

다음 포스트

TIL 2022-11-03 나 혼자 게시판 만들기

0개의 댓글