public static ArrayList<Study> getOpenAndNotParticipated(String emailAddress) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query =
"SELECT * FROM study WHERE NOT EXISTS(SELECT * FROM answer WHERE study.code = answer.code AND email = ?)"
+ "AND status = ? ";
try {
ps = connection.prepareStatement(query);
ps.setString(1, emailAddress);
ps.setString(2, "open");
rs = ps.executeQuery();
Study study = null;
ArrayList<Study> studies = new ArrayList<Study>();
while (rs.next()) {
study = new Study();
study.setName(rs.getString("name"));
study.setCode(rs.getString("code"));
study.setDescription(rs.getString("description"));
study.setCreatorEmail(rs.getString("creatorEmail"));
study.setDateCreated(rs.getTimestamp("dateCreated"));
study.setQuestion(rs.getString("question"));
study.setRequestedParticipants(rs.getInt("requestedParticipants"));
study.setNumOfParticipants(rs.getInt("numOfParticipants"));
study.setStatus(rs.getString("status"));
study.setImageURL(rs.getString("imageURL"));
studies.add(study);
}
return studies;
} catch (SQLException e) {
System.out.println(e);
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static Study getStudy(String studyCode) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT * FROM study " + "WHERE code = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, studyCode);
rs = ps.executeQuery();
Study study = null;
if (rs.next()) {
study = new Study();
study.setName(rs.getString("name"));
study.setCode(rs.getString("code"));
study.setDescription(rs.getString("description"));
study.setCreatorEmail(rs.getString("creatorEmail"));
study.setDateCreated(rs.getTimestamp("dateCreated"));
study.setQuestion(rs.getString("question"));
study.setRequestedParticipants(rs.getInt("requestedParticipants"));
study.setNumOfParticipants(rs.getInt("numOfParticipants"));
study.setStatus(rs.getString("status"));
study.setImageURL(rs.getString("imageURL"));
}
return study;
} catch (SQLException e) {
System.out.println(e);
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}