API tools faq
paste
Advertisement
Guest User

PlayerDAO

a guest
Nov 20th, 2014
145
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 28.70 KB | None | 0 0
raw download clone embed print report
  1. package mysql5;
  2.  
  3. import com.aionlightning.commons.database.DB;
  4. import com.aionlightning.commons.database.DatabaseFactory;
  5. import com.aionlightning.commons.database.IUStH;
  6. import com.aionlightning.commons.database.ParamReadStH;
  7. import com.aionlightning.commons.utils.GenericValidator;
  8. import com.aionlightning.gameserver.configs.main.CacheConfig;
  9. import com.aionlightning.gameserver.configs.main.GSConfig;
  10. import com.aionlightning.gameserver.dao.MySQL5DAOUtils;
  11. import com.aionlightning.gameserver.dao.PlayerDAO;
  12. import com.aionlightning.gameserver.dataholders.DataManager;
  13. import com.aionlightning.gameserver.dataholders.PlayerInitialData;
  14. import com.aionlightning.gameserver.dataholders.PlayerInitialData.LocationData;
  15. import com.aionlightning.gameserver.model.Gender;
  16. import com.aionlightning.gameserver.model.PlayerClass;
  17. import com.aionlightning.gameserver.model.Race;
  18. import com.aionlightning.gameserver.model.account.PlayerAccountData;
  19. import com.aionlightning.gameserver.model.gameobjects.player.Mailbox;
  20. import com.aionlightning.gameserver.model.gameobjects.player.Player;
  21. import com.aionlightning.gameserver.model.gameobjects.player.PlayerCommonData;
  22. import com.aionlightning.gameserver.world.MapRegion;
  23. import com.aionlightning.gameserver.world.World;
  24. import com.aionlightning.gameserver.world.WorldPosition;
  25. import com.google.common.collect.Maps;
  26. import javolution.util.FastMap;
  27. import org.apache.commons.lang.StringUtils;
  28. import org.slf4j.Logger;
  29. import org.slf4j.LoggerFactory;
  30.  
  31. import java.sql.*;
  32. import java.util.*;
  33. import java.util.Map.Entry;
  34.  
  35. /**
  36. * @author SoulKeeper, Saelya
  37. * @author cura
  38. */
  39. public class MySQL5PlayerDAO extends PlayerDAO {
  40.  
  41. private static final Logger log = LoggerFactory.getLogger(MySQL5PlayerDAO.class);
  42. private FastMap<Integer, PlayerCommonData> playerCommonData = new FastMap<Integer, PlayerCommonData>().shared();
  43. private FastMap<String, PlayerCommonData> playerCommonDataByName = new FastMap<String, PlayerCommonData>().shared();
  44.  
  45. /**
  46. * {@inheritDoc}
  47. */
  48. @Override
  49. public boolean isNameUsed(final String name) {
  50. PreparedStatement s = DB.prepareStatement("SELECT count(id) as cnt FROM players WHERE ? = players.name");
  51. try {
  52. s.setString(1, name);
  53. ResultSet rs = s.executeQuery();
  54. rs.next();
  55. return rs.getInt("cnt") > 0;
  56. } catch (SQLException e) {
  57. log.error("Can't check if name " + name + ", is used, returning possitive result", e);
  58. return true;
  59. } finally {
  60. DB.close(s);
  61. }
  62. }
  63.  
  64. @Override
  65. public Map<Integer, String> getPlayerNames(Collection<Integer> playerObjectIds) {
  66.  
  67. if (GenericValidator.isBlankOrNull(playerObjectIds)) {
  68. return Collections.emptyMap();
  69. }
  70.  
  71. Map<Integer, String> result = Maps.newHashMap();
  72.  
  73. String sql = "SELECT id, `name` FROM players WHERE id IN(%s)";
  74. sql = String.format(sql, StringUtils.join(playerObjectIds, ", "));
  75. PreparedStatement s = DB.prepareStatement(sql);
  76. try {
  77. ResultSet rs = s.executeQuery();
  78. while (rs.next()) {
  79. int id = rs.getInt("id");
  80. String name = rs.getString("name");
  81. result.put(id, name);
  82. }
  83. } catch (SQLException e) {
  84. throw new RuntimeException("Failed to load player names", e);
  85. } finally {
  86. DB.close(s);
  87. }
  88.  
  89. return result;
  90. }
  91.  
  92. /**
  93. * {@inheritDoc}
  94. */
  95. @Override
  96. public void changePlayerId(final Player player, final int accountId) {
  97. Connection con = null;
  98. try {
  99. con = DatabaseFactory.getConnection();
  100. PreparedStatement stmt = con
  101. .prepareStatement("UPDATE players SET account_id=? WHERE id=?");
  102. stmt.setInt(1, accountId);
  103. stmt.setInt(2, player.getObjectId());
  104. stmt.execute();
  105. stmt.close();
  106. } catch (Exception e) {
  107. log.error("Error saving player: " + player.getObjectId() + " " + player.getName(), e);
  108. } finally {
  109. DatabaseFactory.close(con);
  110. }
  111. }
  112.  
  113. /**
  114. * {@inheritDoc}
  115. */
  116. @Override
  117. public void storePlayer(final Player player) {
  118. Connection con = null;
  119. try {
  120. con = DatabaseFactory.getConnection();
  121. PreparedStatement stmt = con
  122. .prepareStatement("UPDATE players SET name=?, exp=?, recoverexp=?, x=?, y=?, z=?, heading=?, world_id=?, gender=?, race=?, player_class=?, last_online=?, quest_expands=?, npc_expands=?, advanced_stigma_slot_size=?, warehouse_size=?, note=?, title_id=?, bonus_title_id=?, dp=?, soul_sickness=?, mailbox_letters=?, reposte_energy=?, bg_points=?, mentor_flag_time=?, initial_gamestats=?, world_owner=? WHERE id=?");
  123.  
  124. log.debug("[DAO: MySQL5PlayerDAO] storing player " + player.getObjectId() + " " + player.getName());
  125. PlayerCommonData pcd = player.getCommonData();
  126. stmt.setString(1, player.getName());
  127. stmt.setLong(2, pcd.getExp());
  128. stmt.setLong(3, pcd.getExpRecoverable());
  129. stmt.setFloat(4, player.getX());
  130. stmt.setFloat(5, player.getY());
  131. stmt.setFloat(6, player.getZ());
  132. stmt.setInt(7, player.getHeading());
  133. stmt.setInt(8, player.getWorldId());
  134. stmt.setString(9, player.getGender().toString());
  135. stmt.setString(10, player.getRace().toString());
  136. stmt.setString(11, pcd.getPlayerClass().toString());
  137. stmt.setTimestamp(12, pcd.getLastOnline());
  138. stmt.setInt(13, player.getQuestExpands());
  139. stmt.setInt(14, player.getNpcExpands());
  140. stmt.setInt(15, pcd.getAdvencedStigmaSlotSize());
  141. stmt.setInt(16, player.getWarehouseSize());
  142. stmt.setString(17, pcd.getNote());
  143. stmt.setInt(18, pcd.getTitleId());
  144. stmt.setInt(19, pcd.getBonusTitleId());
  145. stmt.setInt(20, pcd.getDp());
  146. stmt.setInt(21, pcd.getDeathCount());
  147. Mailbox mailBox = player.getMailbox();
  148. int mails = mailBox != null ? mailBox.size() : pcd.getMailboxLetters();
  149. stmt.setInt(22, mails);
  150. stmt.setLong(23, pcd.getCurrentReposteEnergy());
  151. stmt.setInt(24, player.getCommonData().getBattleGroundPoints());
  152. stmt.setInt(25, pcd.getMentorFlagTime());
  153. if (player.getPosition().getWorldMapInstance() == null) { //FIXME!
  154. log.error("Error saving player: " + player.getObjectId() + " " + player.getName() + ", world map instance is null. Setting world owner to 0. Position: " + player.getWorldId() + " " + player.getX() + " " + player.getY() + " " + player.getZ());
  155. stmt.setInt(27, 0);
  156. } else {
  157. stmt.setInt(27, player.getPosition().getWorldMapInstance().getOwnerId());
  158. }
  159. stmt.setInt(28, player.getObjectId());
  160. stmt.execute();
  161. stmt.close();
  162. } catch (Exception e) {
  163. log.error("Error saving player: " + player.getObjectId() + " " + player.getName(), e);
  164. } finally {
  165. DatabaseFactory.close(con);
  166. }
  167. if (CacheConfig.CACHE_COMMONDATA) {
  168. PlayerCommonData cached = playerCommonData.get(player.getObjectId());
  169. if (cached != null) {
  170. playerCommonData.putEntry(player.getCommonData().getPlayerObjId(), player.getCommonData());
  171. playerCommonDataByName.putEntry(player.getName().toLowerCase(), player.getCommonData());
  172. }
  173. }
  174. }
  175.  
  176. /**
  177. * {@inheritDoc}
  178. */
  179. @Override
  180. public boolean saveNewPlayer(final PlayerCommonData pcd, final int accountId, final String accountName) {
  181. Connection con = null;
  182. try {
  183. con = DatabaseFactory.getConnection();
  184. PreparedStatement preparedStatement = con
  185. .prepareStatement("INSERT INTO players(id, `name`, account_id, account_name, x, y, z, heading, world_id, gender, race, player_class , quest_expands, npc_expands, warehouse_size, online) "
  186. + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)");
  187.  
  188. log.debug("[DAO: MySQL5PlayerDAO] saving new player: " + pcd.getPlayerObjId() + " " + pcd.getName());
  189.  
  190. preparedStatement.setInt(1, pcd.getPlayerObjId());
  191. preparedStatement.setString(2, pcd.getName());
  192. preparedStatement.setInt(3, accountId);
  193. preparedStatement.setString(4, accountName);
  194. preparedStatement.setFloat(5, pcd.getPosition().getX());
  195. preparedStatement.setFloat(6, pcd.getPosition().getY());
  196. preparedStatement.setFloat(7, pcd.getPosition().getZ());
  197. preparedStatement.setInt(8, pcd.getPosition().getHeading());
  198. preparedStatement.setInt(9, pcd.getPosition().getMapId());
  199. preparedStatement.setString(10, pcd.getGender().toString());
  200. preparedStatement.setString(11, pcd.getRace().toString());
  201. preparedStatement.setString(12, pcd.getPlayerClass().toString());
  202. preparedStatement.setInt(13, pcd.getQuestExpands());
  203. preparedStatement.setInt(14, pcd.getNpcExpands());
  204. preparedStatement.setInt(15, pcd.getWarehouseSize());
  205. preparedStatement.execute();
  206. preparedStatement.close();
  207. } catch (Exception e) {
  208. log.error("Error saving new player: " + pcd.getPlayerObjId() + " " + pcd.getName(), e);
  209. return false;
  210. } finally {
  211. DatabaseFactory.close(con);
  212. }
  213. if (CacheConfig.CACHE_COMMONDATA) {
  214. playerCommonData.put(pcd.getPlayerObjId(), pcd);
  215. playerCommonDataByName.put(pcd.getName().toLowerCase(), pcd);
  216. }
  217. return true;
  218. }
  219.  
  220. @Override
  221. public PlayerCommonData loadPlayerCommonDataByName(final String name) {
  222. Player player = World.getInstance().findPlayer(name);
  223. if (player != null) {
  224. return player.getCommonData();
  225. }
  226. PlayerCommonData pcd = playerCommonDataByName.get(name.toLowerCase());
  227. if (pcd != null) {
  228. return pcd;
  229. }
  230. int playerObjId = 0;
  231.  
  232. Connection con = null;
  233. try {
  234. con = DatabaseFactory.getConnection();
  235. PreparedStatement stmt = con.prepareStatement("SELECT id FROM players WHERE name = ?");
  236. stmt.setString(1, name);
  237. ResultSet rset = stmt.executeQuery();
  238. if (rset.next()) {
  239. playerObjId = rset.getInt("id");
  240. }
  241. rset.close();
  242. stmt.close();
  243. } catch (Exception e) {
  244. log.error("Could not restore playerId data for player name: " + name + " from DB: " + e.getMessage(), e);
  245. } finally {
  246. DatabaseFactory.close(con);
  247. }
  248.  
  249. if (playerObjId == 0) {
  250. return null;
  251. }
  252. return loadPlayerCommonData(playerObjId);
  253. }
  254.  
  255. @Override
  256. public PlayerCommonData loadPlayerCommonData(final int playerObjId) {
  257.  
  258. PlayerCommonData cached = playerCommonData.get(playerObjId);
  259. if (cached != null) {
  260. log.debug("[DAO: MySQL5PlayerDAO] PlayerCommonData for id: " + playerObjId + " obtained from cache");
  261. return cached;
  262. }
  263. final PlayerCommonData cd = new PlayerCommonData(playerObjId);
  264. boolean success = false;
  265. Connection con = null;
  266. try {
  267. con = DatabaseFactory.getConnection();
  268. PreparedStatement stmt = con.prepareStatement("SELECT * FROM players WHERE id = ?");
  269. stmt.setInt(1, playerObjId);
  270. ResultSet resultSet = stmt.executeQuery();
  271. log.debug("[DAO: MySQL5PlayerDAO] loading from db " + playerObjId);
  272.  
  273. if (resultSet.next()) {
  274. success = true;
  275. cd.setName(resultSet.getString("name"));
  276. // set player class before exp
  277. cd.setPlayerClass(PlayerClass.valueOf(resultSet.getString("player_class")));
  278. cd.setExp(resultSet.getLong("exp"));
  279. cd.setRecoverableExp(resultSet.getLong("recoverexp"));
  280. cd.setRace(Race.valueOf(resultSet.getString("race")));
  281. cd.setGender(Gender.valueOf(resultSet.getString("gender")));
  282. cd.setLastOnline(resultSet.getTimestamp("last_online"));
  283. cd.setNote(resultSet.getString("note"));
  284. cd.setQuestExpands(resultSet.getInt("quest_expands"));
  285. cd.setNpcExpands(resultSet.getInt("npc_expands"));
  286. cd.setAdvencedStigmaSlotSize(resultSet.getInt("advanced_stigma_slot_size"));
  287. cd.setTitleId(resultSet.getInt("title_id"));
  288. cd.setBonusTitleId(resultSet.getInt("bonus_title_id"));
  289. cd.setWarehouseSize(resultSet.getInt("warehouse_size"));
  290. cd.setOnline(resultSet.getBoolean("online"));
  291. cd.setMailboxLetters(resultSet.getInt("mailbox_letters"));
  292. cd.setDp(resultSet.getInt("dp"));
  293. cd.setDeathCount(resultSet.getInt("soul_sickness"));
  294. cd.setCurrentReposteEnergy(resultSet.getLong("reposte_energy"));
  295. cd.setBattleGroundPoints(resultSet.getInt("bg_points"));
  296.  
  297. float x = resultSet.getFloat("x");
  298. float y = resultSet.getFloat("y");
  299. float z = resultSet.getFloat("z");
  300. byte heading = resultSet.getByte("heading");
  301. int worldId = resultSet.getInt("world_id");
  302. PlayerInitialData playerInitialData = DataManager.PLAYER_INITIAL_DATA;
  303. MapRegion mr = World.getInstance().getWorldMap(worldId).getMainWorldMapInstance().getRegion(x, y, z);
  304. if (mr == null && playerInitialData != null) {
  305. // unstuck unlucky characters :)
  306. LocationData ld = playerInitialData.getSpawnLocation(cd.getRace());
  307. x = ld.getX();
  308. y = ld.getY();
  309. z = ld.getZ();
  310. heading = ld.getHeading();
  311. worldId = ld.getMapId();
  312. }
  313.  
  314. WorldPosition position = World.getInstance().createPosition(worldId, x, y, z, heading, 0);
  315. cd.setPosition(position);
  316. cd.setWorldOwnerId(resultSet.getInt("world_owner"));
  317. cd.setMentorFlagTime(resultSet.getInt("mentor_flag_time"));
  318. cd.setLastTransferTime(resultSet.getLong("last_transfer_time"));
  319. } else {
  320. log.info("Missing PlayerCommonData from db " + playerObjId);
  321. }
  322. resultSet.close();
  323. stmt.close();
  324. } catch (Exception e) {
  325. log.error("Could not restore PlayerCommonData data for player: " + playerObjId + " from DB: " + e.getMessage(), e);
  326. } finally {
  327. DatabaseFactory.close(con);
  328. }
  329.  
  330. if (success) {
  331. if (CacheConfig.CACHE_COMMONDATA) {
  332. playerCommonData.put(playerObjId, cd);
  333. playerCommonDataByName.put(cd.getName().toLowerCase(), cd);
  334. }
  335. return cd;
  336. }
  337. return null;
  338. }
  339.  
  340. /**
  341. * {@inheritDoc}
  342. */
  343. @Override
  344. public void deletePlayer(int playerId) {
  345. PreparedStatement statement = DB.prepareStatement("DELETE FROM players WHERE id = ?");
  346. try {
  347. statement.setInt(1, playerId);
  348. } catch (SQLException e) {
  349. log.error("Some crap, can't set int parameter to PreparedStatement", e);
  350. }
  351. if (CacheConfig.CACHE_COMMONDATA) {
  352. PlayerCommonData pcd = playerCommonData.remove(playerId);
  353. if (pcd != null) {
  354. playerCommonDataByName.remove(pcd.getName().toLowerCase());
  355. }
  356. }
  357. DB.executeUpdateAndClose(statement);
  358. }
  359.  
  360. /**
  361. * {@inheritDoc}
  362. */
  363. @Override
  364. public List<Integer> getPlayerOidsOnAccount(final int accountId) {
  365. final List<Integer> result = new ArrayList<Integer>();
  366. boolean success = DB.select("SELECT id FROM players WHERE account_id = ?", new ParamReadStH() {
  367. @Override
  368. public void handleRead(ResultSet resultSet) throws SQLException {
  369. while (resultSet.next()) {
  370. result.add(resultSet.getInt("id"));
  371. }
  372. }
  373.  
  374. @Override
  375. public void setParams(PreparedStatement preparedStatement) throws SQLException {
  376. preparedStatement.setInt(1, accountId);
  377. }
  378. });
  379.  
  380. return success ? result : null;
  381. }
  382.  
  383. /**
  384. * {@inheritDoc}
  385. */
  386. @Override
  387. public void setCreationDeletionTime(final PlayerAccountData acData) {
  388. DB.select("SELECT creation_date, deletion_date FROM players WHERE id = ?", new ParamReadStH() {
  389. @Override
  390. public void setParams(PreparedStatement stmt) throws SQLException {
  391. stmt.setInt(1, acData.getPlayerCommonData().getPlayerObjId());
  392. }
  393.  
  394. @Override
  395. public void handleRead(ResultSet rset) throws SQLException {
  396. rset.next();
  397.  
  398. acData.setDeletionDate(rset.getTimestamp("deletion_date"));
  399. acData.setCreationDate(rset.getTimestamp("creation_date"));
  400. }
  401. });
  402. }
  403.  
  404. /**
  405. * {@inheritDoc}
  406. */
  407. @Override
  408. public void updateDeletionTime(final int objectId, final Timestamp deletionDate) {
  409. DB.insertUpdate("UPDATE players set deletion_date = ? where id = ?", new IUStH() {
  410. @Override
  411. public void handleInsertUpdate(PreparedStatement preparedStatement) throws SQLException {
  412. preparedStatement.setTimestamp(1, deletionDate);
  413. preparedStatement.setInt(2, objectId);
  414. preparedStatement.execute();
  415. }
  416. });
  417. }
  418.  
  419. /**
  420. * {@inheritDoc}
  421. */
  422. @Override
  423. public void storeCreationTime(final int objectId, final Timestamp creationDate) {
  424. DB.insertUpdate("UPDATE players set creation_date = ? where id = ?", new IUStH() {
  425. @Override
  426. public void handleInsertUpdate(PreparedStatement preparedStatement) throws SQLException {
  427. preparedStatement.setTimestamp(1, creationDate);
  428. preparedStatement.setInt(2, objectId);
  429. preparedStatement.execute();
  430. }
  431. });
  432. }
  433.  
  434. @Override
  435. public void storeLastOnlineTime(final int objectId, final Timestamp lastOnline) {
  436. DB.insertUpdate("UPDATE players set last_online = ? where id = ?", new IUStH() {
  437. @Override
  438. public void handleInsertUpdate(PreparedStatement preparedStatement) throws SQLException {
  439. preparedStatement.setTimestamp(1, lastOnline);
  440. preparedStatement.setInt(2, objectId);
  441. preparedStatement.execute();
  442. }
  443. });
  444. }
  445.  
  446. /**
  447. * {@inheritDoc}
  448. */
  449. @Override
  450. public int[] getUsedIDs() {
  451. PreparedStatement statement = DB.prepareStatement("SELECT id FROM players", ResultSet.TYPE_SCROLL_INSENSITIVE,
  452. ResultSet.CONCUR_READ_ONLY);
  453.  
  454. try {
  455. ResultSet rs = statement.executeQuery();
  456. rs.last();
  457. int count = rs.getRow();
  458. rs.beforeFirst();
  459. int[] ids = new int[count];
  460. for (int i = 0; i < count; i++) {
  461. rs.next();
  462. ids[i] = rs.getInt("id");
  463. }
  464. return ids;
  465. } catch (SQLException e) {
  466. log.error("Can't get list of id's from players table", e);
  467. } finally {
  468. DB.close(statement);
  469. }
  470.  
  471. return new int[0];
  472. }
  473.  
  474. /**
  475. * {@inheritDoc} - Saelya
  476. */
  477. @Override
  478. public void onlinePlayer(final Player player, final boolean online) {
  479. DB.insertUpdate("UPDATE players SET online=? WHERE id=?", new IUStH() {
  480. @Override
  481. public void handleInsertUpdate(PreparedStatement stmt) throws SQLException {
  482. log.debug("[DAO: MySQL5PlayerDAO] online status " + player.getObjectId() + " " + player.getName());
  483.  
  484. stmt.setBoolean(1, online);
  485. stmt.setInt(2, player.getObjectId());
  486. stmt.execute();
  487. }
  488. });
  489. }
  490.  
  491. /**
  492. * {@inheritDoc} - Nemiroff
  493. */
  494. @Override
  495. public void setPlayersOffline(final boolean online) {
  496. DB.insertUpdate("UPDATE players SET online=?", new IUStH() {
  497. @Override
  498. public void handleInsertUpdate(PreparedStatement stmt) throws SQLException {
  499. stmt.setBoolean(1, online);
  500. stmt.execute();
  501. }
  502. });
  503. }
  504.  
  505. @Override
  506. public String getPlayerNameByObjId(final int playerObjId) {
  507. final String[] result = new String[1];
  508. DB.select("SELECT name FROM players WHERE id = ?", new ParamReadStH() {
  509. @Override
  510. public void handleRead(ResultSet arg0) throws SQLException {
  511. // TODO: Auto-generated method stub
  512. arg0.next();
  513. result[0] = arg0.getString("name");
  514. }
  515.  
  516. @Override
  517. public void setParams(PreparedStatement arg0) throws SQLException {
  518. // TODO: Auto-generated method stub
  519. arg0.setInt(1, playerObjId);
  520. }
  521. });
  522. return result[0];
  523. }
  524.  
  525. @Override
  526. public int getPlayerIdByName(final String playerName) {
  527. final int[] result = new int[1];
  528. DB.select("SELECT id FROM players WHERE name = ?", new ParamReadStH() {
  529. @Override
  530. public void handleRead(ResultSet arg0) throws SQLException {
  531. // TODO: Auto-generated method stub
  532. arg0.next();
  533. result[0] = arg0.getInt("id");
  534. }
  535.  
  536. @Override
  537. public void setParams(PreparedStatement arg0) throws SQLException {
  538. // TODO: Auto-generated method stub
  539. arg0.setString(1, playerName);
  540. }
  541. });
  542. return result[0];
  543. }
  544.  
  545. /**
  546. * {@inheritDoc}
  547. */
  548. @Override
  549. public int getAccountIdByName(final String name) {
  550. Connection con = null;
  551. int accountId = 0;
  552. try {
  553. con = DatabaseFactory.getConnection();
  554. PreparedStatement s = con.prepareStatement("SELECT `account_id` FROM `players` WHERE `name` = ?");
  555. s.setString(1, name);
  556. ResultSet rs = s.executeQuery();
  557. rs.next();
  558. accountId = rs.getInt("account_id");
  559. rs.close();
  560. s.close();
  561. } catch (Exception e) {
  562. return 0;
  563. } finally {
  564. DatabaseFactory.close(con);
  565. }
  566. return accountId;
  567. }
  568.  
  569. /**
  570. * @author xTz
  571. */
  572. @Override
  573. public void storePlayerName(final PlayerCommonData recipientCommonData) {
  574. Connection con = null;
  575. try {
  576. con = DatabaseFactory.getConnection();
  577. PreparedStatement stmt = con.prepareStatement("UPDATE players SET name=? WHERE id=?");
  578.  
  579. log.debug("[DAO: MySQL5PlayerDAO] storing playerName " + recipientCommonData.getPlayerObjId() + " "
  580. + recipientCommonData.getName());
  581.  
  582. stmt.setString(1, recipientCommonData.getName());
  583. stmt.setInt(2, recipientCommonData.getPlayerObjId());
  584. stmt.execute();
  585. stmt.close();
  586. } catch (Exception e) {
  587. log.error(
  588. "Error saving playerName: " + recipientCommonData.getPlayerObjId() + " " + recipientCommonData.getName(), e);
  589. } finally {
  590. DatabaseFactory.close(con);
  591. }
  592. }
  593.  
  594. @Override
  595. public int getCharacterCountOnAccount(final int accountId) {
  596. Connection con = null;
  597. int cnt = 0;
  598.  
  599. try {
  600. con = DatabaseFactory.getConnection();
  601. PreparedStatement stmt = con
  602. .prepareStatement("SELECT COUNT(*) AS cnt FROM `players` WHERE `account_id` = ? AND (players.deletion_date IS NULL || players.deletion_date > CURRENT_TIMESTAMP)");
  603. stmt.setInt(1, accountId);
  604. ResultSet rs = stmt.executeQuery();
  605. rs.next();
  606. cnt = rs.getInt("cnt");
  607. rs.close();
  608. stmt.close();
  609. } catch (Exception e) {
  610. return 0;
  611. } finally {
  612. DatabaseFactory.close(con);
  613. }
  614.  
  615. return cnt;
  616. }
  617.  
  618. @Override
  619. public int getCharacterCountForRace(Race race) {
  620. Connection con = null;
  621. int count = 0;
  622. try {
  623. con = DatabaseFactory.getConnection();
  624. PreparedStatement stmt = con
  625. .prepareStatement("SELECT COUNT(DISTINCT(`account_name`)) AS `count` FROM `players` WHERE `race` = ? AND `exp` >= ?");
  626. stmt.setString(1, race.name());
  627. stmt.setLong(2, DataManager.PLAYER_EXPERIENCE_TABLE.getStartExpForLevel(GSConfig.RATIO_MIN_REQUIRED_LEVEL));
  628. ResultSet rs = stmt.executeQuery();
  629. rs.next();
  630. count = rs.getInt("count");
  631. rs.close();
  632. stmt.close();
  633. } catch (Exception e) {
  634. return 0;
  635. } finally {
  636. DatabaseFactory.close(con);
  637. }
  638.  
  639. return count;
  640. }
  641.  
  642. @Override
  643. public int getOnlinePlayerCount() {
  644. Connection con = null;
  645. int count = 0;
  646. try {
  647. con = DatabaseFactory.getConnection();
  648. PreparedStatement stmt = con.prepareStatement("SELECT COUNT(*) AS `count` FROM `players` WHERE `online` = ?");
  649. stmt.setBoolean(1, true);
  650. ResultSet rs = stmt.executeQuery();
  651. rs.next();
  652. count = rs.getInt("count");
  653. rs.close();
  654. stmt.close();
  655. } catch (Exception e) {
  656. return 0;
  657. } finally {
  658. DatabaseFactory.close(con);
  659. }
  660.  
  661. return count;
  662. }
  663.  
  664. /**
  665. * {@inheritDoc}
  666. */
  667. @Override
  668. public Set<Integer> getInactiveAccounts(final int daysOfInactivity, final int limitation) {
  669. String SELECT_QUERY = "SELECT account_id FROM players WHERE UNIX_TIMESTAMP(CURDATE())-UNIX_TIMESTAMP(last_online) > ? * 24 * 60 * 60";
  670.  
  671. final Map<Integer, Integer> inactiveAccounts = FastMap.newInstance();
  672.  
  673. DB.select(SELECT_QUERY, new ParamReadStH() {
  674. @Override
  675. public void setParams(PreparedStatement stmt) throws SQLException {
  676. stmt.setInt(1, daysOfInactivity);
  677. }
  678.  
  679. @Override
  680. public void handleRead(ResultSet rset) throws SQLException {
  681. while (rset.next() && (limitation == 0 || limitation > inactiveAccounts.size())) {
  682. int accountId = rset.getInt("account_id");
  683. //number of inactive chars on account
  684. Integer numberOfChars = 0;
  685.  
  686. if ((numberOfChars = inactiveAccounts.get(accountId)) != null) {
  687. inactiveAccounts.put(accountId, numberOfChars + 1);
  688. } else {
  689. inactiveAccounts.put(accountId, 1);
  690. }
  691. }
  692. }
  693. });
  694.  
  695. //filter accounts with active chars on them
  696. for (Iterator<Entry<Integer, Integer>> i = inactiveAccounts.entrySet().iterator(); i.hasNext();) {
  697. Entry<Integer, Integer> entry = i.next();
  698.  
  699. //atleast one active char on account
  700. if (entry.getValue() < this.getCharacterCountOnAccount(entry.getKey())) {
  701. i.remove();
  702. }
  703. }
  704.  
  705. return inactiveAccounts.keySet();
  706. }
  707.  
  708. /**
  709. * {@inheritDoc} - KID
  710. */
  711. @Override
  712. public void setPlayerLastTransferTime(final int playerId, final long time) {
  713. DB.insertUpdate("UPDATE players SET last_transfer_time=? WHERE id=?", new IUStH() {
  714. @Override
  715. public void handleInsertUpdate(PreparedStatement stmt) throws SQLException {
  716. stmt.setLong(1, time);
  717. stmt.setInt(2, playerId);
  718. stmt.execute();
  719. }
  720. });
  721. }
  722.  
  723. /**
  724. * {@inheritDoc}
  725. */
  726. @Override
  727. public boolean supports(String s, int i, int i1) {
  728. return MySQL5DAOUtils.supports(s, i, i1);
  729. }
  730. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!