dbaccess.java

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

package com.db;

import java.util.*;
import java.sql.DriverManager;
import java.sql.*;
import com.db.Databasec;
import bbs.bean.*;
import bbs.bean.BbsKinds;
import bbs.bean.BbsSection;

public class DBAccess
    extends Databasec {

  private ArrayList columnName = new ArrayList();
  private ResultSet res;
  //查询表
  public ArrayList select(String tabName) throws Exception {
    ArrayList data = new ArrayList();
    try {
      this.open();
      String sql = "select * from " + tabName;
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      ResultSetMetaData rmd = res.getMetaData();
      int count = rmd.getColumnCount();
      for (int i = 1; i < count; i++) {
        this.columnName.add(rmd.getColumnName(i));
      }
      while (res.next()) {
        ArrayList row = new ArrayList();
        for (int i = 1; i < count; i++) {
          row.add(res.getString(i));
        }
        data.add(row);
      }
    }
    finally {
      this.close();
    }
    return data;
  }

  //向用户表中插入数据
  public int insert(BbsUsers obj) throws Exception {
    open();
    try {
      String sql = "insert into bbsUsers (UName,Upassword,Uemail,Usex,UIP,Uremark,UregDate) values(?,?,?,?,?,?,?)";
      pst = con.prepareStatement(sql);
      pst.setString(1, obj.getUname());
      pst.setString(2, obj.getUpassword());
      pst.setString(3, obj.getUemail());
      pst.setString(4, obj.getUsex());
      pst.setString(5, obj.getUip());
      pst.setString(6, obj.getUremark());
      pst.setString(7, obj.getUregdate());
      System.out.println("插入的SQL========" + sql);
      return pst.executeUpdate();
    }
    finally {
      this.close();
    }
  }

  //检查用户名和密码
  public boolean check(String name, String pwd) throws Exception {
    try {
      this.open();
      String sql = "select * from bbsUsers where UName=? and Upassword=? and UID=(select PepedomID from Pepedom where PepedomName='坛主')";
      pst = con.prepareStatement(sql);
      pst.setString(1, name);
      pst.setString(2, pwd);
      res = pst.executeQuery();
      if (res.next()) {
        return true;
      }
    }
    finally {
      this.close();
    }
    return false;
  }

  //检查用户名和密码
  public boolean checkUser(String name, String pwd) throws Exception {
    try {
      this.open();
      String sql = "select * from bbsUsers where UName=? and Upassword=?";
      pst = con.prepareStatement(sql);
      pst.setString(1, name);
      pst.setString(2, pwd);
      res = pst.executeQuery();
      if (res.next()) {
        return true;
      }
    }
    finally {
      this.close();
    }
    return false;
  }

  //更新用户名和密码
  public int updateUser(String oldUser, String name, String pwd) throws
      Exception {
    try {
      this.open();
      String sql = "update bbsUsers set UName=?,Upassword=? where UID=(select UID from bbsUsers where UName=?)";
      pst = con.prepareStatement(sql);
      pst.setString(1, name);
      pst.setString(2, pwd);
      pst.setString(3, oldUser);
      return pst.executeUpdate();
    }
    finally {
      this.close();
    }
  }

  public ArrayList getColumnName() {
    return columnName;
  }

  //查询版块信息
  public ArrayList selectKind() throws Exception {
    ArrayList list = new ArrayList();
    try {
      this.open();
      String sql = "select KID,KName from bbsKinds";
      pst = con.prepareStatement(sql);
      res = pst.executeQuery();
      while (res.next()) {
        BbsKinds obj = new BbsKinds();
        int idd = res.getInt(1);
        obj.setKid(idd);
        obj.setKname(res.getString(2));
        obj.setSubMenu(this.selectSection(idd + ""));
        list.add(obj);
      }
      res.close();
    }
    finally {
      this.close();
    }
    return list;
  }

  //查询子版块的信息
  public ArrayList selectSection(String id) throws Exception {
    ArrayList list = new ArrayList();
    try {
      this.open();
      String sql = "select * from view_section where KID=" + id;
      pst = con.prepareStatement(sql);
      ResultSet res = pst.executeQuery();
      while (res.next()) {
        BbsSection obj = new BbsSection();
        obj.setSid(res.getInt(1));
        obj.setKid(res.getInt(2));
        obj.setSname(res.getString(3));
        obj.setUname(res.getString(4));
        obj.setSprofile(res.getString(5));
        obj.setSclickcount(res.getInt(6));
        obj.setStopiccount(res.getInt(7));
        String s = res.getString(8);
        obj.setSdate(s.substring(0, s.indexOf('.')));
        list.add(obj);
      }
    }
    finally {
      this.close();
    }
    return list;
  }

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

  /**
   * 根据用户名查询出用户的详细信息
   * @param name String
   * @return BbsUsers
   * @throws Exception
   */
  public BbsUsers selectUser(String name) throws Exception {
    BbsUsers user = new BbsUsers();
    try {
      this.open();
      String sql = "select * from view_userinfo where UName=?";
      pst = con.prepareStatement(sql);
      pst.setString(1, name);
      System.out.println("name========="+name);
      res = pst.executeQuery();
      if (res.next()) {
        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"));
      }
    }
    finally {
      this.close();
    }
    return user;
  }

  //查询注册用户信息
  public ArrayList selectUser(int uid) throws Exception {
    ArrayList list = new ArrayList();
    try {
      this.open();
      String sql = "select * from view_userinfo where UID=?";
      pst = con.prepareStatement(sql);
      pst.setInt(1, uid);
      res = pst.executeQuery();
      if (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;
  }
  //登录成功 改变状态
  public int setState(String name) throws Exception{
    try{
      this.open();
      String sql = "update bbsUsers set Ustate=1 where UName=?";
      pst = con.prepareStatement(sql);
      pst.setString(1,name);
      return pst.executeUpdate();
    }
    finally{
      this.close();
    }
  }
    //退出登录 改变状态
  public int setStateTo(String name) throws Exception{
    try{
      this.open();
      String sql = "update bbsUsers set Ustate=0 where UName=?";
      pst = con.prepareStatement(sql);
      pst.setString(1,name);
      return pst.executeUpdate();
    }
    finally
    {
      this.close();
    }
  }
}