· 6 years ago · Dec 25, 2019, 04:30 PM
1package net.venixdevelopment.util;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.util.UUID;
10
11import org.bukkit.Bukkit;
12import org.bukkit.inventory.Inventory;
13import org.bukkit.scheduler.BukkitRunnable;
14
15import net.venixdevelopment.VenixFFA;
16import net.venixdevelopment.listener.JoinListener;
17
18public class SQL {
19
20 private static SQL instance;
21 private static Connection con;
22 private String table = VenixFFA.getInstance().getConfig().getString("SQL.table");
23
24 public static SQL getInstance() {
25 if (instance == null) {
26 instance = new SQL();
27 }
28 return instance;
29 }
30
31 public Connection getConnection() {
32 return con;
33 }
34
35 public synchronized void openSQLConnection() {
36 new BukkitRunnable() {
37 @Override
38 public void run() {
39 try {
40 con = DriverManager.getConnection(
41 "jdbc:mysql://" + VenixFFA.getInstance().getConfig().getString("SQL.ip") + ":3306/"
42 + VenixFFA.getInstance().getConfig().getString("SQL.database"),
43 VenixFFA.getInstance().getConfig().getString("SQL.user"),
44 VenixFFA.getInstance().getConfig().getString("SQL.password"));
45 Bukkit.getLogger().info("[FFA][SQL] Successfully connected!");
46 } catch (SQLException e) {
47 Bukkit.getLogger().info("[FFA][SQL] Connection failed!");
48 }
49 }
50 }.runTaskAsynchronously(VenixFFA.getInstance());
51 }
52
53 public void closeConnection() {
54 try {
55 getConnection().close();
56 } catch (SQLException e) {
57 System.out.print(e.getMessage());
58 }
59 }
60
61 public void newTables() {
62 Connection connection = getConnection();
63 try {
64 Statement statement1 = connection.createStatement();
65 Statement statement2 = connection.createStatement();
66 try {
67 statement1.executeUpdate("CREATE TABLE IF NOT EXISTS `" + table
68 + "`(`uuid` VARCHAR(60),`kills` INT NOT NULL DEFAULT '0',`deaths` INT NOT NULL DEFAULT '0',`kd` DECIMAL(11,2) NOT NULL DEFAULT '0.0')");
69 statement2.executeUpdate(
70 "CREATE TABLE IF NOT EXISTS `ffa_kits`(`uuid` VARCHAR(36), `layout` VARCHAR(9999))");
71 } catch (SQLException e) {
72 e.printStackTrace();
73 } finally {
74 try {
75 assert (statement1 != null);
76 statement1.close();
77
78 assert (statement2 != null);
79 statement2.close();
80
81 } catch (SQLException e) {
82 e.printStackTrace();
83 }
84 }
85 return;
86 } catch (SQLException e) {
87 e.printStackTrace();
88 }
89 }
90
91 public void dropTable() {
92 Connection connection = getConnection();
93 try {
94 Statement statement1 = connection.createStatement();
95 try {
96 statement1.executeUpdate("DROP TABLE `ffa_kits`");
97 } catch (SQLException e) {
98 e.printStackTrace();
99 } finally {
100 assert (statement1 != null);
101 statement1.close();
102
103 }
104 } catch (SQLException e) {
105 e.printStackTrace();
106 }
107 }
108
109 public boolean checkForData(String uuid) {
110 PreparedStatement statement1 = null;
111 try {
112 statement1 = getConnection().prepareStatement("SELECT * FROM `" + table + "` WHERE `uuid` = ?;");
113 statement1.setString(1, uuid);
114 statement1.executeQuery();
115 ResultSet rs = statement1.getResultSet();
116 if (rs.next()) {
117 return true;
118 }
119 } catch (SQLException e) {
120 e.printStackTrace();
121 } finally {
122 try {
123 assert (statement1 != null);
124 statement1.close();
125
126 } catch (SQLException e) {
127 e.printStackTrace();
128 }
129 }
130 return false;
131 }
132
133 public void createInv(String uuid, String inventory) {
134 PreparedStatement statement = null;
135 try {
136 statement = getConnection().prepareStatement("INSERT INTO `ffa_kits` (uuid, layout) VALUES (?, ?);");
137 statement.setString(1, uuid);
138 statement.setString(2, inventory);
139 statement.executeUpdate();
140 } catch (SQLException e) {
141 System.out.print(e.getMessage());
142
143 } finally {
144 try {
145 assert (statement != null);
146 statement.close();
147
148 } catch (SQLException e) {
149 System.out.print(e.getMessage());
150 }
151 }
152 }
153
154 public Inventory getInv(String uuid) {
155 PreparedStatement statement = null;
156 try {
157 statement = getConnection().prepareStatement("SELECT `layout` FROM `ffa_kits` WHERE `uuid` = ?;");
158 statement.setString(1, uuid);
159 statement.executeQuery();
160 ResultSet rs = statement.getResultSet();
161 if (rs.next()) {
162 return InventorySerializer.stringToInventory(rs.getString("layout"));
163 }
164 } catch (SQLException e) {
165 System.out.println(e.getMessage());
166 } finally {
167 try {
168 assert (statement != null);
169 statement.close();
170
171 } catch (SQLException e) {
172 e.printStackTrace();
173 }
174 }
175 return null;
176 }
177
178 public void updateInventory(String uuid, String inventory) {
179 if (getInv(uuid) == null) {
180 createInv(uuid, inventory);
181 return;
182 }
183 PreparedStatement statement = null;
184 try {
185 statement = getConnection().prepareStatement("UPDATE `ffa_kits` SET `layout` = ? WHERE `uuid` = ?;");
186 statement.setString(1, inventory);
187 statement.setString(2, uuid);
188 statement.executeUpdate();
189 } catch (SQLException e) {
190 System.out.println(e.getMessage());
191 } finally {
192 try {
193 assert (statement != null);
194 statement.close();
195
196 } catch (SQLException e) {
197 System.out.println(e.getMessage());
198 }
199 }
200 }
201
202 public void createPlayer(String uuid) {
203 PreparedStatement statement = null;
204 try {
205 statement = getConnection()
206 .prepareStatement("INSERT INTO `" + table + "` (uuid, kills, deaths, kd) VALUES (?, ?, ?, ?);");
207 statement.setString(1, uuid);
208 statement.setInt(2, 0);
209 statement.setInt(3, 0);
210 statement.setDouble(4, 0.0);
211 statement.executeUpdate();
212 } catch (SQLException e) {
213 e.printStackTrace();
214 } finally {
215 try {
216 assert (statement != null);
217 statement.close();
218
219 } catch (SQLException e) {
220 e.printStackTrace();
221 }
222 }
223 }
224
225 public void updateStats(String uuid, int kills, int deaths, double kd) {
226 PreparedStatement statement = null;
227 try {
228 statement = getConnection().prepareStatement(
229 "UPDATE `" + table + "` SET `kills` = ?, `deaths` = ?, `kd` = ? WHERE `uuid` = ?;");
230 statement.setInt(1, kills);
231 statement.setInt(2, deaths);
232 statement.setDouble(3, kd);
233 statement.setString(4, uuid);
234 statement.executeUpdate();
235 } catch (SQLException e) {
236 e.printStackTrace();
237 } finally {
238 try {
239 assert (statement != null);
240 statement.close();
241
242 } catch (SQLException e) {
243 e.printStackTrace();
244 }
245 }
246 }
247
248 public void loadData(UUID uuid) {
249 new BukkitRunnable() {
250 @Override
251 public void run() {
252 try {
253 PreparedStatement statement = getConnection()
254 .prepareStatement("SELECT * FROM `" + table + "` WHERE `uuid` = ?;");
255 statement.setString(1, uuid.toString());
256 statement.executeQuery();
257
258 ResultSet rs = statement.getResultSet();
259 if (rs.isBeforeFirst()) {
260 while (rs.next()) {
261 JoinListener.kills.put(uuid.toString(), rs.getInt("kills"));
262 JoinListener.deaths.put(uuid.toString(), rs.getInt("deaths"));
263 JoinListener.kd.put(uuid.toString(), rs.getDouble("kd"));
264 }
265 }
266 rs.close();
267 statement.close();
268 } catch (SQLException e) {
269 System.out.print(e.getMessage());
270 }
271 }
272 }.runTaskAsynchronously(VenixFFA.getInstance());
273 }
274}