public Standings() {
Connection con = null;
try {
con = Connection.getConnection();
con.startConnection();
HashMap<Integer, Team> teamshelper = new HashMap<Integer, Team>();
// Make sure all teams are in the list, even if no games are played
PreparedStatement pstmt = con.prepareStatement("select teamid, teamname from team");
pstmt.execute();
ResultSet rs = pstmt.getResultSet();
while (rs.next()) {
Team team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
teamshelper.put(rs.getInt("teamid"), team);
}
// Separate queries for home team wins, away team wins, home team draw and awayteamdraws
// Home team wins
pstmt =
con.prepareStatement(
"select hometeam as teamid, teamname, count(*) as score "
+ "from (select h.teamid as hometeam, h.teamname as teamname, a.teamid as awayteam, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore "
+ "from game g1 join game g2 on g1.gameid = g2.gameid "
+ "join team h on g1.hometeam = h.teamid "
+ "join team a on g2.awayteam = a.teamid "
+ "full join score s1 on s1.gameid = g1.gameid "
+ "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) "
+ "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) "
+ "where g1.date < now() "
+ "group by h.teamid, h.teamname, a.teamid, g1.gameid, g1.date "
+ "order by g1.date desc) game "
+ "where htscore > atscore "
+ "group by hometeam, awayteam, teamname");
System.out.println(pstmt.toString());
pstmt.execute();
rs = pstmt.getResultSet();
while (rs.next()) {
Team team = null;
if (teamshelper.containsKey(rs.getInt("teamid"))) {
team = teamshelper.get(rs.getInt("teamid"));
team.addPoints(rs.getInt("score") * 2);
} else { // May only happen, if game is changed while the code is executing. Very unlikely.
team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
team.addPoints(rs.getInt("score") * 2); // A win counts 2 points
}
teamshelper.put(rs.getInt("teamid"), team);
}
// Away team wins
pstmt =
con.prepareStatement(
"select awayteam as teamid, teamname, count(*) as score "
+ "from (select h.teamid as hometeam, a.teamid as awayteam, a.teamname as teamname, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore "
+ "from game g1 join game g2 on g1.gameid = g2.gameid "
+ "join team h on g1.hometeam = h.teamid "
+ "join team a on g2.awayteam = a.teamid "
+ "full join score s1 on s1.gameid = g1.gameid "
+ "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) "
+ "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) "
+ "where g1.date < now() "
+ "group by h.teamid, a.teamname, a.teamid, g1.gameid, g1.date "
+ "order by g1.date desc) game "
+ "where atscore > htscore "
+ "group by hometeam, awayteam, teamname");
pstmt.execute();
rs = pstmt.getResultSet();
while (rs.next()) {
Team team = null;
if (teamshelper.containsKey(rs.getInt("teamid"))) {
team = teamshelper.get(rs.getInt("teamid"));
team.addPoints(rs.getInt("score") * 2);
} else { // May only happen, if game is changed while the code is executing. Very unlikely.
team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
team.addPoints(rs.getInt("score") * 2); // A win counts 2 points
}
teamshelper.put(rs.getInt("teamid"), team);
}
// Home team draws
pstmt =
con.prepareStatement(
"select hometeam as teamid, teamname, count(*) as score "
+ "from (select h.teamid as hometeam, a.teamid as awayteam, h.teamname as teamname, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore "
+ "from game g1 join game g2 on g1.gameid = g2.gameid "
+ "join team h on g1.hometeam = h.teamid "
+ "join team a on g2.awayteam = a.teamid "
+ "full join score s1 on s1.gameid = g1.gameid "
+ "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) "
+ "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) "
+ "where g1.date < now() "
+ "group by h.teamid, h.teamname, a.teamid, g1.gameid, g1.date "
+ "order by g1.date desc) game "
+ "where htscore = atscore "
+ "group by hometeam, awayteam, teamname");
pstmt.execute();
rs = pstmt.getResultSet();
while (rs.next()) {
Team team = null;
if (teamshelper.containsKey(rs.getInt("teamid"))) {
team = teamshelper.get(rs.getInt("teamid"));
team.addPoints(rs.getInt("score"));
} else { // May only happen, if game is changed while the code is executing. Very unlikely.
team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
team.addPoints(rs.getInt("score")); // A draw counts 1 point
}
teamshelper.put(rs.getInt("teamid"), team);
}
// Away team draws
pstmt =
con.prepareStatement(
"select awayteam as teamid, teamname, count(*) as score "
+ "from (select h.teamid as hometeam, a.teamid as awayteam, a.teamname as teamname, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore "
+ "from game g1 join game g2 on g1.gameid = g2.gameid "
+ "join team h on g1.hometeam = h.teamid "
+ "join team a on g2.awayteam = a.teamid "
+ "full join score s1 on s1.gameid = g1.gameid "
+ "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) "
+ "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) "
+ "where g1.date < now() "
+ "group by h.teamid, a.teamname, a.teamid, g1.gameid, g1.date "
+ "order by g1.date desc) game "
+ "where htscore = atscore "
+ "group by hometeam, awayteam, teamname");
pstmt.execute();
rs = pstmt.getResultSet();
while (rs.next()) {
Team team = null;
if (teamshelper.containsKey(rs.getInt("teamid"))) {
team = teamshelper.get(rs.getInt("teamid"));
team.addPoints(rs.getInt("score"));
} else { // May only happen, if game is changed while the code is executing. Very unlikely.
team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
team.addPoints(rs.getInt("score")); // A draw counts 1 point
}
teamshelper.put(rs.getInt("teamid"), team);
}
Collection<Team> c = teamshelper.values();
Iterator<Team> i = c.iterator();
teams = new ArrayList<Team>();
while (i.hasNext()) teams.add(i.next());
Collections.sort(teams);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.closeConnection();
} catch (SQLException e) {
// If it can't be closed just continue.
} catch (NullPointerException e) {
// Connection was never initialized, just do nothing.
e.printStackTrace();
}
}
}
