public void saveToDB(boolean update, boolean full) {
Connection con = DatabaseConnection.getConnection();
try {
// clients should not be able to log back before their old state is saved (see MapleClient#getLoginState) so we are save to switch to a very low isolation level here
con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
// connections are thread local now, no need to synchronize anymore =)
con.setAutoCommit(false);
PreparedStatement ps;
if (update) {
ps = con.prepareStatement("UPDATE characters SET level = ?, fame = ?, str = ?, dex = ?, luk = ?, `int` = ?, exp = ?, hp = ?, mp = ?, maxhp = ?, maxmp = ?, sp = ?, ap = ?, gm = ?, skincolor = ?, gender = ?, job = ?, hair = ?, face = ?, map = ?, meso = ?, hpApUsed = ?, mpApUsed = ?, spawnpoint = ?, party = ?, buddyCapacity = ?, messengerid = ?, messengerposition = ?, reborns = ?, pvpkills = ?, pvpdeaths = ?, clan = ?, mountlevel = ?, mountexp = ?, mounttiredness = ?, married = ?, partnerid = ?, zakumlvl = ?, marriagequest = ?, rateselection = ? WHERE id = ?");
} else {
ps = con.prepareStatement("INSERT INTO characters (level, fame, str, dex, luk, `int`, exp, hp, mp, maxhp, maxmp, sp, ap, gm, skincolor, gender, job, hair, face, map, meso, hpApUsed, mpApUsed, spawnpoint, party, buddyCapacity, messengerid, messengerposition, reborns, pvpkills, pvpdeaths, clan, mountlevel, mountexp, mounttiredness, married, partnerid, zakumlvl, marriagequest, accountid, name, world) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
}
ps.setInt(1, level);
ps.setInt(2, fame);
ps.setInt(3, str);
ps.setInt(4, dex);
ps.setInt(5, luk);
ps.setInt(6, int_);
ps.setInt(7, exp.get());
ps.setInt(8, hp);
ps.setInt(9, mp);
ps.setInt(10, maxhp);
ps.setInt(11, maxmp);
ps.setInt(12, remainingSp);
ps.setInt(13, remainingAp);
ps.setInt(14, gmLevel);
ps.setInt(15, skinColor.getId());
ps.setInt(16, gender);
ps.setInt(17, job.getId());
ps.setInt(18, hair);
ps.setString(19, rateselect);
ps.setInt(20, face);
if (map == null) {
ps.setInt(20, 541010110);
} else {
if (map.getForcedReturnId() != 999999999) {
ps.setInt(20, map.getForcedReturnId());
} else {
ps.setInt(20, map.getId());
}
}
ps.setInt(21, meso.get());
ps.setInt(22, hpApUsed);
ps.setInt(23, mpApUsed);
if (map == null || map.getId() == 610020000 || map.getId() == 610020001) {
ps.setInt(24, 0);
} else {
MaplePortal closest = map.findClosestSpawnpoint(getPosition());
if (closest != null) {
ps.setInt(24, closest.getId());
} else {
ps.setInt(24, 0);
}
}
if (party != null) {
ps.setInt(25, party.getId());
} else {
ps.setInt(25, -1);
}
ps.setInt(26, buddylist.getCapacity());
if (messenger != null) {
ps.setInt(27, messenger.getId());
ps.setInt(28, messengerposition);
} else {
ps.setInt(27, 0);
ps.setInt(28, 4);
}
ps.setInt(29, reborns);
ps.setInt(30, pvpkills);
ps.setInt(31, pvpdeaths);
ps.setInt(*** clan);
if (maplemount != null) {
ps.setInt(33, maplemount.getLevel());
ps.setInt(34, maplemount.getExp());
ps.setInt(35, maplemount.getTiredness());
} else {
ps.setInt(33, 1);
ps.setInt(34, 0);
ps.setInt(35, 0);
}
ps.setInt(36, married ? 1 : 0);
ps.setInt(37, partnerid);
ps.setInt(38, zakumLvl > 2 ? 2 : zakumLvl);
ps.setInt(39, marriageQuestLevel);
if (update) {
ps.setInt(40, id);
} else {
ps.setInt(40, accountid);
ps.setString(41, name);
ps.setInt(42, world);
}
if (!full) {
ps.executeUpdate();
ps.close();
} else {
int updateRows = ps.executeUpdate();
if (!update) {
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
this.id = rs.getInt(1);
} else {
throw new DatabaseException("Inserting char failed.");
}
rs.close();
} else if (updateRows < 1) {
throw new DatabaseException("Character not in database (" + id + ")");
}
ps.close();
for (int i = 0; i < 3; i++) {
if (pets[i] != null) {
pets[i].saveToDb();
} else {
break;
}
}
deleteWhereCharacterId(con, "DELETE FROM skillmacros WHERE characterid = ?");
for (int i = 0; i < 5; i++) {
SkillMacro macro = skillMacros[i];
if (macro != null) {
ps = con.prepareStatement("INSERT INTO skillmacros (characterid, skill1, skill2, skill3, name, shout, position) VALUES (?, ?, ?, ?, ?, ?, ?)");
ps.setInt(1, id);
ps.setInt(2, macro.getSkill1());
ps.setInt(3, macro.getSkill2());
ps.setInt(4, macro.getSkill3());
ps.setString(5, macro.getName());
ps.setInt(6, macro.getShout());
ps.setInt(7, i);
ps.executeUpdate();
ps.close();
}
}
deleteWhereCharacterId(con, "DELETE FROM inventoryitems WHERE characterid = ?");
ps = con.prepareStatement("INSERT INTO inventoryitems (characterid, itemid, inventorytype, position, quantity, owner, petid) VALUES (?, ?, ?, ?, ?, ?, ?)");
PreparedStatement pse = con.prepareStatement("INSERT INTO inventoryequipment VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
for (MapleInventory iv : inventory) {
ps.setInt(3, iv.getType().getType());
for (IItem item : iv.list()) {
ps.setInt(1, id);
ps.setInt(2, item.getItemId());
ps.setInt(4, item.getPosition());
ps.setInt(5, item.getQuantity());
ps.setString(6, item.getOwner());
ps.setInt(7, item.getPetId());
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
int itemid;
if (rs.next()) {
itemid = rs.getInt(1);
rs.close();
} else {
rs.close();
throw new DatabaseException("Inserting char failed.");
}
if (iv.getType().equals(MapleInventoryType.EQUIP) || iv.getType().equals(MapleInventoryType.EQUIPPED)) {
pse.setInt(1, itemid);
IEquip equip = (IEquip) item;
pse.setInt(2, equip.getUpgradeSlots());
pse.setInt(3, equip.getLevel());
pse.setInt(4, equip.getStr());
pse.setInt(5, equip.getDex());
pse.setInt(6, equip.getInt());
pse.setInt(7, equip.getLuk());
pse.setInt(8, equip.getHp());
pse.setInt(9, equip.getMp());
pse.setInt(10, equip.getWatk());
pse.setInt(11, equip.getMatk());
pse.setInt(12, equip.getWdef());
pse.setInt(13, equip.getMdef());
pse.setInt(14, equip.getAcc());
pse.setInt(15, equip.getAvoid());
pse.setInt(16, equip.getHands());
pse.setInt(17, equip.getSpeed());
pse.setInt(18, equip.getJump());
pse.setInt(19, equip.getRingId());
pse.setInt(20, equip.getLocked());
pse.executeUpdate();
}
}
}
ps.close();
pse.close();
deleteWhereCharacterId(con, "DELETE FROM queststatus WHERE characterid = ?");
ps = con.prepareStatement("INSERT INTO queststatus (`queststatusid`, `characterid`, `quest`, `status`, `time`, `forfeited`) VALUES (DEFAULT, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
pse = con.prepareStatement("INSERT INTO queststatusmobs VALUES (DEFAULT, ?, ?, ?)");
ps.setInt(1, id);
for (MapleQuestStatus q : quests.values()) {
ps.setInt(2, q.getQuest().getId());
ps.setInt(3, q.getStatus().getId());
ps.setInt(4, (int) (q.getCompletionTime() / 1000));
ps.setInt(5, q.getForfeited());
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
rs.next();
for (int mob : q.getMobKills().keySet()) {
pse.setInt(1, rs.getInt(1));
pse.setInt(2, mob);
pse.setInt(3, q.getMobKills(mob));
pse.executeUpdate();
}
rs.close();
}
ps.close();
pse.close();
deleteWhereCharacterId(con, "DELETE FROM skills WHERE characterid = ?");
ps = con.prepareStatement("INSERT INTO skills (characterid, skillid, skilllevel, masterlevel) VALUES (?, ?, ?, ?)");
ps.setInt(1, id);
for (Entry<ISkill, SkillEntry> skill_ : skills.entrySet()) {
ps.setInt(2, skill_.getKey().getId());
ps.setInt(3, skill_.getValue().skillevel);
ps.setInt(4, skill_.getValue().masterlevel);
ps.executeUpdate();
}
ps.close();
deleteWhereCharacterId(con, "DELETE FROM keymap WHERE characterid = ?");
ps = con.prepareStatement("INSERT INTO keymap (characterid, `key`, `type`, `action`) VALUES (?, ?, ?, ?)");
ps.setInt(1, id);
for (Entry<Integer, MapleKeyBinding> keybinding : keymap.entrySet()) {
ps.setInt(2, keybinding.getKey().intValue());
ps.setInt(3, keybinding.getValue().getType());
ps.setInt(4, keybinding.getValue().getAction());
ps.executeUpdate();
}
ps.close();
deleteWhereCharacterId(con, "DELETE FROM savedlocations WHERE characterid = ?");
ps = con.prepareStatement("INSERT INTO savedlocations (characterid, `locationtype`, `map`) VALUES (?, ?, ?)");
ps.setInt(1, id);
for (SavedLocationType savedLocationType : SavedLocationType.values()) {
if (savedLocations[savedLocationType.ordinal()] != -1) {
ps.setString(2, savedLocationType.name());
ps.setInt(3, savedLocations[savedLocationType.ordinal()]);
ps.executeUpdate();
}
}
ps.close();
deleteWhereCharacterId(con, "DELETE FROM buddies WHERE characterid = ? AND pending = 0");
ps = con.prepareStatement("INSERT INTO buddies (characterid, `buddyid`, `pending`) VALUES (?, ?, 0)");
ps.setInt(1, id);
for (BuddylistEntry entry : buddylist.getBuddies()) {
if (entry.isVisible()) {
ps.setInt(2, entry.getCharacterId());
ps.executeUpdate();
}
}
ps.close();
ps = con.prepareStatement("UPDATE accounts SET `paypalNX` = ?, `mPoints` = ?, `cardNX` = ?, `donorPoints` = ?,`votepoints` = ? WHERE id = ?");
ps.setInt(1, paypalnx);
ps.setInt(2, maplepoints);
ps.setInt(3, cardnx);
ps.setInt(4, donatePoints);
ps.setInt(5, votepoints);
ps.setInt(6, client.getAccID());
ps.executeUpdate();
ps.close();
if (storage != null) {
storage.saveToDB();
}
if (update) {
ps = con.prepareStatement("DELETE FROM achievements WHERE accountid = ?");
ps.setInt(1, accountid);
ps.executeUpdate();
ps.close();
for (Integer achid : finishedAchievements) {
ps = con.prepareStatement("INSERT INTO achievements(charid, achievementid, accountid) VALUES(?, ?, ?)");
ps.setInt(1, id);
ps.setInt(2, achid);
ps.setInt(3, accountid);
ps.executeUpdate();
ps.close();
}
}
}
con.commit();
} catch (Exception e) {
log.error(MapleClient.getLogMessage(this, "[charsave] Error saving character data"), e);
try {
con.rollback();
} catch (SQLException e1) {
log.error(MapleClient.getLogMessage(this, "[charsave] Error Rolling Back"), e);
}
} finally {
try {
con.setAutoCommit(true);
con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
} catch (SQLException e) {
log.error(MapleClient.getLogMessage(this, "[charsave] Error going back to autocommit mode"), e);
}
}
}
.
