MainParser.java

package main.java;

import org.apache.commons.dbcp.BasicDataSource;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;

public class MainParser extends DefaultHandler {

    List<Movie> movies;

    ArrayList<Genre> tempGenres;

    private String tempId;

    private String tempTitle;

    private int tempYear;

    private String tempDirector;

    public String tempVal;

    private Movie tempMovie;

    private Genre tempGenre;

    private BasicDataSource dataSource;

    public MainParser() throws IOException {
        movies = new ArrayList<Movie>();

        File errorFile = new File("mainerrors.txt");
        if (errorFile.exists()) {
            errorFile.delete();
        }
        errorFile.createNewFile();
    }

    public void runExample() {
        parseDocument();
        printData();
    }

    private void parseDocument() {

        //get a factory
        SAXParserFactory spf = SAXParserFactory.newInstance();

        try {

            //get a new instance of parser
            SAXParser sp = spf.newSAXParser();

            //parse the file and also register this class for call backs
            sp.parse("mains243.xml", this);

        } catch (SAXException se) {
            se.printStackTrace();
        } catch (ParserConfigurationException pce) {
            pce.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        }
    }

    /**
     * Iterate through the list and print
     * the contents
     */
    private void printData() {

        Iterator<Movie> it = movies.iterator();
        while (it.hasNext()) {
            System.out.println(it.next().toString());
        }

        System.out.println("No of Movies '" + movies.size() + "'.");
    }

    public List<Movie> getMovies() {
        return movies;
    }

    //Event Handlers
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        if (qName.equalsIgnoreCase("film")) {
            tempMovie = new Movie();
        } else if (qName.equalsIgnoreCase("t")) {
            tempTitle = "";
        } else if (qName.equalsIgnoreCase("released")) {
            tempYear = -1;
        } else if (qName.equalsIgnoreCase("fid")) {
            tempId = "";
        } else if (qName.equalsIgnoreCase("dirname")) {
            tempDirector = "";
        } else if (qName.equalsIgnoreCase("cats")) {
            tempGenres = new ArrayList();
        } else if (qName.equalsIgnoreCase("cat")) {
            tempGenre = new Genre();
        }
    }

    public void characters(char[] ch, int start, int length) throws SAXException {
        tempVal = new String(ch, start, length);
    }

    public void endElement(String uri, String localName, String qName) throws SAXException {

        if (qName.equalsIgnoreCase("film")) {
            if (tempMovie != null) {
                tempMovie.setDirector(tempDirector);
                tempMovie.setYear(tempYear);
                tempMovie.setTitle(tempTitle);
                tempMovie.setId(tempId);
                tempMovie.setGenres(tempGenres);
                movies.add(tempMovie);
            }
        } else if (qName.equalsIgnoreCase("dirname")) {
            tempDirector = tempVal;
        } else if (qName.equalsIgnoreCase("fid")) {
            tempId = tempVal;
        } else if (qName.equalsIgnoreCase("year") || qName.equalsIgnoreCase("released")) {
            try {
                tempYear = Integer.parseInt(tempVal.trim());
            }
            catch (NumberFormatException ne) {
                try {
                    BufferedWriter writer = new BufferedWriter(new FileWriter("mainerrors.txt", true));
                    writer.append("Not a valid year: " + tempVal + "\n");
                    writer.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                tempYear = -1;
                tempMovie = null;
            }
        } else if (qName.equalsIgnoreCase("t")) {
            if (tempVal.equals("NKT")) {
                try {
                    BufferedWriter writer = new BufferedWriter(new FileWriter("mainerrors.txt", true));
                    writer.append("Unknown movie title: " + tempVal + "\n");
                    writer.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                tempMovie = null;
            }
            else {
                tempTitle = tempVal;
            }
        } else if (qName.equalsIgnoreCase("cat")) {
            try {
                tempGenre.setName(tempVal.trim().toLowerCase());
                tempGenres.add(tempGenre);
            }
            catch (Exception e) {
                try {
                    BufferedWriter writer = new BufferedWriter(new FileWriter("mainerrors.txt", true));
                    writer.append("Not a valid genre: " + tempVal + "\n");
                    writer.close();
                } catch (IOException ioe) {
                    ioe.printStackTrace();
                }
                tempMovie = null;
            }
        }
    }

    public void assembleSQLFile(List<Movie> movies) throws SQLException, IOException, NamingException {
        dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/moviedb");
        dataSource.setUsername("mytestuser");
        dataSource.setPassword("My6$Password");

        // Create the SQL file to be written to
        File mainSQL = new File("main.sql");
        if (mainSQL.exists()) {
            mainSQL.delete();
        }
        mainSQL.createNewFile();
        BufferedWriter mainSQLWriter = new BufferedWriter(new FileWriter("main.sql", true));
        mainSQLWriter.append("begin;\n");

        HashSet<String> movieIds = new HashSet();
        HashMap<String, Integer> genreIds = new HashMap();
        int currentGenreId = -1;

        try (Connection conn = dataSource.getConnection()) {
            // Need to query the database to get the currently inserted IDs of movies and genres + the max id of genres
            String movieIdQuery = "SELECT id from movies";
            PreparedStatement movieIdStatement = conn.prepareStatement(movieIdQuery);
            ResultSet movieIdSet = movieIdStatement.executeQuery();

            while (movieIdSet.next()) {
                movieIds.add(movieIdSet.getString("id"));
            }
            movieIdSet.close();
            movieIdStatement.close();

            String genreNameQuery = "SELECT * from genres";
            PreparedStatement genreStatement = conn.prepareStatement(genreNameQuery);
            ResultSet genreSet = genreStatement.executeQuery();

            while (genreSet.next()) {
                genreIds.put(genreSet.getString("name"), genreSet.getInt("id"));
            }
            genreSet.close();
            genreStatement.close();

            String genreCountQuery = "SELECT COUNT(*) as genreCount from genres";
            PreparedStatement genreCountStatement = conn.prepareStatement(genreCountQuery);
            ResultSet genreCountSet = genreCountStatement.executeQuery();

            while (genreCountSet.next()) {
                currentGenreId = genreCountSet.getInt("genreCount") + 1;
            }

            // With list of movies, write insert SQL statements only if the values are new to the DB
            // For every genre in a movie, write insert only if the name is new...update ID as well (also add genres_in_movies insert)
            for (Movie movie : movies) {
                // Inserting new movie

                movie.setTitle(movie.getTitle().replaceAll("\\\"", ""));

                if (!movieIds.contains(movie.getId())) {
                    movieIds.add(movie.getId());
                    String movieInsertStr = String.format("INSERT INTO movies VALUES(\"%s\", \"%s\", %d, \"%s\", %d);\n",
                            movie.getId(), movie.getTitle(),
                            movie.getYear(), movie.getDirector(), (int) ((Math.random() * 100) + 1));
                    movieInsertStr = movieInsertStr.replaceAll("\\\\", "");
                    mainSQLWriter.append(movieInsertStr);

                    String emptyRatingStr = String.format("INSERT INTO ratings VALUES(\"%s\", -1, 0);\n", movie.getId());
                    mainSQLWriter.append(emptyRatingStr);

                    // Inserting new genre
                    for (Genre genre : movie.getGenres()) {

                        if (!genreIds.containsKey(genre.getName())) {
                            genreIds.put(genre.getName(), currentGenreId);
                            String genreInsertStr = String.format("INSERT INTO genres VALUES(%d, \"%s\");\n", currentGenreId, genre.getName());
                            genreInsertStr = genreInsertStr.replaceAll("\\\\", "");
                            mainSQLWriter.append(genreInsertStr);
                            currentGenreId += 1;
                        }

                        // genres_in_moves update
                        String genresInMoviesStr = String.format("INSERT INTO genres_in_movies VALUES(%d, \"%s\");\n", genreIds.get(genre.getName()), movie.getId());
                        genresInMoviesStr = genresInMoviesStr.replaceAll("\\\\", "");
                        mainSQLWriter.append(genresInMoviesStr);
                    }
                }
            }

            // Finally, write commit; line
            mainSQLWriter.append("commit;");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            mainSQLWriter.close();
        }
    }

    public static void main(String[] args) throws IOException, SQLException, NamingException {
        MainParser spe = new MainParser();
        spe.runExample();

        List<Movie> parsedMovies = spe.getMovies();
        spe.assembleSQLFile(parsedMovies);
    }
}