コード例 #1
0
  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);
    }
  }
コード例 #2
0
  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);
    }
  }