· 6 years ago · Apr 23, 2019, 11:14 PM
1import java.io.*;
2import java.util.*;
3import java.sql.*;
4import java.util.stream.IntStream;
5
6public class DMFinalProject {
7 static String DATABASE_DRIVER ="com.mysql.jdbc.Driver";
8 static String DATABASE_URL ="jdbc:mysql://localhost:3306/";
9 static String USERNAME ="root";
10 static String PASSWORD ="root";
11
12 public static void main(String[] args){
13 Connection myConn = null;
14
15 try {
16 myConn = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
17
18 Statement stmt = myConn.createStatement();
19
20 Scanner s = new Scanner(System.in);
21 if(s.nextLine().equals("yes"))
22 makePlayerDB(myConn, stmt);
23 else
24 stmt.executeUpdate("USE MockIMDb");
25
26 while(true){
27 System.out.println("0) Recommendation Engine");
28 System.out.println("1) Recommendation by genre.");
29 System.out.println("2) Recommendation by keyword.");
30 System.out.println("3) Recommendation by actor");
31 System.out.println("4) Recommendation by director");
32 System.out.println("-1 to exit.");
33 String input = "";
34 int intake = 8;
35 try {
36 intake = s.nextInt();
37 s.nextLine();
38 }catch (InputMismatchException e){
39 System.out.println("Wrong Entry Please Try Again");
40 }
41 switch(intake){
42 case 0:
43 System.out.print("Input Movie: ");
44 input = s.nextLine();
45 recommendationEngine(myConn, stmt, input);
46 break;
47 case 1:
48 System.out.print("Input Movie: ");
49 input = s.nextLine();
50 genreRecEngine(myConn, stmt, input);
51 break;
52 case 2:
53 System.out.print("Input Movie: ");
54 input = s.nextLine();
55 keywordRecEngine(myConn, stmt, input);
56 break;
57 case 3:
58 System.out.print("Input Movie: ");
59 input = s.nextLine();
60 actorRecEngine(myConn, stmt, input);
61 break;
62 case 4:
63 System.out.println("Input Movie");
64 input = s.nextLine();
65 directorRecEngine(myConn, stmt, input);
66 break;
67 case -1:
68 System.out.println("Exiting..");
69 return;
70 default:
71 System.out.print("Invalid argument");
72 break;
73 }
74 if(input.equals("-1"))
75 break;
76 }
77 } catch(SQLException se){
78 System.out.println("SQL Exception");
79 se.printStackTrace();
80 } catch(Exception e){
81 e.printStackTrace();
82 } finally{
83 try{
84 if(myConn != null){
85 myConn.close();
86 }
87 } catch(SQLException se){
88 se.printStackTrace();
89 }
90 }
91 }
92
93 public static void recommendationEngine(Connection conn, Statement stmt, String input){
94
95 String query = "SELECT DISTINCT X.original_title from parsed X left join (SELECT M.id, easterbunny.COUNT\n" +
96 " FROM parsed M INNER JOIN (\n" +
97 " SELECT COUNT(g.genre_id) AS COUNT, g.movie_id\n" +
98 " FROM genres g INNER JOIN (\n" +
99 " SELECT T.genre_id\n" +
100 " FROM genres T, parsed PS\n" +
101 " WHERE PS.original_title = \""+input+"\" AND\n" +
102 " T.movie_id = PS.id) AS jesus ON g.genre_id = jesus.genre_id GROUP BY\n" +
103 " g.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title !=\""+input+"\")\n" +
104 "as q1 on X.id=q1.id\n" +
105 " left JOIN\n" +
106 "(SELECT M.id, easterbunny.COUNT\n" +
107 "FROM parsed M INNER JOIN (\n" +
108 " SELECT COUNT(k.keyword_id) AS COUNT, k.movie_id\n" +
109 " FROM keywords k INNER JOIN (\n" +
110 " SELECT T.keyword_id,T.movie_id\n" +
111 " FROM keywords T, parsed PS\n" +
112 " WHERE PS.original_title = \""+input+"\" AND\n" +
113 " T.movie_id = PS.id) AS jesus ON k.keyword_id = jesus.keyword_id GROUP BY\n" +
114 " k.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \""+input+"\" )\n" +
115 "as q2 on q2.id=q1.id and X.id=q2.id\n" +
116 "left JOIN\n" +
117 "(SELECT M.id, easterbunny.COUNT\n" +
118 "FROM parsed M INNER JOIN (\n" +
119 " SELECT COUNT(D.director_id) AS COUNT, D.movie_id\n" +
120 " FROM directors D INNER JOIN (\n" +
121 " SELECT T.director_id,PS.id\n" +
122 " FROM directors T, parsed PS\n" +
123 " WHERE PS.original_title = \""+input+"\" AND\n" +
124 " T.movie_id = PS.id) AS jesus ON D.director_id = jesus.director_id GROUP BY\n" +
125 " D.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title !=\""+input+"\" )\n" +
126 "as q3 on q3.id=q2.id and X.id=q3.id\n" +
127 "left JOIN\n" +
128 "(SELECT M.id,easterbunny.COUNT\n" +
129 "FROM parsed M INNER JOIN (\n" +
130 " SELECT COUNT(C.actor_id) AS COUNT, C.movie_id\n" +
131 " FROM actors C INNER JOIN (\n" +
132 " SELECT T.actor_id,PS.id\n" +
133 " FROM actors T, parsed PS\n" +
134 " WHERE PS.original_title =\""+ input +"\" AND\n" +
135 " T.movie_id = PS.id) AS jesus ON jesus.actor_id = C.actor_id GROUP BY\n" +
136 " C.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \""+input+"\") " +
137 "as q4 on q4.id=q3.id and X.id=q4.id " +
138 " 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";
139 try {
140 ResultSet myRS = stmt.executeQuery(query);
141 //printResultSet(myRS);
142 while(myRS.next()){
143 System.out.println(myRS.getString("original_title"));
144 }
145 System.out.println(" ");
146 } catch(SQLException se){
147 System.out.println("SQL Exception");
148 se.printStackTrace();
149 }
150 }
151
152 public static void genreRecEngine(Connection conn, Statement stmt, String input){
153
154 String query = "SELECT M.original_title " +
155 "FROM parsed M INNER JOIN (" +
156 " SELECT COUNT(g.genre_id) AS yay, g.movie_id" +
157 " FROM genres g INNER JOIN (" +
158 " SELECT T.genre_id " +
159 " FROM genres T, parsed PS" +
160 " WHERE PS.original_title = \"" + input + "\" AND" +
161 " T.movie_id = PS.id) AS jesus ON g.genre_id = jesus.genre_id GROUP BY" +
162 " g.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and m.original_title != \"" +
163 input + "\" ORDER BY" +
164 " easterbunny.yay DESC, M.vote_count DESC, M.popularity DESC LIMIT 5;";
165 try {
166 ResultSet myRS = stmt.executeQuery(query);
167 //printResultSet(myRS);
168 while(myRS.next()){
169 System.out.println(myRS.getString("original_title"));
170 }
171 System.out.println(" ");
172 } catch(SQLException se){
173 System.out.println("SQL Exception");
174 se.printStackTrace();
175 }
176 }
177
178 public static void keywordRecEngine(Connection conn, Statement stmt, String input){
179
180 String query = "SELECT M.original_title " +
181 "FROM parsed M INNER JOIN (" +
182 " SELECT COUNT(k.keyword_id) AS yay, k.movie_id" +
183 " FROM keywords k INNER JOIN (" +
184 " SELECT T.keyword_id " +
185 " FROM keywords T, parsed PS" +
186 " WHERE PS.original_title = \"" + input + "\" AND" +
187 " T.movie_id = PS.id) AS jesus ON k.keyword_id = jesus.keyword_id GROUP BY" +
188 " k.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \"" +
189 input + "\" ORDER BY" +
190 " easterbunny.yay DESC, M.vote_count DESC, M.popularity DESC LIMIT 5;";
191 try {
192 ResultSet myRS = stmt.executeQuery(query);
193 //printResultSet(myRS);
194 while(myRS.next()){
195 System.out.println(myRS.getString("original_title"));
196 }
197 System.out.println(" ");
198 } catch(SQLException se){
199 System.out.println("SQL Exception");
200 se.printStackTrace();
201 }
202 }
203
204 public static void actorRecEngine(Connection conn, Statement stmt, String input){
205
206 String query = "SELECT M.original_title, easterbunny.yay " +
207 "FROM parsed M INNER JOIN (" +
208 " SELECT COUNT(C.actor_id) AS yay, C.movie_id" +
209 " FROM actors C INNER JOIN (" +
210 " SELECT T.actor_id " +
211 " FROM actors T, parsed PS" +
212 " WHERE PS.original_title = \"" + input + "\" AND" +
213 " T.movie_id = PS.id) AS jesus ON jesus.actor_id = C.actor_id GROUP BY" +
214 " C.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \"" +
215 input + "\" ORDER BY" +
216 " easterbunny.yay DESC, M.vote_count DESC, M.popularity DESC LIMIT 5;";
217 try {
218 ResultSet myRS = stmt.executeQuery(query);
219 //printResultSet(myRS);
220 while(myRS.next()){
221 System.out.println(myRS.getString("original_title"));
222 }
223 System.out.println(" ");
224 } catch(SQLException se){
225 System.out.println("SQL Exception");
226 se.printStackTrace();
227 }
228 }
229
230 public static void directorRecEngine(Connection conn, Statement stmt, String input){
231
232 String query = "SELECT M.original_title " +
233 "FROM parsed M INNER JOIN (" +
234 " SELECT COUNT(D.director_id) AS yay, D.movie_id" +
235 " FROM directors D INNER JOIN (" +
236 " SELECT T.director_id " +
237 " FROM directors T, parsed PS" +
238 " WHERE PS.original_title = \"" + input + "\" AND" +
239 " T.movie_id = PS.id) AS jesus ON D.director_id = jesus.director_id GROUP BY" +
240 " D.movie_id) AS easterbunny ON easterbunny.movie_id = M.id and M.original_title != \"" +
241 input + "\" ORDER BY" +
242 " easterbunny.yay DESC, M.vote_count DESC, M.popularity DESC LIMIT 5;";
243 try {
244 ResultSet myRS = stmt.executeQuery(query);
245 //printResultSet(myRS);
246 while(myRS.next()){
247 System.out.println(myRS.getString("original_title"));
248 }
249 System.out.println(" ");
250 } catch(SQLException se){
251 System.out.println("SQL Exception");
252 se.printStackTrace();
253 }
254 }
255
256 public static void makePlayerDB(Connection conn, Statement stmt) throws FileNotFoundException{
257
258 String tmp;
259 String[] var;
260
261 File generes = new File("genres.csv");
262 BufferedReader gbr = new BufferedReader(new FileReader(generes));
263 StringBuilder genres_query = new StringBuilder("INSERT INTO genres VALUES");
264
265 File keywords = new File("keywords.csv");
266 BufferedReader kwbr = new BufferedReader(new FileReader(keywords));
267 StringBuilder kw_query = new StringBuilder("INSERT INTO keywords VALUES");
268
269 File directors = new File("directors.csv");
270 BufferedReader crewbr = new BufferedReader(new FileReader(directors));
271 StringBuilder directors_query = new StringBuilder("INSERT INTO directors VALUES");
272
273 File actor = new File("cast.csv");
274 BufferedReader actorbr = new BufferedReader(new FileReader(actor));
275 StringBuilder actor_query = new StringBuilder("INSERT INTO actors VALUES");
276
277 File parsed = new File("parsed.csv");
278 BufferedReader pbr = new BufferedReader(new FileReader(parsed));
279 StringBuilder parsed_query = new StringBuilder("INSERT INTO parsed VALUES");
280
281 try {
282 // Create database
283 String sqlString = "DROP DATABASE IF EXISTS MockIMDb";
284 stmt.executeUpdate(sqlString);
285 sqlString = "CREATE DATABASE IF NOT EXISTS MockIMDb";
286 stmt.executeUpdate(sqlString);
287 System.out.println("Database created.");
288
289 // Use database
290 sqlString = "USE MockIMDb";
291 stmt.executeUpdate(sqlString);
292
293 /*
294 Creates Players, Teams, Members, Tournaments, Matches, Earnings tables
295 Inserts data into them
296 */
297 sqlString = "CREATE TABLE genres(movie_id INT UNSIGNED, " +
298 "genre_id INT UNSIGNED, " +
299 "name VARCHAR(255), " +
300 "PRIMARY KEY (movie_id, genre_id))";
301 stmt.executeUpdate(sqlString);
302
303 while((tmp = gbr.readLine()) != null) {
304 var = (tmp + " ").split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
305 for(int i = 0; i < var.length; i++) {
306 var[i] = var[i].replace("\"", "");
307 if (var[i].trim().equals(""))
308 var[i] = "NULL";
309 else
310 var[i] = "\"" + var[i] + "\"";
311 }
312 tmp = "(" + String.join(",", var) + "),";
313 genres_query.append(tmp);
314 }
315 genres_query.deleteCharAt(genres_query.length() - 1);
316
317 sqlString = genres_query.toString();
318 //System.out.println(sqlString);
319 stmt.executeUpdate(sqlString);
320
321 sqlString = "CREATE TABLE keywords(movie_id INT UNSIGNED, " +
322 "keyword_id INT UNSIGNED, " +
323 "PRIMARY KEY (keyword_id, movie_id))";
324 stmt.executeUpdate(sqlString);
325
326 while((tmp = kwbr.readLine()) != null) {
327 var = (tmp + " ").split(",");
328 for(int i = 0; i < var.length; i++) {
329 if (var[i].trim().equals(""))
330 var[i] = "NULL";
331 else
332 var[i] = "\"" + var[i] + "\"";
333 }
334 tmp = "(" + String.join(",", var) + "),";
335 kw_query.append(tmp);
336 }
337 kw_query.deleteCharAt(kw_query.length() - 1);
338
339 sqlString = kw_query.toString();
340 stmt.executeUpdate(sqlString);
341
342 sqlString = "CREATE TABLE directors(movie_id INT UNSIGNED, " +
343 "director_id INT UNSIGNED, " +
344 "name VARCHAR(255), " +
345 "PRIMARY KEY (movie_id, director_id))";
346 stmt.executeUpdate(sqlString);
347
348 while((tmp = crewbr.readLine()) != null) {
349 var = (tmp + " ").split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
350 for(int i = 0; i < var.length; i++) {
351 var[i] = var[i].replace("\"", "");
352 if (var[i].trim().equals(""))
353 var[i] = "NULL";
354 else
355 var[i] = "\"" + var[i] + "\"";
356 }
357 tmp = "(" + String.join(",", var) + "),";
358 directors_query.append(tmp);
359 }
360 directors_query.deleteCharAt(directors_query.length() - 1);
361
362 sqlString = directors_query.toString();
363 //System.out.println(sqlString);
364 stmt.executeUpdate(sqlString);
365
366 sqlString = "CREATE TABLE actors(movie_id INT UNSIGNED, " +
367 "cast_id VARCHAR(500), " +
368 "actor_id INT UNSIGNED, " +
369 "PRIMARY KEY (movie_id, cast_id, actor_id))";
370 stmt.executeUpdate(sqlString);
371
372 while((tmp = actorbr.readLine()) != null) {
373 var = (tmp + " ").split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
374 for(int i = 0; i < var.length; i++) {
375 var[i] = var[i].replace("\"", "");
376 if (var[i].trim().equals(""))
377 var[i] = "NULL";
378 else
379 var[i] = "\"" + var[i] + "\"";
380 }
381 tmp = "(" + String.join(",", var) + "),";
382 actor_query.append(tmp);
383 }
384 actor_query.deleteCharAt(actor_query.length() - 1);
385
386 sqlString = actor_query.toString();
387 //System.out.println(sqlString);
388 stmt.executeUpdate(sqlString);
389
390 sqlString = "CREATE TABLE parsed(id INT UNSIGNED, " +
391 "original_title VARCHAR(255), " +
392 "popularity FLOAT, " +
393 "vote_avg FLOAT, " +
394 "vote_count INT UNSIGNED, " +
395 "PRIMARY KEY (id))";
396 stmt.executeUpdate(sqlString);
397
398 while((tmp = pbr.readLine()) != null) {
399 var = (tmp + " ").split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
400 for(int i = 0; i < var.length; i++) {
401 var[i] = var[i].replace("\"", "");
402 if (var[i].trim().equals(""))
403 var[i] = "NULL";
404 else
405 var[i] = "\"" + var[i] + "\"" ;
406 }
407 tmp = "(" + String.join(",", var) + "),";
408 parsed_query.append(tmp);
409 }
410 parsed_query.deleteCharAt(parsed_query.length() - 1);
411
412 sqlString = parsed_query.toString();
413 stmt.executeUpdate(sqlString);
414
415 System.out.println("Tables created");
416
417 /*
418 sqlString = "SELECT * FROM Employees";
419 ResultSet myRS = stmt.executeQuery(sqlString);
420
421 while(myRS.next()){
422 System.out.println("SSN is: " + myRS.getString("emp_id") +
423 ", First name is: " + myRS.getString("first_name") +
424 ", Last name is: " + myRS.getString("last_name"));
425 }
426
427 stmt = myConn.createStatement();
428 sqlString = "DROP DATABASE PlayerDB_Assign4";
429 stmt.executeUpdate(sqlString);
430 System.out.println("Database dropped.");
431 stmt.close();
432 */
433 }catch(SQLException se){
434 System.out.println("SQL Exception");
435 se.printStackTrace();
436 } catch(Exception e){
437 e.printStackTrace();
438 }
439 }
440
441 public static void printResultSet(ResultSet rs) {
442 try {
443 // Get the result meta data, the number of columns, and the column width
444 ResultSetMetaData rsmd = rs.getMetaData();
445 int numVals = rsmd.getColumnCount();
446 int[] columnWidths = new int[numVals];
447
448 // Get the max width of each column's values
449 for (int i = 0; i < numVals; i++) {
450 columnWidths[i] = rsmd.getColumnDisplaySize(i + 1);
451 }
452
453 // Print the headers and figure out the correct formatting for values
454 int colNameLen;
455 String colName;
456 String[] columnFormat = new String[numVals];
457 StringBuilder headers = new StringBuilder();
458 for (int i = 0; i < numVals; i++) {
459 // Adjust the column width if the name is longer than all the values
460 colName = rsmd.getColumnName(i + 1);
461 colNameLen = colName.length();
462 if (colNameLen > columnWidths[i]) { columnWidths[i] = colNameLen; }
463
464 // Save the formatting for value printing and print the corresponding header
465 columnFormat[i] = "|%-" + columnWidths[i] + "s";
466 headers.append(String.format(columnFormat[i], colName));
467 }
468 // Print the correct number of dashes for the table border then print the headers
469 String dashes = new String(new char[IntStream.of(columnWidths).sum() + numVals + 1]).replace("\0", "-");
470 System.out.println(dashes);
471 System.out.println(headers.toString() + "|");
472 System.out.println(dashes);
473
474 // Print the values for each row in the result
475 while (rs.next()) {
476 for (int i = 0; i < numVals; i++) {
477 System.out.printf(String.format(columnFormat[i], rs.getObject(i + 1)));
478 }
479 System.out.println("|");
480 }
481 System.out.println(dashes);
482 }
483 catch (SQLException se) { System.out.println(se); }
484 catch (NullPointerException npe) { System.out.println("{Empty Set}");}
485 }
486}