Popularer.java

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

import com.pugh.sockso.Constants;
import com.pugh.sockso.Properties;
import com.pugh.sockso.Utils;
import com.pugh.sockso.db.Database;
import com.pugh.sockso.music.Track;
import com.pugh.sockso.templates.web.browse.TPopular;
import com.pugh.sockso.web.action.BaseAction;

import org.apache.log4j.Logger;

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

/**
 *  shows popular track information
 * 
 */

public class Popularer extends BaseAction {

    private static final Logger log = Logger.getLogger( Popularer.class  );
    
    /**
     *  shows information about popular things
     * 
     *  @param req the request object
     *  @param res the response object
     * 
     *  @throws IOException
     *  @throws SQLException
     * 
     */

    @Override
    public void handleRequest() throws IOException, SQLException {
        
        showPopularTracks( getPopularTracks() );
        
    }

    /**
     *  shows the page displaying populate tracks
     * 
     *  @param tracks
     * 
     *  @throws java.io.IOException
     * 
     */
    
    protected void showPopularTracks( final List<Track> tracks ) throws IOException, SQLException {
        
        final TPopular tpl = new TPopular();
        
        tpl.setTracks( tracks );
        
        getResponse().showHtml( tpl );

    }

    /**
     *  returns the most popular (most played) tracks.  limit set in properties
     * 
     *  @return
     * 
     *  @throws java.sql.SQLException
     * 
     */
    
    protected List<Track> getPopularTracks() throws SQLException {

        ResultSet rs = null;
        PreparedStatement st = null;
        
        try {

            final Properties p = getProperties();
            final Database db = getDatabase();
            final String sql = Track.getSelectSql() + " , count(l.id) as playCount " +
                        " from tracks t " +
                            " inner join artists ar " +
                            " on ar.id = t.artist_id " +
                            " inner join albums al " +
                            " on al.id = t.album_id " +
                            " inner join genres g " +
                            " on g.id = t.genre_id " +
                            " left outer join play_log l " +
                            " on l.track_id = t.id " +
                        " group by artistId, artistName, albumId, albumName, albumYear, trackId, " +
                            " trackName, trackPath, trackNo, genreId, genreName, dateAdded " +
                        " having count(l.id) > 0 " +
                        " order by count(l.id) desc " +
                        " limit ? ";
            st = db.prepare( sql );
            
            st.setInt( 1, (int) p.get(Constants.WWW_BROWSE_POPULAR_TRACK_COUNT,20) );
            
            rs = st.executeQuery();

            final List<Track> tracks = new ArrayList<Track>();
            while ( rs.next() ) {
                final Track track = Track.createFromResultSet( rs );
                track.setPlayCount( rs.getInt("playCount") );
                tracks.add( track );
            }
            
            return tracks;

        }
        
        finally {
            Utils.close( rs );
            Utils.close( st );
        }
            
    }
    
}