· 7 years ago · Dec 05, 2018, 08:28 PM
1package com;
2
3import java.sql.*;
4import java.text.DateFormat;
5import java.text.SimpleDateFormat;
6import java.util.Date;
7
8public class DB {
9 // JDBC driver name and database URL
10 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
11 static final String DB_URL = "jdbc:mysql://localhost:3306/sampledb";
12
13 // Database credentials
14 static final String USER = "root";
15 static final String PASS = "mysqlpassword";
16
17 public void main() throws Exception {
18 Connection conn = null;
19 Statement stmt = null;
20
21 try{
22 //Register JDBC driver
23 Class.forName("com.mysql.jdbc.Driver");
24
25 //Open a connection
26 System.out.println("Connecting to a selected database...");
27 conn = DriverManager.getConnection(DB_URL, USER, PASS);
28 System.out.println("Connected database successfully...");
29
30 //Execute a query
31 System.out.println("Creating table in given database...");
32 stmt = conn.createStatement();
33
34 //delete existing tables from database
35 String drop1 = "DROP TABLE PC_Members";
36 stmt.executeUpdate(drop1);
37 String drop2 = "DROP TABLE Review_Reports";
38 stmt.executeUpdate(drop2);
39 String drop3 = "DROP TABLE Papers";
40 stmt.executeUpdate(drop3);
41 String drop4 = "DROP VIEW fotouhipapers";
42 stmt.executeUpdate(drop4);
43 String drop5 = "DROP VIEW papersFotouhiisFirstAuthor";
44 stmt.executeUpdate(drop5);
45 String drop6 = "DROP VIEW papersAssigned";
46 stmt.executeUpdate(drop6);
47 String drop7 = "DROP VIEW noPapersAssigned";
48 stmt.executeUpdate(drop7);
49 String drop8 = "DROP VIEW papersRejectedByMattAndJohn";
50 stmt.executeUpdate(drop8);
51
52 //create tables
53 String papers = "CREATE TABLE Papers " +
54 "(paper_id INT NOT NULL AUTO_INCREMENT, " +
55 " title VARCHAR(45), " +
56 " authors VARCHAR(90), " +
57 " abstract VARCHAR(255), " +
58 " pdf VARCHAR(45) NULL, " +
59 " PRIMARY KEY ( paper_id ))";
60
61 String review_reports = "CREATE TABLE Review_Reports " +
62 "(report_id INT NOT NULL AUTO_INCREMENT, " +
63 "pc_id INT NULL, " +
64 "paper_id INT NOT NULL, " +
65 "review_description VARCHAR(300) NULL, " +
66 "final_recommendation VARCHAR(45), " +
67 "date_submitted VARCHAR(10) NULL, " +
68 "PRIMARY KEY ( report_id ))";
69 //"FOREIGN KEY (report_id) REFERENCES Papers (paper_id))";
70
71 String pc_members = "CREATE TABLE PC_Members " +
72 "(pc_id INT NOT NULL AUTO_INCREMENT, " +
73 "name VARCHAR(45) NOT NULL, " +
74 "PRIMARY KEY ( pc_id ))";
75 //"FOREIGN KEY (review_reports_report_id) REFERENCES Review_Reports (report_id))";
76
77 stmt.executeUpdate(papers);
78 System.out.println("Created Papers table");
79 stmt.executeUpdate(review_reports);
80 System.out.println("Created Review Reports table");
81 stmt.executeUpdate(pc_members);
82 System.out.println("Created PC Members table");
83
84 /*stmt.executeUpdate(papers_has_pc_members);
85 System.out.println("Created Papers Has PC Members table");*/
86
87 stmt = conn.createStatement();
88
89 //create 10 tuples for Papers table
90 String sql1 = "insert into Papers (title, authors, abstract, pdf) values(?, ?, ?, ?)";
91 PreparedStatement ps = conn.prepareStatement(sql1);
92
93 for (int i = 1; i < 11; i++) {
94 if (i % 2 == 0) {
95 ps.setString(1, "New Paper Title");
96 ps.setString(2, "Fotouhi");
97 ps.setString(3, "Abstract for paper");
98 ps.setString(4, "pdf");
99 ps.execute();
100 }
101 else {
102 ps.setString(1, "New Paper Title");
103 ps.setString(2, "Ben");
104 ps.setString(3, "Abstract for paper");
105 ps.setString(4, "pdf");
106 ps.execute();
107 }
108 }
109
110 //create 10 tuples for pc members table
111 String sql3 = "insert into PC_Members(name) values(?)";
112 PreparedStatement ps3 = conn.prepareStatement(sql3);
113
114 ps3.setString(1, "Matt");
115 ps3.execute();
116 ps3.setString(1, "John");
117 ps3.execute();
118 ps3.setString(1, "Kyle");
119 ps3.execute();
120 ps3.setString(1, "Sally");
121 ps3.execute();
122 ps3.setString(1, "Ed");
123 ps3.execute();
124 ps3.setString(1, "Anna");
125 ps3.execute();
126 ps3.setString(1, "Peter");
127 ps3.execute();
128 ps3.setString(1, "Sue");
129 ps3.execute();
130
131 }catch(Exception e){
132 //Handle errors for Class.forName
133 throw e;
134 }finally{
135 //block used to close resources
136 try{
137 if(stmt!=null)
138 conn.close();
139 }catch(SQLException se){
140 }// do nothing
141 try{
142 if(conn!=null)
143 conn.close();
144 }catch(SQLException se){
145 se.printStackTrace();
146 }//end finally try
147 }//end try
148 System.out.println("Goodbye!");
149
150 DB b = new DB();
151
152 b.insertIntoPapers("American Revolution Paper", "Fotouhi", "This project involves discovering "
153 + "how the American Revolution was remembered during the nineteenth century.", "pdf");
154 b.insertIntoPapers("Civil War Paper", "Fotouhi, Donald Duck", "This project involves discovering "
155 + "how the Civil War was remembered during the nineteenth century.", "pdf");
156 b.insertIntoPapers("Comic Books Paper", "Peter, Fotouhi, Lou", "This project involves discovering "
157 + "awesome comic books are!", "pdf");
158 b.insertIntoPapers("Batman Paper", "Darin, Lou, Fotouhi", "This project involves a look into "
159 + "Batman's secret life.", "pdf");
160 b.deleteFromPapers(3);
161 b.updateTitleInPaper(2, "my updated title");
162 b.updateAuthorsInPaper(3, "Fotouhi");
163 b.updateAbsInPaper(4, "This is my updated abstract");
164
165 b.insertIntoRR(1, 2, "n/a", "inserted description", "12/01/2018");
166 b.insertIntoRR(4, 2, "n/a", "inserted description", "12/01/2018");
167 b.insertIntoRR(3, 5, "n/a", "inserted description", "12/01/2018");
168 b.insertIntoRR(3, 2, "n/a", "inserted description", "12/01/2018");
169 b.insertIntoRR(2, 6, "n/a", "inserted description", "12/01/2018");
170 b.insertIntoRR(5, 7, "n/a", "inserted description", "12/01/2018");
171 b.insertIntoRR(4, 1, "n/a", "inserted description", "12/01/2018");
172 b.insertIntoRR(8, 10, "n/a", "inserted description", "12/01/2018");
173 b.insertIntoRR(1, 8, "rejected", "inserted description", "12/01/2018");
174 b.insertIntoRR(2, 11, "rejected", "inserted description", "12/01/2018");
175 b.deleteFromRR(1);
176 b.updateFinalRecInRR(4, "accepted");
177 b.updateDescriptionInRR(5, "updated description");
178
179 b.insertIntoPC("Steve");
180 b.deleteFromPCMembers(1);
181
182 b.papersByFotouhi();
183 b.papersFotouhiIsFirstAuthor();
184 b.pcMemberWithmostPapersAssigned();
185 b.pcMemberWithNoPapersAssigned();
186 b.papersRejectedByMattAndJohn();
187
188
189 }//end main
190
191 ///////////////////////////////PAPERS METHODS///////////////////////////////
192
193 //insert into papers table
194 public void insertIntoPapers(String title, String authors, String abs, String pdf) throws SQLException {
195 Connection conn = null;
196 conn = DriverManager.getConnection(DB_URL, USER, PASS);
197 String sql = "insert into Papers (title, authors, abstract, pdf) values(?, ?, ?, ?)";
198 PreparedStatement ps = conn.prepareStatement(sql);
199 ps.setString(1, title);
200 ps.setString(2, authors);
201 ps.setString(3, abs);
202 ps.setString(4, pdf);
203 ps.executeUpdate();
204 }
205
206 //delte from papers table
207 public void deleteFromPapers (int id) throws SQLException {
208 Connection conn = null;
209 conn = DriverManager.getConnection(DB_URL, USER, PASS);
210 PreparedStatement st = conn.prepareStatement("DELETE FROM Papers WHERE paper_id = ?");
211 st.setInt(1, id);
212 st.executeUpdate();
213 }
214
215 //update title in papers table
216 public void updateTitleInPaper (int id, String newTitle) throws SQLException {
217 Connection conn = null;
218 conn = DriverManager.getConnection(DB_URL, USER, PASS);
219 PreparedStatement st = conn.prepareStatement("UPDATE Papers SET title = ? WHERE paper_id = ?");
220 st.setString(1 , newTitle);
221 st.setInt(2, id);
222 st.executeUpdate();
223 }
224
225 //update authors in papers table
226 public void updateAuthorsInPaper (int id, String newAuthors) throws SQLException {
227 Connection conn = null;
228 conn = DriverManager.getConnection(DB_URL, USER, PASS);
229 PreparedStatement st = conn.prepareStatement("UPDATE Papers SET authors = ? WHERE paper_id = ?");
230 st.setString(1 , newAuthors);
231 st.setInt(2, id);
232 st.executeUpdate();
233 }
234
235 //update abstract in papers table
236 public void updateAbsInPaper (int id, String newAbs) throws SQLException {
237 Connection conn = null;
238 conn = DriverManager.getConnection(DB_URL, USER, PASS);
239 PreparedStatement st = conn.prepareStatement("UPDATE Papers SET abstract = ? WHERE paper_id = ?");
240 st.setString(1 , newAbs);
241 st.setInt(2, id);
242 st.executeUpdate();
243 }
244
245///////////////////////////////REVIEW REPORTS METHODS///////////////////////////////
246
247 //insert a review report
248 public void insertIntoRR(int pcID, int paperID, String finalRec, String reviewDes, String date) throws SQLException {
249 Connection conn = null;
250 conn = DriverManager.getConnection(DB_URL, USER, PASS);
251 String sql = "insert into Review_Reports (pc_id, paper_id, final_recommendation, review_description, "
252 + "date_submitted) values(?, ?, ?, ?, ?)";
253 PreparedStatement ps = conn.prepareStatement(sql);
254 ps.setInt(1, pcID);
255 ps.setInt(2, paperID);
256 ps.setString(3, finalRec);
257 ps.setString(4, reviewDes);
258 ps.setString(5, date);
259 ps.executeUpdate();
260 }
261
262 //delete row from Review Reports
263 public void deleteFromRR (int id) throws SQLException {
264 Connection conn = null;
265 conn = DriverManager.getConnection(DB_URL, USER, PASS);
266 PreparedStatement st = conn.prepareStatement("DELETE FROM Review_Reports WHERE report_id = ?");
267 PreparedStatement st1 = conn.prepareStatement("DELETE FROM Papers WHERE paper_id = ?");
268 st1.setInt(1, id);
269 st.setInt(1, id);
270 st1.executeUpdate();
271 st.executeUpdate();
272 }
273
274 //update final recommendation in Review Reports
275 public void updateFinalRecInRR (int id, String updatedRec) throws SQLException {
276 Connection conn = null;
277 conn = DriverManager.getConnection(DB_URL, USER, PASS);
278 PreparedStatement st = conn.prepareStatement("UPDATE Review_Reports SET final_recommendation = ? "
279 + "WHERE report_id = ?");
280 st.setString(1 , updatedRec);
281 st.setInt(2, id);
282 st.executeUpdate();
283 }
284
285 //update review description in review reports
286 public void updateDescriptionInRR (int id, String updatedDescription) throws SQLException {
287 Connection conn = null;
288 conn = DriverManager.getConnection(DB_URL, USER, PASS);
289 PreparedStatement st = conn.prepareStatement("UPDATE Review_Reports SET review_description = ? "
290 + "WHERE report_id = ?");
291 st.setString(1 , updatedDescription);
292 st.setInt(2, id);
293 st.executeUpdate();
294 }
295
296///////////////////////////////PC MEMBERS METHODS///////////////////////////////
297
298 //insert into PC Members
299 public void insertIntoPC(String name) throws SQLException {
300 Connection conn = null;
301 conn = DriverManager.getConnection(DB_URL, USER, PASS);
302 String sql = "insert into PC_Members (name) values(?)";
303 PreparedStatement ps = conn.prepareStatement(sql);
304 ps.setString(1, name);
305 ps.executeUpdate();
306 }
307
308 //delete row from PC Members
309 public void deleteFromPCMembers (int id) throws SQLException {
310 Connection conn = null;
311 conn = DriverManager.getConnection(DB_URL, USER, PASS);
312 PreparedStatement st = conn.prepareStatement("DELETE FROM PC_Members WHERE pc_id = ?");
313 st.setInt(1, id);
314 st.executeUpdate();
315 }
316
317 ////////////////////////////PART 2 METHODS////////////////////////////
318
319 //list papers only by Fotouhi
320 public void papersByFotouhi () throws SQLException {
321 Connection conn = null;
322 conn = DriverManager.getConnection(DB_URL, USER, PASS);
323 PreparedStatement st = conn.prepareStatement("CREATE VIEW FotouhiPapers AS SELECT * FROM Papers "
324 + "WHERE authors = 'Fotouhi'");
325 st.executeUpdate();
326 }
327
328 //list of papers where Fotouhi is the first author
329 public void papersFotouhiIsFirstAuthor () throws SQLException {
330 Connection conn = null;
331 conn = DriverManager.getConnection(DB_URL, USER, PASS);
332 PreparedStatement st = conn.prepareStatement("CREATE VIEW papersFotouhiisFirstAuthor AS SELECT paper_id, title, authors FROM Papers "
333 + "WHERE substring_index(authors,',', 1)='Fotouhi'");
334 st.executeUpdate();
335 }
336
337 //list papers co-authored by Lou and Fotouhi
338 public void papersFotouhiLouCoAuthor () throws SQLException {
339 Connection conn = null;
340 conn = DriverManager.getConnection(DB_URL, USER, PASS);
341 PreparedStatement st = conn.prepareStatement("CREATE VIEW papersLouAndFotouhiCoAuthor AS SELECT title, authors FROM Papers "
342 + "WHERE substring_index(authors,',', 1)='Fotouhi'");
343 st.executeUpdate();
344 }
345
346 //pc members with the most papers assigned
347 public void pcMemberWithmostPapersAssigned () throws SQLException {
348 Connection conn = null;
349 conn = DriverManager.getConnection(DB_URL, USER, PASS);
350 PreparedStatement st = conn.prepareStatement("CREATE VIEW papersAssigned AS " +
351 "SELECT pc_id, COUNT(*) AS num_papers_assigned " +
352 "FROM pc_members " +
353 "INNER JOIN review_reports " +
354 "USING (pc_id) " +
355 "INNER JOIN papers " +
356 "USING (paper_id) " +
357 "GROUP BY pc_id " +
358 "ORDER BY num_papers_assigned DESC " +
359 "LIMIT 1");
360 st.executeUpdate();
361 }
362
363 //pc members with no papars assigned for review
364 public void pcMemberWithNoPapersAssigned () throws SQLException {
365 Connection conn = null;
366 conn = DriverManager.getConnection(DB_URL, USER, PASS);
367 PreparedStatement st = conn.prepareStatement("CREATE VIEW noPapersAssigned AS " +
368 "SELECT (papers.paper_id + 1) AS noPapersAssigned " +
369 "FROM papers " +
370 "WHERE NOT EXISTS " +
371 "(SELECT review_reports.pc_id " +
372 "FROM review_reports " +
373 "WHERE review_reports.pc_id = papers.paper_id + 1)");
374 st.executeUpdate();
375 }
376
377 //papers rejected by Matt and John
378 public void papersRejectedByMattAndJohn () throws SQLException {
379 Connection conn = null;
380 conn = DriverManager.getConnection(DB_URL, USER, PASS);
381 PreparedStatement st = conn.prepareStatement("CREATE VIEW papersRejectedByMattAndJohn " +
382 "AS SELECT (review_reports.paper_id) AS papersRejectedByMattAndJohn " +
383 "FROM review_reports " +
384 "WHERE review_reports.final_recommendation = \"rejected\"");
385 st.executeUpdate();
386 }
387
388}//end class