Artister.java

package com.pugh.sockso.web.action.browse;

import com.pugh.sockso.Utils;
import com.pugh.sockso.db.Database;
import com.pugh.sockso.music.Album;
import com.pugh.sockso.music.Artist;
import com.pugh.sockso.templates.web.browse.TArtist;
import com.pugh.sockso.web.BadRequestException;
import com.pugh.sockso.web.Request;
import com.pugh.sockso.web.action.BaseAction;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Artister extends BaseAction {
    
    /**
     *  browses a particular artist
     * 
     *  @param req the request object
     *  @param res the response object
     * 
     *  @throws IOException
     *  @throws SQLException
     *  @throws BadRequestException
     * 
     */
    
    @Override
    public void handleRequest() throws IOException, SQLException, BadRequestException {
        
        final Request req = getRequest();
        final int id = Integer.parseInt( req.getUrlParam(2)  );
        final Artist artist = getArtist( id );
        final List<Album> albums = getArtistAlbums( id );
        
        showArtist( artist, albums );
        
    }
    
    /**
     *  shows the artist page listing their albums
     * 
     *  @param artist
     *  @param albums
     * 
     *  @throws java.io.IOException
     * 
     */
    
    protected void showArtist( final Artist artist, final List<Album> albums  ) throws IOException, SQLException {
        
        final TArtist tpl = new TArtist();

        tpl.setArtist( artist );
        tpl.setAlbums( albums );

        getResponse().showHtml( tpl );

    }

    /**
     *  fetches the albums for an artist
     * 
     *  @param artistId
     * 
     *  @return
     * 
     *  @throws java.sql.SQLException
     * 
     */
    
    protected List<Album> getArtistAlbums( final int artistId ) throws SQLException {

        ResultSet rs = null;
        PreparedStatement st = null;
        
        try {
            
            final Database db = getDatabase();
            final String sql = " select ar.id as artistId, ar.name as artistName, " +
                        " al.id as albumId, al.name as albumName, al.year as albumYear, count(t.id) as trackCount " +
                        " from albums al " +
                            " inner join artists ar " +
                            " on ar.id = al.artist_id " +
                            " left outer join tracks t " +
                            " on t.album_id = al.id " +
                        " where (t.artist_id = ? or al.artist_id = ?) " +
                        " group by artistId, artistName, albumId, albumYear, albumName " +
                        " order by al.year desc, al.name asc ";
            st = db.prepare( sql );
            st.setInt( 1, artistId );
            st.setInt( 2, artistId );
            rs = st.executeQuery();

            final List<Album> albums = new ArrayList<Album>();
            
            while ( rs.next() ) {
                final Artist artist = new Artist.Builder()
                        .id(rs.getInt("artistId"))
                        .name(rs.getString("artistName"))
                        .build();
                albums.add(
                    new Album.Builder()
                        .artist( artist )
                        .id( rs.getInt("albumId") )
                        .name( rs.getString("albumName") )
                        .year( rs.getString("albumYear") )
                        .trackCount( rs.getInt("trackCount") )
                        .build()
                );
            }

            return albums;
            
        }
        
        finally {
            Utils.close( rs );
            Utils.close( st );
        }
            
    }
    
    /**
     *  fetches an artist, if not found then a BadRequestException is thrown
     * 
     *  @param artistId
     * 
     *  @return
     * 
     *  @throws java.sql.SQLException
     *  @throws com.pugh.sockso.web.BadRequestException
     * 
     */
    
    protected Artist getArtist( final int artistId ) throws SQLException, BadRequestException {
        
        ResultSet rs = null;
        PreparedStatement st = null;
        
        try {

            final Database db = getDatabase();
            final String sql = " select ar.id as id, ar.name as name, ar.date_added as date_added, " +
                        " count(t.id) as trackCount, " +
                        " ( " +
                            " select count(*) as playCount " +
                            " from play_log l " +
                                " inner join tracks t " +
                                " on t.id = l.track_id " +
                            " where t.artist_id = ar.id " +
                        " ) as playCount " +
                  " from artists ar " +
                       " left outer join tracks t " +
                       " on t.artist_id = ar.id " +
                  " where ar.id = ? " +
                  " group by ar.id, ar.name, ar.date_added " +
                  " limit 1 ";
            st = db.prepare( sql );
            st.setInt( 1, artistId );
            rs = st.executeQuery();
            if ( !rs.next() )
                throw new BadRequestException( "artist not found", 404 );
            
            return new Artist.Builder()
                .id(rs.getInt("id"))
                .name(rs.getString("name"))
                .dateAdded(rs.getDate("date_added"))
                .trackCount(rs.getInt("trackCount"))
                .playCount(rs.getInt("playCount"))
                .build();
        
        }
        
        finally {
            Utils.close( rs );
            Utils.close( st );
        }

    }
    
}