prob 5

    /*
     * A Java program that uses the JDBC driver for SQLite
     * to extract data from our movie database.
     *
     * Before compiling this program, you must download the JAR file for the
     * SQLite JDBC Driver and add it to your classpath. See the Java-specific
     * notes in the assignment for more details.
     *
     * In addition, the database file should be in the same folder
     * as the program.
     *
     * Computer Science E-66
     */


    import java.util.*;     // needed for the Scanner class
    import java.sql.*;      // needed for the JDBC-related classes
import java.io.*;       // needed for the PrintStream class

    public class MovieToXML {

        public static void main(String[] args)
          throws ClassNotFoundException, SQLException, FileNotFoundException
        {
            Scanner console = new Scanner(System.in);

            // Connect to the database.
            System.out.print("name of database file: ");
            String db_filename = console.next();
            Class.forName("org.sqlite.JDBC");
            Connection db = DriverManager.getConnection("jdbc:sqlite:" + db_filename);

            // Create a PrintStream for the results file.
           // System.out.print("name of results file: ");
            String out_filename = "out.txt";
            PrintStream outfile = new PrintStream(out_filename);
            int numMoviesReturned = 0;


            // Create a Statement object and use it to execute the query.
            Statement stmt = db.createStatement();

            String query = "SELECT *" +
                   "FROM Movie AS m LEFT OUTER JOIN Oscar AS o " +
                   "ON m.id = o.movie_id;";


            ResultSet results = stmt.executeQuery(query);

            String nameCheck = "" ;

            Object Xstring = "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>";
            outfile.println(Xstring);
            outfile.println();
            movieContainer movieInfoBuffer = new movieContainer();
            Object MoviesTag = "<movies>";
            outfile.println(MoviesTag);
            // Iterate over the tuples in the result and write them to the file.
            while (results.next()) {
                String name = results.getString(2);    // 1 = leftmost column
                if(name.matches(nameCheck)){
                    String dir = results.getString("director_id");
                    if(!movieInfoBuffer.directors.contains(dir))
                        movieInfoBuffer.directors = movieInfoBuffer.directors + " " + "P" + dir;
                    String actor_id2 = results.getString("actor_id");
                    if(!movieInfoBuffer.actors.contains(actor_id2))
                        movieInfoBuffer.actors = movieInfoBuffer.actors + " " + "P" + actor_id2;
                    String value = results.getString("type");
                    String yearValue = results.getString(10);
                    if (!value.equals(null)){
                        value = results.getString(11);
                        String opid = results.getString("person_id");
                        if(value.matches("BEST-PICTURE"))
                            value = "0000000";
                        else {
                            value = opid;
                        }
                        value = "O" + yearValue + value;
                    }
                    if (!movieInfoBuffer.oscars.contains(value))
                        movieInfoBuffer.oscars = movieInfoBuffer.oscars + " " + value;

                }
                else{
                        if (movieInfoBuffer.id != null){
                            // write contents of mcontainer to xml file
                            Object MovieTag = "  <movie ";
                            outfile.print(MovieTag);
                            outfile.println("id="+  "\"" + "M" + movieInfoBuffer.id + "\""+ " " + "directors="+
                                            "\"" + "P" + movieInfoBuffer.directors + "\"");
                            outfile.print("         actors=");
                            outfile.println("\"" + movieInfoBuffer.actors + "\"" + ">");
                            if (movieInfoBuffer.oscars != null){
                                outfile.print("         oscars=");
                                outfile.println("\"" + movieInfoBuffer.oscars + "\"" + " ");
                            }
                            outfile.println("    <name>"+ movieInfoBuffer.name + "</name>");
                            outfile.println("    <year>"+ movieInfoBuffer.year + "</year>");
                            if(movieInfoBuffer.rating != null)
                                outfile.println("    <rating>"+ movieInfoBuffer.rating + "</rating>");
                            outfile.println("    <runtime>"+ movieInfoBuffer.runtime + "</runtime>");
                            if(movieInfoBuffer.genre != null)
                                outfile.println("    <genre>"+ movieInfoBuffer.genre +"</genre>");
                            if(movieInfoBuffer.earnings_rank != null)
                                outfile.println("    <earnings_rank>"+ movieInfoBuffer.earnings_rank+"</earnings_rank>");
                            Object closeMovieTag = "  </movie>";
                            outfile.println(closeMovieTag);
                            movieInfoBuffer =  new movieContainer();
                            numMoviesReturned++;
                        }
                        else{
                            // add values to movieInfoBuffer fields
                        //  populateBuffer(results,movieInfoBuffer);

                            //String name = results.getString("name");
                            String id = results.getString("id");
                            String Query = "SELECT director_id FROM Director " +
                                           "WHERE movie_id = " + id;

                            Statement stmt2 = db.createStatement();
                            ResultSet result2 = stmt2.executeQuery(Query);
                            String director_id = result2.getString(1);
                            String actor_id = results.getString("actor_id");
                            String year = results.getString(2);
                            String oscars = results.getString("type");

                            if (oscars !=null){
                                oscars = results.getString(11);
                                String opid = results.getString("person_id");
                                if(oscars.matches("BEST-PICTURE"))
                                    oscars = "0000000";
                                else {
                                    oscars = opid;
                                }
                            }
                                // poulate Buffer
                                String rating = results.getString(4);
                                String runtime = results.getString(5);
                                String genre = results.getString("genre");
                                String earnings_rank = results.getString("earnings_rank");
                                String oscYear = results.getString(10);
                                movieInfoBuffer.id = id;
                                movieInfoBuffer.name = name;
                                movieInfoBuffer.year = year;
                                movieInfoBuffer.rating = rating;
                                movieInfoBuffer.runtime = runtime;
                                movieInfoBuffer.genre = genre;
                                movieInfoBuffer.earnings_rank = earnings_rank;
                                movieInfoBuffer.directors = director_id;
                                movieInfoBuffer.actors = "P" + actor_id;
                                if (oscars != null){
                                    movieInfoBuffer.oscYear = oscYear;
                                    movieInfoBuffer.oscars = "O"+ oscYear + oscars;
                                }
                            nameCheck = name;
                        }

                }
            }

            Object closeMoviesTag = "</movies>";
            outfile.println(closeMoviesTag);
            System.out.println("numMovies="+numMoviesReturned);
            System.out.println("movies.xml has been written.");
            // Close the file and the database connection.
            outfile.close();
            db.close();
        }