public void init ( ) {
connectionManager = DBConnectionManager.getInstance ( ) ;
}
public BBSResultStatus addItem ( Item item ) {
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
if ( item.getReference ( ) == 0 ) {
String query = "Select max ( num ) as top from bbslist" ;
ResultSet topNu = stmt.executeQuery ( query ) ;
topNu.next ( ) ;
int number = 0 ;
if ( topNu.getInt ( "top" ) == 0 ) number = 1;
number = topNu.getInt ( "top" ) ;
item.setReference ( ++number ) ;
System.out.println ( "reference로 자신의 number ( " +number + " ) 를 갔는다." ) ;
item.setStep ( 1 ) ;
item.setLev ( 1 ) ;
}
else {
int lev = 0 ;
String levQuery = "select max ( lev ) as top from bbslist where reference = " + item.getReference ( ) ;
ResultSet levMax = stmt.executeQuery ( levQuery ) ;
levMax.next ( ) ;
lev = levMax.getInt ( "top" ) ;
if ( lev == 1 ) {
item.setLev ( 2 ) ;
item.setStep ( 2 ) ;
System.out.println ( "답변 게시물이 처음입니다." ) ;
}
else {
String query = "select min ( step ) as nu from bbslist where reference = "+item.getReference ( ) + " and lev<=" + item.getLev() + " and step>" + item.getStep() ;
ResultSet rs = stmt.executeQuery ( query ) ;
rs.next ( ) ;
int nu = rs.getInt ( "nu" ) ;
if ( nu == 0 ) {
item.setLev ( item.getLev ( ) +1 ) ;
String stepQuery = "select max ( step ) as top from bbslist where reference = "+item.getReference ( ) ;
ResultSet step = stmt.executeQuery ( stepQuery ) ;
step.next ( ) ;
int stepMax = step.getInt ( "top" ) ;
item.setStep ( stepMax+1 ) ;
}
else {
item.setLev ( item.getLev ( ) + 1 ) ;
item.setStep ( nu ) ;
String stepQuery = "update bbslist set step = step+1 where reference = " + item.getReference ( ) + " and step>= " +nu ;
int answer = stmt.executeUpdate ( stepQuery ) ;
if ( answer == 0 ) System.out.println("update 성공");
else System.out.println ( "update 실패!!!" ) ;
}
}
}
stmt.executeUpdate ( "Insert BBSList values ( '" + item.getTeamID ( ) +"','"+ item.getBBSBoardName ( ) +"','" + item.getAuthor()+"','"+ item.getEmail()+"','"+ item.getTitle()+"','" + item.getItemType()+"','"+ item.getContent()+"','"+ item.getAttachedFile()+"','"+ item.getDate()+"'," + item.getViewCount()+","+ item.getReference() + "," + item.getStep() +"," + item.getLev()+ ")" );
con.commit ( ) ;
}
catch ( Exception ex ) {
ex.printStackTrace ( ) ;
System.out.println ( ex.toString ( ) ) ;
result.setResultStatus ( 1 ) ;
return result ;
}
finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) { stmt.close(); }
}
catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( "" ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
result.setResultStatus ( 0 ) ;
return result ;
}
public Item[] listItem ( String boardName, OptionProperty option ) {
Item[] items = null ;
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String board = "" ;
if ( boardName == null ) board = "and BBSBoardName='"+ option.getFirstBoard() + "'";
else board = "and BBSBoardName='" + boardName + "'";
String total = "Select count ( * ) as total from bbslist where dateval >= '" + option.getStartDate ( ) + "' and dateval <= '" + option.getEndDate() +"' " +board;
ResultSet number = stmt.executeQuery ( total ) ;
number.next ( ) ;
getDataNumber = number.getInt ( "total" ) ;
items = new Item[getDataNumber+1] ;
String query = "Select * from bbslist where dateval>= '" + option.getStartDate ( ) + "' and dateval<= '" + option.getEndDate ( ) +"' " + board + "  order by reference " + option.getSortMethod() + ", step";
ResultSet list = stmt.executeQuery ( query ) ;
int no = 0 ;
while ( list.next ( ) ) {
items[++no] = new Item ( list.getString ( "TeamID" ) , list.getString ( "BBSBoardName" ) , list.getInt("num"), list.getString("author"), list.getString("email"), list.getString("title"), list.getString("itemType"), list.getString("content"), list.getString("attachedFile"), list.getString("dateval"), list.getInt("viewCount"), list.getInt("reference"),list.getInt("step"), list.getInt("lev") );
}
con.commit ( ) ;
}
catch ( Exception ex ) {
System.out.println ( " ========= > listItem ( ) " ) ;
System.out.println ( ex.toString ( ) ) ;
return items ;
}
finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) {
stmt.close ( ) ;
}
}
catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( "" ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
return items ;
}
public Item[] searchItems ( String boardName, SearchProperty search, OptionProperty option ) {
Item[] items = null ;
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String title, author, content ;
String board = "and bbsboardName = '" + boardName + "'" ;
if ( search.getTitle ( ) != null ) if ( search.getAuthor() != null || search.getContent() != null) title = " and ( title like '%" + search.getTitle() +"%'";
else title = " and ( title like '%" + search.getTitle() +"%')";
else title = "" ;
if ( search.getAuthor ( ) != null ) {
if ( title == "" ) {
if ( search.getContent ( ) != null ) author = " and ( author like '%" + search.getAuthor() +"%'";
else author = " and ( author like '%" + search.getAuthor() +"%')";
}
else {
if ( search.getContent ( ) != null ) author = " or author like '%" + search.getAuthor() +"%'";
else author = " or author like '%" + search.getAuthor() +"%')";
}
}
else author = "" ;
if ( search.getContent ( ) != null ) {
if ( title == "" ) {
if ( author == "" ) content = " and ( content like '%" + search.getContent() +"%' )";
else content = " or content like '%" + search.getContent() +"%' )";
}
else content = " or content like '%" + search.getContent() +"%' )";
}
else content = "" ;
String total = "Select count ( * ) as total from bbslist where ( davteval>= '" + option.getStartDate ( ) + "' and dateval<='" + option.getEndDate() +"' " + board + " ) " + title + author + content ;
ResultSet number = stmt.executeQuery ( total ) ;
number.next ( ) ;
getDataNumber = number.getInt ( "total" ) ;
items = new Item[getDataNumber+1] ;
String query = "Select * from bbslist where ( dateval>= '" + option.getStartDate ( ) + "' and dateval<= '" + option.getEndDate ( ) +"' " + board + " ) " + title + author + content + " order by reference " + option.getSortMethod() + ", step";
ResultSet list = stmt.executeQuery ( query ) ;
int no = 0 ;
while ( list.next ( ) ) {
items[++no] = new Item ( list.getString ( "TeamID" ) , list.getString ( "BBSBoardName" ) , list.getInt("num"), list.getString("author"), list.getString("email"), list.getString("title"), list.getString("itemType"), list.getString("content"), list.getString("attachedFile"), list.getString("dateval"), list.getInt("viewCount"), list.getInt("reference"),list.getInt("step"), list.getInt("lev") );
}
con.commit ( ) ;
}
catch ( Exception ex ) {
System.out.println ( ex.toString ( ) ) ;
return items ;
}
finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) {
stmt.close ( ) ;
}
}
catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( "" ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
return items ;
}
public Item readItem ( String id, String boardName, int number, boolean increate ) {
System.out.println ( "read item 시작합니다." ) ;
System.out.println ( " boardname = " + boardName + ", number = " + number + ", boolean = " + increate ) ;
Statement stmt = null ;
Connection con = null ;
Item tmpItem = new Item ( ) ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String readQuery = "select * from BBSList where BBSBoardName = '" + boardName + "' and num = " +number ;
ResultSet rst = stmt.executeQuery ( readQuery ) ;
rst.next ( ) ;
tmpItem.setTeamID ( rst.getString ( "TeamID" ) ) ;
tmpItem.setBBSBoardName ( rst.getString ( "BBSBoardName" ) ) ;
tmpItem.setNumber ( rst.getInt ( "num" ) ) ;
tmpItem.setAuthor ( rst.getString ( "Author" ) ) ;
tmpItem.setEmail ( rst.getString ( "Email" ) ) ;
tmpItem.setTitle ( rst.getString ( "Title" ) ) ;
tmpItem.setItemType ( rst.getString ( "Itemtype" ) ) ;
tmpItem.setContent ( rst.getString ( "Content" ) ) ;
tmpItem.setAttachedFile ( rst.getString ( "AttachedFile" ) ) ;
tmpItem.setDate ( rst.getString ( "dateval" ) ) ;
tmpItem.setViewCount ( rst.getInt ( "ViewCount" ) ) ;
tmpItem.setReference ( rst.getInt ( "Reference" ) ) ;
tmpItem.setStep ( rst.getInt ( "Step" ) ) ;
tmpItem.setLev ( rst.getInt ( "Lev" ) ) ;
if ( increate == true ) {
String increaseQuery = "update BBSList set ViewCount = ViewCount+1 where BBSBoardName = '" + boardName + "' and num = " + number ;
int result = stmt.executeUpdate ( increaseQuery ) ;
}
String check = "select * from BBSDocumentState where id = '"+ id +"' and num = " + number ;
ResultSet state = stmt.executeQuery ( check ) ;
if ( !state.next ( ) ) {
String insert = "insert BBSDocumentState values ( '" + id +"', " + number + " ) " ;
int result = stmt.executeUpdate ( insert ) ;
}
con.commit ( ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> Exception in readItem ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
tmpItem = null ;
} finally {
try {
con.setAutoCommit ( true ) ;
stmt.close ( ) ;
connectionManager.freeConnection ( "access", con ) ;
} catch ( SQLException es ) {
System.out.println ( es.toString ( ) ) ;
}
}
return tmpItem ;
}
public BBSResultStatus checkDocumentOpen ( String id, int number ) {
Statement stmt = null ;
Connection con = null ;
Item tmpItem = new Item ( ) ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String check = "select * from BBSDocumentState where id = '" + id + "' and num = "+ number ;
ResultSet state = stmt.executeQuery ( check ) ;
con.commit ( ) ;
if ( state.next ( ) ) result.setResultStatus( 0 );
else result.setResultStatus( 1 );
} catch ( Exception e ) {
System.out.println ( "-----------> Exception in checkDocumentOpen ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
e.printStackTrace ( ) ;
result.setResultStatus ( 1 ) ;
return result ;
} finally {
try {
con.setAutoCommit ( true ) ;
stmt.close ( ) ;
connectionManager.freeConnection ( "access", con ) ;
} catch ( SQLException es ) {
System.out.println ( es.toString ( ) ) ;
}
}
return result ;
}
public BBSResultStatus modifyItem ( String boardName, Item newItem, int number ) {
BBSResultStatus result = new BBSResultStatus ( ) ;
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String attachedFile = "" ;
if ( newItem.getAttachedFile ( ) != null ) attachedFile = newItem.getAttachedFile();
String query = "update BBSList set " + "Author = '" + newItem.getAuthor() + "', " + "Email = '" + newItem.getEmail() + "', " + "Title = '" + newItem.getTitle() + "', " + "ItemType = '" + newItem.getItemType() + "', " + "Content = '" + newItem.getContent() + "', " + "AttachedFile = '" + attachedFile + "', " + "Date = '" + newItem.getDate() +  "' " + "where BBSBoardName='" + newItem.getBBSBoardName() + "' and num=" + newItem.getNumber() ;
int r = stmt.executeUpdate ( query ) ;
if ( r == 1 ) result.setResultStatus( 0 );
else result.setResultStatus( 1 );
con.commit ( ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> in modifyItem ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
e.printStackTrace ( ) ;
result.setResultStatus ( ResultStatus.FAILURE ) ;
} finally {
try {
con.setAutoCommit ( true ) ;
stmt.close ( ) ;
connectionManager.freeConnection ( "access", con ) ;
} catch ( SQLException es ) {
System.out.println ( es.toString ( ) ) ;
}
}
return result ;
}
public BBSResultStatus deleteItem ( String boardName, int number ) {
BBSResultStatus result = new BBSResultStatus ( ) ;
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String query = "delete from BBSlist where BBSBoardName = '" + boardName + "' and num = " + Integer.toString ( number ) ;
int re = stmt.executeUpdate ( query ) ;
if ( re == 1 ) result.setResultStatus( 0 );
else result.setResultStatus( 1 );
con.commit ( ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> in deleteItem ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
e.printStackTrace ( ) ;
result.setResultStatus ( ResultStatus.FAILURE ) ;
} finally {
try {
con.setAutoCommit ( true ) ;
stmt.close ( ) ;
connectionManager.freeConnection ( "access", con ) ;
} catch ( SQLException es ) {
System.out.println ( es.toString ( ) ) ;
}
}
return result ;
}
public BBSResultStatus setPersonalOption ( String id,OptionProperty option ) {
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String idQuery = "select * from BBSOptionProperty where ID = '" + id + "'" ;
ResultSet idResult = stmt.executeQuery ( idQuery ) ;
String query = "" ;
if ( idResult.next ( ) ) {
query = " update BBSOptionProperty set FirstBoard = '" + option.getFirstBoard ( ) + "', Period= " + option.getPeriod() + ", SortMethod='" + option.getSortMethod() + "', numOfItemsInPage=" +  option.getNumOfItemsInPage() + ", title='" +  option.getTitle() + "', author='" +  option.getAuthor() + "', content='" +  option.getContent() + "'  where ID='" + id + "'";
}
else {
query = "insert BBSOptionProperty values ( '"+ id +"', '" + option.getFirstBoard ( ) +"', " + option.getPeriod()  +", '" + option.getSortMethod() +"', " + option.getNumOfItemsInPage() +", '" + option.getTitle() +"', '"  + option.getAuthor() +"', '" + option.getContent() +"' )";
}
int re = stmt.executeUpdate ( query ) ;
if ( re == 1 ) result.setResultStatus( 0 );
else result.setResultStatus( 1 );
con.commit ( ) ;
}
catch ( Exception ex ) {
ex.printStackTrace ( ) ;
System.out.println ( ex.toString ( ) ) ;
result.setResultStatus ( 1 ) ;
return result ;
}
finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) { stmt.close(); }
}
catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( "" ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
return result ;
}
public OptionProperty getPersonalOption ( String id ) {
OptionProperty option = new OptionProperty ( ) ;
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String idQuery = "select * from BBSOptionProperty where ID = '" + id + "'" ;
ResultSet idResult = stmt.executeQuery ( idQuery ) ;
if ( idResult.next ( ) ) {
option.setSortMethod ( idResult.getString ( "sortMethod" ) ) ;
option.setFirstBoard ( idResult.getString ( "firstBoard" ) ) ;
option.setPeriod ( idResult.getInt ( "period" ) ) ;
option.setNumOfItemsInPage ( idResult.getInt ( "numOfItemsInPage" ) ) ;
option.setTitle ( idResult.getString ( "title" ) ) ;
option.setAuthor ( idResult.getString ( "author" ) ) ;
option.setContent ( idResult.getString ( "content" ) ) ;
}
else {
option = null ;
}
con.commit ( ) ;
}
catch ( Exception ex ) {
ex.printStackTrace ( ) ;
System.out.println ( ex.toString ( ) ) ;
option = null ;
return option ;
}
finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) { stmt.close(); }
}
catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( "" ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
return option ;
}
public BBSResultStatus createBBS ( String Department, String Team, String BoardName ) {
BBSResultStatus result = new BBSResultStatus ( ) ;
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
ResultSet rslt = stmt.executeQuery ( "select * from bbsboardname where boardname = '"+ BoardName +"'" ) ;
if ( rslt.next ( ) ) {
result.setResultStatus ( ResultStatus.SUCCESS ) ;
System.out.println ( " exits" ) ;
}
else {
String query = "insert BBSBoardName ( Team, Department, BoardName ) values ( '" + Team + "', '" + Department+ "', '" + BoardName + "' ) " ;
int i = stmt.executeUpdate ( query ) ;
if ( i == 1 ) result.setResultStatus(ResultStatus.SUCCESS);
else result.setResultStatus(ResultStatus.FAILURE);
}
con.commit ( ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> in createBBS ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
e.printStackTrace ( ) ;
result.setResultStatus ( ResultStatus.FAILURE ) ;
} finally {
try {
con.setAutoCommit ( true ) ;
stmt.close ( ) ;
connectionManager.freeConnection ( "access", con ) ;
} catch ( SQLException es ) {
System.out.println ( es.toString ( ) ) ;
}
}
return result ;
}
public BBSResultStatus deleteBBS ( String BoardName ) {
BBSResultStatus result = new BBSResultStatus ( ) ;
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String queryBoardName = "delete from BBSBoardName where boardname = '"+ BoardName + "' " ;
System.out.println ( queryBoardName ) ;
int name = stmt.executeUpdate ( queryBoardName ) ;
if ( name == 1 ) System.out.println("성공입니다.");
else System.out.println("실패입니다.");
int list = 0 ;
String bbsList = "select count ( * ) as total from bbslist where bbsboardname = '"+BoardName +"' " ;
ResultSet rs = stmt.executeQuery ( bbsList ) ;
rs.next ( ) ;
if ( rs.getInt ( "total" ) >= 1 ) {
String queryBBSList = "delete from bbslist where bbsboardname = '" + BoardName + "' " ;
System.out.println ( queryBBSList ) ;
list = stmt.executeUpdate ( queryBBSList ) ;
if ( list == 1 ) System.out.println("성공입니다.");
else System.out.println("실패입니다.");
}
else list = 1;
if ( name == 1 && list == 1 ) {
result.setResultStatus ( ResultStatus.SUCCESS ) ;
System.out.println ( "성공입니다." ) ;
}
else {
result.setResultStatus ( ResultStatus.FAILURE ) ;
System.out.println ( "실패입니다" ) ;
}
con.commit ( ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> in deleteBBS ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
e.printStackTrace ( ) ;
result.setResultStatus ( ResultStatus.FAILURE ) ;
} finally {
try {
con.setAutoCommit ( true ) ;
stmt.close ( ) ;
connectionManager.freeConnection ( "access", con ) ;
} catch ( SQLException es ) {
System.out.println ( es.toString ( ) ) ;
}
}
return result ;
}
public BBSResultStatus renameBBS ( String oldBoardName, String newBoardName ) {
BBSResultStatus result = new BBSResultStatus ( ) ;
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String queryBoardName = "update BBSBoardName set boardname = '"+ newBoardName+ "' where boardname = '"+ oldBoardName + "' " ;
int name = stmt.executeUpdate ( queryBoardName ) ;
int list = 1 ;
String bbsList = "select count ( * ) as total from bbslist where bbsboardname = '"+oldBoardName +"' " ;
ResultSet rs = stmt.executeQuery ( bbsList ) ;
rs.next ( ) ;
if ( rs.getInt ( "total" ) >= 1 ) {
String queryBBSList = "update BBSList set bbsboardname = '" + newBoardName + "' where bbsboardname = '"+ oldBoardName + "' " ;
list = stmt.executeUpdate ( queryBBSList ) ;
}
int club = 1 ;
if ( oldBoardName.substring ( 0,3 ) .equals ( "동아리" ) ) {
String clubList = "select count ( * ) as total from club where name = '"+oldBoardName.substring ( 4 ) +"' " ;
rs = stmt.executeQuery ( clubList ) ;
rs.next ( ) ;
if ( rs.getInt ( "total" ) >= 1 ) {
String clubUpdate = "update club set name = '"+newBoardName.substring ( 4 ) +"' where name = '"+oldBoardName.substring ( 4 ) +"'" ;
club = stmt.executeUpdate ( clubUpdate ) ;
}
}
result.setResultStatus ( ResultStatus.SUCCESS ) ;
con.commit ( ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> in renameBBS ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
e.printStackTrace ( ) ;
result.setResultStatus ( ResultStatus.FAILURE ) ;
} finally {
try {
con.setAutoCommit ( true ) ;
stmt.close ( ) ;
connectionManager.freeConnection ( "access", con ) ;
} catch ( SQLException es ) {
System.out.println ( es.toString ( ) ) ;
}
}
return result ;
}
public BBSResultStatus checkBBS ( String boardName ) {
BBSResultStatus result = new BBSResultStatus ( ) ;
Statement stmt = null ;
Connection con = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String queryBoardName = "select * from bbsboardName where boardName = '" + boardName + "'" ;
ResultSet rslt = stmt.executeQuery ( queryBoardName ) ;
if ( rslt.next ( ) ) {
result.setResultStatus ( ResultStatus.SUCCESS ) ;
}
else {
result.setResultStatus ( ResultStatus.FAILURE ) ;
}
con.commit ( ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> in checkBBS ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
e.printStackTrace ( ) ;
result.setResultStatus ( ResultStatus.FAILURE ) ;
} finally {
try {
con.setAutoCommit ( true ) ;
stmt.close ( ) ;
connectionManager.freeConnection ( "access", con ) ;
} catch ( SQLException es ) {
System.out.println ( es.toString ( ) ) ;
}
}
return result ;
}
public String[][][] getBBStree ( ) {
Connection con = null ;
Statement stmt = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
ResultSet rslt = stmt.executeQuery ( "SELECT department FROM bbsboardName group by department" ) ;
int depNum = 0 ;
while ( rslt.next ( ) ) ++depNum;
rslt = stmt.executeQuery ( "SELECT team FROM bbsboardName group by Team" ) ;
int teamNum = 0 ;
while ( rslt.next ( ) ) ++teamNum;
rslt = stmt.executeQuery ( "SELECT boardname FROM bbsboardname group by boardname" ) ;
int boardNum = 0 ;
while ( rslt.next ( ) ) ++boardNum;
String[][][] bbs = new String[depNum+1][teamNum+2][boardNum+2] ;
int index = 0 ;
rslt = stmt.executeQuery ( "SELECT department FROM bbsboardname group by department" ) ;
while ( rslt.next ( ) ) {
bbs[index][0][0] = rslt.getString ( "department" ) ;
bbs[index++][1][0] = "null" ;
}
bbs[ index ][0][0] = "null" ;
rslt = stmt.executeQuery ( "SELECT department, team FROM bbsboardname group by department, team" ) ;
while ( rslt.next ( ) ) {
String dep = rslt.getString ( "department" ) ;
for ( int i = 0 ; i < depNum ; i++ ) {
if ( bbs[i][0][0].equals ( dep ) ) {
for ( int j = 1 ; j < ( teamNum+2 ) ; j++ ) {
if ( bbs[i][j][0].equals ( "null" ) ) {
bbs[i][j][0] = rslt.getString ( "team" ) ;
bbs[i][j][1] = "null" ;
bbs[i][j+1][0] = "null" ;
break ;
}
}
}
}
}
rslt = stmt.executeQuery ( "SELECT * FROM bbsboardname" ) ;
while ( rslt.next ( ) ) {
String dep = rslt.getString ( "department" ) ;
String team = rslt.getString ( "team" ) ;
for ( int i = 0 ; i < depNum ; i++ ) {
for ( int j = 1 ; j < ( teamNum+1 ) ; j++ ) {
if ( bbs[i][0][0].equals ( dep ) && bbs[i][j][0].equals ( team ) ) {
for ( int k = 1 ; k < ( boardNum+2 ) ; k++ ) {
if ( bbs[i][j][k].equals ( "null" ) ) {
bbs[i][j][k] = rslt.getString ( "boardname" ) ;
bbs[i][j][k+1] = "null" ;
break ;
}
}
break ;
}
}
}
}
rslt.close ( ) ;
con.commit ( ) ;
return bbs ;
} catch ( Exception e ) {
System.out.println ( "-----------> in BBStree ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
return null ;
} finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) { stmt.close ( ) ; }
} catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( " " ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
}
public String[][] getClubTree ( ) {
Connection con = null ;
Statement stmt = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
ResultSet rslt = stmt.executeQuery ( "SELECT name FROM club group by name" ) ;
int clubNum = 0 ;
while ( rslt.next ( ) ) ++clubNum;
rslt = stmt.executeQuery ( "SELECT id FROM club group by id" ) ;
int memeberNum = 0 ;
while ( rslt.next ( ) ) ++memeberNum;
String[][] club = new String[clubNum+1][memeberNum+2] ;
int index = 0 ;
rslt = stmt.executeQuery ( "SELECT name FROM club group by name" ) ;
while ( rslt.next ( ) ) {
club[index][0] = rslt.getString ( "name" ) ;
club[index++][1] = "null" ;
}
club[ index ][0] = "null" ;
rslt = stmt.executeQuery ( "SELECT name, id FROM club group by name, id" ) ;
while ( rslt.next ( ) ) {
String name = rslt.getString ( "name" ) ;
for ( int i = 0 ; i < clubNum ; i++ ) {
if ( club[i][0].equals ( name ) ) {
for ( int j = 1 ; j < ( memeberNum+2 ) ; j++ ) {
if ( club[i][j].equals ( "null" ) ) {
String memeberId = rslt.getString ( "id" ) ;
club[i][j] = memeberId ;
club[i][j+1] = "null" ;
break ;
}
}
}
}
}
for ( int i = 0 ; !club[i][0].equals ( "null" ) ; i ++ ) for ( int j = 1 ; !club[i][j].equals("null"); j ++ ) {
String memeberId = club[i][j] ;
rslt = stmt.executeQuery ( "select memeberName from club where id = '"+ memeberId + "'" ) ;
rslt.next ( ) ;
String memeberName = rslt.getString ( "memeberName" ) ;
String temp = memeberName + " ( " + memeberId + " ) " ;
club[i][j] = temp ;
}
rslt.close ( ) ;
con.commit ( ) ;
return club ;
} catch ( Exception e ) {
System.out.println ( "-----------> in getClubTree ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
return null ;
} finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) { stmt.close ( ) ; }
} catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( " " ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
}
public BBSResultStatus clubInsert ( String name, String memeber ) {
Connection con = null ;
Statement stmt = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
for ( int i = 0 ; i < memeber.length ( ) ; i ++ ) {
String temp = memeber.substring ( 0, memeber.indexOf ( " ;" ) ) ;
int j = temp.indexOf ( " ( " ) ;
String memeberName = temp.substring ( 0, j ) ;
String id = temp.substring ( j+1, temp.length ( ) -1 ) ;
ResultSet rslt = stmt.executeQuery ( "Select * from club where name = '"+name+"' and id = '"+ id +"'" ) ;
if ( !rslt.next ( ) ) {
int re = stmt.executeUpdate ( " insert club values ( '"+ id+"', '"+ name +"', '"+memeberName +"' ) " ) ;
if ( re == 0 ) {
result.setResultStatus ( 1 ) ;
return result ;
}
}
memeber = memeber.substring ( memeber.indexOf ( " ;" ) +1, memeber.length ( ) ) ;
}
con.commit ( ) ;
result.setResultStatus ( 0 ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> in clubInsert ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
result.setResultStatus ( 1 ) ;
return result ;
} finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) { stmt.close ( ) ; }
} catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( " " ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
return result ;
}
public BBSResultStatus clubDelete ( String name, String memeber ) {
Connection con = null ;
Statement stmt = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
ResultSet rslt = stmt.executeQuery ( "select count ( * ) as total from club where name = '" + name + "'" ) ;
rslt.next ( ) ;
if ( rslt.getInt ( "total" ) >= 1 ) {
if ( memeber.equals ( "all" ) ) {
int re = stmt.executeUpdate ( "Delete from club where name = '" + name + "'" ) ;
if ( re == 0 ) {
result.setResultStatus ( 1 ) ;
return result ;
}
}
else {
for ( int i = 0 ; i < memeber.length ( ) ; i++ ) {
System.out.println ( memeber ) ;
String temp = memeber.substring ( 0, memeber.indexOf ( " ;" ) ) ;
int j = temp.indexOf ( " ( " ) ;
String memeberName = temp.substring ( 0, j ) ;
String id = temp.substring ( j+1, temp.length ( ) -1 ) ;
rslt = stmt.executeQuery ( "Select * from club where name = '"+name+"' and id = '"+ id +"'" ) ;
if ( rslt.next ( ) ) {
int re = stmt.executeUpdate ( " delete from club where name = '"+name+"' and id = '"+ id +"'" ) ;
if ( re == 0 ) {
result.setResultStatus ( 1 ) ;
return result ;
}
}
memeber = memeber.substring ( memeber.indexOf ( " ;" ) +1, memeber.length ( ) ) ;
}
}
}
con.commit ( ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> in clubDelete ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
result.setResultStatus ( 1 ) ;
return result ;
} finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) { stmt.close ( ) ; }
} catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( " " ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
result.setResultStatus ( 0 ) ;
return result ;
}
public String[] getClub ( String id ) {
Connection con = null ;
Statement stmt = null ;
String[] club = null ;
try {
con = connectionManager.getConnection ( "access" ) ;
con.setAutoCommit ( false ) ;
stmt = con.createStatement ( ) ;
String query = "select count ( * ) as total from club where id = '" + id + "'" ;
ResultSet rslt = stmt.executeQuery ( query ) ;
rslt.next ( ) ;
int total = rslt.getInt ( "total" ) ;
if ( total >= 1 ) {
club = new String[ total ] ;
String query2 = "select name from club where id = '" + id + "'" ;
rslt = stmt.executeQuery ( query2 ) ;
int index = 0 ;
while ( rslt.next ( ) ) {
club[index++] = rslt.getString ( "name" ) ;
}
}
con.commit ( ) ;
} catch ( Exception e ) {
System.out.println ( "-----------> in getClub ( ) " ) ;
System.out.println ( e.toString ( ) ) ;
return club ;
} finally {
try {
con.setAutoCommit ( true ) ;
if ( stmt != null ) { stmt.close ( ) ; }
} catch ( SQLException sqle ) {
while ( sqle != null ) {
System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
System.out.println ( "Message : " + sqle.getMessage ( ) ) ;
System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
sqle = sqle.getNextException ( ) ;
System.out.println ( " " ) ;
} ;
connectionManager.freeConnection ( "access", con ) ;
}
connectionManager.freeConnection ( "access", con ) ;
}
return club ;
}
