· 2 years ago · Oct 19, 2022, 10:50 AM
1package me.mathiaseklund.itemization.utils;
2
3import java.sql.Connection;
4import java.sql.PreparedStatement;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.util.List;
8import java.util.UUID;
9
10import org.bukkit.Bukkit;
11import org.bukkit.entity.Player;
12import org.bukkit.scheduler.BukkitTask;
13
14import com.google.common.collect.Lists;
15import com.zaxxer.hikari.HikariConfig;
16import com.zaxxer.hikari.HikariDataSource;
17
18import me.mathiaseklund.itemization.Main;
19import me.mathiaseklund.itemization.enchants.CustomEnchantment;
20import me.mathiaseklund.itemization.enchants.EnchantManager;
21import me.mathiaseklund.itemization.entities.CustomEntity;
22import me.mathiaseklund.itemization.entities.EntityManager;
23import me.mathiaseklund.itemization.items.CustomItem;
24import me.mathiaseklund.itemization.items.Items;
25import me.mathiaseklund.itemization.items.Recipes;
26import me.mathiaseklund.itemization.players.PlayerData;
27import me.mathiaseklund.itemization.protection.BlockProtectionManager;
28
29public class SQLManager {
30
31 static Main main;
32 HikariDataSource hikari;
33 String host, database, user, password;
34 int port;
35
36 List<PlayerData> saveQueue = Lists.newArrayList();
37 BukkitTask saveTask;
38
39 public SQLManager() {
40 if (main == null) {
41 main = Main.getMain();
42 }
43 load();
44 }
45
46 void load() {
47 host = main.getConf().getString("mysql.host", "localhost");
48 database = main.getConf().getString("mysql.database", "itemization");
49 user = main.getConf().getString("mysql.user", "root");
50 password = main.getConf().getString("mysql.password", "");
51 port = main.getConf().getInt("mysql.port", 3306);
52 HikariConfig config = new HikariConfig();
53 config.setJdbcUrl("jdbc:mysql://" + host + ":" + port + "/" + database);
54 config.setUsername(user);
55 config.setPassword(password);
56 config.addDataSourceProperty("cachePrepStmts", "true");
57 config.addDataSourceProperty("prepStmtCacheSize", "250");
58 config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
59
60 hikari = new HikariDataSource(config);
61
62 setupTables();
63 }
64
65 public HikariDataSource getDataSource() {
66 return hikari;
67 }
68
69 public Connection getConnection() throws SQLException {
70 return getDataSource().getConnection();
71 }
72
73 void setupTables() {
74 Util.debug("Checking tables and creating missing ones.");
75 try {
76 Connection con = getConnection();
77 String sql = "CREATE TABLE IF NOT EXISTS items(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE, material VARCHAR(255) NOT NULL, displayname VARCHAR(255),lore VARCHAR(255)"
78 + ",type VARCHAR(255),level int DEFAULT 0,entitytype VARCHAR(255) NULL DEFAULT NULL,skullowner VARCHAR(255) NULL DEFAULT NULL"
79 + ",farmdrops VARCHAR(255) NULL DEFAULT NULL,xpdrops INT DEFAULT 0,raredrops VARCHAR(255) NULL DEFAULT NULL,glow BOOLEAN NOT NULL DEFAULT FALSE"
80 + ",, PRIMARY KEY (id));";
81 PreparedStatement stmt = con.prepareStatement(sql);
82 stmt.execute();
83 stmt.close();
84
85 sql = "CREATE TABLE IF NOT EXISTS users(id INT NOT NULL AUTO_INCREMENT, uuid VARCHAR(255) NOT NULL UNIQUE, blocksbroken INT DEFAULT 0, totalblocksbroken INT DEFAULT 0, hsloc VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id));";
86 stmt = con.prepareStatement(sql);
87 stmt.execute();
88 stmt.close();
89
90 sql = "CREATE TABLE IF NOT EXISTS permits(id INT NOT NULL AUTO_INCREMENT, owner VARCHAR(255) NOT NULL, type VARCHAR(255) NOT NULL, target VARCHAR(255) NOT NULL, PRIMARY KEY (id));";
91 stmt = con.prepareStatement(sql);
92 stmt.execute();
93 stmt.close();
94
95 sql = "CREATE TABLE IF NOT EXISTS recipes(id INT NOT NULL AUTO_INCREMENT, result INT"
96 + ", i1 VARCHAR(255) NULL DEFAULT NULL, i2 VARCHAR(255) NULL DEFAULT NULL, i3 VARCHAR(255) NULL DEFAULT NULL, i4 VARCHAR(255) NULL DEFAULT NULL, i5 VARCHAR(255) NULL DEFAULT NULL"
97 + ", i6 VARCHAR(255) NULL DEFAULT NULL, i7 VARCHAR(255) NULL DEFAULT NULL, i8 VARCHAR(255) NULL DEFAULT NULL, i9 VARCHAR(255) NULL DEFAULT NULL"
98 + ", PRIMARY KEY (id));";
99 stmt = con.prepareStatement(sql);
100 stmt.execute();
101 stmt.close();
102
103 sql = "CREATE TABLE IF NOT EXISTS enchants(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL,"
104 + " itemtypes VARCHAR(255) NULL DEFAULT NULL" + ",displayname VARCHAR(255) NULL DEFAULT NULL"
105 + ",maxlevel INT DEFAULT 1" + ",lore VARCHAR(255) NULL DEFAULT NULL"
106 + ",cost VARCHAR(255) NULL DEFAULT NULL" + ", PRIMARY KEY (id));";
107 stmt = con.prepareStatement(sql);
108 stmt.execute();
109 stmt.close();
110
111 sql = "CREATE TABLE IF NOT EXISTS entities(id INT NOT NULL AUTO_INCREMENT" + ", name VARCHAR(255) NOT NULL"
112 + ", displayname VARCHAR(255) NULL DEFAULT NULL" + ", type VARCHAR(255) DEFAULT ?"
113 + ", maxhealth DOUBLE DEFAULT -1" + ", kbres DOUBLE DEFAULT -1"
114 + ", movementspeed DOUBLE DEFAULT -1" + ", zsr DOUBLE DEFAULT -1" + ", dmg DOUBLE DEFAULT -1"
115 + ", kb DOUBLE DEFAULT -1" + ", armor DOUBLE DEFAULT -1" + ", armortoughness DOUBLE DEFAULT -1"
116 + ", helm VARCHAR(255) NULL DEFAULT NULL" + ", chest VARCHAR(255) NULL DEFAULT NULL"
117 + ", legs VARCHAR(255) NULL DEFAULT NULL" + ", boots VARCHAR(255) NULL DEFAULT NULL"
118 + ", mainhand VARCHAR(255) NULL DEFAULT NULL" + ", offhand VARCHAR(255) NULL DEFAULT NULL"
119 + ", PRIMARY KEY (id)" + ");";
120 stmt = con.prepareStatement(sql);
121 stmt.setString(1, "ZOMBIE");
122 stmt.execute();
123 stmt.close();
124
125 // Close Connection
126 con.close();
127 } catch (SQLException e) {
128 e.printStackTrace();
129 }
130 }
131
132 public void loadItems(Items items) {
133 Util.debug("Loading all items.");
134 try {
135 Connection con = getConnection();
136 String sql = "SELECT * FROM items";
137 PreparedStatement stmt = con.prepareStatement(sql);
138 ResultSet results = stmt.executeQuery();
139 while (results.next()) {
140 int id = results.getInt("id");
141 String name = results.getString("name");
142 String material = results.getString("material");
143 String displayName = results.getString("displayname");
144 String lore = results.getString("lore");
145 String type = results.getString("type");
146 String entityType = results.getString("entitytype");
147 int level = results.getInt("level");
148 String skullowner = results.getString("skullowner");
149 String farmDrops = results.getString("farmdrops");
150 int cooldown = results.getInt("cooldown");
151 int xpdrops = results.getInt("xpdrops");
152 String raredrops = results.getString("raredrops");
153 boolean glow = results.getBoolean("glow");
154
155 items.loadItem(id, name, material, displayName, lore, type, level, entityType, skullowner, farmDrops,
156 cooldown, xpdrops, raredrops, glow);
157 }
158 results.close();
159 stmt.close();
160 con.close();
161 } catch (SQLException e) {
162 e.printStackTrace();
163 }
164 }
165
166 public void createNewItem(Player player, String name, String material) {
167 createNewItem(player, name, material, null);
168 }
169
170 public void createNewItem(Player player, String name, String material, String skullowner) {
171 Util.debug("Creating a new item: " + name);
172 try {
173 Connection con = getConnection();
174 String sql = "SELECT id FROM items WHERE name=?";
175 PreparedStatement stmt = con.prepareStatement(sql);
176 stmt.setString(1, name);
177 ResultSet results = stmt.executeQuery();
178 if (results.next()) {
179 results.close();
180 stmt.close();
181 Util.msg(player, "&4ERROR:&7 An item already exists with that name.");
182 } else {
183 results.close();
184 stmt.close();
185 sql = "INSERT INTO items (name, material, skullowner) VALUES(?,?,?);";
186 stmt = con.prepareStatement(sql);
187 stmt.setString(1, name);
188 stmt.setString(2, material);
189 stmt.setString(3, skullowner);
190 stmt.execute();
191 stmt.close();
192 sql = "SELECT * FROM items WHERE name=?";
193 stmt = con.prepareStatement(sql);
194 stmt.setString(1, name);
195 results = stmt.executeQuery();
196 if (results.next()) {
197 int id = results.getInt("id");
198 results.close();
199 stmt.close();
200 main.getItems().loadItem(id, name, material, null, null, null, 1, null, skullowner, null, 0, 0,
201 null, false);
202 } else {
203 Util.msg(player, "&4ERROR:&7 Unexpected error occurred. Try again!");
204 }
205 con.close();
206 }
207
208 } catch (SQLException e) {
209 e.printStackTrace();
210 }
211 }
212
213 public void loadPlayerData(PlayerData data) {
214 try {
215 Connection con = getConnection();
216 String sql = "SELECT * FROM users WHERE uuid =?";
217 PreparedStatement stmt = con.prepareStatement(sql);
218 stmt.setString(1, data.getPlayer().getUniqueId().toString());
219 ResultSet result = stmt.executeQuery();
220 int id;
221 long brokenBlocks, totalBrokenBlocks;
222 String hsLoc;
223 if (result.next()) {
224 // found data.
225 id = result.getInt("id");
226 brokenBlocks = result.getLong("blocksbroken");
227 totalBrokenBlocks = result.getLong("totalblocksbroken");
228 hsLoc = result.getString("hsloc");
229 result.close();
230 stmt.close();
231 con.close();
232
233 data.setData(id, brokenBlocks, totalBrokenBlocks, hsLoc);
234 } else {
235 result.close();
236 stmt.close();
237
238 sql = "INSERT INTO users (uuid) VALUES (?)";
239 stmt = con.prepareStatement(sql);
240 stmt.setString(1, data.getPlayer().getUniqueId().toString());
241 stmt.execute();
242 stmt.close();
243 con.close();
244
245 loadPlayerData(data);
246 }
247 } catch (SQLException e) {
248 e.printStackTrace();
249 }
250 }
251
252 public void savePlayerData(PlayerData data) {
253 if (!saveQueue.contains(data)) {
254 saveQueue.add(data);
255 }
256
257 if (saveTask == null) {
258 updateSaveTask();
259 }
260 }
261
262 public void updateSaveTask() {
263 if (saveQueue.isEmpty()) {
264 saveTask = null;
265 // Util.debug("Save Queue is Empty");
266 return;
267 }
268
269 saveTask = Bukkit.getScheduler().runTaskLaterAsynchronously(main, new Runnable() {
270 public void run() {
271 PlayerData data = saveQueue.get(0);
272 saveQueue.remove(0);
273 forceSaveData(data);
274 // Util.debug("Saved data for " + data.getPlayer().getName());
275 updateSaveTask();
276 }
277 }, 20);
278 }
279
280 public void forceSaveData(PlayerData data) {
281 try {
282 Connection con = getConnection();
283 String sql = "UPDATE users SET blocksbroken=?,totalblocksbroken=?,hsloc=? WHERE id=?";
284 PreparedStatement stmt = con.prepareStatement(sql);
285 stmt.setLong(1, data.getBlocksBroken());
286 stmt.setLong(2, data.getTotalBlocksBroken());
287 stmt.setString(3, data.getHsLoc());
288 stmt.setInt(4, data.getId());
289 stmt.execute();
290 stmt.close();
291 con.close();
292 } catch (SQLException e) {
293 e.printStackTrace();
294 }
295 }
296
297 public void saveCustomItem(CustomItem ci) {
298 int id = ci.getId();
299 Util.debug("Saving Item Data: " + ci.getName());
300
301 try {
302 Connection con = getConnection();
303 String sql = "UPDATE items SET displayname=?,lore=?,entitytype=?,type=?,farmdrops=?,cooldown=?,skullowner=?,xpdrops=?,raredrops=?,glow=? WHERE id=?";
304 PreparedStatement stmt = con.prepareStatement(sql);
305 stmt.setString(1, ci.getDisplayName());
306 stmt.setString(2, ci.getLore());
307 stmt.setString(3, ci.getEntityType());
308 stmt.setString(4, ci.getType().toString());
309 stmt.setString(5, ci.getFarmDrops());
310 stmt.setInt(6, ci.getCooldown());
311 stmt.setString(7, ci.getSkullOwner());
312 stmt.setInt(8, ci.getXpDrops());
313 stmt.setString(9, ci.getRareDrops());
314 stmt.setBoolean(10, ci.isGlow());
315
316 stmt.setInt(11, id);
317
318 stmt.execute();
319 stmt.close();
320 con.close();
321 Util.debug("Saved Item.");
322 } catch (SQLException e) {
323 e.printStackTrace();
324 }
325
326 }
327
328 public void loadPermits(BlockProtectionManager bpm) {
329 Util.debug("Loading Permits from Database");
330 try {
331 Connection con = getConnection();
332 String sql = "SELECT * FROM permits";
333 PreparedStatement stmt = con.prepareStatement(sql);
334 ResultSet result = stmt.executeQuery();
335 while (result.next()) {
336 UUID owner = UUID.fromString(result.getString("owner"));
337 UUID target = UUID.fromString(result.getString("target"));
338 String type = result.getString("type").toUpperCase();
339
340 bpm.setPermit(owner, target, type);
341 }
342 result.close();
343 stmt.close();
344 con.close();
345 } catch (SQLException e) {
346 e.printStackTrace();
347 }
348 }
349
350 public void addPermit(String owner, String target, String type) {
351 try {
352 Connection con = getConnection();
353 String sql = "INSERT INTO permits (owner, type, target) VALUES (?,?,?);";
354 PreparedStatement stmt = con.prepareStatement(sql);
355 stmt.setString(1, owner);
356 stmt.setString(2, type);
357 stmt.setString(3, target);
358 stmt.execute();
359 stmt.close();
360 con.close();
361 } catch (SQLException e) {
362 e.printStackTrace();
363 }
364 }
365
366 public void loadRecipes(Recipes recipes) {
367 Util.debug("Loading recipes from database.");
368 try {
369 Connection con = getConnection();
370 String sql = "SELECT * FROM recipes";
371 PreparedStatement stmt = con.prepareStatement(sql);
372 ResultSet result = stmt.executeQuery();
373 while (result.next()) {
374 // int id = result.getInt("id");
375 int res = result.getInt("result");
376 List<String> ingredients = Lists.newArrayList();
377 String i1 = result.getString("i1");
378 String i2 = result.getString("i2");
379 String i3 = result.getString("i3");
380 String i4 = result.getString("i4");
381 String i5 = result.getString("i5");
382 String i6 = result.getString("i6");
383 String i7 = result.getString("i7");
384 String i8 = result.getString("i8");
385 String i9 = result.getString("i9");
386
387 if (i1 != null) {
388 ingredients.add(i1);
389 }
390 if (i2 != null) {
391 ingredients.add(i2);
392 }
393 if (i3 != null) {
394 ingredients.add(i3);
395 }
396 if (i4 != null) {
397 ingredients.add(i4);
398 }
399 if (i5 != null) {
400 ingredients.add(i5);
401 }
402 if (i6 != null) {
403 ingredients.add(i6);
404 }
405 if (i7 != null) {
406 ingredients.add(i7);
407 }
408 if (i8 != null) {
409 ingredients.add(i8);
410 }
411 if (i9 != null) {
412 ingredients.add(i9);
413 }
414
415 recipes.loadRecipe(res, ingredients);
416 }
417
418 result.close();
419 stmt.close();
420 con.close();
421 } catch (SQLException e) {
422 e.printStackTrace();
423 }
424 }
425
426 public void setRecipe(int resultId, List<String> ingredients) {
427 try {
428 Connection con = getConnection();
429 String sql = "INSERT INTO recipes (result, i1,i2,i3,i4,i5,i6,i7,i8,i9) VALUES (?,?,?,?,?,?,?,?,?,?)";
430 PreparedStatement stmt = con.prepareStatement(sql);
431 stmt.setInt(1, resultId);
432 for (int i = 0; i < 9; i++) {
433 Util.debug("I: " + i);
434 try {
435 stmt.setString(2 + i, ingredients.get(i));
436 } catch (Exception e) {
437 stmt.setString(2 + i, null);
438 }
439 }
440 // stmt.setString(2, ingredients.get(0));
441 // stmt.setString(3, ingredients.get(1));
442 // stmt.setString(4, ingredients.get(2));
443 // stmt.setString(5, ingredients.get(3));
444 // stmt.setString(6, ingredients.get(4));
445 // stmt.setString(7, ingredients.get(5));
446 // stmt.setString(8, ingredients.get(6));
447 // stmt.setString(9, ingredients.get(7));
448 // stmt.setString(10, ingredients.get(8));
449
450 stmt.execute();
451 con.close();
452 } catch (SQLException e) {
453 e.printStackTrace();
454 }
455 main.getItems().getRecipes().reloadRecipes();
456 }
457
458 public void deleteRecipe(int recipeId) {
459 try {
460 Connection con = getConnection();
461 String sql = "DELETE FROM recipes WHERE id=?";
462 PreparedStatement stmt = con.prepareStatement(sql);
463 stmt.setInt(1, recipeId);
464 stmt.execute();
465 stmt.close();
466 con.close();
467
468 main.getItems().getRecipes().reloadRecipes();
469 } catch (SQLException e) {
470 e.printStackTrace();
471 }
472 }
473
474 public void loadEnchantments(EnchantManager manager) {
475 try {
476 Connection con = getConnection();
477 String sql = "SELECT * FROM enchants";
478 PreparedStatement stmt = con.prepareStatement(sql);
479 ResultSet result = stmt.executeQuery();
480 manager.loadEnchants(result);
481 result.close();
482 stmt.close();
483 con.close();
484 } catch (SQLException e) {
485 e.printStackTrace();
486 }
487 }
488
489 public void saveEnchantment(CustomEnchantment enchant) {
490 try {
491 Connection con = getConnection();
492 String sql = "UPDATE enchants SET itemtypes=?,displayname=?,maxlevel=?,lore=?,cost=? WHERE id=?";
493 PreparedStatement stmt = con.prepareStatement(sql);
494 stmt.setString(1, enchant.getItemTypes());
495 stmt.setString(2, enchant.getDisplayName());
496 stmt.setInt(3, enchant.getMaxLevel());
497 stmt.setString(4, enchant.getLore());
498 stmt.setString(5, enchant.getCost());
499 stmt.setInt(6, enchant.getId());
500 stmt.execute();
501 con.close();
502 } catch (SQLException e) {
503 e.printStackTrace();
504 }
505 }
506
507 public void createEnchantment(String name) {
508 try {
509 Connection con = getConnection();
510 String sql = "INSERT INTO enchants (name) VALUES (?)";
511 PreparedStatement stmt = con.prepareStatement(sql);
512 stmt.setString(1, name);
513 stmt.execute();
514 stmt.close();
515 con.close();
516 loadEnchantment(name);
517 } catch (SQLException e) {
518 e.printStackTrace();
519 }
520 }
521
522 public void loadEnchantment(String name) {
523 try {
524 Connection con = getConnection();
525 String sql = "SELECT * FROM enchants WHERE name=? LIMIT 1";
526 PreparedStatement stmt = con.prepareStatement(sql);
527 stmt.setString(1, name);
528 ResultSet results = stmt.executeQuery();
529 main.getEnchantManager().loadEnchants(results);
530 results.close();
531 stmt.close();
532 con.close();
533 } catch (SQLException e) {
534 e.printStackTrace();
535 }
536 }
537
538 public void saveEntity(CustomEntity ent) {
539 try {
540 Connection con = getConnection();
541 String sql = "UPDATE entities SET displayname=?,maxhealth=?,armor=?,armortoughness=?,dmg=?,kb=?,kbres=?,movementspeed=?,zsr=?,helm=?,chest=?,legs=?,boots=?,mainhand=?,offhand=? WHERE id=?";
542 PreparedStatement stmt = con.prepareStatement(sql);
543
544 stmt.setString(1, ent.getDisplayName());
545 stmt.setDouble(2, ent.getMaxHealth());
546 stmt.setDouble(3, ent.getArmor());
547 stmt.setDouble(4, ent.getArmorToughness());
548 stmt.setDouble(5, ent.getAttackDamage());
549 stmt.setDouble(6, ent.getAttackKnockback());
550 stmt.setDouble(7, ent.getKnockbackResistance());
551 stmt.setDouble(8, ent.getMovementSpeed());
552 stmt.setDouble(9, ent.getZombieSpawnReinforcements());
553
554 stmt.setString(10, ent.getHelmStr());
555 stmt.setString(11, ent.getChestStr());
556 stmt.setString(12, ent.getLegsStr());
557 stmt.setString(13, ent.getBootsStr());
558 stmt.setString(14, ent.getMainHandStr());
559 stmt.setString(15, ent.getOffHandStr());
560
561 stmt.setInt(16, ent.getId());
562
563 stmt.execute();
564 stmt.close();
565 con.close();
566 } catch (SQLException e) {
567 e.printStackTrace();
568 }
569 }
570
571 public void createEntity(String name) {
572 try {
573 Connection con = getConnection();
574 String sql = "INSERT INTO entities (name) VALUES (?);";
575 PreparedStatement stmt = con.prepareStatement(sql);
576 stmt.setString(1, name);
577 stmt.execute();
578 stmt.close();
579 con.close();
580 loadEntity(name);
581 } catch (SQLException e) {
582 e.printStackTrace();
583 }
584 }
585
586 void loadEntity(String name) {
587 try {
588 Connection con = getConnection();
589 String sql = "SELECT * FROM entities WHERE name=?";
590 PreparedStatement stmt = con.prepareStatement(sql);
591 stmt.setString(1, name);
592 ResultSet results = stmt.executeQuery();
593 main.getEntityManager().loadEntities(results);
594 results.close();
595 stmt.close();
596 con.close();
597 } catch (SQLException e) {
598 e.printStackTrace();
599 }
600 }
601
602 public void loadEntities(EntityManager manager) {
603 try {
604 Connection con = getConnection();
605 String sql = "SELECT * FROM entities";
606 PreparedStatement stmt = con.prepareStatement(sql);
607 ResultSet results = stmt.executeQuery();
608 manager.loadEntities(results);
609 results.close();
610 stmt.close();
611 con.close();
612 } catch (SQLException e) {
613 e.printStackTrace();
614 }
615
616 }
617}
618