Advertisement
Guest User

Untitled

a guest
Dec 2nd, 2018
117
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.64 KB | None | 0 0
  1. /*
  2. Author: Brandon Zhou
  3. Course: CS 157A
  4. Date: 11/16/2018
  5. */
  6.  
  7. import java.sql.*;
  8.  
  9. public class Main {
  10.  
  11. public static void main(String[] args) throws Exception
  12. {
  13. Connection conn = getConnection();
  14.  
  15. try
  16. {
  17. createDatabase(conn);
  18. dropTables(conn);
  19. createTables(conn);
  20. populate(conn);
  21. testAdd(conn);
  22. requiredQueries(conn);
  23. //testCascade(conn);
  24. }
  25. catch (Exception e)
  26. {
  27. e.printStackTrace();
  28. }
  29. finally
  30. {
  31. try
  32. {
  33. conn.close();
  34. }
  35. catch (SQLException e)
  36. {
  37. System.out.println("SQL Exception " + e.getMessage());
  38. e.printStackTrace();
  39. }
  40. }
  41. }
  42.  
  43.  
  44.  
  45. public static void createTables(Connection conn) throws Exception
  46. {
  47. PreparedStatement create = null;
  48.  
  49. // publishers
  50. create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS publishers "
  51. + "(publisherID int NOT NULL AUTO_INCREMENT, publisherName char(100) NOT NULL UNIQUE, "
  52. + "PRIMARY KEY (publisherID))");
  53. create.executeUpdate();
  54.  
  55. // titles
  56. create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS titles "
  57. + "(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, "
  58. + "FOREIGN KEY (publisherID) REFERENCES publishers(publisherID) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (isbn))");
  59. create.executeUpdate();
  60.  
  61. // authors
  62. create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS authors "
  63. + "(authorID int NOT NULL AUTO_INCREMENT, firstname char(20) NOT NULL, lastname char(20) NOT NULL, "
  64. + "PRIMARY KEY (authorID), UNIQUE fullname (firstname, lastname))");
  65. create.executeUpdate();
  66.  
  67. // authorISBN
  68. create = conn.prepareStatement("CREATE TABLE IF NOT EXISTS authorISBN "
  69. + "(authorID int, isbn char(10), "
  70. + "FOREIGN KEY (isbn) REFERENCES titles(isbn) ON UPDATE CASCADE ON DELETE CASCADE, "
  71. + "FOREIGN KEY (authorID) REFERENCES authors(authorID) ON UPDATE CASCADE ON DELETE CASCADE)");
  72. create.executeUpdate();
  73.  
  74. System.out.println("Created tables");
  75. }
  76.  
  77. public static void testAdd(Connection conn) throws Exception
  78. {
  79. Statement stmt = conn.createStatement();
  80.  
  81. //New Book 1
  82.  
  83. String sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Omid', 'Sharghi')";
  84. stmt.executeUpdate(sql);
  85.  
  86. sql = "INSERT INTO publishers (publisherName) VALUES ('Omid Publishing')";
  87. stmt.executeUpdate(sql);
  88.  
  89. sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
  90. + "VALUES ('0123456789', '3', '2001', (SELECT publisherID FROM publishers WHERE publisherName = 'Omid Publishing'), '25.00', 'To Kill A Mockingbird')";
  91. stmt.executeUpdate(sql);
  92.  
  93. sql = "INSERT INTO authorISBN (authorID, isbn) "
  94. + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Omid' AND lastname = 'Sharghi'), (SELECT isbn FROM titles WHERE title = 'To Kill A Mockingbird'))";
  95. stmt.executeUpdate(sql);
  96.  
  97. // New Book 2
  98.  
  99. sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Albert', 'Camus')";
  100. stmt.executeUpdate(sql);
  101.  
  102. sql = "INSERT INTO publishers (publisherName) VALUES ('Gallimard')";
  103. stmt.executeUpdate(sql);
  104.  
  105. sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
  106. + "VALUES ('1123456789', '1', '1942', (SELECT publisherID FROM publishers WHERE publisherName = 'Gallimard'), '32.23', 'The Stranger')";
  107. stmt.executeUpdate(sql);
  108.  
  109. sql = "INSERT INTO authorISBN (authorID, isbn) "
  110. + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Albert' AND lastname = 'Camus'), (SELECT isbn FROM titles WHERE title = 'The Stranger'))";
  111. stmt.executeUpdate(sql);
  112.  
  113. // New Book 3
  114.  
  115. sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Dan', 'Brown')";
  116. stmt.executeUpdate(sql);
  117.  
  118. sql = "INSERT INTO publishers (publisherName) VALUES ('Doubleday')";
  119. stmt.executeUpdate(sql);
  120.  
  121. sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
  122. + "VALUES ('0385504209', '1', '2003', (SELECT publisherID FROM publishers WHERE publisherName = 'Doubleday'), '17.95', 'The Da Vinci Code')";
  123. stmt.executeUpdate(sql);
  124.  
  125. sql = "INSERT INTO authorISBN (authorID, isbn) "
  126. + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'), (SELECT isbn FROM titles WHERE title = 'The Da Vinci Code'))";
  127. stmt.executeUpdate(sql);
  128.  
  129. // New Book 4
  130.  
  131. sql = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Dan', 'Brown')";
  132. stmt.executeUpdate(sql);
  133.  
  134. sql = "INSERT INTO publishers (publisherName) VALUES ('Pocket Books')";
  135. stmt.executeUpdate(sql);
  136.  
  137. sql = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
  138. + "VALUES ('1416524800', '1', '2006', (SELECT publisherID FROM publishers WHERE publisherName = 'Pocket Books'), '17.01', 'Deception Point')";
  139. stmt.executeUpdate(sql);
  140.  
  141. sql = "INSERT INTO authorISBN (authorID, isbn) "
  142. + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'), (SELECT isbn FROM titles WHERE title = 'Deception Point'))";
  143. stmt.executeUpdate(sql);
  144.  
  145. }
  146.  
  147. public static void requiredQueries(Connection conn) throws Exception
  148. {
  149. Statement stmt = conn.createStatement();
  150.  
  151. //Statement 1
  152. String query = "SELECT * FROM authors ORDER BY lastname, firstname";
  153. ResultSet rs = stmt.executeQuery(query);
  154.  
  155. System.out.println("\nAUTHORS: ");
  156. while(rs.next())
  157. {
  158. System.out.println(rs.getString("firstname") + " " + rs.getString("lastname"));
  159. }
  160.  
  161. //Statement 2
  162. query = "SELECT * FROM publishers";
  163. rs = stmt.executeQuery(query);
  164.  
  165. System.out.println("\nPUBLISHERS: ");
  166. while(rs.next())
  167. {
  168. System.out.println(rs.getString("publisherName"));
  169. }
  170.  
  171. //Statement 3
  172. query = "SELECT title, year, isbn FROM titles WHERE publisherID = (SELECT publisherID FROM publishers WHERE publisherName = 'Knopf')";
  173. rs = stmt.executeQuery(query);
  174.  
  175. System.out.println("\nTITLE FOR PUBLISHER: ");
  176. while(rs.next())
  177. {
  178. System.out.println("Title:" + rs.getString("title") + " Year:" + rs.getString("year") + " ISBN:" + rs.getString("isbn"));
  179. }
  180.  
  181. //Statement 4
  182. query = "INSERT IGNORE INTO authors (firstname, lastname) VALUES ('Stephen', 'King')";
  183. stmt.executeUpdate(query);
  184.  
  185. //Statement 5
  186. query = "UPDATE authors SET firstname = 'Steph' WHERE lastname = 'King' AND firstname = 'Stephen'";
  187. stmt.executeUpdate(query);
  188.  
  189. //Statement 6
  190. query = "INSERT INTO publishers (publisherName) VALUES ('St. Martins Press')";
  191. stmt.executeUpdate(query);
  192.  
  193. query = "INSERT INTO titles (isbn, editionNumber, year, publisherID, price, title) "
  194. + "VALUES ('031218087X', '1', '1998', (SELECT publisherID FROM publishers WHERE publisherName = 'St. Martins Press'), '16.15', 'Digital Fortress')";
  195. stmt.executeUpdate(query);
  196.  
  197. query = "INSERT INTO authorISBN (authorID, isbn) "
  198. + "VALUES ((SELECT authorID FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'), (SELECT isbn FROM titles WHERE title = 'Digital Fortress'))";
  199. stmt.executeUpdate(query);
  200.  
  201. //Statement 7
  202. query = "INSERT INTO publishers (publisherName) VALUES ('Bloomsbury')";
  203. stmt.executeUpdate(query);
  204.  
  205. //Statement 8
  206. query = "UPDATE publishers SET publisherName = 'Simon and Schuster' WHERE publisherName = 'Simon & Schuster'";
  207. stmt.executeUpdate(query);
  208. }
  209.  
  210. public static void dropTables(Connection conn) throws Exception
  211. {
  212. Statement stmt = conn.createStatement();
  213.  
  214. String sql = "DROP TABLE IF EXISTS authorISBN";
  215. stmt.executeUpdate(sql);
  216.  
  217. sql = "DROP TABLE IF EXISTS authors";
  218. stmt.executeUpdate(sql);
  219.  
  220. sql = "DROP TABLE IF EXISTS titles";
  221. stmt.executeUpdate(sql);
  222.  
  223. sql = "DROP TABLE IF EXISTS publishers";
  224. stmt.executeUpdate(sql);
  225. }
  226.  
  227. public static Connection getConnection() throws Exception{
  228. try
  229. {
  230. String driver = "com.mysql.cj.jdbc.Driver";
  231. String url = "jdbc:mysql://localhost:3306/testDB";
  232. String username = "root";
  233. String password = "test157a";
  234. Class.forName(driver);
  235.  
  236. Connection conn = DriverManager.getConnection(url, username, password);
  237. System.out.println("Success");
  238. return conn;
  239. }
  240. catch(Exception e)
  241. {
  242. System.out.println(e);;
  243. }
  244. return null;
  245. }
  246.  
  247. public static void testCascade(Connection conn) throws Exception
  248. {
  249. Statement stmt = conn.createStatement();
  250.  
  251. String sql = "DELETE FROM authors WHERE firstname = 'Dan' AND lastname = 'Brown'";
  252. stmt.executeUpdate(sql);
  253.  
  254. //sql = "DELETE FROM publishers WHERE publisherName = 'Omid Publishing'";
  255. //stmt.executeUpdate(sql);
  256. }
  257.  
  258. public static void createDatabase(Connection conn) throws Exception
  259. {
  260. Statement s = conn.createStatement();
  261. int result = s.executeUpdate("CREATE DATABASE IF NOT EXISTS testDB");
  262. System.out.println("Result: " + result);
  263. }
  264.  
  265. public static void populate(Connection conn) throws Exception
  266. {
  267. Statement stmt = conn.createStatement();
  268.  
  269. // Pulled from Amazon editor's choices for 2018
  270.  
  271. // Populate authors table
  272. String sql = "INSERT INTO authors(firstname, lastname) VALUES "
  273. + "('Tara', 'Westover'),"
  274. + "('Esi', 'Edugyan'),"
  275. // Vincent and Vladic co-authored a book
  276. + "('Lynn', 'Vincent'),"
  277. + "('Sara', 'Vladic'),"
  278. + "('Stephen', 'King'),"
  279. + "('Francisco', 'Cantu'),"
  280. + "('A.J.', 'Finn'),"
  281. + "('Diane', 'Setterfield'),"
  282. + "('Tomi', 'Adeyemi'),"
  283. + "('Leif', 'Enger'),"
  284. + "('Tommy', 'Orange'),"
  285. + "('Kristin', 'Hannah'),"
  286. + "('Beth', 'Macy'),"
  287. + "('Kevin', 'McCarthy'),"
  288. + "('Ottessa', 'Moshfegh'),"
  289. + "('David', 'Blight'),"
  290. + "('Yuval', 'Harari'),"
  291. + "('Tana', 'French'),"
  292. + "('Shobha', 'Rao'),"
  293. + "('Heather', 'Morris'),"
  294. + "('John', 'Carreyrou')";
  295. stmt.executeUpdate(sql);
  296.  
  297. // Populate publishers table
  298. sql = "INSERT INTO publishers(publisherName) VALUES "
  299. + "('Random House'),"
  300. + "('Knopf'),"
  301. + "('Simon & Schuster'),"
  302. + "('Scribner'),"
  303. + "('Riverhead Books'),"
  304. + "('William Morrow'),"
  305. + "('Atria/Emily Bestler Books'),"
  306. + "('Henry Holt and Co.'),"
  307. + "('Grove Press'),"
  308. + "('St. Martin\\'s Press')," // Had to use escape characters to handle the apostrophe
  309. + "('Little, Brown and Company'),"
  310. + "('W. W. Norton & Company'),"
  311. + "('Penguin Press'),"
  312. + "('Spiegel & Grau'),"
  313. + "('Viking'),"
  314. + "('Flatiron Books'),"
  315. + "('Harper Paperbacks')";
  316. stmt.executeUpdate(sql);
  317.  
  318. // Populate titles table
  319. // Realized too late that since I'm using a best-of-2018 list all the years and editions are identical
  320. // Randomly chose a few different values for year to avoid potentially overlooking an issue
  321. sql = "INSERT INTO titles(isbn, editionNumber, year, publisherID, price, title) VALUES "
  322. + "('0399590501', 1, '2015', 1, 16.80, 'Educated'),"
  323. + "('0525521429', 2, '2013', 2, 18.32, 'Washington Black'),"
  324. + "('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'),"
  325. + "('1982102314', 1, '2018', 4, 11.97, 'Elevation'),"
  326. + "('0735217718', 1, '2018', 5, 17.68, 'The Line Becomes a River: Dispatches from the Border'),"
  327. + "('0062678416', 1, '2018', 6, 14.51, 'The Woman in the Window'),"
  328. + "('0743298071', 1, '2018', 7, 20.85, 'Once Upon a River'),"
  329. + "('1250170972', 1, '2018', 8, 14.24, 'Children of Blood and Bone'),"
  330. + "('0802128785', 1, '2018', 9, 17.97, 'Virgil Wander'),"
  331. + "('0525520375', 1, '2018', 2, 15.57, 'There There'),"
  332. + "('0312577230', 1, '2018', 10, 19.36, 'The Great Alone'),"
  333. + "('0316551244', 1, '2018', 11, 19.04, 'Dopesick: Dealers\\, Doctors\\, and the Drug Company that Addicted America'),"
  334. + "('0393652041', 1, '2018', 12, 17.07, 'Wolves of Eden'),"
  335. + "('0525522115', 1, '2018', 13, 17.68, 'My Year of Rest and Relaxation'),"
  336. + "('1416590315', 1, '2018', 3, 22.50, 'Frederick Douglas: Prophet of Freedom'),"
  337. + "('0525512172', 1, '2018', 14, 12.87, '21 Lessons for the 21st Century'),"
  338. + "('0735224629', 1, '2018', 15, 18.30, 'The Witch Elm'),"
  339. + "('1250074256', 1, '2018', 16, 17.24, 'Girls Burn Brighter'),"
  340. + "('0062797158', 1, '2018', 17, 13.58, 'The Tattoist of Auschwitz'),"
  341. + "('152473165X', 1, '2018', 2, 12.81, 'Bad Blood: Secrets and Lies in a Silicon Valley Startup')";
  342. stmt.executeUpdate(sql);
  343.  
  344. // Populate authorISBN
  345. sql = "INSERT INTO authorISBN(authorID, isbn) VALUES "
  346. + "(1, '0399590501'),"
  347. + "(2, '0525521429'),"
  348. + "(3, '1501135945'),"
  349. + "(4, '1501135945'),"
  350. + "(5, '1982102314'),"
  351. + "(6, '0735217718'),"
  352. + "(7, '0062678416'),"
  353. + "(8, '0743298071'),"
  354. + "(9, '1250170972'),"
  355. + "(10, '0802128785'),"
  356. + "(11, '0525520375'),"
  357. + "(12, '0312577230'),"
  358. + "(13, '0316551244'),"
  359. + "(14, '0393652041'),"
  360. + "(15, '1416590315'),"
  361. + "(16, '0525512172'),"
  362. + "(17, '0735224629'),"
  363. + "(18, '1250074256'),"
  364. + "(19, '0062797158'),"
  365. + "(20, '152473165X')";
  366. stmt.executeUpdate(sql);
  367. }
  368. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement