public void addMusician(Musician m) {
try {
PreparedStatement stmt =
conn.prepareStatement(
"insert into musicians values(0, ?, ?, ?, ?, ?, ?)",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setString(1, m.getUsername());
stmt.setString(2, m.getPassword());
stmt.setString(3, m.getFirstName());
stmt.setString(4, m.getLastName());
if (m.getBirthdate() == null) {
stmt.setTimestamp(5, null);
} else {
stmt.setTimestamp(5, new java.sql.Timestamp(m.getBirthdate().getTime()));
}
if (m.getHabitation() == null) {
stmt.setNull(6, java.sql.Types.INTEGER);
} else {
stmt.setInt(6, m.getHabitation().getId());
}
stmt.executeUpdate();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
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;
}
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext(APP_CTX);
Musician player = (Musician) ctx.getBean("player");
player.play();
Musician mPlayer = (Musician) ctx.getBean("mPlayer");
mPlayer.play();
Musician harpist = (Musician) ctx.getBean("harpist");
harpist.play();
}
public void updateMusician(Musician m) {
try {
PreparedStatement stmt =
conn.prepareStatement(
"update musicians m set first_name = ?,"
+ "last_name = ?, birthdate = ?, habitation_id = ?, password = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setString(1, m.getFirstName());
stmt.setString(2, m.getLastName());
stmt.setTimestamp(3, new java.sql.Timestamp(m.getBirthdate().getTime()));
stmt.setInt(4, m.getHabitation().getId());
stmt.setString(5, m.getPassword());
stmt.executeUpdate();
// set instrument_skills
stmt =
conn.prepareStatement(
"delete from instrument_skills where musician_id = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, m.getId());
stmt.executeUpdate();
for (Instrument i : m.getSkills()) {
stmt =
conn.prepareStatement(
"insert into instrument_skills values(?, ?)",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1, m.getId());
stmt.setInt(2, i.getId());
stmt.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}
}