smproxy.java

计算机技术的快速发展

/*
 * 创建日期 2005-5-6
 */
package com.suninformation.schoolmate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.suninformation.user.*;
import com.suninformation.database.DBManager;
import com.suninformation.user.UnacceptableException;

/**
 * @author 刘镇
 *
 * 校友录操作代理类
 */
public class SMProxy {
  private static final String SCHOOL_COUNT =
      "SELECT count(1) FROM sm_school WHERE provinceid=? AND cityid=? AND lvid=?";

  private static final String DELETE_SCHOOL =
      "DELETE FROM sm_school WHERE schoolid=?";

  private static final String ALL_SCHOOL =
      "SELECT schoolid FROM sm_school WHERE provinceid=? AND cityid=? AND lvid=?";

  private static final String SEARCH_SCHOOL = "SELECT schoolid FROM sm_school WHERE provinceid=? AND cityid=? AND lvid=? AND schoolname LIKE ? ORDER BY schoolname";

  private static final String SEARCH_SCHOOL_NO_NAME = "SELECT schoolid FROM sm_school WHERE provinceid=? AND cityid=? AND lvid=? ORDER BY schoolname";

  private static final String SEARCH_MY_CLASS =
      "SELECT classid FROM sm_member WHERE username=?";

  private static final String SEARCH_MY_SCHOOLMATE =
      "SELECT username FROM sm_user WHERE realname LIKE ?";

  /**
   * 获取学校
   *
   * @param schoolid
   *            学校ID
   * @return 学校类SMSchool
   * @throws SMSchoolNotFoundException
   * @throws UnacceptableException
   */
  public SMSchool getSMSchool(int schoolid) throws SMSchoolNotFoundException,
      UnacceptableException {
    return new SMSchool(schoolid);
  }

  /**
   * 获取学校总数
   *
   * @param provinceid int
   * @param cityid int
   * @param lvid int
   * @return int
   * @throws UnacceptableException
   */
  public int getSMSchoolCount(int provinceid, int cityid, int lvid) throws
      UnacceptableException {
    int count = -1;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = DBManager.getConnection();
      pstmt = conn.prepareStatement(SCHOOL_COUNT);
      pstmt.setInt(1, provinceid);
      pstmt.setInt(2, cityid);
      pstmt.setInt(3, lvid);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        count = rs.getInt(1);
      }
    }
    catch (SQLException sqle) {

    }
    finally {
      DBManager.closeObject(conn, pstmt, rs);
    }
    return count;
  }

  /**
   * 根据所在省份ID和城市ID信息，返回适合条件的所有学校信息。
   *
   * @param provinceid
   *            省份ID
   * @param cityid
   *            城市ID
   * @param lvid
   *            学校级别ID，即：1-大学(大学,大专,研究院)，2-中专(中专,技校)，3-中学(初中,高中)，4-小学，5-其它
   * @return 校友录学校类实例数组
   * @throws UnacceptableException
   */
  public SMSchool[] getSMSchools(int provinceid, int cityid, int lvid) throws
      UnacceptableException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    SMSchool[] iSMSchool = null;
    int number = getSMSchoolCount(provinceid, cityid, lvid);
    if (number >= 1) {
      iSMSchool = new SMSchool[number];
      try {
        conn = DBManager.getConnection();
        pstmt = conn.prepareStatement(ALL_SCHOOL);
        pstmt.setInt(1, provinceid);
        pstmt.setInt(2, cityid);
        pstmt.setInt(3, lvid);
        rs = pstmt.executeQuery();
        for (int i = 1; i <= number; i++) {
          iSMSchool[i] = new SMSchool(rs.getInt(1));
        }
      }
      catch (Exception e) {
        throw new UnacceptableException("数据库操作失败！", e);
      }
      finally {
        DBManager.closeObject(conn, pstmt, rs);
      }
    }
    return iSMSchool;
  }

  /**
   * 搜索学校 ,功能与getSMSchools()方法大体相同，只是多了学校名称参数，所有参数必须填写！
   *
   * @param provinceid int
   * @param cityid int
   * @param lvid int
   * @param schoolname String
   * @return 学校类数组SMSchool[]
   * @throws SMSchoolNotFoundException
   * @throws UnacceptableException
   */
  public SMSchool[] searchSMSchool(int provinceid, int cityid, int lvid, String schoolname) throws  SMSchoolNotFoundException, UnacceptableException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    SMSchool[] iSMSchool = null;
    int number = -1;
    try {
      conn = DBManager.getConnection();
      if (schoolname != "" && schoolname != null) {
        pstmt = conn.prepareStatement("SELECT count(1) FROM sm_school WHERE provinceid=? AND cityid=? AND lvid=? AND schoolname LIKE ?");
        pstmt.setInt(1, provinceid);
        pstmt.setInt(2, cityid);
        pstmt.setInt(3, lvid);
        pstmt.setString(4, "%" + schoolname + "%");
      }
      else {
        pstmt = conn.prepareStatement("SELECT count(1) FROM sm_school WHERE provinceid=? AND cityid=? AND lvid=?");
        pstmt.setInt(1, provinceid);
        pstmt.setInt(2, cityid);
        pstmt.setInt(3, lvid);
      }
      rs = pstmt.executeQuery();
      if (rs.next()) {
        number = rs.getInt(1);
      }
    }
    catch (SQLException sqle) {
    }
    if (number >= 1) {
      iSMSchool = new SMSchool[number];
      try {
        conn = DBManager.getConnection();
        if (schoolname != "" && schoolname != null) {
          pstmt = conn.prepareStatement(SEARCH_SCHOOL);
          pstmt.setInt(1, provinceid);
          pstmt.setInt(2, cityid);
          pstmt.setInt(3, lvid);
          pstmt.setString(4, "%" + schoolname + "%");
        }
        else {
          pstmt = conn.prepareStatement(SEARCH_SCHOOL_NO_NAME);
          pstmt.setInt(1, provinceid);
          pstmt.setInt(2, cityid);
          pstmt.setInt(3, lvid);
        }
        rs = pstmt.executeQuery();
        for (int i = 1; i <= number && rs.next(); i++) {
          iSMSchool[i-1] = new SMSchool(rs.getInt(1));
        }
      }
      catch (Exception e) {
        throw new UnacceptableException("数据库操作失败！", e);
      }
      finally {
        DBManager.closeObject(conn, pstmt, rs);
      }
    }
    return iSMSchool;
  }

  /**
   * 添加新学校
   *
   * @param provinceid int
   * @param cityid int
   * @param lvid int
   * @param schoolname String
   * @return 学校类SMSchool
   * @throws SMSchoolAlreadyExistsException
   * @throws UnacceptableException
   */
  public SMSchool addNewSMSchool(int provinceid, int cityid, int lvid,
                                 String schoolname) throws
      SMSchoolAlreadyExistsException,
      UnacceptableException {
    return new SMSchool(provinceid, cityid, lvid, schoolname);
  }

  /**
   * 删除学校相关信息
   *
   * @param schoolid
   *            学校ID
   * @throws SMSchoolNotFoundException
   * @throws UnacceptableException
   */
  public void deleteSMSchool(int schoolid) throws SMSchoolNotFoundException,
      UnacceptableException {
    this.getSMSchool(schoolid).deleteSMClasses();
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = DBManager.getConnection();
      pstmt = conn.prepareStatement(DELETE_SCHOOL);
      pstmt.setInt(1, schoolid);
      pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
      throw new UnacceptableException("删除学校失败!", sqle);
    }
    finally {
      DBManager.closeObject(conn, pstmt, rs);
    }
  }

  /**
   * 获取成员username所加入的班级
   *
   * @param username String
   * @return com.suninformation.schoolmate.SMClass[]
   * @throws UnacceptableException
   */
  public SMClass[] getMyClass(String username) throws UnacceptableException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    SMClass[] iSMClass = null;
    int number = -1;
    try {
      conn = DBManager.getConnection();
      pstmt = conn
          .prepareStatement("SELECT count(1) FROM sm_member WHERE username=?");
      pstmt.setString(1, username);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        number = rs.getInt(1);
      }
    }
    catch (SQLException sqle) {
    }
    if (number >= 1) {
      iSMClass = new SMClass[number];
      try {
        conn = DBManager.getConnection();
        pstmt = conn.prepareStatement(SEARCH_MY_CLASS);
        pstmt.setString(1, username);
        rs = pstmt.executeQuery();
        for (int i = 0; i < number && rs.next(); i++) {
          iSMClass[i] = new SMClass(rs.getInt(1));
        }
      }
      catch (Exception e) {
        throw new UnacceptableException("数据库操作失败！", e);
      }
      finally {
        DBManager.closeObject(conn, pstmt, rs);
      }
    }
    return iSMClass;
  }

  /**
   * 找同学
   *
   * @param realname String
   * @return UserInfo[]
   * @throws UnacceptableException
   */
  public UserInfo[] searchMySchoolmate(String realname) throws
      UnacceptableException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    UserInfo[] iUserInfo = null;
    int number = -1;
    try {
      conn = DBManager.getConnection();
      pstmt = conn.prepareStatement(
          "SELECT count(1) FROM sm_user WHERE realname LIKE %?%");
      pstmt.setString(1, realname);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        number = rs.getInt(1);
      }
    }
    catch (SQLException sqle) {
    }
    if (number >= 1) {
        iUserInfo = new UserInfo[number];
      try {
        conn = DBManager.getConnection();
        pstmt = conn.prepareStatement(SEARCH_MY_SCHOOLMATE);
        pstmt.setString(1, realname);
        rs = pstmt.executeQuery();
        for (int i = 1; i <= number; i++) {
            iUserInfo[i] = new UserInfo(rs.getString(1));
        }
      }
      catch (Exception e) {
        throw new UnacceptableException("数据库操作失败！", e);
      }
      finally {
        DBManager.closeObject(conn, pstmt, rs);
      }
    }
    return iUserInfo;
  }
}