Advertisement
Guest User

Untitled

a guest
Dec 2nd, 2017
139
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.76 KB | None | 0 0
  1. public class MySQLManager
  2. {
  3. private Connection connection = null;
  4.  
  5. private final String strHost;
  6. private final int intPort;
  7. private final String strDatabase;
  8. private final String strUsername;
  9. private final String strPassword;
  10.  
  11. private int retries = 0;
  12. private String lastError = "";
  13.  
  14. public MySQLManager(final String strHost, final int intPort, final String strDatabase, final String strUsername, final String strPassword)
  15. {
  16. this.strHost = strHost;
  17. this.intPort = intPort;
  18. this.strDatabase = strDatabase;
  19. this.strUsername = strUsername;
  20. this.strPassword = strPassword;
  21. getConnection();
  22. }
  23.  
  24. public Connection getConnection()
  25. {
  26. if (connection == null)
  27. {
  28. try
  29. {
  30. connection = DriverManager
  31. .getConnection("jdbc:mysql://" + strHost + ":" + intPort + "/" + strDatabase + "?user=" + strUsername + "&password=" + strPassword + "&autoreconnect=true");
  32. retries = 0;
  33. System.out.println("Successfully connected to the database.");
  34. }
  35. catch (final Exception e)
  36. {
  37. connection = null;
  38. System.err.println("Unable to connect to the " + strDatabase + " database with the user " + strUsername + " on " + strHost + " with port " + intPort + ": "
  39. + e.getMessage());
  40. System.err.println("Please check the host, port, database, username and password in the config.yml of the plugin and reload the plugin!");
  41. e.printStackTrace();
  42. }
  43. }
  44. return connection;
  45. }
  46.  
  47. public String escapeString(final String strSQL)
  48. {
  49. if (strSQL == null)
  50. {
  51. return null;
  52. }
  53. return strSQL.replace("'", "").replace("´", "").replace("`", "").replace(";", "").replace("#", "").replace("--", "")
  54. .replace("/*", "").replace("*/", "").replace("'", "\\'");
  55. }
  56.  
  57. public boolean update(final String strSQL)
  58. {
  59. Statement st = null;
  60. try
  61. {
  62. st = getConnection().createStatement();
  63. st.executeUpdate(strSQL);
  64. return true;
  65. }
  66. catch (final Exception e)
  67. {
  68. System.err.println("Unable to perform MySQL update '" + strSQL + "': " + e.getMessage());
  69. if (retries <= 3 && !lastError.equalsIgnoreCase(e.getMessage()))
  70. {
  71. System.err.println("Reconnecting...");
  72. connection = null;
  73. retries++;
  74. lastError = e.getMessage();
  75. getConnection();
  76. System.out.println("Retry...");
  77. update(strSQL);
  78. }
  79. }
  80. finally
  81. {
  82. try
  83. {
  84. st.close();
  85. }
  86. catch (final Exception e)
  87. {
  88. }
  89. }
  90. return false;
  91. }
  92.  
  93. public ResultSet query(final String strSQL)
  94. {
  95. ResultSet rs = null;
  96. try
  97. {
  98. final Statement st = getConnection().createStatement();
  99. rs = st.executeQuery(strSQL);
  100. }
  101. catch (final Exception e)
  102. {
  103. System.err.println("Unable to perform MySQL query '" + strSQL + "': " + e.getMessage());
  104. if (retries <= 3 && !lastError.equalsIgnoreCase(e.getMessage()))
  105. {
  106. System.err.println("Reconnecting...");
  107. connection = null;
  108. retries++;
  109. lastError = e.getMessage();
  110. getConnection();
  111. System.out.println("Retry...");
  112. return query(strSQL);
  113. }
  114. }
  115. return rs;
  116. }
  117.  
  118. public boolean hasStats(final String strTable, final String strType, String strKey, final String strValue)
  119. {
  120. strKey = escapeString(strKey);
  121. final String strSQL = "SELECT `" + strType + "` FROM `" + strTable + "` WHERE " + strKey + "='" + strValue + "'";
  122.  
  123. final ResultSet rstStats = query(strSQL);
  124. try
  125. {
  126. if (rstStats != null && rstStats.next())
  127. {
  128. return true;
  129. }
  130. }
  131. catch (final Exception e)
  132. {
  133. System.err.println("Unable to perform MySQL query '" + strSQL + "': " + e.getMessage());
  134. if (retries <= 3 && !lastError.equalsIgnoreCase(e.getMessage()))
  135. {
  136. System.err.println("Reconnecting...");
  137. connection = null;
  138. retries++;
  139. lastError = e.getMessage();
  140. getConnection();
  141. System.out.println("Retry...");
  142. return hasStats(strTable, strType, strKey, strValue);
  143. }
  144. }
  145. finally
  146. {
  147. try
  148. {
  149. rstStats.close();
  150. }
  151. catch (final SQLException e)
  152. {
  153. }
  154. }
  155. return false;
  156. }
  157.  
  158. public boolean hasStats(final String strTable, final String strType, String strKey1, String strValue1, String strKey2,
  159. String strValue2)
  160. {
  161. strKey1 = escapeString(strKey1);
  162. strKey2 = escapeString(strKey2);
  163. strValue1 = escapeString(strValue1);
  164. strValue2 = escapeString(strValue2);
  165. final String strSQL = "SELECT `" + strType + "` FROM `" + strTable + "` WHERE " + strKey1 + "='" + strValue1 + "' AND " + strKey2 + "='" + strValue2 + "'";
  166.  
  167. final ResultSet rstStats = query(strSQL);
  168. try
  169. {
  170. if (rstStats != null && rstStats.next())
  171. {
  172. return true;
  173. }
  174. }
  175. catch (final Exception e)
  176. {
  177. System.err.println("Unable to perform MySQL query '" + strSQL + "': " + e.getMessage());
  178. if (retries <= 3 && !lastError.equalsIgnoreCase(e.getMessage()))
  179. {
  180. System.err.println("Reconnecting...");
  181. connection = null;
  182. retries++;
  183. lastError = e.getMessage();
  184. getConnection();
  185. System.out.println("Retry...");
  186. return hasStats(strTable, strType, strKey1, strValue1, strKey2, strValue2);
  187. }
  188. }
  189. finally
  190. {
  191. try
  192. {
  193. rstStats.close();
  194. }
  195. catch (final SQLException e)
  196. {
  197. }
  198. }
  199. return false;
  200. }
  201.  
  202. public int getStats(final String strTable, final String strType, String strKey, String strValue)
  203. {
  204. strKey = escapeString(strKey);
  205. strValue = escapeString(strValue);
  206. final String strSQL = "SELECT `" + strType + "` FROM `" + strTable + "` WHERE " + strKey + "='" + strValue + "'";
  207.  
  208. int i = 0;
  209. final ResultSet rstStats = query(strSQL);
  210. try
  211. {
  212. if (rstStats.next())
  213. {
  214. i = rstStats.getInt(1);
  215. }
  216. rstStats.close();
  217. }
  218. catch (final Exception e)
  219. {
  220. System.err.println("Unable to perform MySQL query '" + strSQL + "': " + e.getMessage());
  221. e.printStackTrace();
  222. }
  223. finally
  224. {
  225. try
  226. {
  227. rstStats.close();
  228. }
  229. catch (final SQLException e)
  230. {
  231. }
  232. }
  233. return i;
  234. }
  235.  
  236. public ResultSet getMultiStats(final String strTable, final String strType, final String strKey, final String strValue)
  237. {
  238. return query("SELECT `" + strType + "` FROM `" + strTable + "` WHERE " + strKey + "='" + strValue + "'");
  239. }
  240.  
  241. public String getString(final String strTable, final String strType, String strKey, final String strValue)
  242. {
  243. strKey = escapeString(strKey);
  244. final String strSQL = "SELECT `" + strType + "` FROM `" + strTable + "` WHERE " + strKey + "='" + strValue + "'";
  245.  
  246. String i = "";
  247. final ResultSet rstStats = query(strSQL);
  248. try
  249. {
  250. if (rstStats.next())
  251. {
  252. i = rstStats.getString(1);
  253. }
  254. rstStats.close();
  255. }
  256. catch (final Exception e)
  257. {
  258. System.err.println("Unable to perform MySQL query '" + strSQL + "': " + e.getMessage());
  259. e.printStackTrace();
  260. }
  261. try
  262. {
  263. rstStats.close();
  264. }
  265. catch (final SQLException e)
  266. {
  267. }
  268. return i;
  269. }
  270.  
  271. public int getInt(final String strTable, final String strType, String strKey, final String strValue)
  272. {
  273. strKey = escapeString(strKey);
  274. final String strSQL = "SELECT `" + strType + "` FROM `" + strTable + "` WHERE " + strKey + "='" + strValue + "'";
  275.  
  276. int i = 0;
  277. final ResultSet rstStats = query(strSQL);
  278. try
  279. {
  280. if (rstStats.next())
  281. {
  282. i = rstStats.getInt(1);
  283. }
  284. rstStats.close();
  285. }
  286. catch (final Exception e)
  287. {
  288. System.err.println("Unable to perform MySQL query '" + strSQL + "': " + e.getMessage());
  289. e.printStackTrace();
  290. }
  291. try
  292. {
  293. rstStats.close();
  294. }
  295. catch (final SQLException e)
  296. {
  297. }
  298. return i;
  299. }
  300.  
  301. public void setStats(final String strTable, final String strType, String strKey, final String strValue, Object objStats)
  302. {
  303. strKey = escapeString(strKey);
  304. objStats = escapeString(objStats.toString());
  305.  
  306. update("UPDATE " + strTable + " SET " + strType + "='" + objStats + "' WHERE " + strKey + "='" + strValue + "'");
  307. }
  308.  
  309. public void addStats(final String strTable, final String strType, String strKey, String strValue, final int intAmount)
  310. {
  311. strKey = escapeString(strKey);
  312. strValue = escapeString(strValue);
  313.  
  314. int i = 0;
  315. final ResultSet rstStats = query("SELECT `" + strType + "` FROM `" + strTable + "` WHERE " + strKey + "='" + strValue + "'");
  316. try
  317. {
  318. if (rstStats.next())
  319. {
  320. i = rstStats.getInt(1);
  321. i += intAmount;
  322. update("UPDATE " + strTable + " SET " + strType + "='" + i + "' WHERE " + strKey + "='" + strValue + "'");
  323. }
  324. }
  325. catch (final Exception e)
  326. {
  327. System.err.println("Unable to add stats (" + strType + " " + intAmount + ") from table " + strTable + " where " + strKey + "=" + strValue);
  328. e.printStackTrace();
  329. }
  330. try
  331. {
  332. rstStats.close();
  333. }
  334. catch (final SQLException e)
  335. {
  336. }
  337. }
  338.  
  339. public void removeStats(final String strTable, final String strType, String strKey, final String strValue, final int intAmount)
  340. {
  341. strKey = escapeString(strKey);
  342.  
  343. int i = 0;
  344. final ResultSet rstStats = query("SELECT `" + strType + "` FROM `" + strTable + "` WHERE " + strKey + "='" + strValue + "'");
  345. try
  346. {
  347. if (rstStats.next())
  348. {
  349. i = rstStats.getInt(1);
  350. i -= intAmount;
  351. update("UPDATE " + strTable + " SET " + strType + "='" + i + "' WHERE " + strKey + "='" + strValue + "'");
  352. }
  353. }
  354. catch (final Exception e)
  355. {
  356. System.err.println("Unable to remove stats (" + strType + " " + intAmount + ") from table " + strTable + " where " + strKey + "=" + strValue);
  357. e.printStackTrace();
  358. }
  359. try
  360. {
  361. rstStats.close();
  362. }
  363. catch (final SQLException e)
  364. {
  365. }
  366. }
  367.  
  368. public ResultSet getResultSet(final String strTable, final String strColumn)
  369. {
  370. return query("SELECT " + strColumn + " FROM " + strTable);
  371. }
  372.  
  373. public void destroy()
  374. {
  375. try
  376. {
  377. if (connection != null && !connection.isClosed())
  378. {
  379. connection.close();
  380. }
  381. }
  382. catch (final Exception e)
  383. {
  384. }
  385. }
  386. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement