/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
