· 6 years ago · Apr 14, 2019, 12:32 AM
1public static void makePlayerDB(Connection conn, Statement stmt) throws FileNotFoundException{
2
3 String tmp;
4 String[] var;
5
6 File players = new File("players.csv");
7 BufferedReader pbr = new BufferedReader(new FileReader(players));
8 StringBuilder players_query = new StringBuilder("INSERT INTO Players VALUES");
9
10 File teams = new File("teams.csv");
11 BufferedReader teambr = new BufferedReader(new FileReader(teams));
12 StringBuilder teams_query = new StringBuilder("INSERT INTO Teams VALUES");
13
14 File members = new File("members.csv");
15 BufferedReader mbr = new BufferedReader(new FileReader(members));
16 StringBuilder members_query = new StringBuilder("INSERT INTO Members VALUES");
17
18 File tourny = new File("tournaments.csv");
19 BufferedReader tourbr = new BufferedReader(new FileReader(tourny));
20 StringBuilder tourny_query = new StringBuilder("INSERT INTO Tournaments VALUES");
21
22 File matches = new File("matches_v2.csv");
23 BufferedReader matchbr = new BufferedReader(new FileReader(matches));
24 StringBuilder matches_query = new StringBuilder("INSERT INTO Matches VALUES");
25
26 File earnings = new File("earnings.csv");
27 BufferedReader ebr = new BufferedReader(new FileReader(earnings));
28 StringBuilder earnings_query = new StringBuilder("INSERT INTO Earnings VALUES");
29
30 try {
31 // Create database
32 String sqlString = "DROP DATABASE IF EXISTS PlayerDB_Assign4";
33 stmt.executeUpdate(sqlString);
34 sqlString = "CREATE DATABASE IF NOT EXISTS PlayerDB_Assign4";
35 stmt.executeUpdate(sqlString);
36 System.out.println("Database created.");
37
38 // Use database
39 sqlString = "USE PlayerDB_Assign4";
40 stmt.executeUpdate(sqlString);
41
42 /*
43 Creates Players, Teams, Members, Tournaments, Matches, Earnings tables
44 Inserts data into them
45 */
46 sqlString = "CREATE TABLE Players(player_id INT UNSIGNED, " +
47 "tag VARCHAR(255), " +
48 "real_name VARCHAR(255), " +
49 "nationality VARCHAR(255), " +
50 "birthday DATE, " +
51 "game_race VARCHAR(255), " +
52 "PRIMARY KEY (player_id))";
53 stmt.executeUpdate(sqlString);
54
55 while((tmp = pbr.readLine()) != null) {
56 var = (tmp + " ").split(",");
57 for(int i = 0; i < var.length; i++) {
58 if (var[i].trim().equals(""))
59 var[i] = "NULL";
60 }
61 tmp = "(" + String.join(",", var) + "),";
62 players_query.append(tmp);
63 }
64 players_query.deleteCharAt(players_query.length() - 1);
65
66 sqlString = players_query.toString();
67 stmt.executeUpdate(sqlString);
68
69 sqlString = "CREATE TABLE Teams(team_id INT UNSIGNED, " +
70 "name VARCHAR(255), " +
71 "founded DATE, " +
72 "disbanded DATE, " +
73 "PRIMARY KEY (team_id))";
74 stmt.executeUpdate(sqlString);
75
76 while((tmp = teambr.readLine()) != null) {
77 var = (tmp + " ").split(",");
78 for(int i = 0; i < var.length; i++) {
79 if (var[i].trim().equals(""))
80 var[i] = "NULL";
81 }
82 tmp = "(" + String.join(",", var) + "),";
83 teams_query.append(tmp);
84 }
85 teams_query.deleteCharAt(teams_query.length() - 1);
86
87 sqlString = teams_query.toString();
88 stmt.executeUpdate(sqlString);
89
90 sqlString = "CREATE TABLE Members(player INT UNSIGNED, " +
91 "team INT UNSIGNED, " +
92 "start_date DATE, " +
93 "end_date DATE, " +
94 "PRIMARY KEY (player, start_date))";
95 stmt.executeUpdate(sqlString);
96
97 while((tmp = mbr.readLine()) != null) {
98 var = (tmp + " ").split(",");
99 for(int i = 0; i < var.length; i++) {
100 if (var[i].trim().equals(""))
101 var[i] = "NULL";
102 }
103 tmp = "(" + String.join(",", var) + "),";
104 members_query.append(tmp);
105 }
106 members_query.deleteCharAt(members_query.length() - 1);
107
108 sqlString = members_query.toString();
109 stmt.executeUpdate(sqlString);
110
111 sqlString = "CREATE TABLE Tournaments(tournament_id INT UNSIGNED, " +
112 "name VARCHAR(255), " +
113 "region VARCHAR(255), " +
114 "major BOOLEAN, " +
115 "PRIMARY KEY (tournament_id))";
116 stmt.executeUpdate(sqlString);
117
118 while((tmp = tourbr.readLine()) != null) {
119 var = (tmp + " ").split(",");
120 for(int i = 0; i < var.length; i++) {
121 if (var[i].trim().equals(""))
122 var[i] = "NULL";
123 }
124 tmp = "(" + String.join(",", var) + "),";
125 tourny_query.append(tmp);
126 }
127 tourny_query.deleteCharAt(tourny_query.length() - 1);
128
129 sqlString = tourny_query.toString();
130 stmt.executeUpdate(sqlString);
131
132 sqlString = "CREATE TABLE Matches(match_id INT UNSIGNED, " +
133 "date DATE, " +
134 "tournament INT UNSIGNED, " +
135 "playerA INT UNSIGNED, " +
136 "playerB INT UNSIGNED, " +
137 "scoreA INT UNSIGNED, " +
138 "scoreB INT UNSIGNED, " +
139 "offline BOOLEAN, " +
140 "PRIMARY KEY (match_id))";
141 stmt.executeUpdate(sqlString);
142
143 while((tmp = matchbr.readLine()) != null) {
144 var = (tmp + " ").split(",");
145 for(int i = 0; i < var.length; i++) {
146 if (var[i].trim().equals(""))
147 var[i] = "NULL";
148 }
149 tmp = "(" + String.join(",", var) + "),";
150 matches_query.append(tmp);
151 }
152 matches_query.deleteCharAt(matches_query.length() - 1);
153
154 sqlString = matches_query.toString();
155 stmt.executeUpdate(sqlString);
156
157 sqlString = "CREATE TABLE Earnings(tournament INT UNSIGNED, " +
158 "player INT UNSIGNED, " +
159 "prize_money INT UNSIGNED, " +
160 "position INT UNSIGNED, " +
161 "PRIMARY KEY (tournament, player))";
162 stmt.executeUpdate(sqlString);
163
164 while((tmp = ebr.readLine()) != null) {
165 var = (tmp + " ").split(",");
166 for(int i = 0; i < var.length; i++) {
167 if (var[i].trim().equals(""))
168 var[i] = "NULL";
169 }
170 tmp = "(" + String.join(",", var) + "),";
171 earnings_query.append(tmp);
172 }
173 earnings_query.deleteCharAt(earnings_query.length() - 1);
174
175 sqlString = earnings_query.toString();
176 stmt.executeUpdate(sqlString);
177
178 System.out.println("Tables created");
179
180 /*
181 sqlString = "SELECT * FROM Employees";
182 ResultSet myRS = stmt.executeQuery(sqlString);
183
184 while(myRS.next()){
185 System.out.println("SSN is: " + myRS.getString("emp_id") +
186 ", First name is: " + myRS.getString("first_name") +
187 ", Last name is: " + myRS.getString("last_name"));
188 }
189
190 stmt = myConn.createStatement();
191 sqlString = "DROP DATABASE PlayerDB_Assign4";
192 stmt.executeUpdate(sqlString);
193 System.out.println("Database dropped.");
194 stmt.close();
195 */
196 }catch(SQLException se){
197 System.out.println("SQL Exception");
198 se.printStackTrace();
199 } catch(Exception e){
200 e.printStackTrace();
201 }
202 }