cmtmgmt.java~1~

论坛元程序

package yy;

import yy.*;
import java.io.*;
import java.util.*;
import java.sql.*;

public class CmtMgmt {
  private Connection con;

  //构造方法
  public CmtMgmt() {
    con = DataBaseConnection.getConnection();
  }

  //返回所有的社区
  public Collection getAllCommunity() throws Exception {
    Statement stmt = con.createStatement();
    ResultSet rst = stmt.executeQuery(
        "select * from Community order by state,create_time desc");
    Collection ret = new ArrayList();
    while (rst.next()) {
      Community temp = new Community();
      temp.setCmtId(rst.getInt("community_id"));
      temp.setCmtName(rst.getString("community_name"));
      java.sql.Date date = rst.getDate("create_time");
      temp.setCreateTime(date);
      temp.setHostName(rst.getString("host_name"));
      temp.setHostPWD(rst.getString("host_password"));
      temp.setLastPublisher(rst.getString("last_publisher"));
      date = rst.getDate("last_publishtime");
      temp.setLastPublishTime(date);
      temp.setNumOfMessage(rst.getInt("numofmessage"));
      temp.setState(rst.getInt("state"));
      ret.add(temp);
    }
    con.close();
    return ret;
  }

  //返回特定的一个社区
  public Community getOneCommunity(int cmtId) throws Exception {
    Statement stmt = con.createStatement();
    ResultSet rst = stmt.executeQuery(
        "select * from Community where community_id=" + cmtId);
    Community temp = new Community();
    if (rst.next()) {
      temp.setCmtId(rst.getInt("community_id"));
      temp.setCmtName(rst.getString("community_name"));
      java.sql.Date date = rst.getDate("create_time");
      temp.setCreateTime(date);
      temp.setHostName(rst.getString("host_name"));
      temp.setHostPWD(rst.getString("host_password"));
      temp.setLastPublisher(rst.getString("last_publisher"));
      date = rst.getDate("last_publishtime");
      temp.setLastPublishTime(date);
      temp.setNumOfMessage(rst.getInt("numofmessage"));
      temp.setState(rst.getInt("state"));
    }
    con.close();
    return temp;
  }

  //增加一个新的社区
  public void addNewCommunity(Community community) throws Exception {
    PreparedStatement pstmt = con.prepareStatement("insert into Porducts(community_name,create_time,host_name,host_password,numofmessage,last_publisher,last_publishtime,state) values(?,?,?,?,?,?,?,?)");
    pstmt.setString(1, community.getCmtName());
    pstmt.setDate(2, community.getCreateTime());
    pstmt.setString(3, community.getHostName());
    pstmt.setString(4, community.getHostPWD());
    pstmt.setInt(5, community.getNumOfMessage());
    pstmt.setString(6, community.getLastPublisher());
    pstmt.setDate(7, community.getLastPublishTime());
    pstmt.setInt(8, community.getState());
    pstmt.execute();
  }

  //改变社区的状态
  public void setCommunityState(int cmtId) throws Exception {
    Statement stmt = con.createStatement();
    ResultSet rst = stmt.executeQuery(
        "select * from Community where community_id=" + cmtId);

    if (rst.next()) {
      int state = rst.getInt("state");
      if (state == 1) {
        stmt.execute("update Community set state=0 where community_id=" + cmtId);
      }
      else {
        stmt.execute("update Community set state=1 where community_id=" + cmtId);
      }
    }
  }

  //撤掉一个版主
  public void retireHost(int cmtId) throws Exception {
    Statement stmt = con.createStatement();
    //找到相应斑竹的ID
    ResultSet rst = stmt.executeQuery(
        "select User.* from User,Community where Community.community_id=" +
        cmtId +
        " and (User.user_rank=2 or User_rank=3) and Community.host_name=User.user_name");
    if (rst.next()) {
      int hostId = rst.getInt("user_id");
      int hostRank = rst.getInt("user_rank");
      //撤掉原来的斑竹
      //注意字符串空值是null
      stmt.execute(
          "update Community set host_name=null,host_password=null where community_id=" +
          cmtId);
      //若此斑竹还是其他社区的斑竹，则不更改它的属性，否则将它变为普通用户,但如果是管理员，则保持不变
      if (hostRank == 2) {
        ResultSet temp = stmt.executeQuery(
            "select Community.* from Community,User where User.user_id=" +
            hostId +
            " and Community.host_name=User.user_name");
        if (! (temp.next())) {
          stmt.execute("update User set user_rank=1 where user_id=" + hostId);
        }
      }
    }
  }

  //委任相应社区的斑竹
  //判断相应社区斑竹是否存在以及对新委任斑竹的信息确认都在网页中判断
  public void deputeHost(int cmtId, int hostId) throws Exception {
    Statement stmt = con.createStatement();
    //将要委任斑竹的信息提取出来
    ResultSet rstHost = stmt.executeQuery("select * from User where user_id=" +
                                          hostId);
    if (rstHost.next()) {
      String hostName = rstHost.getString("user_name");
      String hostPWD = rstHost.getString("user_password");
      int userRank = rstHost.getInt("user_rank");

      //这里字符串要加引号
      stmt.execute("update Community set host_name='" + hostName +
                   "',host_password='" + hostPWD + "' where community_id=" +
                   cmtId);

      //若当前用户的状态是申请斑竹等级，则指定为斑竹等级
      if (userRank == 4) {
        stmt.execute("update User set user_rank=2 where user_id=" + hostId);
      }
    }
  }

  //更换相应社区的斑竹
  public void changeHost(int cmtId, int hostId) throws Exception {
    retireHost(cmtId);
    deputeHost(cmtId, hostId);
  }

}