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