smschool.java

计算机技术的快速发展

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

import java.sql.Connection;
//import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.suninformation.database.DBManager;
import com.suninformation.user.UnacceptableException;

/**
 * @author 刘镇
 *
 * 校友录学校类
 */
public class SMSchool {

  private static final String LOAD_SCHOOL_BY_SCHOOLNAME =
      "SELECT schoolid,provinceid,cityid,lvid FROM sm_school WHERE schoolname=?";

  private static final String LOAD_SCHOOL_BY_SCHOOLID =
      "SELECT provinceid,cityid,lvid,schoolname FROM sm_school WHERE schoolid=?";

  private static final String INSERT_SCHOOL =
      "INSERT INTO sm_school(provinceid,cityid,lvid,schoolname) VALUES(?,?,?,?)";

  private static final String SAVE_SCHOOL = "UPDATE sm_school SET provinceid=?,cityid=?,lvid=?,schoolname=? WHERE schoolid=?";

  private static final String CLASS_COUNT =
      "SELECT count(1) FROM sm_class WHERE schoolid=?";

  private static final String ALL_CLASS_FOR_THIS_SCHOOL =
      "SELECT classid FROM sm_class WHERE schoolid=?";

  private static final String SEARCH_CLASS_FROM_THIS_SCHOOL = "SELECT classid FROM sm_class WHERE schoolid=? AND beginyear=? AND classname LIKE ? ORDER BY beginyear,classname";

  private static final String SEARCH_CLASS_FROM_THIS_SCHOOL_NO_CLASSNAME = "SELECT classid FROM sm_class WHERE schoolid=? AND beginyear=? ORDER BY beginyear,classname";

  private static final String SEARCH_CLASS_FROM_THIS_SCHOOL_NO_CLASSNAME_NO_BEGINYEAR = "SELECT classid FROM sm_class WHERE schoolid=? ORDER BY beginyear,classname";

  private static final String DELETE_CLASS =
      "DELETE FROM sm_class WHERE classid=?";

  private static final String DELETE_ALL_CLASS =
      "DELETE FROM sm_class WHERE schoolid=?";

  private int schoolId = -1; //学校ID

  private int provinceId = -1; //学校所在省份

  private int cityId = -1; //学校所在城市

  private int lvId = -1; //学校级别

  private String schoolName = null; //学校名称

  private boolean isChanged = false; //已修改标记

  /**
   * @return 返回 lvId。
   */
  public int getLvId() {
    return lvId;
  }

  /**
   * @param lvId
   *            要设置的 lvId。
   */
  public void setLvId(int lvId) {
    this.lvId = lvId;
    this.isChanged = true;
  }

  /**
   * @return 返回 provinceId。
   */
  public int getProvinceId() {
    return provinceId;
  }

  /**
   * @param provinceId
   *            要设置的 provinceId。
   */
  public void setProvinceId(int provinceId) {
    this.provinceId = provinceId;
    this.isChanged = true;
  }

  /**
   * @return 返回 schoolId。
   */
  public int getSchoolId() {
    return schoolId;
  }

  /**
   * @param schoolId
   *            要设置的 schoolId。
   */
  public void setSchoolId(int schoolId) {
    this.schoolId = schoolId;
    this.isChanged = true;
  }

  /**
   * @return 返回 schoolName。
   */
  public String getSchoolName() {
    return schoolName;
  }

  /**
   * @param schoolName
   *            要设置的 schoolName。
   */
  public void setSchoolName(String schoolName) {
    this.schoolName = schoolName;
    this.isChanged = true;
  }

  /**
   * @return 返回 cityId。
   */
  public int getCityId() {
    return cityId;
  }

  //////////////////////////////////////////////////////////////////

  //////////////////////////////////////////////////////////////////

  /**
   * 构造方法，其可以通过schoolid读取学校信息。
   *
   * @param schoolid
   * @throws SMSchoolNotFoundException
   * @throws UnacceptableException
   */
  public SMSchool(int schoolid) throws SMSchoolNotFoundException,
      UnacceptableException {
    if (schoolid < 0) {
      throw new UnacceptableException("参数值有问题，“学校ID”不能小于0。");
    }
    this.schoolId = schoolid;
    loadFromDB();
  }

  /**
   * 构造方法，其可以通过schoolname读取学校信息。
   *
   * @param schoolname
   * @throws SMSchoolNotFoundException
   * @throws UnacceptableException
   */
  public SMSchool(String schoolname) throws SMSchoolNotFoundException,
      UnacceptableException {
    if (schoolname == null || schoolname == "") {
      throw new UnacceptableException("参数值有问题，“学校名称”不能为空。");
    }
    this.schoolName = schoolname;
    loadFromDB();
  }

  /**
   * 构造方法，用来创建一个新的学校。
   *
   * @param provinceid
   * @param cityid
   * @param lvid
   * @param schoolname
   * @throws SMSchoolAlreadyExistsException
   * @throws UnacceptableException
   */
  public SMSchool(int provinceid, int cityid, int lvid, String schoolname) throws
      SMSchoolAlreadyExistsException, UnacceptableException {
    this.provinceId = provinceid;
    this.cityId = cityid;
    this.lvId = lvid;
    this.schoolName = schoolname;
    insertIntoDB();
  }

  /**
   * 读取学校信息
   *
   * @throws SMSchoolNotFoundException
   * @throws UnacceptableException
   */
  private void loadFromDB() throws SMSchoolNotFoundException,
      UnacceptableException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = DBManager.getConnection();
      if (this.schoolId == -1) {
        pstmt = conn.prepareStatement(LOAD_SCHOOL_BY_SCHOOLNAME);
        pstmt.setString(1, this.schoolName);
        rs = pstmt.executeQuery();
        if (!rs.next()) {
          throw new SMSchoolNotFoundException("学校“" + this.schoolName
                                              + "”不存在。");
        }
        this.schoolId = rs.getInt(1);
        this.provinceId = rs.getInt(2);
        this.cityId = rs.getInt(3);
        this.lvId = rs.getInt(4);
      }
      else {
        pstmt = conn.prepareStatement(LOAD_SCHOOL_BY_SCHOOLID);
        pstmt.setInt(1, this.schoolId);
        rs = pstmt.executeQuery();
        if (!rs.next()) {
          throw new SMSchoolNotFoundException("学校ID“" + this.schoolId
                                              + "”不存在。");
        }
        this.provinceId = rs.getInt(1);
        this.cityId = rs.getInt(2);
        this.lvId = rs.getInt(3);
        this.schoolName = rs.getString(4);
      }
    }
    catch (SQLException sqle) {
      throw new UnacceptableException("读取学校数据失败。", sqle);
    }
    finally {
      DBManager.closeObject(conn, pstmt, rs);
    }
  }

  /**
   * 添加新学校操作
   *
   * @throws SMSchoolAlreadyExistsException
   * @throws UnacceptableException
   */
  private void insertIntoDB() throws SMSchoolAlreadyExistsException,
      UnacceptableException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      loadFromDB();
      throw new SMSchoolAlreadyExistsException("该学校已存在！");
    }
    catch (SMSchoolNotFoundException unfe) {
      try {
        conn = DBManager.getConnection();
        pstmt = conn.prepareStatement(INSERT_SCHOOL);
        pstmt.setInt(1, this.provinceId);
        pstmt.setInt(2, this.cityId);
        pstmt.setInt(3, this.lvId);
        pstmt.setString(4, this.schoolName);
        pstmt.executeUpdate();
      }
      catch (SQLException e) {
        throw new UnacceptableException("写入学校数据失败.", e);
      }
      finally {
        DBManager.closeObject(conn, pstmt, rs);
      }
    }
  }

  /**
   * 保存数据到DB
   *
   */
  private void saveToDB() throws UnacceptableException {
    Connection conn = null;
    try {
      conn = DBManager.getConnection();
      saveToDB(conn);
    }
    catch (SQLException sqle) {
      throw new UnacceptableException("保存数据出错", sqle);
    }
    finally {
      try {
        conn.close();
      }