Spring/Spring Legacy
[JDBC]게시판(CRUD) 구현
Jenny_yoon
2023. 2. 25. 18:04
728x90
반응형
1. board.jsp 생성
데이터베이스 연결
<%@page import="java.util.HashMap"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.util.Map"%>
<%@page import="java.util.List"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
Connection conn = null; //Connection: 데이터베이스와 연결을 수립하는 데 사용
PreparedStatement pstmt = null;
//PreparedStatement: 미리 컴파일된 SQL 문을 나타내며 실행할 때 매개변수를 전달함
String url = "jdbc:mariadb://wisejia.iptime.org:포트넘버/아이디";
String id = "id";
String pw = "pw";
Class.forName("org.mariadb.jdbc.Driver");
//Class.forName()을 사용하여 MariaDB JDBC 드라이버를 로드
//아래 객체를 생성하고 사용하기 전에 (Class.forName()로) 데이터베이스 드라이버를 로드해야함
conn = DriverManager.getConnection(url, id, pw); // 데이터베이스에 연결
pstmt = conn.prepareStatement("SELECT * FROM boardview LIMIT 0, 10");
ResultSet rs = pstmt.executeQuery(); //ResultSet: 명령에 대한 반환값
//즉, 주어진 코드에서는 MariaDB JDBC 드라이버를 로드하고,
//데이터베이스 서버에 연결을 수립하고, 미리 컴파일된 쿼리문을 실행하고, ResultSet 객체를 가져와서 결과
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
while(rs.next()){ //next(); 뽑아줄 값이 존재하면 참, 아니면 거짓
//ResultSet을 반복하여 각 데이터 행을 검색
Map<String, Object> e = new HashMap<String, Object>();
//getXXX() 메소드를 사용하여 값을 가져와 각 데이터 행마다 채우기
e.put("b_no", rs.getInt("b_no"));//저장할 키, 저장할 값
e.put("b_title", rs.getString("b_title"));
e.put("b_date", rs.getString("b_date"));
e.put("member_name", rs.getString("member_name"));
e.put("b_like", rs.getInt("b_like"));
e.put("b_read", rs.getInt("b_read"));
e.put("comment", rs.getInt("comment"));
list.add(e);
}
%>(위 같은 페이지 하단에 넣기)
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>board</title>
</head>
<body>
<h1>board</h1>
<!-- 데이터베이스에 연결해서 해당 내용 가져오기 -->
<table border="1">
<tr>
<td>번호</td>
<td>제목</td>
<td>글쓴이</td>
<td>날짜</td>
<td>좋아요</td>
<td>읽음</td>
</tr>
<%
for(int i = 0; i < list.size(); i++){
%>
<tr>
<td><%=list.get(i).get("b_no") %></td>
<td>
<a href="./detail.jsp?bno=<%=list.get(i).get("b_no") %>">
<%=list.get(i).get("b_title") %>
</a>
</td>
<td><%=list.get(i).get("member_name") %></td>
<td><%=list.get(i).get("b_date") %></td>
<td><%=list.get(i).get("b_like") %></td>
<td><%=list.get(i).get("b_read") %></td>
</tr>
<%
}
%>
</table>
<a href="./write.jsp">글쓰기</a>
</body>
</html>
DBConnection 생성
(중복코드 방지위해 DBConnection 따로 생성)
public class DBConnection {
public Connection getConn() {
Connection conn = null;
try {
Class.forName("org.mariadb.jdbc.Driver");
String url = "jdbc:mariadb://wisejia.iptime.org:포트번호/아이디";
String id = "id";
String pw = "pw";
conn = DriverManager.getConnection(url, id, pw);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
BoardDTO 생성
//전송객체
public class BoardDTO {
private int b_no, b_like, b_read, comment;
private String b_title, b_content, member_name, b_date;
public int getB_no() {
return b_no;
}
public void setB_no(int b_no) {
this.b_no = b_no;
}
public int getB_like() {
return b_like;
}
public void setB_like(int b_like) {
this.b_like = b_like;
}
public int getB_read() {
return b_read;
}
public void setB_read(int b_read) {
this.b_read = b_read;
}
public int getComment() {
return comment;
}
public void setComment(int comment) {
this.comment = comment;
}
public String getB_title() {
return b_title;
}
public void setB_title(String b_title) {
this.b_title = b_title;
}
public String getB_content() {
return b_content;
}
public void setB_content(String b_content) {
this.b_content = b_content;
}
public String getMember_name() {
return member_name;
}
public void setMember_name(String member_name) {
this.member_name = member_name;
}
public String getB_date() {
return b_date;
}
public void setB_date(String b_date) {
this.b_date = b_date;
}
}
BoardDAO 생성
* write DAO (게시글 작성)
public class BoardDAO {
//데이터 베이스 접속 객체
// 기능별로 구분
public void write(BoardDTO board) { //BoardDTO에 있는값 뽑아서 쓰기
// 데이터베이스 접속
DBConnection dbConn = new DBConnection();
Connection conn = dbConn.getConn(); //getConn():데이터베이스 연결에 사용할 Connection 객체 가져오기
// 쿼리문 만들기
String sql = "INSERT INTO board SET b_title=?, b_content=?, member_no=?";
//?: SQL 쿼리의 값이 나중에 전달되는 것을 의미(SQL 쿼리 문자열에서 ?는 나중에 전달되는 값으로 대체됨)
//SELECT만 resultset 필요
// SQL인젝션 공격
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, board.getB_title());// 물음표 위치, 값
pstmt.setString(2, board.getB_content());
pstmt.setString(3, board.getMember_name());
//실행 (3가지 방법)
//pstmt.execute(); //boolean값 반환 (실행됬는지 안됬는지)
//pstmt.executeQuery(); //SELECT에서 사용(결과값이 있을떄)
int result = pstmt.executeUpdate(); //int반환(몇개가 영향 받았는지)
System.out.println(result);
} 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();
}
}
}
}- write.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글쓰기</title>
</head>
<body>
<h1>글쓰기</h1>
<form action="./writeAction.jsp" method="post">
<input type="text" name="title"><br>
<textarea name="content"></textarea><br>
<button type="submit">글쓰기</button>
</form>
</body>
</html>
* delete DAO (게시글 삭제)
public void delete(int bno) {
// DB
DBConnection dbConn = new DBConnection();
Connection conn = dbConn.getConn();
// sql 생성
String sql = "DELETE FROM board WHERE b_no=?";
// pstmt 생성
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, bno);
//실행(execute)
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();
}
}
}
}- delete.jsp
<%@page import="com.poseidon.dao.BoardDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
//DAO만들어서
BoardDAO dao = new BoardDAO();
//delete()메소드 호출해서
//bno숫자 잡기
int bno = Integer.parseInt(request.getParameter("bno"));
dao.delete(bno);
//삭제시키기
response.sendRedirect("./board.jsp");
%>
* detail DAO (게시글 보기)
public BoardDTO detail(int bno) {
// DB
DBConnection dbConn = new DBConnection();
Connection conn = dbConn.getConn();
// sql
String sql = "SELECT * FROM boardview WHERE b_no=?";
// pstmt
PreparedStatement pstmt = null;
// rs (execute 반환값이 있어서 rs)
ResultSet rs = null;
// DTO
BoardDTO dto = new BoardDTO();
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, bno);
// execute = 반환값이 있어서 rs
rs = pstmt.executeQuery();
if (rs.next()) {
dto.setB_no(rs.getInt("b_no"));
dto.setB_title(rs.getString("b_title"));
dto.setB_content(rs.getString("b_content"));
dto.setMember_name(rs.getString("member_name"));
dto.setB_like(rs.getInt("b_like"));
dto.setB_read(rs.getInt("b_read"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
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();
}
}
}
return dto;
}- detail.jsp
<%@page import="java.util.HashMap"%>
<%@page import="java.util.Map"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String url = "jdbc:mariadb://wisejia.iptime.org:포트번호/id";
String id = "id";
String pw = "pw";
String sql = "SELECT * FROM boardview WHERE b_no="+ request.getParameter("bno");
Class.forName("org.mariadb.jdbc.Driver");
conn = DriverManager.getConnection(url, id, pw);
//Class.forName()을 사용하여 MariaDB JDBC 드라이버를 로드
pstmt = conn.prepareStatement(sql);
//(conn로) sql 쿼리를 실행할 수 있는 PreparedStatement 객체를 생성
rs = pstmt.executeQuery();
//쿼리문 실행 //ResultSet: SELECT인 경우에만 필요
//즉 사용자가 요청한 게시글 번호(bno)를 기반으로 데이터베이스에 연결하고,
//해당 게시글의 정보를 가져와서 화면에 표시하는 데 필요한 SQL 쿼리문을 실행
Map<String, Object> detail = new HashMap<String, Object>();
if(rs.next()){
detail.put("b_no", rs.getInt("b_no"));
detail.put("b_title", rs.getString("b_title"));
detail.put("b_content", rs.getString("b_content"));
detail.put("b_date", rs.getString("b_date"));
detail.put("member_name", rs.getString("member_name"));
detail.put("b_like", rs.getInt("b_like"));
detail.put("b_read", rs.getInt("b_read"));
detail.put("comment", rs.getInt("comment"));
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>톺아보기</title>
</head>
<body>
번호 : <%=detail.get("b_no") %><br>
제목 : <%=detail.get("b_title") %>
<a href="./delete.jsp?bno=<%=detail.get("b_no") %>">[삭제]</a>
<a href="./update?bno=<%=detail.get("b_no") %>">[수정]</a><br>
날짜 : <%=detail.get("b_date") %><br>
읽음 : <%=detail.get("b_like") %><br>
글쓴이 : <%=detail.get("member_name") %><br>
내용 : <%=detail.get("b_content") %><br>
<button onclick="location.href='./board.jsp'">보드로</button>
</body>
</html>
* update DAO (게시글 수정)
public void update(BoardDTO dto) {
//db
DBConnection DBConn = new DBConnection();
Connection conn = DBConn.getConn();
//sql
String sql = "UPDATE board SET b_title=?, b_content=? WHERE b_no=?";
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dto.getB_title());
pstmt.setString(2, dto.getB_content());
pstmt.setInt(3, dto.getB_no());
//execute
pstmt.execute();
} 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();}}
}
}- update.jsp
<%@page import="com.poseidon.dto.BoardDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
//디스패쳐에서 가져오기
BoardDTO dto = (BoardDTO)request.getAttribute("dto");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>수정하기</title>
</head>
<body>
<div>
<form action="./update" method="post">
<input type="hidden" name="bno" value="<%=dto.getB_no() %>">
<input type="text" name="title" value="<%=dto.getB_title() %>"><br>
<textarea name="content"><%=dto.getB_content() %></textarea><br>
<button type="submit">수정하기</button>
</form>
</div>
</body>
</html>
서블릿 생성(서블릿 방식으로 하는 경우)
package com.poseidon.web;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import javax.security.auth.message.callback.PrivateKeyCallback.Request;
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;
import com.poseidon.dao.BoardDAO;
import com.poseidon.db.DBConnection;
import com.poseidon.dto.BoardDTO;
@WebServlet("/update")
public class Update extends HttpServlet {
private static final long serialVersionUID = 1L;
public Update() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//GET
System.out.println("get방식으로 왔습니다");
//DAO접속
BoardDAO dao = new BoardDAO();
//detail호출
int bno = Integer.parseInt(request.getParameter("bno"));
BoardDTO dto = dao.detail(bno);
//값 jsp로 전달
RequestDispatcher rd = request.getRequestDispatcher("./update.jsp");
request.setAttribute("dto", dto);//데이터베이스에서 가져온 dto붙이기
//setAttribute는 AddObject()개념과 똑같음
rd.forward(request, response);//값 넘기기
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//POST
System.out.println("post방식으로 왔습니다");
//수정하고
BoardDTO dto = new BoardDTO();
dto.setB_no(Integer.parseInt(request.getParameter("bno")));
dto.setB_title(request.getParameter("title"));
dto.setB_content(request.getParameter("content"));
//DAO객체 생성해서 update(dto) 만들어주기
BoardDAO dao = new BoardDAO();
dao.update(dto);
//페이지 이동
//response.sendRedirect("./detail.jsp?bno="+dto.getB_no());
//서블릿이 자체적으로 화면을 구성하고 싶을땐 아래처럼 해주세요.
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=utf-8");
PrintWriter pw = response.getWriter();
pw.println("<html>");
pw.println("<head>");
pw.println("<title>수정이 완료되었습니다</title>");
pw.println("</head>");
pw.println("<body>");
pw.println("<h1>수정이 완료되었습니다 아래 버튼을 눌러주세요</h1>");
pw.println("<a href=\"./detail.jsp?bno="+dto.getB_no()+"\">");
//pw.println("<a href='./detail.jsp?bno="+dto.getB_no()+"'>");
pw.println("상세보기 화면으로 이동하기</a>");
pw.println("</body>");
pw.println("</html>");
pw.close();
}
}728x90
반응형