I have a packet handler that basically is used for buying an item -> user talks to an NPC, which will perform checks.

Happy case -> player has money -> update the item table and reduce their money -> insert into the item table (purchased item now belongs to the player).

And this is the code now:

import static org.l2jmobius.gameserver.model.actor.Npc.INTERACTION_DISTANCE;
import static org.l2jmobius.gameserver.model.itemcontainer.Inventory.MAX_ADENA;

import java.util.ArrayList;
import java.util.List;

import org.l2jmobius.Config;
import org.l2jmobius.gameserver.data.xml.BuyListData;
import org.l2jmobius.gameserver.model.WorldObject;
import org.l2jmobius.gameserver.model.actor.Creature;
import org.l2jmobius.gameserver.model.actor.Player;
import org.l2jmobius.gameserver.model.actor.instance.Merchant;
import org.l2jmobius.gameserver.model.buylist.BuyListHolder;
import org.l2jmobius.gameserver.model.buylist.Product;
import org.l2jmobius.gameserver.model.holders.ItemHolder;
import org.l2jmobius.gameserver.network.PacketLogger;
import org.l2jmobius.gameserver.network.SystemMessageId;
import org.l2jmobius.gameserver.network.serverpackets.ActionFailed;
import org.l2jmobius.gameserver.network.serverpackets.ItemList;
import org.l2jmobius.gameserver.network.serverpackets.StatusUpdate;
import org.l2jmobius.gameserver.util.Util;

public class RequestBuyItem extends ClientPacket
{
private static final int BATCH_LENGTH = 8;
private int _listId;
private List<ItemHolder> _items = null;
@Override
protected void readImpl()
{
_listId = readInt();
final int size = readInt();
if ((size <= 0) || (size > Config.MAX_ITEM_IN_PACKET) || ((size * BATCH_LENGTH) != remaining()))
{
return;
}
_items = new ArrayList<>(size);
for (int i = 0; i < size; i++)
{
final int itemId = readInt();
final int count = readInt();
if ((count > Integer.MAX_VALUE) || (itemId < 1) || (count < 1))
{
_items = null;
return;
}
if (count > 10000) // Count check.
{
_items = null;
return;
}
_items.add(new ItemHolder(itemId, count));
}
}
@Override
protected void runImpl()
{
final Player player = getPlayer();
if (player == null)
{
return;
}
if (!getClient().getFloodProtectors().canPerformTransaction())
{
player.sendMessage("You are buying too fast.");
return;
}
if (_items == null)
{
player.sendMessage("You cannot buy more than 10.000 items.");
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
// Alt game - Karma punishment
if (!Config.ALT_GAME_KARMA_PLAYER_CAN_SHOP && (player.getKarma() > 0))
{
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
final WorldObject target = player.getTarget();
Creature merchant = null;
if (!player.isGM())
{
if (!(target instanceof Merchant) || (!player.isInsideRadius3D(target, INTERACTION_DISTANCE)) || (player.getInstanceId() != target.getInstanceId()))
{
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
merchant = (Creature) target;
}
double castleTaxRate = 0;
double baseTaxRate = 0;
if ((merchant == null) && !player.isGM())
{
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
final BuyListHolder buyList = BuyListData.getInstance().getBuyList(_listId);
if (buyList == null)
{
Util.handleIllegalPlayerAction(player, "Warning!! Character " + player.getName() + " of account " + player.getAccountName() + " sent a false BuyList list_id " + _listId, Config.DEFAULT_PUNISH);
return;
}
if (merchant != null)
{
if (!buyList.isNpcAllowed(merchant.getId()))
{
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
if (merchant instanceof Merchant)
{
castleTaxRate = ((Merchant) merchant).getMpc().getCastleTaxRate();
baseTaxRate = ((Merchant) merchant).getMpc().getBaseTaxRate();
}
else
{
baseTaxRate = 0.5;
}
}
int subTotal = 0;
// Check for buylist validity and calculates summary values
long slots = 0;
long weight = 0;
for (ItemHolder i : _items)
{
int price = -1;
final Product product = buyList.getProductByItemId(i.getId());
if (product == null)
{
Util.handleIllegalPlayerAction(player, "Warning!! Character " + player.getName() + " of account " + player.getAccountName() + " sent a false BuyList list_id " + _listId + " and item_id " + i.getId(), Config.DEFAULT_PUNISH);
return;
}
if (!product.getItem().isStackable() && (i.getCount() > 1))
{
Util.handleIllegalPlayerAction(player, "Warning!! Character " + player.getName() + " of account " + player.getAccountName() + " tried to purchase invalid quantity of items at the same time.", Config.DEFAULT_PUNISH);
player.sendPacket(SystemMessageId.YOU_HAVE_EXCEEDED_THE_QUANTITY_THAT_CAN_BE_INPUTTED);
return;
}
price = product.getPrice();
if ((product.getItemId() >= 3960) && (product.getItemId() <= 4026))
{
price *= Config.RATE_SIEGE_GUARDS_PRICE;
}
if (price < 0)
{
PacketLogger.warning("ERROR, no price found .. wrong buylist ??");
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
if ((price == 0) && !player.isGM() && Config.ONLY_GM_ITEMS_FREE)
{
player.sendMessage("Ohh Cheat dont work? You have a problem now!");
Util.handleIllegalPlayerAction(player, "Warning!! Character " + player.getName() + " of account " + player.getAccountName() + " tried buy item for 0 adena.", Config.DEFAULT_PUNISH);
return;
}
// trying to buy more then available
if (product.hasLimitedStock() && (i.getCount() > product.getCount()))
{
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
if ((MAX_ADENA / i.getCount()) < price)
{
Util.handleIllegalPlayerAction(player, "Warning!! Character " + player.getName() + " of account " + player.getAccountName() + " tried to purchase over " + MAX_ADENA + " adena worth of goods.", Config.DEFAULT_PUNISH);
return;
}
// first calculate price per item with tax, then multiply by count
price = (int) (price * (1 + castleTaxRate + baseTaxRate));
subTotal += i.getCount() * price;
if (subTotal > MAX_ADENA)
{
Util.handleIllegalPlayerAction(player, "Warning!! Character " + player.getName() + " of account " + player.getAccountName() + " tried to purchase over " + MAX_ADENA + " adena worth of goods.", Config.DEFAULT_PUNISH);
return;
}
weight += i.getCount() * product.getItem().getWeight();
if (player.getInventory().getItemByItemId(product.getItemId()) == null)
{
slots++;
}
}
if (!player.isGM() && ((weight > Integer.MAX_VALUE) || (weight < 0) || !player.getInventory().validateWeight((int) weight)))
{
player.sendPacket(SystemMessageId.YOU_HAVE_EXCEEDED_THE_WEIGHT_LIMIT);
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
if (!player.isGM() && ((slots > Integer.MAX_VALUE) || (slots < 0) || !player.getInventory().validateCapacity((int) slots)))
{
player.sendPacket(SystemMessageId.YOUR_INVENTORY_IS_FULL);
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
// Charge buyer and add tax to castle treasury if not owned by npc clan
if ((subTotal < 0) || !player.reduceAdena("Buy", subTotal, player.getLastFolkNPC(), false))
{
player.sendPacket(SystemMessageId.YOU_DO_NOT_HAVE_ENOUGH_ADENA);
player.sendPacket(ActionFailed.STATIC_PACKET);
return;
}
// Proceed the purchase
for (ItemHolder i : _items)
{
final Product product = buyList.getProductByItemId(i.getId());
if (product == null)
{
Util.handleIllegalPlayerAction(player, "Warning!! Character " + player.getName() + " of account " + player.getAccountName() + " sent a false BuyList list_id " + _listId + " and item_id " + i.getId(), Config.DEFAULT_PUNISH);
continue;
}
if (product.hasLimitedStock())
{
if (product.decreaseCount(i.getCount()))
{
player.getInventory().addItem("Buy", i.getId(), i.getCount(), player, merchant);
}
}
else
{
player.getInventory().addItem("Buy", i.getId(), i.getCount(), player, merchant);
}
}
// add to castle treasury
if (merchant instanceof Merchant)
{
((Merchant) merchant).getCastle().addToTreasury((long) (subTotal * castleTaxRate));
}
final StatusUpdate su = new StatusUpdate(player);
su.addAttribute(StatusUpdate.CUR_LOAD, player.getCurrentLoad());
player.sendPacket(su);
player.sendPacket(new ItemList(player, true));
}
}

if ((subTotal < 0) || !player.reduceAdena("Buy", subTotal, player.getLastFolkNPC(), false)) -> this is called to reduce the adena

and here is added:player.getInventory().addItem("Buy", i.getId(), i.getCount(), player, merchant);

In other words, the ideal is to have a database transaction here, but I have no idea how I would be able to do this. These are the update / insert functions:

Insert function:

public Item saveItem(Item item) {
    if (_existsInDb || getObjectId() == 0) {
        return null;
    }

    try (Connection con = DatabaseFactory.getConnection();
         PreparedStatement ps = con.prepareStatement("INSERT INTO items (owner_id,item_id,count,loc,loc_data,enchant_level,object_id,custom_type1,custom_type2,mana_left,time) VALUES (?,?,?,?,?,?,?,?,?,?,?)")) {

        ps.setInt(1, _ownerId);
        ps.setInt(2, _itemId);
        ps.setInt(3, getCount());
        ps.setString(4, _loc.name());
        ps.setInt(5, _locData);
        ps.setInt(6, getEnchantLevel());
        ps.setInt(7, getObjectId());
        ps.setInt(8, _type1);
        ps.setInt(9, _type2);
        ps.setInt(10, _mana);
        ps.setLong(11, getTime());
        ps.executeUpdate();

        if (_augmentation != null) {
            updateItemAttributes(con);
        }

        _existsInDb = true;
        _storedInDb = true;

        ps.executeUpdate();
        return item;
    } catch (SQLException e) {
        LOGGER.log(Level.SEVERE, "Error saving item to database: " + e.getMessage(), e);
        return null;
    }
}

Update function:

private void updateInDb() {
    if (!_existsInDb || _wear || _storedInDb) {
        return;
    }

    try (Connection con = DatabaseFactory.getConnection();
         PreparedStatement ps = con.prepareStatement("UPDATE items SET owner_id=?,count=?,loc=?,loc_data=?,enchant_level=?,custom_type1=?,custom_type2=?,mana_left=?,time=? WHERE object_id = ?")) {
        ps.setInt(1, _ownerId);
        ps.setInt(2, _count);
        ps.setString(3, _loc.name());
        ps.setInt(4, _locData);
        ps.setInt(5, _enchantLevel);
        ps.setInt(6, _type1);
        ps.setInt(7, _type2);
        ps.setInt(8, _mana);
        ps.setLong(9, _time);
        ps.setInt(10, getObjectId());
        ps.executeUpdate();
        _existsInDb = true;
        _storedInDb = true;
    } catch (Exception e) {
        LOGGER.log(Level.SEVERE, "Could not update item " + this + " in DB: Reason: " + e.getMessage(), e);
    }
}

I wanted to add a proxy pattern at the beginning, starting the transaction in the proxy and passing it to where I handle the connection, but as I’m new to Java I’m a little lost on how to do this without annotations

Essentially, I need to rollback if it fails at any point, send a response packet, and if it’s a happy case, I need to commit.