· 8 years ago · May 12, 2018, 10:04 PM
1// Below are the imports required for the program
2
3import java.io.BufferedReader;
4import java.io.File;
5import java.io.FileReader;
6import java.io.IOException;
7import java.sql.*;
8import java.util.Scanner;
9
10public class Chess { // Main class
11
12 // List of all the variables used in the program
13 private static String user = "";
14 private static String pass = "";
15 private static String host = "";
16 private static String database = "";
17 private static StringBuilder sb = new StringBuilder();
18 private static Scanner userInput = new Scanner(System.in);
19 private static String tblName = "";
20 private static Connection conn;
21
22 public static void main(String[] args)
23 throws SQLException {
24 // the following statement loads the MySQL jdbc driver
25
26 try {
27 Class.forName("com.mysql.jdbc.Driver");
28 } catch (ClassNotFoundException e) {
29 System.out.println("Could not load the driver");
30 }
31
32 try {
33 // connect to the database
34
35 connectDatabaseMySql(user, pass, host, database);
36 Statement s = conn.createStatement();
37
38 // Below are the Methods that are used to create the sql tables
39 createClubTable(s);
40 createPlayerTable(s);
41 createMatchTable(s);
42 createGameTable(s);
43 clubTrigger();
44 createMenu();
45
46 } catch (Exception e) {
47 System.out.println("Error: " + e.toString());
48 }
49 conn.commit();
50 conn.close();
51 }
52
53 // Method used to connect to the database
54 public static void connectDatabaseMySql(String user, String pass, String host, String database) throws SQLException {
55
56 try {
57
58 // Below appears in the console for the user to type in userid, password, hostname and database name
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 //This will be displayed in the console
70 System.out.println("Username:" + user + ", Password: " + pass + ", Hostname: " + host + ", Database: " + database);
71
72 //Will throw exception if error is caught
73 } catch (Exception e) {
74 System.out.println("Error: " + e.toString());
75 }
76 //This is connecting netbeans to phpmyadmin
77 conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/chess", user, pass);
78 conn.setAutoCommit(false);
79 }
80
81
82 public static void createDatabase(Statement s) throws SQLException {
83
84 String sql = ("CREATED DATABASE CHESS SUCCESSFULLY");
85 System.out.println("Database created successfully");
86
87 s.executeUpdate(sql);
88
89 }
90
91 //Below is the code and sql statements used to create the club table
92 public static void createClubTable(Statement s) throws Exception {
93 String line = "";
94 String tokens[];//Creating the string tokens
95 //Accessing the text file
96 String data_file = "src//Club.txt";
97
98 // If the tables below already exist then they are dropped and created again
99 s.execute("drop table if exists player");
100 s.execute("drop table if exists club");
101
102 //This is the sql used to create the Club table
103 String sql = "CREATE TABLE IF NOT EXISTS Club (ClubName VARCHAR(20) NOT NULL, Address VARCHAR(40), DateFormed VARCHAR(10), PRIMARY KEY(ClubName))";
104 s.executeUpdate(sql);
105
106 String insertClub = "insert into club values (?,?,?)";
107 PreparedStatement p = conn.prepareStatement(insertClub);
108
109 System.out.println("\nCreated Club Table");
110
111 try {
112
113 File inputFile = new File(data_file);
114 FileReader inf = new FileReader(inputFile);//Making the file reader
115 BufferedReader inb = new BufferedReader(inf);//Making the buffer reader
116 System.out.println("Ready to read line");
117 line = inb.readLine(); // read a line
118
119 while ((line != null)) {
120 tokens = line.split(","); // split line into ‘,’
121 // separated tokens
122 System.out.println(tokens[0] + " " + tokens[1]
123 + " " + tokens[2] + " ");
124
125 //Should trim leading/trailing spaces from tokens.
126 p.setString(1, tokens[0]);
127 p.setString(2, tokens[1]);
128 p.setString(3, tokens[2]);
129 p.execute();
130 p.clearParameters();
131 line = inb.readLine(); //read next line
132
133 }
134 inb.close();
135 inf.close();
136
137 } catch (IOException e) {
138 System.out.println("Error:" + e.toString());//The exception that will appear if an error is caught
139 }
140 conn.commit();
141
142 }
143
144 //Below is the code and sql statements used to create the player table
145 public static void createPlayerTable(Statement s) throws Exception {
146 String line = "";
147 String tokens[];
148 // Below is used to read in the text file
149 String data_file = "src//Player.txt";
150
151 // This is the sql statement used to create the player table
152 String sql = "CREATE TABLE IF NOT EXISTS Player (PlayerName VARCHAR (20) NOT NULL, DateOfBirth VARCHAR(20), FIDERating INT not null, FIDETitle VARCHAR(30), ClubName VARCHAR(20), PRIMARY KEY(PlayerName), FOREIGN KEY(Clubname) REFERENCES Club(ClubName))";
153 s.execute(sql);
154 //Below is used to show how many variables need to be inserted in the player table
155 String insertPlayer = "insert into player values (?,?,?,?,?)";
156 PreparedStatement p = conn.prepareStatement(insertPlayer);
157
158 System.out.println("\nCreated Player Table");
159
160 try {
161
162 File inputFile = new File(data_file);
163 FileReader inf = new FileReader(inputFile);//Making the file reader
164 BufferedReader inb = new BufferedReader(inf);//Making the buffer reader
165 System.out.println("Ready to read line");
166 line = inb.readLine(); // read a line
167
168 while ((line != null)) {
169 tokens = line.split(","); // split line into ‘,’
170 // separated tokens
171 System.out.println(tokens[0] + " " + tokens[1]
172 + " " + tokens[2] + " " + tokens[3] + " "
173 + tokens[4] + " ");
174
175 //Should trim leading/trailing spaces from tokens.
176 p.setString(1, tokens[0]);
177 p.setString(2, tokens[1]);
178 p.setInt(3, Integer.parseInt(tokens[2]));
179 p.setString(4, tokens[3]);
180 p.setString(5, tokens[4]);
181
182 p.execute();
183 p.clearParameters();
184
185 line = inb.readLine(); //read next line
186
187 }
188 inb.close();
189 inf.close();
190
191 } catch (IOException e) {
192 System.out.println("Error: " + e.toString());//This is the exception that will appear if an error is caught
193 }
194
195 conn.commit();
196 }
197
198 //Below is the code and sql statements used to create the Match table
199 public static void createMatchTable(Statement s) throws Exception {
200 String line = "";
201 String tokens[];
202 // Below is used to read in the text file
203 String data_file = "src//TblMatch.txt";
204
205 // If the tables below already exist then they are dropped and created again
206 s.execute("drop table if exists Game");
207 s.execute("drop table if exists TblMatch");
208 //Below is the sql statement used to create tblMatch table
209 String sql = "CREATE TABLE IF NOT EXISTS TblMatch (MatchID VARCHAR(4) NOT NULL, MatchDate VARCHAR(10), Venue VARCHAR (20), Score VARCHAR(20), WinningClub VARCHAR(20), LosingClub VARCHAR(20),PRIMARY KEY(MatchID))";
210 s.execute(sql);
211
212 //Below is used to show how many variables need to be inserted in the player table
213 String insertMatch = "insert into TblMatch values (?,?,?,?,?,?)";
214 PreparedStatement p = conn.prepareStatement(insertMatch);
215
216 System.out.println("\nCreated TblMatch Table");
217
218 try {
219
220 File inputFile = new File(data_file);
221 FileReader inf = new FileReader(inputFile);//Making the file reader
222 BufferedReader inb = new BufferedReader(inf);//Making the buffer reader
223 System.out.println("Ready to read line");
224 line = inb.readLine(); // read a line
225
226 //while line is not null print the following
227 while ((line != null)) {
228 tokens = line.split(","); // split line into ‘,’
229 // separated tokens
230 System.out.println(tokens[0] + " " + tokens[1]
231 + " " + tokens[2] + " " + tokens[3] + " "
232 + tokens[4] + " "
233 + tokens[5] + " ");
234
235 //Should trim leading/trailing spaces from tokens.
236 p.setString(1, tokens[0]);
237 p.setString(2, tokens[1]);
238 p.setString(3, tokens[2]);
239 p.setString(4, tokens[3]);
240 p.setString(5, tokens[4]);
241 p.setString(6, tokens[5]);
242
243 p.execute();
244 p.clearParameters();
245 line = inb.readLine(); //read next line
246
247 }
248 inb.close();
249 inf.close();
250
251 } catch (IOException e) {
252 System.out.println("Error: " + e.toString());//This is the exception that will appear if an error is caught
253 }
254
255 conn.commit();
256
257 }
258
259 public static void createGameTable(Statement s) throws Exception {
260 String line = "";
261 String tokens[];
262 // Below is used to read in the text file
263 String data_file = "src//Game.txt";
264
265 //Below is the sql statement used to create the game table in the database
266 String sql = "CREATE TABLE IF NOT EXISTS Game (GameID VARCHAR(20) NOT NULL, DatePlayed VARCHAR(10), BoardNum TINYINT, Score VARCHAR(10), MatchID VARCHAR(10), WhitePlayer VARCHAR(20), BlackPlayer VARCHAR(20), PRIMARY KEY(GameID), FOREIGN KEY(MatchID) REFERENCES TblMatch(MatchID))";
267 s.execute(sql);
268
269 //Below is used to show how many variables need to be inserted in the player table
270 String insertGame = "INSERT INTO game VALUES(?,?,?,?,?,?,?)";
271 PreparedStatement p = conn.prepareStatement(insertGame);
272
273 System.out.println("\nCreated Game Table");
274
275 try {
276
277 File inputFile = new File(data_file);
278 FileReader inf = new FileReader(inputFile);//Making the file reader
279 BufferedReader inb = new BufferedReader(inf);//Making the buffer reader
280 System.out.println("Ready to read line");
281 line = inb.readLine(); // read a line
282
283 //while line is not null print the following
284 while ((line != null)) {
285 tokens = line.split(","); // split line into ‘,’
286 // separated tokens
287 System.out.println(tokens[0] + " " + tokens[1]
288 + " " + tokens[2] + " " + tokens[3] + " "
289 + tokens[4] + " "
290 + tokens[5] + " "
291 + tokens[6] + " ");
292
293 //Should trim leading/trailing spaces from tokens.
294 p.setString(1, tokens[0]);
295 p.setString(2, tokens[1]);
296 p.setInt(3, Integer.parseInt(tokens[2]));
297 p.setString(4, tokens[3]);
298 p.setString(5, tokens[4]);
299 p.setString(6, tokens[5]);
300 p.setString(7, tokens[6]);
301
302 p.execute();
303 p.clearParameters();
304 line = inb.readLine(); //read next line
305
306 }
307 inb.close();
308 inf.close();
309
310 } catch (IOException e) {
311 System.out.println("Error: " + e.toString());//This is the exception that will appear if an error is caught
312 }
313
314 conn.commit();
315
316 }
317 //The trigger below stops the user from deleting a club due to data integrity
318 public static void clubTrigger() throws SQLException {
319 StringBuilder tb = new StringBuilder();
320
321 try{
322 System.out.println("\nTrigger Created");
323 tb.append(" CREATE trigger ClubDeletion before delete on club ");
324 tb.append(" for each ROW Begin ");
325 tb.append(" declare msg varchar(140); ");
326 tb.append(" set msg = concat('TriggerError: Club can not be deleted due to the integrity restraint'); ");
327 tb.append(" signal sqlstate '45000' ");
328 tb.append(" set message_text = msg; ");
329 tb.append(" END; ");//This is the sql that was used to create the trigger
330
331 conn.createStatement().execute(tb.toString());
332
333
334
335 }
336 catch (SQLException e)
337 {
338
339 System.out.println("Error: " + e.toString());//This is the exception that will appear if an error is caught
340 }
341
342 conn.commit();
343 }
344
345 //Below is my first stored procedure, which just shows table player details
346 public static void selectTableStatement(Connection conn, String tbl) throws Exception {
347
348 Statement s = conn.createStatement();
349 s.execute("drop procedure if exists Select_Table_qry");//Drops this procdure if it extists
350 s.execute("create procedure Select_Table_qry(IN player VARCHAR(20)) BEGIN SELECT * FROM player " + "; END");//The sql statement used to show player table details, also contains IN parameters
351 CallableStatement cs = conn.prepareCall("{CALL Select_Table_qry(?)}");
352 cs.setString(1, tbl);//What I am trying to output
353 ResultSet result = cs.executeQuery();
354
355 System.out.println("Results:\n ");
356
357 System.out.println("Display the details of player table:");
358
359 //Below is how the results will be shown in the console
360 System.out.println("\nPlayer Table shows");
361 //While there is a result that applies rows will continue to be added to the results
362 while (result.next()) {
363 System.out.println(result.getString(1) + " " + result.getString(2)
364 + " " + result.getInt(3) + " " + result.getString(4) + " "
365 + result.getString(5));
366 }
367
368 }
369 //Below is my second stored procedure, this show a players name and details of the club that they belong to
370
371 public static void selectPlayerNameAndClub(Connection conn, String playName) throws Exception {
372 Statement s = conn.createStatement();
373 s.execute("drop procedure if exists Select_Club_qry");//Drops this procdure if it extists
374 s.execute("create procedure Select_Club_qry(IN player VARCHAR(20)) BEGIN SELECT player.PlayerName, club.ClubName, club.Address, club.DateFormed\n"
375 +//Select statement
376 "FROM player\n"
377 +//From statement
378 "LEFT JOIN club\n"
379 +//Left Join
380 "ON player.ClubName=club.ClubName\n"
381 + "WHERE PlayerName= player " + "; END");//The sql statement used to show player and club details, also contains IN parameters
382 CallableStatement cs = conn.prepareCall("{CALL Select_Club_qry(?)}");
383 cs.setString(1, playName);//What I am trying to output
384 ResultSet result = cs.executeQuery();
385
386 System.out.println("Results: ");
387 System.out.println("\nShow the name of the player and details of the club that they belong to:\n");
388 //Below is how the results will be shown in the console
389 while (result.next()) {//While there is a result that applies rows will continue to be added to the results
390 System.out.println(result.getString(1) + " " + result.getString(2)
391 + " " + result.getString(3) + " " + result.getString(4));
392 }
393
394 }
395
396 //Below is my third stored procedure, this shows the number of players with a FIDERating over 1199
397 public static void showFideOver1199(Connection conn, String rating) throws Exception {
398
399 Statement s = conn.createStatement();
400 s.execute("drop procedure if exists Select_FIDE_qry");//Drops this procdure if it extists
401 s.execute("create procedure Select_FIDE_qry(IN player VARCHAR(20))BEGIN SELECT Count(fiderating) \n"
402 + "FROM player\n"
403 + //From Statement (what table it has to get information from)
404 "Where fiderating > 1199" + "; END");//The sql statement used to show the number of players with FIDERating greater than 1199, also contains IN parameters
405 CallableStatement cs = conn.prepareCall("{CALL Select_FIDE_qry(?)}");
406 cs.setString(1, rating);//What I am trying to output
407 ResultSet result = cs.executeQuery();
408 //Below is how the results will be shown in the console
409 System.out.println("How many players have a FIDErating greater than 1199:\n");
410 System.out.println("Results:\n ");
411
412 while (result.next()) {//While there is a result that applies rows will continue to be added to the results
413 System.out.println(result.getString(1));
414 }
415
416 }
417
418 //Below is my fourth procedure, this shows the different scores and the number of games that had those scores
419 public static void showGroupByScore(Connection conn, String matches) throws Exception {
420
421 Statement s = conn.createStatement();
422 s.execute("drop procedure if exists Select_MATCHID_qry");//Drops this procdure if it extists
423 s.execute("create procedure Select_MATCHID_qry(IN tblMatch VARCHAR(20)) BEGIN SELECT Count(MatchID), score\n"
424 + "FROM game\n"
425 + "GROUP BY score" + "; END");//This is the sql statement that is used to show the scores and the number of games that had that score
426 CallableStatement cs = conn.prepareCall("{CALL Select_MATCHID_qry(?)}");
427 cs.setString(1, matches);//What I am trying to output
428 ResultSet result = cs.executeQuery();
429 System.out.println("Results:\n ");
430 System.out.println("\nShow the different scores and the number of games that had that score:\n");
431
432 while (result.next()) {//While there is a result that applies rows will continue to be added to the results
433
434 System.out.println("Number of games: " + result.getString(1) + " " + "The Score: " + result.getString(2));
435 }
436
437 }
438
439 //This is my fifth procedure, this is used to find the players that have a FIDERating less than 1250
440 public static void showGroupByFideratingUnder(Connection conn, String FIDE) throws Exception {
441
442 Statement s = conn.createStatement();
443 s.execute("drop procedure if exists Select_Under_qry");//Drops this procdure if it extists
444 s.execute("create procedure Select_Under_qry(IN player VARCHAR(20)) BEGIN SELECT Count(playername), fiderating\n"
445 + "FROM player\n"
446 + "GROUP BY fiderating\n"
447 + "HAVING fiderating < 1250" + "; END");//This is the sql statement that is used to find the players with a FIDERating less than 1250
448 CallableStatement cs = conn.prepareCall("{CALL Select_Under_qry(?)}");
449 cs.setString(1, FIDE);//What I am trying to output
450 ResultSet result = cs.executeQuery();
451 System.out.println("Results: ");
452 System.out.println("\nDisplay the number of players that have a FIDERating less than 1250 and their score:\n");
453
454 while (result.next()) {//While there is a result that applies rows will continue to be added to the results
455
456 System.out.println("Number of Players: " + result.getString(1) + " " + "The Fiderating: " + result.getString(2));
457 }
458
459 }
460
461 //This is my sixth procedure, this is used to find the scores of games that were not played on board 1
462 public static void selectAllBoardsAcceptBoardNum1(Connection conn, String bNum) throws Exception {
463
464 Statement s = conn.createStatement();
465 s.execute("drop procedure if exists Select_BoardNum_qry");//Drops this procdure if it extists
466 s.execute("create procedure Select_BoardNum_qry(IN game VARCHAR(20)) BEGIN SELECT gameid, score\n"
467 + "FROM game\n"
468 + "WHERE boardnum >(\n"
469 + "SELECT MIN(boardnum)\n"
470 + "FROM game , tblmatch\n"
471 + "WHERE game.matchID = tblmatch.matchID)\n" + "; END");//This is the sql statement that is used to find score of games not played on board 1
472 CallableStatement cs = conn.prepareCall("{CALL Select_BoardNum_qry(?)}");
473 cs.setString(1, bNum);//What I am trying to output
474 ResultSet result = cs.executeQuery();
475 System.out.println("Results: ");
476 System.out.println("\nShow the Score of games that werent played on board 1:\n");
477
478 while (result.next()) {//While there is a result that applies rows will continue to be added to the results
479
480 System.out.println("Game: " + result.getString(1) + " " + "Game Score: " + result.getString(2));
481 }
482
483 }
484
485 //Below is my seventh procedure, this is used to find the players that have a FIDERating greatert than the average and their FIDERatings
486 public static void showGroupAboveAverage(Connection conn, String playName) throws Exception {
487
488 Statement s = conn.createStatement();
489 s.execute("drop procedure if exists Select_Average_qry");//Drops this procdure if it extists
490 s.execute("create procedure Select_Average_qry(IN player VARCHAR(20)) BEGIN SELECT playername, fiderating\n"
491 + "FROM player\n"
492 + "WHERE fiderating >(\n"
493 + "SELECT AVG(fiderating)\n"
494 + "FROM player)\n" + "; END");//This is the sql statement that is used to find the players that have a FIDERating greater than the average FIDERating
495 CallableStatement cs = conn.prepareCall("{CALL Select_Average_qry(?)}");
496 cs.setString(1, playName);//What I am trying to output
497 ResultSet result = cs.executeQuery();
498 System.out.println("Results: ");
499 System.out.println("\nShow the players that have a FIDERating greater than the average FIDERating:\n");
500
501 while (result.next()) {//While there is a result that applies rows will continue to be added to the results
502
503 System.out.println("Player: " + result.getString(1) + " " + " Fiderating: " + result.getString(2));
504 }
505
506 }
507
508 //This is the menu that I have created to display the stored procedures that can be selected
509 public static void createMenu() {
510
511 int choice = 0;
512 try {
513
514 do {
515
516 System.out.println("\n-----------------------Chess System---------------------"
517 + "\n1 - Stored Procedure 1 - Display player table"
518 + "\n2 - Stored Procedure 2 - Show the Details of a player and the players club"
519 + "\n3 - Stored Procedure 3 - Show the number of players with FIDErating over 1199"
520 + "\n4 - Stored Procedure 4 - Show the different scores and the number of games that had those scores"
521 + "\n5 - Stored Procedure 5 - Show the number of players and their FIDERating having a FIDErating under 1250"
522 + "\n6 - Stored Procedure 6 - Showing players with a Fiderating higher than the anverage FIDERating"
523 + "\n7 - Stored Procedure 7 - Show the gameID and Score on all boards accept Boards numbered 1"
524 + "\n8 - Exit Program"
525 + "\n-----------------------Chess System---------------------");
526
527 System.out.println("Select which procedure you want to use");
528
529 System.out.println();
530
531 //The user is able to select what procedure they want to see
532 choice = userInput.nextInt();
533
534 //This is the first procedure
535 switch (choice) {
536 case 1: {
537 String tbl = null;
538
539 selectTableStatement(conn, tbl);//The name of the method for procedure 1
540 }
541
542 break;
543
544 //This is the Second procedure
545 case 2: {
546 String playName = null;
547 System.out.println("Which player do you wish to SELECT");
548 String submenu1 = "1- George\n 2- Dave\n 3- Pete\n 4- Sarah\n 5- Beth\n 6- Jordan\n 7- Andrew\n 8- Neil\n 9- Tom\n 10- Tim\n 11- John\n 12- Joel\n 13- Kerri\n 14- Joanne\n 15- Cora\n 16- Ben";
549 System.out.println(submenu1);
550 int playerChoice = userInput.nextInt();
551 //The user selects a number to display the details of one of the players club
552 switch (playerChoice) {
553 case 1:
554 playName = "George";
555 break;
556 case 2:
557 playName = "Dave";
558 break;
559 case 3:
560 playName = "Pete";
561 break;
562 case 4:
563 playName = "Sarah";
564 break;
565 case 5:
566 playName = "Beth";
567 break;
568 case 6:
569 playName = "Jordan";
570 break;
571 case 7:
572 playName = "Andrew";
573 break;
574 case 8:
575 playName = "Neil";
576 break;
577 case 9:
578 playName = "Tom";
579 break;
580 case 10:
581 playName = "Tim";
582 break;
583 case 11:
584 playName = "John";
585 break;
586 case 12:
587 playName = "Joel";
588 break;
589 case 13:
590 playName = "Kerri";
591 break;
592 case 14:
593 playName = "Joanne";
594 break;
595 case 15:
596 playName = "Cora";
597 break;
598 case 16:
599 playName = "Ben";
600 break;
601 }
602
603 selectPlayerNameAndClub(conn, playName);//The name of the method for procedure 2
604
605 }
606 break;
607
608 //This is the third procedure
609 case 3: {
610
611 String rating = null;
612
613 showFideOver1199(conn, rating);//The name of the method for procedure 3
614
615 }
616 break;
617
618 //This is the fourth procedure
619 case 4: {
620
621 String matches = null;
622
623 showGroupByScore(conn, matches);//The name of the method for procedure 4
624
625 }
626 break;
627
628 //This is the fifth procedure
629 case 5: {
630
631 String FIDE = null;
632
633 showGroupByFideratingUnder(conn, FIDE);//The name of the method for procedure 5
634
635 }
636 break;
637
638 //This is the sixth procedure
639 case 6: {
640
641 String playName = null;
642
643 showGroupAboveAverage(conn, playName);//The name of the method for procedure 6
644
645 }
646 break;
647
648 //This is the seventh procedure
649 case 7: {
650
651 String bNum = null;
652
653 selectAllBoardsAcceptBoardNum1(conn, bNum);//The name of the method for procedure 7
654
655 }
656 break;
657
658 //This is used to exit the program when the procedures have been run or when the user wnats to exit
659 case 8: {
660 System.out.println("Exiting Program");
661 System.exit(0);
662 }
663
664 }
665
666 } while (choice != 8);//this creates a loop so that the user can keep running procedures until they wish to exit/select 8
667
668 } catch (Exception e) {
669 System.out.println("Error: " + e.toString());//This is the exception that will appear if an error is caught
670 }
671 }
672}