Data Modeling Final Project (Java Source)

import java.io.*;
import java.util.*;
import java.sql.*;
import java.util.stream.IntStream;

public class DMFinalProject {
    static String DATABASE_DRIVER ="com.mysql.jdbc.Driver";
    static String DATABASE_URL ="jdbc:mysql://localhost:3306/";
    static String USERNAME ="root";
    static String PASSWORD ="root";

    public static void main(String[] args){
        Connection myConn = null;

        try {
            myConn = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);

            Statement stmt = myConn.createStatement();

            Scanner s = new Scanner(System.in);
            if(s.nextLine().equals("yes"))
                makePlayerDB(myConn, stmt);
            else
                stmt.executeUpdate("USE MockIMDb");

            while(true){
                System.out.println("0) Recommendation Engine");
                System.out.println("1) Recommendation by genre.");
                System.out.println("2) Recommendation by keyword.");
                System.out.println("3) Recommendation by actor");
                System.out.println("4) Recommendation by director");
                System.out.println("-1 to exit.");
                String input = "";
                int intake = 8;
                try {
                    intake = s.nextInt();
                    s.nextLine();
                }catch (InputMismatchException e){
                    System.out.println("Wrong Entry Please Try Again");
                }
                switch(intake){
                    case 0:
                        System.out.print("Input Movie: ");
                        input = s.nextLine();
                        recommendationEngine(myConn, stmt, input);
                        break;
                    case 1:
                        System.out.print("Input Movie: ");
                        input = s.nextLine();
                        genreRecEngine(myConn, stmt, input);
                        break;
                    case 2:
                        System.out.print("Input Movie: ");
                        input = s.nextLine();
                        keywordRecEngine(myConn, stmt, input);
                        break;
                    case 3:
                        System.out.print("Input Movie: ");
                        input = s.nextLine();
                        actorRecEngine(myConn, stmt, input);
                        break;
                    case 4:
                        System.out.println("Input Movie");
                        input = s.nextLine();
                        directorRecEngine(myConn, stmt, input);
                        break;
                    case -1:
                        System.out.println("Exiting..");
                        return;
                    default:
                        System.out.print("Invalid argument");
                        break;
                }
                if(input.equals("-1"))
                    break;
            }
        } catch(SQLException se){
            System.out.println("SQL Exception");
            se.printStackTrace();
        } catch(Exception e){
            e.printStackTrace();
        } finally{
            try{
                if(myConn != null){
                    myConn.close();
                }
            } catch(SQLException se){
                se.printStackTrace();
            }
        }
    }

    public static void recommendationEngine(Connection conn, Statement stmt, String input){

        String query = "SELECT DISTINCT X.original_title from parsed X left join (SELECT M.id, easterbunny.COUNT\n" +
                " FROM parsed M INNER JOIN (\n" +
                "   SELECT COUNT(g.genre_id) AS COUNT, g.movie_id\n" +
                "   FROM genres g INNER JOIN (\n" +
                "       SELECT T.genre_id\n" +
                "       FROM genres T, parsed PS\n" +
                "       WHERE PS.original_title = \""+input+"\" AND\n" +
                "           T.movie_id = PS.id) AS jesus ON g.genre_id = jesus.genre_id GROUP BY\n" +
                "           g.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title !=\""+input+"\")\n" +
                "as q1 on X.id=q1.id\n" +
                " left JOIN\n" +
                "(SELECT M.id, easterbunny.COUNT\n" +
                "FROM parsed M INNER JOIN (\n" +
                "     SELECT COUNT(k.keyword_id) AS COUNT, k.movie_id\n" +
                "     FROM keywords k INNER JOIN (\n" +
                "          SELECT T.keyword_id,T.movie_id\n" +
                "          FROM keywords T, parsed PS\n" +
                "          WHERE PS.original_title = \""+input+"\" AND\n" +
                "             T.movie_id = PS.id) AS jesus ON k.keyword_id = jesus.keyword_id GROUP BY\n" +
                "             k.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \""+input+"\" )\n" +
                "as q2 on q2.id=q1.id and X.id=q2.id\n" +
                "left JOIN\n" +
                "(SELECT M.id, easterbunny.COUNT\n" +
                "FROM parsed M INNER JOIN (\n" +
                "   SELECT COUNT(D.director_id) AS COUNT, D.movie_id\n" +
                "   FROM directors D INNER JOIN (\n" +
                "       SELECT T.director_id,PS.id\n" +
                "       FROM directors T, parsed PS\n" +
                "       WHERE PS.original_title = \""+input+"\" AND\n" +
                "           T.movie_id = PS.id) AS jesus ON D.director_id = jesus.director_id GROUP BY\n" +
                "           D.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title !=\""+input+"\" )\n" +
                "as q3 on q3.id=q2.id and X.id=q3.id\n" +
                "left JOIN\n" +
                "(SELECT M.id,easterbunny.COUNT\n" +
                "FROM parsed M INNER JOIN (\n" +
                "   SELECT COUNT(C.actor_id) AS COUNT, C.movie_id\n" +
                "   FROM actors C INNER JOIN (\n" +
                "       SELECT T.actor_id,PS.id\n" +
                "       FROM actors T, parsed PS\n" +
                "       WHERE PS.original_title =\""+ input +"\" AND\n" +
                "               T.movie_id = PS.id) AS jesus ON jesus.actor_id = C.actor_id GROUP BY\n" +
                "               C.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \""+input+"\") " +
                "as q4 on q4.id=q3.id and X.id=q4.id " +
                " order by q1.COUNT desc, q2.COUNT desc, q3.COUNT desc, q4.COUNT desc, X.popularity desc, X.vote_avg desc, X.vote_count desc limit 5";
        try {
            ResultSet myRS = stmt.executeQuery(query);
            //printResultSet(myRS);
            while(myRS.next()){
                System.out.println(myRS.getString("original_title"));
            }
            System.out.println(" ");
        } catch(SQLException se){
            System.out.println("SQL Exception");
            se.printStackTrace();
        }
    }

    public static void genreRecEngine(Connection conn, Statement stmt, String input){

        String query = "SELECT M.original_title " +
                "FROM parsed M INNER JOIN (" +
                "  SELECT COUNT(g.genre_id) AS yay, g.movie_id" +
                "  FROM genres g INNER JOIN (" +
                "    SELECT T.genre_id " +
                "    FROM genres T, parsed PS" +
                "    WHERE PS.original_title = \"" + input + "\" AND" +
                "        T.movie_id = PS.id) AS jesus ON g.genre_id = jesus.genre_id GROUP BY" +
                "          g.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and m.original_title != \"" +
                            input + "\" ORDER BY" +
                "        easterbunny.yay DESC, M.vote_count DESC, M.popularity DESC LIMIT 5;";
        try {
            ResultSet myRS = stmt.executeQuery(query);
            //printResultSet(myRS);
            while(myRS.next()){
                System.out.println(myRS.getString("original_title"));
            }
            System.out.println(" ");
        } catch(SQLException se){
            System.out.println("SQL Exception");
            se.printStackTrace();
        }
    }

    public static void keywordRecEngine(Connection conn, Statement stmt, String input){

        String query = "SELECT M.original_title " +
                "FROM parsed M INNER JOIN (" +
                "  SELECT COUNT(k.keyword_id) AS yay, k.movie_id" +
                "  FROM keywords k INNER JOIN (" +
                "    SELECT T.keyword_id " +
                "    FROM keywords T, parsed PS" +
                "    WHERE PS.original_title = \"" + input + "\" AND" +
                "        T.movie_id = PS.id) AS jesus ON  k.keyword_id = jesus.keyword_id GROUP BY" +
                "          k.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \"" +
                input + "\" ORDER BY" +
                "        easterbunny.yay DESC, M.vote_count DESC, M.popularity DESC LIMIT 5;";
        try {
            ResultSet myRS = stmt.executeQuery(query);
            //printResultSet(myRS);
            while(myRS.next()){
                System.out.println(myRS.getString("original_title"));
            }
            System.out.println(" ");
        } catch(SQLException se){
            System.out.println("SQL Exception");
            se.printStackTrace();
        }
    }

    public static void actorRecEngine(Connection conn, Statement stmt, String input){

        String query = "SELECT M.original_title, easterbunny.yay " +
                "FROM parsed M INNER JOIN (" +
                "  SELECT COUNT(C.actor_id) AS yay, C.movie_id" +
                "  FROM actors C INNER JOIN (" +
                "    SELECT T.actor_id " +
                "    FROM actors T, parsed PS" +
                "    WHERE PS.original_title = \"" + input + "\" AND" +
                "        T.movie_id = PS.id) AS jesus ON jesus.actor_id = C.actor_id GROUP BY" +
                "          C.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \"" +
                input + "\" ORDER BY" +
                "        easterbunny.yay DESC, M.vote_count DESC, M.popularity DESC LIMIT 5;";
        try {
            ResultSet myRS = stmt.executeQuery(query);
            //printResultSet(myRS);
            while(myRS.next()){
                System.out.println(myRS.getString("original_title"));
            }
            System.out.println(" ");
        } catch(SQLException se){
            System.out.println("SQL Exception");
            se.printStackTrace();
        }
    }

    public static void directorRecEngine(Connection conn, Statement stmt, String input){

        String query = "SELECT M.original_title " +
                "FROM parsed M INNER JOIN (" +
                "  SELECT COUNT(D.director_id) AS yay, D.movie_id" +
                "  FROM directors D INNER JOIN (" +
                "    SELECT T.director_id " +
                "    FROM directors T, parsed PS" +
                "    WHERE PS.original_title = \"" + input + "\" AND" +
                "        T.movie_id = PS.id) AS jesus ON D.director_id = jesus.director_id GROUP BY" +
                "          D.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \"" +
                input + "\" ORDER BY" +
                "        easterbunny.yay DESC, M.vote_count DESC, M.popularity DESC LIMIT 5;";
        try {
            ResultSet myRS = stmt.executeQuery(query);
            //printResultSet(myRS);
            while(myRS.next()){
                System.out.println(myRS.getString("original_title"));
            }
            System.out.println(" ");
        } catch(SQLException se){
            System.out.println("SQL Exception");
            se.printStackTrace();
        }
    }

    public static void makePlayerDB(Connection conn, Statement stmt) throws FileNotFoundException{

        String tmp;
        String[] var;

        File generes = new File("genres.csv");
        BufferedReader gbr = new BufferedReader(new FileReader(generes));
        StringBuilder genres_query = new StringBuilder("INSERT INTO genres VALUES");

        File keywords = new File("keywords.csv");
        BufferedReader kwbr = new BufferedReader(new FileReader(keywords));
        StringBuilder kw_query = new StringBuilder("INSERT INTO keywords VALUES");

        File directors = new File("directors.csv");
        BufferedReader crewbr = new BufferedReader(new FileReader(directors));
        StringBuilder directors_query = new StringBuilder("INSERT INTO directors VALUES");

        File actor = new File("cast.csv");
        BufferedReader actorbr = new BufferedReader(new FileReader(actor));
        StringBuilder actor_query = new StringBuilder("INSERT INTO actors VALUES");

        File parsed = new File("parsed.csv");
        BufferedReader pbr = new BufferedReader(new FileReader(parsed));
        StringBuilder parsed_query = new StringBuilder("INSERT INTO parsed VALUES");

        try {
            // Create database
            String sqlString = "DROP DATABASE IF EXISTS MockIMDb";
            stmt.executeUpdate(sqlString);
            sqlString = "CREATE DATABASE IF NOT EXISTS MockIMDb";
            stmt.executeUpdate(sqlString);
            System.out.println("Database created.");

            // Use database
            sqlString = "USE MockIMDb";
            stmt.executeUpdate(sqlString);

            /*
            Creates Players, Teams, Members, Tournaments, Matches, Earnings tables
            Inserts data into them
             */
            sqlString = "CREATE TABLE genres(movie_id INT UNSIGNED, " +
                    "genre_id INT UNSIGNED, " +
                    "name VARCHAR(255), " +
                    "PRIMARY KEY (movie_id, genre_id))";
            stmt.executeUpdate(sqlString);

            while((tmp = gbr.readLine()) != null) {
                var = (tmp + " ").split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
                for(int i = 0; i < var.length; i++) {
                    var[i] = var[i].replace("\"", "");
                    if (var[i].trim().equals(""))
                        var[i] = "NULL";
                    else
                        var[i] = "\"" + var[i] + "\"";
                }
                tmp = "(" + String.join(",", var) + "),";
                genres_query.append(tmp);
            }
            genres_query.deleteCharAt(genres_query.length() - 1);

            sqlString = genres_query.toString();
            //System.out.println(sqlString);
            stmt.executeUpdate(sqlString);

            sqlString = "CREATE TABLE keywords(movie_id INT UNSIGNED, " +
                    "keyword_id INT UNSIGNED, " +
                    "PRIMARY KEY (keyword_id, movie_id))";
            stmt.executeUpdate(sqlString);

            while((tmp = kwbr.readLine()) != null) {
                var = (tmp + " ").split(",");
                for(int i = 0; i < var.length; i++) {
                    if (var[i].trim().equals(""))
                        var[i] = "NULL";
                    else
                        var[i] = "\"" + var[i] + "\"";
                }
                tmp = "(" + String.join(",", var) + "),";
                kw_query.append(tmp);
            }
            kw_query.deleteCharAt(kw_query.length() - 1);

            sqlString = kw_query.toString();
            stmt.executeUpdate(sqlString);

            sqlString = "CREATE TABLE directors(movie_id INT UNSIGNED, " +
                    "director_id INT UNSIGNED, " +
                    "name VARCHAR(255), " +
                    "PRIMARY KEY (movie_id, director_id))";
            stmt.executeUpdate(sqlString);

            while((tmp = crewbr.readLine()) != null) {
                var = (tmp + " ").split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
                for(int i = 0; i < var.length; i++) {
                    var[i] = var[i].replace("\"", "");
                    if (var[i].trim().equals(""))
                        var[i] = "NULL";
                    else
                        var[i] = "\"" + var[i] + "\"";
                }
                tmp = "(" + String.join(",", var) + "),";
                directors_query.append(tmp);
            }
            directors_query.deleteCharAt(directors_query.length() - 1);

            sqlString = directors_query.toString();
            //System.out.println(sqlString);
            stmt.executeUpdate(sqlString);

            sqlString = "CREATE TABLE actors(movie_id INT UNSIGNED, " +
                    "cast_id VARCHAR(500), " +
                    "actor_id INT UNSIGNED, " +
                    "PRIMARY KEY (movie_id, cast_id, actor_id))";
            stmt.executeUpdate(sqlString);

            while((tmp = actorbr.readLine()) != null) {
                var = (tmp + " ").split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
                for(int i = 0; i < var.length; i++) {
                    var[i] = var[i].replace("\"", "");
                    if (var[i].trim().equals(""))
                        var[i] = "NULL";
                    else
                        var[i] = "\"" + var[i] + "\"";
                }
                tmp = "(" + String.join(",", var) + "),";
                actor_query.append(tmp);
            }
            actor_query.deleteCharAt(actor_query.length() - 1);

            sqlString = actor_query.toString();
            //System.out.println(sqlString);
            stmt.executeUpdate(sqlString);

            sqlString = "CREATE TABLE parsed(id INT UNSIGNED, " +
                    "original_title VARCHAR(255), " +
                    "popularity FLOAT, " +
                    "vote_avg FLOAT, " +
                    "vote_count INT UNSIGNED, " +
                    "PRIMARY KEY (id))";
            stmt.executeUpdate(sqlString);

            while((tmp = pbr.readLine()) != null) {
                var = (tmp + " ").split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
                for(int i = 0; i < var.length; i++) {
                    var[i] = var[i].replace("\"", "");
                    if (var[i].trim().equals(""))
                        var[i] = "NULL";
                    else
                        var[i] = "\"" + var[i] + "\"" ;
                }
                tmp = "(" + String.join(",", var) + "),";
                parsed_query.append(tmp);
            }
            parsed_query.deleteCharAt(parsed_query.length() - 1);

            sqlString = parsed_query.toString();
            stmt.executeUpdate(sqlString);

            System.out.println("Tables created");

            /*
            sqlString = "SELECT * FROM Employees";
            ResultSet myRS = stmt.executeQuery(sqlString);

            while(myRS.next()){
                System.out.println("SSN is: " + myRS.getString("emp_id") +
                        ", First name is: " + myRS.getString("first_name") +
                        ", Last name is: " + myRS.getString("last_name"));
            }

            stmt = myConn.createStatement();
            sqlString = "DROP DATABASE PlayerDB_Assign4";
            stmt.executeUpdate(sqlString);
            System.out.println("Database dropped.");
            stmt.close();
            */
        }catch(SQLException se){
            System.out.println("SQL Exception");
            se.printStackTrace();
        } catch(Exception e){
            e.printStackTrace();
        }
    }

    public static void printResultSet(ResultSet rs) {
        try {
            // Get the result meta data, the number of columns, and the column width
            ResultSetMetaData rsmd = rs.getMetaData();
            int numVals  = rsmd.getColumnCount();
            int[] columnWidths = new int[numVals];

            // Get the max width of each column's values
            for (int i = 0; i < numVals; i++) {
                columnWidths[i] = rsmd.getColumnDisplaySize(i + 1);
            }

            // Print the headers and figure out the correct formatting for values
            int colNameLen;
            String colName;
            String[] columnFormat = new String[numVals];
            StringBuilder headers = new StringBuilder();
            for (int i = 0; i < numVals; i++) {
                // Adjust the column width if the name is longer than all the values
                colName = rsmd.getColumnName(i + 1);
                colNameLen = colName.length();
                if (colNameLen > columnWidths[i]) { columnWidths[i] = colNameLen; }

                // Save the formatting for value printing and print the corresponding header
                columnFormat[i] = "|%-" + columnWidths[i] + "s";
                headers.append(String.format(columnFormat[i], colName));
            }
            // Print the correct number of dashes for the table border then print the headers
            String dashes = new String(new char[IntStream.of(columnWidths).sum() + numVals + 1]).replace("\0", "-");
            System.out.println(dashes);
            System.out.println(headers.toString() + "|");
            System.out.println(dashes);

            // Print the values for each row in the result
            while (rs.next()) {
                for (int i = 0; i < numVals; i++) {
                    System.out.printf(String.format(columnFormat[i], rs.getObject(i + 1)));
                }
                System.out.println("|");
            }
            System.out.println(dashes);
        }
        catch (SQLException se) { System.out.println(se); }
        catch (NullPointerException npe) { System.out.println("{Empty Set}");}
    }
}