· 5 years ago · Apr 04, 2020, 09:50 AM
1package io.github.idoomful.atomcustomseeds.toolbox.data.SQL;
2
3import com.zaxxer.hikari.HikariConfig;
4import com.zaxxer.hikari.HikariDataSource;
5import io.github.idoomful.atomcustomseeds.AtomCustomSeeds;
6import io.github.idoomful.atomcustomseeds.toolbox.data.JsonVector;
7import io.github.idoomful.atomcustomseeds.toolbox.data.PlayerSeedTemplate;
8import io.github.idoomful.atomcustomseeds.toolbox.data.SeedsContainerTemplate;
9import org.bukkit.Bukkit;
10import org.bukkit.configuration.file.FileConfiguration;
11import org.bukkit.entity.Player;
12
13import java.io.File;
14import java.sql.*;
15import java.util.ArrayList;
16import java.util.HashMap;
17import java.util.List;
18import java.util.concurrent.ConcurrentHashMap;
19import java.util.stream.Collectors;
20
21public class ManagerSQL {
22 private boolean useSQLite;
23 private HikariDataSource dataSource;
24 private Connection con = null;
25 private AtomCustomSeeds main;
26 private ConcurrentHashMap<String, Boolean> hasSeeds = new ConcurrentHashMap<>();
27
28 private final String playersTable = "CREATE TABLE IF NOT EXISTS `acs_players`(" +
29 "`name` varchar(16)," +
30 "`seeds` JSON" +
31 ");";
32
33 private final String removeEntry = "DELETE FROM `acs_players` WHERE `name`=?;";
34 private final String hasAnySeed = "SELECT 1 FROM `acs_players` WHERE `name`=?;";
35 private final String allPlayerEntries = "SELECT * FROM `acs_players`;";
36 private final String allSeeds = "SELECT `seeds` FROM `acs_players`;";
37 private final String getSeedsList = "SELECT `seeds` FROM `acs_players` WHERE `name`=?;";
38 private final String setSeedsList = "UPDATE `acs_players` SET `seeds`=? WHERE `name`=?;";
39 private final String addNewSeed = "INSERT INTO `acs_players`(`seeds`,`name`) VALUES(?,?);";
40
41 public ManagerSQL(AtomCustomSeeds main) {
42 this.main = main;
43
44 FileConfiguration settYML = main.getConfigs().getSettingsYML();
45 useSQLite = settYML.getBoolean("database.use-SQLite");
46
47 setup();
48 getConnection();
49
50 Bukkit.getScheduler().scheduleSyncRepeatingTask(main, () -> {
51 for(String player : hasSeeds.keySet()) {
52 if(Bukkit.getPlayer(player) == null) hasSeeds.remove(player);
53 hasAnySeedDB(player, new Callback<Boolean>() {
54 @Override
55 public void done(Boolean result) {
56 hasSeeds.put(player, result);
57 }
58
59 @Override
60 public void failure(Throwable throwable) {
61 throwable.printStackTrace();
62 }
63 });
64 }
65 }, 0, 100);
66 }
67
68 public boolean isConnectionActive() {
69 try {
70 return con != null && !con.isClosed();
71 } catch (SQLException e) {
72 e.printStackTrace();
73 }
74 return false;
75 }
76
77 public Connection getConnection() {
78 try {
79 if(isConnectionActive()) {
80 return con;
81 } else {
82 if(useSQLite) {
83 con = DriverManager.getConnection("jdbc:sqlite:" + main.getDataFolder() + File.separator + "players.db");
84 } else {
85 con = dataSource.getConnection();
86 }
87 }
88 } catch (SQLException e) {
89 e.printStackTrace();
90 }
91 return con;
92 }
93
94 private void setup() {
95 if(!useSQLite) setupPool();
96 Connection conn = getConnection();
97
98 try(
99 Statement statement = conn.createStatement()
100 ) {
101 statement.execute(playersTable);
102 //statement.execute(islandsTable);
103
104 if(!useSQLite) conn.close();
105 } catch (SQLException e) {
106 e.printStackTrace();
107 }
108 }
109
110 private void setupPool() {
111 final FileConfiguration settYML = main.getConfigs().getSettingsYML();
112
113 HikariConfig config = new HikariConfig();
114 config.setJdbcUrl(
115 "jdbc:mysql://" +
116 settYML.getString("database.mysql.hostname") +
117 ":" +
118 settYML.getString("database.mysql.port") +
119 "/" +
120 settYML.getString("database.mysql.database")
121 );
122 config.setDriverClassName("com.mysql.jdbc.Driver");
123 config.setUsername(settYML.getString("database.mysql.username"));
124 config.setPassword(settYML.getString("database.mysql.password"));
125 config.setMinimumIdle(settYML.getInt("database.mysql.connection-pool.minimum-connections"));
126 config.setMaximumPoolSize(settYML.getInt("database.mysql.connection-pool.maximum-connections"));
127 config.setConnectionTimeout(settYML.getInt("database.mysql.connection-pool.connection-timeout"));
128 config.setConnectionTestQuery("SELECT 1");
129 config.addDataSourceProperty("useSSL", settYML.getBoolean("database.mysql.useSSL"));
130
131 dataSource = new HikariDataSource(config);
132 }
133
134/* public void removeIslandEntry(String leader) {
135 Connection conn = getConnection();
136
137 try(
138 PreparedStatement statement = conn.prepareStatement(removeIslandEntry.replace("var1", leader))
139 ) {
140 statement.execute();
141 if(!useSQLite) conn.close();
142 } catch (SQLException e) {
143 e.printStackTrace();
144 }
145 }*/
146
147 public void removeEntry(String player) {
148 Connection conn = getConnection();
149
150 try(
151 PreparedStatement statement = conn.prepareStatement(removeEntry)
152 ) {
153 statement.setString(1, player);
154 statement.execute();
155 if(!useSQLite) conn.close();
156 } catch (SQLException e) {
157 e.printStackTrace();
158 }
159 }
160
161 public void setSeed(String player, PlayerSeedTemplate newSeed) {
162 List<PlayerSeedTemplate> seeds = PlayerSeedTemplate.getSeedList(player);
163 List<String> jsonSeeds = new ArrayList<>();
164
165 for(int i = 0; i < seeds.size(); i++) {
166 final JsonVector oldCenter = seeds.get(i).getCenter();
167 final JsonVector newCenter = newSeed.getCenter();
168
169 if((oldCenter.getX() == newCenter.getX()) && (oldCenter.getY() == newCenter.getY()) && (oldCenter.getZ() == newCenter.getZ())) {
170 seeds.set(i, newSeed);
171 break;
172 }
173 }
174
175 for(PlayerSeedTemplate seed : seeds) {
176 jsonSeeds.add(AtomCustomSeeds.getInstance().getGson().toJson(seed));
177 }
178
179 final SeedsContainerTemplate seedsList = new SeedsContainerTemplate();
180 seedsList.setContainer(jsonSeeds);
181
182 final String stringJsonSeeds = main.getGson().toJson(seedsList);
183
184 setSeedsList(player, stringJsonSeeds);
185 }
186
187 public void addSeed(Player player, PlayerSeedTemplate seed) {
188 if(getAllSeeds().stream().noneMatch(s -> main.getGson().toJson(s).equals(main.getGson().toJson(seed)))) {
189 SeedsContainerTemplate seedsList = new SeedsContainerTemplate();
190
191 seedsList.setContainer(PlayerSeedTemplate.getJsonSeedList(player.getName()));
192 seedsList.getContainer().add(main.getGson().toJson(seed));
193
194 String seeds = main.getGson().toJson(seedsList);
195
196 main.getSQL().setSeedsList(player.getName(), seeds);
197 }
198
199 if(main.getCache().getAllSeeds().stream().noneMatch(s -> main.getGson().toJson(s).equals(main.getGson().toJson(seed)))) {
200 main.getCache().getAllSeeds().add(seed);
201 }
202 }
203
204 public void removeSeed(String player, String id) {
205 final ArrayList<PlayerSeedTemplate> seeds = PlayerSeedTemplate.getSeedList(player);
206 final ArrayList<String> jsonSeeds = new ArrayList<>();
207
208 for(PlayerSeedTemplate seed : seeds) {
209 if(!seed.getID().equals(id)) {
210 jsonSeeds.add(AtomCustomSeeds.getInstance().getGson().toJson(seed, PlayerSeedTemplate.class));
211 } else {
212 main.getCache().getAllSeeds().removeIf(s -> s.getID().equals(seed.getID()) && s.getOwnerName().equals(seed.getOwnerName()));
213 }
214 }
215
216 if(jsonSeeds.isEmpty()) {
217 removeEntry(player);
218 return;
219 }
220
221 SeedsContainerTemplate container = new SeedsContainerTemplate();
222 container.setContainer(jsonSeeds);
223
224 setSeedsList(player, AtomCustomSeeds.getInstance().getGson().toJson(container));
225 }
226
227 private void hasAnySeedDB(String player, Callback<Boolean> result) {
228 Connection conn = getConnection();
229 ResultSet seedsList = null;
230
231 try (
232 PreparedStatement statement = conn.prepareStatement(hasAnySeed)
233 ) {
234 statement.setString(1, player);
235 seedsList = statement.executeQuery();
236 result.done(seedsList.next());
237 if(!useSQLite) conn.close();
238 } catch (SQLException e) {
239 result.failure(e);
240 } finally {
241 try {
242 assert seedsList != null;
243 seedsList.close();
244 } catch (SQLException e) {
245 e.printStackTrace();
246 }
247 }
248 }
249
250 public void hasAnySeed(String player, Callback<Boolean> result) {
251 if(hasSeeds.containsKey(player)) {
252 result.done(hasSeeds.get(player));
253 } else {
254 hasAnySeedDB(player, result);
255 }
256 }
257
258 public HashMap<String, List<PlayerSeedTemplate>> getAllEntries() {
259 HashMap<String, List<PlayerSeedTemplate>> entries = new HashMap<>();
260 Connection conn = getConnection();
261
262
263 try(
264 PreparedStatement st1 = conn.prepareStatement(allPlayerEntries);
265 ResultSet qu = st1.executeQuery()
266 ) {
267 if(qu != null) {
268 while(qu.next()) {
269 SeedsContainerTemplate seeds = main.getGson().fromJson(qu.getString(2), SeedsContainerTemplate.class);
270 entries.put(qu.getString(1), seeds.getContainer().stream()
271 .map(str -> main.getGson().fromJson(str, PlayerSeedTemplate.class))
272 .collect(Collectors.toList()));
273 }
274 } else return entries;
275 } catch (SQLException e) {
276 e.printStackTrace();
277 } finally {
278 if(!useSQLite) {
279 try {
280 conn.close();
281 } catch (SQLException e) {
282 e.printStackTrace();
283 }
284 }
285 }
286
287 return entries;
288 }
289
290 /*// @param uuid : The UUID of an island leader that has seeds on it
291 public void getSeedIsland(String leader, Callback<SeedIsland> callback) {
292 Connection conn = getConnection();
293
294 try(
295 PreparedStatement st1 = conn.prepareStatement(getSeedIsland.replace("var1", leader));
296 ResultSet qu = st1.executeQuery()
297 ) {
298 callback.done(new SeedIsland(qu.getString(1), qu.getByte(2)));
299 } catch (SQLException e) {
300 callback.done(null);
301 callback.failure(e);
302 } finally {
303 if(!useSQLite) {
304 try {
305 conn.close();
306 } catch (SQLException e) {
307 e.printStackTrace();
308 }
309 }
310 }
311 }
312
313 public void hasSeedIsland(String leader, Callback<Boolean> callback) {
314 Connection conn = getConnection();
315
316 try(
317 PreparedStatement st1 = conn.prepareStatement(getSeedIsland.replace("var1", leader));
318 ResultSet qu = st1.executeQuery()
319 ) {
320 callback.done(qu.next());
321 } catch (SQLException e) {
322 e.printStackTrace();
323 callback.done(false);
324 } finally {
325 if(!useSQLite) {
326 try {
327 conn.close();
328 } catch (SQLException e) {
329 e.printStackTrace();
330 }
331 }
332 }
333 }
334
335 public void addSeedIsland(SeedIsland si) {
336 Connection conn = getConnection();
337
338 hasSeedIsland(si.getLeader(), new Callback<Boolean>() {
339 @Override
340 public void done(Boolean result) {
341 if(!result) {
342 try(PreparedStatement statement = conn.prepareStatement(addSeedIsland
343 .replace("var1", si.getLeader())
344 .replace("var2", si.getSeedCount() + ""))) {
345 statement.execute();
346 if(!useSQLite) conn.close();
347 } catch (SQLException e) {
348 e.printStackTrace();
349 }
350 } else setSeedIsland(si);
351 }
352
353 @Override
354 public void failure(Throwable throwable) {
355 throwable.printStackTrace();
356 }
357 });
358 }
359
360 public void setSeedIsland(SeedIsland si) {
361 Connection conn = getConnection();
362
363 try(
364 PreparedStatement st1 = conn.prepareStatement(setSeedIsland
365 .replace("var1", si.getSeedCount() + "")
366 .replace("var2", si.getLeader()))
367 ) {
368 st1.execute();
369 } catch (SQLException e) {
370 e.printStackTrace();
371 } finally {
372 if(!useSQLite) {
373 try {
374 conn.close();
375 } catch (SQLException e) {
376 e.printStackTrace();
377 }
378 }
379 }
380 }*/
381
382 public List<PlayerSeedTemplate> getAllSeeds() {
383 List<PlayerSeedTemplate> seedsList = new ArrayList<>();
384 Connection conn = getConnection();
385
386 try(
387 PreparedStatement st1 = conn.prepareStatement(allSeeds);
388 ResultSet qu = st1.executeQuery()
389 ) {
390 if(qu != null) {
391 while(qu.next()) {
392 SeedsContainerTemplate seeds = main.getGson().fromJson(qu.getString(1), SeedsContainerTemplate.class);
393 seedsList.addAll(seeds.getContainer().stream()
394 .map(str -> main.getGson().fromJson(str, PlayerSeedTemplate.class))
395 .collect(Collectors.toList()));
396 seedsList.forEach(seed -> seed.setUniqueID(seed.getUniqueID()));
397 }
398 } else return seedsList;
399 } catch (SQLException e) {
400 e.printStackTrace();
401 } finally {
402 if(!useSQLite) {
403 try {
404 conn.close();
405 } catch (SQLException e) {
406 e.printStackTrace();
407 }
408 }
409 }
410
411 return seedsList;
412 }
413
414 public void getSeedsList(String player, Callback<String> jsonResponse) {
415 Connection conn = getConnection();
416 ResultSet seedsList;
417
418 try(
419 PreparedStatement statement = conn.prepareStatement(getSeedsList)
420 ) {
421 statement.setString(1, player);
422 seedsList = statement.executeQuery();
423
424 if(seedsList.next()) {
425 jsonResponse.done(seedsList.getString(1));
426 } else {
427 jsonResponse.failure(new Exception());
428 }
429
430 seedsList.close();
431 if(!useSQLite) conn.close();
432 } catch (SQLException e) {
433 e.printStackTrace();
434 }
435 }
436
437 public void setSeedsList(String player, String seeds) {
438 getSeedsList(player, new Callback<String>() {
439 @Override
440 public void done(String result) {
441 Connection conn = getConnection();
442
443 try(PreparedStatement statement = conn.prepareStatement(setSeedsList)) {
444 statement.setString(1, seeds);
445 statement.setString(2, player);
446 statement.execute();
447 if(!useSQLite) conn.close();
448 } catch (SQLException e) {
449 e.printStackTrace();
450 }
451 }
452
453 @Override
454 public void failure(Throwable throwable) {
455 Connection conn = getConnection();
456
457 try(PreparedStatement statement = conn.prepareStatement(addNewSeed)) {
458 statement.setString(1, seeds);
459 statement.setString(2, player);
460 statement.execute();
461 if(!useSQLite) conn.close();
462 } catch (SQLException e) {
463 e.printStackTrace();
464 }
465 }
466 });
467 }
468}