· 7 years ago · Dec 02, 2018, 09:48 PM
1/*
2 Author: Brandon Zhou
3 Course: CS 157A
4 Date: 11/16/2018
5 */
6
7import java.sql.*;
8
9public 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}