package ee.widespace.forum.data;
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import ee.widespace.forum.LastMessage;
import ee.widespace.forum.MainQuery;
import ee.widespace.forum.Message;
import ee.widespace.forum.Node;
import ee.widespace.util.MarkupWriter;
public class ForumData {
private Connection conn;
/**
* BannerDataImpl constructor comment.
*/
public ForumData( Connection conn ) {
this.conn = conn;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public List getAllForums() throws SQLException {
LinkedList list = new LinkedList();
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"ID\",\"Name\" FROM \"Forum\"");
try {
ResultSet rset = stmt.executeQuery();
try {
while (rset.next()) {
MainQuery mq = new MainQuery();
LastMessage lm = new LastMessage();
lm = getLastMessageAuthor(rset.getInt(1) );
mq.numbersOfMessages = getMessageCount( rset.getInt(1) );
mq.name = rset.getString(2);
mq.dateOfLastMessage = lm.dateOfLastMessage;
mq.authorOfLastMessage = lm.authorOfLastMessage;
list.add(mq);
}
} finally {rset.close();}
} finally {stmt.close();}
return list;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public int getMessageCount( int forumID ) throws SQLException {
int msgCount;
PreparedStatement stmt = conn.prepareStatement(
"SELECT count(*) FROM \"Message\" WHERE \"ForumID\"=?"
);
try {
stmt.setInt( 1, forumID );
ResultSet rset = stmt.executeQuery();
try {
if ( rset.next() ) {
msgCount=rset.getInt(1);
System.out.println("getMsgCount: " + msgCount);
}
else msgCount=0;
} finally { rset.close();}
}finally { stmt.close();}
return msgCount;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public String getLastMessageDate( int forumID ) throws SQLException {
PreparedStatement stmt = conn.prepareStatement(
"SELECT max (\"Date\") FROM \"Message\" WHERE \"ForumID\"=? "
);
stmt.setInt(1,forumID);
try {
ResultSet rset = stmt.executeQuery();
try {
if ( rset.next() ) {
return rset.getString(1);
}
}finally { rset.close();}
}finally { stmt.close();}
return null;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public LastMessage getLastMessageAuthor( int forumID) throws SQLException {
LastMessage lm = new LastMessage();
String st = getLastMessageDate( forumID );
if ( st == null ){
lm.authorOfLastMessage = "not avaible";
lm.dateOfLastMessage = "not avaible";
} else {
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"Name\" FROM \"Message\" WHERE \"ForumID\"=? AND \"Date\"=? "
);
stmt.setInt(1,forumID);
stmt.setString(2,st);
try {
ResultSet rset = stmt.executeQuery();
try {
if ( rset.next() ) {
lm.authorOfLastMessage = rset.getString(1) ;
StringBuffer sb = new StringBuffer( st.replace('T' , ' ') );
sb.delete(19,26);
String date = new String( sb );
lm.dateOfLastMessage = date;
System.out.println("getLastMessageAuhtor: " + lm.authorOfLastMessage);
}
}finally { rset.close();}
}finally { stmt.close();}
}
return lm;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public int getID( String name ) throws SQLException {
int forumID = 0;
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"ID\" FROM \"Forum\" WHERE \"Name\"=? ");
try {
stmt.setString(1, name);
ResultSet rset = stmt.executeQuery();
try {
if (rset.next()){
forumID = rset.getInt(1);
}
} finally {rset.close();}
} finally {stmt.close();}
return forumID;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public Node getNode( String forumName ) throws SQLException {
int forumID = getForumID( forumName );
String spacer = new String(" ");
LinkedList list = new LinkedList();
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"MessageID\", \"Subject\", \"Name\", \"Date\" " +
"FROM \"Message\" " +
"WHERE \"ForumID\"=? AND \"ReplayID\"='0' "
);
try {
stmt.setInt( 1, forumID );
ResultSet rset = stmt.executeQuery();
try {
while ( rset.next() ) {
Message msg = new Message();
msg.messageID = rset.getInt(1);
msg.subject = rset.getString(2);
msg.name = rset.getString(3);
msg.date = rset.getString(4);
msg.spacer = spacer + spacer;
list.add( new Node(msg, getMessageList(forumID, msg.messageID, msg.spacer )) );
}
} finally {rset.close();}
} finally {stmt.close();}
Node node = new Node( forumName, list );
return node;
}
private List getMessageList( int forumID, int parentID ,String spacer) throws SQLException {
LinkedList list = new LinkedList();
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"MessageID\", \"Subject\", \"Name\", \"Date\" " +
"FROM \"Message\" "+
"WHERE \"ForumID\"=? AND \"ReplayID\"=?"
);
try {
stmt.setInt( 1, forumID );
stmt.setInt( 2, parentID );
ResultSet rset = stmt.executeQuery();
try {
while ( rset.next() ) {
Message msg = new Message();
msg.messageID = rset.getInt(1);
msg.subject = rset.getString(2);
msg.name = rset.getString(3);
msg.date = rset.getString(4);
msg.spacer = spacer + " ";
list.add( new Node(msg, getMessageList(forumID, msg.messageID, msg.spacer)) );
}
} finally {rset.close();}
} finally {stmt.close();}
return list;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public int getForumID( String forumName) throws SQLException {
int ID = 0 ;
System.out.println("forumName " + forumName);
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"ID\" FROM \"Forum\" WHERE \"Name\"=? "
);
try {
stmt.setString( 1, forumName );
ResultSet rset = stmt.executeQuery();
try {
if (rset.next()) {
ID = rset.getInt(1);
}
} finally {rset.close();}
} finally {stmt.close();}
System.out.println( " getForumID : " + ID);
return ID;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public Message getMessage( int messageID) throws SQLException {
Message msg = new Message();
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"Subject\",\"Name\",\"Date\",\"Message\" " +
"FROM \"Message\" WHERE \"MessageID\"=? "
);
try {
stmt.setInt( 1, messageID );
ResultSet rset = stmt.executeQuery();
try {
if (rset.next()) {
String st = rset.getString(3);
StringBuffer sb = new StringBuffer( st.replace('T', ' ') );
sb.delete(19, 26);
msg.subject = rset.getString(1);
msg.name = rset.getString(2);
msg.date = sb.toString();
msg.message = rset.getString(4);
}
} finally {rset.close();}
} finally {stmt.close();}
return msg;
}
public void addMessage( Message msg, String forumName ) throws SQLException {
System.out.println("init addTopic");
PreparedStatement stmtMsgID = conn.prepareStatement(
"SELECT max (\"MessageID\") FROM \"Message\" "
);
try {
ResultSet rset = stmtMsgID.executeQuery();
if ( !rset.next() ) throw new SQLException();
int messageIDmax = rset.getInt(1);
System.out.println("rset.getInt(1)" + rset.getInt(1));
PreparedStatement stmt = conn.prepareStatement(
"INSERT INTO \"Message\" ( \"MessageID\", \"ReplayID\", \"Subject\", \"Name\", \"Date\", \"Message\", \"Active\", \"ForumID\" ) VALUES(?, ?, ?, ?, ?, ?, ?, ?) "
); try {
stmt.setInt( 1, messageIDmax + 1);
stmt.setInt( 2, msg.replayID );
stmt.setString( 3, msg.subject );
stmt.setString( 4, msg.name );
stmt.setString( 5, msg.date.toString() );
stmt.setString( 6, msg.message );
stmt.setBoolean( 7, true );
stmt.setInt( 8, getForumID( forumName ));
stmt.executeUpdate();
} finally { stmt.close();}
} finally {stmtMsgID.close();}
}
public void addForum( String forumName ) throws SQLException {
System.out.println("init new Forum");
PreparedStatement stmtFrmID = conn.prepareStatement(
"SELECT max (\"ID\") FROM \"Forum\" "
);
try {
ResultSet rset = stmtFrmID.executeQuery();
if ( !rset.next() ) throw new SQLException();
int forumIDmax = rset.getInt(1);
System.out.println("rset.getInt(1):=" + rset.getInt(1));
PreparedStatement stmt = conn.prepareStatement(
"INSERT INTO \"Forum\" ( \"ID\", \"Name\") VALUES(?, ?) "
); try {
stmt.setInt( 1, forumIDmax + 1);
stmt.setString( 2, forumName );
stmt.executeUpdate();
} finally { stmt.close();}
} finally {stmtFrmID.close();}
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public int returnParentID( int messageID ) throws SQLException {
int parentID = 0;
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"ReplayID\" FROM \"Message\" WHERE \"MessageID\"=? "
);
try {
stmt.setInt( 1, messageID );
ResultSet rset = stmt.executeQuery();
try {
if (rset.next()) {
parentID = rset.getInt(1);
}
} finally {rset.close();}
} finally {stmt.close();}
return parentID;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public int returnForumID( int messageID ) throws SQLException {
int forumID = 0;
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"ForumID\" FROM \"Message\" WHERE \"MessageID\"=? "
);
try {
stmt.setInt( 1, messageID );
ResultSet rset = stmt.executeQuery();
try {
if (rset.next()) {
forumID = rset.getInt(1);
}
} finally {rset.close();}
} finally {stmt.close();}
return forumID;
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public void reassignChild( int newParentID, int ParentID ) throws SQLException {
PreparedStatement stmt = conn.prepareStatement(
"SELECT \"MessageID\" FROM \"Message\" WHERE \"ReplayID\"=? "
);
try {
stmt.setInt( 1, ParentID );
ResultSet rset = stmt.executeQuery();
try {
while (rset.next()) {
PreparedStatement stmt2 = conn.prepareStatement(
"UPDATE \"Message\" SET \"ReplayID\"=? WHERE \"MessageID\"=? "
);
try {
stmt2.setInt( 1, newParentID);
stmt2.setInt( 2, rset.getInt( 1 ));
stmt2.executeUpdate();
} finally {stmt2.close();}
}
} finally {rset.close();}
} finally {stmt.close();}
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public void deleteMessage( int messageID ) throws SQLException {
int parentID = returnParentID( messageID );
int forumID = returnForumID( messageID );
reassignChild( parentID , messageID);
System.out.println("deleteMessage ... prepered");
PreparedStatement stmt = conn.prepareStatement(
"DELETE FROM \"Message\" WHERE \"MessageID\"=? "
);
try {
stmt.setInt( 1, messageID );
stmt.execute();
System.out.println("deleteMessage ... ok");
} finally {stmt.close();}
}
/**
* Insert the method's description here.
* Creation date: (6/13/2001 14:58:55 PM)
* @param forum ee.widespace.forum.Forum
*/
public int deleteForum( String forumName ) throws SQLException {
int forumID = getForumID( forumName );
PreparedStatement stmt2 = conn.prepareStatement(
"SELECT \"Name\" FROM \"Message\" WHERE \"ForumID\"=? "
);
try {
stmt2.setInt( 1, forumID );
ResultSet rset = stmt2.executeQuery();
if (rset.next()){
return 1;
} else {
PreparedStatement stmt = conn.prepareStatement(
"DELETE FROM \"Forum\" WHERE \"Name\"=? "
);
try {
stmt.setString( 1, forumName );
stmt.execute();
System.out.println("deleteForum ..." + forumName + "ok");
} finally {stmt.close();}
}
rset.close();
} finally {stmt2.close();}
return 0;
}
}