View Javadoc
   // $Header: /cvsroot/daoexamples/daoexamples/src/java/daoexamples/movie/MovieUtil.java,v 1.7 2003/09/27 17:27:11 sullis Exp $
    
   /*
    * 
    *
    * 
    * 
    */
    
  package daoexamples.movie;
  
  import daoexamples.exception.*;
  import java.sql.*;
  import java.util.Collection;
  import javax.sql.*;
  import javax.naming.Context;
  import javax.naming.InitialContext;
  import javax.naming.NamingException;
  import javax.transaction.UserTransaction;
  import org.apache.commons.logging.*;
  
  /***
   *
   * utilities for the Movie DAO package
   *  
   * @author Sean C. Sullivan
   * 
   */
  public final class MovieUtil
  {
  	static final private Log log = LogFactory.getLog(MovieUtil.class);
  
  	static public String getUniqueMovieId(final Connection conn)
  		throws java.sql.SQLException
  	{
  		
  		if (null == conn)
  		{
  			throw new NullPointerException("conn parameter");
  		}
  
  		if (conn.isClosed())
  		{
  			throw new IllegalArgumentException(
  					"connection is closed");
  		}
  
  		String id = null;
  		
  		Statement stmtSelect = null;
  		ResultSet rs = null;
  		
  		StringBuffer sbSelect = new StringBuffer();
  		
  		sbSelect.append("SELECT ");
  		sbSelect.append(MovieConstants.MOVIE_ID_SEQUENCE_NAME);
  		sbSelect.append(".nextval ");
  		sbSelect.append(" FROM DUAL ");
  		
  		try
  		{
  			stmtSelect = conn.createStatement();
  			rs = stmtSelect.executeQuery(sbSelect.toString());
  			rs.next();
  			id = rs.getString("nextval");
  		}
  		finally
  		{
  			MovieUtil.closeStatement(stmtSelect);
  			MovieUtil.closeResultSet(rs);
  		}
  	
  		return id;
  	}
  	
  	/***
  	 *  
  	 * @param conn must be non-null
  	 * 
  	 * the caller is responsible for committing the transaction
  	 * 
  	 * @throws SQLException
  	 * 
  	 */
  	static public void setupMovieTable()
  		throws SQLException
  	{
  		Statement stmtCreateTable = null;
  		Statement stmtDropTable = null;
  		Statement stmtCreateSeq = null;
  		Statement stmtDropSeq = null;
  		
  		Connection conn = null;
  		
  		try
  		{
  			//
  			// 
  			// This method uses a non-XA JDBC connection because
 			// "DROP TABLE" and "CREATE TABLE" will only work
 			// with a non-XA JDBC connection.
 			//
 			conn = MovieUtil.getNonXADBConnection();
 			conn.setAutoCommit(true);
 
 			log.debug(
 				conn.getMetaData().getDriverName()
 				+ " - "
 				+ conn.getMetaData().getDriverVersion());
 			
 			StringBuffer sbDropTable = new StringBuffer();
 			sbDropTable.append("DROP TABLE ");
 			sbDropTable.append(MovieConstants.MOVIE_TABLE_NAME);
 		
 			stmtDropTable = conn.createStatement();
 		
 			try
 			{
 				stmtDropTable.execute(sbDropTable.toString());
 			}
 			catch (SQLException ignored)
 			{
 				// The 'DROP TABLE' statement may throw
 				// an exception. We ignore it.
 			} 
 		
 			StringBuffer sbDropSeq = new StringBuffer();
 			sbDropSeq.append("DROP SEQUENCE ");
 			sbDropSeq.append(MovieConstants.MOVIE_ID_SEQUENCE_NAME);
 		
 			stmtDropSeq = conn.createStatement();
 		
 			try
 			{
 				stmtDropSeq.execute(sbDropSeq.toString());
 			}
 			catch (SQLException ignored)
 			{
 				// The 'DROP SEQUENCE' statement may throw
 				// an exception. We ignore it.
 			} 
 
 			StringBuffer sbCreateSeq = new StringBuffer();
 			sbCreateSeq.append("CREATE SEQUENCE ");
 			sbCreateSeq.append(MovieConstants.MOVIE_ID_SEQUENCE_NAME);
 			sbCreateSeq.append(" START WITH 1 INCREMENT BY 1 NOMAXVALUE ORDER NOCACHE");
 		
 			stmtCreateSeq = conn.createStatement();
 			stmtCreateSeq.execute(sbCreateSeq.toString());
 		
 			StringBuffer sbCreateTable = new StringBuffer();
 			sbCreateTable.append("CREATE TABLE ");
 			sbCreateTable.append(MovieConstants.MOVIE_TABLE_NAME);
 			sbCreateTable.append(" ( ");
 			sbCreateTable.append(" movie_id NUMBER(12), ");
 			sbCreateTable.append(" rating VARCHAR2(10), ");
 			sbCreateTable.append(" year VARCHAR2(5), ");
 			sbCreateTable.append(" title VARCHAR2(60) ");
 			sbCreateTable.append(" ) ");
 		
 			stmtCreateTable = conn.createStatement();
 		
 			log.debug("CREATE TABLE statement: "
 						+ sbCreateTable.toString());
 
 			stmtCreateTable.execute(sbCreateTable.toString());
 			
 			log.debug("Statement executed: "
 								+ sbCreateTable.toString());
 								
 		}
 		finally
 		{
 			MovieUtil.closeStatement(stmtDropTable);
 			MovieUtil.closeStatement(stmtDropSeq);
 			MovieUtil.closeStatement(stmtCreateTable);
 			MovieUtil.closeStatement(stmtCreateSeq);
 			MovieUtil.closeJDBCConnection(conn);
 		}
 	}
 	
 	private MovieUtil()
 	{
 		// this constructor is intentionally private
 	}
 
 	static public Connection getNonXADBConnection()
 	{
 		 Connection conn = null;
 		 
 		 DataSource ds = null;
 		 
 		 try
 		 {
 			Object obj = lookup(MovieConstants.MOVIE_NONXA_DATASOURCE_NAME);
 			ds = (DataSource) narrow(obj, DataSource.class); 
 		 	
 			conn = ds.getConnection();
 		 }
 		 catch (SQLException ex)
 		 {
 			throw new DAORuntimeException(ex);
 		 }
 		 return conn;
 	}
 
 	public static Connection getXADBConnection()
 	{
 		 Connection conn = null;
 		 
 		 DataSource ds = null;
 		 
 		 try
 		 {
 			Object obj = lookup(MovieConstants.MOVIE_XA_DATASOURCE_NAME);
 			ds = (DataSource) narrow(obj, DataSource.class); 
 		 	
 		 	conn = ds.getConnection();
 		 }
 		 catch (SQLException ex)
 		 {
 		 	throw new DAORuntimeException(ex);
 		 }
 		 
 		 
 		 return conn;
 	}
 	
 	public static void closeJDBCConnection(final Connection conn)
 	{
 		if (conn != null)
 		{
 			 try
 			 {
 				conn.close();
 			 }
 			 catch (SQLException ex)
 			 {
 				log.error(conn, ex);
 			 }
 		}
 	}
 
 	public static void closeStatement(final Statement stmt)
 	{
 		if (stmt != null)
 		{
 			 try
 			 {
 				stmt.close();
 			 }
 			 catch (SQLException ex)
 			 {
 				log.error(stmt, ex);
 			 }
 		}
 	}
 
 	public static void closeResultSet(final ResultSet rs)
 	{
 		if (rs != null)
 		{
 			 try
 			 {
 				rs.close();
 			 }
 			 catch (SQLException ex)
 			 {
 				log.error(rs, ex);
 			 }
 		}
 	}
 
 	static public UserTransaction getUserTransaction()
 	{
 		Object obj = lookup("java:comp/UserTransaction");
 		
 		UserTransaction tx = (UserTransaction) narrow(obj, UserTransaction.class);
 		
 		return tx;
 	} 
 
 	static public Object lookup(final String name)
 	{
 		Context ctx = getInitialContext();
 		
 		Object result = null;
 		
 		try
 		{
 			result = ctx.lookup(name);
 		}
 		catch (NamingException ex)
 		{
 			throw new DAORuntimeException(ex);
 		}
 		
 		return result;
 	}
 	
 	static public Context getInitialContext()
 	{
 		Context ctx = null;
 
 		try
 		{
 			ctx = new InitialContext();
 		}
 		catch (NamingException ex)
 		{
 			throw new DAORuntimeException(ex);
 		}		
 		return ctx; 
 	}
 	
 	static public Object narrow(final Object obj, final Class clazz)
 	{
 		return javax.rmi.PortableRemoteObject.narrow(obj, clazz);
 	}
 
 	/***
 	 *  
 	 * @param rs must be non-null
 	 * 
 	 * @return a Collection containing zero or more {@link Movie} objects
 	 * 
 	 */
 	static public Collection makeMovieObjectsFromResultSet(final ResultSet rs)
 		throws java.sql.SQLException
 	{
 		Collection result = new java.util.ArrayList();
 	
 		while (rs.next())
 		{
 			String id = rs.getString("movie_id"); 	
 			String rating = rs.getString("rating"); 	
 			String year = rs.getString("year"); 	
 			String title = rs.getString("title");
 			Movie m = new MovieImpl(id, rating, year, title);
 			result.add(m); 	
 		}
 		
 		return result;
 	}
 			
 }