[Spring Boot Basics] Truy vấn với Spring Data JPA: derived query method, @Query với JPQL và native query

package com.example.demo.product;
 
import java.math.BigDecimal;
 
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
 
@Entity
@Table(name = "products")
public class Product {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @Column(nullable = false, length = 120)
    private String name;
 
    @Column(nullable = false, length = 40, unique = true)
    private String sku;
 
    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;
 
    private int stock;
 
    @Column(nullable = false, length = 60)
    private String category;
 
    @Enumerated(EnumType.STRING)
    @Column(nullable = false, length = 20)
    private ProductStatus status = ProductStatus.ACTIVE;
 
    private Boolean featured; 
 
    protected Product() {
    }
 
    public Product(String name, String sku, BigDecimal price, int stock, String category) {
        this.name = name;
        this.sku = sku;
        this.price = price;
        this.stock = stock;
        this.category = category;
    }
 
    public Boolean getFeatured() { return featured; } 
    public void setFeatured(Boolean featured) { this.featured = featured; } 
 
    // the other getters and setters, equals, hashCode and toString as in article 26
}

package com.example.demo.product;
 
import static com.example.demo.product.ProductStatus.ACTIVE;
import static com.example.demo.product.ProductStatus.DISCONTINUED;
import static com.example.demo.product.ProductStatus.OUT_OF_STOCK;
 
import java.math.BigDecimal;
import java.util.List;
 
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
 
@Component
@Order(1)
class CatalogSeeder implements CommandLineRunner {
 
    private final ProductRepository repository;
 
    CatalogSeeder(ProductRepository repository) {
        this.repository = repository;
    }
 
    @Override
    public void run(String... args) {
        if (repository.count() > 0) {
            return;
        }
        repository.saveAll(List.of(
                product("Mechanical keyboard", "KB-01", "89.90", 25, "keyboards", ACTIVE, true),
                product("Compact keyboard", "KB-02", "59.00", 12, "keyboards", ACTIVE, null),
                product("Wireless keyboard", "KB-03", "45.50", 0, "keyboards", DISCONTINUED, false),
                product("Ergonomic keyboard", "KB-04", "129.00", 7, "keyboards", ACTIVE, null),
                product("Wireless mouse", "MS-01", "24.50", 3, "mice", ACTIVE, null),
                product("Gaming mouse", "MS-02", "49.90", 18, "mice", ACTIVE, null),
                product("Vertical mouse", "MS-03", "39.00", 0, "mice", OUT_OF_STOCK, false),
                product("27-inch monitor", "MN-01", "279.00", 6, "monitors", ACTIVE, null),
                product("32-inch 4K monitor", "MN-02", "449.00", 2, "monitors", ACTIVE, true),
                product("Portable monitor", "MN-03", "189.00", 9, "monitors", ACTIVE, null),
                product("Noise-cancelling headphones", "AU-01", "199.00", 11, "audio", ACTIVE, true),
                product("USB microphone", "AU-02", "89.90", 4, "audio", ACTIVE, null),
                product("Desk speakers", "AU-03", "79.00", 0, "audio", DISCONTINUED, false),
                product("USB-C hub", "AC-01", "39.00", 40, "accessories", ACTIVE, null),
                product("Laptop stand", "AC-02", "34.90", 15, "accessories", ACTIVE, null),
                product("Mouse pad XL", "AC-03", "19.90", 60, "accessories", ACTIVE, null),
                product("Screen cleaner, 100% alcohol-free", "AC-04", "9.90", 80, "accessories", ACTIVE, null),
                product("Webcam 1080p", "AC-05", "64.00", 14, "accessories", ACTIVE, null),
                product("Cable organiser", "AC-06", "12.50", 33, "accessories", ACTIVE, null)));
    }
 
    private static Product product(String name, String sku, String price, int stock, String category,
                                   ProductStatus status, Boolean featured) {
        Product product = new Product(name, sku, new BigDecimal(price), stock, category);
        product.setStatus(status);
        product.setFeatured(featured);
        return product;
    }
}

spring.application.name=demo
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE

spring.jpa.hibernate.ddl-auto=create 

package com.example.demo.lab;
 
import java.util.List;
import java.util.function.Supplier;
 
import com.example.demo.product.Product;
import com.example.demo.product.ProductRepository;
 
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
 
@Component
@Order(2)
class QueryLab implements CommandLineRunner {
 
    private final ProductRepository repository;
 
    QueryLab(ProductRepository repository) {
        this.repository = repository;
    }
 
    @Override
    public void run(String... args) {
        show("findByCategory(\"monitors\")", () -> repository.findByCategory("monitors"));
    }
 
    private void show(String call, Supplier<List<Product>> query) {
        System.out.println(">>> " + call);
        List<Product> products = query.get();
        for (Product p : products) {
            System.out.println(String.format("%-5s  %-33s %7s  %3d  %-11s %-12s %s",
                    p.getSku(), p.getName(), p.getPrice(), p.getStock(), p.getCategory(),
                    p.getStatus(), p.getFeatured()));
        }
        System.out.println("(" + products.size() + " rows)");
    }
}

./gradlew bootJar
java -jar build/libs/demo-0.0.1-SNAPSHOT.jar

>>> findByCategory("monitors")
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.category=?
binding parameter (1:VARCHAR) <- [monitors]
MN-01  27-inch monitor                    279.00    6  monitors    ACTIVE       null
MN-02  32-inch 4K monitor                 449.00    2  monitors    ACTIVE       true
MN-03  Portable monitor                   189.00    9  monitors    ACTIVE       null
(3 rows)

package com.example.demo.product;
 
import java.math.BigDecimal;
import java.util.List;
 
import org.springframework.data.jpa.repository.JpaRepository;
 
public interface ProductRepository extends JpaRepository<Product, Long> {
 
    List<Product> findByCategory(String category);
 
    List<Product> findTop3ByCategoryAndPriceLessThanOrderByPriceDesc(String category, BigDecimal price); 
}

java -jar build/libs/demo-0.0.1-SNAPSHOT.jar --logging.level.org.springframework.data.jpa.repository.query=DEBUG

>>> findTop3ByCategoryAndPriceLessThanOrderByPriceDesc("keyboards", 100)
QueryPreparer: Derived query for query method [public abstract java.util.List com.example.demo.product.ProductRepository.findTop3ByCategoryAndPriceLessThanOrderByPriceDesc(java.lang.String,java.math.BigDecimal)]: 'SELECT p FROM Product p WHERE p.category = :category AND p.price < :price ORDER BY p.price desc'
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.category=? and p1_0.price<? order by p1_0.price desc fetch first ? rows only
binding parameter (1:VARCHAR) <- [keyboards]
binding parameter (2:NUMERIC) <- [100]
binding parameter (3:INTEGER) <- [3]
KB-01  Mechanical keyboard                 89.90   25  keyboards   ACTIVE       true
KB-02  Compact keyboard                    59.00   12  keyboards   ACTIVE       null
KB-03  Wireless keyboard                   45.50    0  keyboards   DISCONTINUED false
(3 rows)

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    List<Product> findByCategoryAndStockGreaterThanOrStatus(String category, int stock, ProductStatus status); 
}

>>> findByCategoryAndStockGreaterThanOrStatus("keyboards", 10, DISCONTINUED)
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.category=? and p1_0.stock>? or p1_0.status=?
binding parameter (1:VARCHAR) <- [keyboards]
binding parameter (2:INTEGER) <- [10]
binding parameter (3:ENUM) <- [DISCONTINUED]
KB-01  Mechanical keyboard                 89.90   25  keyboards   ACTIVE       true
KB-02  Compact keyboard                    59.00   12  keyboards   ACTIVE       null
KB-03  Wireless keyboard                   45.50    0  keyboards   DISCONTINUED false
AU-03  Desk speakers                       79.00    0  audio       DISCONTINUED false
(4 rows)

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    Optional<Product> findBySku(String sku); 
 
    Optional<Product> findByPrice(BigDecimal price); 
 
    Product findByStock(int stock); 
 
    Stream<Product> streamByCategory(String category); 
 
    boolean existsBySku(String sku); 
 
    long countByCategory(String category); 
}

>>> findByPrice(89.90)
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.price=?
binding parameter (1:NUMERIC) <- [89.90]
!!! org.springframework.dao.IncorrectResultSizeDataAccessException: Query did not return a unique result: 2 results were returned
!!! caused by org.hibernate.NonUniqueResultException: Query did not return a unique result: 2 results were returned

>>> streamByCategory("keyboards") without a transaction
!!! org.springframework.dao.InvalidDataAccessApiUsageException: You're trying to execute a streaming query method without a surrounding transaction that keeps the connection open so that the Stream can actually be consumed; Make sure the code consuming the stream uses @Transactional or any other way of declaring a (read-only) transaction

@Transactional(readOnly = true)
public List<String> skusInCategory(String category) {
    try (Stream<Product> products = repository.streamByCategory(category)) {
        return products.map(Product::getSku).toList();
    }
}

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    List<Product> findByCategory(String category);
 
    List<Product> findByCategry(String category); 
}

2026-09-13T16:55:07.431+07:00 ERROR 37010 --- [demo] [           main] o.s.boot.SpringApplication               : Application run failed
...
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'productRepository' defined in com.example.demo.product.ProductRepository defined in @EnableJpaRepositories declared on DataJpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: No property 'categry' found for type 'Product'; Did you mean 'category'
...
Caused by: org.springframework.data.repository.query.QueryCreationException: Cannot create query for method [ProductRepository.findByCategry(java.lang.String)]; No property 'categry' found for type 'Product'; Did you mean 'category'
	at org.springframework.data.repository.query.QueryCreationException.create(QueryCreationException.java:109) ~[spring-data-commons-4.1.1.jar!/:4.1.1]
...
Caused by: org.springframework.data.core.PropertyReferenceException: No property 'categry' found for type 'Product'; Did you mean 'category'
	at org.springframework.data.core.SimplePropertyPath.<init>(SimplePropertyPath.java:94) ~[spring-data-commons-4.1.1.jar!/:4.1.1]

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    @Query("select p from Product p where p.category = :category and p.price <= :maxPrice order by p.price") 
    List<Product> findInCategoryUpTo(String category, BigDecimal maxPrice); 
}

>>> findInCategoryUpTo("keyboards", 90)
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.category=? and p1_0.price<=? order by p1_0.price
binding parameter (1:VARCHAR) <- [keyboards]
binding parameter (2:NUMERIC) <- [90]
KB-03  Wireless keyboard                   45.50    0  keyboards   DISCONTINUED false
KB-02  Compact keyboard                    59.00   12  keyboards   ACTIVE       null
KB-01  Mechanical keyboard                 89.90   25  keyboards   ACTIVE       true
(3 rows)

javap -v -cp build/classes/java/main com.example.demo.product.ProductRepository | grep -m1 -A2 'MethodParameters:'

    MethodParameters:
      Name                           Flags
      category

tasks.withType(JavaCompile).configureEach {
    options.compilerArgs.remove('-parameters')
}

>>> searchByName("MOUSE")
!!! org.springframework.dao.InvalidDataAccessApiUsageException: For queries with named parameters you need to provide names for method parameters; Use @Param for query method parameters, or when on Java 8+ use the javac flag -parameters
!!! caused by java.lang.IllegalStateException: For queries with named parameters you need to provide names for method parameters; Use @Param for query method parameters, or when on Java 8+ use the javac flag -parameters

    List<Product> findInCategoryUpTo(String category, BigDecimal maxPrice); 
    List<Product> findInCategoryUpTo(@Param("category") String category, @Param("maxPrice") BigDecimal maxPrice); 

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    @Query("select p from Product p where p.status = ?1 and p.stock < ?2 order by p.stock") 
    List<Product> findRunningLow(ProductStatus status, int threshold); 
}

>>> findRunningLow(ACTIVE, 5)
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.status=? and p1_0.stock<? order by p1_0.stock
binding parameter (1:ENUM) <- [ACTIVE]
binding parameter (2:INTEGER) <- [5]
MN-02  32-inch 4K monitor                 449.00    2  monitors    ACTIVE       true
MS-01  Wireless mouse                      24.50    3  mice        ACTIVE       null
AU-02  USB microphone                      89.90    4  audio       ACTIVE       null
(3 rows)

    @Query("select p from Product p where p.category = :category and p.price <= :maxPrice order by p.price") 
    @Query("select p from products p where p.category = :category and p.price <= :maxPrice order by p.price") 
    List<Product> findInCategoryUpTo(String category, BigDecimal maxPrice);

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'productRepository' defined in com.example.demo.product.ProductRepository defined in @EnableJpaRepositories declared on DataJpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Query validation failed for 'select p from products p where p.category = :category and p.price <= :maxPrice order by p.price'
...
Caused by: org.springframework.data.repository.query.QueryCreationException: Cannot create query for method [ProductRepository.findInCategoryUpTo(java.lang.String,java.math.BigDecimal)]; Query validation failed for 'select p from products p where p.category = :category and p.price <= :maxPrice order by p.price'
...
Caused by: java.lang.IllegalArgumentException: org.hibernate.query.sqm.UnknownEntityException: Could not resolve root entity 'products'
...
Caused by: org.hibernate.query.sqm.UnknownEntityException: Could not resolve root entity 'products'

Caused by: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: At 1:7 and token '*', extraneous input '*' expecting {'(', '[', ':', '{', '?', ID, VERSION, VERSIONED, NATURALID, FK, ABSENT, ALL, AND, ANY, ARRAY, AS, ASC, AVG, BETWEEN, BOTH, BREADTH, BY, CASE, CAST, COLLATE, COLUMN, COLUMNS, CONDITIONAL, CONFLICT, CONSTRAINT, ... HEX_LITERAL, BINARY_LITERAL, '{ts', '{d', '{t', '+', '-', IDENTIFIER, QUOTED_IDENTIFIER}; Bad HQL grammar [select * from products where category = :category and price <= :maxPrice order by price]

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    @Query("select p from Product p where lower(p.name) like lower(concat('%', :q, '%'))") 
    List<Product> searchByName(String q); 
}

>>> searchByName("MOUSE")
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where lower(p1_0.name) like lower(('%'||?||'%')) escape ''
binding parameter (1:VARCHAR) <- [MOUSE]
MS-01  Wireless mouse                      24.50    3  mice        ACTIVE       null
MS-02  Gaming mouse                        49.90   18  mice        ACTIVE       null
MS-03  Vertical mouse                      39.00    0  mice        OUT_OF_STOCK false
AC-03  Mouse pad XL                        19.90   60  accessories ACTIVE       null
(4 rows)

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    @Query("select p from Product p where p.sku in :skus") 
    List<Product> findAllBySkus(Collection<String> skus); 
}

>>> findAllBySkus([KB-01, MS-02, XX-99])
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.sku in (?,?,?)
binding parameter (1:VARCHAR) <- [KB-01]
binding parameter (2:VARCHAR) <- [MS-02]
binding parameter (3:VARCHAR) <- [XX-99]
KB-01  Mechanical keyboard                 89.90   25  keyboards   ACTIVE       true
MS-02  Gaming mouse                        49.90   18  mice        ACTIVE       null
(2 rows)

    @Query("select avg(p.price) from Product p where p.category = :category")
    Double averagePrice(String category);
 
    @Query("""
            select p.category, count(p), avg(p.price)
            from Product p
            group by p.category
            order by p.category
            """)
    List<Object[]> priceStatsByCategory();

>>> averagePrice("keyboards")
select avg(p1_0.price) from products p1_0 where p1_0.category=?
binding parameter (1:VARCHAR) <- [keyboards]
result 80.85 (Double)
>>> priceStatsByCategory()
select p1_0.category,count(p1_0.id),avg(p1_0.price) from products p1_0 group by p1_0.category order by p1_0.category
[accessories, 6, 30.033333333333]  (String, Long, Double)
[audio, 3, 122.633333333333]  (String, Long, Double)
[keyboards, 4, 80.85]  (String, Long, Double)
[mice, 3, 37.8]  (String, Long, Double)
[monitors, 3, 305.666666666667]  (String, Long, Double)
(5 rows)

>>> findByNameContaining("%")
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.name like ? escape '\'
binding parameter (1:VARCHAR) <- [%\%%]
AC-04  Screen cleaner, 100% alcohol-free    9.90   80  accessories ACTIVE       null
(1 rows)
>>> findByNameContaining("_")
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.name like ? escape '\'
binding parameter (1:VARCHAR) <- [%\_%]
(0 rows)
>>> searchByName("%")  [JPQL concat]
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where lower(p1_0.name) like lower(('%'||?||'%')) escape ''
binding parameter (1:VARCHAR) <- [%]
KB-01  Mechanical keyboard                 89.90   25  keyboards   ACTIVE       true
KB-02  Compact keyboard                    59.00   12  keyboards   ACTIVE       null
...
AC-06  Cable organiser                     12.50   33  accessories ACTIVE       null
(19 rows)

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    @Query("select p from Product p where p.name like %?#{escape([0])}% escape ?#{escapeCharacter()}") 
    List<Product> searchByNameEscaped(String q); 
}

>>> searchByNameEscaped("%")
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.name like ? escape ?
binding parameter (1:VARCHAR) <- [%\%%]
binding parameter (2:CHAR) <- [\]
AC-04  Screen cleaner, 100% alcohol-free    9.90   80  accessories ACTIVE       null
(1 rows)

    @Query(value = """
            select * from products
            where to_tsvector('english', name) @@ websearch_to_tsquery('english', :terms)
            order by price
            """, nativeQuery = true)
    List<Product> fullTextSearch(String terms);

java -jar build/libs/demo-0.0.1-SNAPSHOT.jar --spring.profiles.active=postgres

>>> fullTextSearch("keyboards")
select * from products
where to_tsvector('english', name) @@ websearch_to_tsquery('english', ?)
order by price
 
binding parameter (1:VARCHAR) <- [keyboards]
KB-03  Wireless keyboard                   45.50    0  keyboards   DISCONTINUED false
KB-02  Compact keyboard                    59.00   12  keyboards   ACTIVE       null
KB-01  Mechanical keyboard                 89.90   25  keyboards   ACTIVE       true
KB-04  Ergonomic keyboard                 129.00    7  keyboards   ACTIVE       null
(4 rows)
>>> fullTextSearch("wireless keyboards")
select * from products
where to_tsvector('english', name) @@ websearch_to_tsquery('english', ?)
order by price
 
binding parameter (1:VARCHAR) <- [wireless keyboards]
KB-03  Wireless keyboard                   45.50    0  keyboards   DISCONTINUED false
(1 rows)
>>> fullTextSearch("mouse -gaming")
select * from products
where to_tsvector('english', name) @@ websearch_to_tsquery('english', ?)
order by price
 
binding parameter (1:VARCHAR) <- [mouse -gaming]
AC-03  Mouse pad XL                        19.90   60  accessories ACTIVE       null
MS-01  Wireless mouse                      24.50    3  mice        ACTIVE       null
MS-03  Vertical mouse                      39.00    0  mice        OUT_OF_STOCK false
(3 rows)

>>> fullTextSearch("keyboards")
select * from products
where to_tsvector('english', name) @@ websearch_to_tsquery('english', ?)
order by price
 
HHH000247: ErrorCode: 90022, SQLState: 90022
Function "TO_TSVECTOR" not found; SQL statement:
select * from products
where to_tsvector('english', name) @@ websearch_to_tsquery('english', ?)
order by price
 [90022-240]
!!! org.springframework.dao.InvalidDataAccessResourceUsageException: Could not prepare statement [Function "TO_TSVECTOR" not found; SQL statement:
...
!!! caused by org.hibernate.exception.SQLGrammarException: Could not prepare statement [Function "TO_TSVECTOR" not found; SQL statement:
...
!!! caused by org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "TO_TSVECTOR" not found; SQL statement:

package com.example.demo.product;
 
import java.util.List;
 
import jakarta.persistence.EntityManager;
 
import org.springframework.stereotype.Component;
 
@Component
public class UnsafeProductSearch {
 
    private final EntityManager entityManager;
 
    public UnsafeProductSearch(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
 
    // DO NOT DO THIS: user input concatenated into SQL
    @SuppressWarnings("unchecked")
    public List<Product> findByName(String name) {
        String sql = "select * from products where name = '" + name + "'";
        return entityManager.createNativeQuery(sql, Product.class).getResultList();
    }
}

>>> unsafe.findByName("' OR '1'='1")
select * from products where name = '' OR '1'='1'
KB-01  Mechanical keyboard                 89.90   25  keyboards   ACTIVE       true
KB-02  Compact keyboard                    59.00   12  keyboards   ACTIVE       null
...
AC-06  Cable organiser                     12.50   33  accessories ACTIVE       null
(19 rows)

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    @Query(value = "select * from products where name = :name", nativeQuery = true) 
    List<Product> findByNameNative(String name); 
}

>>> repository.findByNameNative("' OR '1'='1")
select * from products where name = ?
binding parameter (1:VARCHAR) <- [' OR '1'='1]
(0 rows)
>>> repository.findByNameNative("USB-C hub")
select * from products where name = ?
binding parameter (1:VARCHAR) <- [USB-C hub]
AC-01  USB-C hub                           39.00   40  accessories ACTIVE       null
(1 rows)

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    @Modifying
    @Query("update Product p set p.price = p.price * :factor where p.category = :category") 
    int changePrices(String category, BigDecimal factor); 
}

>>> repository.changePrices("keyboards", 1.10) without a transaction
!!! org.springframework.dao.InvalidDataAccessApiUsageException: No active transaction for update or delete query
!!! caused by jakarta.persistence.TransactionRequiredException: No active transaction for update or delete query

>>> service.changePricesWithoutModifying("keyboards", 1.10)
!!! org.springframework.dao.InvalidDataAccessApiUsageException: Query executed via 'getResultList()' or 'getSingleResult()' must be a 'select' query [update Product p set p.price = p.price * :factor where p.category = :category]
!!! caused by java.lang.IllegalStateException: Query executed via 'getResultList()' or 'getSingleResult()' must be a 'select' query [update Product p set p.price = p.price * :factor where p.category = :category]
!!! caused by org.hibernate.query.IllegalSelectQueryException: Expecting a SELECT Query [org.hibernate.query.sqm.tree.select.SqmSelectStatement], but found org.hibernate.query.sqm.tree.update.SqmUpdateStatement [update Product p set p.price = p.price * :factor where p.category = :category]

package com.example.demo.product;
 
import java.math.BigDecimal;
 
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
@Service
public class ProductService {
 
    private final ProductRepository repository;
 
    public ProductService(ProductRepository repository) {
        this.repository = repository;
    }
 
    @Transactional
    public int changePrices(String category, BigDecimal factor) {
        return repository.changePrices(category, factor);
    }
}

>>> service.changePrices("keyboards", 1.10)
update products p1_0 set price=(p1_0.price*cast(? as numeric(10,2))) where p1_0.category=?
binding parameter (1:NUMERIC) <- [1.10]
binding parameter (2:VARCHAR) <- [keyboards]
result 4 (Integer)
>>> findByCategory("keyboards") after
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.category=?
binding parameter (1:VARCHAR) <- [keyboards]
KB-01  Mechanical keyboard                 98.89   25  keyboards   ACTIVE       true
KB-02  Compact keyboard                    64.90   12  keyboards   ACTIVE       null
KB-03  Wireless keyboard                   50.05    0  keyboards   DISCONTINUED false
KB-04  Ergonomic keyboard                 141.90    7  keyboards   ACTIVE       null
(4 rows)
>>> service.changePrices("nothing", 1.10)
update products p1_0 set price=(p1_0.price*cast(? as numeric(10,2))) where p1_0.category=?
binding parameter (1:NUMERIC) <- [1.10]
binding parameter (2:VARCHAR) <- [nothing]
result 0 (Integer)

@Transactional
public void priceChangeTrace() {
    Product keyboard = repository.findBySku("KB-01").orElseThrow();
    System.out.println("1. loaded       KB-01 price " + keyboard.getPrice());
 
    int rows = repository.changePrices("keyboards", new BigDecimal("1.10"));
    System.out.println("2. bulk update  " + rows + " rows");
 
    Product byId = repository.findById(keyboard.getId()).orElseThrow();
    System.out.println("3. findById     KB-01 price " + byId.getPrice() + ", same object: " + (byId == keyboard));
 
    Product bySku = repository.findBySku("KB-01").orElseThrow();
    System.out.println("4. findBySku    KB-01 price " + bySku.getPrice() + ", same object: " + (bySku == keyboard));
    System.out.println("5. old variable KB-01 price " + keyboard.getPrice());
}

select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.sku=?
binding parameter (1:VARCHAR) <- [KB-01]
1. loaded       KB-01 price 89.90
update products p1_0 set price=(p1_0.price*cast(? as numeric(10,2))) where p1_0.category=?
binding parameter (1:NUMERIC) <- [1.10]
binding parameter (2:VARCHAR) <- [keyboards]
2. bulk update  4 rows
3. findById     KB-01 price 89.90, same object: true
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.sku=?
binding parameter (1:VARCHAR) <- [KB-01]
4. findBySku    KB-01 price 89.90, same object: true
5. old variable KB-01 price 89.90

@Transactional
public void staleEntityWrittenBack() {
    Product keyboard = repository.findBySku("KB-01").orElseThrow();
    int rows = repository.changePrices("keyboards", new BigDecimal("1.10"));
    System.out.println("1. bulk update  " + rows + " rows");
    keyboard.setStock(24);
    System.out.println("2. stock of the loaded KB-01 set to 24");
}

select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.sku=?
binding parameter (1:VARCHAR) <- [KB-01]
update products p1_0 set price=(p1_0.price*cast(? as numeric(10,2))) where p1_0.category=?
binding parameter (1:NUMERIC) <- [1.10]
binding parameter (2:VARCHAR) <- [keyboards]
1. bulk update  4 rows
2. stock of the loaded KB-01 set to 24
update products set category=?,featured=?,name=?,price=?,sku=?,status=?,stock=? where id=?
binding parameter (1:VARCHAR) <- [keyboards]
binding parameter (2:BOOLEAN) <- [true]
binding parameter (3:VARCHAR) <- [Mechanical keyboard]
binding parameter (4:NUMERIC) <- [89.90]
binding parameter (5:VARCHAR) <- [KB-01]
binding parameter (6:ENUM) <- [ACTIVE]
binding parameter (7:INTEGER) <- [24]
binding parameter (8:BIGINT) <- [1]
>>> findByCategory("keyboards") after the transaction
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.category=?
binding parameter (1:VARCHAR) <- [keyboards]
KB-01  Mechanical keyboard                 89.90   24  keyboards   ACTIVE       true
KB-02  Compact keyboard                    64.90   12  keyboards   ACTIVE       null
KB-03  Wireless keyboard                   50.05    0  keyboards   DISCONTINUED false
KB-04  Ergonomic keyboard                 141.90    7  keyboards   ACTIVE       null
(4 rows)

    @Modifying
    @Modifying(clearAutomatically = true) 
    @Query("update Product p set p.price = p.price * :factor where p.category = :category")
    int changePrices(String category, BigDecimal factor);

select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.sku=?
binding parameter (1:VARCHAR) <- [KB-01]
1. loaded       KB-01 price 89.90
update products p1_0 set price=(p1_0.price*cast(? as numeric(10,2))) where p1_0.category=?
binding parameter (1:NUMERIC) <- [1.10]
binding parameter (2:VARCHAR) <- [keyboards]
2. bulk update  4 rows
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.id=?
binding parameter (1:BIGINT) <- [1]
3. findById     KB-01 price 98.89, same object: false
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.sku=?
binding parameter (1:VARCHAR) <- [KB-01]
4. findBySku    KB-01 price 98.89, same object: false
5. old variable KB-01 price 89.90

1. loaded KB-01, stock set to 0 in memory
update products set category=?,featured=?,name=?,price=?,sku=?,status=?,stock=? where id=?
...
binding parameter (7:INTEGER) <- [0]
binding parameter (8:BIGINT) <- [1]
update products p1_0 set price=(p1_0.price*cast(? as numeric(10,2))) where p1_0.category=?
binding parameter (1:NUMERIC) <- [1.10]
binding parameter (2:VARCHAR) <- [keyboards]
2. bulk update  4 rows
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.sku=?
binding parameter (1:VARCHAR) <- [KB-01]
3. findBySku    KB-01 price 98.89, stock 0

public interface ProductRepository extends JpaRepository<Product, Long> {
 
    long deleteByCategory(String category); 
 
    @Modifying
    @Query("delete from Product p where p.category = :category") 
    int deleteAllInCategory(String category); 
}

>>> repository.deleteByCategory("audio") without a transaction
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.category=?
binding parameter (1:VARCHAR) <- [audio]
!!! org.springframework.dao.InvalidDataAccessApiUsageException: No EntityManager with actual transaction available for current thread - cannot reliably process 'remove' call
!!! caused by jakarta.persistence.TransactionRequiredException: No EntityManager with actual transaction available for current thread - cannot reliably process 'remove' call
>>> repository.deleteAllInCategory("audio") without a transaction
!!! org.springframework.dao.InvalidDataAccessApiUsageException: No active transaction for update or delete query
!!! caused by jakarta.persistence.TransactionRequiredException: No active transaction for update or delete query

    @PreRemove
    void printRemoval() { 
        System.out.println("@PreRemove " + sku); 
    } 

>>> service.deleteCategoryDerived("audio")
select p1_0.id,p1_0.category,p1_0.featured,p1_0.name,p1_0.price,p1_0.sku,p1_0.status,p1_0.stock from products p1_0 where p1_0.category=?
binding parameter (1:VARCHAR) <- [audio]
@PreRemove AU-01
@PreRemove AU-02
@PreRemove AU-03
delete from products where id=?
binding parameter (1:BIGINT) <- [11]
delete from products where id=?
binding parameter (1:BIGINT) <- [12]
delete from products where id=?
binding parameter (1:BIGINT) <- [13]
result 3 (Long)
>>> service.deleteCategoryInBulk("mice")
delete from products p1_0 where p1_0.category=?
binding parameter (1:VARCHAR) <- [mice]
result 3 (Integer)