rendered paste bodyIndex: C:/L2J/L2_GameServer_c4/java/net/sf/l2j/Config.java
===================================================================
--- C:/L2J/L2_GameServer_c4/java/net/sf/l2j/Config.java (revisione 1593)
+++ C:/L2J/L2_GameServer_c4/java/net/sf/l2j/Config.java (copia locale)
@@ -518,6 +518,7 @@
public static boolean BYPASS_VALIDATION;
public static boolean GMAUDIT;
+ public static long SQL_LOG_EXEC_TIME_MAX;
public static void load()
{
@@ -674,6 +675,7 @@
BYPASS_VALIDATION = Boolean.valueOf(serverSettings.getProperty("BypassValidation", "False"));
GMAUDIT = Boolean.valueOf(serverSettings.getProperty("GMAudit", "False"));
+ SQL_LOG_EXEC_TIME_MAX = Integer.parseInt(serverSettings.getProperty("SQLLogExecTimeMax", "30000"));
}
catch (Exception e)
{
Index: C:/L2J/L2_GameServer_c4/java/net/sf/l2j/sql/SQLHandler.java
===================================================================
--- C:/L2J/L2_GameServer_c4/java/net/sf/l2j/sql/SQLHandler.java (revisione 0)
+++ C:/L2J/L2_GameServer_c4/java/net/sf/l2j/sql/SQLHandler.java (revisione 0)
@@ -0,0 +1,262 @@
+package net.sf.l2j.sql;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.text.SimpleDateFormat;
+import java.util.Iterator;
+import java.util.List;
+import java.util.logging.Logger;
+
+import net.sf.l2j.gameserver.model.L2ItemInstance;
+import net.sf.l2j.Config;
+import net.sf.l2j.L2DatabaseFactory;
+
+public final class SQLHandler
+{
+ private static final String SELECT_REMOVED_ITEMS_SQL = "SELECT object_id, count FROM items WHERE object_id = ?";
+ private static final String DELETE_REMOVED_ITEMS_SQL = "DELETE FROM items WHERE object_id = ?";
+ private static final String UPDATE_REMOVED_ITEMS_SQL = "UPDATE items SET count = ? WHERE object_id = ?";
+ private static final String INSERT_NEW_ITEMS_SQL = "INSERT INTO items VALUES (?,?,?,?,?,?,?,?,?,null,0,0)";
+
+ private static Logger _log = Logger.getLogger(SQLHandler.class.getName());
+
+ public boolean removeItems(List<L2ItemInstance> removeItems)
+ {
+ long beginTime = System.currentTimeMillis();
+ Connection con = null;
+
+ try
+ {
+ con = L2DatabaseFactory.getInstance().getConnection();
+
+ con.setAutoCommit(false);
+ for (Iterator<L2ItemInstance> it = removeItems.iterator(); it.hasNext();)
+ {
+ L2ItemInstance item = it.next();
+
+ PreparedStatement statement = con.prepareStatement(SELECT_REMOVED_ITEMS_SQL);
+ statement.setInt(1, item.getObjectId());
+ ResultSet res = statement.executeQuery();
+ if (res.next())
+ {
+ if (res.getInt("count") == item.getCount())
+ {
+ PreparedStatement statement2 = con.prepareStatement(DELETE_REMOVED_ITEMS_SQL);
+ statement2.setInt(1, item.getObjectId());
+ statement2.execute();
+ }
+ else
+ {
+ PreparedStatement statement2 = con.prepareStatement(UPDATE_REMOVED_ITEMS_SQL);
+ statement2.setInt(1, item.getCount());
+ statement2.setInt(2, item.getObjectId());
+ statement2.execute();
+ }
+ }
+ else
+ {
+ PreparedStatement statement2 = con.prepareStatement(DELETE_REMOVED_ITEMS_SQL);
+ statement2.setInt(1, item.getObjectId());
+ statement2.execute();
+ }
+ }
+ con.commit();
+ }
+ catch (Exception e1)
+ {
+ try
+ {
+ if (con != null) con.rollback();
+ }
+ catch (SQLException e2)
+ {
+ _log.warning("Unable to rollback transaction!");
+ return false;
+ }
+ _log.warning("Error removing Objects from DB. Transaction rollback.");
+ return false;
+ }
+ finally
+ {
+ try
+ {
+ if (con != null) con.close();
+ }
+ catch (SQLException e3)
+ {
+ _log.warning("Unable to close connection!");
+ return false;
+ }
+ validateExecutionTime(beginTime);
+ }
+ return true;
+ }
+
+ public boolean addItems(List<L2ItemInstance> addItems)
+ {
+ long beginTime = System.currentTimeMillis();
+ Connection con = null;
+
+ try
+ {
+ con = L2DatabaseFactory.getInstance().getConnection();
+
+ con.setAutoCommit(false);
+ for (Iterator<L2ItemInstance> it = addItems.iterator(); it.hasNext();)
+ {
+ L2ItemInstance item = it.next();
+
+ PreparedStatement statement = con.prepareStatement(INSERT_NEW_ITEMS_SQL);
+ statement.setInt(1, item.getOwnerId());
+ statement.setInt(2, item.getObjectId());
+ statement.setInt(3, item.getItemId());
+ statement.setInt(4, item.getCount());
+ statement.setInt(5, item.getEnchantLevel());
+ statement.setString(6, item.getLocation().toString());
+ statement.setInt(7, item.getEquipSlot());
+ statement.setInt(8, item.getPriceToSell());
+ statement.setInt(9, item.getPriceToBuy());
+ statement.execute();
+ }
+ con.commit();
+ }
+ catch (Exception e1)
+ {
+ try
+ {
+ if (con != null) con.rollback();
+ }
+ catch (SQLException e2)
+ {
+ _log.warning("Unable to rollback transaction!");
+ return false;
+ }
+ _log.warning("Error inserting Objects into DB. Transaction rollback.");
+ return false;
+ }
+ finally
+ {
+ try
+ {
+ if (con != null) con.close();
+ }
+ catch (SQLException e3)
+ {
+ _log.warning("Unable to close connection!");
+ return false;
+ }
+ validateExecutionTime(beginTime);
+ }
+ return true;
+ }
+
+ public boolean exchangeItems(List<L2ItemInstance> addItems, List<L2ItemInstance> removeItems)
+ {
+ long beginTime = System.currentTimeMillis();
+ Connection con = null;
+
+ try
+ {
+ con = L2DatabaseFactory.getInstance().getConnection();
+
+ con.setAutoCommit(false);
+ for (Iterator<L2ItemInstance> it = removeItems.iterator(); it.hasNext();)
+ {
+ L2ItemInstance item = it.next();
+
+ PreparedStatement statement = con.prepareStatement(SELECT_REMOVED_ITEMS_SQL);
+ statement.setInt(1, item.getObjectId());
+ ResultSet res = statement.executeQuery();
+ if (res.next())
+ {
+ if (res.getInt("count") == item.getCount())
+ {
+ PreparedStatement statement2 = con.prepareStatement(DELETE_REMOVED_ITEMS_SQL);
+ statement2.setInt(1, item.getObjectId());
+ statement2.execute();
+ }
+ else
+ {
+ PreparedStatement statement2 = con.prepareStatement(UPDATE_REMOVED_ITEMS_SQL);
+ statement2.setInt(1, item.getCount());
+ statement2.setInt(2, item.getObjectId());
+ statement2.execute();
+ }
+ }
+ else
+ {
+ PreparedStatement statement2 = con.prepareStatement(DELETE_REMOVED_ITEMS_SQL);
+ statement2.setInt(1, item.getObjectId());
+ statement2.execute();
+ }
+ }
+
+ for (Iterator<L2ItemInstance> it = addItems.iterator(); it.hasNext();)
+ {
+ L2ItemInstance item = it.next();
+
+ PreparedStatement statement = con.prepareStatement(INSERT_NEW_ITEMS_SQL);
+ statement.setInt(1, item.getOwnerId());
+ statement.setInt(2, item.getObjectId());
+ statement.setInt(3, item.getItemId());
+ statement.setInt(4, item.getCount());
+ statement.setInt(5, item.getEnchantLevel());
+ statement.setString(6, item.getLocation().toString());
+ statement.setInt(7, item.getEquipSlot());
+ statement.setInt(8, item.getPriceToSell());
+ statement.setInt(9, item.getPriceToBuy());
+ statement.execute();
+ }
+ con.commit();
+ }
+ catch (Exception e1)
+ {
+ try
+ {
+ if (con != null) con.rollback();
+ }
+ catch (SQLException e2)
+ {
+ _log.warning("Unable to rollback transaction!");
+ return false;
+ }
+ _log.warning("Error exchanging Objects in DB. Transaction rollback.");
+ return false;
+ }
+ finally
+ {
+ try
+ {
+ if (con != null) con.close();
+ }
+ catch (SQLException e3)
+ {
+ _log.warning("Unable to close connection!");
+ return false;
+ }
+ validateExecutionTime(beginTime);
+ }
+ return true;
+ }
+
+ private void validateExecutionTime(long beginTime)
+
+ {
+ long endTime = System.currentTimeMillis();
+
+ if (Config.SQL_LOG_EXEC_TIME_MAX <= (endTime - beginTime))
+ {
+ if (Config.DEBUG) // this part dumps data if we want it (DEBUG==True)
+ {
+ _log.warning("exchangeItems() method Execution start: " + new SimpleDateFormat("HH:mm:ss:SSS").format(beginTime));
+ _log.warning("exchangeItems() method Execution end: " + new SimpleDateFormat("HH:mm:ss:SSS").format(endTime));
+ _log.warning("Command Execution Time: " + (endTime - beginTime) + " milliseconds.");
+ }
+
+ // and here we can add some serious handling if we need to
+ // (like reinitializing C3P0 or some in-depth analysys of why connections took so long to execute)
+ }
+ }
+}