Gnipi

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 final SQLHandler _instance = new SQLHandler();	
    private static Logger _log = Logger.getLogger(SQLHandler.class.getName());

    public static SQLHandler getInstance() { return _instance; };
    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;    }
    
    public String getStringValue(String what, String from, String where,String value)
    {
		Connection con=null;
		String result="";
		
		try
		{
			con = L2DatabaseFactory.getInstance().getConnection();
			PreparedStatement statement = con.prepareStatement("SELECT "+what+" FROM "+from+" WHERE "+where+"=?");
			statement.setString(1, value);
			ResultSet rset = statement.executeQuery();
			if (rset.next())
			{
				result = rset.getString(what);
				statement.close();
				con.close();
				return result;
			}
			statement.close();
		} catch (SQLException e) {
				_log.warning("SQLException: "+e);
		} finally 
		{
			try {
				if (con != null)	con.close();
			} catch (SQLException e2) {
				_log.warning("Unable to close connection!" + e2);
			}
		}
    	return "";
    }
    
    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 start: " + 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)
    	}
	}
}