· 9 years ago · Mar 24, 2017, 12:28 AM
1/*
2 * To change this license header, choose License Headers in Project Properties.
3 * To change this template file, choose Tools | Templates
4 * and open the template in the editor.
5 */
6package poetassignment;
7
8import java.io.IOException;
9import java.sql.Connection;
10import java.sql.DriverManager;
11import java.sql.ResultSet;
12import java.sql.SQLException;
13import java.sql.Statement;
14import java.util.Scanner;
15import java.sql.*;
16
17/**
18 *
19 * @author samuel
20 */
21public class MethodClass {
22
23 public void CreateAllTables(String host, String user, String pass) throws SQLException {
24
25 try {
26 Class.forName("com.mysql.jdbc.Driver");
27 } catch (ClassNotFoundException e) {
28 System.out.println("Could not load the driver");
29 }
30
31 Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/poetdb", user, pass);
32 Statement s = conn.createStatement();
33 conn.setAutoCommit(false);
34
35 //CREATE POET TABLE
36 s.executeUpdate("create table IF NOT EXISTS POET( PoetName varchar(20) PRIMARY KEY NOT NULL, DateOfBirth date NOT NULL, PlaceOfBirth varchar(20) NOT NULL, Nationality varchar(20) NOT NULL, Language varchar(20) NOT NULL, NumRecordings int NOT NULL)");
37 System.out.println("Created table POET");
38
39 //CREATE TRANSLATOR TABLE
40 s.executeUpdate("create table IF NOT EXISTS TRANSLATOR( TranslatorName varchar(20) PRIMARY KEY NOT NULL, DateOfBirth date NOT NULL, PlaceOfBirth varchar(20) NOT NULL, Nationality varchar(20) NOT NULL, Language varchar(20) NOT NULL)");
41 System.out.println("Created table TRANSLATOR");
42
43 //CREATE POEM TABLE
44 s.executeUpdate("create table IF NOT EXISTS POEM( PoemTitle varchar(20) PRIMARY KEY NOT NULL, DatePublished date NOT NULL, Language varchar(20) NOT NULL, NumWords int NOT NULL, PoetName varchar(20) NOT NULL, FOREIGN KEY (PoetName) REFERENCES POET (PoetName))");
45 System.out.println("Created table POEM");
46
47 //CREATE TRANSLATION TABLE
48 s.executeUpdate("create table IF NOT EXISTS TRANSLATION( TranslationID int PRIMARY KEY NOT NULL, TranslatorName varchar(20) NOT NULL, PoemTitle varchar(20) NOT NULL, TranslationDate date NOT NULL, FOREIGN KEY (TranslatorName) REFERENCES TRANSLATOR (TranslatorName), FOREIGN KEY (PoemTitle) REFERENCES POEM (PoemTitle))");
49 System.out.println("Created table TRANSLATION");
50
51 //CREATE RECORDING TABLE
52 s.executeUpdate("create table IF NOT EXISTS RECORDING( RecordingID int PRIMARY KEY NOT NULL, RecordingDate date NOT NULL, Place varchar(20) NOT NULL, Duration time NOT NULL, Format varchar(10) NOT NULL, PoemTitle varchar(20) NOT NULL, PoetName varchar(20) NOT NULL, FOREIGN KEY (PoemTitle) REFERENCES POEM (PoemTitle), FOREIGN KEY (PoetName) REFERENCES POET(PoetName))");
53 System.out.println("Created table RECORDING");
54 conn.commit();
55 }
56
57 public void PopulateAllTables(String host, String user, String pass) throws SQLException {
58
59 try {
60 Class.forName("com.mysql.jdbc.Driver");
61 } catch (ClassNotFoundException e) {
62 System.out.println("Could not load the driver");
63 }
64
65 Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/poetdb", user, pass);
66 Statement s = conn.createStatement();
67 conn.setAutoCommit(false);
68
69 //POPULATE POET TABLE
70 s.executeUpdate("load data local infile 'C://files/poets.txt' into table poet LINES TERMINATED BY '\\r\\n'");
71
72 //POPULATE TRANSLATOR TABLE
73 s.executeUpdate("load data local infile 'C://files/translators.txt' into table translator LINES TERMINATED BY '\\r\\n'");
74
75 //POPULATE POEM TABLE
76 s.executeUpdate("load data local infile 'C://files/poems.txt' into table poem LINES TERMINATED BY '\\r\\n'");
77
78 //POPULATE TRANSLATION TABLE
79 s.executeUpdate("load data local infile 'C://files/translations.txt' into table translation LINES TERMINATED BY '\\r\\n'");
80
81 //POPULATE RECORDING TABLE
82 s.executeUpdate("load data local infile 'C://files/recordings.txt' into table recording LINES TERMINATED BY '\\r\\n'");
83 conn.commit();
84 }
85
86 public void CreateProcedures(String host, String user, String pass) throws SQLException {
87
88 try {
89 Class.forName("com.mysql.jdbc.Driver");
90 } catch (ClassNotFoundException e) {
91 System.out.println("Could not load the driver");
92 }
93
94 Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/poetdb", user, pass);
95 Statement s = conn.createStatement();
96 conn.setAutoCommit(false);
97
98 //CREATING SIMPLE SELECT STORED PROCEDURE (PROC1)
99 s.executeUpdate("CREATE PROCEDURE GetAllPoets() BEGIN SELECT * FROM POET; END");
100 //CREATING SELECT WITH JOIN STORED PROCEDURE (PROC2)
101 s.executeUpdate("CREATE PROCEDURE GetLanguage() BEGIN SELECT TRANSLATION.TranslationID, TRANSLATION.TranslatorName, TRANSLATION.PoemTitle, TRANSLATION.TranslationDate, TRANSLATOR.Language FROM TRANSLATION INNER JOIN TRANSLATOR ON TRANSLATION.TranslatorName=TRANSLATOR.TranslatorName; END");
102 //CREATING SELECT WITH AN AGGREGATE FUNCTION (PROC3)
103 s.executeUpdate("CREATE PROCEDURE MaxWords() BEGIN SELECT MAX(Poem.NumWords) FROM Poem; END");
104 //CREATING SELECT WITH AGGREGATE FUNCTION AND GROUP BY (PROC4)
105 s.executeUpdate("CREATE PROCEDURE NumOfTranslationsLang() BEGIN SELECT Translator.Language, COUNT(Translation.TranslatorName) AS NumberOfTranslations FROM Translation INNER JOIN TRANSLATOR ON TRANSLATION.TranslatorName=TRANSLATOR.TranslatorName GROUP BY Language; END");
106 //CREATING SELECT WITH AGGREGATE FUNCTION, GROUP BY AND HAVING CLAUSE (PROC5)
107 s.executeUpdate("CREATE PROCEDURE NumOfTranslationsLangOver2() BEGIN SELECT Translator.Language, COUNT(Translation.TranslatorName) AS NumberOfTranslations FROM Translation INNER JOIN TRANSLATOR ON TRANSLATION.TranslatorName=TRANSLATOR.TranslatorName GROUP BY Language HAVING NumberOfTranslations>2; END");
108 //CREATING UNCORRELATED NESTED SELECT (PROC6)
109 s.executeUpdate("CREATE PROCEDURE UncorrelatedSelect() BEGIN SELECT * FROM Translation WHERE TranslatorName in(SELECT TranslatorName FROM Translator WHERE year(DateOfBirth)='1989'); END");
110 //CREATING CORRELATED SUBQUERY (PRO7)
111 s.executeUpdate("CREATE PROCEDURE CorrelatedSubquery() BEGIN SELECT t.TranslatorName, tt.TranslationDate FROM Translator t JOIN Translation tt ON t.TranslatorName = tt.TranslatorName WHERE TranslationDate = (SELECT MAX(TranslationDate) FROM Translation ttt WHERE ttt.TranslatorName = t.TranslatorName); END");
112
113 conn.commit();
114 }
115
116 public void CallProcedures(String host, String user, String pass) throws SQLException {
117
118 try {
119 Class.forName("com.mysql.jdbc.Driver");
120 } catch (ClassNotFoundException e) {
121 System.out.println("Could not load the driver");
122 }
123
124 Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/poetdb", user, pass);
125 Statement s = conn.createStatement();
126 conn.setAutoCommit(false);
127
128 ResultSet result;
129 CallableStatement stmt;
130 String proc1, proc2, proc3, proc4, proc5, proc6, proc7;
131
132//PRINTING RESULTSET OF PROCEDURES
133 proc1 = "{CALL poetdb.GetAllPoets()}";
134 proc2 = "{CALL poetdb.GetLanguage()}";
135 proc3 = "{CALL poetdb.MaxWords()}";
136 proc4 = "{CALL poetdb.NumOfTranslationsLang()}";
137 proc5 = "{CALL poetdb.NumOfTranslationsLangOver2()}";
138 proc6 = "{CALL poetdb.UncorrelatedSelect()}";
139 proc7 = "{CALL poetdb.CorrelatedSubquery()}";
140
141 stmt = conn.prepareCall(proc1);
142 result = stmt.executeQuery();
143
144 System.out.println("Results: ");
145 while (result.next()) {
146 System.out.println(result.getString(1) + " " + result.getString(2) + " " + result.getString(3) + " " + result.getString(4) + " " + result.getString(5));
147 }
148
149 stmt = conn.prepareCall(proc2);
150 result = stmt.executeQuery();
151 System.out.println("Results: ");
152 while (result.next()) {
153 System.out.println(result.getString(1) + " " + result.getString(2) + " " + result.getString(3) + " " + result.getString(4) + " " + result.getString(5));
154 }
155
156 stmt = conn.prepareCall(proc3);
157 result = stmt.executeQuery();
158 System.out.println("Results: ");
159 while (result.next()) {
160 System.out.println(result.getString(1) + " words");
161 }
162
163 stmt = conn.prepareCall(proc4);
164 result = stmt.executeQuery();
165 System.out.println("Results: ");
166 while (result.next()) {
167 System.out.println(result.getString(1) + " " + result.getString(2));
168 }
169
170 stmt = conn.prepareCall(proc5);
171 result = stmt.executeQuery();
172 System.out.println("Results: ");
173 while (result.next()) {
174 System.out.println(result.getString(1) + " " + result.getString(2));
175 }
176
177 stmt = conn.prepareCall(proc6);
178 result = stmt.executeQuery();
179 System.out.println("Results: ");
180 while (result.next()) {
181 System.out.println(result.getString(1) + " " + result.getString(2) + " " + result.getString(3) + " " + result.getString(4));
182 }
183
184 stmt = conn.prepareCall(proc7);
185 result = stmt.executeQuery();
186 System.out.println("Results: ");
187 while (result.next()) {
188 System.out.println(result.getString(1) + " " + result.getString(2));
189 }
190
191 conn.commit();
192 }
193}