· 5 years ago · Nov 04, 2020, 03:32 AM
1//===================main
2
3package part4_csc471;
4
5import Model.MySQL_Initialize;
6import Model.MySQL_Queries;
7import Model.DoctorData;
8import Model.PatientData;
9import java.io.File;
10import java.io.IOException;
11import java.util.logging.Level;
12import java.util.logging.Logger;
13
14/**
15 *
16 * @author szoor
17 */
18public class Main {
19
20 public static void main(String[] args) {
21
22 DoctorData doctorData = new DoctorData(new File("DataSet_2.txt"));
23 PatientData patientData = new PatientData(new File("DataSet_1.txt"));
24
25 try {
26 doctorData.clearFile();
27 } catch (IOException ex) {
28 Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
29 }
30 try {
31 patientData.clearFile();
32 } catch (IOException ex) {
33 Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
34 }
35
36 try {
37 doctorData.createFile();
38 doctorData.populateLists();
39 doctorData.displayLists();
40 doctorData.writeToFile();
41
42 patientData.createFile();
43 patientData.populateLists();
44 patientData.displayLists();
45 patientData.writeToFile();
46
47 MySQL_Initialize sql1 = new MySQL_Initialize("jdbc:mysql://localhost:3306/RandomDataset", "C:\\Users\\szoor\\OneDrive\\Documents\\NetBeansProjects\\Part4_CSC471\\DataSet_1.txt");
48 MySQL_Initialize sql2 = new MySQL_Initialize("jdbc:mysql://localhost:3306/RandomDataset", "C:\\Users\\szoor\\OneDrive\\Documents\\NetBeansProjects\\Part4_CSC471\\DataSet_2.txt");
49
50 MySQL_Initialize.dropTables();
51
52 sql2.createDoctorTable();
53 sql2.loadDoctorFileData();
54
55 sql1.createPatientTable();
56 sql1.loadPatientFileData();
57
58 MySQL_Queries.select("doctor");
59 MySQL_Queries.select("patient");
60 MySQL_Queries.setForeignKey();
61 // MySQL_Queries.joinTables();
62
63 } catch (Exception ex) {
64 Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
65 }
66
67 }
68
69}
70
71
72//============================
73
74package Model;
75
76import java.sql.Connection;
77import java.sql.DriverManager;
78import java.sql.PreparedStatement;
79import java.sql.ResultSet;
80import java.sql.SQLException;
81import java.sql.Statement;
82import java.util.logging.Level;
83import java.util.logging.Logger;
84
85/**
86 *
87 * @author szoor
88 */
89public class MySQL_Queries extends MySQL_Initialize {
90
91 public static void setForeignKey() {
92 try (Connection con = DriverManager.getConnection(url, user, password)) {
93 String setFK = "ALTER TABLE patient ADD FOREIGN KEY (doctor_id) REFERENCES doctor(id)";
94 PreparedStatement addFK = con.prepareStatement(setFK);
95 addFK.executeUpdate();
96 if (addFK != null) {
97 System.out.println("Foreign key 'doctor_id' referencing 'doctor(id)' has been added");
98
99 }
100
101 } catch (SQLException ex) {
102 Logger.getLogger(MySQL_Queries.class.getName()).log(Level.SEVERE, null, ex);
103 }
104 }
105
106 public static void joinTables() {
107 try (Connection con = DriverManager.getConnection(url, user, password)) {
108 Statement stmt = con.createStatement();
109
110 String query = "SELECT * FROM user INNER JOIN playercharacter ON user.userid=playercharacter.userid WHERE user.age=playercharacter.level;";
111 ResultSet rs = stmt.executeQuery(query);
112 int cols = rs.getMetaData().getColumnCount();
113 while (rs.next()) {
114 String s = "";
115 for (int i = 1; i <= cols; i++) {
116 s += (rs.getMetaData().getColumnName(i) + " : " + rs.getString(i) + "\t");
117 }
118 System.out.println(s);
119 }
120 System.out.println("Joined tables");
121 con.close();
122
123 } catch (SQLException ex) {
124 System.out.println(ex);
125 ex.printStackTrace();
126 }
127 }
128
129 public static void select(String table) throws ClassNotFoundException, SQLException {
130
131 Class.forName("com.mysql.cj.jdbc.Driver");
132 Connection con = DriverManager.getConnection(url, user, password);
133 Statement stmt = con.createStatement();
134
135 // SELECT query
136 String query = "SELECT * FROM " + table;
137
138 ResultSet selectSet = stmt.executeQuery(query);
139 int results = 0;
140
141 if (table.equals("patient")) {
142 while (selectSet.next()) {
143 results++;
144 System.out.println("ID: " + selectSet.getString(1));
145 System.out.println("DOCTOR_ID: " + selectSet.getString(2));
146 System.out.println("AGE: " + selectSet.getString(3));
147 System.out.println("SEX: " + selectSet.getString(4));
148 System.out.println("\n--------------------------\n");
149 }
150 } else if (table.equals("doctor")) {
151 while (selectSet.next()) {
152 System.out.println("ID: " + selectSet.getString(1));
153 System.out.println("PATIENT_ID: " + selectSet.getString(2));
154 System.out.println("DEGREE: " + selectSet.getString(3));
155 System.out.println("SEX: " + selectSet.getString(4));
156 System.out.println("\n--------------------------\n");
157 }
158 }
159 selectSet.close();
160
161 con.close();
162 }
163}
164
165
166//====================================
167
168
169package Model;
170
171import part4_csc471.*;
172import java.io.BufferedReader;
173import java.io.File;
174import java.io.FileReader;
175import java.sql.*;
176import java.util.ArrayList;
177import java.util.logging.Level;
178import java.util.logging.Logger;
179
180public class MySQL_Initialize {
181
182 protected static String url = "";
183 protected static String user = "root";
184 protected static String password = "2916";
185 protected String filePath;
186 private static boolean patientTableExists = true;
187 private static boolean doctorTableExists = true;
188
189 public MySQL_Initialize() {
190
191 }
192
193 public MySQL_Initialize(String url, String filePath) {
194 this.url = url;
195 this.filePath = filePath;
196 //pass database address (url) and text file path (filePath)
197 }
198
199 public static void dropTables() {
200
201 //drop tables before creating again
202 try (Connection con = DriverManager.getConnection(url, user, password)) {
203
204 if (patientTableExists) {
205 String dropPatient = "DROP TABLE patient";
206 PreparedStatement drop1 = con.prepareStatement(dropPatient);
207 drop1.executeUpdate();
208 //set table exists to false
209 patientTableExists = false;
210 System.out.println("Table 'patient' dropped");
211 } else {
212 System.out.println("'patient' table does not exist - cannot drop");
213 }
214 if (doctorTableExists) {
215 String dropDoctor = "DROP TABLE doctor";
216 PreparedStatement drop2 = con.prepareStatement(dropDoctor);
217 drop2.executeUpdate();
218 doctorTableExists = false;
219
220 System.out.println("Table 'doctor' dropped");
221 } else {
222 System.out.println("'doctor' table does not exist - cannot drop");
223 }
224
225 con.close();
226 } catch (SQLException ex) {
227 Logger.getLogger(MySQL_Initialize.class.getName()).log(Level.SEVERE, null, ex);
228 }
229
230 }
231
232 public void createPatientTable() throws Exception {
233
234 try (Connection con = DriverManager.getConnection(this.url, this.user, this.password)) {
235 patientTableExists = false;
236 if (!patientTableExists) {
237 String createSQL = "CREATE TABLE patient(id varchar(10) NOT NULL PRIMARY KEY, doctor_id varchar(10), age varchar(10), sex varchar(10))";
238 PreparedStatement create = con.prepareStatement(createSQL);
239 create.executeUpdate();
240 patientTableExists = true;
241 // create marker
242 System.out.println("Table 'patient' was created");
243 con.close();
244
245 } else {
246 System.out.println("Table 'patient' already exists");
247 }
248 } catch (SQLException ex) {
249 Logger.getLogger(MySQL_Initialize.class.getName()).log(Level.SEVERE, null, ex);
250
251 }
252
253 }
254
255 public void createDoctorTable() throws Exception {
256
257 try (Connection con = DriverManager.getConnection(this.url, this.user, this.password)) {
258 doctorTableExists = false;
259 if (!doctorTableExists) {
260 String createSQL = "CREATE TABLE doctor(id varchar(10) NOT NULL PRIMARY KEY, patient_id varchar(10), degree varchar(10), sex varchar(10))";
261 PreparedStatement create = con.prepareStatement(createSQL);
262 create.executeUpdate();
263 doctorTableExists = true;
264 // create marker
265 System.out.println("Table 'doctor' was created");
266 con.close();
267
268 } else {
269 System.out.println("Table 'doctor' already exists");
270 }
271 } catch (SQLException ex) {
272 Logger.getLogger(MySQL_Initialize.class.getName()).log(Level.SEVERE, null, ex);
273 }
274
275 }
276
277 public void insertPatient(String id, String doctor_id, String age, String sex) throws ClassNotFoundException, Exception {
278
279
280 try (Connection con = DriverManager.getConnection(this.url, this.user, this.password)) {
281 Class.forName("com.mysql.cj.jdbc.Driver");
282 if (con != null) {
283 // connect successful
284 }
285 String querySetLimit = "SET GLOBAL max_allowed_packet=104857600;"; //10 MB
286 Statement stSetLimit = con.createStatement();
287 stSetLimit.execute(querySetLimit);
288 Statement insertSQL = con.createStatement();
289 // Insert data
290 String insert = "INSERT INTO patient values('" + id + "', '" + doctor_id + "', '" + age + "', '" + sex + "')";
291 insertSQL.executeUpdate(insert);
292
293 con.close();
294
295 } catch (SQLException ex) {
296 Logger.getLogger(MySQL_Initialize.class.getName()).log(Level.SEVERE, null, ex);
297 }
298 }
299
300//called by load doctor file data
301 public void insertDoctor(String id, String patient_id, String degree, String sex) throws ClassNotFoundException, Exception {
302
303 try (Connection con = DriverManager.getConnection(this.url, this.user, this.password)) {
304 Class.forName("com.mysql.cj.jdbc.Driver");
305 if (con != null) {
306 // connect successful
307 }
308 String querySetLimit = "SET GLOBAL max_allowed_packet=104857600;"; //10 MB
309 Statement stSetLimit = con.createStatement();
310 stSetLimit.execute(querySetLimit);
311 Statement insertSQL = con.createStatement();
312 // Insert data
313 String insert = "INSERT INTO doctor values('" + id + "', '" + patient_id + "', '" + degree + "', '" + sex + "')";
314 insertSQL.executeUpdate(insert);
315
316 con.close();
317
318 } catch (SQLException ex) {
319 Logger.getLogger(MySQL_Initialize.class.getName()).log(Level.SEVERE, null, ex);
320 }
321 }
322
323//called by load patient file data
324 public void loadPatientFileData() throws Exception {
325
326 int count = 0;
327 ArrayList<String[]> dataSet = getFileData();
328 for (String[] data : dataSet) {
329 insertPatient(data[0], data[1], data[2], data[3]);
330 count++;
331 if (count > 2005) {
332 System.out.println("'patient' txt file was not cleared properly, manually delete and try again - if done that and error persists recheck logic");
333 System.exit(0);
334 }
335 System.out.println("Loading patient data... " + count + "/2000");
336 if (count == 2000) {
337 System.out.println("Load patient file data has completed.");
338 }
339 }
340 }
341
342 public void loadDoctorFileData() throws Exception {
343 int count = 0;
344 ArrayList<String[]> dataSet = getFileData();
345 for (String[] data : dataSet) {
346 insertDoctor(data[0], data[1], data[2], data[3]);
347 count++;
348 if (count > 2005) {
349 System.out.println("'doctor' txt file was not cleared properly, manually delete and try again");
350 System.exit(0);
351 }
352 System.out.println("Loading doctor data... " + count + "/2000");
353 if (count == 2000) {
354 System.out.println("Load doctor file data has completed.");
355 }
356 }
357 }
358
359 public ArrayList<String[]> getFileData() throws Exception {
360
361 ArrayList<String[]> dataSet;
362 try (BufferedReader br = new BufferedReader(new FileReader(this.filePath))) {
363 dataSet = new ArrayList<>();
364 String str;
365 while ((str = br.readLine()) != null) {
366 String[] data = str.replaceAll("\\s+", "").split(",");
367 dataSet.add(data);
368 }
369 }
370 return dataSet;
371 }
372
373}
374