Exemple #1
0
  /**
   * Determine whether or a not a User with the supplied researcherID exists
   *
   * @param username The researcherID to test
   * @return true if the user exists, false if not
   * @throws SQLException if a database error was encountered
   */
  public static boolean userExists(int researcherID) throws SQLException {
    boolean returnVal = false;

    // Get our connection to the database.
    Connection conn = DBConnectionManager.getConnection("yrc");
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      stmt = conn.prepareStatement("SELECT researcherID FROM tblUsers WHERE researcherID = ?");
      stmt.setInt(1, researcherID);

      rs = stmt.executeQuery();

      // No rows returned.
      if (!rs.next()) {
        returnVal = false;
      } else {
        returnVal = true;
      }

      rs.close();
      rs = null;

      stmt.close();
      stmt = null;

      conn.close();
      conn = null;
    } finally {

      // Always make sure result sets and statements are closed,
      // and the connection is returned to the pool
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {;
        }
        rs = null;
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {;
        }
        stmt = null;
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {;
        }
        conn = null;
      }
    }

    return returnVal;
  }
Exemple #2
0
  /**
   * Determine whether or a not a Researcher with the supplied email exists
   *
   * @param email The email to test
   * @return The researcher ID of the researcher if it exists, -1 if it doesn't
   * @throws SQLException if a database error was encountered
   */
  public static int emailExists(String email) throws SQLException {
    int returnVal = -1;

    if (email == null || email.equals("")) {
      return -1;
    }

    // Get our connection to the database.
    Connection conn = DBConnectionManager.getConnection("yrc");
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      stmt =
          conn.prepareStatement(
              "SELECT researcherID FROM tblResearchers WHERE researcherEmail = ?");
      stmt.setString(1, email);

      rs = stmt.executeQuery();

      // No rows returned.
      if (!rs.next()) {
        returnVal = -1;
      } else {
        returnVal = rs.getInt("researcherID");
      }

      rs.close();
      rs = null;

      stmt.close();
      stmt = null;

      conn.close();
      conn = null;
    } finally {

      // Always make sure result sets and statements are closed,
      // and the connection is returned to the pool
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {;
        }
        rs = null;
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {;
        }
        stmt = null;
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {;
        }
        conn = null;
      }
    }

    return returnVal;
  }
Exemple #3
0
  /**
   * Get a populated User object corresponding to a username.
   *
   * @param username The username to test
   * @return The User object corresponding to that username.
   * @throws NoSuchUserException if that username does not exist.
   * @throws SQLException if a database error was encountered.
   */
  public static User getUser(String username) throws NoSuchUserException, SQLException {
    // The User to return
    User theUser;

    // Make sure the username isn't null
    if (username == null) {
      throw new NoSuchUserException("got null for username in UserUtils.getUser");
    }

    // Get our connection to the database.
    Connection conn = DBConnectionManager.getConnection("yrc");
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      stmt = conn.prepareStatement("SELECT researcherID FROM tblUsers WHERE username = ?");
      stmt.setString(1, username);

      rs = stmt.executeQuery();

      // No rows returned.
      if (!rs.next()) {
        throw new NoSuchUserException("Username not found.");
      }

      theUser = new User();

      try {
        theUser.load(rs.getInt("researcherID"));
      } catch (InvalidIDException e) {
        throw new NoSuchUserException(
            "Somehow, we got an invalid ID ("
                + rs.getInt("researcherID")
                + ") after we got the ID from the username...  This can't be good.");
      }

      rs.close();
      rs = null;

      stmt.close();
      stmt = null;

      conn.close();
      conn = null;
    } finally {

      // Always make sure result sets and statements are closed,
      // and the connection is returned to the pool
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {;
        }
        rs = null;
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {;
        }
        stmt = null;
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {;
        }
        conn = null;
      }
    }

    return theUser;
  }
Exemple #4
0
  /**
   * Simply return an ArrayList of all the researchers in the database (as Researcher objects)
   *
   * @return A list of all the Researchers in the database
   */
  public static ArrayList getAllResearchers() throws SQLException, InvalidIDException {
    ArrayList retList = new ArrayList();

    // Get our connection to the database.
    Connection conn = DBConnectionManager.getConnection("yrc");
    Statement stmt = null;
    ResultSet rs = null;

    try {
      stmt = conn.createStatement();

      // Our SQL statement
      String sqlStr =
          "SELECT researcherID, researcherFirstName, researcherLastName, researcherEmail, researcherOrganization FROM tblResearchers ORDER BY researcherLastName";

      // Our results
      rs = stmt.executeQuery(sqlStr);

      // Iterate over list and populate our return list
      while (rs.next()) {
        Researcher researcher = new Researcher();
        researcher.setID(rs.getInt("researcherID"));
        researcher.setFirstName(rs.getString("researcherFirstName"));
        researcher.setLastName(rs.getString("researcherLastName"));
        researcher.setEmail(rs.getString("researcherEmail"));
        researcher.setOrganization(rs.getString("researcherOrganization"));

        retList.add(researcher);
      }

      rs.close();
      rs = null;

      stmt.close();
      stmt = null;

      conn.close();
      conn = null;
    } finally {

      // Always make sure result sets and statements are closed,
      // and the connection is returned to the pool
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {;
        }
        rs = null;
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {;
        }
        stmt = null;
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {;
        }
        conn = null;
      }
    }

    return retList;
  }
Exemple #5
0
  /**
   * Get all of the projects for which the supplied researcher ID is associated as a researcher
   *
   * @param researcherID The researcher ID to use
   * @return An list of populated Project objects
   */
  public static List<Project> getProjectsByResearcher(int researcherID) throws SQLException {
    List<Project> retList = new ArrayList<Project>();

    // Get our connection to the database.
    Connection conn = DBConnectionManager.getConnection("yrc");
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      // Our SQL statement
      String sqlStr =
          "SELECT DISTINCT  p.projectID "
              + "FROM tblProjects AS p "
              + "LEFT OUTER JOIN projectResearcher AS pr "
              + "ON p.projectID = pr.projectID "
              + "WHERE (p.projectPI = ? OR pr.researcherID = ?) "
              + "ORDER BY p.projectID";

      stmt = conn.prepareStatement(sqlStr);
      stmt.setInt(1, researcherID);
      stmt.setInt(2, researcherID);

      // Our results
      rs = stmt.executeQuery();

      // Iterate over list and populate our return list
      while (rs.next()) {
        int projectID = rs.getInt("projectID");

        try {
          Project proj = ProjectDAO.instance().load(projectID);
          retList.add(proj);
        } catch (InvalidIDException iie) {
          continue;
        }
      }
    } finally {

      // Always make sure result sets and statements are closed,
      // and the connection is returned to the pool
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {;
        }
        rs = null;
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {;
        }
        stmt = null;
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {;
        }
        conn = null;
      }
    }

    return retList;
  }