如何通过JPA按指定百分比批量更新实体中的所有价格？

Let's break down the issues in your code and fix them step by step:

Key Problems in Your Current Implementation #

• Mismatched Named Query Name: You're using "ProductDTO.updatePriceByProcent" (note the spelling Procent) in your code, but your named query is defined as "ProductDTO.updatePriceByPercent" (Percent). This will throw a NoSuchQueryException since JPA can't locate the query.
• Updating a DTO Instead of an Entity: JPA's UPDATE operation only works with entity classes (annotated with @Entity). ProductDTO is a Data Transfer Object, not an entity, so you can't run an update query against it directly.
• Calling getResultList() After executeUpdate(): The executeUpdate() method is for write operations (INSERT/UPDATE/DELETE) and returns the number of affected rows. getResultList() is meant for SELECT queries—calling it after an update will throw an exception because there's no result set to return.

Step-by-Step Solution #

1. Correct the Named Query (Target Your Entity) #

First, ensure you have an entity class (e.g., Product) annotated with @Entity, and define the update query against it:

@Entity
@NamedQuery(
    name = "Product.updatePriceByPercent",
    query = "UPDATE Product p SET p.price = p.price * (1 + :percent/100)"
)
public class Product {
    @Id
    private Long id;
    private BigDecimal price; // Use BigDecimal for currency to avoid precision issues
    // Add other fields, getters, and setters
}

This query updates each product's price by the given percentage:

• A positive percent (e.g., 10) increases the price by 10%
• A negative percent (e.g., -5) decreases the price by 5%

2. Fix the Service Layer Code #

Update your method to first run the bulk update, then fetch the updated entities and convert them to DTOs:

@Override
public List<ProductDTO> updatePriceByPercent(double percent) {
    EntityManager em = getEntityManager();
    try {
        em.getTransaction().begin();

        // Execute the bulk update
        Query updateQuery = em.createNamedQuery("Product.updatePriceByPercent");
        updateQuery.setParameter("percent", percent);
        int affectedRows = updateQuery.executeUpdate();
        System.out.printf("Updated %d products%n", affectedRows);

        // Fetch all updated products (add filters here if needed)
        TypedQuery<Product> fetchQuery = em.createQuery("SELECT p FROM Product p", Product.class);
        List<Product> updatedProducts = fetchQuery.getResultList();

        em.getTransaction().commit();

        // Convert entities to DTOs (implement your own conversion logic)
        return updatedProducts.stream()
                .map(this::convertToProductDTO)
                .collect(Collectors.toList());
    } catch (Exception e) {
        if (em.getTransaction().isActive()) {
            em.getTransaction().rollback();
        }
        throw new RuntimeException("Failed to update product prices", e);
    } finally {
        em.close();
    }
}

// Example DTO conversion method
private ProductDTO convertToProductDTO(Product product) {
    ProductDTO dto = new ProductDTO();
    dto.setId(product.getId());
    dto.setPrice(product.getPrice());
    // Map other fields as needed
    return dto;
}

3. Critical Notes #

• Transaction Management: Always wrap write operations (like executeUpdate()) in a transaction to ensure data consistency.
• Use BigDecimal for Currency: For price fields, avoid double or float due to precision errors. Update your method parameter to BigDecimal and adjust the query accordingly if needed.
• Avoid Direct DTO Updates: JPA operates on entities, not DTOs. Always update entities first, then convert them to DTOs for your API or service layer.

内容的提问来源于stack exchange，提问作者Przemek Lewandowski