· 8 years ago · Oct 06, 2017, 07:18 AM
1package com.battlecraft.zonedabone.battlesnitch.controllers;
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;
9
10import org.bukkit.ChatColor;
11import org.bukkit.Material;
12import org.bukkit.block.Block;
13import org.bukkit.entity.Player;
14
15import com.battlecraft.zonedabone.battlesnitch.BattleSnitch;
16
17public class MYSQLController {
18
19 public Connection con;
20
21 final String new_table_snitches = "CREATE TABLE `battlesnitch`.`snitches` ("
22 + "`idsnitches` INT NOT NULL AUTO_INCREMENT ,"
23 + "`x` INT NOT NULL ,"
24 + "`y` INT NOT NULL ,"
25 + "`z` INT NOT NULL ,"
26 + "`world` VARCHAR(45) NOT NULL ,"
27 + "PRIMARY KEY (`idsnitches`) );";
28
29 final String new_table_blocks = "CREATE TABLE `battlesnitch`.`blocks` ("
30 + "`idblocks` INT NOT NULL ," + "`x` INT NOT NULL ,"
31 + "`y` INT NOT NULL ," + "`z` INT NOT NULL ,"
32 + "`world` VARCHAR(45) NOT NULL ,"
33 + "PRIMARY KEY (`idblocks`,x,y,z,world) );";
34
35 final String new_table_snitched = "CREATE TABLE `battlesnitch`.`snitched` ("
36 + "`idsnitched` INT NOT NULL ,"
37 + "`player` VARCHAR(45) NOT NULL ,"
38 + "`time` MEDIUMTEXT NULL ,"
39 + "PRIMARY KEY (`idsnitched`, `player`));";
40
41 public boolean DEBUG;
42 public String URL;
43 public String PORT;
44 public String USERNAME;
45 public String PASSWORD;
46 public BattleSnitch plugin;
47
48 /**
49 * Initializes the mysql controller and calls load()
50 *
51 * @param url
52 * the url for the databse
53 * @param port
54 * the port for the database
55 * @param username
56 * the username to use for the database
57 * @param password
58 * the password to use for the database
59 * @param debug
60 * whether or not to print debug statements
61 * @param instance
62 * the BattleSnitch plugin instanciating this
63 */
64 public MYSQLController(String url, String port, String username,
65 String password, boolean debug, BattleSnitch instance) {
66 DEBUG = debug;
67 URL = url;
68 PORT = port;
69 USERNAME = username;
70 PASSWORD = password;
71 plugin = instance;
72 load();
73 }
74
75 /**
76 * Loads the database and creates the database and tables. Do not run
77 * directly.
78 *
79 * @return
80 */
81 public boolean load() {
82 try {
83 Class.forName("com.mysql.jdbc.Driver");
84 if (DEBUG)
85 System.out.println("Got Driver");
86 } catch (ClassNotFoundException e1) {
87 System.err.println("Failed getting driver");
88 e1.printStackTrace();
89 return false;
90 }
91 String strStmt = "CREATE DATABASE IF NOT EXISTS battlesnitch";
92 try {
93 con = DriverManager.getConnection("jdbc:mysql://" + URL + ":"
94 + PORT, USERNAME, PASSWORD);
95 Statement st = con.createStatement();
96 st.executeUpdate(strStmt);
97 if (DEBUG)
98 System.out.println("Creating db");
99 } catch (SQLException e) {
100 System.err.println("Failed creating db: " + strStmt);
101 e.printStackTrace();
102 return false;
103 }
104 try {
105 con = DriverManager.getConnection("jdbc:mysql://" + URL + ":"
106 + PORT + "/battlesnitch", USERNAME, PASSWORD);
107 } catch (SQLException e1) {
108 e1.printStackTrace();
109 return false;
110 }
111
112 createTable("desc snitches", new_table_snitches, null);
113 createTable("desc blocks", new_table_blocks, null);
114 createTable("desc snitched", new_table_snitched, null);
115 try {
116 con.setAutoCommit(false);
117 } catch (SQLException e) {
118 e.printStackTrace();
119 }
120
121 return true;
122 }
123
124 /**
125 * Creates a table with the specified index if none exists. Do not run
126 * directly.
127 *
128 * @param sql_table_exists
129 * The query to execute to check if the table exists
130 * @param sql_create_table
131 * The statement to execute to make the table
132 * @param sql_create_index
133 * The statement to execute to make the index, or null to make
134 * none
135 * @return
136 */
137 private boolean createTable(String sql_table_exists,
138 String sql_create_table, String sql_create_index) {
139 String strStmt;
140 strStmt = sql_table_exists;
141 // / Check to see if our table exists;
142 boolean table_exists = false;
143 try {
144 Statement st = con.createStatement();
145 st.executeUpdate(strStmt);
146 if (DEBUG)
147 System.out.println("table exists");
148 table_exists = true;
149 } catch (SQLException e) {
150 if (DEBUG)
151 System.out.println("table does not exist");
152 }
153 // / If the table exists nothing left to do
154 if (table_exists)
155 return true;
156 // / Create our table and index
157 strStmt = sql_create_table;
158 Statement st = null;
159 int result = 0;
160 try {
161 st = con.createStatement();
162 result = st.executeUpdate(strStmt);
163 if (DEBUG)
164 System.out.println("Created Table with stmt=" + strStmt);
165
166 if (sql_create_index != null) {
167 try {
168 st = con.createStatement();
169 st.executeUpdate(sql_create_index);
170 if (DEBUG)
171 System.out.println("Created Index");
172 } catch (Exception e) {
173 if (DEBUG)
174 System.err.println("Failed in creating Index");
175 return false;
176 }
177 }
178 } catch (SQLException e) {
179 if (DEBUG)
180 System.err.println("Failed in creating Table " + strStmt
181 + " result=" + result);
182 e.printStackTrace();
183 return false;
184 }
185
186 return true;
187 }
188
189 /**
190 * returns the database ID associated with a block, or 0 if none exists
191 *
192 * @param block
193 * the block to look up in the database
194 * @return the database ID of that block
195 */
196 public int getID(Block block) {
197 int x = block.getX();
198 int y = block.getY();
199 int z = block.getZ();
200 String world = block.getWorld().getName();
201 try {
202 PreparedStatement ps = con
203 .prepareStatement("SELECT idsnitches FROM snitches WHERE x = ? AND y = ? AND z = ? and world = ?");
204 ps.setInt(1, x);
205 ps.setInt(2, y);
206 ps.setInt(3, z);
207 ps.setString(4, world);
208 ResultSet rs = ps.executeQuery();
209 if (!rs.next()) {
210 return 0;
211 }
212 return (rs.getInt("idsnitches"));
213 } catch (SQLException e) {
214 System.out.println("ERROR");
215 e.printStackTrace();
216 return 0;
217 }
218 }
219
220 /**
221 * Creates the database records for a given block. Will wipe old records for
222 * this block.
223 *
224 * @param block
225 * The block to create the records for
226 * @return
227 */
228 public boolean makeSnitch(Block block) {
229 if (getID(block) == 0) {
230 int x = block.getX();
231 int y = block.getY();
232 int z = block.getZ();
233 String world = block.getWorld().getName();
234 try {
235 PreparedStatement ps = con
236 .prepareStatement("INSERT INTO snitches (x,y,z,world) VALUES (?,?,?,?)");
237 ps.setInt(1, x);
238 ps.setInt(2, y);
239 ps.setInt(3, z);
240 ps.setString(4, world);
241 ps.execute();
242 con.commit();
243 int id = getID(block);
244 for (int xs = x - 5; xs <= x + 5; xs++) {
245 for (int ys = y - 5; ys <= y + 5; ys++) {
246 for (int zs = z - 5; zs <= z + 5; zs++) {
247 ps = con.prepareStatement("INSERT INTO blocks (idblocks,x,y,z,world) VALUES (?,?,?,?,?)");
248 ps.setInt(1, id);
249 ps.setInt(2, xs);
250 ps.setInt(3, ys);
251 ps.setInt(4, zs);
252 ps.setString(5, world);
253 ps.execute();
254 }
255 }
256 }
257 con.commit();
258 return true;
259 } catch (SQLException e) {
260 e.printStackTrace();
261 return false;
262 }
263 } else {
264 breakSnitch(block);
265 return makeSnitch(block);
266 }
267 }
268
269 /**
270 * removes records for a given block
271 *
272 * @param block
273 * the block to remove records from
274 * @return
275 */
276 public boolean breakSnitch(Block block) {
277 int id = getID(block);
278 if (id != 0) {
279 try {
280 PreparedStatement ps = con
281 .prepareStatement("DELETE FROM blocks WHERE idblocks = ?");
282 ps.setInt(1, id);
283 ps.execute();
284 ps = con.prepareStatement("DELETE FROM snitched WHERE idsnitched = ?");
285 ps.setInt(1, id);
286 ps.execute();
287 ps = con.prepareStatement("DELETE FROM snitches WHERE idsnitches = ?");
288 ps.setInt(1, id);
289 ps.execute();
290 con.commit();
291 return true;
292 } catch (SQLException e) {
293 e.printStackTrace();
294 return false;
295 }
296 } else {
297 return false;
298 }
299 }
300
301 /**
302 * check if a player is in a snitch zone in a certain block, and if so
303 * records it
304 *
305 * @param player
306 * the name of the player to check
307 * @param block
308 * the block the player's head is in
309 */
310 public void checkBlock(String player, Block block) {
311 int x = block.getX();
312 int y = block.getY();
313 int z = block.getZ();
314 String world = block.getWorld().getName();
315 try {
316 PreparedStatement ps = con
317 .prepareStatement("SELECT idblocks FROM blocks WHERE x=? and y=? and z=? and world=?");
318 ps.setInt(1, x);
319 ps.setInt(2, y);
320 ps.setInt(3, z);
321 ps.setString(4, world);
322 ResultSet rs = ps.executeQuery();
323 while (rs.next()) {
324 ps = con.prepareStatement("INSERT INTO snitched VALUES (?,?,?) ON DUPLICATE KEY UPDATE time=?");
325 ps.setInt(1, rs.getInt(1));
326 ps.setString(2, player);
327 ps.setLong(3, System.currentTimeMillis());
328 ps.setLong(4, System.currentTimeMillis());
329 ps.execute();
330 }
331 con.commit();
332 } catch (SQLException e) {
333 e.printStackTrace();
334 return;
335 }
336 }
337
338 /**
339 * Shows a player the history of a given block
340 *
341 * @param player
342 * the player to check for
343 * @param block
344 * the block to check
345 */
346 public void checkHistory(Player player, Block block) {
347 int id = getID(block);
348 if (id != 0) {
349 try {
350 PreparedStatement ps = con
351 .prepareStatement("SELECT count(*) FROM snitched WHERE idsnitched = ?");
352 ps.setInt(1, id);
353 ResultSet rs = ps.executeQuery();
354 rs.next();
355 if (rs.getInt(1) == 0) {
356 player.sendMessage(ChatColor.GOLD
357 + "Nobody has been here in the past week.");
358 } else {
359 if (rs.getInt(1) == 1) {
360 player.sendMessage(ChatColor.GOLD
361 + "1 person has been here in the past week:");
362 } else {
363 player.sendMessage(ChatColor.GOLD + "" + rs.getInt(1)
364 + " people have been here in the past week:");
365 }
366 ps = con.prepareStatement("SELECT player FROM snitched WHERE idsnitched = ?");
367 ps.setInt(1, id);
368 rs = ps.executeQuery();
369 while (rs.next()) {
370 player.sendMessage(ChatColor.GOLD + rs.getString(1));
371 }
372 }
373 } catch (SQLException e) {
374 e.printStackTrace();
375 return;
376 }
377 } else {
378 makeSnitch(block);
379 player.sendMessage(ChatColor.GOLD
380 + "This jukebox is now a snitch block.");
381 }
382 }
383
384 /**
385 * Cleans out records older than a week
386 */
387 public void cleanRecords() {
388 try {
389 PreparedStatement ps = con
390 .prepareStatement("DELETE FROM snitched WHERE time < ?");
391 ps.setLong(1, System.currentTimeMillis() - 604800000);
392 ps.execute();
393 } catch (SQLException e) {
394 return;
395 }
396 }
397}