[KOSTA] Spring 기반 Cloud 서비스 개발자 양성과정 50일차 - JDBC 실습
0
💻 KOSTA Spring 기반 Cloud 서비스 구현 개발자 양성 과정 실습
목록 보기
40/47
📃 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>
DB 엔지니어👍