albumbean.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 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 AlbumBean
{
	private DataSource dataSource = null;
	Log log = LogFactory.getLog(AlbumBean.class);	// commons logging reference
	
	/**
	 * Constructor
	 * @param dataSource
	 */
	public AlbumBean(DataSource dataSource)
	{
		this.dataSource = dataSource;
	}
	
	/**
	 * Deletes an album from the database
	 * @param id
	 * @throws ModuleException
	 */
	public void deleteAlbum(int id) throws ModuleException
	{
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sQuery = "";
		AlbumDTO album = null;
		try
		{
			con = dataSource.getConnection();
			stmt = con.createStatement();
			sQuery = "DELETE FROM albums WHERE id=" + id;
			int result = stmt.executeUpdate(sQuery);
		}
		catch (SQLException se)
		{
			log.error("Error in deleting album.");
			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;
			}
		}
	}
	
	/**
	 * Searches for an album in the database
	 * @param id
	 * @return AlbumDTO
	 * @throws ModuleException
	 */
	public AlbumDTO findAlbum(int id) throws ModuleException
	{
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sQuery = "";
		AlbumDTO album = null;
		try
		{
			con = dataSource.getConnection();
			stmt = con.createStatement();
			sQuery = "SELECT * FROM albums WHERE id=" + id;
			rs = stmt.executeQuery(sQuery);
			if(rs.next())
			{
				album = new AlbumDTO();
				album.setAlbum(rs.getString("album"));
				album.setArtist(rs.getString("artist"));
				album.setDescription(rs.getString("description"));
				album.setId(rs.getInt("id"));
				album.setUserid(rs.getInt("userid"));
				album.setYear(rs.getString("year"));				
				album.setType(rs.getString("type"));				
				album.setCategory(rs.getString("category"));				
			}
		}
		catch (SQLException se)
		{
			log.error("Error in finding album.");
			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 album;
	}
	
	/**
	 * Saves an album into the database.
	 * @param album
	 * @throws ModuleException
	 */
	public void saveAlbum(AlbumDTO album) throws ModuleException
	{
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sQuery = "";
		try
		{
			con = dataSource.getConnection();
			stmt = con.createStatement();
			sQuery = "INSERT INTO albums (album,artist,year,type,category,description,userid,created)";
			sQuery += " values('" + filter(album.getAlbum()) + "','";
			sQuery += filter(album.getArtist()) + "','";
			sQuery += album.getYear() + "','";
			sQuery += album.getType() + "','";
			sQuery += album.getCategory() + "','";
			sQuery += filter(album.getDescription()) + "',";
			sQuery += album.getUserid() +",";
			sQuery += "now())";
			int result = stmt.executeUpdate(sQuery);
		}
		catch (SQLException se)
		{
			log.error("Error in creating album.");
			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;
			}
		}
	}
	
	/**
	 * Updates an existing album in the database
	 * @param album
	 * @throws ModuleException
	 */
	public void updateAlbum(AlbumDTO album) throws ModuleException
	{
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sQuery = "";
		try
		{
			con = dataSource.getConnection();
			stmt = con.createStatement();
			sQuery = "UPDATE albums ";
			sQuery += "SET album='" + filter(album.getAlbum()) + "',";
			sQuery += "artist='" + filter(album.getArtist()) + "',";
			sQuery += "year='" + album.getYear() + "',";
			sQuery += "type='" + album.getType() + "',";
			sQuery += "category='" + album.getCategory() + "',";
			sQuery += "description='" + filter(album.getDescription()) + "',";
			sQuery += "userid=" + album.getUserid() ;
			sQuery += " WHERE id =" +album.getId();
			int result = stmt.executeUpdate(sQuery);
		}
		catch (SQLException se)
		{
			log.error("Error in updating album.");
			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;
			}
		}
	}
	
	/**
	 * Filters all single quotes in a String, replacing with double quotes
	 * @param value
	 * @return String
	 */
	private String filter(String value)
	{
		return value.replaceAll("'","''");	// replace 1 single quote with 2 single quotes
	}
}