예제 #1
0
  private Statistic getMostPlayedAgainst(Long team_id) {

    StringBuilder sqlToQuery =
        new StringBuilder(
            "SELECT DISTINCT Team.id, Team.team_name, Team.image, COUNT(*) AS count_matches from Team, Game ");
    sqlToQuery.append(
        "WHERE (end_time != 0 AND Team.id = Game.home_team_id AND Game.visitor_team_id = ");
    sqlToQuery.append(team_id);
    sqlToQuery.append(" ) ");
    sqlToQuery.append("OR (end_time != 0 AND Game.home_team_id = ");
    sqlToQuery.append(team_id);
    sqlToQuery.append(" And Team.id = Game.visitor_team_id ) ");
    sqlToQuery.append("Group BY Team.id ORDER BY count_matches DESC LIMIT 1;");

    ResultSet resultset = OpenFoosDatabase.executeQueryToFoosBase(sqlToQuery.toString());
    Team team = new Team();
    Statistic statistic = new Statistic();
    try {
      while (resultset.next()) {

        team.id = resultset.getLong("id");
        team.team_name = resultset.getString("team_name");
        team.image = resultset.getString("image");
        statistic.count_most_played_against = resultset.getInt("count_matches");
      }
      resultset.close();
      if (team.id != null && team.team_name != null) {
        statistic.target_team = team;
      }
    } catch (SQLException e) {
      System.out.println(e.toString());
    }

    return statistic;
  }
예제 #2
0
  // the difference is equal to 1. Matches that was played 10-9 or 9-10
  private Statistic getMostRegularAppearances(Long team_id) {

    StringBuilder sqlToQuery =
        new StringBuilder(
            "SELECT DISTINCT Team.id, Team.team_name, Team.image, count(*) AS count_matches FROM Game, Team ");
    sqlToQuery.append("WHERE ( winner_id != ");
    sqlToQuery.append(team_id);
    sqlToQuery.append(" ) ");
    sqlToQuery.append("AND (( Team.id = Game.home_team_id AND Game.visitor_team_id = ");
    sqlToQuery.append(team_id);
    sqlToQuery.append(" ) ");
    sqlToQuery.append("OR ( Game.home_team_id = ");
    sqlToQuery.append(team_id);
    sqlToQuery.append(" AND Team.id = Game.visitor_team_id ))");
    sqlToQuery.append(
        " AND (( home_score=10 AND visitor_score=9 ) OR ( home_score=9 AND visitor_score=10 ))");
    sqlToQuery.append("GROUP BY Team.id ORDER BY count_matches DESC LIMIT 1;");

    ResultSet resultset = OpenFoosDatabase.executeQueryToFoosBase(sqlToQuery.toString());
    Team team = new Team();
    Statistic statistic = new Statistic();
    try {
      while (resultset.next()) {

        team.id = resultset.getLong("id");
        team.team_name = resultset.getString("team_name");
        team.image = resultset.getString("image");
        statistic.count_most_regular_appearances = resultset.getInt("count_matches");
      }
      resultset.close();
      if (team.id != null && team.team_name != null) {
        statistic.target_team = team;
      }
    } catch (SQLException e) {
      System.out.println(e.toString());
    }
    return statistic;
  }
예제 #3
0
  private Statistic getMostWonAgainst(Long team_id) {

    StringBuilder sqlToQuery =
        new StringBuilder(
            "SELECT DISTINCT Team.id, Team.team_name, Team.image,  count(*) AS count_matches FROM Team, Game ");
    sqlToQuery.append("WHERE ( winner_id = ");
    sqlToQuery.append(team_id);
    sqlToQuery.append(" ) ");
    sqlToQuery.append("AND (( Team.id = Game.home_team_id AND Game.visitor_team_id = ");
    sqlToQuery.append(team_id);
    sqlToQuery.append(" ) ");
    sqlToQuery.append("OR ( Game.home_team_id = ");
    sqlToQuery.append(team_id);
    sqlToQuery.append(" AND Team.id = Game.visitor_team_id ))");
    sqlToQuery.append("GROUP BY Team.id ORDER BY count_matches DESC LIMIT 1;");

    ResultSet resultset = OpenFoosDatabase.executeQueryToFoosBase(sqlToQuery.toString());
    Team team = new Team();
    Statistic statistic = new Statistic();
    try {
      while (resultset.next()) {

        team.id = resultset.getLong("id");
        team.team_name = resultset.getString("team_name");
        team.image = resultset.getString("image");
        statistic.count_most_won_against = resultset.getInt("count_matches");
      }
      resultset.close();
      if (team.id != null && team.team_name != null) {
        statistic.target_team = team;
      }
    } catch (SQLException e) {
    } finally {
    }
    return statistic;
  }