[KOSTA] Spring 기반 Cloud 서비스 개발자 양성과정 50일차 - JDBC 실습

JUNBEOM PARK·2022년 4월 13일
0
post-thumbnail

📃 JDBC를 사용하여 오라클 DB내 데이터를 화면 출력

BoardDao.java

public List<Board> listBoard() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Board> list = null; //ResultSet => Board => List
		
		String sql = "select * from board order by seq desc";
		
		try {
			conn = getDBCPConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			list = new ArrayList<Board>();
			
			while(rs.next()) {
				Board board = new Board(); //1객체 <= 1로우
				board.setSeq(rs.getInt("seq"));
				board.setTitle(rs.getString("title"));
				board.setWriter(rs.getString("writer"));
				board.setContents(rs.getString("contents"));
				board.setRegdate(rs.getString("regdate"));
				board.setHitcount(rs.getInt("hitcount"));
				
				list.add(board);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(pstmt != null) {
				try {
					
				} catch (Exception e2) {}
			}
		}
		
		return list;
	}

list.jsp

<%@page import="kosta.bean.Board"%>
<%@page import="java.util.List"%>
<%@page import="kosta.bean.BoardDao2"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%
	BoardDao2 dao = BoardDao2.getInstance();
	List<Board> list = dao.listBoard();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<a href="insert_form.jsp">글쓰기</a>
	<table border="1">
		<tr>
			<td>글번호</td>
			<td>제목</td>
			<td>작성자</td>
			<td>작성일자</td>
			<td>조회수</td>
		</tr>
		<% for(int i = 0; i < list.size(); i++) { 
				Board board = list.get(i);
		%>
		<tr>
			<td><%= board.getSeq() %></td>
			<td><a href="detail.jsp?seq=<%= board.getSeq() %>"><%= board.getTitle() %></a></td>
			<td><%= board.getWriter() %></td>
			<td><%= board.getRegdate() %></td>
			<td><%= board.getHitcount() %></td>
		</tr>
		<%} %>
	</table>
</body>
</html>




📃 각 게시글에 대한 상세 정보 가져오기

board.java

	public Board detailBoard(int seq) {
		Board board = new Board();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from board where seq=?";
		
		try {
			conn = getDBCPConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, seq);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				
				board.setSeq(rs.getInt("seq"));
				board.setTitle(rs.getString("title"));
				board.setWriter(rs.getString("writer"));
				board.setContents(rs.getString("contents"));
				board.setRegdate(rs.getString("regdate"));
				board.setHitcount(rs.getInt("hitcount"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			
		}
		
		return board;
	}

detail.jsp

<%@page import="kosta.bean.Board"%>
<%@page import="kosta.bean.BoardDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!-- 글번호 받기 String -> int
	BoardDao 객체 구하기
	BoardDao detailBoard() 호출
	결과값을 화면에 적절히 출력 -->    

<%
	int seq = 1;
	if(request.getParameter("seq") != null){
		seq = Integer.parseInt(request.getParameter("seq"));
	}
	
	BoardDao dao = BoardDao.getInstance();
	Board board = dao.detailBoard(seq);

%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<ul>
	<li><%= board.getSeq() %></li>
	<li><%= board.getTitle() %></li>
	<li><%= board.getWriter() %></li>
	<li><%= board.getContents() %></li>
	<li><%= board.getHitcount() %></li>

</ul>
<br>

<a href="updateForm.jsp?seq=<%=seq%>">글수정</a>

</body>
</html>



📃 글 내용 수정하기


BoardDao.java

public int update(Board board) {
		int re = -1;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "update board set title=?,contents=? where seq=?";
		try {
			conn = getDBCPConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, board.getTitle());
			pstmt.setString(2, board.getContents());
			pstmt.setInt(3, board.getSeq());
			
			re = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {}
			}
		}
		return re;
	}

updateForm.jsp

<%@page import="kosta.bean.Board"%>
<%@page import="kosta.bean.BoardDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
    <%
	int seq = 1;
	if(request.getParameter("seq") != null){
		seq = Integer.parseInt(request.getParameter("seq"));
	}
	
	BoardDao dao = BoardDao.getInstance();
	Board board = dao.detailBoard(seq);

%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h3>글 수정 폼</h3>
	<form action="updateProc.jsp" method="post">
		<input type="hidden" name="seq" value="<%= board.getSeq()%>">
		작성자 : <%= board.getWriter() %><br>
		제목 : <input type="text" name="title" value="<%= board.getTitle()%>"><br>
		내용 : <br>
		<textarea rows="6" cols="70" name="contents"><%= board.getContents()%></textarea>
		<input type="submit" value="수정완료">
	</form>
</body>
</html>

updateProc.jsp

<%@page import="kosta.bean.BoardDao"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page import="kosta.bean.Board"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%
	request.setCharacterEncoding("utf-8");
%>
<jsp:useBean id="board" class="kosta.bean.Board"/>
<jsp:setProperty property="*" name="board"/>
<%
	BoardDao dao = BoardDao.getInstance();
	int re = dao.update(board);
	
	if(re > 0){
		response.sendRedirect("list.jsp");
	}

%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

</body>
</html>
profile
DB 엔지니어👍

0개의 댓글