public void deleteMember(Band band, String username) {
try {
PreparedStatement stmt =
conn.prepareStatement(
"delete from available_times where band_id = ? and musician_id = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, band.getId());
stmt.setInt(2, this.getMusicianIdFromName(username));
stmt.executeUpdate();
stmt =
conn.prepareStatement(
"delete from bandmembers where band_id = ? and musician_id = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, band.getId());
stmt.setInt(2, this.getMusicianIdFromName(username));
stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public int getIdFromRehearsalRequest(Band band, RehearsalRequest rehearsalRequest) {
int id = -1;
try {
PreparedStatement stmt =
conn.prepareStatement(
"select id from rehearsal_requests where band_id = ? and start_time = ? and end_time = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, band.getId());
stmt.setTimestamp(2, new java.sql.Timestamp(rehearsalRequest.getStartTime().getTime()));
stmt.setTimestamp(3, new java.sql.Timestamp(rehearsalRequest.getEndTime().getTime()));
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
id = rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return id;
}
public void updateBand(Band band) {
try {
PreparedStatement stmt =
conn.prepareStatement(
"update bands set costs_per_hour = ? where id = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, band.getCostsPerHour());
stmt.setInt(2, band.getId());
stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void addAppearance(Band band, Appointment appointment) {
try {
PreparedStatement stmt =
conn.prepareStatement(
"insert into appearances values (?, ?)",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, appointment.getId());
stmt.setInt(2, band.getId());
stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void addMember(Band band, String username) {
try {
PreparedStatement stmt =
conn.prepareStatement(
"insert into bandmembers values(?, ?, ?)",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, band.getId());
stmt.setInt(2, this.getMusicianIdFromName(username));
stmt.setTimestamp(3, new java.sql.Timestamp(new Date().getTime()));
stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void addRehearsalRequest(Band band, RehearsalRequest rehearsalRequest) {
try {
PreparedStatement stmt =
conn.prepareStatement(
"insert into rehearsal_requests values (0, ?, ?, ?, ?)",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, band.getId());
stmt.setTimestamp(2, new java.sql.Timestamp(rehearsalRequest.getStartTime().getTime()));
stmt.setTimestamp(3, new java.sql.Timestamp(rehearsalRequest.getEndTime().getTime()));
stmt.setDouble(4, rehearsalRequest.getDuration());
stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void addAppointment(Band band, Appointment appointment) {
try {
PreparedStatement stmt =
conn.prepareStatement(
"insert into appointments values (0, ?, ?, ?, ?, ?, ?, ?)",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, band.getId());
stmt.setInt(2, appointment.getLocation().getId());
stmt.setInt(3, appointment.getGrounded());
stmt.setTimestamp(4, new java.sql.Timestamp(appointment.getStartTime().getTime()));
stmt.setTimestamp(5, new java.sql.Timestamp(appointment.getEndTime().getTime()));
stmt.setString(6, appointment.getName());
stmt.setString(7, appointment.getDescription());
stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Band allAvailableTimes(Band band) {
Band ret = band;
band.getLeader().setAvailableTimes(new Vector<AvailableTime>());
for (Musician m : band.getMusicians()) {
m.setAvailableTimes(new Vector<AvailableTime>());
}
try {
PreparedStatement stmt =
conn.prepareStatement(
"select id, musician_id, start_time, end_time from available_times where band_id = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, band.getId());
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
for (Musician m : band.getMusicians()) {
if (m.getId() == rs.getInt(2)) {
m.addAvailableTime(
new AvailableTime(rs.getInt(1), rs.getTimestamp(3), rs.getTimestamp(4)));
}
}
if (band.getLeader().getId() == rs.getInt(2)) {
band.getLeader()
.addAvailableTime(
new AvailableTime(rs.getInt(1), rs.getTimestamp(3), rs.getTimestamp(4)));
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ret;
}
