· 8 years ago · Nov 16, 2017, 12:46 PM
1
2import java.io.BufferedReader;
3import java.io.File;
4import java.io.FileReader;
5import java.io.IOException;
6import java.sql.*;
7import java.text.DateFormat;
8import java.text.ParseException;
9import java.text.SimpleDateFormat;
10import java.util.Scanner;
11import java.io.Console;
12
13public class Chess {
14
15 private static String user = "";
16 private static String pass = "";
17 private static String host = "";
18 private static String database = "";
19 private static StringBuilder sb = new StringBuilder();
20 private static Scanner userInput = new Scanner(System.in);
21 private static String tblName = "";
22 private static Connection conn;
23
24 public static void main(String[] args)
25 throws SQLException {
26 // the following statement loads the MySQL jdbc driver
27 // make sure it is in the CLASSPATH
28
29 try {
30 Class.forName("com.mysql.jdbc.Driver");
31 } catch (ClassNotFoundException e) {
32 System.out.println("Could not load the driver");
33 }
34
35 try {
36 // connect db
37
38 connectDatabaseMySql(user, pass, host, database);
39 Statement s = conn.createStatement();
40
41 createPlayerTable(s);
42 createClubTable(s);
43 createGameTable(s);
44 createMatchTable(s);
45
46 createMenu();
47
48 } catch (Exception e) {
49 System.out.println("Error: " + e.toString());
50 }
51 conn.commit();
52 conn.close();
53 }
54
55 public static void connectDatabaseMySql(String user, String pass, String host, String database) throws SQLException {
56
57 try {
58
59 Scanner sc = new Scanner(System.in);
60 System.out.println("Type userid");
61 user = sc.next();
62 System.out.println("Type password");
63 pass = sc.next();
64 System.out.println("Type hostname");
65 host = sc.next();
66 System.out.println("Type Database Name");
67 database = sc.next();
68
69 System.out.println("Username:" + user + ", Password: " + pass + ", Hostname: " + host + ", Database: " + database);
70
71 } catch (Exception e) {
72 System.out.println("Error: " + e.toString());
73 }
74 conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/chess", user, pass);
75 conn.setAutoCommit(false);
76 }
77
78 public static void createDatabase(Statement s) throws SQLException {
79
80 String sql = ("CREATED DATABASE CHESS SUCCESSFULLY");
81 System.out.println("Database created successfully");
82
83 s.executeUpdate(sql);
84
85 }
86
87 public static void createPlayerTable(Statement s) throws Exception {
88 String line = "";
89 String tokens[];
90 String data_file = "src//Player.txt";
91
92 s.execute("drop table if exists player");
93
94 String sql = "CREATE TABLE IF NOT EXISTS Player (PlayerName VARCHAR (20) PRIMARY KEY, DateOfBirth VARCHAR(20), FIDERating INT not null, FIDETitle VARCHAR(30), ClubName VARCHAR(20))";
95 s.execute(sql);
96
97 String insertPlayer = "insert into player values (?,?,?,?,?)";
98 PreparedStatement p = conn.prepareStatement(insertPlayer);
99
100 System.out.println("Created Player Table");
101
102 try {
103
104 File inputFile = new File(data_file);
105 FileReader inf = new FileReader(inputFile);
106 BufferedReader inb = new BufferedReader(inf);
107 System.out.println("Ready to read line");
108 line = inb.readLine(); // read a line
109
110 while ((line != null)) {
111 tokens = line.split(","); // split line into ‘,’
112 // separated tokens
113 System.out.println(tokens[0] + " " + tokens[1]
114 + " " + tokens[2] + " " + tokens[3] + " "
115 + tokens[4] + " ");
116
117 //Should trim leading/trailing spaces from tokens.
118 p.setString(1, tokens[0]);
119 p.setString(2, tokens[1]);
120 p.setInt(3, Integer.parseInt(tokens[2]));
121 p.setString(4, tokens[3]);
122 p.setString(5, tokens[4]);
123
124 p.execute();
125 p.clearParameters();
126
127 line = inb.readLine(); //read next line
128
129 }
130 inb.close();
131 inf.close();
132
133 } catch (IOException e) {
134 System.out.println("Error: " + e.toString());
135 }
136
137 }
138
139 public static void createClubTable(Statement s) throws Exception {
140 String line = "";
141 String tokens[];
142 String data_file = "src//Club.txt";
143
144 s.execute("drop table if exists club");
145
146 String sql = "CREATE TABLE IF NOT EXISTS Club (ClubName VARCHAR(20) PRIMARY KEY, Address VARCHAR(40), DateFormed VARCHAR(10))";
147 s.executeUpdate(sql);
148
149 String insertClub = "insert into club values (?,?,?)";
150 PreparedStatement p = conn.prepareStatement(insertClub);
151
152 System.out.println("Created Club Table");
153
154 try {
155
156 File inputFile = new File(data_file);
157 FileReader inf = new FileReader(inputFile);
158 BufferedReader inb = new BufferedReader(inf);
159 System.out.println("Ready to read line");
160 line = inb.readLine(); // read a line
161
162 while ((line != null)) {
163 tokens = line.split(","); // split line into ‘,’
164 // separated tokens
165 System.out.println(tokens[0] + " " + tokens[1]
166 + " " + tokens[2] + " ");
167
168 //Should trim leading/trailing spaces from tokens.
169 p.setString(1, tokens[0]);
170 p.setString(2, tokens[1]);
171 p.setString(3, tokens[2]);
172 p.execute();
173 p.clearParameters();
174 line = inb.readLine(); //read next line
175
176 }
177 inb.close();
178 inf.close();
179
180 } catch (IOException e) {
181 System.out.println("Error:" + e.toString());
182 }
183
184 }
185
186 public static void createGameTable(Statement s) throws Exception {
187 String line = "";
188 String tokens[];
189 String data_file = "src//Game.txt";
190
191 s.execute("drop table if exists game");
192
193 String sql = "CREATE TABLE IF NOT EXISTS Game (GameID VARCHAR(20) PRIMARY KEY, DatePlayed VARCHAR(10), BoardNum TINYINT, Score VARCHAR(10), MatchID VARCHAR (10), WhitePlayer VARCHAR(20), BlackPlayer VARCHAR(20))";
194 s.executeUpdate(sql);
195
196 String insertGame = "INSERT INTO game VALUES(?,?,?,?,?,?,?)";
197 PreparedStatement p = conn.prepareStatement(insertGame);
198
199 System.out.println("Created Game Table");
200
201 try {
202
203 File inputFile = new File(data_file);
204 FileReader inf = new FileReader(inputFile);
205 BufferedReader inb = new BufferedReader(inf);
206 System.out.println("Ready to read line");
207 line = inb.readLine(); // read a line
208
209 while ((line != null)) {
210 tokens = line.split(","); // split line into ‘,’
211 // separated tokens
212 System.out.println(tokens[0] + " " + tokens[1]
213 + " " + tokens[2] + " " + tokens[3] + " "
214 + tokens[4] + " "
215 + tokens[5] + " "
216 + tokens[6] + " ");
217
218 //Should trim leading/trailing spaces from tokens.
219 p.setString(1, tokens[0]);
220 p.setString(2, tokens[1]);
221 p.setInt(3, Integer.parseInt(tokens[2]));
222 p.setString(4, tokens[3]);
223 p.setString(5, tokens[4]);
224 p.setString(6, tokens[5]);
225 p.setString(7, tokens[6]);
226
227 p.execute();
228 p.clearParameters();
229 line = inb.readLine(); //read next line
230
231 }
232 inb.close();
233 inf.close();
234
235 } catch (IOException e) {
236 System.out.println("Error: " + e.toString());
237 }
238
239 }
240
241 public static void createMatchTable(Statement s) throws Exception {
242 String line = "";
243 String tokens[];
244 String data_file = "src//TblMatch.txt";
245
246 s.execute("drop table if exists TblMatch");
247 String sql = "CREATE TABLE IF NOT EXISTS TblMatch (MatchID VARCHAR(4) PRIMARY KEY, MatchDate VARCHAR(10), Venue VARCHAR (20), Score VARCHAR(10), WinningClub VARCHAR(20), LosingClub VARCHAR (20))";
248 s.execute(sql);
249
250 String insertMatch = "insert into TblMatch values (?,?,?,?,?,?)";
251 PreparedStatement p = conn.prepareStatement(insertMatch);
252
253 System.out.println("Created TblMatch Table");
254
255 try {
256
257 File inputFile = new File(data_file);
258 FileReader inf = new FileReader(inputFile);
259 BufferedReader inb = new BufferedReader(inf);
260 System.out.println("Ready to read line");
261 line = inb.readLine(); // read a line
262
263 while ((line != null)) {
264 tokens = line.split(","); // split line into ‘,’
265 // separated tokens
266 System.out.println(tokens[0] + " " + tokens[1]
267 + " " + tokens[2] + " " + tokens[3] + " "
268 + tokens[4] + " "
269 + tokens[5] + " ");
270
271 //Should trim leading/trailing spaces from tokens.
272 p.setString(1, tokens[0]);
273 p.setString(2, tokens[1]);
274 p.setString(3, tokens[2]);
275 p.setString(4, tokens[3]);
276 p.setString(5, tokens[4]);
277 p.setString(6, tokens[5]);
278
279 p.execute();
280 p.clearParameters();
281 line = inb.readLine(); //read next line
282
283 }
284 inb.close();
285 inf.close();
286
287 } catch (IOException e) {
288 System.out.println("Error: " + e.toString());
289 }
290
291 }
292
293 public static void selectTableStatement(Connection conn, String tbl) throws Exception {
294
295 Statement s = conn.createStatement();
296 s.execute("drop procedure if exists Select_Table_qry");
297 s.execute("create procedure Select_Table_qry() BEGIN SELECT * FROM " + tbl + "; END");
298 CallableStatement cs = conn.prepareCall("{CALL Select_Table_qry()}");
299 ResultSet result = cs.executeQuery();
300
301 System.out.println("Results: ");
302
303 if(tbl.equals("player")){
304 System.out.println("Player Table shows");
305 while (result.next()) {
306 System.out.println(result.getString(1) + " " + result.getString(2)
307 + " " + result.getInt(3) + " " + result.getString(4) + " "
308 + result.getString(5));
309 }
310 }else if(tbl.equals("game")){
311 System.out.println("Game Table shows");
312 while (result.next()) {
313 System.out.println(result.getString(1) + " " + result.getString(2)
314 + " " + result.getInt(3) + " " + result.getString(4) + " "
315 + result.getString(5) + " " + result.getString(6));
316 }
317 } else if(tbl.equals("club")){
318 System.out.println("Club Table shows");
319 while (result.next()) {
320 System.out.println(result.getString(1) + " " + result.getString(2)
321 + " " + result.getString(3));
322 }
323 } else if(tbl.equals("tblmatch")){
324 System.out.println("Match Table shows");
325 while (result.next()) {
326 System.out.println(result.getString(1) + " " + result.getString(2)
327 + " " + result.getString(3) + " " + result.getString(4) + " "
328 + result.getString(5) + " " + result.getString(6));
329 }
330 }
331
332
333 }
334
335 public static void selectPlayerNameAndClub(Connection conn, String playName) throws Exception {
336 Statement s = conn.createStatement();
337 s.execute("drop procedure if exists Select_Club_qry");
338 s.execute("create procedure Select_Club_qry() BEGIN SELECT player.PlayerName, club.ClubName, club.Address, club.DateFormed\n" +
339 "FROM player\n" +
340 "LEFT JOIN club\n" +
341 "ON player.ClubName=club.ClubName\n"+
342 "WHERE PlayerName="+ "'" +playName+"'" +"; END");
343 CallableStatement cs = conn.prepareCall("{CALL Select_Club_qry()}");
344 ResultSet result = cs.executeQuery();
345
346 System.out.println("Results: ");
347
348 while (result.next()) {
349 System.out.println(result.getString(1) + " " + result.getString(2)
350 + " " + result.getString(3) + " " + result.getString(4));
351 }
352
353 }
354
355 public static void showFideOver1199(Connection conn)throws Exception{
356
357 Statement s = conn.createStatement();
358 s.execute("drop procedure if exists Select_FIDE_qry");
359 s.execute("create procedure Select_FIDE_qry() BEGIN SELECT Count(fiderating)\n" +
360 "FROM player\n" +
361 "Where fiderating > 1199"+"; END");
362 CallableStatement cs = conn.prepareCall("{CALL Select_FIDE_qry()}");
363 ResultSet result = cs.executeQuery();
364
365 System.out.println("How many players have a FIDErating greater than 1199");
366 System.out.println("Results:\n ");
367
368
369 while (result.next()) {
370 System.out.println(result.getString(1));
371 }
372
373
374 }
375
376
377 public static void showGroupByScore(Connection conn)throws Exception{
378
379 Statement s = conn.createStatement();
380 s.execute("drop procedure if exists Select_MATCHID_qry");
381 s.execute("create procedure Select_MATCHID_qry() BEGIN SELECT Count(MatchID), score\n" +
382 "FROM game\n" +
383 "GROUP BY score"+"; END");
384 CallableStatement cs = conn.prepareCall("{CALL Select_MATCHID_qry()}");
385 ResultSet result = cs.executeQuery();
386
387 while (result.next()) {
388 System.out.println("Number of Teams: " + result.getString(1) + " " + "The Score: " + result.getString(2));
389 }
390
391 }
392
393 public static void showGroupByFideratingUnder(Connection conn)throws Exception{
394
395 Statement s = conn.createStatement();
396 s.execute("drop procedure if exists Select_Under_qry");
397 s.execute("create procedure Select_Under_qry() BEGIN SELECT Count(playername), fiderating\n" +
398 "FROM player\n" +
399 "GROUP BY fiderating\n" +
400 "HAVING fiderating < 1250" + "; END");
401 CallableStatement cs = conn.prepareCall("{CALL Select_Under_qry()}");
402 ResultSet result = cs.executeQuery();
403
404 while (result.next()) {
405 System.out.println("Number of Players: " + result.getString(1) + " " + "The Fiderating: " + result.getString(2));
406 }
407
408 }
409
410 public static void showGroupAboveAverage(Connection conn)throws Exception{
411
412 Statement s = conn.createStatement();
413 s.execute("drop procedure if exists Select_Average_qry");
414 s.execute("create procedure Select_Average_qry() BEGIN SELECT playername, fiderating\n" +
415 "FROM player\n" +
416 "WHERE fiderating >(\n" +
417 "SELECT AVG(fiderating)\n" +
418 "FROM player\n" +
419 "WHERE playername = playername)" + "; END");
420 CallableStatement cs = conn.prepareCall("{CALL Select_Average_qry()}");
421 ResultSet result = cs.executeQuery();
422
423 while (result.next()) {
424 System.out.println("Player: " + result.getString(1) + " " + "The Fiderating: " + result.getString(2));
425 }
426
427 }
428
429 public static void createMenu() {
430
431 int choice = 0;
432 try {
433
434 do {
435
436 System.out.println("\nChess System"
437 + "\n1 - Stored Procedure 1 - Select a table to view"
438 + "\n2 - Stored Procedure 2 - SELECT Details of a players club"
439 + "\n3 - Stored Procedure 3 - Show fide of over 1199"
440 + "\n4 - Stored Procedure 4 - Show the number of clubs grouped by score"
441 + "\n5 - Stored Procedure 5 - Show the number of players having a fierating of under 1250"
442 + "\n6 - Stored Procedure 6 - Showing players with a Fiderating higher than the anverage"
443 + "\n7 - Stored Procedure 7 - correleted subquery"
444 + "\n8 - Exit Program\n");
445
446 System.out.println("Select which procedure you want to use");
447
448
449 System.out.println();
450
451 choice = userInput.nextInt();
452
453 switch (choice) {
454 case 1: {
455 String tbl = null;
456 System.out.println("Which table do you wish to SELECT");
457 String submenu1 = "1- Players \n 2- Club \n 3- Game\n 4- Match";
458 System.out.println(submenu1);
459 int tblChoice = userInput.nextInt();
460 switch (tblChoice) {
461 case 1:
462 tbl = "player";
463 break;
464 case 2:
465 tbl = "club";
466 break;
467 case 3:
468 tbl = "game";
469 break;
470 case 4:
471 tbl= "tblmatch";
472 break;
473 }
474 selectTableStatement(conn, tbl);
475 }
476
477 break;
478
479 case 2: {
480 String playName = null;
481 System.out.println("Which player do you wish to SELECT");
482 String submenu1 = "1- Ben \n 2- Beth \n 3- Darius\n 4- George\n 5- Peter";
483 System.out.println(submenu1);
484 int playerChoice = userInput.nextInt();
485 switch (playerChoice) {
486 case 1:
487 playName = "Ben";
488 break;
489 case 2:
490 playName = "Beth";
491 break;
492 case 3:
493 playName = "Darius";
494 break;
495 case 4:
496 playName= "George";
497 break;
498 case 5:
499 playName= "Peter";
500 break;
501 }
502
503 selectPlayerNameAndClub(conn, playName);
504
505 }
506 break;
507
508 case 3: {
509
510 showFideOver1199(conn);
511
512 }
513 break;
514
515 case 4: {
516
517 showGroupByScore(conn);
518
519 }
520 break;
521
522 case 5: {
523
524 showGroupByFideratingUnder(conn);
525
526 }
527 break;
528
529 case 6: {
530
531 showGroupAboveAverage(conn);
532
533 }
534 break;
535
536 case 7: {
537
538
539 }
540 break;
541
542 case 8: {
543 System.out.println("Exiting Program");
544 System.exit(0);
545 }
546
547 }
548
549 } while (choice != 8);
550
551 } catch (Exception e) {
552 System.out.println("Error: " + e.toString());
553 }
554 }
555}