· 9 years ago · Aug 17, 2017, 09:02 PM
1package ru.progrm_jarvis.contentmakers.data.sql;
2
3import jline.internal.Nullable;
4import net.md_5.bungee.BungeeCord;
5import net.md_5.bungee.api.scheduler.ScheduledTask;
6import net.md_5.bungee.config.Configuration;
7import net.md_5.bungee.scheduler.BungeeScheduler;
8import ru.progrm_jarvis.contentmakers.ContentMakersPlugin;
9import ru.progrm_jarvis.contentmakers.config.ConfigManager;
10import ru.progrm_jarvis.contentmakers.console.Tracer;
11import ru.progrm_jarvis.contentmakers.data.sql.callback.SqlCallback;
12import ru.progrm_jarvis.contentmakers.player.stats.SqlStatsManager;
13
14
15import java.sql.*;
16import java.text.SimpleDateFormat;
17import java.util.ArrayList;
18import java.util.List;
19import java.util.Map;
20
21/**
22 * Created by progrm_jarvis on 14.06.17.
23 */
24public class SQLManager {
25 private static ContentMakersPlugin plugin;
26
27 public static boolean enabled = false;
28
29 private static BungeeScheduler scheduler;
30
31 private static String host = "localhost", user = "root", database = "ContentMakers", password = "password";
32 private static int port = 3306;
33
34 private static Connection connection;
35
36 /**
37 * Initialise the class
38 */
39 public static void init() {
40 Tracer.msg("1");
41 //Set plugin
42 plugin = ContentMakersPlugin.getInstance();
43 Tracer.msg("2");
44 //Set scheduler
45 scheduler = BungeeCord.getInstance().getScheduler();
46
47 Tracer.msg("3");
48 //Load configs data
49 loadCfg();
50
51 Tracer.msg("4");
52 //Open connection
53 openConnection();
54
55 Tracer.msg("5");
56 //Init all classes using SQL
57 SqlStatsManager.init();
58 }
59
60 /**
61 * Load all configurable data from default config
62 */
63 public static void loadCfg() {
64 Configuration config = ConfigManager.cfg("config.yml").getSection("database.mysql");
65 if (config != null) {
66 enabled = config.getBoolean("enabled");
67 if (enabled) {
68 //Set values
69 host = config.getString("host");
70 port = config.getInt("port");
71 user = config.getString("user");
72 database = config.getString("database");
73 password = config.getString("password");
74
75 //Announce options loaded
76 announceOptionsLoaded();
77 }
78 } else {
79 Tracer.msg("MySQL has not been enabled");
80 enabled = false;
81 }
82 }
83
84 /**
85 Announce that MySQL has been enabled and output info
86 */
87 public static void announceOptionsLoaded() {
88 String password = "";
89 for (int i = 0; i < SQLManager.password.length(); i++) password = password.concat("*");
90 Tracer.msg("MySQL has been enabled:" +
91 "\n host: " + host +
92 "\n port: " + port +
93 "\n user: " + user +
94 "\n database: " + database +
95 "\n password: " + password
96 );
97 }
98
99 /**
100 * Opens connection to the specified database
101 */
102 public static void openConnection() {
103 if (enabled) {
104 try {
105 if (connection != null && !connection.isClosed()) return;
106 } catch (SQLException e) {
107 traceException(e);
108 }
109
110 synchronized (ContentMakersPlugin.getInstance()) {
111 try {
112 if (connection != null && !connection.isClosed()) return;
113 } catch (SQLException e) {
114 traceException(e);
115 }
116 try {
117 Class.forName("com.mysql.jdbc.Driver");
118 connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database,
119 user, password
120 );
121 enabled = true;
122 Tracer.msg("Successfully connected to MySQL!");
123 } catch (ClassNotFoundException | SQLException e) {
124 enabled = false;
125 traceException(e);
126 }
127 }
128 }
129 }
130
131 private static List<ScheduledTask> tasks = new ArrayList<>();
132
133 /**
134 * Executes async SQL-Query or SQL-Update (depending on need of result)
135 * @param callback callback to be called on Success (if not null)
136 * @param result whether to require SQL-result (true - Query, false - Update)
137 * @param sqls SQL-Commands to be executed
138 */
139 public static void executeAsync(@Nullable SqlCallback callback, boolean result, String... sqls) {
140 if (enabled) {
141 tasks.add(scheduler.runAsync(plugin, () -> {
142 try (Statement statement = connection.createStatement()) {
143 if (result) {
144 List<ResultSet> results = new ArrayList<>();
145 for (String sql : sqls) results.add(statement.executeQuery(sql));
146 if (callback != null) callback.call(results);
147 } else {
148 for (String sql : sqls) statement.executeUpdate(sql);
149 if (callback != null) callback.call(null);
150 }
151 } catch (SQLException e) {
152 traceException(e);
153 }
154 }));
155 }
156 }
157
158 /**
159 * Closes connection (mostly for server stop/reload)
160 */
161 public static void closeConnection() {
162 try {
163 if (!connection.isClosed()) connection.close();
164 } catch (SQLException e) {
165 traceException(e);
166 }
167 }
168
169 /**
170 * Simple method to generate the table without an Exception that it already exists
171 * @param table name of table to create
172 * @param properties properties of table to create
173 * @param callback callback to be called on Success
174 */
175 public static void createTable(String table, String properties, @Nullable SqlCallback callback) {
176 String sql = "CREATE TABLE IF NOT EXISTS `" + table + "` (" + properties + ")";
177 executeAsync(callback, false, sql);
178 }
179
180 /**
181 * Hardcoded method of adding columns
182 * (should not generate Exceptions because of Async if only it's not used before table exists)
183 * @param table name of table to insert the column
184 * @param column name of column to insert
185 * @param properties column properties
186 * @param callback callback to be called on Success
187 */
188 public static void addColumn(String table, String column, String properties, @Nullable SqlCallback callback) {
189 if (enabled) {
190 tasks.add(scheduler.runAsync(plugin, () -> {
191 String sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + table + "' " +
192 "AND COLUMN_NAME='" + column + "'";
193 Tracer.msg(sql);
194 try (ResultSet result = connection.createStatement().executeQuery(sql)) {
195 if (isSqlResultNull(result)) {
196 Tracer.sql("Column " + column + " was not found in table " + table + ". Adding it.");
197 sql = "ALTER TABLE `" + table + "` ADD `" + column + "` " + properties;
198 executeAsync(null, false, sql);
199 } else Tracer.sql("Column " + column + " was found in table " + table);
200 } catch (SQLException e) {
201 traceException(e);
202 }
203 }));
204 }
205 }
206
207 /**
208 * Safe method to put or update some data to the table
209 * @param table name of table to add data into
210 * @param key name of field by which to recognise the line to add data into
211 * @param valueOfKey value of field by which to recognise the line to add data into
212 * @param params data to add
213 * @param callback callback to be called on Success
214 */
215 public static void update(String table, String key, String valueOfKey, Map<String, Object> params,
216 @Nullable SqlCallback callback) {
217 tasks.add(scheduler.runAsync(plugin, () -> {
218 //Проверка на наличие в БД
219 String sql = "SELECT * FROM `" + table + "` WHERE `" + key + "`='" + valueOfKey + "'";
220 Tracer.msg(sql);
221 try (ResultSet result = connection.createStatement().executeQuery(sql)) {
222 if (isSqlResultNull(result)) {
223 //Игрока нет в БД
224 Tracer.sql("not found in table");
225 //
226 sql = "INSERT INTO `" + table + "` (`" + key + "`) VALUES ('" + valueOfKey + "')";
227 executeAsync(null, false, sql);
228 } else Tracer.sql("found in table");
229 //Setting data for user
230 //Generate changes
231 sql = "UPDATE `" + table + "` SET " + mapToSqlParams(params) +
232 " WHERE " + key + "='" + valueOfKey + "'";
233 Tracer.msg(sql);
234 SQLManager.executeAsync(callback, false, sql);
235
236 } catch (SQLException e) {
237 traceException(e);
238 }
239 }));
240 }
241
242 /**
243 * Simple transformation of <code>Map"<"String, Object">"</code> to Sql String of params (`key`="value")
244 * @param params Map of key and value of some SQL field
245 * @return String of SQL params
246 */
247 public static String mapToSqlParams(Map<String, Object> params) {
248 StringBuilder paramsAsString = new StringBuilder();
249 for (String valuesKey : params.keySet()) paramsAsString.append("`").append(valuesKey).append("`='")
250 .append(params.get(valuesKey)).append("',");
251 paramsAsString = new StringBuilder(paramsAsString.substring(0, paramsAsString.length() - 1));
252 Tracer.sql("CHANGES: " + paramsAsString);
253 return paramsAsString.toString();
254 }
255
256 /**
257 * Simplifies error logging of Sql-Related Exceptions
258 * @param e exception to be traced
259 */
260 public static void traceException(Exception e) {
261 Tracer.err("MySQL-related exception caught while trying to open connection:");
262 e.printStackTrace();
263 }
264
265 /**
266 * Checks if given <code>ResultSet</code> is a null result
267 * @param result result of SQL Query
268 * @return whether or not result was null (true if Exception was caught)
269 */
270 private static boolean isSqlResultNull(ResultSet result) {
271 try {
272 return !result.next();
273 } catch (SQLException e) {
274 traceException(e);
275 return true;
276 }
277 }
278
279 private static final SimpleDateFormat dateFormat = new SimpleDateFormat("YYYY-MM-dd hh:mm:ss");
280
281 /**
282 * Generates current TimeStamp by format "YYYY-MM-dd hh:mm:ss"
283 * @return current TimeStamp ready for SQL
284 */
285 public static String currentTimestamp() {
286 return dateFormat.format(new Timestamp(System.currentTimeMillis()));
287 }
288}