/**
* Updates a playlist that is selected by ID.
*
* @param p the playlist to be updated.
* @throws SQLException
*/
public void update(Playlist p) throws SQLException {
String sql = "" + "UPDATE Playlist " + "SET Name = ? " + "WHERE ID =" + p.getId();
Connection con = dataSource.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, p.getName());
int affectedRows = ps.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Unable to update playlist.");
}
}
/**
* Adds a song-object to a playlist-object in the database.
*
* @param p the playlist-object for the song to be added to.
* @param s the song-object to be added to the playlist.
* @throws SQLException
*/
public void addSong(Playlist p, Song s) throws SQLException {
Connection con = dataSource.getConnection();
String sql1 =
"" + "select MAX(seqNo)'top' " + "From PlaylistSong " + "where PlaylistID = " + p.getId();
PreparedStatement ps1 = con.prepareStatement(sql1);
ResultSet rs = ps1.executeQuery();
rs.next();
int seqNo = rs.getInt("top") + 1;
String sql = "" + "INSERT INTO PlayListSong " + "VALUES(?,?,?)";
PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
ps.setInt(1, p.getId());
ps.setInt(2, s.getId());
ps.setInt(3, seqNo);
int affectedRows = ps.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Unable to add song to playlist.");
}
ResultSet keys = ps.getGeneratedKeys();
keys.next();
}
/**
* Removes a song from a given playlist on the server.
*
* @param p the playlist-object for the song to be removed from.
* @param s the song-object to be removed.
* @throws SQLException
*/
public void removeSong(Playlist p, Song s) throws SQLException {
String sql = "" + "DELETE " + "from PlayListSong " + "WHERE PlaylistID = ? and SongID = ?";
Connection con = dataSource.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, p.getId());
ps.setInt(2, s.getId());
int affectedRows = ps.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Unable to delete song from playlist");
}
}
/**
* Puts a new playlist-object on the server.
*
* @param p a given playlist-object.
* @return the newly created playlist object.
* @throws SQLException
*/
public Playlist insert(Playlist p) throws SQLException {
String sql = "" + "INSERT INTO Playlist (Name)" + "VALUES(?)";
Connection con = dataSource.getConnection();
PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
ps.setString(1, p.getName());
int affectedRows = ps.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Unable to insert playlist.");
}
ResultSet keys = ps.getGeneratedKeys();
keys.next();
int id = keys.getInt(1);
return new Playlist(id, p);
}