/**
* 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;
}
/** 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;
}
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;
}
/**
* 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;
}
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;
}