/*
 * This program lists the movies in which a given actor
 * has appeared, grouped by year  The first name and last name of the
 * actor are supplied as command-line arguments.  This
 * version of the program uses a PreparedStatement.
 */

// You need to import the java.sql package to use JDBCimport java.sql.*;
import java.sql.*;

public class Movie4 {
  public static void main(String[] args) {
    if(args.length!=1){
      System.out.println("Usage: java Movie3 <actor>");
      System.exit(1);
    }
    String aname = args[0]; // actor name
    
    // Connect to the database
    // You can put a database name before the ? in the connection URL.
    Connection conn = null;
    String url = "jdbc:mysql://sql.cs.oberlin.edu/?";
    String parms = "user=alice&password=tiger&useSSL=false";
    
    try {
      conn = DriverManager.getConnection(url+parms);
	
      // Create a PreparedStatement
      PreparedStatement stmt =
	conn.prepareStatement("select title,year,role from imdb.productions"+
	  " natural join imdb.principals natural join imdb.persons"+
	  " where name = ? and type = 'movie' and (category='actor' "+
          " or category='actress') order by year");
      stmt.setString(1,aname);

      // Select data from the MOVIE table
      ResultSet rset = stmt.executeQuery();

      // Iterate through the result and print the movie titles
      int ct = 0;
      String oldyear = "1000";
      while (rset.next () && ct<100){
	  String year = rset.getString("year");
	  if(year==null)
	      year = "YEAR UNKNOWN";
	  if(!oldyear.equals(year)){
	      System.out.println(year+":");
	      oldyear = year;
	  }
	  System.out.printf("\t%-40s  %s\n",rset.getString("title"),rset.getString("role"));
	++ct;
      }

      // close the ResultSet
      if(rset != null)
	rset.close();
      // close the connection
      if (conn != null)
	conn.close();
    }
    catch(SQLException e) {
      e.printStackTrace();
    }
    catch(Exception ee){
      ee.printStackTrace();
    }
  }
}