topicdb.java

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

/**
 * 数据库类：负责主题表的增、删、改、查
 */
package bbs.db;

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

import bbs.bean.*;
import com.db.*;

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

  private ResultSet res;
  private String sql = null;
  private int currentPage = 1;
  private int pageCount = 1;
  private int rows = 10;

  /**
   * 查询主题表信息
   * @return ArrayList
   * @throws Exception
   */
  public ArrayList select(int page, String sid) throws Exception {
    ArrayList list = new ArrayList();
    try {
      this.open();
      sql = "select count(*) from view_topic";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      int counts = res.getInt(1);
      this.pageCount = counts % rows == 0 ? counts / rows : counts / rows + 1;
      if (page > pageCount) {
        page = pageCount;
      }
      if (page < 1) {
        page = 1;
      }
      int pp = (page - 1) * rows;
      sql = "select top " + rows +
          " * from view_topic where TID not in(select top " + pp +
          " TID from view_topic order by TID) and TsID=" + sid +
          " order by TID";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      while (res.next()) {
        BbsTopic obj = new BbsTopic();
        obj.setTid(res.getInt("TID"));
        obj.setTsid(res.getInt("TsID"));
        obj.setKname(res.getString("kind"));
        obj.setUname(res.getString("user"));
        obj.setTreplycount(res.getInt("TreplyCount"));
        obj.setTtopic(res.getString("Ttopic"));
        String s = res.getString("Ttime");
        obj.setTtime(s.substring(0, s.indexOf('.')));
        obj.setTclickcount(res.getInt("TclickCount"));
        obj.setRname(res.getString("rname"));
        list.add(obj);
      }
    }
    finally {
      this.close();
    }
    return list;
  }

  /**
   * 查询所有贴子
   * @return int
   * @throws Exception
   */
  public int seleteTopic() 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();
    }
  }

  /**
   * 根据贴子编号查询贴子详细信息
   * @param tid String
   * @return BbsTopic
   * @throws Exception
   */
  public BbsTopic selectTopic(String tid) throws Exception {
    BbsTopic obj = new BbsTopic();
    try {
      this.open();
      String sql = "select * from bbsTopic where TID=" + tid;
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      if (res.next()) {
        obj.setTid(res.getInt("TID"));
        obj.setTsid(res.getInt("TsID"));
        obj.setTreplycount(res.getInt("TreplyCount"));
        obj.setTtopic(res.getString("Ttopic"));
        obj.setTcontents(res.getString("Tcontents"));
        obj.setTtime(res.getString("Ttime"));
        obj.setTclickcount(res.getInt("TclickCount"));
        obj.setTstate(res.getInt("Tstate"));
      }
    }
    finally {
      this.close();
    }
    return obj;
  }

  /**
   * 添加贴子方法
   * @param obj BbsTopic
   * @return int
   * @throws Exception
   */
  public int insert(BbsTopic obj) throws Exception {
    try {
      this.open();
      sql = "select UID from bbsUsers where UName='" + obj.getUname() + "'";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      int uid = res.getInt(1);
      sql =
          "insert into bbsTopic (TsID,TuID,Ttopic,Tcontents,Ttime,TIP) values(" +
          obj.getTsid() + "," + uid + ",'" + obj.getTtopic() + "','" +
          obj.getTcontents() + "','" + obj.getTtime() + "','" + obj.getTip() +
          "')";
      pst = con.prepareStatement(sql);
      return pst.executeUpdate();
    }
    finally {
      this.close();
    }
  }

  /**
   * 设置贴子的点击率
   * @param tid int
   * @return int
   * @throws Exception
   */
  public int setClickCount(int tid) throws Exception {
    try {
      this.open();
      String sql = "update bbsTopic set TclickCount=TclickCount+1 where TID=?";
      pst = con.prepareStatement(sql);
      pst.setInt(1, tid);
      return pst.executeUpdate();
    }
    finally {
      this.close();
    }
  }

  /**
   * 查找贴子方法
   * @param page int
   * @param tname String
   * @return ArrayList
   * @throws Exception
   */
  public ArrayList findtopic(int page, String tname) throws Exception {
    ArrayList list = new ArrayList();
    try {
      this.open();
      sql = "select count(*) from view_topic";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      int counts = res.getInt(1);
      this.pageCount = counts % rows == 0 ? counts / rows : counts / rows + 1;
      if (page > pageCount) {
        page = pageCount;
      }
      if (page < 1) {
        page = 1;
      }
      int pp = (page - 1) * rows;
      sql = "select top " + rows +
          " * from view_topic where TID not in(select top " + pp +
          " TID from view_topic order by TID) and Ttopic like'%" + tname +
          "%' order by TID";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      while (res.next()) {
        BbsTopic obj = new BbsTopic();
        obj.setTid(res.getInt("TID"));
        obj.setTsid(res.getInt("TsID"));
        obj.setKname(res.getString("kind"));
        obj.setUname(res.getString("user"));
        obj.setTreplycount(res.getInt("TreplyCount"));
        obj.setTtopic(res.getString("Ttopic"));
        String s = res.getString("Ttime");
        obj.setTtime(s.substring(0, s.indexOf('.')));
        obj.setTclickcount(res.getInt("TclickCount"));
        obj.setRname(res.getString("rname"));
        list.add(obj);
      }
    }
    finally {
      this.close();
    }
    return list;
  }

  /**
   * 查询所有贴子的信息
   * @param page int
   * @return ArrayList
   * @throws Exception
   */
  public ArrayList selectAll(int page) throws Exception {
    ArrayList list = new ArrayList();
    try {
      this.open();
      sql = "select count(*) from view_topic";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      int counts = res.getInt(1);
      this.pageCount = counts % rows == 0 ? counts / rows : counts / rows + 1;
      if (page > pageCount) {
        page = pageCount;
      }
      if (page < 1) {
        page = 1;
      }
      int pp = (page - 1) * rows;
      sql = "select top " + rows +
          " * from view_topic where TID not in(select top " + pp +
          " TID from view_topic order by TID) order by TID";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      while (res.next()) {
        BbsTopic obj = new BbsTopic();
        obj.setTid(res.getInt("TID"));
        obj.setTsid(res.getInt("TsID"));
        obj.setKname(res.getString("kind"));
        obj.setUname(res.getString("user"));
        obj.setTreplycount(res.getInt("TreplyCount"));
        obj.setTtopic(res.getString("Ttopic"));
        String s = res.getString("Ttime");
        obj.setTtime(s.substring(0, s.indexOf('.')));
        obj.setTclickcount(res.getInt("TclickCount"));
        obj.setRname(res.getString("rname"));
        list.add(obj);
      }
    }
    finally {
      this.close();
    }
    return list;
  }

  /**
   * 删除方法
   * 根据贴子编号进行删除
   * @param tid String
   * @return int
   * @throws Exception
   */
  public int delete(String tid) throws Exception {
    try {
      this.open();
      sql = "delete from bbsReply where RtID="+tid;
      pst = con.prepareStatement(sql);
      pst.executeUpdate();
      sql = "delete from bbsTopic where TID=" + tid + "";
      pst = con.prepareStatement(sql);
      return pst.executeUpdate();
    }
    finally {
      this.close();
    }
  }

  /**
   * 根据用户查询所发贴子
   * @param page int
   * @param name String
   * @return ArrayList
   * @throws Exception
   */
  public ArrayList selectAllUserTopic(int page, String name) throws Exception {
    ArrayList list = new ArrayList();
    try {
      this.open();
      sql = "select count(*) from view_topic";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      res.next();
      int counts = res.getInt(1);
      this.pageCount = counts % rows == 0 ? counts / rows : counts / rows + 1;
      if (page > pageCount) {
        page = pageCount;
      }
      if (page < 1) {
        page = 1;
      }
      int pp = (page - 1) * rows;
      sql = "select top " + rows +
          " * from view_topic where TID not in(select top " + pp +
          " TID from view_topic order by TID) and [user]='" + name +
          "' order by TID";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      while (res.next()) {
        BbsTopic obj = new BbsTopic();
        obj.setTid(res.getInt("TID"));
        obj.setTsid(res.getInt("TsID"));
        obj.setKname(res.getString("kind"));
        obj.setUname(res.getString("user"));
        obj.setTreplycount(res.getInt("TreplyCount"));
        obj.setTtopic(res.getString("Ttopic"));
        String s = res.getString("Ttime");
        obj.setTtime(s.substring(0, s.indexOf('.')));
        obj.setTclickcount(res.getInt("TclickCount"));
        obj.setRname(res.getString("rname"));
        list.add(obj);
      }
    }
    finally {
      this.close();
    }
    return list;
  }

  /**
   * 修改方法
   * 根据贴子编号进行修改
   * @param obj BbsTopic
   * @return int
   * @throws Exception
   */
  public int update(BbsTopic obj) throws Exception {
    try {
      this.open();
      sql = "update bbsTopic set Ttopic='" + obj.getTtopic() + "',Tcontents='" +
          obj.getTcontents() + "' where TID=" + obj.getTid();
      System.out.println("One ===" + sql);
      System.out.println("One ===" + obj.getTtopic());
      System.out.println("One ===" + obj.getTcontents());
      pst = con.prepareStatement(sql);
      return pst.executeUpdate();
    }
    finally {
      this.close();
    }
  }
  /**
   * 根据用户名修改用户积分
   * @param name String
   * @return int
   * @throws Exception
   */
  public int modifypoint(String name)throws Exception{
    try{
      this.open();
      sql = "update bbsUsers set Upoint=Upoint-10 where UName='"+name+"'";
      pst = con.prepareStatement(sql);
      return pst.executeUpdate();
    }
    finally{
      this.close();
    }
  }

  public int getRows() {
    return rows;
  }

  public int getCurrentPage() {
    return currentPage;
  }

  public int getPageCount() {
    return pageCount;
  }

  public void setRows(int rows) {
    this.rows = rows;
  }
}