dbmessagecount.java

一个关于商业的网站

/**
 * $ file: DbMessageCount.java
 * $ Revision: 1.0
 * $ Modified Record:
                   $2003.4.6: Created.

 * Copyright (C) 2003 www.studyjava.com All rights reserved.
 *
*/


package com.everstar.bbs;

import java.sql.*;
import java.util.*;

import com.coolservlets.forum.*;
import com.coolservlets.forum.database.DbConnectionManager;
import com.coolservlets.util.StringUtils;

public class DbMessageCount {

    /*private static final String THREAD_CLICKED_COUNT = "SELECT sum(a.count) FROM jiveMsgCount a, jiveMessage b "
                                                     +"WHERE a.messageID=b.messageID and b.threadID=?";
    private static final String MSG_CLICKED_COUNT = "SELECT count FROM jiveMsgCount WHERE messageID=?";
    private static final String INS_MSG_COUNT = "INSERT jiveMsgCount VALUES(?,0,NULL,NULL)";
    private static final String SET_MSG_CLICKED_COUNT = "UPDATE jiveMsgCount SET count=count +1 WHERE messageID=?";
    private static final String POST_SORT = "SELECT b.name, b.userName, b.email, count(a.messageID) FROM jiveMessage a, jiveUser b "
                                          +"WHERE a.userID=b.userID GROUP BY a.userID  ORDER BY 4 DESC";
    private static final String POST_SORT_FORUM ="SELECT b.name, b.userName, b.email, count(a.messageID) FROM jiveMessage a, jiveUser b, jiveThread c "
                                                +"WHERE a.userID=b.userID and a.threadID=c.threadID and c.forumID=? GROUP BY a.userID  ORDER BY 4 DESC";
    */
    private Connection con = null;
    private	DbConnectionManager dbConManager = new DbConnectionManager();

   /** Static means just do once.
   */
    public void DbMessageCount(){

   }

   /*static {
        try {
            Class.forName("org.gjt.mm.mysql.Driver"); //JDBC driver
        }
        catch(ClassNotFoundException e) {e.printStackTrace();}
    }*/

    private String begin() {
        try {
            //con = DriverManager.getConnection("jdbc:mysql://192.168.1.8:3306/jive", "root", ""); 
            con = dbConManager.getConnection();
        }
        catch(Exception e) {
            e.printStackTrace();
            return "Cannot connect";
        }
        return "";
    }

    //added by Jack
    public int getIconID(int messageID){
     String sqlStr = "select icon from jiveMsgCount where messageID="
                     +Integer.toString(messageID);
      begin();
      try {
      	   Statement stmt = con.createStatement();
           ResultSet rs = stmt.executeQuery(sqlStr);
           if(rs.next()){
                 int temp = rs.getInt(1);
                 con.close();
                 return temp;
           }
           else{
                 con.close();
               return -1;
               }
      }
      catch (SQLException ex) {
            return -1;
      }
   }

   //Added by Jack SavingForumIcon to jiveMsgCount.
   public int saveForumIcon(int messageID,int icon){
       String sqlStr = "select messageID from jiveMsgCount where messageID ="
                       + Integer.toString(messageID);
       begin();
       try {
     	    Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sqlStr);
            if(rs.next()){
               sqlStr = "update jiveMsgCount set icon="
                        +Integer.toString(icon)+" where messageID ="+Integer.toString(messageID);
               stmt.executeUpdate(sqlStr);
                con.close();
                return 1;
            }
           else {
               sqlStr = "insert into jiveMsgCount values("
                        + Integer.toString(messageID)+",0,"+Integer.toString(icon)+",0)";
               stmt.execute(sqlStr);
                 con.close();
               return 1;
           }
     }
     catch (SQLException ex) {
          return -1;
      }
   }

    //Get the message's clicked times from jiveMsgCount. Author :Bruce.
    public int getMsgCount(int messageID) {
	String sqlStr = "SELECT count FROM jiveMsgCount WHERE messageID="+Integer.toString(messageID);
	begin();
	try {
		Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(sqlStr);
                if (rs.next()) {
			int count = rs.getInt(1);
                 con.close();
			return count;
		}
		else{
                 con.close();
			return -1;
			}
 	}
	catch (SQLException ex) {
		return -1;
       }
   }

    //Get the thread's clicked times from jiveMsgCount. Author :Bruce.
    public int getThreadCount(int threadID) {
	String sqlStr = "SELECT sum(a.count) FROM jiveMsgCount a, jiveMessage b "
                            +"WHERE a.messageID=b.messageID and b.threadID="+Integer.toString(threadID);
	begin();
	try {
		Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(sqlStr);
                if (rs.next()) {
			int count = rs.getInt(1);
                 con.close();
			return count;
		}
		else{
                 con.close();
			return -1;
			}
  	}
	catch (SQLException ex) {
		return -1;
     }
   }

      //Insert a record to jiveMsgCount. Author: Bruce.
      public int insMsgCount(int messageID) {
	String sqlStr = "SELECT messageID FROM jiveMsgCount WHERE messageID ="
				+ Integer.toString(messageID);
	begin();
	try {
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlStr);
		if(rs.next()){
			sqlStr = "UPDATE jiveMsgCount SET count=0 WHERE messageID ="+Integer.toString(messageID);
			stmt.executeUpdate(sqlStr);
                 con.close();
			return 1;
		}
		else {
			sqlStr = "INSERT INTO jiveMsgCount VALUES(" + Integer.toString(messageID) + ",0,NULL,NULL)";
			stmt.executeUpdate(sqlStr);
                 con.close();
			return 1;
		}
 	}
	catch (SQLException ex) {
		return -1;
 	}
   }


    //Add one to the message's clicktimes. Author: Bruce	
    public int setMsgCount(int messageID) {
	String sqlStr = "SELECT messageID FROM jiveMsgCount WHERE messageID ="
				+ Integer.toString(messageID);
	begin();
	try {
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlStr);
		if(rs.next()){
			sqlStr = "UPDATE jiveMsgCount SET count=count +1 WHERE messageID ="+Integer.toString(messageID);
			stmt.executeUpdate(sqlStr);
                 con.close();
			return 1;
		}
		else {
			//Insert a recorder if there don't exist the message's recorder. This is important to the old jive system.
			//And it's count equal 1,icon is the first.
			sqlStr = "INSERT INTO jiveMsgCount VALUES(" + Integer.toString(messageID) + ",1,1,NULL)";
			stmt.executeUpdate(sqlStr);
                 con.close();
			return 1;
 		}
 	}
	catch (SQLException ex) {
		return -1;
  	}
   }


    //Sorting the total amount of messages a user has posted . Author: Bruce.
    public Properties getSort(int forumID) {
	String sqlStr = new String();
	if (forumID==-1)
		sqlStr = "SELECT b.name, b.userName, b.email, count(a.messageID) FROM jiveMessage a, jiveUser b "
				+"WHERE a.userID=b.userID GROUP BY b.name, b.userName, b.email  ORDER BY 4 DESC";
	else
		sqlStr = "SELECT b.name, b.userName, b.email, count(a.messageID) FROM jiveMessage a, jiveUser b, jiveThread c "
                            +"WHERE a.userID=b.userID and a.threadID=c.threadID and c.forumID="+ Integer.toString(forumID)
                            +" GROUP BY b.name, b.userName, b.email  ORDER BY 4 DESC";
	Properties Props = new Properties();
 	begin();
	try {
		Statement stmt = con.createStatement();
               	ResultSet rs = stmt.executeQuery(sqlStr);
 		int i=0;
		while(rs.next()) {
                     Vector vect = new Vector();
                     i++;
                     vect.add(rs.getString(1));  //"name"
                     vect.add(rs.getString(2));  //"username"
                     vect.add(rs.getString(3));  //"email"
                     vect.add(rs.getString(4));  //count(a.messageID)
                     Props.put(new Integer(i),vect);  
		}
                	con.close();
 			return Props;
		
 	}
	catch (SQLException ex) {
		return null;
 	}
   }
 

}