statdb.java

功能描述 1） 后台：管理员负责对整个论坛进行管理

package bbs.db;

import java.sql.*;
import com.db.Databasec;
import java.util.ArrayList;
import bbs.bean.Pepedom;

public class StatDB
    extends Databasec {
  public ArrayList select(String tabName) throws Exception {
    return null;
  }

  private ResultSet res;

  /**
   * 查询所有会员
   * @return int
   * @throws Exception
   */
  public int selectUsers() throws Exception {
    try {
      this.open();
      String sql = "select count(*) from bbsUsers";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      return res.getInt(1);
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询所有发贴会员
   * @return int
   * @throws Exception
   */
  public int selectTopicUsers() throws Exception {
    try {
      this.open();
      String sql = "select count(*) from bbsUsers where UTopicCount>0";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      return res.getInt(1);
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询所有未发贴的会员
   * @return int
   * @throws Exception
   */
  public int selectNoTopicUsers() throws Exception {
    try {
      this.open();
      String sql = "select count(*) from bbsUsers where UTopicCount=0";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      return res.getInt(1);
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询最新注册的会员
   * @return int
   * @throws Exception
   */
  public String selectNewUsers() throws Exception {
    try {
      this.open();
      String sql = "select top 1 Uname from bbsUsers order by UID DESC";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      return res.getString(1);
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询所有贴子总数
   * @return int
   * @throws Exception
   */
  public int selectAllTopic() throws Exception {
    try {
      this.open();
      String sql = "select count(*) from bbsTopic";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      return res.getInt(1);
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询今日发贴数量
   * @return int
   * @throws Exception
   */
  public int selectDayTopic() throws Exception {
    try {
      this.open();
      String sql = "select count(*) from bbsTopic where DATEPART(second,Ttime)>0 and DATEPART(day,Ttime)=(select DATEPART(day,getdate())) and DATEPART(month,Ttime)=(select DATEPART(month,getdate())) and DATEPART(year,Ttime)=(select DATEPART(year,getdate()))";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      return res.getInt(1);
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询所有版块数量
   * @return int
   * @throws Exception
   */
  public int selectAllSections() throws Exception {
    try {
      this.open();
      String sql = "select count(*) from bbsSection";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      return res.getInt(1);
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询人气最旺的版块
   * @return String
   * @throws Exception
   */
  public String selectPopSections() throws Exception {
    try {
      this.open();
      String sql =
          "select top 1 sname from bbsSection order by StopicCount DESC";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      return res.getString(1);
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询所有管理员
   * @return int
   * @throws Exception
   */
  public int selectAllAdmin() throws Exception {
    try {
      this.open();
      String sql = "select count(*) from bbsUsers where UPepedomID < 4";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      return res.getInt(1);
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询论坛之星
   * @return int
   * @throws Exception
   */
  public String selectStarSection() throws Exception {
    try {
      this.open();
      String sql = "select top 2 UName from bbsUsers order by Upoint DESC";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      res.next();
      return res.getString(1);
    }
    finally {
      this.close();
    }
  }
  /**
   * 查询论坛高级信息表
   * @return Pepedom
   * @throws Exception
   */
  public Pepedom getPepedom() throws Exception{
    Pepedom obj = new Pepedom();
    try{
      this.open();
      String sql = "select * from bbsDetails";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      if(res.next()){
        obj.setIssend(res.getInt(1));
        obj.setIsreplay(res.getInt(2));
        obj.setIsAuditing(res.getInt(3));
        obj.setIsdegrade(res.getInt(4));
      }

    }
    finally{
      this.close();
    }
    return obj;
  }
}