· 6 years ago · Jun 19, 2019, 10:00 PM
1package nycuro.database;
2
3import cn.nukkit.scheduler.AsyncTask;
4import com.zaxxer.hikari.HikariConfig;
5import com.zaxxer.hikari.HikariDataSource;
6import it.unimi.dsi.fastutil.ints.*;
7import nycuro.API;
8import nycuro.Loader;
9import nycuro.database.objects.ProfileFactions;
10import nycuro.database.objects.ProfileProxy;
11
12import java.sql.Connection;
13import java.sql.PreparedStatement;
14import java.sql.ResultSet;
15import java.sql.SQLException;
16import java.util.HashMap;
17import java.util.Map;
18import java.util.function.Consumer;
19
20public class Database {
21
22 public static Int2ObjectMap<String> scoreboardcoinsName = new Int2ObjectOpenHashMap<>();
23 public static Int2DoubleMap scoreboardcoinsValue = new Int2DoubleOpenHashMap();
24 public static Int2ObjectMap<String> scoreboardkillsName = new Int2ObjectOpenHashMap<>();
25 public static Int2IntMap scoreboardkillsValue = new Int2IntOpenHashMap();
26 public static Int2ObjectMap<String> scoreboarddeathsName = new Int2ObjectOpenHashMap<>();
27 public static Int2IntMap scoreboarddeathsValue = new Int2IntOpenHashMap();
28 public static Int2ObjectMap<String> scoreboardtimeName = new Int2ObjectOpenHashMap<>();
29 public static Int2LongMap scoreboardtimeValue = new Int2LongOpenHashMap();
30 public static Int2ObjectMap<String> scoreboardvotesName = new Int2ObjectOpenHashMap<>();
31 public static Int2IntMap scoreboardvotesValue = new Int2IntOpenHashMap();
32 public static Map<String, ProfileProxy> profileProxy = new HashMap<>();
33 public static Map<String, ProfileFactions> profileFactions = new HashMap<>();
34 private static HikariDataSource DATASOURCE_PROXY;
35 private static HikariDataSource DATASOURCE_FACTIONS;
36
37 public static void connectToDatabaseHub() {
38 String address = "hosting3.gazduirejocuri.ro";
39 String name = "chzoneeu_proxy";
40 String username = "chzoneeu_nycu";
41 String password = "unprost2019";
42
43 HikariConfig config = new HikariConfig();
44 config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
45 config.addDataSourceProperty("serverName", address);
46 config.addDataSourceProperty("port", "3306");
47 config.addDataSourceProperty("databaseName", name);
48 config.addDataSourceProperty("user", username);
49 config.addDataSourceProperty("password", password);
50 DATASOURCE_PROXY = new HikariDataSource(config);
51
52 DATASOURCE_PROXY.setMaximumPoolSize(10);
53
54 String query = "create table if not exists dates (`name` varchar(20), `language` int, `gems` REAL, `time` INTEGER, `votes` INTEGER)";
55
56 try (Connection connection = DATASOURCE_PROXY.getConnection();
57 PreparedStatement preparedStatement = connection.prepareStatement(query)) {
58 preparedStatement.executeUpdate();
59 } catch (SQLException e) {
60 e.printStackTrace();
61 }
62 }
63
64 public static void addDatesPlayerHub(String name) {
65 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
66 @Override
67 public void onRun() {
68 try (Connection connection = DATASOURCE_PROXY.getConnection();
69 PreparedStatement preparedStatement =
70 connection.prepareStatement("SELECT * from `dates` WHERE `name` =?")) {
71 preparedStatement.setString(1, name);
72 try (ResultSet resultSet = preparedStatement.executeQuery()) {
73 while (resultSet.next()) {
74 profileProxy.put(name, new ProfileProxy(
75 resultSet.getString("name"),
76 resultSet.getInt("language"),
77 resultSet.getDouble("gems"),
78 resultSet.getLong("time"),
79 resultSet.getInt("votes")
80 ));
81 }
82 }
83 } catch (SQLException e) {
84 e.printStackTrace();
85 }
86 }
87 });
88 }
89
90 public void playerExist(String name, Consumer<Boolean> consumer) {
91 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
92 @Override
93 public void onRun() {
94 try (Connection connection = DATASOURCE_PROXY.getConnection();
95 PreparedStatement preparedStatement =
96 connection.prepareStatement("SELECT * from `dates` WHERE `name` =?")) {
97 preparedStatement.setString(1, name);
98 try (ResultSet resultSet = preparedStatement.executeQuery()) {
99 consumer.accept(resultSet.next());
100 }
101 } catch (SQLException e) {
102 e.printStackTrace();
103 }
104 }
105 });
106 }
107
108 public static void connectToDatabaseFactions() {
109 String address = "hosting3.gazduirejocuri.ro";
110 String name = "chzoneeu_factions";
111 String username = "chzoneeu_nycu";
112 String password = "unprost2019";
113
114 HikariConfig config = new HikariConfig();
115 config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
116 config.addDataSourceProperty("serverName", address);
117 config.addDataSourceProperty("port", "3306");
118 config.addDataSourceProperty("databaseName", name);
119 config.addDataSourceProperty("user", username);
120 config.addDataSourceProperty("password", password);
121 DATASOURCE_FACTIONS = new HikariDataSource(config);
122
123 DATASOURCE_FACTIONS.setMaximumPoolSize(10);
124
125 String query = "create table if not exists dates (`name` varchar(20), `job` int, `kills` int, `deaths` int, `cooldown` INTEGER, `experience` INTEGER, `level` int, `necesary` INTEGER, `time` INTEGER, `dollars` REAL)";
126
127 try (Connection connection = DATASOURCE_FACTIONS.getConnection();
128 PreparedStatement preparedStatement = connection.prepareStatement(query)) {
129 preparedStatement.executeUpdate();
130 } catch (SQLException e) {
131 e.printStackTrace();
132 }
133
134 //Loader.registerTops();
135 }
136
137 public static void addDatesPlayerFactions(String name) {
138 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
139 @Override
140 public void onRun() {
141 try (Connection connection = DATASOURCE_FACTIONS.getConnection();
142 PreparedStatement preparedStatement =
143 connection.prepareStatement("SELECT * from `dates` WHERE `name` =?")) {
144 preparedStatement.setString(1, name);
145 try (ResultSet resultSet = preparedStatement.executeQuery()) {
146 while (resultSet.next()) {
147 profileFactions.put(name, new ProfileFactions(
148 resultSet.getString("name"),
149 resultSet.getInt("job"),
150 resultSet.getInt("kills"),
151 resultSet.getInt("deaths"),
152 resultSet.getLong("cooldown"),
153 resultSet.getDouble("experience"),
154 resultSet.getInt("level"),
155 resultSet.getDouble("necesary"),
156 resultSet.getLong("time"),
157 resultSet.getDouble("dollars")
158 ));
159 }
160 }
161 } catch (SQLException e) {
162 e.printStackTrace();
163 }
164 }
165 });
166 }
167
168 public static void getTopDollars() {
169 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
170 @Override
171 public void onRun() {
172 try (Connection connection = DATASOURCE_FACTIONS.getConnection();
173 PreparedStatement preparedStatement =
174 connection.prepareStatement("SELECT `name`, `dollars` from `dates` ORDER BY `dollars` DESC LIMIT 10")) {
175 try (ResultSet resultSet = preparedStatement.executeQuery()) {
176 while (resultSet.next()) {
177 if (!scoreboardcoinsValue.isEmpty()) scoreboardcoinsValue.clear();
178 if (!scoreboardcoinsName.isEmpty()) scoreboardcoinsName.clear();
179 switch (resultSet.getRow()) {
180 case 1:
181 scoreboardcoinsName.put(1, resultSet.getString("name"));
182 scoreboardcoinsValue.put(1, resultSet.getDouble("dollars"));
183 break;
184 case 2:
185 scoreboardcoinsName.put(2, resultSet.getString("name"));
186 scoreboardcoinsValue.put(2, resultSet.getDouble("dollars"));
187 break;
188 case 3:
189 scoreboardcoinsName.put(3, resultSet.getString("name"));
190 scoreboardcoinsValue.put(3, resultSet.getDouble("dollars"));
191 break;
192 case 4:
193 scoreboardcoinsName.put(4, resultSet.getString("name"));
194 scoreboardcoinsValue.put(4, resultSet.getDouble("dollars"));
195 break;
196 case 5:
197 scoreboardcoinsName.put(5, resultSet.getString("name"));
198 scoreboardcoinsValue.put(5, resultSet.getDouble("dollars"));
199 break;
200 case 6:
201 scoreboardcoinsName.put(6, resultSet.getString("name"));
202 scoreboardcoinsValue.put(6, resultSet.getDouble("dollars"));
203 break;
204 case 7:
205 scoreboardcoinsName.put(7, resultSet.getString("name"));
206 scoreboardcoinsValue.put(7, resultSet.getDouble("dollars"));
207 break;
208 case 8:
209 scoreboardcoinsName.put(8, resultSet.getString("name"));
210 scoreboardcoinsValue.put(8, resultSet.getDouble("dollars"));
211 break;
212 case 9:
213 scoreboardcoinsName.put(9, resultSet.getString("name"));
214 scoreboardcoinsValue.put(9, resultSet.getDouble("dollars"));
215 break;
216 case 10:
217 scoreboardcoinsName.put(10, resultSet.getString("name"));
218 scoreboardcoinsValue.put(10, resultSet.getDouble("dollars"));
219 break;
220 }
221 }
222 }
223 } catch (SQLException e) {
224 e.printStackTrace();
225 }
226 }
227 });
228 }
229
230 public static void getTopKills() {
231 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
232 @Override
233 public void onRun() {
234 try (Connection connection = DATASOURCE_FACTIONS.getConnection();
235 PreparedStatement preparedStatement =
236 connection.prepareStatement("SELECT `name`, `kills` from `dates` ORDER BY `kills` DESC LIMIT 10")) {
237 try (ResultSet resultSet = preparedStatement.executeQuery()) {
238 while (resultSet.next()) {
239 if (!scoreboardkillsValue.isEmpty()) scoreboardkillsValue.clear();
240 if (!scoreboardkillsName.isEmpty()) scoreboardkillsName.clear();
241 switch (resultSet.getRow()) {
242 case 1:
243 scoreboardkillsName.put(1, resultSet.getString("name"));
244 scoreboardkillsValue.put(1, resultSet.getInt("kills"));
245 break;
246 case 2:
247 scoreboardkillsName.put(2, resultSet.getString("name"));
248 scoreboardkillsValue.put(2, resultSet.getInt("kills"));
249 break;
250 case 3:
251 scoreboardkillsName.put(3, resultSet.getString("name"));
252 scoreboardkillsValue.put(3, resultSet.getInt("kills"));
253 break;
254 case 4:
255 scoreboardkillsName.put(4, resultSet.getString("name"));
256 scoreboardkillsValue.put(4, resultSet.getInt("kills"));
257 break;
258 case 5:
259 scoreboardkillsName.put(5, resultSet.getString("name"));
260 scoreboardkillsValue.put(5, resultSet.getInt("kills"));
261 break;
262 case 6:
263 scoreboardkillsName.put(6, resultSet.getString("name"));
264 scoreboardkillsValue.put(6, resultSet.getInt("kills"));
265 break;
266 case 7:
267 scoreboardkillsName.put(7, resultSet.getString("name"));
268 scoreboardkillsValue.put(7, resultSet.getInt("kills"));
269 break;
270 case 8:
271 scoreboardkillsName.put(8, resultSet.getString("name"));
272 scoreboardkillsValue.put(8, resultSet.getInt("kills"));
273 break;
274 case 9:
275 scoreboardkillsName.put(9, resultSet.getString("name"));
276 scoreboardkillsValue.put(9, resultSet.getInt("kills"));
277 break;
278 case 10:
279 scoreboardkillsName.put(10, resultSet.getString("name"));
280 scoreboardkillsValue.put(10, resultSet.getInt("kills"));
281 break;
282 }
283 }
284 }
285 } catch (SQLException e) {
286 e.printStackTrace();
287 }
288 }
289 });
290 }
291
292 public static void getTopDeaths() {
293 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
294 @Override
295 public void onRun() {
296 try (Connection connection = DATASOURCE_FACTIONS.getConnection();
297 PreparedStatement preparedStatement =
298 connection.prepareStatement("SELECT `name`, `deaths` from `dates` ORDER BY `deaths` DESC LIMIT 10")) {
299 try (ResultSet resultSet = preparedStatement.executeQuery()) {
300 while (resultSet.next()) {
301 if (!scoreboarddeathsValue.isEmpty()) scoreboarddeathsValue.clear();
302 if (!scoreboarddeathsName.isEmpty()) scoreboarddeathsName.clear();
303 switch (resultSet.getRow()) {
304 case 1:
305 scoreboarddeathsName.put(1, resultSet.getString("name"));
306 scoreboarddeathsValue.put(1, resultSet.getInt("deaths"));
307 break;
308 case 2:
309 scoreboarddeathsName.put(2, resultSet.getString("name"));
310 scoreboarddeathsValue.put(2, resultSet.getInt("deaths"));
311 break;
312 case 3:
313 scoreboarddeathsName.put(3, resultSet.getString("name"));
314 scoreboarddeathsValue.put(3, resultSet.getInt("deaths"));
315 break;
316 case 4:
317 scoreboarddeathsName.put(4, resultSet.getString("name"));
318 scoreboarddeathsValue.put(4, resultSet.getInt("deaths"));
319 break;
320 case 5:
321 scoreboarddeathsName.put(5, resultSet.getString("name"));
322 scoreboarddeathsValue.put(5, resultSet.getInt("deaths"));
323 break;
324 case 6:
325 scoreboarddeathsName.put(6, resultSet.getString("name"));
326 scoreboarddeathsValue.put(6, resultSet.getInt("deaths"));
327 break;
328 case 7:
329 scoreboarddeathsName.put(7, resultSet.getString("name"));
330 scoreboarddeathsValue.put(7, resultSet.getInt("deaths"));
331 break;
332 case 8:
333 scoreboarddeathsName.put(8, resultSet.getString("name"));
334 scoreboarddeathsValue.put(8, resultSet.getInt("deaths"));
335 break;
336 case 9:
337 scoreboarddeathsName.put(9, resultSet.getString("name"));
338 scoreboarddeathsValue.put(9, resultSet.getInt("deaths"));
339 break;
340 case 10:
341 scoreboarddeathsName.put(10, resultSet.getString("name"));
342 scoreboarddeathsValue.put(10, resultSet.getInt("deaths"));
343 break;
344 }
345 }
346 }
347 } catch (SQLException e) {
348 e.printStackTrace();
349 }
350 }
351 });
352 }
353
354 public static void getTopTime() {
355 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
356 @Override
357 public void onRun() {
358 try (Connection connection = DATASOURCE_PROXY.getConnection();
359 PreparedStatement preparedStatement =
360 connection.prepareStatement("SELECT `name`, `time` from `dates` ORDER BY `time` DESC LIMIT 10")) {
361 try (ResultSet resultSet = preparedStatement.executeQuery()) {
362 while (resultSet.next()) {
363 if (!scoreboardtimeValue.isEmpty()) scoreboardtimeValue.clear();
364 if (!scoreboardtimeName.isEmpty()) scoreboardtimeName.clear();
365 switch (resultSet.getRow()) {
366 case 1:
367 scoreboardtimeName.put(1, resultSet.getString("name"));
368 scoreboardtimeValue.put(1, resultSet.getLong("time"));
369 break;
370 case 2:
371 scoreboardtimeName.put(2, resultSet.getString("name"));
372 scoreboardtimeValue.put(2, resultSet.getLong("time"));
373 break;
374 case 3:
375 scoreboardtimeName.put(3, resultSet.getString("name"));
376 scoreboardtimeValue.put(3, resultSet.getLong("time"));
377 break;
378 case 4:
379 scoreboardtimeName.put(4, resultSet.getString("name"));
380 scoreboardtimeValue.put(4, resultSet.getLong("time"));
381 break;
382 case 5:
383 scoreboardtimeName.put(5, resultSet.getString("name"));
384 scoreboardtimeValue.put(5, resultSet.getLong("time"));
385 break;
386 case 6:
387 scoreboardtimeName.put(6, resultSet.getString("name"));
388 scoreboardtimeValue.put(6, resultSet.getLong("time"));
389 break;
390 case 7:
391 scoreboardtimeName.put(7, resultSet.getString("name"));
392 scoreboardtimeValue.put(7, resultSet.getLong("time"));
393 break;
394 case 8:
395 scoreboardtimeName.put(8, resultSet.getString("name"));
396 scoreboardtimeValue.put(8, resultSet.getLong("time"));
397 break;
398 case 9:
399 scoreboardtimeName.put(9, resultSet.getString("name"));
400 scoreboardtimeValue.put(9, resultSet.getLong("time"));
401 break;
402 case 10:
403 scoreboardtimeName.put(10, resultSet.getString("name"));
404 scoreboardtimeValue.put(10, resultSet.getLong("time"));
405 break;
406 }
407 }
408 }
409 } catch (SQLException e) {
410 e.printStackTrace();
411 }
412 }
413 });
414 }
415
416 public static void getTopVotes() {
417 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
418 @Override
419 public void onRun() {
420 try (Connection connection = DATASOURCE_PROXY.getConnection();
421 PreparedStatement preparedStatement =
422 connection.prepareStatement("SELECT `name`, `votes` from `dates` ORDER BY `time` DESC LIMIT 10")) {
423 try (ResultSet resultSet = preparedStatement.executeQuery()) {
424 while (resultSet.next()) {
425 if (!scoreboardvotesValue.isEmpty()) scoreboardvotesValue.clear();
426 if (!scoreboardvotesName.isEmpty()) scoreboardvotesName.clear();
427 switch (resultSet.getRow()) {
428 case 1:
429 scoreboardvotesName.put(1, resultSet.getString("name"));
430 scoreboardvotesValue.put(1, resultSet.getInt("votes"));
431 break;
432 case 2:
433 scoreboardvotesName.put(2, resultSet.getString("name"));
434 scoreboardvotesValue.put(2, resultSet.getInt("votes"));
435 break;
436 case 3:
437 scoreboardvotesName.put(3, resultSet.getString("name"));
438 scoreboardvotesValue.put(3, resultSet.getInt("votes"));
439 break;
440 case 4:
441 scoreboardvotesName.put(4, resultSet.getString("name"));
442 scoreboardvotesValue.put(4, resultSet.getInt("votes"));
443 break;
444 case 5:
445 scoreboardvotesName.put(5, resultSet.getString("name"));
446 scoreboardvotesValue.put(5, resultSet.getInt("votes"));
447 break;
448 case 6:
449 scoreboardvotesName.put(6, resultSet.getString("name"));
450 scoreboardvotesValue.put(6, resultSet.getInt("votes"));
451 break;
452 case 7:
453 scoreboardvotesName.put(7, resultSet.getString("name"));
454 scoreboardvotesValue.put(7, resultSet.getInt("votes"));
455 break;
456 case 8:
457 scoreboardvotesName.put(8, resultSet.getString("name"));
458 scoreboardvotesValue.put(8, resultSet.getInt("votes"));
459 break;
460 case 9:
461 scoreboardvotesName.put(9, resultSet.getString("name"));
462 scoreboardvotesValue.put(9, resultSet.getInt("votes"));
463 break;
464 case 10:
465 scoreboardvotesName.put(10, resultSet.getString("name"));
466 scoreboardvotesValue.put(10, resultSet.getInt("votes"));
467 break;
468 }
469 }
470 }
471 } catch (SQLException e) {
472 e.printStackTrace();
473 }
474 }
475 });
476 }
477
478 public static void saveDatesPlayerFromProxy(String name) {
479 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
480 @Override
481 public void onRun() {
482 saveUnAsyncDatesPlayerFromProxy(name);
483 }
484 });
485 }
486
487 public static void saveUnAsyncDatesPlayerFromProxy(String name) {
488 ProfileProxy profile = profileProxy.get(name);
489 try (Connection connection = DATASOURCE_PROXY.getConnection();
490 PreparedStatement preparedStatement =
491 connection.prepareStatement("UPDATE `dates` SET `language` = ?, `gems` = ?, `time` = ?, `votes` = ? WHERE `name` = ?")) {
492 preparedStatement.setInt(1, profile.getLanguage());
493 preparedStatement.setDouble(2, profile.getGems());
494 preparedStatement.setLong(3, profile.getTime());
495 preparedStatement.setInt(4, profile.getVotes());
496 preparedStatement.setString(5, name);
497 preparedStatement.executeUpdate();
498 } catch (SQLException e) {
499 e.printStackTrace();
500 }
501 }
502
503 public void addNewPlayer(String name) {
504 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
505 @Override
506 public void onRun() {
507 try (Connection connection = DATASOURCE_PROXY.getConnection();
508 PreparedStatement preparedStatement =
509 connection.prepareStatement("INSERT INTO `dates` (`name`, `language`, `gems`, `time`, `votes`) VALUES (?, ?, ?, ?, ?)")) {
510 preparedStatement.setString(1, name);
511 preparedStatement.setInt(2, 0);
512 preparedStatement.setDouble(3, 0);
513 preparedStatement.setLong(4, 0);
514 preparedStatement.setInt(5, 0);
515 profileProxy.put(name, new ProfileProxy(
516 name,
517 0,
518 0,
519 0,
520 0
521 ));
522 preparedStatement.executeUpdate();
523 } catch (SQLException e) {
524 e.printStackTrace();
525 }
526 }
527 });
528 }
529
530 public void setLanguage(String name, int language) {
531 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
532 @Override
533 public void onRun() {
534 try (Connection connection = DATASOURCE_PROXY.getConnection();
535 PreparedStatement preparedStatement =
536 connection.prepareStatement("UPDATE `dates` SET `language` =? WHERE `name` =?")) {
537 preparedStatement.setInt(1, language);
538 preparedStatement.setString(2, name);
539 preparedStatement.executeUpdate();
540 } catch (SQLException e) {
541 e.printStackTrace();
542 }
543 }
544 });
545 }
546
547 public void setGems(String name, double gems) {
548 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
549 @Override
550 public void onRun() {
551 try (Connection connection = DATASOURCE_PROXY.getConnection();
552 PreparedStatement preparedStatement =
553 connection.prepareStatement("UPDATE `dates` SET `gems` =? WHERE `name` =?")) {
554 preparedStatement.setDouble(1, gems);
555 preparedStatement.setString(2, name);
556 preparedStatement.executeUpdate();
557 } catch (SQLException e) {
558 e.printStackTrace();
559 }
560 }
561 });
562 }
563
564 public void setVotes(String name, int votes) {
565 API.getMainAPI().getServer().getScheduler().scheduleAsyncTask(API.getMainAPI(), new AsyncTask() {
566 @Override
567 public void onRun() {
568 try (Connection connection = DATASOURCE_PROXY.getConnection();
569 PreparedStatement preparedStatement =
570 connection.prepareStatement("UPDATE `dates` SET `votes` =? WHERE `name` =?")) {
571 preparedStatement.setDouble(1, votes);
572 preparedStatement.setString(2, name);
573 preparedStatement.executeUpdate();
574 } catch (SQLException e) {
575 e.printStackTrace();
576 }
577 }
578 });
579 }
580}