Advanced Topics

import java.sql.Connection;
import org.pojoquery.DB;

DB.runInTransaction(dataSource, (Connection connection) -> {
    // All operations use the same connection = same transaction
    User user = new User();
    user.setFirstName("Alice");
    PojoQuery.insert(connection, user);

    Account account = new Account();
    account.setUserId(user.getId());
    account.setBalance(BigDecimal.ZERO);
    PojoQuery.insert(connection, account);

    return true; // Return value from the transaction
});

// Using a DataSource - connection is obtained and closed automatically
public static <T> T runInTransaction(DataSource dataSource, Transaction<T> transaction);

// Using an existing Connection - you manage the connection lifecycle
public static <T> T runInTransaction(Connection connection, Transaction<T> transaction);

Order createdOrder = DB.runInTransaction(dataSource, connection -> {
    Order order = new Order();
    order.setCustomerId(customerId);
    order.setOrderDate(LocalDate.now());
    PojoQuery.insert(connection, order);

    for (CartItem item : cartItems) {
        OrderLine line = new OrderLine();
        line.setOrderId(order.getId());
        line.setProductId(item.getProductId());
        line.setQuantity(item.getQuantity());
        PojoQuery.insert(connection, line);
    }

    return order; // Return the created order with its generated ID
});

try {
    DB.runInTransaction(dataSource, connection -> {
        PojoQuery.insert(connection, order);
        PojoQuery.insert(connection, invalidOrderLine); // This fails
        return null;
    });
} catch (DB.DatabaseException e) {
    // Transaction was rolled back
    // The cause contains the original SQLException
    System.err.println("Transaction failed: " + e.getCause().getMessage());
}

DB.runInTransaction(dataSource, connection -> {
    PojoQuery.delete(connection, oldRecord);
    PojoQuery.insert(connection, newRecord);
    return null; // No meaningful return value
});

DB.runInTransaction(dataSource, outerConn -> {
    PojoQuery.insert(outerConn, record1);

    // This uses the same transaction - NOT a nested transaction
    DB.runInTransaction(outerConn, innerConn -> {
        PojoQuery.insert(innerConn, record2);
        return null;
    });

    return null;
});
// Both inserts commit or rollback together

import org.pojoquery.HasVersion;

@Table("product")
public class Product implements HasVersion {
    @Id Long id;
    String name;
    BigDecimal price;
    Long version; // Version column in database

    @Override
    public Long getVersion() {
        return version;
    }

    @Override
    public void setVersion(Long version) {
        this.version = version;
    }

    // Other getters and setters omitted for brevity
}

CREATE TABLE product (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    price DECIMAL(10,2),
    version BIGINT DEFAULT 0
);

import org.pojoquery.StaleObjectException;

// User A fetches the product
Product productA = PojoQuery.build(Product.class)
    .addWhere("product.id = ?", 123L)
    .execute(dataSource)
    .get(0);
// productA.getVersion() = 1

// User B fetches the same product
Product productB = PojoQuery.build(Product.class)
    .addWhere("product.id = ?", 123L)
    .execute(dataSource)
    .get(0);
// productB.getVersion() = 1

// User A updates the product
productA.setPrice(new BigDecimal("29.99"));
PojoQuery.update(dataSource, productA); // Success! version becomes 2

// User B tries to update the same product
productB.setPrice(new BigDecimal("24.99"));
try {
    PojoQuery.update(dataSource, productB); // Fails! version mismatch
} catch (StaleObjectException e) {
    // Handle the conflict - typically refresh and retry, or notify user
    System.out.println("Product was modified by another user. Please refresh.");
}

public void updateProductPrice(Long productId, BigDecimal newPrice) {
    int maxRetries = 3;
    for (int attempt = 0; attempt < maxRetries; attempt++) {
        try {
            Product product = PojoQuery.build(Product.class)
                .addWhere("product.id = ?", productId)
                .execute(dataSource)
                .stream().findFirst()
                .orElseThrow(() -> new RuntimeException("Product not found"));

            product.setPrice(newPrice);
            PojoQuery.update(dataSource, product);
            return; // Success

        } catch (StaleObjectException e) {
            if (attempt == maxRetries - 1) {
                throw new RuntimeException("Failed to update after " + maxRetries + " attempts", e);
            }
            // Retry with fresh data
        }
    }
}