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); } }