// MusicBean.java
import java.util.*;
import javax.ejb.*;
import javax.naming.*;
import java.rmi.RemoteException;

public class MusicBean implements SessionBean {

  // class-wide MusicDAO object to access the data
  // MusicDAO object is instantiated in ejbCreate()
  // and provides implementation of the MusicDAO interface
  // for the particular database we're using
  private MusicDAO dao;

  // Business methods

  public ArrayList getMusicList()
  {
    try {
      // Encapsulate database calls in MusicDAO
      return(dao.dbLoadMusicList());
    } catch (MusicDAOSysException ex) {
      throw new EJBException("getMusicList: " +
               ex.getMessage());
    }
  }

  public ArrayList getTrackList(RecordingVO rec)
             throws NoTrackListException {
    ArrayList trackList;
    try {
      // Encapsulate database calls in MusicDAO
      trackList = dao.dbLoadTrackList(rec);

    } catch (MusicDAOSysException ex) {
      throw new EJBException("getTrackList: " +
          ex.getMessage());
    }

    if (trackList.size() == 0) {
      throw new NoTrackListException(
          "No Track List found for RecordingID " +
                   rec.getRecordID());
    }
    return trackList;
  }

  // EJB methods

  public void ejbCreate() {
    try {
      // The MusicDAOFactory class returns
      // an implementation of the MusicDAO interface
      dao = MusicDAOFactory.getDAO();
    }

    catch (MusicDAOSysException ex) {
      throw new EJBException(ex.getMessage());
    }
  }
  public MusicBean() {}
  public void ejbRemove() {}
  public void ejbActivate() {}
  public void ejbPassivate() {}
  public void setSessionContext(SessionContext sc) {}

} // MusicBean

// MusicDAOFactory.java
import javax.naming.NamingException;
import javax.naming.InitialContext;
import MusicDAOSysException;

public class MusicDAOFactory {

  // Instantiate a subclass that implements the
  // MusicDAO interface.
  // Obtain subclass name from the deployment descriptor

  public static MusicDAO getDAO()
             throws MusicDAOSysException {

  MusicDAO musicDAO = null;
  String musicDAOClass = "java:comp/env/MusicDAOClass";
  String className = null;

    try {
      InitialContext ic = new InitialContext();

      // Lookup value of environment entry
      // for DAO classname
      // Value is set in deployment descriptor
      className = (String) ic.lookup(musicDAOClass);

      // Instantiate class, which will provide
      // implementation for the MusicDAO interface
      musicDAO = (MusicDAO) Class.forName(
                 className).newInstance();

    } catch (Exception ex) {
      throw new MusicDAOSysException(
           "MusicDAOFactory.getDAO: " +
           "NamingException for <" + className +
"> DAO class : 
" + ex.getMessage());
    }
    return musicDAO;
  }
}

// MusicDAO.java
import java.util.ArrayList;
import MusicDAOSysException;
import RecordingVO;

// Interface for Music Data Access Object

public interface MusicDAO {
  public ArrayList dbLoadMusicList()
      throws MusicDAOSysException;
  public ArrayList dbLoadTrackList(RecordingVO rec)
      throws MusicDAOSysException;
}

// MusicDAOSysException.java
// System Exception
import java.lang.RuntimeException;

public class MusicDAOSysException extends RuntimeException {
// MusicDAOSysException extends standard RuntimeException.
// Thrown by MusicDAO subclasses when there is an
// unrecoverable system error (typically SQLException)

  public MusicDAOSysException(String msg) {
    super(msg);
  }
  public MusicDAOSysException() {
    super();
  }
}

// MusicDAOCloudscape.java
import java.util.ArrayList;
import javax.ejb.*;
import javax.naming.*;
import javax.sql.DataSource;
import java.sql.*;
import MusicDAOSysException;

// Implements MusicDAO for Cloudscape database.
public class MusicDAOCloudscape implements MusicDAO {

  private Connection con = null;
  private DataSource datasource = null;

  public MusicDAOCloudscape()
             throws MusicDAOSysException {
    String dbName = "java:comp/env/jdbc/MusicDB";
    try {
      InitialContext ic = new InitialContext();
      datasource = (DataSource) ic.lookup(dbName);
    } catch (NamingException ex) {
      throw new MusicDAOSysException(
          "Exception during datasource lookup: " +
            dbName + ":
" + ex.getMessage());
    }
  }

  // Obtain a JDBC Database connection
  private void getConnection() throws MusicDAOSysException
  {
    try {
      con = datasource.getConnection();

    } catch (SQLException ex) {
      throw new MusicDAOSysException(
        "SQLException during DB Connection:
" +
        ex.getMessage());
    }
  }
  // Release JDBC Database connection
  private void disConnect() throws MusicDAOSysException
  {
    try {
      if (con != null) con.close();
    } catch (SQLException ex) {
      throw new MusicDAOSysException(
        "SQLException during DB close connection:
" +
        ex.getMessage());
    }
  }

  private void closeStatement(PreparedStatement s)
            throws MusicDAOSysException {
    try {
      if (s != null) s.close();
    } catch (SQLException ex) {
      throw new MusicDAOSysException(
        "SQL Exception during statement close
"
        + ex.getMessage());
    }
  }

  private void closeResultSet(ResultSet rs)
            throws MusicDAOSysException {
    try {
      if (rs != null) rs.close();
    } catch (SQLException ex) {
      throw new MusicDAOSysException(
        "SQL Exception during result set close
"
        + ex.getMessage());
    }
  }

  public ArrayList dbLoadMusicList()
           throws MusicDAOSysException {

    ArrayList mList = new ArrayList();

    // The following query searches 3 tables in
    // the Music Collection database to create a composite
    // RecordingVO object.
    // We select all fields in the Recordings table,
    // the RecordingArtistName field in the
    // Recording Artists table, and the MusicCategory field
    // from the Music Categories table.
    // The Where clause matches the correct records from
    // the Recording Artists and Music Categories tables.

    String selectQuery = "Select Recordings.*, " +
      ""Recording Artists".RecordingArtistName, " +
      ""Music Categories".MusicCategory " +
      "From Recordings, "Recording Artists", " +
      ""Music Categories" " +
      "Where Recordings.RecordingArtistID = " +
      ""Recording Artists".RecordingArtistID and " +
      "Recordings.MusicCategoryID = " +
      ""Music Categories".MusicCategoryID ";

    PreparedStatement musicStmt = null;
    ResultSet rs = null;
    try {
      // Obtain a database connection
      getConnection();
      musicStmt = con.prepareStatement(selectQuery);

      // Execute the query; results are in ResultSet rs
      rs = musicStmt.executeQuery();

      // Loop through ResultSet and create RecordingVO
      // object for each row

      while (rs.next())
      {
        // Create RecordingVO object and add to mList
        // Use getInt() and getString() to pull data from
        // ResultSet rs.

        // The parameter numbers match the order of the
        // field names in the query statement.
        mList.add(new RecordingVO(
          rs.getInt(1),       // RecordingID
          rs.getString(2),    // RecordingTitle
          rs.getString(9),     // RecordingArtistName
          rs.getString(10),    // MusicCategory
          rs.getString(5),     // RecordingLabel
          rs.getInt(7)));       // NumberofTracks
      }

    } catch(SQLException ex) {
      throw new MusicDAOSysException(
        "SQLException reading recording data:
" +
        ex.getMessage());

    } finally {
      closeResultSet(rs);
      closeStatement(musicStmt);

      // Release the database connection
      disConnect();
    }
    return mList;
  }

  public ArrayList dbLoadTrackList(RecordingVO rec)
         throws MusicDAOSysException {

    ArrayList tList = new ArrayList();

    // Select all records from Tracks table
    // where the RecordingID is the same as the
    // In parameter
    // Order the records by TrackNumber field

    String selectQuery = "Select * From Tracks " +
      "Where RecordingID = ? Order By TrackNumber";

    PreparedStatement trackStmt = null;
    ResultSet rs = null;
    try {
      // Obtain a database connection
      getConnection();

      trackStmt = con.prepareStatement(selectQuery);

      // Set In parameter to RecordingID in
      // RecordingVO argument
      trackStmt.setInt(1, rec.getRecordID());

      // Execute query; return in ResultSet rs
      rs = trackStmt.executeQuery();

      while (rs.next())
      {
        // Loop through ResultSet and create TrackVO
        // Add to tList ArrayList
        tList.add(new TrackVO(
        rs.getInt(2),            // trackNumber
        rs.getString(3),         // title
        rs.getString(4)));       // trackLength
      }

    } catch (SQLException ex) {
      throw new MusicDAOSysException(
        "SQLException while reading track data:
"
        + ex.getMessage());

    } finally {
      closeResultSet(rs);
      closeStatement(trackStmt);
      // Release the database connection
      disConnect();
    }
    return tList;
  }
} // MusicDAOCloudscape