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