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;
}
}