ByLetterer.java

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

import com.pugh.sockso.Utils;
import com.pugh.sockso.db.Database;
import com.pugh.sockso.music.Artist;
import com.pugh.sockso.templates.web.browse.TByLetter;
import com.pugh.sockso.web.Request;
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 artists by letter
 * 
 */

public class ByLetterer extends BaseAction {
    
    private static final Logger log = Logger.getLogger( ByLetterer.class );

    /**
     *  browses artists by their first letter
     * 
     *  @param req the request object
     *  @param res the response object
     * 
     *  @throws IOException
     *  @throws SQLException
     * 
     */
    
    @Override
    public void handleRequest() throws IOException, SQLException {
        
        final Request req = getRequest();
        
        String letter = req.getUrlParam(2);
        if ( !letter.equals("") )
            letter = letter.substring(0,1).toLowerCase();

        showByLetter( letter, getArtistsByLetter(letter) );
        
    }
    
    /**
     *  shows the page to browse artists by letter
     * 
     *  @param letter
     *  @param artists
     * 
     *  @throws java.io.IOException
     * 
     */
    
    protected void showByLetter( final String letter, final List<Artist> artists ) throws IOException, SQLException {

        final TByLetter tpl = new TByLetter();

        tpl.setArtists( artists );
        tpl.setLetter( letter );

        getResponse().showHtml( tpl );
        
    }

    /**
     *  returns the artists that start with a particular letter
     * 
     *  @param letter
     * 
     *  @return
     * 
     *  @throws java.sql.SQLException
     * 
     */
    
    protected List<Artist> getArtistsByLetter( final String letter ) throws SQLException {
        
        ResultSet rs = null;
        PreparedStatement st = null;
                
        try {
            
            final Database db = getDatabase();

            // Get the count of the albums based on track album_id, NOT the count of
            // album rows for artist, as the artist may appear on albums for which
            // they are NOT the album artist
            final String sql = "" +
                   " select id, name, count(*) as albumCount " +
                   " from (select ar.id as id, ar.name as name, count(tr.album_id) " +
                         " from artists ar " +
                             " left outer join tracks tr " +
                             " on tr.artist_id = ar.id " +
                         " where " +
                         ( letter.equals("")
                                   // doesn't start with a-z
                                   // @TODO ascii() not sqlite compatible
                                   ? " ascii(lower(ar.name)) < 96 " +
                                         " or ascii(lower(ar.name)) > 123 "
                                   // starts with a particular letter
                                   : " ar.browse_name like ? ") +
                         " group by tr.album_id, ar.id, ar.name) " +
                   " group by name, id " +
                   " order by name asc ";

            st = db.prepare( sql );

            if ( !letter.equals("") ) {
                st.setString( 1, letter + "%" );
            }

            rs = st.executeQuery();

            final List<Artist> artists = new ArrayList<Artist>();
            while ( rs.next() ) {
                artists.add( new Artist.Builder()
                        .id(rs.getInt("id"))
                        .name(rs.getString("name"))
                        .albumCount(rs.getInt("albumCount"))
                        .build()
                );
            }

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