/**
* 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;
}
/**
* Add a shooter to the database.
*
* @param shooterName The name of the shooter
* @param clubName The name of the club that the shooter is shooting for, the club must exist in
* the database.
*/
public void addShooter(String shooterName, String clubName) {
String insertSql =
"INSERT INTO shooters (name, club) "
+ "VALUES ( ? , (SELECT id FROM clubs WHERE club = ?))";
DBUtils.executeStatement(connector, insertSql, shooterName, clubName);
}
/** 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;
}
/**
* Add a new competition to the database.
*
* @param shooterName Name of the shooter, must exist in the database
* @param clubName Name of the club that the shooter is shooting for, must exist in the database,
* and be related to the shooter
* @param competitionID The id of the competition, the same id which is set for the SIUS lane.
*/
public void addCompetition(String shooterName, String clubName, int competitionID) {
String insertSql =
"INSERT INTO competitions (id, shooter) VALUES (?, ("
+ "SELECT id FROM shooters "
+ "JOIN clubs ON shooters.club = clubs.id "
+ "WHERE shooters.name = ? "
+ "AND clubs.club = ?))";
DBUtils.executeStatement(connector, insertSql, competitionID, shooterName, clubName);
}
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;
}
/**
* Add a shot to the database
*
* @param shot The ShotEvent from which to take information to store in the database
* @throws ParseException if the time in provided ShotEvent cannot be parsed.
*/
public void addShot(ShotEvent shot) throws ParseException, IllegalStateException {
String insertSql =
"INSERT INTO shots "
+ "(time, seqNumber, xcoord, ycoord, value, decimalvalue, type, competitionid, fireingpoint, caliber)"
+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
DBUtils.executeStatement(
connector,
insertSql,
SIUSUtils.getLogTime(shot.getLogTimeStamp()),
shot.getSequenceNumber(),
shot.getXcoord(),
shot.getYcoord(),
shot.getShotValue(),
shot.getDecimalShotValue(),
SIUSUtils.getShotType(shot.getShotAttr()).ordinal(),
shot.getShooterID(),
shot.getFireingPointID(),
shot.getCaliber());
}
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;
}
public void addMatch(String name, List<Integer> teamA, List<Integer> teamB) {
ArgValidator.checkNotNullOrEmpty(name, "name");
ArgValidator.checkNotNullAndLength(teamA, 3, "teamA");
ArgValidator.checkNotNullAndLength(teamB, 3, "teamB");
String insertSql =
"INSERT INTO matches (name, "
+ "competition1a, "
+ "competition1b, "
+ "competition2a, "
+ "competition2b, "
+ "competition3a, "
+ "competition3b) VALUES ( ?, ?, ?, ?, ?, ?, ?)";
DBUtils.executeStatement(
connector,
insertSql,
name,
teamA.get(0),
teamB.get(0),
teamA.get(1),
teamB.get(1),
teamA.get(2),
teamB.get(2));
}
public void addCompetition(int competitionID, int shooterID) {
String insertSql = "INSERT INTO competitions (id, shooter) VALUES (?, ?)";
DBUtils.executeStatement(connector, insertSql, competitionID, shooterID);
}
/**
* Add a club to the database
*
* @param clubName String with the name of the club
*/
public void addClub(String clubName) {
String insertSql = "INSERT INTO clubs (club) VALUES (?)";
DBUtils.executeStatement(connector, insertSql, clubName);
}
