· 7 years ago · Dec 02, 2018, 09:38 PM
1package database;
2/*
3 Author: Brandon Zhou
4 Course: CS 157A
5 Date: 11/16/2018
6 */
7
8import java.sql.*;
9
10public class Main {
11
12 public static void main(String[] args) throws Exception
13 {
14 Connection conn = getConnection();
15
16 try
17 {
18 createDatabase(conn);
19 dropTables(conn);
20 createTables(conn);
21 populate(conn);
22 testAdd(conn);
23 requiredQueries(conn);
24 //testCascade(conn);
25 }
26 catch (Exception e)
27 {
28 e.printStackTrace();
29 }
30 finally
31 {
32 try
33 {
34 conn.close();
35 }
36 catch (SQLException e)
37 {
38 System.out.println("SQL Exception " + e.getMessage());
39 e.printStackTrace();
40 }
41 }
42 }
43
44
45
46 public static void createTables(Connection c) throws Exception
47 {
48 Connection conn = c;
49 PreparedStatement create = null;
50 //Connection conn = getConnection();
51
52 // publishers
53 create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS publishers "
54 + "(publisherID int NOT NULL AUTO_INCREMENT, publisherName char(100) NOT NULL UNIQUE, "
55 + "PRIMARY KEY (publisherID))");
56 create.executeUpdate();
57
58 // titles
59 create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS titles "
60 + "(isbn char(10) NOT NULL, editionNumber int NOT NULL, year char(4) NOT NULL, publisherID int NOT NULL, price float(8,2) NOT NULL, title varchar(500) NOT NULL, "
61 + "FOREIGN KEY (publisherID) REFERENCES publishers(publisherID) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (isbn))");
62 create.executeUpdate();
63
64 // authors
65 create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS authors "
66 + "(authorID int NOT NULL AUTO_INCREMENT, firstname char(20) NOT NULL, lastname char(20) NOT NULL, "
67 + "PRIMARY KEY (authorID), UNIQUE fullname (firstname, lastname))");
68 create.executeUpdate();
69
70 // authorISBN
71 create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS authorISBN "
72 + "(authorID int, isbn char(10), "
73 + "FOREIGN KEY (isbn) REFERENCES titles(isbn) ON UPDATE CASCADE ON DELETE CASCADE, "
74 + "FOREIGN KEY (authorID) REFERENCES authors(authorID) ON UPDATE CASCADE ON DELETE CASCADE)");
75 create.executeUpdate();
76
77 System.out.println("Created tables");
78 }
79
80 public static void testAdd(Connection c) throws Exception
81 {
82 Connection conn = c;
83 //Connection conn = getConnection();
84
85 Statement stmt = conn.createStatement();
86
87 //New Book 1
88
89 String sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Omid', 'Sharghi')";
90 stmt.executeUpdate(sql);
91
92 sql = "INSERT INTO publishers (publisherName) VALUES ('Omid Publishing')";
93 stmt.executeUpdate(sql);
94
95 sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
96 + "VALUES ('0123456789', '3', '2001', (SELECT publisherID FROM publishers WHERE publisherName = 'Omid Publishing'), '25.00', 'To Kill A Mockingbird')";
97 stmt.executeUpdate(sql);
98
99 sql = "INSERT INTO authorISBN (authorID, isbn) "
100 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Omid' AND lastname = 'Sharghi'), (SELECT isbn FROM titles WHERE title = 'To Kill A Mockingbird'))";
101 stmt.executeUpdate(sql);
102
103 // New Book 2
104
105 sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Albert', 'Camus')";
106 stmt.executeUpdate(sql);
107
108 sql = "INSERT INTO publishers (publisherName) VALUES ('Gallimard')";
109 stmt.executeUpdate(sql);
110
111 sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
112 + "VALUES ('1123456789', '1', '1942', (SELECT publisherID FROM publishers WHERE publisherName = 'Gallimard'), '32.23', 'The Stranger')";
113 stmt.executeUpdate(sql);
114
115 sql = "INSERT INTO authorISBN (authorID, isbn) "
116 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Albert' AND lastname = 'Camus'), (SELECT isbn FROM titles WHERE title = 'The Stranger'))";
117 stmt.executeUpdate(sql);
118
119 // New Book 3
120
121 sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Dan', 'Brown')";
122 stmt.executeUpdate(sql);
123
124 sql = "INSERT INTO publishers (publisherName) VALUES ('Doubleday')";
125 stmt.executeUpdate(sql);
126
127 sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
128 + "VALUES ('0385504209', '1', '2003', (SELECT publisherID FROM publishers WHERE publisherName = 'Doubleday'), '17.95', 'The Da Vinci Code')";
129 stmt.executeUpdate(sql);
130
131 sql = "INSERT INTO authorISBN (authorID, isbn) "
132 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'), (SELECT isbn FROM titles WHERE title = 'The Da Vinci Code'))";
133 stmt.executeUpdate(sql);
134
135 // New Book 4
136
137 sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Dan', 'Brown')";
138 stmt.executeUpdate(sql);
139
140 sql = "INSERT INTO publishers (publisherName) VALUES ('Pocket Books')";
141 stmt.executeUpdate(sql);
142
143 sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
144 + "VALUES ('1416524800', '1', '2006', (SELECT publisherID FROM publishers WHERE publisherName = 'Pocket Books'), '17.01', 'Deception Point')";
145 stmt.executeUpdate(sql);
146
147 sql = "INSERT INTO authorISBN (authorID, isbn) "
148 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'), (SELECT isbn FROM titles WHERE title = 'Deception Point'))";
149 stmt.executeUpdate(sql);
150
151 }
152
153 public static void requiredQueries(Connection c) throws Exception
154 {
155 Connection conn = c;
156 //Connection conn = getConnection();
157 Statement stmt = conn.createStatement();
158
159 //Statement 1
160 String query = "SELECT * FROM authors ORDER BY lastname, firstname";
161 ResultSet rs = stmt.executeQuery(query);
162
163 System.out.println("\nAUTHORS: ");
164 while(rs.next())
165 {
166 System.out.println(rs.getString("firstname") + " " + rs.getString("lastname"));
167 }
168
169 //Statement 2
170 query = "SELECT * FROM publishers";
171 rs = stmt.executeQuery(query);
172
173 System.out.println("\nPUBLISHERS: ");
174 while(rs.next())
175 {
176 System.out.println(rs.getString("publisherName"));
177 }
178
179 //Statement 3
180 query = "SELECT title, year, isbn FROM titles WHERE publisherID = (SELECT publisherID FROM publishers WHERE publisherName = 'Knopf')";
181 rs = stmt.executeQuery(query);
182
183 System.out.println("\nTITLE FOR PUBLISHER: ");
184 while(rs.next())
185 {
186 System.out.println("Title:" + rs.getString("title") + " Year:" + rs.getString("year") + " ISBN:" + rs.getString("isbn"));
187 }
188
189 //Statement 4
190 query = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Stephen', 'King')";
191 stmt.executeUpdate(query);
192
193 //Statement 5
194 query = "UPDATE authors SET firstname = 'Steph' WHERE lastname = 'King' AND firstname = 'Stephen'";
195 stmt.executeUpdate(query);
196
197 //Statement 6
198 query = "INSERT INTO publishers (publisherName) VALUES ('St. Martins Press')";
199 stmt.executeUpdate(query);
200
201 query = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
202 + "VALUES ('031218087X', '1', '1998', (SELECT publisherID FROM publishers WHERE publisherName = 'St. Martins Press'), '16.15', 'Digital Fortress')";
203 stmt.executeUpdate(query);
204
205 query = "INSERT INTO authorISBN (authorID, isbn) "
206 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'), (SELECT isbn FROM titles WHERE title = 'Digital Fortress'))";
207 stmt.executeUpdate(query);
208
209 //Statement 7
210 query = "INSERT INTO publishers (publisherName) VALUES ('Bloomsbury')";
211 stmt.executeUpdate(query);
212
213 //Statement 8
214 query = "UPDATE publishers SET publisherName = 'Simon and Schuster' WHERE publisherName = 'Simon & Schuster'";
215 stmt.executeUpdate(query);
216 }
217
218 public static void dropTables(Connection c) throws Exception
219 {
220 Connection conn = c;
221 //Connection conn = getConnection();
222 Statement stmt = conn.createStatement();
223
224 String sql = "DROP TABLE IF EXISTS authorISBN";
225 stmt.executeUpdate(sql);
226
227 sql = "DROP TABLE IF EXISTS authors";
228 stmt.executeUpdate(sql);
229
230 sql = "DROP TABLE IF EXISTS titles";
231 stmt.executeUpdate(sql);
232
233 sql = "DROP TABLE IF EXISTS publishers";
234 stmt.executeUpdate(sql);
235 }
236
237 public static Connection getConnection() throws Exception{
238 try
239 {
240 String driver = "com.mysql.cj.jdbc.Driver";
241 String url = "jdbc:mysql://localhost:3306/testDB";
242 String username = "root";
243 String password = "password";
244 Class.forName(driver);
245
246 Connection conn = DriverManager.getConnection(url, username, password);
247 System.out.println("Success");
248 return conn;
249 }
250 catch(Exception e)
251 {
252 System.out.println(e);;
253 }
254 return null;
255 }
256
257 public static void testCascade(Connection c) throws Exception
258 {
259 Connection conn = c;
260 //Connection conn = getConnection();
261 Statement stmt = conn.createStatement();
262
263 String sql = "DELETE FROM authors WHERE firstname = 'Omid'";
264 stmt.executeUpdate(sql);
265
266 //sql = "DELETE FROM publishers WHERE publisherName = 'Omid Publishing'";
267 //stmt.executeUpdate(sql);
268 }
269
270 public static void createDatabase(Connection conn) throws Exception
271 {
272 String driver = "com.mysql.cj.jdbc.Driver";
273 String url = "jdbc:mysql://localhost:3306/";
274 String username = "root";
275 String password = "password";
276 Class.forName(driver);
277
278 conn = DriverManager.getConnection(url, username, password);
279
280 Statement s = conn.createStatement();
281 int result = s.executeUpdate("CREATE DATABASE IF NOT EXISTS testDB");
282 System.out.println("Result: " + result);
283
284 }
285
286 public static void populate(Connection c) throws Exception
287 {
288 Connection conn = c;
289 //Connection conn = getConnection();
290 Statement stmt = conn.createStatement();
291
292 // Pulled from Amazon editor's choices for 2018
293
294 // Populate authors table
295 String sql = "INSERT INTO authors(firstname, lastname) VALUES "
296 + "('Tara', 'Westover'),"
297 + "('Esi', 'Edugyan'),"
298 // Vincent and Vladic co-authored a book
299 + "('Lynn', 'Vincent'),"
300 + "('Sara', 'Vladic'),"
301 + "('Stephen', 'King'),"
302 + "('Francisco', 'Cantu'),"
303 + "('A.J.', 'Finn'),"
304 + "('Diane', 'Setterfield'),"
305 + "('Tomi', 'Adeyemi'),"
306 + "('Leif', 'Enger'),"
307 + "('Tommy', 'Orange'),"
308 + "('Kristin', 'Hannah'),"
309 + "('Beth', 'Macy'),"
310 + "('Kevin', 'McCarthy'),"
311 + "('Ottessa', 'Moshfegh'),"
312 + "('David', 'Blight'),"
313 + "('Yuval', 'Harari'),"
314 + "('Tana', 'French'),"
315 + "('Shobha', 'Rao'),"
316 + "('Heather', 'Morris'),"
317 + "('John', 'Carreyrou')";
318 stmt.executeUpdate(sql);
319
320 // Populate publishers table
321 sql = "INSERT INTO publishers(publisherName) VALUES "
322 + "('Random House'),"
323 + "('Knopf'),"
324 + "('Simon & Schuster'),"
325 + "('Scribner'),"
326 + "('Riverhead Books'),"
327 + "('William Morrow'),"
328 + "('Atria/Emily Bestler Books'),"
329 + "('Henry Holt and Co.'),"
330 + "('Grove Press'),"
331 + "('St. Martin\\'s Press')," // Had to use escape characters to handle the apostrophe
332 + "('Little, Brown and Company'),"
333 + "('W. W. Norton & Company'),"
334 + "('Penguin Press'),"
335 + "('Spiegel & Grau'),"
336 + "('Viking'),"
337 + "('Flatiron Books'),"
338 + "('Harper Paperbacks')";
339 stmt.executeUpdate(sql);
340
341 // Populate titles table
342 // Realized too late that since I'm using a best-of-2018 list all the years and editions are identical
343 // Randomly chose a few different values for year to avoid potentially overlooking an issue
344 sql = "INSERT INTO titles(isbn, editionNumber, year, publisherID, price, title) VALUES "
345 + "('0399590501', 1, '2015', 1, 16.80, 'Educated'),"
346 + "('0525521429', 2, '2013', 2, 18.32, 'Washington Black'),"
347 + "('1501135945', 1, '2018', 3, 12.86, 'Indianapolis: The True Story of the Worst Sea Disaster in U.S. Naval History and the Fifty-Year Fight to Exonerate an Innocent Man'),"
348 + "('1982102314', 1, '2018', 4, 11.97, 'Elevation'),"
349 + "('0735217718', 1, '2018', 5, 17.68, 'The Line Becomes a River: Dispatches from the Border'),"
350 + "('0062678416', 1, '2018', 6, 14.51, 'The Woman in the Window'),"
351 + "('0743298071', 1, '2018', 7, 20.85, 'Once Upon a River'),"
352 + "('1250170972', 1, '2018', 8, 14.24, 'Children of Blood and Bone'),"
353 + "('0802128785', 1, '2018', 9, 17.97, 'Virgil Wander'),"
354 + "('0525520375', 1, '2018', 2, 15.57, 'There There'),"
355 + "('0312577230', 1, '2018', 10, 19.36, 'The Great Alone'),"
356 + "('0316551244', 1, '2018', 11, 19.04, 'Dopesick: Dealers\\, Doctors\\, and the Drug Company that Addicted America'),"
357 + "('0393652041', 1, '2018', 12, 17.07, 'Wolves of Eden'),"
358 + "('0525522115', 1, '2018', 13, 17.68, 'My Year of Rest and Relaxation'),"
359 + "('1416590315', 1, '2018', 3, 22.50, 'Frederick Douglas: Prophet of Freedom'),"
360 + "('0525512172', 1, '2018', 14, 12.87, '21 Lessons for the 21st Century'),"
361 + "('0735224629', 1, '2018', 15, 18.30, 'The Witch Elm'),"
362 + "('1250074256', 1, '2018', 16, 17.24, 'Girls Burn Brighter'),"
363 + "('0062797158', 1, '2018', 17, 13.58, 'The Tattoist of Auschwitz'),"
364 + "('152473165X', 1, '2018', 2, 12.81, 'Bad Blood: Secrets and Lies in a Silicon Valley Startup')";
365 stmt.executeUpdate(sql);
366
367 // Populate authorISBN
368 sql = "INSERT INTO authorISBN(authorID, isbn) VALUES "
369 + "(1, '0399590501'),"
370 + "(2, '0525521429'),"
371 + "(3, '1501135945'),"
372 + "(4, '1501135945'),"
373 + "(5, '1982102314'),"
374 + "(6, '0735217718'),"
375 + "(7, '0062678416'),"
376 + "(8, '0743298071'),"
377 + "(9, '1250170972'),"
378 + "(10, '0802128785'),"
379 + "(11, '0525520375'),"
380 + "(12, '0312577230'),"
381 + "(13, '0316551244'),"
382 + "(14, '0393652041'),"
383 + "(15, '1416590315'),"
384 + "(16, '0525512172'),"
385 + "(17, '0735224629'),"
386 + "(18, '1250074256'),"
387 + "(19, '0062797158'),"
388 + "(20, '152473165X')";
389 stmt.executeUpdate(sql);
390 }
391}package database;
392/*
393 Author: Brandon Zhou
394 Course: CS 157A
395 Date: 11/16/2018
396 */
397
398import java.sql.*;
399
400public class Main {
401
402 public static void main(String[] args) throws Exception
403 {
404 Connection conn = getConnection();
405
406 try
407 {
408 createDatabase(conn);
409 dropTables(conn);
410 createTables(conn);
411 populate(conn);
412 testAdd(conn);
413 requiredQueries(conn);
414 //testCascade(conn);
415 }
416 catch (Exception e)
417 {
418 e.printStackTrace();
419 }
420 finally
421 {
422 try
423 {
424 conn.close();
425 }
426 catch (SQLException e)
427 {
428 System.out.println("SQL Exception " + e.getMessage());
429 e.printStackTrace();
430 }
431 }
432 }
433
434
435
436 public static void createTables(Connection c) throws Exception
437 {
438 Connection conn = c;
439 PreparedStatement create = null;
440 //Connection conn = getConnection();
441
442 // publishers
443 create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS publishers "
444 + "(publisherID int NOT NULL AUTO_INCREMENT, publisherName char(100) NOT NULL UNIQUE, "
445 + "PRIMARY KEY (publisherID))");
446 create.executeUpdate();
447
448 // titles
449 create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS titles "
450 + "(isbn char(10) NOT NULL, editionNumber int NOT NULL, year char(4) NOT NULL, publisherID int NOT NULL, price float(8,2) NOT NULL, title varchar(500) NOT NULL, "
451 + "FOREIGN KEY (publisherID) REFERENCES publishers(publisherID) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (isbn))");
452 create.executeUpdate();
453
454 // authors
455 create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS authors "
456 + "(authorID int NOT NULL AUTO_INCREMENT, firstname char(20) NOT NULL, lastname char(20) NOT NULL, "
457 + "PRIMARY KEY (authorID), UNIQUE fullname (firstname, lastname))");
458 create.executeUpdate();
459
460 // authorISBN
461 create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS authorISBN "
462 + "(authorID int, isbn char(10), "
463 + "FOREIGN KEY (isbn) REFERENCES titles(isbn) ON UPDATE CASCADE ON DELETE CASCADE, "
464 + "FOREIGN KEY (authorID) REFERENCES authors(authorID) ON UPDATE CASCADE ON DELETE CASCADE)");
465 create.executeUpdate();
466
467 System.out.println("Created tables");
468 }
469
470 public static void testAdd(Connection c) throws Exception
471 {
472 Connection conn = c;
473 //Connection conn = getConnection();
474
475 Statement stmt = conn.createStatement();
476
477 //New Book 1
478
479 String sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Omid', 'Sharghi')";
480 stmt.executeUpdate(sql);
481
482 sql = "INSERT INTO publishers (publisherName) VALUES ('Omid Publishing')";
483 stmt.executeUpdate(sql);
484
485 sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
486 + "VALUES ('0123456789', '3', '2001', (SELECT publisherID FROM publishers WHERE publisherName = 'Omid Publishing'), '25.00', 'To Kill A Mockingbird')";
487 stmt.executeUpdate(sql);
488
489 sql = "INSERT INTO authorISBN (authorID, isbn) "
490 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Omid' AND lastname = 'Sharghi'), (SELECT isbn FROM titles WHERE title = 'To Kill A Mockingbird'))";
491 stmt.executeUpdate(sql);
492
493 // New Book 2
494
495 sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Albert', 'Camus')";
496 stmt.executeUpdate(sql);
497
498 sql = "INSERT INTO publishers (publisherName) VALUES ('Gallimard')";
499 stmt.executeUpdate(sql);
500
501 sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
502 + "VALUES ('1123456789', '1', '1942', (SELECT publisherID FROM publishers WHERE publisherName = 'Gallimard'), '32.23', 'The Stranger')";
503 stmt.executeUpdate(sql);
504
505 sql = "INSERT INTO authorISBN (authorID, isbn) "
506 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Albert' AND lastname = 'Camus'), (SELECT isbn FROM titles WHERE title = 'The Stranger'))";
507 stmt.executeUpdate(sql);
508
509 // New Book 3
510
511 sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Dan', 'Brown')";
512 stmt.executeUpdate(sql);
513
514 sql = "INSERT INTO publishers (publisherName) VALUES ('Doubleday')";
515 stmt.executeUpdate(sql);
516
517 sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
518 + "VALUES ('0385504209', '1', '2003', (SELECT publisherID FROM publishers WHERE publisherName = 'Doubleday'), '17.95', 'The Da Vinci Code')";
519 stmt.executeUpdate(sql);
520
521 sql = "INSERT INTO authorISBN (authorID, isbn) "
522 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'), (SELECT isbn FROM titles WHERE title = 'The Da Vinci Code'))";
523 stmt.executeUpdate(sql);
524
525 // New Book 4
526
527 sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Dan', 'Brown')";
528 stmt.executeUpdate(sql);
529
530 sql = "INSERT INTO publishers (publisherName) VALUES ('Pocket Books')";
531 stmt.executeUpdate(sql);
532
533 sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
534 + "VALUES ('1416524800', '1', '2006', (SELECT publisherID FROM publishers WHERE publisherName = 'Pocket Books'), '17.01', 'Deception Point')";
535 stmt.executeUpdate(sql);
536
537 sql = "INSERT INTO authorISBN (authorID, isbn) "
538 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'), (SELECT isbn FROM titles WHERE title = 'Deception Point'))";
539 stmt.executeUpdate(sql);
540
541 }
542
543 public static void requiredQueries(Connection c) throws Exception
544 {
545 Connection conn = c;
546 //Connection conn = getConnection();
547 Statement stmt = conn.createStatement();
548
549 //Statement 1
550 String query = "SELECT * FROM authors ORDER BY lastname, firstname";
551 ResultSet rs = stmt.executeQuery(query);
552
553 System.out.println("\nAUTHORS: ");
554 while(rs.next())
555 {
556 System.out.println(rs.getString("firstname") + " " + rs.getString("lastname"));
557 }
558
559 //Statement 2
560 query = "SELECT * FROM publishers";
561 rs = stmt.executeQuery(query);
562
563 System.out.println("\nPUBLISHERS: ");
564 while(rs.next())
565 {
566 System.out.println(rs.getString("publisherName"));
567 }
568
569 //Statement 3
570 query = "SELECT title, year, isbn FROM titles WHERE publisherID = (SELECT publisherID FROM publishers WHERE publisherName = 'Knopf')";
571 rs = stmt.executeQuery(query);
572
573 System.out.println("\nTITLE FOR PUBLISHER: ");
574 while(rs.next())
575 {
576 System.out.println("Title:" + rs.getString("title") + " Year:" + rs.getString("year") + " ISBN:" + rs.getString("isbn"));
577 }
578
579 //Statement 4
580 query = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Stephen', 'King')";
581 stmt.executeUpdate(query);
582
583 //Statement 5
584 query = "UPDATE authors SET firstname = 'Steph' WHERE lastname = 'King' AND firstname = 'Stephen'";
585 stmt.executeUpdate(query);
586
587 //Statement 6
588 query = "INSERT INTO publishers (publisherName) VALUES ('St. Martins Press')";
589 stmt.executeUpdate(query);
590
591 query = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
592 + "VALUES ('031218087X', '1', '1998', (SELECT publisherID FROM publishers WHERE publisherName = 'St. Martins Press'), '16.15', 'Digital Fortress')";
593 stmt.executeUpdate(query);
594
595 query = "INSERT INTO authorISBN (authorID, isbn) "
596 + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'), (SELECT isbn FROM titles WHERE title = 'Digital Fortress'))";
597 stmt.executeUpdate(query);
598
599 //Statement 7
600 query = "INSERT INTO publishers (publisherName) VALUES ('Bloomsbury')";
601 stmt.executeUpdate(query);
602
603 //Statement 8
604 query = "UPDATE publishers SET publisherName = 'Simon and Schuster' WHERE publisherName = 'Simon & Schuster'";
605 stmt.executeUpdate(query);
606 }
607
608 public static void dropTables(Connection c) throws Exception
609 {
610 Connection conn = c;
611 //Connection conn = getConnection();
612 Statement stmt = conn.createStatement();
613
614 String sql = "DROP TABLE IF EXISTS authorISBN";
615 stmt.executeUpdate(sql);
616
617 sql = "DROP TABLE IF EXISTS authors";
618 stmt.executeUpdate(sql);
619
620 sql = "DROP TABLE IF EXISTS titles";
621 stmt.executeUpdate(sql);
622
623 sql = "DROP TABLE IF EXISTS publishers";
624 stmt.executeUpdate(sql);
625 }
626
627 public static Connection getConnection() throws Exception{
628 try
629 {
630 String driver = "com.mysql.cj.jdbc.Driver";
631 String url = "jdbc:mysql://localhost:3306/testDB";
632 String username = "root";
633 String password = "password";
634 Class.forName(driver);
635
636 Connection conn = DriverManager.getConnection(url, username, password);
637 System.out.println("Success");
638 return conn;
639 }
640 catch(Exception e)
641 {
642 System.out.println(e);;
643 }
644 return null;
645 }
646
647 public static void testCascade(Connection c) throws Exception
648 {
649 Connection conn = c;
650 //Connection conn = getConnection();
651 Statement stmt = conn.createStatement();
652
653 String sql = "DELETE FROM authors WHERE firstname = 'Omid'";
654 stmt.executeUpdate(sql);
655
656 //sql = "DELETE FROM publishers WHERE publisherName = 'Omid Publishing'";
657 //stmt.executeUpdate(sql);
658 }
659
660 public static void createDatabase(Connection conn) throws Exception
661 {
662 String driver = "com.mysql.cj.jdbc.Driver";
663 String url = "jdbc:mysql://localhost:3306/";
664 String username = "root";
665 String password = "password";
666 Class.forName(driver);
667
668 conn = DriverManager.getConnection(url, username, password);
669
670 Statement s = conn.createStatement();
671 int result = s.executeUpdate("CREATE DATABASE IF NOT EXISTS testDB");
672 System.out.println("Result: " + result);
673
674 }
675
676 public static void populate(Connection c) throws Exception
677 {
678 Connection conn = c;
679 //Connection conn = getConnection();
680 Statement stmt = conn.createStatement();
681
682 // Pulled from Amazon editor's choices for 2018
683
684 // Populate authors table
685 String sql = "INSERT INTO authors(firstname, lastname) VALUES "
686 + "('Tara', 'Westover'),"
687 + "('Esi', 'Edugyan'),"
688 // Vincent and Vladic co-authored a book
689 + "('Lynn', 'Vincent'),"
690 + "('Sara', 'Vladic'),"
691 + "('Stephen', 'King'),"
692 + "('Francisco', 'Cantu'),"
693 + "('A.J.', 'Finn'),"
694 + "('Diane', 'Setterfield'),"
695 + "('Tomi', 'Adeyemi'),"
696 + "('Leif', 'Enger'),"
697 + "('Tommy', 'Orange'),"
698 + "('Kristin', 'Hannah'),"
699 + "('Beth', 'Macy'),"
700 + "('Kevin', 'McCarthy'),"
701 + "('Ottessa', 'Moshfegh'),"
702 + "('David', 'Blight'),"
703 + "('Yuval', 'Harari'),"
704 + "('Tana', 'French'),"
705 + "('Shobha', 'Rao'),"
706 + "('Heather', 'Morris'),"
707 + "('John', 'Carreyrou')";
708 stmt.executeUpdate(sql);
709
710 // Populate publishers table
711 sql = "INSERT INTO publishers(publisherName) VALUES "
712 + "('Random House'),"
713 + "('Knopf'),"
714 + "('Simon & Schuster'),"
715 + "('Scribner'),"
716 + "('Riverhead Books'),"
717 + "('William Morrow'),"
718 + "('Atria/Emily Bestler Books'),"
719 + "('Henry Holt and Co.'),"
720 + "('Grove Press'),"
721 + "('St. Martin\\'s Press')," // Had to use escape characters to handle the apostrophe
722 + "('Little, Brown and Company'),"
723 + "('W. W. Norton & Company'),"
724 + "('Penguin Press'),"
725 + "('Spiegel & Grau'),"
726 + "('Viking'),"
727 + "('Flatiron Books'),"
728 + "('Harper Paperbacks')";
729 stmt.executeUpdate(sql);
730
731 // Populate titles table
732 // Realized too late that since I'm using a best-of-2018 list all the years and editions are identical
733 // Randomly chose a few different values for year to avoid potentially overlooking an issue
734 sql = "INSERT INTO titles(isbn, editionNumber, year, publisherID, price, title) VALUES "
735 + "('0399590501', 1, '2015', 1, 16.80, 'Educated'),"
736 + "('0525521429', 2, '2013', 2, 18.32, 'Washington Black'),"
737 + "('1501135945', 1, '2018', 3, 12.86, 'Indianapolis: The True Story of the Worst Sea Disaster in U.S. Naval History and the Fifty-Year Fight to Exonerate an Innocent Man'),"
738 + "('1982102314', 1, '2018', 4, 11.97, 'Elevation'),"
739 + "('0735217718', 1, '2018', 5, 17.68, 'The Line Becomes a River: Dispatches from the Border'),"
740 + "('0062678416', 1, '2018', 6, 14.51, 'The Woman in the Window'),"
741 + "('0743298071', 1, '2018', 7, 20.85, 'Once Upon a River'),"
742 + "('1250170972', 1, '2018', 8, 14.24, 'Children of Blood and Bone'),"
743 + "('0802128785', 1, '2018', 9, 17.97, 'Virgil Wander'),"
744 + "('0525520375', 1, '2018', 2, 15.57, 'There There'),"
745 + "('0312577230', 1, '2018', 10, 19.36, 'The Great Alone'),"
746 + "('0316551244', 1, '2018', 11, 19.04, 'Dopesick: Dealers\\, Doctors\\, and the Drug Company that Addicted America'),"
747 + "('0393652041', 1, '2018', 12, 17.07, 'Wolves of Eden'),"
748 + "('0525522115', 1, '2018', 13, 17.68, 'My Year of Rest and Relaxation'),"
749 + "('1416590315', 1, '2018', 3, 22.50, 'Frederick Douglas: Prophet of Freedom'),"
750 + "('0525512172', 1, '2018', 14, 12.87, '21 Lessons for the 21st Century'),"
751 + "('0735224629', 1, '2018', 15, 18.30, 'The Witch Elm'),"
752 + "('1250074256', 1, '2018', 16, 17.24, 'Girls Burn Brighter'),"
753 + "('0062797158', 1, '2018', 17, 13.58, 'The Tattoist of Auschwitz'),"
754 + "('152473165X', 1, '2018', 2, 12.81, 'Bad Blood: Secrets and Lies in a Silicon Valley Startup')";
755 stmt.executeUpdate(sql);
756
757 // Populate authorISBN
758 sql = "INSERT INTO authorISBN(authorID, isbn) VALUES "
759 + "(1, '0399590501'),"
760 + "(2, '0525521429'),"
761 + "(3, '1501135945'),"
762 + "(4, '1501135945'),"
763 + "(5, '1982102314'),"
764 + "(6, '0735217718'),"
765 + "(7, '0062678416'),"
766 + "(8, '0743298071'),"
767 + "(9, '1250170972'),"
768 + "(10, '0802128785'),"
769 + "(11, '0525520375'),"
770 + "(12, '0312577230'),"
771 + "(13, '0316551244'),"
772 + "(14, '0393652041'),"
773 + "(15, '1416590315'),"
774 + "(16, '0525512172'),"
775 + "(17, '0735224629'),"
776 + "(18, '1250074256'),"
777 + "(19, '0062797158'),"
778 + "(20, '152473165X')";
779 stmt.executeUpdate(sql);
780 }
781}