faq_bean.java

FAQ管理全源代码

package faq_project;

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

public class FAQ_Bean {
  private Connection con;
  private ResultSet rs, r;
  private Statement stmt;
  private PreparedStatement addstmt;
  private String ref;
  private int cnt, count;
  private String[] str;

  //连接SQL
  public FAQ_Bean() throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    con = DriverManager.getConnection("jdbc:odbc:asdf", "sa", "");
    stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                               ResultSet.CONCUR_UPDATABLE);
    addstmt = con.prepareStatement(
        "INSERT INTO FAQ (question,answer,type) VALUES (?,?,?)");

    rs = stmt.executeQuery("Select * from FAQ");
  }

  //得到当前记录信息
  public List getLists() throws Exception {
    List lst = new ArrayList();
    lst.add(String.valueOf(rs.getInt(1)));
    lst.add(rs.getString(2));
    if (rs.getString(3) == null) {
      lst.add("暂无人回答");
    }
    else {
      lst.add(rs.getString(3));
    }
    lst.add(rs.getString(4));
    lst.add(rs.getString(5));
    return lst;
  }

  //刷新
  public void flushAll() throws Exception {
    rs = stmt.executeQuery("Select count(*)  AS Expr1 from FAQ");
    rs.next();
    count = rs.getInt(1);
    rs = stmt.executeQuery("Select * from FAQ order by type");
  }

  //显示类型
  public String[] getType() throws Exception {
    rs = stmt.executeQuery(
        "SELECT COUNT(*) AS Expr1 FROM (SELECT DISTINCT Type FROM FAQ) DERIVEDTBL");
    rs.next();
    cnt = rs.getInt(1);
    rs = stmt.executeQuery("SELECT DISTINCT Type FROM FAQ");
    int i = 0;
    try {
      str = new String[cnt];
      while (rs.next()) {
        str[i] = rs.getString(1);
        i++;
      }
    }
    catch (Exception e) {
      System.out.println("error");
    }
    return str;
  }

  //根据类型显示问题
  public void flushType(String Type) throws Exception {
    rs = stmt.executeQuery("Select * from FAQ where type='" + Type + "'");
  }

  public void fulshId(int id) throws Exception {
    System.out.println("delete()");
    rs = stmt.executeQuery("Select * from FAQ where id=" + id);
  }

  public boolean fulshTitle(String title) throws Exception {
    r = stmt.executeQuery(
        "SELECT count(*) as aa FROM FAQ WHERE (Question LIKE '%" + title +
        "%') OR (Type LIKE '%" + title + "%')");
    r.next();
    int QTotal = r.getInt(1);
    rs = stmt.executeQuery("SELECT * FROM FAQ WHERE (Question LIKE '%" + title +
                           "%') OR (Type LIKE '%" + title + "%')");
    if (QTotal == 0) {
      return false;
    }
    else {
      return true;
    }
  }

  public boolean fulshTitle(String title, String type) throws Exception {
    r = stmt.executeQuery(
        "SELECT count(*) as aa FROM FAQ WHERE (Question LIKE '%" + title +
        "%') and (type='" + type + "')");
    r.next();
    int QTotal = r.getInt(1);
    rs = stmt.executeQuery("SELECT * FROM FAQ WHERE (Question LIKE '%" + title +
                           "%') and (type='" + type + "')");
    if (QTotal == 0) {
      return false;
    }
    else {
      return true;
    }
  }

  //下一条记录（如果有的话）
  public boolean next() throws Exception {
    if (rs.next()) {
      return true;
    }
    else {
      return false;
    }
  }

  //以下为设置相关信息，为添加记录时有准备
  public void setQuestion(String Question) throws Exception {
    addstmt.setString(1, Question);
  }

  public void setAnswer(String Answer) throws Exception {
    addstmt.setString(2, Answer);
  }

  public void setType(String Type) throws Exception {
    addstmt.setString(3, Type);
  }

//以下为根据ID更新其它列
  public void setQuestion(String Question, String id) throws Exception {
    if (!Question.equals("")) {
      stmt.executeUpdate("update FAQ set Question='" + Question +
                         "' where id=" + id);
    }
  }

  public void setAnswer(String Answer, String id) throws Exception {
    if (!Answer.equals("")) {
      stmt.executeUpdate("update FAQ set Answer='" + Answer +
                         "' where id=" + id);
    }
  }

  public void setModified(String id) throws Exception {
    stmt.executeUpdate("update FAQ set Modified=getdate() where id=" + id);
  }

  public void setType(String Type, String id) throws Exception {
    if (!Type.equals("")) {
      stmt.executeUpdate("update FAQ set Type='" + Type +
                         "' where id=" + id);
    }
  }

  //设置添加记录信息后提交
  public void commit() throws Exception {
    addstmt.executeUpdate();
  }

  //根据ID删除记录
  public void DeleteOfID(int id) throws Exception {
    stmt.executeUpdate("Delete from FAQ where id=" + id);
  }

  //根据Type删除记录
  public void DeleteOfType(String type) throws Exception {
    stmt.executeUpdate("Delete from FAQ where type='" + type + "'");
  }

  //根据日期段删除记录
  public void DeleteOfDate(String date1, String date2) throws Exception {
    stmt.executeUpdate("Delete FROM FAQ WHERE (Modified BETWEEN '" + date1 +
                       "' AND '" + date2 + "')");
  }

  //查找全部（没有指定类型）问题
  public void setQuery(String KeyWord) throws Exception {
    rs = stmt.executeQuery(
        "Select * from FAQ where FreeText(Question,'" + KeyWord + "')");
  }

  //根据类型查找问题
  public void setQuery(String KeyWord, String Type) throws Exception {
    rs = stmt.executeQuery(
        "Select * from FAQ where FreeText(Question,'" + KeyWord +
        "') and type='" + Type + "')");
  }

  public int getCount() {
    return count;
  }

}