import java.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.*;
import javax.sql.*;

public class DvdBean implements EntityBean {

    private EntityContext entityContext;
    private Connection connection;
    private String dbName = "java:comp/env/jdbc/DvdDB";
    /*bean state fields*/
    private String isbn;  //the primary key
    private String title;
    private String format;    
    
    /*methods in remote interface, access state fields*/ 
    
    public void setTitle(String newTitle) {
       this.title = newTitle;
    }
    
    public void setFormat(String newFormat) {
       this.format = newFormat;
    }    
 
    public String getTitle() {
        return title;
    }
 
    public String getFormat() {
       return format;
    }
    
    public String getAsString() {
       return("dvd = isbn: " + isbn + 
              " title: " + title + 
              " format: " + format);          
    }     
    
    
    /*methods in home interface, use table(s)  */    
    
    public String ejbCreate(String isbn, 
                            String title, 
                            String format) throws CreateException 
    {
       try 
       {
          dbInsertRow(isbn, title, format);
       } catch (Exception exception) {
         throw new EJBException(
           "ejbCreate():dbInsertRowCalled with isbn: " + isbn + 
           " title: " + title + 
           " format: " + format + 
           " exception: " + exception.getMessage());}

       this.isbn = isbn;
       this.title = title;
       this.format = format;

       return isbn;
    }

   public String ejbFindByPrimaryKey(String primaryKey) 
     throws FinderException {
      boolean result;
      
      try 
      {
         result = dbSelectByPrimaryKey(primaryKey);
      } catch (Exception exception) {
        throw new EJBException(
        "ejbFindByPrimaryKey(): after calling dbSelectByPrimaryKey " + 
        exception.getMessage());}
      
      if (result) {
         return primaryKey;
      } else {
         throw new ObjectNotFoundException (
           "ejbFindByPrimaryKey(): Row for id " + primaryKey + 
           " not found.");
      }
   }

   //differs from ejbFindByPrimaryKey in that it returns a collection, 
   //  and so can return a null set 
   public Collection ejbFindByIsbn(String primaryKey) 
     throws FinderException {
      Collection result;
      
      try {
         result = dbSelectByIsbn(primaryKey);
      } catch (Exception exception) {
        throw new EJBException(
          "ejbCheckForPrimaryKey(): after calling dbSelectByIsbn " + 
          exception.getMessage());}
      
      return result;
   } 
   
   public Collection ejbFindByTitle(String title) 
     throws FinderException {
      Collection result;
      try {
         result = dbSelectByTitle(title);
      } catch (Exception exception) {
        throw new EJBException(
          "ejbFindByTitle: after calling dbSelectByTitle() " + 
          exception.getMessage());}
      return result;
   }
   
   public Collection ejbFindByTitleWith(String title) 
     throws FinderException {
      Collection result;
      try 
      {
         result = dbSelectByTitleWith(title);
      } catch (Exception exception) {
        throw new EJBException(
          "ejbFindByTitleWith: after calling dbSelectTitleWith() " + 
          exception.getMessage());}
      return result;
   }   
   

    /*methods necessary in an entity bean */     
   
   public void ejbRemove() 
   {
      try 
      {
         dbDeleteRow(isbn);
      } catch (Exception exception) {
        throw new EJBException(
        "ejbRemove(): after calling dbDeleteRow() " + 
        exception.getMessage());}
   } 

   public void setEntityContext(EntityContext entityContext) {
      this.entityContext = entityContext;
      try {
         dbMakeConnection();
      } catch (Exception exception) {
        throw new EJBException(
          "setEntityContext(): after calling dbMakeConnection() " +
          exception.getMessage());}
   }

   public void unsetEntityContext() {
      try {
         connection.close();
      } catch (SQLException exception) {
        throw new EJBException(
          "unsetEntityContext(): closing connection" + 
          exception.getMessage());}
   }

   public void ejbActivate() {
      isbn = (String)entityContext.getPrimaryKey();
   }

   public void ejbPassivate() {

      isbn = null;
   }

   public void ejbLoad() {
      try {
         dbLoadRow();
       } catch (Exception exception) {
         throw new EJBException(
           "ejbLoad():dbLoadRow called using this.isbn: " + 
           this.isbn +
           " exception " + exception.getMessage());}
   }
   
   public void ejbStore() {
      try {
         dbStoreRow();
       } catch (Exception exception) {
         throw new EJBException("ejbStore(): " +  
         exception.getMessage());
       }
   }

   public void ejbPostCreate(String isbn, 
                             String title, 
                             String format) {
   }


   /*db methods*/

   private void dbMakeConnection() 
     throws NamingException, SQLException {
      InitialContext initialContext = new InitialContext();
      DataSource dataSource = 
        (DataSource)initialContext.lookup(dbName);
      connection =  dataSource.getConnection();
   }

   private void dbInsertRow (String isbn, 
                             String title, 
                             String format) throws SQLException {
      String insertStatement = "insert into dvd values ( ? , ? , ? )";
      PreparedStatement preparedStatement = 
        connection.prepareStatement(insertStatement);

      preparedStatement.setString(1, isbn);
      preparedStatement.setString(2, title);
      preparedStatement.setString(3, format);

      preparedStatement.executeUpdate();
      preparedStatement.close();
   }

   private void dbDeleteRow(String isbn) throws SQLException {
      String deleteStatement = "delete from dvd where isbn = ? ";
      PreparedStatement preparedStatement = 
        connection.prepareStatement(deleteStatement);

      preparedStatement.setString(1, isbn);
      preparedStatement.executeUpdate();
      preparedStatement.close();
   }

   private boolean dbSelectByPrimaryKey(String primaryKey) 
     throws SQLException {
      String selectStatement = 
        "select isbn from dvd where isbn = ? ";
      PreparedStatement preparedStatement = 
        connection.prepareStatement(selectStatement);
      preparedStatement.setString(1, primaryKey);

      ResultSet resultSet = preparedStatement.executeQuery();
      boolean result = resultSet.next();
      
      preparedStatement.close();
      return result;
   }
   
   private Collection dbSelectByIsbn(String isbnIn) 
     throws SQLException {
      String selectStatement = "select isbn from dvd where isbn = ? ";
      PreparedStatement preparedStatement = 
        connection.prepareStatement(selectStatement);
      preparedStatement.setString(1, isbnIn);
      
      ResultSet resultSet = preparedStatement.executeQuery();
      
      ArrayList arrayList = new ArrayList();
      while (resultSet.next()) {
         String isbn = resultSet.getString(1);
         arrayList.add(isbn);
      }
      
      preparedStatement.close();
      return arrayList;
   }   

   private Collection dbSelectByTitle(String title) 
     throws SQLException {
      String selectStatement = 
        "select isbn from dvd where title = ? ";
      PreparedStatement preparedStatement = 
        connection.prepareStatement(selectStatement);
      preparedStatement.setString(1, title);
      
      ResultSet resultSet = preparedStatement.executeQuery();

      ArrayList arrayList = new ArrayList();
      while (resultSet.next()) {
         String isbn = resultSet.getString(1);
         arrayList.add(isbn);
      }

      preparedStatement.close();
      return arrayList;
   }

   private Collection dbSelectByTitleWith(String title) 
     throws SQLException {
      String selectStatement = 
        "select isbn from dvd where title like ? ";
      PreparedStatement preparedStatement = 
        connection.prepareStatement(selectStatement);
      preparedStatement.setString(1, "%" + title + "%");

      ResultSet resultSet = preparedStatement.executeQuery();
      
      ArrayList arrayList = new ArrayList();
      while (resultSet.next()) {
         String isbn = resultSet.getString("isbn");
         arrayList.add(isbn);
      }

      preparedStatement.close();
      return arrayList;
   }   
   
   private void dbLoadRow() throws SQLException { 
      String selectStatement = 
        "select title, format from dvd where isbn = ? ";
      PreparedStatement preparedStatement = 
        connection.prepareStatement(selectStatement);
      preparedStatement.setString(1, this.isbn);

      ResultSet resultSet = preparedStatement.executeQuery();

      if (resultSet.next()) {
         this.title = resultSet.getString(1);
         this.format = resultSet.getString(2);
         preparedStatement.close();
      } else {
         preparedStatement.close();
         throw new NoSuchEntityException(
           "dbLoadRow(): Row for isbn " + isbn + 
           " not found in database.");
      }
   }

   private void dbStoreRow() throws SQLException {
      String updateStatement = 
        "update dvd set title =  ? , format = ? where isbn = ?";
      PreparedStatement preparedStatement = 
        connection.prepareStatement(updateStatement);

      preparedStatement.setString(1, title);
      preparedStatement.setString(2, format);
      preparedStatement.setString(3, isbn);
      int rowCount = preparedStatement.executeUpdate();
      preparedStatement.close();

      if (rowCount == 0) {
         throw new EJBException(
           "dbStoreRow(): Storing row for isbn " + isbn + " failed.");
      }
   }

}