자바, 스프링, JPA, C#, WPF, Xamarin, SQL, DB, Angular, AI, Block Chain을 사랑하는 풀스택개발자(mylife68@nate.com): 스트럿츠 멀티 게시판5. 사용자( 2

2013년 8월 11일 일요일

스트럿츠 멀티 게시판5. 사용자( 2 - 2 ) BoardDAO.java( 2 )

/*
* 개요 : 게시판 검색 리스트
* 작성일 : 2005-08-10
* 작성자 : 김길재
* 수정자 :
*/
public List list( String board_code ,
String current_page ,
String search_type ,
String search_keyword )
{
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 , search_type , search_keyword ); //게시물 갯수
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 += " AND " + search_type + " LIKE '%' || ? || '%' \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 += " AND " + search_type + " LIKE '%' || ? || '%' \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 += " AND " + search_type + " LIKE '%' || ? || '%' \n";
sql += " ORDER BY BOARD_NO DESC , \n";
sql += " BOARD_GRADE ASC , \n";
sql += " WRITE_DATE DESC \n";
}

pstmt = con.prepareStatement(sql);

KKJLog.info( "===================================" );
KKJLog.info( "sql = " );
KKJLog.info( sql );
KKJLog.info( "search_type = " + search_type );
KKJLog.info( "search_keyword = " + Util.uni2ksc( search_keyword ) );
KKJLog.info( "===================================" );

pstmt.setString( 1 , board_code );
pstmt.setString( 2 , Util.uni2ksc( search_keyword ) );

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

}

/*
* 개요 : 게시물 리턴
* 작성일 : 2005-08-13
* 작성자 : 김길재
* 수정자 :
*/
public BoardVO view( String board_code ,
String board_no ,
String board_grade ,
String write_date )
{
BoardVO board = new BoardVO();

try
{
con = DriverManager.getConnection(ConnectionPoolManager.URL_PREFIX+"multiboard");

String sql = " SELECT BOARD_CODE , ";
sql += " BOARD_NO , ";
sql += " ( SUBSTR( WRITE_DATE , 1 , 4 ) || '년' || SUBSTR( WRITE_DATE , 6 , 2 ) || '월' || SUBSTR( WRITE_DATE , 9 , 2 ) || '일' ) AS SHORT_WRITE_DATE , ";
sql += " WRITE_DATE , ";
sql += " TITLE , ";
sql += " HIT , ";
sql += " CONTENT_TYPE , ";
sql += " CONTENT , ";
sql += " FILE_NAME , ";
sql += " MEMBER_ID ";
sql += " FROM BOARD ";
sql += " WHERE BOARD_CODE = ? ";
sql += " AND BOARD_NO = ? ";
sql += " AND BOARD_GRADE = ? ";
sql += " AND WRITE_DATE = ? ";

pstmt = con.prepareStatement(sql);

pstmt.setString( 1 , board_code );
pstmt.setInt( 2 , Integer.parseInt( board_no ) );
pstmt.setInt( 3 , Integer.parseInt( board_grade ) );
pstmt.setString( 4 , write_date );

rs = pstmt.executeQuery();

while(rs.next())
{

board.setBoard_code( rs.getString( "BOARD_CODE" ) );
board.setBoard_no( Integer.toString( rs.getInt( "BOARD_NO" ) ) );
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.setContent_type( rs.getString( "CONTENT_TYPE" ) );
board.setFile_name ( rs.getString( "FILE_NAME" ) );
board.setMember_id( rs.getString( "MEMBER_ID" ) );

//CLOB읽어오기
StringBuffer sbuf = new StringBuffer();
Reader reader = rs.getCharacterStream( "CONTENT" );
char[] buffer = new char[ 1024 ];
int read;
while( ( read = reader.read( buffer , 0 , 1024 ) ) != -1 )
{
sbuf.append( buffer, 0 , read );
}
reader.close();

board.setContent( sbuf.toString() );
}
con.commit();
}
catch (Exception e)
{
try
{
con.rollback();
}
catch( Exception e1 )
{}
KKJLog.info( "//////////////////////////////////////////////" );
KKJLog.info( "view() 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 board;

}

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecStruts&wr_id=102

2013년 8월 11일 일요일

스트럿츠 멀티 게시판5. 사용자( 2 - 2 ) BoardDAO.java( 2 )

댓글 없음:

댓글 쓰기

지난주 페이지뷰