musiclistbean.java

the musiccollection struts 1 application i netbeans implementation (strut for dummies book source)

package dummies.struts.music;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts.util.ModuleException;

/**
 * @author Mike Robinson
 *
 */
public class MusicListBean
{
	private DataSource dataSource = null;
	Log log = LogFactory.getLog(LoginBean.class);	// commons logging reference
	
	/**
	 * Constructor
	 * @param dataSource
	 */
	public MusicListBean(DataSource dataSource)
	{
		this.dataSource = dataSource;
	}
	
	/**
	 * Get the collection of albums for this user
	 * @param user
	 * @return Collection of albums
	 * @throws ModuleException
	 */
	public Collection getMusic(UserDTO user) throws ModuleException
	{
		Collection albums = new ArrayList();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sQuery = "";
		try
		{
			con = dataSource.getConnection();
			stmt = con.createStatement();
			sQuery = "SELECT * FROM albums WHERE userid=" + user.getId();
			sQuery += " ORDER BY album";
			rs = stmt.executeQuery(sQuery);
			while (rs.next())
			{
				// Create new user transfer object
				AlbumDTO album = new AlbumDTO();
				album.setAlbum(rs.getString("album"));
				album.setArtist(rs.getString("artist"));
				album.setId(rs.getInt("id"));
				album.setYear(rs.getString("year"));
				
				// save the album in the collection
				albums.add(album);
			}
		}
		catch (SQLException se)
		{
			log.error("Error in retreiving albums.");
			log.error("SQL statement = " + sQuery);
			se.printStackTrace();
			ModuleException me = new ModuleException("error.db.sql");
			throw me;
		}
		
		// Be sure to always try and close ResultSet, Statement, and Connection
		// Note that because we are using a connection pool, closing the Connection
		// does Not automatically result in closing the ResultSet and Statement.
		// Therefore, we do so explicitly here as a best practice.
		finally
		{
			try
			{
				if (rs != null) rs.close();
			}
			catch (SQLException se)
			{
				log.error("Error in closing ResultSet.");
				se.printStackTrace();
				ModuleException me = new ModuleException("error.db.sql");
				throw me;
			}
			try
			{
				if (stmt != null) stmt.close();
			}
			catch (SQLException se)
			{
				log.error("Error in closing Statement.");
				se.printStackTrace();
				ModuleException me = new ModuleException("error.db.sql");
				throw me;
			}
			try
			{
				if (con != null) con.close();
			}
			catch (SQLException se)
			{
				log.error("Error in closing Connection.");
				se.printStackTrace();
				ModuleException me = new ModuleException("error.db.sql");
				throw me;
			}
		}
		return albums;
	}
}