Over-Fetching?

[mighty mango]

So I have a shopping cart API, I have a Cart with OneToMany CartItem, and CartItem with ManyToOne Product.

In my service, I have this code for adding the item:

Cart cart = cartRepository.findById(cartId).orElseThrow(CartNotFoundException::new);

Product product = productRepository.findById(productId).orElseThrow(ProductNotFoundException::new);

cart.addItem(product);

cartRepository.save(cart);

addItem:

public void addItem(Product product) {
        CartItem cartItem = getItem(product.getId());

        if (cartItem != null) {
            cartItem.setQuantity(cartItem.getQuantity() + 1);
        } else {
            cartItem = new CartItem();
            cartItem.setCart(this);
            cartItem.setProduct(product);
            cartItem.setQuantity(1);

            items.add(cartItem);
        }
    }

All of these result in a total of 4 (when I add quantity) database queries, is this something concerning or do I need to optimize?

[dull oak]

<@&987246883653156906> please have a look, thanks.

[grizzled moth]

Technically you don't actually need to load the cart and product. You just need to know that the ids are valid to use for a new CartItem.

Rather than loading them, you can make a query that verifies they are valid, and then ask the EntityManager for a reference (a proxy) for the entity itself.

// check validity of cartId, failing the operation if not.

// and then we just get a proxy for it.
Cart cart = entityManager.getReference(Cart.class, cartId);

Do the same for product.

This reduces the work, but not the roundtrips. You can reduce the cart and product id-validity check to a single query though.

It involves some messier approaches to replace the Read + update-or-insert with an 'upsert' operation - I don't think JPA exposes support for that natively yet.

In SQL it looks something like

INSERT INTO ... (...) VALUES (...)
ON CONFLICT (...)
DO UPDATE SET ...

So you can have it conflict on the (cart_id, product_id) and do an update instead.