mediacatalogbean.java

Sun公司Dream项目

/**
 *
 * @version: 1.0
 * @date: Oct 01, 2002
 *
 */
package com.sun.sjc.idtv.vod.server.mediacatalog;

import java.sql.*;
import java.util.*;
import javax.sql.*;
import javax.ejb.*;
import javax.naming.*;
import com.sun.sjc.idtv.vod.shared.data.*;

/**
 * Implementation bean for the <code>MediaCatalog</code> interface, <code>MediaCatalog</code>
 * defines all possible business methods for the bean.
 *
 * @see MediaCatalog
 * @see MediaCatalogHome
 */
public class MediaCatalogBean implements SessionBean {

    private  javax.ejb.SessionContext m_ctx = null; 
    public static final int QUERYTIMEOUT = 60;
    private DataSource dataSource;

    /**
     * Sets the session context. Required by EJB spec.
     * @param ctx A SessionContext object.
     */
    public void setSessionContext(javax.ejb.SessionContext ctx) { 
        m_ctx = ctx; 
	try {
	    InitialContext ic = new InitialContext();
	    //dataSource = (DataSource) ic.lookup("java:comp/env/jdbc/vod");
	    dataSource = (DataSource) ic.lookup("jdbc/sample");
	} catch (Exception ex) {
	    throw new EJBException("Unable to connect to database. " +
				   ex.getMessage());
	}
    } 

    /**
     * Creates a bean. Required by EJB spec.
     * @exception throws CreateException.
     */
    public void ejbCreate() throws java.rmi.RemoteException, javax.ejb.CreateException { 
    } 

    /**
     * Removes the bean. Required by EJB spec.
     */
    public void ejbRemove() { 
    } 

    /**
     * Loads the state of the bean from secondary storage. Required by EJB spec.
     */
    public void ejbActivate() { 
    } 

    /**
     * Serializes the state of the bean to secondary storage. Required by EJB spec.
     */
    public void ejbPassivate() { 
    } 

    /**
     * Required by EJB spec.
     */
    public void MediaCatalog() { 
    } 


    /**
     * Fetch the list of movies for a given category.
     * @param categoryid the category.
     * @return array of movies
     * @exception RemoteException
     */
    public Movie[] getMovieList(int categoryid) throws java.rmi.RemoteException, SQLException {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Vector v = null;
        try {
            // get db connection from pool
            conn = dataSource.getConnection();

            // get movie list
            stmt = conn.prepareStatement("SELECT id, lysisid, fulltitle, origtitle, shorttitle, shortdescr, longdescr, version, posterurl, cid FROM moviecatalog WHERE (moviecatalog.genre = ?) ORDER BY moviecatalog.shorttitle");

            stmt.setQueryTimeout(QUERYTIMEOUT);
            stmt.setInt(1, categoryid);
            rs = stmt.executeQuery();

            v = new Vector();
            while (rs.next()) {
	            Movie m = new Movie();
	            m.id = rs.getLong(1);
	            m.externalid = rs.getString(2);
	            m.fulltitle = rs.getString(3);
	            m.origtitle = rs.getString(4);
	            m.shorttitle = rs.getString(5);
	            m.shortdescr = rs.getString(6);
	            m.longdescr = rs.getString(7);
	            m.version = rs.getInt(8);
	            m.posterurl = rs.getString(9);
                    m.CID = rs.getString(10);

	            v.addElement(m);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            stmt.close();
            conn.close();
        }
        Movie[] movies = new Movie[v.size()];
        for (int i=0; i<v.size(); i++) {
	        movies[i] = (Movie) v.elementAt(i);
        }
        return movies;
    } 
    public Movie[] getAllMoviesList() throws java.rmi.RemoteException, SQLException {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Vector v = null;
        try {
            // get db connection from pool
            conn = dataSource.getConnection();

            // get movie list
            stmt = conn.prepareStatement("SELECT id, fulltitle, cid FROM moviecatalog");
            stmt.setQueryTimeout(QUERYTIMEOUT);
                 rs = stmt.executeQuery();

            v = new Vector();
            while (rs.next()) {
	            Movie m = new Movie();
	            m.id = rs.getLong(1);
	            m.fulltitle = rs.getString(2);
                    m.CID = rs.getString(3);
	            v.addElement(m);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            stmt.close();
            conn.close();
        }
        Movie[] movies = new Movie[v.size()];
        for (int i=0; i<v.size(); i++) {
	        movies[i] = (Movie) v.elementAt(i);
        }
        return movies;
    } 

    public String getMovieServerIp(Movie movie) throws java.rmi.RemoteException, SQLException {
        Connection conn = dataSource.getConnection();
        PreparedStatement stmt = conn.prepareStatement("SELECT serverip FROM movieurls WHERE id = ?");
        stmt.setQueryTimeout(QUERYTIMEOUT);
        stmt.setLong(1, movie.id);
        ResultSet rs = stmt.executeQuery();
        String serverip = null;
        if(rs.next()) {
            serverip = rs.getString(1);
        }
        rs.close();
        stmt.close();
        conn.close();
        return serverip;
    }
   
    public long getMovieId(String movieName) throws java.rmi.RemoteException, SQLException {
        Connection conn = dataSource.getConnection();
        PreparedStatement stmt = conn.prepareStatement("SELECT id FROM moviecatalog WHERE movieurl = ?");
        stmt.setQueryTimeout(QUERYTIMEOUT);
        stmt.setString(1, "/"+movieName);
        ResultSet rs = stmt.executeQuery();
        long movieId = 0;
        if(rs.next()) {
            movieId = rs.getLong(1);
        }
        rs.close();
        stmt.close();
        conn.close();
        return movieId;
    }
    
    public String getMovieServerURL(Movie movie) throws java.rmi.RemoteException, SQLException {
        Connection conn = dataSource.getConnection();
        PreparedStatement stmt = conn.prepareStatement("SELECT  serverip,movieurls.port FROM movieurls WHERE (movieurls.id = ?)");
        stmt.setQueryTimeout(QUERYTIMEOUT);
        stmt.setLong(1, movie.id);
        ResultSet rs = stmt.executeQuery();
        String serverip = null;
    
        long port = 0;
       
        if(rs.next()) {
            serverip = rs.getString(1);
            port = rs.getLong(2);
           
            
        }
        rs.close();
        stmt.close();
        conn.close();
        return "rtsp://"+serverip+":"+port+movie.movieurl+"";
    }

    /**
     * Fetch a movie's metadata.
     * @param movieid the movie id.
     * @return movie
     * @exception RemoteException
     */
    public Movie getMovieMetadata(long movieid) throws java.rmi.RemoteException, SQLException {

       // get db connection from pool
       Connection conn = dataSource.getConnection();

       // get movie
       PreparedStatement stmt = conn.prepareStatement("SELECT * FROM moviecatalog WHERE (moviecatalog.id = ?)");

       stmt.setQueryTimeout(QUERYTIMEOUT);
       stmt.setLong(1, movieid);
       ResultSet rs = stmt.executeQuery();

       Movie m = new Movie();
       if (rs.next()) {
	   m.id = rs.getLong(1);
	   m.externalid = rs.getString(2);
	   m.fulltitle = rs.getString(3);
	   m.origtitle = rs.getString(4);
	   m.shorttitle = rs.getString(5);
	   m.shortdescr = rs.getString(6);
	   m.longdescr = rs.getString(7);
	   m.version = rs.getInt(8);
	   m.genreid = rs.getInt(9);
	   m.prodcompany = rs.getString(10);
	   m.proddate = new java.util.Date(rs.getDate(11).getTime());
	   m.releasedate = rs.getString(12);
	   m.country = rs.getString(13);
	   m.screenformat = rs.getInt(14);
	   m.showing = rs.getString(15);
	   m.boxofficeratingid = rs.getInt(16);
	   m.parentratingid = rs.getInt(17);
	   m.duration = rs.getInt(18);
	   m.color = rs.getBoolean(19);
	   m.stereo = rs.getBoolean(20);
	   m.surround = rs.getBoolean(21);
	   m.dolby = rs.getBoolean(22);
	   m.dvbcontent = rs.getString(23);
	   m.startdate = new java.util.Date(rs.getDate(24).getTime());
	   m.enddate = new java.util.Date(rs.getDate(25).getTime());
	   m.stdprice = rs.getFloat(26);
	   m.minprice = rs.getFloat(27);
	   m.movieurl = rs.getString(28);
	   m.posterurl = rs.getString(29);
           m.CAS = rs.getBoolean(30);
           m.CID = rs.getString(32);

	   rs.close();
	   stmt.close();
	   conn.close();

	   // fetch genre name and rating names
	   m.genrename = getGenre(m.genreid);
	   m.boxofficerating = getRating(m.boxofficeratingid);
	   m.parentrating = getRating(m.parentratingid);

	   // fetch movie details
	   m = getMovieDetails(m);

       } else {
	   m = null;
       }

       return m;
    } 

    /**
     * Fetch a movie's genre name.
     * @param genreid the genre id.
     * @return genre name
     * @exception RemoteException
     */
    private String getGenre(int genreid) throws java.rmi.RemoteException, SQLException {

       // get db connection from pool
       Connection conn = dataSource.getConnection();

       // get movie
       PreparedStatement stmt = conn.prepareStatement("SELECT genre FROM moviegenres WHERE (moviegenres.id = ?)");

       stmt.setQueryTimeout(QUERYTIMEOUT);
       stmt.setInt(1, genreid);
       ResultSet rs = stmt.executeQuery();

       String genre = "";
       if (rs.next()) {
	   genre = rs.getString(1);
       }
       rs.close();
       stmt.close();
       conn.close();
       return genre;
    }

    /**
     * Fetch a movie's rating name.
     * @param ratingid the rating id.
     * @return rating name
     * @exception RemoteException
     */
    private String getRating(int ratingid) throws java.rmi.RemoteException, SQLException {

       // get db connection from pool
       Connection conn = dataSource.getConnection();

       // get movie
       PreparedStatement stmt = conn.prepareStatement("SELECT name FROM ratings WHERE (ratings.id = ?)");

       stmt.setQueryTimeout(QUERYTIMEOUT);
       stmt.setInt(1, ratingid);
       ResultSet rs = stmt.executeQuery();

       String rating = "";
       if (rs.next()) {
	   rating = rs.getString(1);
       }
       rs.close();
       stmt.close();
       conn.close();
       return rating;
    }

    /**
     * Fetch a movie's details.
     * @param movie the movie.
     * @return movie object populated with repetitive details
     * @exception RemoteException
     */
    private Movie getMovieDetails(Movie movie) throws java.rmi.RemoteException, SQLException {

       // get db connection from pool
       Connection conn = dataSource.getConnection();

       // get movie
       PreparedStatement stmt = conn.prepareStatement("SELECT detailtype, detail FROM moviedetail WHERE (moviedetail.movieid = ?) ORDER BY detailtype");

       stmt.setQueryTimeout(QUERYTIMEOUT);
       stmt.setLong(1, movie.id);
       ResultSet rs = stmt.executeQuery();

       ArrayList actors = new ArrayList();
       ArrayList producers = new ArrayList();
       ArrayList directors = new ArrayList();
       ArrayList composers = new ArrayList();
       ArrayList imageurls = new ArrayList();
       ArrayList trailerurls = new ArrayList();

       while (rs.next()) {
	   int type = rs.getInt(1);
	   switch (type) {
	   case MovieDetail.DETAIL_ACTOR: actors.add(rs.getString(2)); break;
	   case MovieDetail.DETAIL_PRODUCER: producers.add(rs.getString(2)); break;
	   case MovieDetail.DETAIL_DIRECTOR: directors.add(rs.getString(2)); break;
	   case MovieDetail.DETAIL_COMPOSER: composers.add(rs.getString(2)); break;
	   case MovieDetail.DETAIL_IMAGEURL: imageurls.add(rs.getString(2)); break;
	   case MovieDetail.DETAIL_TRAILERURL: trailerurls.add(rs.getString(2)); break;
	   }
       }
       movie.actors = (String[]) actors.toArray(new String[0]);
       movie.producers = (String[]) producers.toArray(new String[0]);
       movie.directors = (String[]) directors.toArray(new String[0]);
       movie.composers = (String[]) composers.toArray(new String[0]);
       movie.imageurls = (String[]) imageurls.toArray(new String[0]);
       movie.trailerurls = (String[]) trailerurls.toArray(new String[0]);
       
       rs.close();
       stmt.close();
       conn.close();
       return movie;
    }

    /**    
     * Fetch general information about catalog for splash screen.
     * @return array of strings containing useful info
     * @exception RemoteException
     */
    public String[] getSplashInfo() throws java.rmi.RemoteException, SQLException {

	 MovieCategory[] cat = getCategoryContentCount();
	 int totalmovies = 0;
	 for (int i=0; i<cat.length; i++) {
	     totalmovies += cat[i].count;
	 }
	 String[] info = new String[5];
	 info[0] = "Welcome to Home Movies\n";
	 info[1] = "* " + cat.length + " Movie categories";
	 info[2] = "* " + totalmovies + " Movies available";
	 info[3] = "* " + totalmovies + " New titles this week";
	 info[4] = "All movies available for 24h rental";

	 return info;
    }

    /**
     * Count number of assets per category.
     * @return array of category objects, with count updated
     * @exception RemoteException
     */
    public MovieCategory[] getCategoryContentCount() throws java.rmi.RemoteException, SQLException {

	// get db connection from pool
	Connection conn = dataSource.getConnection();
	    
	// get counts
	PreparedStatement stmt = conn.prepareStatement("SELECT moviegenres.id, moviegenres.genre, COUNT(moviecatalog.genre) FROM moviegenres, moviecatalog WHERE (moviegenres.id = moviecatalog.genre) GROUP BY moviecatalog.genre, moviegenres.id, moviegenres.genre");
	
	stmt.setQueryTimeout(QUERYTIMEOUT);
	ResultSet rs = stmt.executeQuery();
	Vector v = new Vector();
	while (rs.next()) {
	    MovieCategory r = new MovieCategory();
	    r.genreid = rs.getInt(1);
	    r.genrename = rs.getString(2);
	    r.count = rs.getInt(3);
	    v.addElement(r);
	}
	rs.close();
	stmt.close();
	MovieCategory[] cat = new MovieCategory[v.size()];
	for (int i=0; i<v.size(); i++) {
	    cat[i] = (MovieCategory) v.elementAt(i);
	}
	conn.close();
	return cat;
    }
}