示例#1
0
 /**
  * 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.");
   }
 }
示例#2
0
  /**
   * 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();
  }
示例#3
0
  /**
   * 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");
    }
  }
示例#4
0
  /**
   * 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);
  }