[게시판] BbsServiceImpl

package org.thinker.bbs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.thinker.common.AbstractService;

public class BbsServiceImpl extends AbstractService implements BbsService {

 @Override
 public void create(BbsVO vo) throws Exception {
  
  String sql = "insert into tbl_bbs(bbsno,title,content,writer) values (seq_bbs.nextval,?,?,?)";
  
  Connection con = null;
  PreparedStatement pstmt = null;
  
  try{
   
   con = getConnection();
   
   logger.debug(con);
   
   pstmt = con.prepareStatement(sql);
   
   pstmt.setString(1,vo.getTitle());
   pstmt.setString(2,vo.getContent());
   pstmt.setString(3,vo.getWriter());
   
   System.out.println(pstmt);
   
   int result = pstmt.executeUpdate();
   
   logger.info(result+"건 입력되었음");
   
  }catch(Exception e){
   logger.debug(e.getMessage());
   throw e;
  }finally{
   if(con != null){try{con.close();logger.debug("con is closed?"+con.isClosed());}catch (Exception e){logger.debug(e);throw e;}}
   if(pstmt != null){try{pstmt.close();logger.debug("pstmt is closed?"+pstmt.isClosed());}catch (Exception e){logger.debug(e);throw e;}}
   
  }
 }

 @Override
 public void delete(int bbsno) throws Exception {
  // TODO Auto-generated method stub

 }

 @Override
 public List<BbsVO> list(int pageno) throws Exception {
  
  List<BbsVO> result = new ArrayList<BbsVO>();
  
  StringBuilder builder = new StringBuilder();
  
  builder.append(" SELECT bbsno, title, content, writer, regdate, viewcnt FROM( ");
  builder.append(" SELECT /*+INDEX_DESC(tbl_bbs pk_bbs)*/ ");
  builder.append(" ROWNUM rn, bbsno, title, content, writer, regdate, viewcnt  ");
  builder.append(" FROM TBL_BBS ");
  builder.append(" WHERE ROWNUM <= ? * 10 ");
  builder.append(" ) ");
  builder.append(" WHERE rn > (? * 10) - 10 ");
  
  Connection con = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  
  try{
   
   con = getConnection();
   pstmt = con.prepareStatement(builder.toString());
   pstmt.setInt(1, pageno);
   pstmt.setInt(2, pageno);
   
   rs = pstmt.executeQuery();

   
   while(rs.next()){
    
    BbsVO vo = new BbsVO();
    vo.setBbsno(rs.getInt(1));
    vo.setTitle(rs.getString(2));
    vo.setContent(rs.getString(3));
    vo.setWriter(rs.getString(4));
    vo.setRegdate(rs.getDate(5));
    vo.setViewCnt(rs.getInt(6));
    
    result.add(vo);   
   }
   
  }catch(Exception e){
   logger.debug(e.getMessage());
   throw e;
   
  }finally{
   if(rs != null){try{rs.close(); System.out.println("rs is closed?"+rs.isClosed()); }catch(Exception e){ }}
   if(pstmt != null){try{pstmt.close(); System.out.println("pstmt is closed?"+pstmt.isClosed()); }catch(Exception e){ }}
   if(con != null){try{con.close(); System.out.println("con is closed?"+con.isClosed()); }catch(Exception e){ }}
  }
    
  return result;
 }

}