Advertisement
PROgrm_JARvis

Sql

Aug 17th, 2017
98
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 10.97 KB | None | 0 0
  1. package ru.progrm_jarvis.contentmakers.data.sql;
  2.  
  3. import jline.internal.Nullable;
  4. import net.md_5.bungee.BungeeCord;
  5. import net.md_5.bungee.api.scheduler.ScheduledTask;
  6. import net.md_5.bungee.config.Configuration;
  7. import net.md_5.bungee.scheduler.BungeeScheduler;
  8. import ru.progrm_jarvis.contentmakers.ContentMakersPlugin;
  9. import ru.progrm_jarvis.contentmakers.config.ConfigManager;
  10. import ru.progrm_jarvis.contentmakers.console.Tracer;
  11. import ru.progrm_jarvis.contentmakers.data.sql.callback.SqlCallback;
  12. import ru.progrm_jarvis.contentmakers.player.stats.SqlStatsManager;
  13.  
  14.  
  15. import java.sql.*;
  16. import java.text.SimpleDateFormat;
  17. import java.util.ArrayList;
  18. import java.util.List;
  19. import java.util.Map;
  20.  
  21. /**
  22.  * Created by progrm_jarvis on 14.06.17.
  23.  */
  24. public 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. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement