· 8 years ago · Oct 05, 2017, 07:34 PM
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 ,"+
31 "`x` INT NOT NULL ,"+
32 "`y` INT NOT NULL ,"+
33 "`z` INT NOT NULL ,"+
34 "`world` VARCHAR(45) NOT NULL ,"+
35 "PRIMARY KEY (`idblocks`,x,y,z,world) );";
36
37
38
39 final String new_table_snitched = "CREATE TABLE `battlesnitch`.`snitched` ("+
40 "`idsnitched` INT NOT NULL ,"+
41 "`player` VARCHAR(45) NOT NULL ,"+
42 "`time` MEDIUMTEXT NULL ,"+
43 "PRIMARY KEY (`idsnitched`, `player`));";
44
45 public boolean DEBUG;
46 public String URL;
47 public String PORT;
48 public String USERNAME;
49 public String PASSWORD;
50 public BattleSnitch plugin;
51
52 public MYSQLController(String url, String port, String username, String password, boolean debug, BattleSnitch instance){
53 DEBUG = debug;
54 URL = url;
55 PORT = port;
56 USERNAME = username;
57 PASSWORD = password;
58 plugin = instance;
59 load();
60 }
61
62 public boolean load(){
63 try {
64 Class.forName("com.mysql.jdbc.Driver");
65 if (DEBUG) System.out.println("Got Driver");
66 } catch (ClassNotFoundException e1) {
67 System.err.println("Failed getting driver");
68 e1.printStackTrace();
69 return false;
70 }
71 String strStmt = "CREATE DATABASE IF NOT EXISTS battlesnitch";
72 try {
73 con = DriverManager.getConnection("jdbc:mysql://"+URL+":" + PORT, USERNAME,PASSWORD);
74 Statement st = con.createStatement();
75 st.executeUpdate(strStmt);
76 if (DEBUG) System.out.println("Creating db");
77 } catch (SQLException e) {
78 System.err.println("Failed creating db: " + strStmt);
79 e.printStackTrace();
80 return false;
81 }
82 try {
83 con = DriverManager.getConnection("jdbc:mysql://"+URL+":" + PORT +"/battlesnitch", USERNAME,PASSWORD);
84 } catch (SQLException e1) {
85 e1.printStackTrace();
86 return false;
87 }
88
89 createTable("desc snitches", new_table_snitches, null);
90 createTable("desc blocks", new_table_blocks, null);
91 createTable("desc snitched", new_table_snitched, null);
92 try {
93 con.setAutoCommit(false);
94 } catch (SQLException e) {
95 e.printStackTrace();
96 }
97
98
99
100 return true;
101 }
102
103 private boolean createTable(String sql_table_exists,
104 String sql_create_table,String sql_create_index) {
105 String strStmt;
106 strStmt = sql_table_exists;
107 /// Check to see if our table exists;
108 boolean table_exists = false;
109 try {
110 Statement st = con.createStatement();
111 st.executeUpdate(strStmt);
112 if (DEBUG) System.out.println("table exists");
113 table_exists = true;
114 } catch (SQLException e) {
115 if (DEBUG) System.out.println("table does not exist");
116 }
117 /// If the table exists nothing left to do
118 if (table_exists)
119 return true;
120 /// Create our table and index
121 strStmt = sql_create_table;
122 Statement st = null;
123 int result =0;
124 try {
125 st = con.createStatement();
126 result = st.executeUpdate(strStmt);
127 if (DEBUG) System.out.println("Created Table with stmt=" + strStmt);
128
129 if (sql_create_index != null){
130 try{
131 st = con.createStatement();
132 st.executeUpdate(sql_create_index);
133 if (DEBUG) System.out.println("Created Index");
134 } catch (Exception e){
135 if (DEBUG) System.err.println("Failed in creating Index");
136 return false;
137 }
138 }
139 } catch (SQLException e) {
140 if (DEBUG) System.err.println("Failed in creating Table " +
141 strStmt + " result=" + result);
142 e.printStackTrace();
143 return false;
144 }
145
146 return true;
147 }
148
149 public int getID(Block block){
150 int x = block.getX();
151 int y = block.getY();
152 int z = block.getZ();
153 String world = block.getWorld().getName();
154 try{
155 PreparedStatement ps = con.prepareStatement("SELECT idsnitches FROM snitches WHERE x = ? AND y = ? AND z = ? and world = ?");
156 ps.setInt(1, x);
157 ps.setInt(2, y);
158 ps.setInt(3, z);
159 ps.setString(4, world);
160 ResultSet rs = ps.executeQuery();
161 if(!rs.next()){
162 return 0;
163 }
164 return (rs.getInt("idsnitches"));
165 }catch (SQLException e){
166 System.out.println("ERROR");
167 e.printStackTrace();
168 return 0;
169 }
170 }
171
172 public boolean makeSnitch(Block block){
173 if(getID(block)==0){
174 int x = block.getX();
175 int y = block.getY();
176 int z = block.getZ();
177 String world = block.getWorld().getName();
178 try{
179 PreparedStatement ps = con.prepareStatement("INSERT INTO snitches (x,y,z,world) VALUES (?,?,?,?)");
180 ps.setInt(1, x);
181 ps.setInt(2, y);
182 ps.setInt(3, z);
183 ps.setString(4, world);
184 ps.execute();
185 con.commit();
186 int id = getID(block);
187 for(int xs = x-5;xs<=x+5;xs++){
188 for(int ys = y-5;ys<=y+5;ys++){
189 for(int zs = z-5;zs<=z+5;zs++){
190 ps = con.prepareStatement("INSERT INTO blocks (idblocks,x,y,z,world) VALUES (?,?,?,?,?)");
191 ps.setInt(1, id);
192 ps.setInt(2, xs);
193 ps.setInt(3, ys);
194 ps.setInt(4, zs);
195 ps.setString(5, world);
196 ps.execute();
197 }
198 }
199 }
200 con.commit();
201 return true;
202 }catch (SQLException e){
203 e.printStackTrace();
204 return false;
205 }
206 }else{
207 breakSnitch(block);
208 return makeSnitch(block);
209 }
210 }
211
212 public boolean breakSnitch(Block block){
213 int id = getID(block);
214 if(id!=0){
215 try{
216 PreparedStatement ps = con.prepareStatement("DELETE FROM blocks WHERE idblocks = ?");
217 ps.setInt(1,id);
218 ps.execute();
219 ps = con.prepareStatement("DELETE FROM snitched WHERE idsnitched = ?");
220 ps.setInt(1,id);
221 ps.execute();
222 ps = con.prepareStatement("DELETE FROM snitches WHERE idsnitches = ?");
223 ps.setInt(1,id);
224 ps.execute();
225 con.commit();
226 return true;
227 }catch (SQLException e){
228 e.printStackTrace();
229 return false;
230 }
231 }else{
232 return false;
233 }
234 }
235
236 public void checkBlock(String player, Block block){
237 int x = block.getX();
238 int y = block.getY();
239 int z = block.getZ();
240 String world = block.getWorld().getName();
241 try{
242 PreparedStatement ps = con.prepareStatement("SELECT idblocks FROM blocks WHERE x=? and y=? and z=? and world=?");
243 ps.setInt(1, x);
244 ps.setInt(2, y);
245 ps.setInt(3, z);
246 ps.setString(4, world);
247 ResultSet rs = ps.executeQuery();
248 while(rs.next()){
249 ps = con.prepareStatement("INSERT INTO snitched VALUES (?,?,?) ON DUPLICATE KEY UPDATE time=?");
250 ps.setInt(1, rs.getInt(1));
251 ps.setString(2, player);
252 ps.setLong(3, System.currentTimeMillis());
253 ps.setLong(4, System.currentTimeMillis());
254 ps.execute();
255 con.commit();
256 }
257 }catch (SQLException e){
258 e.printStackTrace();
259 return;
260 }
261 }
262
263 public void checkHistory(Player player, Block block){
264 int id = getID(block);
265 if(id!=0){
266 try{
267 PreparedStatement ps = con.prepareStatement("SELECT count(*) FROM snitched WHERE idsnitched = ?");
268 ps.setInt(1, id);
269 ResultSet rs = ps.executeQuery();
270 rs.next();
271 if(rs.getInt(1)==0){
272 player.sendMessage(ChatColor.GOLD+"Nobody has been here in the past week.");
273 }else{
274 if(rs.getInt(1)==1){
275 player.sendMessage(ChatColor.GOLD+"1 person has been here in the past week:");
276 }else{
277 player.sendMessage(ChatColor.GOLD+""+rs.getInt(1)+" people have been here in the past week:");
278 }
279 ps = con.prepareStatement("SELECT player FROM snitched WHERE idsnitched = ?");
280 ps.setInt(1, id);
281 rs = ps.executeQuery();
282 while(rs.next()){
283 player.sendMessage(ChatColor.GOLD+rs.getString(1));
284 }
285 }
286 }catch (SQLException e){
287 e.printStackTrace();
288 return;
289 }
290 }else{
291 makeSnitch(block);
292 player.sendMessage(ChatColor.GOLD+"This jukebox is now a snitch block.");
293 }
294 }
295
296 public void cleanRecords(){
297 try{
298 PreparedStatement ps = con.prepareStatement("DELETE FROM snitched WHERE time < ?");
299 ps.setLong(1, System.currentTimeMillis()-15000);
300 ps.execute();
301 }catch (SQLException e){
302 return;
303 }
304 }
305}