prob5

    /*
     * 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 JavaSample {
        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);

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

            String query = "SELECT *" +
                           "FROM Movie ;";

            ResultSet results = stmt.executeQuery(query);

            int numMoviesReturned = 0;

            while (results.next()){
                String id = results.getString("id");
                String name = results.getString("name");
                String year = results.getString("year");
                String rating = results.getString("rating");
                String runtime = results.getString("runtime");
                String genre = results.getString("genre");
                String earnings_rank = results.getString("earnings_rank");
                String directors = "";
                String actors = "";
                String oscars = "";
                String oscYear = "";

                String dirQuery = "SELECT director_id FROM Director WHERE Director.movie_id = " + id;

                Statement dirstmt = db.createStatement();
                ResultSet dirRes = dirstmt.executeQuery(dirQuery);

                while(dirRes.next()){
                   String dir_id = dirRes.getString(1);
                   if(!directors.contains(dir_id))
                       directors = directors + "P" + dir_id + " ";
                }


                String actorQuery = "SELECT actor_id FROM Actor WHERE Actor.movie_id = " + id;

                Statement actorstmt = db.createStatement();
                ResultSet actorRes = actorstmt.executeQuery(actorQuery);

                while(actorRes.next()){
                   String act_id = actorRes.getString(1);
                   if(!actors.contains(act_id))
                      actors = actors + "P" + act_id + " ";
                }

                String oscQuery = "SELECT type,year,person_id FROM oscar WHERE movie_id = " + id;

                Statement oscstmt = db.createStatement();
                ResultSet oscarRes = oscstmt.executeQuery(oscQuery);

                while(oscarRes.next()){
                   String type = oscarRes.getString("type");
                   String year1 = oscarRes.getString("year");
                   String oscarpid = oscarRes.getString("person_id");
                   if (!type.equals(null)){


                        if(type.matches("BEST-PICTURE"))
                            type = "0000000";
                        else {
                            type = oscarpid;
                        }
                       type = "O" + year1 + type+ " ";
                    }

                   if(!oscars.contains(type))
                      oscars = oscars + type;
                }

                // write contents to xml file
                Object MovieTag = "  <movie ";
                outfile.print(MovieTag);
                outfile.println("id="+  "\"" + "M" + id + "\""+ " " + "directors="+  "\"" +  directors + "\"");
                outfile.print("         actors=");
                outfile.println("\"" + actors + "\"" + ">");
                if (oscars != null){
                    outfile.print("         oscars=");
                    outfile.println("\"" + oscars + "\"" + " ");
                }
                outfile.println("    <name>"+ name + "</name>");
                outfile.println("    <year>"+ year + "</year>");
                if(rating != null)
                    outfile.println("    <rating>"+ rating + "</rating>");
                outfile.println("    <runtime>"+ runtime + "</runtime>");
                if(genre != null)
                    outfile.println("    <genre>"+ genre +"</genre>");
                if(earnings_rank != null)
                    outfile.println("    <earnings_rank>"+ earnings_rank+"</earnings_rank>");
                Object closeMovieTag = "  </movie>";
                outfile.println(closeMovieTag);
                numMoviesReturned++;

            }


            System.out.println("numMovies="+numMoviesReturned);
            // Close the file and the database connection.
            outfile.close();
            db.close();
        }
    }