userdbaccess.java

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

/**
 * 数据库类：负数版块的增、删、改、查
 */
package bbs.db;

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

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

public class UserDBAccess
    extends Databasec {

  public ArrayList select(String tabName) throws Exception {
    return null;
  }

  private ResultSet res = null;
  private String sql = null;
  private int currentPage = 1; //当前页
  private int pageCount = 1; //总页数
  private int rows = 5; //每页显示的行数

  /**
   * 根据ID号查询用户信息
   * @param id String
   * @return BbsUsers
   * @throws Exception
   */
  public BbsUsers selectByID(String id) throws Exception {
    BbsUsers obj = new BbsUsers();
    try {
      this.open();
      sql = "select UName,Uemail,Usex,(select PepedomName from Pepedom where PepedomID=UPepedomID) as 'duty' from bbsUsers where UID=?";
      pst = con.prepareStatement(sql);
      pst.setString(1, id);
      res = pst.executeQuery();
      if (res.next()) {
        obj.setUname(res.getString("UName"));
        obj.setUemail(res.getString("Uemail"));
        obj.setUsex(res.getString("Usex"));
        obj.setUduty(res.getString("duty"));
      }
    }
    finally {
      this.close();
    }
    return obj;
  }

  /**
   * 根据用户名查询用户信息
   * @param name String
   * @return BbsUsers
   * @throws Exception
   */
  public BbsUsers selectByName(String name) throws Exception {
    BbsUsers obj = new BbsUsers();
    try {
      this.open();
      sql = "select UName,Uemail,Usex,(select PepedomName from Pepedom where PepedomID=UPepedomID) as 'duty' from bbsUsers where UName=?";
      pst = con.prepareStatement(sql);
      pst.setString(1, name);
      res = pst.executeQuery();
      if (res.next()) {
        obj.setUname(res.getString("UName"));
        obj.setUemail(res.getString("Uemail"));
        obj.setUsex(res.getString("Usex"));
        obj.setUduty(res.getString("duty"));
      }
    }
    finally {
      this.close();
    }
    return obj;
  }

  /**
   * 根据用户邮箱查询用户信息
   * @param email String
   * @return BbsUsers
   * @throws Exception
   */
  public BbsUsers selectByEmail(String email) throws Exception {
    BbsUsers obj = new BbsUsers();
    try {
      this.open();
      sql = "select UName,Uemail,Usex,(select PepedomName from Pepedom where PepedomID=UPepedomID) as 'duty' from bbsUsers where Uemail=?";
      pst = con.prepareStatement(sql);
      pst.setString(1, email);
      res = pst.executeQuery();
      if (res.next()) {
        obj.setUname(res.getString("UName"));
        obj.setUemail(res.getString("Uemail"));
        obj.setUsex(res.getString("Usex"));
        obj.setUduty(res.getString("duty"));
      }
    }
    finally {
      this.close();
    }
    return obj;
  }

  /**
   * 负表查询数据库现有的所有版块
   * @return ArrayList
   * @throws Exception
   */
  public ArrayList select(int page) throws Exception {
    ArrayList list = new ArrayList();
    try {
      this.open();
      sql = "select count(*) from view_user";
      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_user where UID not in(select top " + pp +
          " UID from view_user order by UID) order by UID";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      while (res.next()) {
        BbsUsers obj = new BbsUsers();
        obj.setUid(res.getInt(1));
        obj.setUname(res.getString(2));
        obj.setUemail(res.getString(3));
        obj.setUsex(res.getString(4));
        obj.setUduty(res.getString(5));
        list.add(obj);
      }
    }
    finally {
      this.close();
    }
    return list;
  }

  /**
   * 删除用户方法
   * @param id String
   * @return int
   * @throws Exception
   */
  public int delete(String id) throws Exception {
    try {
      this.open();
      String sql = "exec proc_delete_user " + id;
      pst = con.prepareCall(sql);
      pst.executeUpdate();
      return 1;
    }
    finally {
      this.close();
    }
  }

  /**
   * 查询所有会员的详细信息
   * @return BbsUsers
   * @throws Exception
   */
  public ArrayList selectUserAll(int page) throws Exception {
    ArrayList list = new ArrayList();
    try {
      this.open();
      String sql = "select count(*) from view_userinfo";
      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_userinfo where UID not in(select top "+pp+" UID from view_userinfo order by UID) order by UID";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      while (res.next()) {
        BbsUsers user = new BbsUsers();
        user.setUid(res.getInt("UID"));
        user.setUname(res.getString("UName"));
        user.setUpassword(res.getString("Upassword"));
        user.setUemail(res.getString("Uemail"));
        user.setUsex(res.getString("Usex"));
        user.setUip(res.getString("UIP"));
        user.setUduty(res.getString("tudy"));
        user.setUtopicCount(res.getInt("UTopicCount"));
        user.setUreplayCount(res.getInt("UReplayCount"));
        user.setUremark(res.getString("Uremark"));
        String s = res.getString("UregDate");
        user.setUregdate(s.substring(0, s.indexOf('.')));
        user.setUhonor(res.getString("honor"));
        user.setUstate(res.getInt("Ustate"));
        user.setUpoint(res.getInt("Upoint"));
        user.setUlogintime(res.getString("UloginTime"));
        user.setUexittime(res.getString("UExitTime"));
        list.add(user);
      }
    }
    finally {
      this.close();
    }
    return list;
  }
  /**
   * 根据用户名查询是否有址用户
   * @param name String
   * @return boolean
   * @throws Exception
   */
  public boolean checkUser(String name) throws Exception{
    try{
      this.open();
      sql = "select UID from bbsUsers where UName='"+name+"'";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      if(res.next())
      {
        return true;
      }
    }
    finally{
      this.close();
    }
    return false;
  }
  /**
   * 更改用户权限
   * @param name String
   * @return int
   * @throws Exception
   */
  public int abolish(String name) throws Exception{
    try{
      this.open();
      sql = "update bbsUsers set UPepedomID=4 where UName='"+name+"'";
      pst = con.prepareStatement(sql);
      pst.executeUpdate();
      sql = "update bbsSection set SmasterID=null where SmasterID=(select UID from bbsUsers where UName='"+name+"')";
      pst = con.prepareStatement(sql);
      return pst.executeUpdate();
    }
    finally{
      this.close();
    }
  }


  public int getRows() {
    return rows;
  }

  public int getPageCount() {
    return pageCount;
  }

  public int getCurrentPage() {
    return currentPage;
  }

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

}