지난번에 list.jsp에서 데이터베이스 부분을 DAO(Database Access Object)(BoardDao)라는 부분으로 분리해냈지.
마찬가지고 insert.jsp도 BoardDao에 분리해보자.
public boolean insertBoard(BoardDto boardDto){
Connection cn = null;
PreparedStatement st = null;
String sql = "insert into board (seq, title, name, password, content) " +
"values (boardseq.nextval, ?, ?, ?, ?)";
boolean result = false;
try {
Class.forName("oracle.jdbc.OracleDriver"); //드라이버 있는지 확인
cn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "oraclejava", "oraclejava");
st = cn.prepareStatement(sql); //오라클에 쿼리 먼저 던져놔.
st.setString(1, boardDto.getTitle());
st.setString(2, boardDto.getName());
st.setString(3, boardDto.getPassword());
st.setString(4, boardDto.getContent());
if (st.executeUpdate() > 0 ) result = true; //영향받은 레코드수 리턴
} catch (Exception e){
e.printStackTrace();
} finally {
if (st!= null) try { st.close(); } catch(Exception e) {}
if (cn!= null) try { cn.close(); } catch(Exception e) {}
}
return result;
}
<%@page import="board.model.BoardDto"%>
<%@page import="board.model.BoardDao"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String name = request.getParameter("name");
String title = request.getParameter("title");
String password = request.getParameter("password");
String content = request.getParameter("content");
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
BoardDto boardDto = new BoardDto();
boardDto.setName(name);
boardDto.setTitle(title);
boardDto.setPassword(password);
boardDto.setContent(content);
BoardDao boardDao = new BoardDao();
boolean result = boardDao.insertBoard(boardDto);
%>
<script>
alert('<%= result == true ? "글이 입력되었습니다." : "글 입력 실패" %>');
location.href='<%= result == true ? "list.jsp" : "javascript:history.back();" %>';
</script>
</body>
</html>
</table> <a href="insert.jsp">글쓰기</a> </body> </html>
이제 BoardDao의 중복된 코드를 처리해 보자.
package board.model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class BoardDao {
private Connection cn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
public BoardDao(){
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} //드라이버 있는지 확인
}
public void getConnection(){
try {
cn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe", "oraclejava", "oraclejava" );
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void dbClose(){
if (rs!= null) try { rs.close(); } catch(Exception e) {}
if (ps!= null) try { ps.close(); } catch(Exception e) {}
if (cn!= null) try { cn.close(); } catch(Exception e) {}
}
public List<BoardDto> getList(){
List<BoardDto> list = new ArrayList<BoardDto>();
String sql =
"SELECT seq, title, name, writeday, readcount " +
"FROM board " +
"ORDER BY seq DESC ";
try {
getConnection();
ps = cn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
BoardDto boardDto = new BoardDto();
boardDto.setSeq(rs.getInt("seq"));
boardDto.setTitle(rs.getString("title"));
boardDto.setName(rs.getString("name"));
boardDto.setWriteday(rs.getString("writeday"));
boardDto.setReadcount(rs.getInt("readcount"));
list.add(boardDto);
}
} catch(Exception e) {
e.printStackTrace();
} finally {
dbClose();
}
return list;
}
public boolean insertBoard(BoardDto boardDto){
String sql =
"insert into board (seq, title, name, password, content) " +
"values(boardseq.nextval, ?, ?, ?, ?)";
boolean result = false;
try {
getConnection();
ps = cn.prepareStatement(sql);
ps.setString(1, boardDto.getTitle());
ps.setString(2, boardDto.getName());
ps.setString(3, boardDto.getPassword());
ps.setString(4, boardDto.getContent());
if(ps.executeUpdate() > 0) result = true; //영향받은 레코드수 리턴
} catch(Exception e){
e.printStackTrace();
} finally {
dbClose();
}
return result;
}
}
