· 10 years ago · Apr 21, 2016, 07:00 PM
1package io.github.neoplay.QuickSG.MySQL;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.util.UUID;
9
10import org.bukkit.entity.Player;
11
12import io.github.neoplay.QuickSG.QuickSG;
13
14public class MySQL {
15 public static String host = QuickSG.cfg.getString("MySQL.Host");
16 public static String port = QuickSG.cfg.getString("MySQL.Port");
17 public static String database = QuickSG.cfg.getString("MySQL.Datenbank");
18 public static String username = QuickSG.cfg.getString("MySQL.Benutzername");
19 public static String password = QuickSG.cfg.getString("MySQL.Passwort");
20 public static Connection connection;
21
22 public static Connection getConnection() {
23 return connection;
24 }
25
26 public static boolean isConnected() {
27 return connection != null;
28 }
29
30 public static void connect() {
31 if (!isConnected()) {
32 try {
33 connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username,
34 password);
35 } catch (SQLException ex) {
36 ex.printStackTrace();
37 }
38 }
39 }
40
41 public static void disconnect() {
42 if (isConnected()) {
43 try {
44 connection.close();
45 } catch (SQLException ex) {
46 ex.printStackTrace();
47 }
48 }
49 }
50
51 public static void createTableIfNotExists() {
52 if (isConnected()) {
53 try {
54 PreparedStatement ps = getConnection().prepareStatement(
55 "CREATE TABLE IF NOT EXISTS QuickSG_Stats_Player (Name VARCHAR(255), UUID VARCHAR(255), Kills INT(11), Deaths INT(11), Wins INT(11), Looses INT(11), Draws INT(11))");
56 ps.executeUpdate();
57 ps.close();
58 } catch (SQLException ex) {
59 ex.printStackTrace();
60 }
61 }
62 }
63
64 public static boolean isUserExisting(Player p) {
65 try {
66 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
67 ps.setString(1, p.getUniqueId().toString());
68 ResultSet result = ps.executeQuery();
69 boolean user = result.next();
70 result.close();
71 ps.close();
72 return user;
73 } catch (Exception ex) {
74 ex.printStackTrace();
75 }
76 return false;
77 }
78
79 public static boolean isUserExisting(UUID uuid) {
80 try {
81 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
82 ps.setString(1, uuid.toString());
83 ResultSet result = ps.executeQuery();
84 boolean user = result.next();
85 result.close();
86 ps.close();
87 return user;
88 } catch (Exception ex) {
89 ex.printStackTrace();
90 }
91 return false;
92 }
93
94 public static void registerPlayer(Player p) {
95 if (isUserExisting(p)) {
96 return;
97 }
98 try {
99 PreparedStatement ps = connection.prepareStatement(
100 "INSERT INTO QuickSG_Stats_Player (Spielername, UUID, Kills, Deaths, Wins, Looses, Draws) VALUES (?, ?, ?, ?, ?, ?, ?)");
101 ps.setString(1, p.getName());
102 ps.setString(2, p.getUniqueId().toString());
103 ps.setInt(3, 0);
104 ps.setInt(4, 0);
105 ps.setInt(5, 0);
106 ps.setInt(6, 0);
107 ps.setInt(7, 0);
108 ps.execute();
109 ps.close();
110 } catch (Exception ex) {
111 ex.printStackTrace();
112 }
113 }
114
115 public static void setPlayerName(Player p) {
116 try {
117 PreparedStatement ps = connection.prepareStatement("UPDATE QuickSG_Stats_Player SET Name = ? WHERE UUID = ?");
118 ps.setString(1, p.getName());
119 ps.setString(2, p.getUniqueId().toString());
120 ps.executeUpdate();
121 ps.close();
122 } catch (Exception ex) {
123 ex.printStackTrace();
124 }
125 }
126
127 public static String getPlayer(UUID uuid) {
128 try {
129 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
130 ps.setString(1, uuid.toString());
131 ResultSet result = ps.executeQuery();
132 result.next();
133 String name = result.getString("Name");
134 result.close();
135 ps.close();
136 return name;
137 } catch (Exception ex) {
138 ex.printStackTrace();
139 }
140 return "null";
141 }
142
143 public static int getKills(Player p) {
144 try {
145 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
146 ps.setString(1, p.getUniqueId().toString());
147 ResultSet result = ps.executeQuery();
148 result.next();
149 int kills = result.getInt("Kills");
150 result.close();
151 ps.close();
152 return kills;
153 } catch (Exception ex) {
154 ex.printStackTrace();
155 }
156 return -1;
157 }
158
159 public static int getKills(UUID uuid) {
160 try {
161 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
162 ps.setString(1, uuid.toString());
163 ResultSet result = ps.executeQuery();
164 result.next();
165 int kills = result.getInt("Kills");
166 result.close();
167 ps.close();
168 return kills;
169 } catch (Exception ex) {
170 ex.printStackTrace();
171 }
172 return -1;
173 }
174
175 public static int getDeaths(Player p) {
176 try {
177 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
178 ps.setString(1, p.getUniqueId().toString());
179 ResultSet result = ps.executeQuery();
180 result.next();
181 int deaths = result.getInt("Deaths");
182 result.close();
183 ps.close();
184 return deaths;
185 } catch (Exception ex) {
186 ex.printStackTrace();
187 }
188 return -1;
189 }
190
191 public static int getDeaths(UUID uuid) {
192 try {
193 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
194 ps.setString(1, uuid.toString());
195 ResultSet result = ps.executeQuery();
196 result.next();
197 int deaths = result.getInt("Deaths");
198 result.close();
199 ps.close();
200 return deaths;
201 } catch (Exception ex) {
202 ex.printStackTrace();
203 }
204 return -1;
205 }
206
207 public static double getKD(Player p) {
208 try {
209 PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
210 ps1.setString(1, p.getUniqueId().toString());
211 ResultSet result1 = ps1.executeQuery();
212 result1.next();
213 double kills = result1.getInt("Kills");
214 result1.close();
215 ps1.close();
216 PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
217 ps2.setString(1, p.getUniqueId().toString());
218 ResultSet result2 = ps2.executeQuery();
219 result2.next();
220 double deaths = result2.getInt("Deaths");
221 result2.close();
222 ps2.close();
223 if ((kills == 0.0D) && (deaths == 0.0D)) {
224 return 0.0D;
225 }
226 if (deaths == 0.0D) {
227 return kills;
228 }
229 return Math.round(kills / deaths * 100.0D) / 100.0D;
230 } catch (Exception ex) {
231 ex.printStackTrace();
232 }
233 return -1.0D;
234 }
235
236 public static double getKD(UUID uuid) {
237 try {
238 PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
239 ps1.setString(1, uuid.toString());
240 ResultSet result1 = ps1.executeQuery();
241 result1.next();
242 double kills = result1.getInt("Kills");
243 result1.close();
244 ps1.close();
245 PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
246 ps2.setString(1, uuid.toString());
247 ResultSet result2 = ps2.executeQuery();
248 result2.next();
249 double deaths = result2.getInt("Deaths");
250 result2.close();
251 ps2.close();
252 if ((kills == 0.0D) && (deaths == 0.0D)) {
253 return 0.0D;
254 }
255 if (deaths == 0.0D) {
256 return kills;
257 }
258 return Math.round(kills / deaths * 100.0D) / 100.0D;
259 } catch (Exception ex) {
260 ex.printStackTrace();
261 }
262 return -1.0D;
263 }
264
265 public static int getGamesPlayed(Player p) {
266 try {
267 PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
268 ps1.setString(1, p.getUniqueId().toString());
269 ResultSet result1 = ps1.executeQuery();
270 result1.next();
271 int wins = result1.getInt("Wins");
272 result1.close();
273 ps1.close();
274 PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
275 ps2.setString(1, p.getUniqueId().toString());
276 ResultSet result2 = ps2.executeQuery();
277 result2.next();
278 int looses = result2.getInt("Looses");
279 result2.close();
280 ps2.close();
281 PreparedStatement ps3 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
282 ps3.setString(1, p.getUniqueId().toString());
283 ResultSet result3 = ps3.executeQuery();
284 result3.next();
285 int undecideds = result3.getInt("Draws");
286 result3.close();
287 ps3.close();
288 return wins + looses + undecideds;
289 } catch (Exception ex) {
290 ex.printStackTrace();
291 }
292 return -1;
293 }
294
295 public static int getGamesPlayed(UUID uuid) {
296 try {
297 PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
298 ps1.setString(1, uuid.toString());
299 ResultSet result1 = ps1.executeQuery();
300 result1.next();
301 int wins = result1.getInt("Wins");
302 result1.close();
303 ps1.close();
304 PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
305 ps2.setString(1, uuid.toString());
306 ResultSet result2 = ps2.executeQuery();
307 result2.next();
308 int looses = result2.getInt("Looses");
309 result2.close();
310 ps2.close();
311 PreparedStatement ps3 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
312 ps3.setString(1, uuid.toString());
313 ResultSet result3 = ps3.executeQuery();
314 result3.next();
315 int undecideds = result3.getInt("Draws");
316 result3.close();
317 ps3.close();
318 return wins + looses + undecideds;
319 } catch (Exception ex) {
320 ex.printStackTrace();
321 }
322 return -1;
323 }
324
325 public static int getWins(Player p) {
326 try {
327 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
328 ps.setString(1, p.getUniqueId().toString());
329 ResultSet result = ps.executeQuery();
330 result.next();
331 int wins = result.getInt("Wins");
332 result.close();
333 ps.close();
334 return wins;
335 } catch (Exception ex) {
336 ex.printStackTrace();
337 }
338 return -1;
339 }
340
341 public static int getWins(UUID uuid) {
342 try {
343 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
344 ps.setString(1, uuid.toString());
345 ResultSet result = ps.executeQuery();
346 result.next();
347 int wins = result.getInt("Wins");
348 result.close();
349 ps.close();
350 return wins;
351 } catch (Exception ex) {
352 ex.printStackTrace();
353 }
354 return -1;
355 }
356
357 public static int getLooses(Player p) {
358 try {
359 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
360 ps.setString(1, p.getUniqueId().toString());
361 ResultSet result = ps.executeQuery();
362 result.next();
363 int looses = result.getInt("Looses");
364 result.close();
365 ps.close();
366 return looses;
367 } catch (Exception ex) {
368 ex.printStackTrace();
369 }
370 return -1;
371 }
372
373 public static int getLooses(UUID uuid) {
374 try {
375 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
376 ps.setString(1, uuid.toString());
377 ResultSet result = ps.executeQuery();
378 result.next();
379 int looses = result.getInt("Looses");
380 result.close();
381 ps.close();
382 return looses;
383 } catch (Exception ex) {
384 ex.printStackTrace();
385 }
386 return -1;
387 }
388
389 public static int getUndecideds(Player p) {
390 try {
391 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
392 ps.setString(1, p.getUniqueId().toString());
393 ResultSet result = ps.executeQuery();
394 result.next();
395 int wins = result.getInt("Draws");
396 result.close();
397 ps.close();
398 return wins;
399 } catch (Exception ex) {
400 ex.printStackTrace();
401 }
402 return -1;
403 }
404
405 public static int getUndecideds(UUID uuid) {
406 try {
407 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
408 ps.setString(1, uuid.toString());
409 ResultSet result = ps.executeQuery();
410 result.next();
411 int wins = result.getInt("Draws");
412 result.close();
413 ps.close();
414 return wins;
415 } catch (Exception ex) {
416 ex.printStackTrace();
417 }
418 return -1;
419 }
420
421 public static double getWL(Player p) {
422 try {
423 PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
424 ps1.setString(1, p.getUniqueId().toString());
425 ResultSet result1 = ps1.executeQuery();
426 result1.next();
427 double wins = result1.getInt("Wins");
428 result1.close();
429 ps1.close();
430 PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
431 ps2.setString(1, p.getUniqueId().toString());
432 ResultSet result2 = ps2.executeQuery();
433 result2.next();
434 double looses = result2.getInt("Looses");
435 result2.close();
436 ps2.close();
437 if ((wins == 0.0D) && (looses == 0.0D)) {
438 return 0.0D;
439 }
440 if (looses == 0.0D) {
441 return wins;
442 }
443 return Math.round(wins / looses * 100.0D) / 100.0D;
444 } catch (Exception ex) {
445 ex.printStackTrace();
446 }
447 return -1.0D;
448 }
449
450 public static double getWL(UUID uuid) {
451 try {
452 PreparedStatement ps1 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
453 ps1.setString(1, uuid.toString());
454 ResultSet result1 = ps1.executeQuery();
455 result1.next();
456 double wins = result1.getInt("Wins");
457 result1.close();
458 ps1.close();
459 PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player WHERE UUID = ?");
460 ps2.setString(1, uuid.toString());
461 ResultSet result2 = ps2.executeQuery();
462 result2.next();
463 double looses = result2.getInt("Looses");
464 result2.close();
465 ps2.close();
466 if ((wins == 0.0D) && (looses == 0.0D)) {
467 return 0.0D;
468 }
469 if (looses == 0.0D) {
470 return wins;
471 }
472 return Math.round(wins / looses * 100.0D) / 100.0D;
473 } catch (Exception ex) {
474 ex.printStackTrace();
475 }
476 return -1.0D;
477 }
478
479 public static int getRank(Player p) {
480 int rank = -1;
481 try {
482 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player ORDER BY Wins DESC");
483 ResultSet result = ps.executeQuery();
484 while (result.next()) {
485 String uuid2 = result.getString("UUID");
486 if (uuid2.equalsIgnoreCase(p.getUniqueId().toString())) {
487 rank = result.getRow();
488 break;
489 }
490 }
491 result.close();
492 ps.close();
493 } catch (Exception ex) {
494 ex.printStackTrace();
495 }
496 return rank;
497 }
498
499 public static int getRank(UUID uuid) {
500 int rank = -1;
501 try {
502 PreparedStatement ps = connection.prepareStatement("SELECT * FROM QuickSG_Stats_Player ORDER BY Wins DESC");
503 ResultSet result = ps.executeQuery();
504 while (result.next()) {
505 String uuid2 = result.getString("UUID");
506 if (uuid2.equalsIgnoreCase(uuid.toString())) {
507 rank = result.getRow();
508 break;
509 }
510 }
511 result.close();
512 ps.close();
513 } catch (Exception ex) {
514 ex.printStackTrace();
515 }
516 return rank;
517 }
518
519 public static void addKill(Player p) {
520 try {
521 PreparedStatement ps = connection.prepareStatement("UPDATE QuickSG_Stats_Player SET Kills = ? WHERE uuid = ?");
522 ps.setInt(1, getKills(p) + 1);
523 ps.setString(2, p.getUniqueId().toString());
524 ps.executeUpdate();
525 ps.close();
526 } catch (Exception ex) {
527 ex.printStackTrace();
528 }
529 }
530
531 public static void addDeath(Player p) {
532 try {
533 PreparedStatement ps = connection.prepareStatement("UPDATE QuickSG_Stats_Player SET Deaths = ? WHERE uuid = ?");
534 ps.setInt(1, getDeaths(p) + 1);
535 ps.setString(2, p.getUniqueId().toString());
536 ps.executeUpdate();
537 ps.close();
538 } catch (Exception ex) {
539 ex.printStackTrace();
540 }
541 }
542
543 public static void addWin(Player p) {
544 try {
545 PreparedStatement ps = connection.prepareStatement("UPDATE QuickSG_Stats_Player SET Wins = ? WHERE uuid = ?");
546 ps.setInt(1, getWins(p) + 1);
547 ps.setString(2, p.getUniqueId().toString());
548 ps.executeUpdate();
549 ps.close();
550 } catch (Exception ex) {
551 ex.printStackTrace();
552 }
553 }
554
555 public static void addLoose(Player p) {
556 try {
557 PreparedStatement ps = connection.prepareStatement("UPDATE QuickSG_Stats_Player SET Looses = ? WHERE uuid = ?");
558 ps.setInt(1, getLooses(p) + 1);
559 ps.setString(2, p.getUniqueId().toString());
560 ps.executeUpdate();
561 ps.close();
562 } catch (Exception ex) {
563 ex.printStackTrace();
564 }
565 }
566
567 public static void addUndecided(Player p) {
568 try {
569 PreparedStatement ps = connection.prepareStatement("UPDATE QuickSG_Stats_Player SET Draws = ? WHERE uuid = ?");
570 ps.setInt(1, getUndecideds(p) + 1);
571 ps.setString(2, p.getUniqueId().toString());
572 ps.executeUpdate();
573 ps.close();
574 } catch (Exception ex) {
575 ex.printStackTrace();
576 }
577 }
578}