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