Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package ru.progrm_jarvis.contentmakers.data.sql;
- import jline.internal.Nullable;
- import net.md_5.bungee.BungeeCord;
- import net.md_5.bungee.api.scheduler.ScheduledTask;
- import net.md_5.bungee.config.Configuration;
- import net.md_5.bungee.scheduler.BungeeScheduler;
- import ru.progrm_jarvis.contentmakers.ContentMakersPlugin;
- import ru.progrm_jarvis.contentmakers.config.ConfigManager;
- import ru.progrm_jarvis.contentmakers.console.Tracer;
- import ru.progrm_jarvis.contentmakers.data.sql.callback.SqlCallback;
- import ru.progrm_jarvis.contentmakers.player.stats.SqlStatsManager;
- import java.sql.*;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.List;
- import java.util.Map;
- /**
- * Created by progrm_jarvis on 14.06.17.
- */
- public class SQLManager {
- private static ContentMakersPlugin plugin;
- public static boolean enabled = false;
- private static BungeeScheduler scheduler;
- private static String host = "localhost", user = "root", database = "ContentMakers", password = "password";
- private static int port = 3306;
- private static Connection connection;
- /**
- * Initialise the class
- */
- public static void init() {
- Tracer.msg("1");
- //Set plugin
- plugin = ContentMakersPlugin.getInstance();
- Tracer.msg("2");
- //Set scheduler
- scheduler = BungeeCord.getInstance().getScheduler();
- Tracer.msg("3");
- //Load configs data
- loadCfg();
- Tracer.msg("4");
- //Open connection
- openConnection();
- Tracer.msg("5");
- //Init all classes using SQL
- SqlStatsManager.init();
- }
- /**
- * Load all configurable data from default config
- */
- public static void loadCfg() {
- Configuration config = ConfigManager.cfg("config.yml").getSection("database.mysql");
- if (config != null) {
- enabled = config.getBoolean("enabled");
- if (enabled) {
- //Set values
- host = config.getString("host");
- port = config.getInt("port");
- user = config.getString("user");
- database = config.getString("database");
- password = config.getString("password");
- //Announce options loaded
- announceOptionsLoaded();
- }
- } else {
- Tracer.msg("MySQL has not been enabled");
- enabled = false;
- }
- }
- /**
- Announce that MySQL has been enabled and output info
- */
- public static void announceOptionsLoaded() {
- String password = "";
- for (int i = 0; i < SQLManager.password.length(); i++) password = password.concat("*");
- Tracer.msg("MySQL has been enabled:" +
- "\n host: " + host +
- "\n port: " + port +
- "\n user: " + user +
- "\n database: " + database +
- "\n password: " + password
- );
- }
- /**
- * Opens connection to the specified database
- */
- public static void openConnection() {
- if (enabled) {
- try {
- if (connection != null && !connection.isClosed()) return;
- } catch (SQLException e) {
- traceException(e);
- }
- synchronized (ContentMakersPlugin.getInstance()) {
- try {
- if (connection != null && !connection.isClosed()) return;
- } catch (SQLException e) {
- traceException(e);
- }
- try {
- Class.forName("com.mysql.jdbc.Driver");
- connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database,
- user, password
- );
- enabled = true;
- Tracer.msg("Successfully connected to MySQL!");
- } catch (ClassNotFoundException | SQLException e) {
- enabled = false;
- traceException(e);
- }
- }
- }
- }
- private static List<ScheduledTask> tasks = new ArrayList<>();
- /**
- * Executes async SQL-Query or SQL-Update (depending on need of result)
- * @param callback callback to be called on Success (if not null)
- * @param result whether to require SQL-result (true - Query, false - Update)
- * @param sqls SQL-Commands to be executed
- */
- public static void executeAsync(@Nullable SqlCallback callback, boolean result, String... sqls) {
- if (enabled) {
- tasks.add(scheduler.runAsync(plugin, () -> {
- try (Statement statement = connection.createStatement()) {
- if (result) {
- List<ResultSet> results = new ArrayList<>();
- for (String sql : sqls) results.add(statement.executeQuery(sql));
- if (callback != null) callback.call(results);
- } else {
- for (String sql : sqls) statement.executeUpdate(sql);
- if (callback != null) callback.call(null);
- }
- } catch (SQLException e) {
- traceException(e);
- }
- }));
- }
- }
- /**
- * Closes connection (mostly for server stop/reload)
- */
- public static void closeConnection() {
- try {
- if (!connection.isClosed()) connection.close();
- } catch (SQLException e) {
- traceException(e);
- }
- }
- /**
- * Simple method to generate the table without an Exception that it already exists
- * @param table name of table to create
- * @param properties properties of table to create
- * @param callback callback to be called on Success
- */
- public static void createTable(String table, String properties, @Nullable SqlCallback callback) {
- String sql = "CREATE TABLE IF NOT EXISTS `" + table + "` (" + properties + ")";
- executeAsync(callback, false, sql);
- }
- /**
- * Hardcoded method of adding columns
- * (should not generate Exceptions because of Async if only it's not used before table exists)
- * @param table name of table to insert the column
- * @param column name of column to insert
- * @param properties column properties
- * @param callback callback to be called on Success
- */
- public static void addColumn(String table, String column, String properties, @Nullable SqlCallback callback) {
- if (enabled) {
- tasks.add(scheduler.runAsync(plugin, () -> {
- String sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + table + "' " +
- "AND COLUMN_NAME='" + column + "'";
- Tracer.msg(sql);
- try (ResultSet result = connection.createStatement().executeQuery(sql)) {
- if (isSqlResultNull(result)) {
- Tracer.sql("Column " + column + " was not found in table " + table + ". Adding it.");
- sql = "ALTER TABLE `" + table + "` ADD `" + column + "` " + properties;
- executeAsync(null, false, sql);
- } else Tracer.sql("Column " + column + " was found in table " + table);
- } catch (SQLException e) {
- traceException(e);
- }
- }));
- }
- }
- /**
- * Safe method to put or update some data to the table
- * @param table name of table to add data into
- * @param key name of field by which to recognise the line to add data into
- * @param valueOfKey value of field by which to recognise the line to add data into
- * @param params data to add
- * @param callback callback to be called on Success
- */
- public static void update(String table, String key, String valueOfKey, Map<String, Object> params,
- @Nullable SqlCallback callback) {
- tasks.add(scheduler.runAsync(plugin, () -> {
- //Проверка на наличие в БД
- String sql = "SELECT * FROM `" + table + "` WHERE `" + key + "`='" + valueOfKey + "'";
- Tracer.msg(sql);
- try (ResultSet result = connection.createStatement().executeQuery(sql)) {
- if (isSqlResultNull(result)) {
- //Игрока нет в БД
- Tracer.sql("not found in table");
- //
- sql = "INSERT INTO `" + table + "` (`" + key + "`) VALUES ('" + valueOfKey + "')";
- executeAsync(null, false, sql);
- } else Tracer.sql("found in table");
- //Setting data for user
- //Generate changes
- sql = "UPDATE `" + table + "` SET " + mapToSqlParams(params) +
- " WHERE " + key + "='" + valueOfKey + "'";
- Tracer.msg(sql);
- SQLManager.executeAsync(callback, false, sql);
- } catch (SQLException e) {
- traceException(e);
- }
- }));
- }
- /**
- * Simple transformation of <code>Map"<"String, Object">"</code> to Sql String of params (`key`="value")
- * @param params Map of key and value of some SQL field
- * @return String of SQL params
- */
- public static String mapToSqlParams(Map<String, Object> params) {
- StringBuilder paramsAsString = new StringBuilder();
- for (String valuesKey : params.keySet()) paramsAsString.append("`").append(valuesKey).append("`='")
- .append(params.get(valuesKey)).append("',");
- paramsAsString = new StringBuilder(paramsAsString.substring(0, paramsAsString.length() - 1));
- Tracer.sql("CHANGES: " + paramsAsString);
- return paramsAsString.toString();
- }
- /**
- * Simplifies error logging of Sql-Related Exceptions
- * @param e exception to be traced
- */
- public static void traceException(Exception e) {
- Tracer.err("MySQL-related exception caught while trying to open connection:");
- e.printStackTrace();
- }
- /**
- * Checks if given <code>ResultSet</code> is a null result
- * @param result result of SQL Query
- * @return whether or not result was null (true if Exception was caught)
- */
- private static boolean isSqlResultNull(ResultSet result) {
- try {
- return !result.next();
- } catch (SQLException e) {
- traceException(e);
- return true;
- }
- }
- private static final SimpleDateFormat dateFormat = new SimpleDateFormat("YYYY-MM-dd hh:mm:ss");
- /**
- * Generates current TimeStamp by format "YYYY-MM-dd hh:mm:ss"
- * @return current TimeStamp ready for SQL
- */
- public static String currentTimestamp() {
- return dateFormat.format(new Timestamp(System.currentTimeMillis()));
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement