/////////// BoardDAO.java //////////////////////////////
package
board.model;
import java.sql.Connection;
import
java.sql.DriverManager;
import java.sql.PreparedStatement;
import
java.sql.ResultSet;
import oracle.jdbc.driver.OracleResultSet;
import
oracle.sql.CLOB;
import java.io.CharArrayReader;
import java.io.Reader;
import java.io.Writer;
import java.util.List;
import
java.util.ArrayList;
import org.apache.struts.upload.FormFile;
import com.bitmechanic.sql.ConnectionPoolManager;
import
board.model.BoardVO;
import manager.model.BoardMasterVO;
import
manager.model.ManagerDAO;
import multiboard.Util;
import
multiboard.FileUploadUtil;
import multiboard.KKJLog;
import
multiboard.Constants;
public class BoardDAO
{
Connection
con=null;
PreparedStatement pstmt=null;
ResultSet rs = null;
Constants constants = new Constants();
String sql = "";
/*
* 개요 : 게시판 일반 리스트
* 작성일 : 2005-08-10
*
작성자 : 김길재
* 수정자 :
*/
public List list( String board_code ,
String current_page )
{
ManagerDAO ManagerDAO = new
ManagerDAO();
BoardMasterVO BoardMaster =
ManagerDAO.view( board_code );
List boardList =
new ArrayList();
try
{
con =
DriverManager.getConnection(ConnectionPoolManager.URL_PREFIX+"multiboard");
String is_page_count =
BoardMaster.getIs_page_count(); // 페이징 사용 여부
//페이지를 나눌 경우
if( is_page_count.equals( "Y" ) )
{
int current_pageInt =
Integer.parseInt( current_page ); //현재 페이지
int cnt_per_page
= Integer.parseInt( BoardMaster.getList_per_page() ); //한 페이지당 나타낼 게시물 리스트 갯수
int board_count = count( board_code ); //게시물 갯수
int page_count = ( board_count / cnt_per_page ) + 1; //페이지
갯수
// 마지막 페이지가 아닐경우 cnt_per_page개씩의 레코드를 리턴하는 sql문 작성
//
마지막 페이지일 경우 cnt_per_page개씩 나눈 나머지만큼의 레코드를 리턴하는 sql문 작성
if( current_pageInt < page_count )
{
sql = " SELECT * FROM ( \n";
sql += " SELECT * FROM ( \n";
sql += " SELECT BOARD_CODE , \n";
sql += " BOARD_NO ,
\n";
sql += "
BOARD_GRADE , \n";
sql += "
( SUBSTR( WRITE_DATE , 1 , 4 ) || '년' || SUBSTR(
WRITE_DATE , 6 , 2 ) || '월' || SUBSTR( WRITE_DATE , 9 , 2 ) || '일' ) AS
SHORT_WRITE_DATE , \n";
sql += "
WRITE_DATE , \n";
sql +=
" TITLE , \n";
sql += " HIT , \n";
sql += " MEMBER_ID \n";
sql += " FROM
BOARD \n";
sql += "
WHERE BOARD_CODE = ? \n";
sql += "
ORDER BY BOARD_NO DESC , \n";
sql += " BOARD_GRADE ASC , \n";
sql += "
WRITE_DATE DESC \n";
sql += "
) WHERE ROWNUM < " + ( ( current_pageInt * cnt_per_page ) + 1 ) + "
ORDER BY BOARD_NO ASC , \n";
sql += "
BOARD_GRADE DESC , \n";
sql +=
"
WRITE_DATE ASC \n";
sql += " ) WHERE ROWNUM < " + ( cnt_per_page + 1 )+ " ORDER BY
BOARD_NO DESC , \n";
sql += "
BOARD_GRADE ASC , \n";
sql
+= " WRITE_DATE DESC \n";
}
else if( current_pageInt == page_count )
{
int board_mod = (
board_count - ( ( page_count - 1 ) * cnt_per_page ) ) + 1;
sql = " SELECT * FROM ( \n";
sql += " SELECT * FROM ( \n";
sql += " SELECT
BOARD_CODE , \n";
sql += "
BOARD_NO , \n";
sql += "
BOARD_GRADE , \n";
sql += " ( SUBSTR( WRITE_DATE , 1 , 4
) || '년' || SUBSTR( WRITE_DATE , 6 , 2 ) || '월' || SUBSTR( WRITE_DATE , 9 , 2 )
|| '일' ) AS SHORT_WRITE_DATE , \n";
sql += "
WRITE_DATE , \n";
sql += " TITLE , \n";
sql += " HIT , \n";
sql += "
MEMBER_ID \n";
sql += "
FROM BOARD \n";
sql += "
WHERE BOARD_CODE = ? \n";
sql
+= " ORDER BY BOARD_NO DESC , \n";
sql += " BOARD_GRADE ASC
, \n";
sql += "
WRITE_DATE DESC \n";
sql += "
) WHERE ROWNUM < " + ( ( current_pageInt * cnt_per_page ) + 1
) + " ORDER BY BOARD_NO ASC , \n";
sql += "
BOARD_GRADE DESC , \n";
sql += "
WRITE_DATE ASC \n";
sql += " ) WHERE ROWNUM < " + board_mod + " ORDER BY
BOARD_NO DESC , \n";
sql += "
BOARD_GRADE ASC , \n";
sql += "
WRITE_DATE DESC \n";
}
}
//페이지를 나누지 않을 경우
else
{
sql = " SELECT BOARD_CODE , \n";
sql += " BOARD_NO , \n";
sql += "
BOARD_GRADE , \n";
sql += " ( SUBSTR(
WRITE_DATE , 1 , 4 ) || '년' || SUBSTR( WRITE_DATE , 6 , 2 ) || '월' || SUBSTR(
WRITE_DATE , 9 , 2 ) || '일' ) AS SHORT_WRITE_DATE , \n";
sql
+= " WRITE_DATE , \n";
sql += "
TITLE , \n";
sql += " HIT , \n";
sql += " MEMBER_ID \n";
sql += "
FROM BOARD \n";
sql += " WHERE BOARD_CODE = ? \n";
sql += " ORDER BY BOARD_NO DESC , \n";
sql += " BOARD_GRADE ASC , \n";
sql += " WRITE_DATE DESC \n";
}
pstmt = con.prepareStatement(sql);
pstmt.setString( 1 , board_code );
rs = pstmt.executeQuery();
while(rs.next())
{
BoardVO board = new BoardVO();
board.setBoard_code( rs.getString( "BOARD_CODE" ) );
board.setBoard_no( Integer.toString( rs.getInt( "BOARD_NO" ) ) );
board.setBoard_grade( Integer.toString( rs.getInt(
"BOARD_GRADE" ) ) );
board.setShort_write_date(
rs.getString( "SHORT_WRITE_DATE" ) );
board.setWrite_date( rs.getString( "WRITE_DATE" ) );
board.setTitle( rs.getString( "TITLE" ) );
board.setHit(
Integer.toString( rs.getInt( "HIT" ) ) );
board.setMember_id( rs.getString( "MEMBER_ID" ) );
boardList.add( board );
}
con.commit();
}
catch (Exception e)
{
try
{
con.rollback();
}
catch( Exception e1 )
{}
KKJLog.info(
"//////////////////////////////////////////////" );
KKJLog.info( "list( String , String ) Error : " + e.toString() );
KKJLog.info( "//////////////////////////////////////////////" );
return null;
}
finally
{
try
{
if ( rs !=
null )
rs.close();
if ( pstmt != null )
pstmt.close();
if ( con != null )
con.close();
}
catch ( Exception ignore
)
{
}
}
return boardList;
}
댓글 없음:
댓글 쓰기