API tools faq
paste
Guest User

Untitled

a guest
Apr 7th, 2018
85
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 23.93 KB | None | 0 0
raw download clone embed print report
  1. package server.dao.database;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  8. import java.util.ArrayList;
  9. import java.util.Date;
  10. import java.util.HashMap;
  11. import java.util.HashSet;
  12. import java.util.logging.Level;
  13. import org.apache.mina.common.IoSession;
  14. import server.BanReason;
  15. import server.Config;
  16. import server.XLogger;
  17. import server.ecoscanner.PlayerWealth;
  18. import server.model.items.ItemList;
  19. import server.model.players.Player;
  20. import server.model.players.PlayerHandler;
  21. import server.model.players.PlayerRights;
  22. import server.util.MyHashSet;
  23. import server.world.Clan;
  24. import server.world.GrandExchangeOffer;
  25. import server.world.Lottery;
  26. import server.world.ShopHandler;
  27.  
  28. public final class Database {
  29.  
  30. private Database() {}
  31.  
  32. private static final ConnectionPoolManager cpm;
  33. private static final HashMap<String, PlayerWrapper> savingPlayers = new HashMap<String, PlayerWrapper>();
  34.  
  35. static {
  36. cpm = new ConnectionPoolManager("jdbc:mysql://pkhdatabase/pkhonor",
  37. "pkhonor", "pkhonorsqlp4ss");
  38. }
  39.  
  40. public static void addSavePlayer(String name, PlayerWrapper pw)
  41. {
  42. savingPlayers.put(name, pw);
  43. }
  44.  
  45. public static void removeSavePlayer(String name)
  46. {
  47. savingPlayers.remove(name);
  48. }
  49.  
  50. public static void logCommand(long millis, int playerid, String command, String arguments) {
  51. Connection conn = cpm.getConnectionFromPool();
  52. PreparedStatement ps = null;
  53. try {
  54. ps = conn.prepareStatement("INSERT INTO log_commands (timestamp, playerid, command, arguments) VALUES (?,?,?,?)");
  55. ps.setString(1, millisToTimestamp(millis));
  56. ps.setInt(2, playerid);
  57. ps.setString(3, command);
  58. ps.setString(4, arguments);
  59. ps.executeUpdate();
  60. } catch (SQLException ex) {
  61. XLogger.getInstance().log(Level.SEVERE, "Exception", ex);
  62. } finally {
  63. try {
  64. if (ps != null) {
  65. ps.close();
  66. conn.commit();
  67. }
  68. } catch (Exception e) {
  69. } finally {
  70. cpm.returnConnectionToPool(conn);
  71. }
  72. }
  73. }
  74.  
  75. public static void logConnection(long millis, int playerid, String ip) {
  76. Connection conn = cpm.getConnectionFromPool();
  77. PreparedStatement ps = null;
  78. try {
  79. ps = conn.prepareStatement("INSERT INTO log_connections (timestamp, playerid, ip) VALUES (?,?,INET_ATON(?))");
  80. ps.setString(1, millisToTimestamp(millis));
  81. ps.setInt(2, playerid);
  82. ps.setString(3, ip);
  83. ps.executeUpdate();
  84. } catch (SQLException ex) {
  85. XLogger.getInstance().log(Level.SEVERE, "Exception", ex);
  86. } finally {
  87. try {
  88. if (ps != null) {
  89. ps.close();
  90. conn.commit();
  91. }
  92. } catch (Exception e) {
  93. } finally {
  94. cpm.returnConnectionToPool(conn);
  95. }
  96. }
  97. }
  98.  
  99. public static void logDrop(long droptime, int dropplayerid, long pickuptime, int pickupplayerid, int itemid, int amount) {
  100. Connection conn = cpm.getConnectionFromPool();
  101. PreparedStatement ps = null;
  102. try {
  103. ps = conn.prepareStatement("INSERT INTO log_drops (droptime, from_playerid, pickuptime, to_playerid, itemid, amount) VALUES (?,?,?,?,?,?)");
  104. ps.setString(1, millisToTimestamp(droptime));
  105. ps.setInt(2, dropplayerid);
  106. ps.setString(3, millisToTimestamp(pickuptime));
  107. ps.setInt(4, pickupplayerid);
  108. ps.setInt(5, itemid);
  109. ps.setInt(6, amount);
  110. ps.executeUpdate();
  111. } catch (SQLException ex) {
  112. XLogger.getInstance().log(Level.SEVERE, "Exception", ex);
  113. } finally {
  114. try {
  115. if (ps != null) {
  116. ps.close();
  117. conn.commit();
  118. }
  119. } catch (Exception e) {
  120. } finally {
  121. cpm.returnConnectionToPool(conn);
  122. }
  123. }
  124. }
  125.  
  126. public static void logTrade(long millis, int from_playerid, int to_playerid, int itemid[], int amount[]) {
  127. if(itemid.length != amount.length || itemid.length < 1)
  128. return;
  129. Connection conn = cpm.getConnectionFromPool();
  130. PreparedStatement ps = null;
  131. try {
  132. ps = conn.prepareStatement("INSERT INTO log_trades (timestamp, from_playerid, to_playerid, itemid, amount) VALUES (?,?,?,?,?)");
  133. ps.setString(1, millisToTimestamp(millis));
  134. ps.setInt(2, from_playerid);
  135. ps.setInt(3, to_playerid);
  136. for (int i = 0; i < itemid.length; i++) {
  137. if(itemid[i] > 0)
  138. {
  139. ps.setInt(4, itemid[i]);
  140. ps.setInt(5, amount[i]);
  141. ps.addBatch();
  142. }
  143. }
  144. ps.executeBatch();
  145. } catch (SQLException ex) {
  146. XLogger.getInstance().log(Level.SEVERE, "Exception", ex);
  147. } finally {
  148. try {
  149. if (ps != null) {
  150. ps.close();
  151. conn.commit();
  152. }
  153. } catch (Exception e) {
  154. } finally {
  155. cpm.returnConnectionToPool(conn);
  156. }
  157. }
  158. }
  159.  
  160. public static void logStake(long millis, int from_playerid, int to_playerid, int[] itemid, int[] amount) {
  161. if(itemid.length != amount.length || itemid.length < 1)
  162. return;
  163. Connection conn = cpm.getConnectionFromPool();
  164. PreparedStatement ps = null;
  165. try {
  166. ps = conn.prepareStatement("INSERT INTO log_stakes_unfinished (timestamp, from_playerid, to_playerid, itemid, amount) VALUES (?,?,?,?,?)");
  167. ps.setString(1, millisToTimestamp(millis));
  168. ps.setInt(2, from_playerid);
  169. ps.setInt(3, to_playerid);
  170. for (int i = 0; i < itemid.length; i++) {
  171. if(itemid[i] > 0)
  172. {
  173. ps.setInt(4, itemid[i]);
  174. ps.setInt(5, amount[i]);
  175. ps.addBatch();
  176. }
  177. }
  178. ps.executeBatch();
  179. } catch (SQLException ex) {
  180. XLogger.getInstance().log(Level.SEVERE, "Exception", ex);
  181. } finally {
  182. try {
  183. if (ps != null) {
  184. ps.close();
  185. conn.commit();
  186. }
  187. } catch (Exception e) {
  188. } finally {
  189. cpm.returnConnectionToPool(conn);
  190. }
  191. }
  192. }
  193.  
  194. public static void logStakeDone(long stakeid, int winnerid) {
  195. Connection conn = cpm.getConnectionFromPool();
  196. Statement s = null;
  197. try {
  198. s = conn.createStatement();
  199. s.executeUpdate("INSERT INTO log_stakes (timestamp, from_playerid, to_playerid, itemid, amount, winnerid) SELECT timestamp, from_playerid, to_playerid, itemid, amount, "+winnerid+" AS winnerid FROM log_stakes_unfinished WHERE timestamp="+millisToTimestamp(stakeid));
  200. s.executeUpdate("DELETE FROM log_stakes_unfinished WHERE timestamp="+millisToTimestamp(stakeid));
  201. } catch (SQLException ex) {
  202. XLogger.getInstance().log(Level.SEVERE, "Exception", ex);
  203. } finally {
  204. try {
  205. if (s != null) {
  206. s.close();
  207. conn.commit();
  208. }
  209. } catch (Exception e) {
  210. } finally {
  211. cpm.returnConnectionToPool(conn);
  212. }
  213. }
  214. }
  215.  
  216. public static void logMBox(long millis, int playerid, int itemid, int amount) {
  217. Connection conn = cpm.getConnectionFromPool();
  218. PreparedStatement ps = null;
  219. try {
  220. ps = conn.prepareStatement("INSERT INTO log_mboxes (timestamp, playerid, itemid, amount) VALUES (?,?,?,?)");
  221. ps.setString(1, millisToTimestamp(millis));
  222. ps.setInt(2, playerid);
  223. ps.setInt(3, itemid);
  224. ps.setInt(4, amount);
  225. ps.executeUpdate();
  226. } catch (SQLException ex) {
  227. XLogger.getInstance().log(Level.SEVERE, "Exception", ex);
  228. } finally {
  229. try {
  230. if (ps != null) {
  231. ps.close();
  232. conn.commit();
  233. }
  234. } catch (Exception e) {
  235. } finally {
  236. cpm.returnConnectionToPool(conn);
  237. }
  238. }
  239. }
  240.  
  241. public static void logExchange(long millis, int from_playerid, int to_playerid, int itemid, long amount) {
  242. Connection conn = cpm.getConnectionFromPool();
  243. PreparedStatement ps = null;
  244. try {
  245. ps = conn.prepareStatement("INSERT INTO log_grand_exchange (timestamp, from_playerid, to_playerid, itemid, amount) VALUES (?,?,?,?,?)");
  246. ps.setString(1, millisToTimestamp(millis));
  247. ps.setInt(2, from_playerid);
  248. ps.setInt(3, to_playerid);
  249. ps.setInt(4, itemid);
  250. ps.setLong(5, amount);
  251. ps.executeUpdate();
  252. } catch (SQLException ex) {
  253. XLogger.getInstance().log(Level.SEVERE, "Exception", ex);
  254. } finally {
  255. try {
  256. if (ps != null) {
  257. ps.close();
  258. conn.commit();
  259. }
  260. } catch (Exception e) {
  261. } finally {
  262. cpm.returnConnectionToPool(conn);
  263. }
  264. }
  265. }
  266.  
  267. public static HashMap<Integer, PlayerWealth> getPlayerWealth() {
  268. Connection conn = cpm.getConnectionFromPool();
  269. HashMap<Integer, PlayerWealth> players = new HashMap<Integer, PlayerWealth>();
  270. Statement s = null;
  271. try {
  272. s = conn.createStatement();
  273. ResultSet res = s.executeQuery("SELECT playerid, timestamp FROM log_connections ORDER BY timestamp");
  274. HashMap<Integer, Long> connections = new HashMap<Integer, Long>();
  275. while(res.next())
  276. connections.put(res.getInt(1), timestampToMillis(res.getString(2)));
  277. System.out.println("Done loading all connections");
  278. res = s.executeQuery("SELECT username, playerid, rights, banned, rights FROM players");
  279. int index = 0;
  280. while(res.next())
  281. {
  282. if(!res.getBoolean(4))
  283. {
  284. int rights = res.getInt(3);
  285. String name = res.getString(1);
  286. if(rights != PlayerRights.HONOR && rights < PlayerRights.ADMIN
  287. && !PlayerRights.superUsers.contains(name))
  288. {
  289. index++;
  290. int playerId = res.getInt(2);
  291. players.put(playerId, new PlayerWealth(name, playerId, connections.containsKey(playerId) ? connections.get(playerId) : 0));
  292. }
  293. }
  294. }
  295. System.out.println("Done loading players");
  296. res = s.executeQuery("SELECT playerid, itemid, amount FROM player_banks");
  297. while(res.next())
  298. {
  299. int playerId = res.getInt(1);
  300. if(players.containsKey(playerId))
  301. players.get(playerId).addItem(res.getInt(2), res.getInt(3));
  302. }
  303. System.out.println("Done loading banks");
  304. res = s.executeQuery("SELECT playerid, e0, n0, e1, n1, e2, n2, e3, n3, e4, n4, e5, n5, e6, n6, e7, n7, e8, n8, e9, n9, e10, n10, e11, n11, e12, n12, e13, n13 FROM player_equipment");
  305. while(res.next())
  306. {
  307. int playerId = res.getInt(1);
  308. if(players.containsKey(playerId))
  309. {
  310. PlayerWealth pw = players.get(playerId);
  311. for(int i = 0; i < 14; i++)
  312. pw.addItem(res.getInt(2 + i*2), res.getInt(3 + i*2));
  313. }
  314. }
  315. System.out.println("Done loading equipment");
  316. res = s.executeQuery("SELECT playerid, i0, a0, i1, a1, i2, a2, i3, a3, i4, a4, i5, a5, i6, a6, i7, a7, i8, a8, i9, a9, i10, a10, i11, a11, i12, a12, i13, a13, i14, a14, i15, a15, i16, a16, i17, a17, i18, a18, i19, a19, i20, a20, i21, a21, i22, a22, i23, a23, i24, a24, i25, a25, i26, a26, i27, a27 FROM player_inventories");
  317. while(res.next())
  318. {
  319. int playerId = res.getInt(1);
  320. if(players.containsKey(playerId))
  321. {
  322. PlayerWealth pw = players.get(playerId);
  323. for(int i = 0; i < 28; i++)
  324. pw.addItem(res.getInt(2 + i*2), res.getInt(3 + i*2));
  325. }
  326. }
  327. System.out.println("Done loading inventories");
  328. res = s.executeQuery("SELECT playerid, itemid, amount, amountdone, priceeach, coins, items, sell FROM grand_exchange");
  329. while(res.next())
  330. {
  331. int playerId = res.getInt(1);
  332. if(players.containsKey(playerId))
  333. {
  334. PlayerWealth pw = players.get(playerId);
  335. int itemId = res.getInt(2);
  336. int amount = res.getInt(3);
  337. int amountDone = res.getInt(4);
  338. long priceEach = res.getLong(5);
  339. long coins = res.getLong(6);
  340. int items = res.getInt(7);
  341. boolean sell = res.getBoolean(8);
  342. if(sell)
  343. {
  344. if(items > 0)
  345. pw.addItem(itemId, items);
  346. if(coins >= 1000000000)
  347. pw.addItem(5020, (int)(coins/new Long(1000000000)));
  348. if(coins > 0)
  349. pw.addItem(995, (int)(coins % new Long(1000000000)));
  350. }
  351. else
  352. {
  353. coins += new Long(amount - amountDone)*priceEach;
  354. if(items > 0)
  355. pw.addItem(itemId, items);
  356. if(coins >= 1000000000)
  357. pw.addItem(5020, (int)(coins/new Long(1000000000)));
  358. if(coins > 0)
  359. pw.addItem(995, (int)(coins % new Long(1000000000)));
  360. }
  361. }
  362. }
  363. System.out.println("Done loading grand exchange");
  364. } catch (Exception ex) {
  365. ex.printStackTrace();
  366. } finally {
  367. try {
  368. if (s != null) {
  369. s.close();
  370. conn.commit();
  371. }
  372. } catch (Exception e) {
  373. } finally {
  374. cpm.returnConnectionToPool(conn);
  375. }
  376. }
  377. return players;
  378. }
  379.  
  380. public static Player getPlayer(String name, IoSession session) {
  381. Connection conn = cpm.getConnectionFromPool();
  382. PreparedStatement ps = null;
  383. Player p = null;
  384. try
  385. {
  386. if(savingPlayers.containsKey(name))
  387. {
  388. PlayerWrapper pw = savingPlayers.get(name);
  389. p = new Player(name);
  390. p.initialize(session);
  391. p.addCreditsToRemove(pw.creditsToRemove);
  392. p.playerLevel = pw.playerLevel.clone();
  393. p.playerXP = pw.playerXP.clone();
  394. p.playerId = pw.playerId;
  395. p.playerIndex = pw.playerIndex;
  396. p.playerName = pw.playerName;
  397. p.playerPass = pw.playerPass;
  398. p.heightLevel = pw.heightLevel;
  399. p.teleportToX = pw.absX;
  400. p.teleportToY = pw.absY;
  401. p.playerRights = pw.playerRights;
  402. p.bankPin = pw.bankPin.clone();
  403. p.lotteryPrize = pw.lotteryPrize;
  404. p.clan = pw.clan;
  405. p.setDonated(pw.donated);
  406. p.autoRet = pw.autoRet;
  407. p.fightMode = pw.fightMode;
  408. p.pkHonorPoints = pw.pkHonorPoints;
  409. p.splitChat = pw.splitChat;
  410. p.privateChat = pw.privateChat;
  411. p.specAmount = pw.specAmount;
  412. p.isRunningToggledOn = pw.isRunningToggledOn;
  413. p.energy = pw.energy;
  414. p.timePlayed = pw.timePlayed;
  415. p.kills = pw.kills;
  416. p.deaths = pw.deaths;
  417. p.dropPartyBalloons = pw.dropPartyBalloons;
  418. p.lastBalloonPurchase = pw.lastBalloonPurchase;
  419. p.crystalBowArrowCount = pw.crystalBowArrowCount;
  420. p.crystalShieldLeft = pw.crystalShieldLeft;
  421. p.skullTimer = pw.skullTimer;
  422. p.playerMagicBook = pw.playerMagicBook;
  423. p.prayerBookTime = pw.prayerBookTime;
  424. p.QH().pestInvasion = pw.pestInvasion;
  425. p.randomCoffin = pw.randomCoffin;
  426. p.barrowsNpcs[1] = pw.barrowsNpcs.clone();
  427. p.dfsCharges = pw.dfsCharges;
  428. p.tzWave = pw.tzWave;
  429. p.clanChannel = pw.clanChannel;
  430. p.lootSharePoints = pw.lootSharePoints;
  431. p.poisonDamage = pw.poisonDamage;
  432. p.autoHeal = pw.autoHeal;
  433. p.cannonStage = pw.cannonStage;
  434. p.cannonBalls = pw.cannonBalls;
  435. p.teleBlockLength = pw.teleBlockLength;
  436. p.teleBlockDelay = System.currentTimeMillis();
  437. p.pcPoints = pw.pcPoints;
  438. p.slayerTask = pw.slayerTask;
  439. p.taskAmount = pw.taskAmount;
  440. p.starter = pw.starter;
  441. p.pureOne = pw.pureOne;
  442. p.expLock = pw.expLock;
  443. p.maxDef = pw.maxDef;
  444. p.maxAtt = pw.maxAtt;
  445. p.armaKills = pw.armaKills;
  446. p.bandosKills = pw.bandosKills;
  447. p.saraKills = pw.saraKills;
  448. p.zammyKills = pw.zammyKills;
  449. p.lastGodwarsPray = pw.lastGodwarsPray;
  450. p.jailOreLeft = pw.jailOreLeft;
  451. p.jailType = pw.jailType;
  452. p.banned = pw.banned;
  453. p.banDuel = pw.banDuel;
  454. p.muteTimer = pw.muteTimer;
  455. p.yellMuteTimer = pw.yellMuteTimer;
  456. p.canTradePks = pw.canTradePks;
  457. p.wildyWyrm = pw.wildyWyrm;
  458. p.degrades = pw.degrades.clone();
  459. p.ACH().wiseOldMan = pw.wiseOldMan.clone();
  460. p.farmingBush = pw.farmingBush.clone();
  461. p.farmingCactus = pw.farmingCactus;
  462. p.farmingCompost = pw.farmingCompost.clone();
  463. p.farmingHerb = pw.farmingHerb.clone();
  464. p.farmingStages = pw.farmingStages.clone();
  465. p.farmingTime = pw.farmingTime.clone();
  466. p.farmingTimes = pw.farmingTimes.clone();
  467. p.farmingTypes = pw.farmingTypes.clone();
  468. p.playerAppearance = pw.playerAppearance;
  469. p.playerConnectCheck = pw.connectedFrom;
  470.  
  471. p.playerItems = pw.playerItems.clone();
  472. p.playerItemsN = pw.playerItemsN.clone();
  473. p.playerEquipment = pw.playerEquipment.clone();
  474. p.playerEquipmentN = pw.playerEquipmentN.clone();
  475. p.bankItems = pw.bankItems.clone();
  476. p.bankItemsN = pw.bankItemsN.clone();
  477.  
  478. p.friends = pw.friends;
  479. p.friendRanks = pw.friendRanks;
  480. p.ignores = pw.ignores;
  481.  
  482. p.geOffers = pw.geOffers.clone();
  483. pw.loaded = true;
  484. }
  485. else
  486. {
  487. ps = conn.prepareStatement("CALL GetPlayerInfo(?);");
  488. ps.setString(1, name);
  489. ResultSet res = ps.executeQuery();
  490. if(res.next()) {
  491. p = new Player(name);
  492. p.initialize(session);
  493. p.playerId = res.getInt(1);
  494. //p.username = res.getString(2); //No need, already set
  495. p.playerPass = res.getString(3);
  496. p.heightLevel = res.getInt(4);
  497. p.teleportToX = res.getInt(5);
  498. p.teleportToY = res.getInt(6);
  499. p.playerRights = res.getInt(7);
  500. p.bankPin = res.getString(8).equals("----") ? new int[]{-1,-1,-1,-1} : new int[]{Integer.parseInt(""+res.getString(8).charAt(0)),Integer.parseInt(""+res.getString(8).charAt(1)),Integer.parseInt(""+res.getString(8).charAt(2)),Integer.parseInt(""+res.getString(8).charAt(3))};
  501. p.setDonated(res.getInt(9));
  502. // p.credits = res.getInt(10); Fetched at runtime when needed
  503. p.fightMode = res.getInt(11);
  504. if(p.fightMode > 9) {
  505. p.fightMode -= 10;
  506. p.autoRet = true;
  507. }
  508. p.pkHonorPoints = res.getInt(12);
  509. p.privateChat = res.getInt(13);
  510. if(p.privateChat > 9) {
  511. p.privateChat -= 10;
  512. p.splitChat = true;
  513. }
  514. p.specAmount = res.getInt(14);
  515. p.energy = res.getInt(15);
  516. if(p.energy < 0) {
  517. p.energy = -p.energy;
  518. p.isRunningToggledOn = false;
  519. }
  520. p.timePlayed = res.getInt(16);
  521. p.kills = res.getInt(17);
  522. p.deaths = res.getInt(18);
  523. p.dropPartyBalloons = res.getInt(19);
  524. p.lastBalloonPurchase = res.getLong(20);
  525. p.crystalBowArrowCount = (int)res.getInt(21)/1000;
  526. p.crystalShieldLeft = res.getInt(21)%1000;
  527. p.skullTimer = res.getInt(22);
  528. p.playerMagicBook = res.getInt(23);
  529. p.prayerBookTime = res.getInt(24);
  530. p.QH().pestInvasion = res.getInt(25);
  531. int barrows = res.getInt(26);
  532. p.randomCoffin = (int)barrows/1000000;
  533. for (int i = 0; i < 6; i++)
  534. p.barrowsNpcs[i][1] = ((int)barrows/(int)Math.pow(10,i))%10;
  535. p.dfsCharges = res.getInt(27);
  536. p.tzWave = res.getInt(28);
  537. p.clanChannel = res.getInt(29);
  538. p.lootSharePoints = res.getInt(30);
  539. p.poisonDamage = res.getInt(31);
  540. p.autoHeal = res.getBoolean(32);
  541. p.cannonStage = res.getInt(33);
  542. if(p.cannonStage > 4) {
  543. p.cannonBalls = p.cannonStage - 4;
  544. p.cannonStage = 4;
  545. }
  546. p.teleBlockLength = res.getInt(34);
  547. p.teleBlockDelay = System.currentTimeMillis();
  548. p.pcPoints = res.getInt(35);
  549. p.slayerTask = (int)res.getInt(36)/1000;
  550. p.taskAmount = res.getInt(36)%1000;
  551. p.starter = res.getInt(37);
  552. p.pureOne = res.getInt(38);
  553. p.expLock = res.getBoolean(39);
  554. p.maxAtt = res.getInt(40);
  555. p.maxDef = res.getInt(41);
  556. String[] ar = res.getString(42).split(";");
  557. p.armaKills = Integer.parseInt(ar[0]);
  558. p.bandosKills = Integer.parseInt(
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!