NewsDaoImpl

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;

import com.maetamong.vo.CategoryVO;

import com.maetamong.vo.JournalistVO;

import com.maetamong.vo.NewsVO;

public class NewsDaoImpl implements NewsDao {

@Override

public List<CategoryVO> getCategoriesOf(int parentCategoryId) {

// 1. 오라클에 접속할 수 있는 드라이버를 로드한다.

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

throw new RuntimeException(e.getMessage(), e);

}

// 2. Connection, PreparedStatement, ResultSet 객체를 선언한다.

Connection conn = null; // Oracle에 접속하는 객체

PreparedStatement pstmt = null; // Oracle에 쿼리를 실행하는 객체

ResultSet rs = null; // Oracle 쿼리의 결과를 받아오는 객체, Select일때만 사용함

// 3. Oracle에 접속한다.

try {

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "NEWS", "NEWS");

// 4. 실행할 쿼리를 준비한다.

// ? 는 Binding 변수 라고 부른다.

// ? 에 파라미터를 매핑한다.

pstmt = conn.prepareStatement(" SELECT CTGR_ID, CTGR_NM, PRNT_CTGR_ID FROM CTGR WHERE PRNT_CTGR_ID = ? ");

// 5. ? 에 파라미터를 매핑한다.

pstmt.setInt(1, parentCategoryId); // 첫 번째 물음표에 parentCategoryId를

// 매핑한다.(앞에는 숫자 뒤에는 문자)

// 6. 쿼리를 실행하고, 결과를 받아온다.

rs = pstmt.executeQuery(); // Select 일때만 사용함

// 리턴될 결과

List<CategoryVO> categories = new ArrayList<CategoryVO>();

// categories에 들어갈 각각의 카테고리 정보들

CategoryVO categoryVO = null;

// 7. 결과셋(ResultSet) 의 각각의 Row를 탐색한다.

// rs.next() 는 행을 하나씩 내려가겠다 라는 의미.

// 만약, 쿼리의 결과가 여러개라면 while을 쓰고 하나라면 if를 쓴다.

while (rs.next()) {

// categoryVO 를 초기화 시킨다.

categoryVO = new CategoryVO();

// Column 들을 하나씩 꺼내서, CategoryVO 에 담는다.

// 결과셋에서 CTGR_ID 컬럼의 값을 categoryVO 에 저장한다.

categoryVO.setCategoryId(rs.getInt("CTGR_ID"));

// 결과셋에서 CTGR_NM 컬럼의 값을 categoryVO 에 저장한다.

categoryVO.setCategoryName(rs.getString("CTGR_NM"));

// 결과셋에서 PRNT_CTGR_ID 컬럼의 값을 categoryVO 에 저장한다.

categoryVO.setParentCategorID(rs.getInt("PRNT_CTGR_ID"));

// 카테고리 목록에 카테고리를 추가한다.

categories.add(categoryVO);

}

// 결과를 반환한다.

return categories;

} catch (SQLException e) {

throw new RuntimeException(e.getMessage(), e);

}

finally {

// rs, pstmt, conn 을 순서대로 닫는다.

if (rs != null) {

try {

rs.close();

} catch (SQLException e) { }

}

if (pstmt != null) {

try {

pstmt.close();

} catch (SQLException e) { }

}

if (conn != null) {

try {

conn.close();

} catch (SQLException e) { }

}

}

}

@Override

public boolean isCategoryLeafNode(int categoryId) {

return getCategoriesOf(categoryId).size() == 0;

}

@Override

public List<JournalistVO> getJournalist() {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

throw new RuntimeException(e.getMessage(), e);

}

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "NEWS", "NEWS");

pstmt = conn.prepareStatement(" SELECT JRNLST_ID, JRNLST_NM, JRNLST_EMAIL, CMPN FROM JRNLST ");

rs = pstmt.executeQuery();

List<JournalistVO> journalists = new ArrayList<JournalistVO>();

JournalistVO journalistVO = null;

while (rs.next()) {

journalistVO = new JournalistVO();

journalistVO.setJournalistId(rs.getInt("JRNLST_ID"));

journalistVO.setJournalistNmae(rs.getString("JRNLST_NM"));

journalistVO.setJournalistIdEmail(rs.getString("JRNLST_EMAIL"));

journalistVO.setCompany(rs.getString("CMPN"));

journalists.add(journalistVO);

}

return journalists;

} catch (SQLException e) {

throw new RuntimeException(e.getMessage(), e);

} finally {

if (rs != null) {

try {

rs.close();

} catch (SQLException e) { }

}

if (pstmt != null) {

try {

pstmt.close();

} catch (SQLException e) { }

}

if (conn != null) {

try {

conn.close();

} catch (SQLException e) { }

}

}

}

@Override

public boolean addNewNews(NewsVO newsVO) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

throw new RuntimeException(e.getMessage(), e);

}

Connection conn = null;

PreparedStatement pstmt = null;

try {

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "NEWS", "NEWS");

StringBuffer query = new StringBuffer();

query.append(" INSERT INTO NEWS ( ");

query.append("    NEWS_ID, SBJ, CONT, ");

query.append("    RCMD_CNT, JRNLST_ID, CTGR_ID, ");

query.append("    CRT_DT)  ");

query.append(" VALUES ( NEWS_ID_SEQ.NEXTVAL, ?, ?, 0, ?, ?, SYSDATE ) ");

pstmt = conn.prepareStatement(query.toString());

pstmt.setString(1, newsVO.getSubject());

pstmt.setString(2, newsVO.getContent());

pstmt.setInt(3, newsVO.getJournalistId());

pstmt.setInt(4, newsVO.getCategoryId());

// insert, update, delete 할 때 사용됨.

int insertCount = pstmt.executeUpdate();

return insertCount > 0;

} catch (SQLException e) {

throw new RuntimeException(e.getMessage(), e);

} finally {

if (pstmt != null) {

try {

pstmt.close();

} catch (SQLException e) { }

}

if (conn != null) {

try {

conn.close();

} catch (SQLException e) { }

}

}

}

@Override

public List<NewsVO> getNewsesOf(int categoryId) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

throw new RuntimeException(e.getMessage(), e);

}

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "NEWS", "NEWS");

StringBuffer query = new StringBuffer();

query.append(" SELECT N.NEWS_ID ");

query.append(" , N.SBJ ");

query.append(" , N.CONT ");

query.append(" , N.RCMD_CNT ");

query.append(" , J.JRNLST_ID ");

query.append(" , J.JRNLST_NM ");

query.append(" , J.JRNLST_EMAIL ");

query.append(" , J.CMPN ");

query.append(" , G.CTGR_ID ");

query.append(" , G.CTGR_NM ");

query.append(" , G.PRNT_CTGR_ID ");

query.append(" , N.CRT_DT ");

query.append(" FROM NEWS N ");

query.append(" , CTGR G ");

query.append(" , JRNLST J");

query.append(" WHERE N.JRNLST_ID = J.JRNLST_ID ");

query.append(" AND N.CTGR_ID = G.CTGR_ID ");

query.append(" AND G.CTGR_ID = ? ");

pstmt = conn.prepareStatement(query.toString());

pstmt.setInt(1, categoryId);

rs = pstmt.executeQuery();

List<NewsVO> newses = new ArrayList<NewsVO>();

NewsVO newsVO = null;

JournalistVO journalistVO = null;

CategoryVO categoryVO = null;

while (rs.next()) {

newsVO = new NewsVO();

// SELECT NEWS_ID, SBJ, CONT, RCMD_CNT,

// JRNLST_ID, CTGR_ID, CRT_DT FROM NEWS

newsVO.setNewsId( rs.getInt("NEWS_ID") );

newsVO.setSubject( rs.getString("SBJ") );

newsVO.setContent( rs.getString("CONT") );

newsVO.setRecommendCount( rs.getInt("RCMD_CNT") );

// 조인하기

newsVO.setJournalistId(rs.getInt("JRNLST_ID"));

journalistVO = newsVO.getJournalistVO();

journalistVO.setJournalistId( rs.getInt("JRNLST_NM") );

journalistVO.setJournalistNmae( rs.getString("JRNLST_NM") );

journalistVO.setJournalistIdEmail( rs.getString("JRNLST_EMAIL") );

journalistVO.setCompany( rs.getString("CMPN") );

newsVO.setCategoryId(rs.getInt("CTGR_ID"));

categoryVO = newsVO.getCategoryVO();

categoryVO.setCategoryId( rs.getInt("CTRG_ID") );

categoryVO.setCategoryName( rs.getString("CTGR_NM") );

categoryVO.setParentCategorID( rs.getInt("PRNT_CTGR_ID") );

newsVO.setCreatedDate(rs.getString("CRT_DT"));

newses.add(newsVO);

}

return newses;

} catch (SQLException e) {

throw new RuntimeException(e.getMessage(), e);

} finally {

if (rs != null) {

try {

rs.close();

} catch (SQLException e) { }

}

if (pstmt != null) {

try {

pstmt.close();

} catch (SQLException e) { }

}

if (conn != null) {

try {

conn.close();

} catch (SQLException e) { }

}

}

}

@Override

public NewsVO getNewsAt(int newsId) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

throw new RuntimeException(e.getMessage(), e);

}

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "NEWS", "NEWS");

StringBuffer query = new StringBuffer();

query.append(" SELECT N.NEWS_ID ");

query.append(" , N.SBJ ");

query.append(" , N.CONT ");

query.append(" , N.RCMD_CNT ");

query.append(" , J.JRNLST_ID ");

query.append(" , J.JRNLST_NM ");

query.append(" , J.JRNLST_EMAIL ");

query.append(" , J.CMPN ");

query.append(" , G.CTGR_ID ");

query.append(" , G.CTGR_NM ");

query.append(" , G.PRNT_CTGR_ID ");

query.append(" , TO_CHAR(N.CRT_DT, 'YYYY-DD-MM HH:MI:SS') DATE "); // 숫자를 문자로 바꾼다.

query.append(" FROM NEWS N ");

query.append(" , CTGR G ");

query.append(" , JRNLST J");

query.append(" WHERE N.JRNLST_ID = J.JRNLST_ID ");

query.append(" AND N.CTGR_ID = G.CTGR_ID ");

query.append(" AND N.NEWS_ID = ? ");

pstmt = conn.prepareStatement(query.toString());

pstmt.setInt(1, newsId);

rs = pstmt.executeQuery();

NewsVO newsVO = null;

JournalistVO journalistVO = null;

CategoryVO categoryVO = null;

if (rs.next()) {

newsVO = new NewsVO();

// SELECT NEWS_ID, SBJ, CONT, RCMD_CNT,

// JRNLST_ID, CTGR_ID, CRT_DT FROM NEWS

newsVO.setNewsId( rs.getInt("NEWS_ID") );

newsVO.setSubject( rs.getString("SBJ") );

newsVO.setContent( rs.getString("CONT") );

newsVO.setRecommendCount( rs.getInt("RCMD_CNT") );

// 조인하기

newsVO.setJournalistId( rs.getInt("JRNLST_ID") );

journalistVO = newsVO.getJournalistVO();

journalistVO.setJournalistId( rs.getInt("JRNLST_ID") );

journalistVO.setJournalistNmae( rs.getString("JRNLST_NM") );

journalistVO.setJournalistIdEmail( rs.getString("JRNLST_EMAIL") );

journalistVO.setCompany( rs.getString("CMPN") );

newsVO.setCategoryId(rs.getInt("CTGR_ID"));

categoryVO = newsVO.getCategoryVO();

categoryVO.setCategoryId( rs.getInt("CTRG_ID") );

categoryVO.setCategoryName( rs.getString("CTGR_NM") );

categoryVO.setParentCategorID( rs.getInt("PRNT_CTGR_ID") );

newsVO.setCreatedDate(rs.getString("CRT_DT"));

}

return newsVO;

} catch (SQLException e) {

throw new RuntimeException(e.getMessage(), e);

} finally {

if (rs != null) {

try {

rs.close();

} catch (SQLException e) { }

}

if (pstmt != null) {

try {

pstmt.close();

} catch (SQLException e) { }

}

if (conn != null) {

try {

conn.close();

} catch (SQLException e) { }

}

}

}

@Override

public void updateRecommendCount(int newsId) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} 

catch (ClassNotFoundException e) {

throw new RuntimeException(e.getMessage(), e);

}

Connection conn = null;

PreparedStatement pstmt = null;

try {

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "NEWS", "NEWS");

StringBuffer query = new StringBuffer();

query.append(" UPDATE NEWS ");

query.append(" SET RCMD_CNT = RCMD_CNT + 1 ");

query.append(" WHERE NEWS_ID = ? ");

pstmt = conn.prepareStatement(query.toString());

pstmt.setInt(1, newsId);

pstmt.executeUpdate();

} 

catch (SQLException e) {

throw new RuntimeException(e.getMessage(), e);

}

finally {

if ( pstmt != null ) {

try {

pstmt.close();

} catch (SQLException e) { }

}

if ( conn != null ) {

try {

conn.close();

} catch (SQLException e) { }

}

}

}

@Override

public void deleteNews(int newsId) {

try {

Class.forName("oracle.jbdc.driver.OracleDriver");

} 

catch (ClassNotFoundException e) {

throw new RuntimeException(e.getMessage(), e);

}

Connection conn = null;

PreparedStatement pstmt = null;

try {

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE ", "NEWS", "NEWS");

StringBuffer query = new StringBuffer();

query.append(" DELETE ");

query.append(" FROM NEWS");

query.append(" WHERE NEWS_ID = ?");

pstmt = conn.prepareStatement(query.toString());

pstmt.setInt(1, newsId);

pstmt.executeUpdate();

} 

catch (SQLException e) {

throw new RuntimeException(e.getMessage(), e);

}

finally {

if ( pstmt != null ) {

try {

pstmt.close();

} catch (SQLException e) { }

}

if ( conn != null ) {

try {

conn.close();

} catch (SQLException e) { }

}

}

}

}