짤막한 MVC설명
오늘의 코드
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<!-- DBCP(Database Connection Pool)을 사용하기 위한 Java Bean 설정 -->
<Resource
auth = "Container"
driverClassName = "oracle.jdbc.driver.OracleDriver"
url = "jdbc:oracle:thin:@localhost:1521:xe"
username = "scott"
password = "tiger"
name = "dbcp/orcl"
type = "javax.sql.DataSource"
maxTotal = "50"
maxWaitMillis = "1000"
/>
<!--
url : DB 연결 경로
username : DB 사용자 이름
password : DB 사용자 비밀번호
name : JNDI에서 찾아올 이름
maxTotal : 동시에 접속할 수 있는 최대 연결 개수
maxWaitMillis : 접속 지연시 기다릴 시간(millisecond)
-->
</Context>
package edu.web.persistence;
public interface BoardQuery {
public static final String TABLE_NAME = "BOARD";
public static final String COL_BOARD_ID = "BOARD_ID";
public static final String COL_BOARD_TITLE = "BOARD_TITLE";
public static final String COL_BOARD_CONTENT = "BOARD_CONTENT";
public static final String COL_MEMBER_ID = "MEMBER_ID";
public static final String COL_BOARD_DATE_CREATED = "BOARD_DATE_CREATED";
// 게시글 등록
// INSERT INTO BOARD
// VALUES (BOARD_SEQ.NEXTVAL, ?, ?, ?, SYSDATE);
public static final String SQL_INSERT =
"INSERT INTO " + TABLE_NAME + " VALUES "
+ "(BOARD_SEQ.NEXTVAL, ?, ?, ?, SYSDATE)";
// 전체 게시글 조회
// SELECT * FROM BOARD ORDER BY BOARD_ID DESC;
public static final String SQL_SELECT_ALL =
"SELECT * FROM " + TABLE_NAME +
" ORDER BY " + COL_BOARD_ID + " DESC";
// 상세 게시글 조회
// SELECT * FROM BOARD
// WHERE BOARD_ID = 1;
public static final String SQL_SELECT_BY_BOARD_ID =
"SELECT * FROM " + TABLE_NAME + " WHERE "
+ COL_BOARD_ID + " = ?";
// 상세 게시글 수정
// UPDATE BOARD
// SET
// BOARD_TITLE = 'TEST',
// BOARD_CONTENT = 'TEST',
// BOARD_DATE_CREATED = SYSDATE
// WHERE BOARD_ID = 1;
public static final String SQL_UPDATE =
"UPDATE " + TABLE_NAME + " SET "
+ COL_BOARD_TITLE + " = ?, "
+ COL_BOARD_CONTENT + " = ?, "
+ COL_BOARD_DATE_CREATED + " = SYSDATE "
+ "WHERE " + COL_BOARD_ID + " = ?";
// 상세 게시글 삭제
// DELETE BOARD WHERE BOARD_ID = ?
public static final String SQL_DELETE =
"DELETE " + TABLE_NAME +
" WHERE " + COL_BOARD_ID + " = ?";
}
package edu.web.persistence;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import edu.web.dbcp.connmgr.ConnMgr;
import edu.web.domain.BoardVO;
public class BoardDAOImple implements BoardDAO, BoardQuery{
private static BoardDAOImple instance = null;
private BoardDAOImple() {}
public static BoardDAOImple getInstance() {
if(instance == null) {
instance = new BoardDAOImple();
}
return instance;
}
@Override
public int insert(BoardVO vo) {
int result = 0;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ConnMgr.getConnection();
pstmt = conn.prepareStatement(SQL_INSERT);
pstmt.setString(1, vo.getBoardTitle());
pstmt.setString(2, vo.getBoardContent());
pstmt.setString(3, vo.getMemberId());
result = pstmt.executeUpdate();
System.out.println("insert 성공");
} catch (Exception e) {
e.printStackTrace();
} finally {
ConnMgr.close(conn, pstmt);
}
return result;
}
@Override
public List<BoardVO> select() {
List<BoardVO> list = new ArrayList<>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = ConnMgr.getConnection();
pstmt = conn.prepareStatement(SQL_SELECT_ALL);
rs = pstmt.executeQuery();
int boardId;
String boardTitle;
String boardContent;
String memberId;
Date boardDateCreated;
BoardVO vo = null;
while(rs.next()) {
boardId = rs.getInt(COL_BOARD_ID);
boardTitle = rs.getString(COL_BOARD_TITLE);
boardContent = rs.getString(COL_BOARD_CONTENT);
memberId = rs.getString(COL_MEMBER_ID);
boardDateCreated = rs.getTimestamp(COL_BOARD_DATE_CREATED);
vo = new BoardVO(boardId, boardTitle, boardContent, memberId, boardDateCreated);
list.add(vo);
}
System.out.println("select all 성공");
} catch (Exception e) {
e.printStackTrace();
} finally {
ConnMgr.close(conn, pstmt, rs);
}
return list;
}
@Override
public BoardVO select(int boardId) {
BoardVO vo = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = ConnMgr.getConnection();
pstmt = conn.prepareStatement(SQL_SELECT_BY_BOARD_ID);
pstmt.setInt(1, boardId);
rs = pstmt.executeQuery();
String boardTitle;
String boardContent;
String memberId;
Date boardDateCreated;
if(rs.next()) {
// boardId = rs.getInt(COL_BOARD_ID);
boardTitle = rs.getString(COL_BOARD_TITLE);
boardContent = rs.getString(COL_BOARD_CONTENT);
memberId = rs.getString(COL_MEMBER_ID);
boardDateCreated = rs.getTimestamp(COL_BOARD_DATE_CREATED);
vo = new BoardVO(boardId, boardTitle, boardContent, memberId, boardDateCreated);
}
System.out.println("select by board_id 성공");
} catch (Exception e) {
e.printStackTrace();
} finally {
ConnMgr.close(conn, pstmt, rs);
}
return vo;
}
@Override
public int update(BoardVO vo) {
int result = 0;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ConnMgr.getConnection();
pstmt = conn.prepareStatement(SQL_UPDATE);
pstmt.setString(1, vo.getBoardTitle());
pstmt.setString(2, vo.getBoardContent());
pstmt.setInt(3, vo.getBoardId());
result = pstmt.executeUpdate();
System.out.println("update 성공");
} catch (Exception e) {
e.printStackTrace();
} finally {
ConnMgr.close(conn, pstmt);
}
return result;
}
@Override
public int delete(int boardId) {
int result = 0;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ConnMgr.getConnection();
pstmt = conn.prepareStatement(SQL_DELETE);
pstmt.setInt(1, boardId);
result = pstmt.executeUpdate();
System.out.println("delete 성공");
} catch (Exception e) {
e.printStackTrace();
} finally {
ConnMgr.close(conn, pstmt);
}
return result;
}
}
package edu.web.persistence;
import java.util.List;
import edu.web.domain.BoardVO;
public interface BoardDAO {
// 게시글 등록
int insert(BoardVO vo);
// 게시글 전체 조회
List<BoardVO> select();
// 게시글 상세 조회
BoardVO select(int boardId);
// 게시글 수정
int update(BoardVO vo);
// 게시글 삭제
int delete(int boardId);
}
package edu.web.domain;
import java.util.Date;
public class BoardVO {
private int boardId;
private String boardTitle;
private String boardContent;
private String memberId;
private Date boardDateCreated;
public BoardVO() {}
public BoardVO(int boardId, String boardTitle, String boardContent, String memberId, Date boardDateCreated) {
super();
this.boardId = boardId;
this.boardTitle = boardTitle;
this.boardContent = boardContent;
this.memberId = memberId;
this.boardDateCreated = boardDateCreated;
}
public int getBoardId() {
return boardId;
}
public void setBoardId(int boardId) {
this.boardId = boardId;
}
public String getBoardTitle() {
return boardTitle;
}
public void setBoardTitle(String boardTitle) {
this.boardTitle = boardTitle;
}
public String getBoardContent() {
return boardContent;
}
public void setBoardContent(String boardContent) {
this.boardContent = boardContent;
}
public String getMemberId() {
return memberId;
}
public void setMemberId(String memberId) {
this.memberId = memberId;
}
public Date getBoardDateCreated() {
return boardDateCreated;
}
public void setBoardDateCreated(Date boardDateCreated) {
this.boardDateCreated = boardDateCreated;
}
@Override
public String toString() {
return "BoardVO [boardId=" + boardId + ", boardTitle=" + boardTitle + ", boardContent=" + boardContent
+ ", memberId=" + memberId + ", boardDateCreated=" + boardDateCreated + "]";
}
}
package edu.web.dbcp.connmgr;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
// DBCP(Data Base Connection Pool)
// - 브라우저에서 서버에 호출 시 매번 DB 연결 객체를 생성하면 과부하가 발생할 수 있음
// - 이를 해결하기 위해 다수의 연결을 컨트롤하는 기법을 DBCP이라 함
// - DBCP 원리
// 1) 웹 컨테이너가 실행되면서 커넥션(Connection) 객체를 미리 풀(Pool)에 생성
// 2) 풀에 저장된 커넥션 객체를 필요할 때 쓰고 반환
// 3) 미리 생성하기 때문에 데이터베이스에 부하를 줄이고 유동적으로 연결을 관리
// DBCP 설정
// 1. ojdbc6.jar 파일을 \webapp\WEB-INF\lib에 저장
// 2. 톰캣 설치 경로\lib 폴더에 있는 tomcat-dbcp.jar 파일을
// 웹 프로젝트 경로\webapp\WEB-INF\lib에 저장
// 3. \webapp\META-INF\context.xml 파일에 Resource 태그 추가
public class ConnMgr {
public static Connection getConnection() throws Exception {
Connection conn = null;
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:comp/env");
DataSource ds = (DataSource) envContext.lookup("dbcp/orcl");
conn = ds.getConnection();
System.out.println("DBCP 연결 성공!");
return conn;
}
public static void close(Connection conn, Statement stmt) {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs) {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
package edu.web.controller;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("*.do") // *.do : ~.do로 선언된 HTTP 호출에 대해 반응
public class BoardController extends HttpServlet {
private static final long serialVersionUID = 1L;
public BoardController() {
}
@Override
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String requestURI = request.getRequestURI();
String requestMethod = request.getMethod();
System.out.println("호출 경로 : " + requestURI);
System.out.println("호출 방식 : " + requestMethod);
if(requestURI.contains("list.do")) {
System.out.println("list 호출 확인");
}
}
}
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:forward page="list.do"></jsp:forward>
</body>
</html>