· 7 years ago · Jan 18, 2019, 06:46 PM
1package net.woodpixel.mysql;
2
3import java.sql.DriverManager;
4import java.sql.ResultSet;
5import java.sql.SQLException;
6import java.util.UUID;
7
8import org.bukkit.OfflinePlayer;
9import org.bukkit.entity.Player;
10
11import com.mysql.jdbc.Connection;
12import com.mysql.jdbc.Statement;
13
14import net.woodpixel.main.Main;
15
16public class DatabaseHandler {
17
18 public final static String COOL_STARKICK = "cooldown_startkick";
19 public final static String COOL_KOPF = "cooldown_kopf";
20 public final static String COOL_MUTEP = "cooldown_mutep";
21 public final static String SCORE_TOGGLE = "toggled_sb";
22
23 public static void openConnection() throws SQLException, ClassNotFoundException {
24 if (Main.connection != null && !Main.connection.isClosed()) {
25 return;
26 }
27
28
29 if (Main.connection != null && !Main.connection.isClosed()) {
30 return;
31 }
32 Class.forName("com.mysql.jdbc.Driver");
33 Main.connection = (Connection) DriverManager.getConnection("jdbc:mysql://" + Main.host+ ":" + Main.port + "/" + Main.database, Main.username, Main.password);
34
35 }
36
37 public static boolean isClosed() throws SQLException {
38 return Main.connection == null || Main.connection.isClosed() || !Main.connection.isValid(0);
39 }
40
41 public static String getOt(Player p) throws SQLException, ClassNotFoundException {
42
43 if (isClosed()) {
44 openConnection();
45 }
46
47 long milliseconds = 0;
48
49 Statement statement = (Statement) Main.connection.createStatement();
50 ResultSet result = statement.executeQuery("SELECT * FROM onlineTimes WHERE uuid = \"" + p.getUniqueId() + "\";");
51 while (result.next()) {
52 long jointime = result.getLong("joinTime");
53 long leavetime = result.getLong("leaveTime");
54 long afktime = result.getLong("afkTime");
55 milliseconds += leavetime - jointime - afktime;
56 }
57 int minutes = (int) ((milliseconds / (1000 * 60)) % 60);
58 int hours = (int) ((milliseconds / (1000 * 60 * 60)));
59 return hours + "h " + minutes + "m";
60
61 }
62
63 public static void createTable() throws Exception {
64 String sql = "CREATE TABLE IF NOT EXISTS " + COOL_KOPF + " (uuid VARCHAR(36), send BIGINT);";
65 String sql2 = "CREATE TABLE IF NOT EXISTS " + COOL_MUTEP + " (uuid VARCHAR(36), send BIGINT);";
66 String sql3 = "CREATE TABLE IF NOT EXISTS " + COOL_STARKICK + " (uuid VARCHAR(36), send BIGINT);";
67 String sql4 = "CREATE TABLE IF NOT EXISTS " + SCORE_TOGGLE + " (uuid VARCHAR(36), statement BOOLEAN);";
68 execute(sql);
69 execute(sql2);
70 execute(sql3);
71 execute(sql4);
72 }
73
74 public static void addKopf(UUID uuid, long send) throws Exception {
75 String sql = "INSERT INTO " + COOL_KOPF + " (uuid, send) VALUES ('" + uuid + "', '" + send + "');";
76 execute(sql);
77 }
78
79 public static void addStartkick(UUID uuid, long send) throws Exception {
80 String sql = "INSERT INTO " + COOL_STARKICK + " (uuid, send) VALUES ('" + uuid + "', '" + send + "');";
81 execute(sql);
82 }
83
84 public static void addMute(UUID uuid, long send) throws Exception {
85 String sql = "INSERT INTO " + COOL_MUTEP + " (uuid, send) VALUES ('" + uuid + "', '" + send + "');";
86 execute(sql);
87 }
88
89 public static void addSbToggle (UUID uuid, boolean statement) throws Exception {
90 String sql = "INSERT INTO " + SCORE_TOGGLE + " (uuid, statement) VALUES ('" + uuid + "', '" + statement + "');";
91 execute(sql);
92 }
93
94 private static void execute(String sql) throws Exception {
95 if (isClosed()) {
96 openConnection();
97 }
98
99 Statement statement = (Statement) Main.connection.createStatement();
100 statement.executeUpdate(sql);
101 statement.close();
102 }
103
104 public static void deleteKopf(Player p) throws Exception {
105 if (isClosed()) {
106 openConnection();
107 }
108
109 String sql = "DELETE * FROM " + COOL_KOPF + " WHERE uuid = \"" + p.getUniqueId() + "\";";
110 execute(sql);
111 }
112
113 public static void deleteStartkick(Player p) throws Exception {
114 if (isClosed()) {
115 openConnection();
116 }
117
118 String sql = "DELETE * FROM " + COOL_STARKICK + " WHERE uuid = \"" + p.getUniqueId() + "\";";
119 execute(sql);
120 }
121
122 public static long getStartkick(Player p) throws SQLException, ClassNotFoundException {
123
124 if (isClosed()) {
125 openConnection();
126 }
127
128 long remain = 0;
129
130 Statement statement = (Statement) Main.connection.createStatement();
131 ResultSet result = statement.executeQuery("SELECT * FROM cooldown_startkick WHERE uuid = \"" + p.getUniqueId() + "\";");
132 while (result.next()) {
133 long remaining = result.getLong("send");
134 remain += remaining;
135 }
136 return remain;
137
138 }
139
140 public static long getKopf(Player p) throws SQLException, ClassNotFoundException {
141
142 if (isClosed()) {
143 openConnection();
144 }
145
146 long remain = 0;
147
148 Statement statement = (Statement) Main.connection.createStatement();
149 ResultSet result = statement.executeQuery("SELECT * FROM cooldown_kopf WHERE uuid = \"" + p.getUniqueId() + "\";");
150 while (result.next()) {
151 long remaining = result.getLong("send");
152 remain += remaining;
153 }
154 return remain;
155
156 }
157
158 public static boolean getSbToggle(Player p) throws ClassNotFoundException, SQLException {
159 if (isClosed()) {
160 openConnection();
161 }
162
163 Statement statement = (Statement) Main.connection.createStatement();
164 ResultSet result = statement.executeQuery("SELECT * FROM toggled_sb WHERE uuid = \"" + p.getUniqueId() + "\";");
165
166 boolean togglestate = result.getBoolean("statement");
167 if (togglestate == true) {
168 return true;
169 }
170 else {
171 return false;
172 }
173 }
174
175 public static void getSbToggleOverwrite(Player p) throws Exception {
176 if (isClosed()) {
177 openConnection();
178 }
179 String sql = "DELETE * FROM toggled_sb WHERE uuid = \"" + p.getUniqueId() + "\";";
180 execute(sql);
181 }
182
183 public static String getOtOff(OfflinePlayer p) throws SQLException, ClassNotFoundException {
184
185 if (isClosed()) {
186 openConnection();
187 }
188
189 long milliseconds = 0;
190
191 Statement statement = (Statement) Main.connection.createStatement();
192 ResultSet result = statement.executeQuery("SELECT * FROM onlineTimes WHERE uuid = \"" + p.getUniqueId() + "\";");
193 while (result.next()) {
194 long jointime = result.getLong("joinTime");
195 long leavetime = result.getLong("leaveTime");
196 long afktime = result.getLong("afkTime");
197 milliseconds += leavetime - jointime - afktime;
198 }
199 int minutes = (int) ((milliseconds / (1000 * 60)) % 60);
200 int hours = (int) ((milliseconds / (1000 * 60 * 60)));
201 return hours + "h " + minutes + "m";
202
203 }
204
205}