コード例 #1
0
  /**
   * Gets the registered shooters present in the database, along with the name of the club they
   * belong to.
   */
  public List<Shooter> getShooters() {
    String selectSql =
        "SELECT shooters.id, shooters.name, clubs.club FROM shooters JOIN clubs ON shooters.club = clubs.id";
    List<Shooter> shooters = new ArrayList<Shooter>();
    try (Connection conn = connector.getConnection(); ) {
      ResultSet dbResult = null;
      PreparedStatement ps = null;
      try {
        ps = DBUtils.makePreparedStatement(conn, selectSql, new Object[0]);
        dbResult = ps.executeQuery();

        while (dbResult.next()) {
          Shooter shooter = new Shooter();
          shooter.setId(dbResult.getInt(1));
          shooter.setShooterName(dbResult.getString(2));
          shooter.setClubName(dbResult.getString(3));
          shooters.add(shooter);
        }
      } finally {
        if (dbResult != null) {
          dbResult.close();
        }
        if (ps != null) {
          ps.close();
        }
      }
    } catch (SQLException e) {
      throw new IllegalStateException(
          "Could not retrieve the clubs for with the SQL '" + selectSql + "'.", e);
    }
    return shooters;
  }
コード例 #2
0
  /** Get the list of clube registered in the database */
  public List<String> getClubs() {
    String selectSql = "SELECT club FROM clubs";
    List<String> clubs = new ArrayList<String>();
    try (Connection conn = connector.getConnection(); ) {
      ResultSet dbResult = null;
      PreparedStatement ps = null;
      try {
        ps = DBUtils.makePreparedStatement(conn, selectSql, new Object[0]);
        dbResult = ps.executeQuery();

        while (dbResult.next()) {
          clubs.add(dbResult.getString(1));
        }
      } finally {
        if (dbResult != null) {
          dbResult.close();
        }
        if (ps != null) {
          ps.close();
        }
      }
    } catch (SQLException e) {
      throw new IllegalStateException(
          "Could not retrieve the clubs for with the SQL '" + selectSql + "'.", e);
    }
    return clubs;
  }
コード例 #3
0
  public Match getMatch(int matchID) {
    String selectSql =
        "SELECT id, name, state, competition1a, competition2a, "
            + "competition3a, competition1b, competition2b, competition3b"
            + " FROM matches"
            + " WHERE id = ?";

    Match match = null;
    try (Connection conn = connector.getConnection(); ) {
      ResultSet dbResult = null;
      PreparedStatement ps = null;
      try {
        ps = DBUtils.makePreparedStatement(conn, selectSql, matchID);
        dbResult = ps.executeQuery();

        while (dbResult.next()) {
          match = new Match();
          match.setMatchID(dbResult.getInt("id"));
          match.setName(dbResult.getString("name"));
          match.setState(MatchState.fromValue(dbResult.getInt("state")));

          List<Integer> teamA = new ArrayList<Integer>(3);
          teamA.add(dbResult.getInt("competition1a"));
          teamA.add(dbResult.getInt("competition2a"));
          teamA.add(dbResult.getInt("competition3a"));

          List<Integer> teamB = new ArrayList<Integer>(3);
          teamB.add(dbResult.getInt("competition1b"));
          teamB.add(dbResult.getInt("competition2b"));
          teamB.add(dbResult.getInt("competition3b"));

          match.setTeamA(teamA);
          match.setTeamB(teamB);
        }
      } finally {
        if (dbResult != null) {
          dbResult.close();
        }
        if (ps != null) {
          ps.close();
        }
      }
    } catch (SQLException e) {
      throw new IllegalStateException(
          "Could not retrieve the clubs for with the SQL '" + selectSql + "'.", e);
    }

    return match;
  }
コード例 #4
0
  /**
   * Method to obtain the shots for a given competion.
   *
   * @param competitionID The id of the competition
   * @param type The type of shots wanted (sighters or competition)
   * @return List<Shot> the shots registered for the given competitionID and shottype
   */
  public List<Shot> getShotsByCompetitionID(int competitionID, ShotType type) {
    List<Shot> shots = null;
    String selectSql =
        "SELECT xcoord, ycoord, seqNumber, value, decimalvalue, time, caliber"
            + " FROM shots"
            + " WHERE competitionid = ?"
            + " AND type = ?"
            + " ORDER BY id";

    try (Connection conn = connector.getConnection(); ) {
      ResultSet dbResult = null;
      PreparedStatement ps = null;
      try {
        ps = DBUtils.makePreparedStatement(conn, selectSql, competitionID, type.ordinal());
        dbResult = ps.executeQuery();
        shots = new ArrayList<Shot>();
        while (dbResult.next()) {
          Shot shot =
              new Shot(
                  dbResult.getFloat("xcoord"),
                  dbResult.getFloat("ycoord"),
                  dbResult.getInt("seqNumber"),
                  dbResult.getInt("value"),
                  dbResult.getInt("decimalvalue"),
                  dbResult.getDate("time"),
                  dbResult.getInt("caliber"));
          shots.add(shot);
        }
      } finally {
        if (dbResult != null) {
          dbResult.close();
        }
        if (ps != null) {
          ps.close();
        }
      }
    } catch (SQLException e) {
      throw new IllegalStateException(
          "Could not retrieve the clubs for with the SQL '" + selectSql + "'.", e);
    }

    return shots;
  }
コード例 #5
0
  public Competition getCompetition(int competitionID) {
    String selectSql =
        "SELECT competitions.id, shooters.name, clubs.club FROM competitions"
            + " JOIN shooters ON competitions.shooter = shooters.id"
            + " JOIN clubs ON shooters.club = clubs.id"
            + " WHERE competitions.id = ?";

    Competition competition = null;
    try (Connection conn = connector.getConnection(); ) {
      ResultSet dbResult = null;
      PreparedStatement ps = null;
      try {
        ps = DBUtils.makePreparedStatement(conn, selectSql, competitionID);
        dbResult = ps.executeQuery();
        while (dbResult.next()) {
          competition = new Competition();
          competition.setCompetitionID(dbResult.getInt(1));
          Shooter shooter = new Shooter();
          shooter.setShooterName(dbResult.getString(2));
          shooter.setClubName(dbResult.getString(3));
          competition.setShooter(shooter);
        }
      } finally {
        if (dbResult != null) {
          dbResult.close();
        }
        if (ps != null) {
          ps.close();
        }
      }
    } catch (SQLException e) {
      throw new IllegalStateException(
          "Could not retrieve the clubs for with the SQL '" + selectSql + "'.", e);
    }

    return competition;
  }