[Advanced Spring Boot] Tối ưu JPA: phát hiện N+1, batch fetching, projection và batch insert

curl -s "https://start.spring.io/starter.zip?type=gradle-project&language=java&bootVersion=4.1.1&javaVersion=21&groupId=com.example&artifactId=demo&name=demo&packageName=com.example.demo&dependencies=web,data-jpa,postgresql,flyway" -o demo.zip

docker run -d --name sba-a5-pg -e POSTGRES_USER=demo -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo -p 5505:5432 postgres:18 -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all

docker exec sba-a5-pg psql -U demo -d demo -c "create extension pg_stat_statements"

spring.application.name=demo
server.port=8205
spring.datasource.url=jdbc:postgresql://localhost:5505/demo
spring.datasource.username=demo
spring.datasource.password=demo
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false
logging.level.org.hibernate.SQL=debug

create table categories (
    id   bigint generated by default as identity primary key,
    name varchar(80) not null unique
);
 
create table products (
    id          bigint generated by default as identity primary key,
    name        varchar(120)   not null,
    sku         varchar(40)    not null unique,
    price       numeric(10, 2) not null,
    category_id bigint         not null references categories (id)
);
create index products_category_id_idx on products (category_id);
 
create table customers (
    id    bigint generated by default as identity primary key,
    email varchar(120) not null unique,
    name  varchar(120) not null
);
 
create table orders (
    id          bigint generated by default as identity primary key,
    customer_id bigint                   not null references customers (id),
    placed_at   timestamp with time zone not null
);
create index orders_customer_id_idx on orders (customer_id);
 
create table order_lines (
    id         bigint generated by default as identity primary key,
    order_id   bigint         not null references orders (id),
    product_id bigint         not null references products (id),
    quantity   integer        not null,
    unit_price numeric(10, 2) not null
);
create index order_lines_order_id_idx on order_lines (order_id);

insert into categories (name)
values ('Keyboards'), ('Mice'), ('Monitors'), ('Headsets'), ('Webcams'), ('Cables'), ('Storage'), ('Chairs');
 
-- 100 products, spread over the 8 categories
insert into products (name, sku, price, category_id)
select (array ['Keyboard', 'Mouse', 'Monitor', 'Headset', 'Webcam', 'Cable', 'SSD', 'Chair'])[1 + (g - 1) % 8]
           || ' ' || lpad(((g - 1) / 8 + 1)::text, 2, '0'),
       'SKU-' || lpad(g::text, 4, '0'),
       9.90 + (g * 37) % 400,
       1 + (g - 1) % 8
from generate_series(1, 100) as g;
 
-- 50 customers
insert into customers (email, name)
select 'customer' || lpad(g::text, 2, '0') || '@example.com', 'Customer ' || lpad(g::text, 2, '0')
from generate_series(1, 50) as g;
 
-- 200 orders
insert into orders (customer_id, placed_at)
select 1 + (g - 1) % 50, timestamptz '2026-01-01 08:00:00+00' + g * interval '7 hours'
from generate_series(1, 200) as g;
 
-- 3 to 5 lines per order
insert into order_lines (order_id, product_id, quantity, unit_price)
select o.id, p.id, 1 + (o.id + n) % 4, p.price
from orders o
         cross join lateral generate_series(1, 3 + o.id % 3) as n
         join products p on p.id = 1 + (o.id * 7 + n * 31) % 100
order by o.id, n;

@Entity
@Table(name = "orders")
public class Order {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "customer_id", nullable = false)
    private Customer customer;
 
    @Column(nullable = false)
    private Instant placedAt;
 
    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<OrderLine> lines = new ArrayList<>();
 
    // constructor, addLine(OrderLine), total() and getters as in the Basics course
}

@Entity
@Table(name = "order_lines")
public class OrderLine {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "order_id", nullable = false)
    private Order order;
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "product_id", nullable = false)
    private Product product;
 
    @Column(nullable = false)
    private int quantity;
 
    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal unitPrice;
 
    // constructor, lineTotal() and getters
}

package com.example.demo.order;
 
import java.math.BigDecimal;
import java.time.Instant;
import java.util.List;
 
public record OrderResponse(Long id, Instant placedAt, List<LineResponse> lines, BigDecimal total) {
 
    public record LineResponse(String product, int quantity, BigDecimal unitPrice) {
 
        static LineResponse from(OrderLine line) {
            return new LineResponse(line.getProduct().getName(), line.getQuantity(), line.getUnitPrice());
        }
    }
 
    public static OrderResponse from(Order order) {
        return new OrderResponse(order.getId(), order.getPlacedAt(),
                order.getLines().stream().map(LineResponse::from).toList(), order.total());
    }
}

@Transactional(readOnly = true)
public PageResponse<OrderResponse> findPage(Pageable pageable) {
    return PageResponse.from(orders.findAll(pageable).map(OrderResponse::from));
}

@GetMapping
public PageResponse<OrderResponse> findPage(@PageableDefault(size = 20, sort = "id") Pageable pageable) {
    return service.findPage(pageable);
}

curl -s 'http://localhost:8205/api/orders?page=3&size=20'

select o1_0.id,o1_0.customer_id,o1_0.placed_at from orders o1_0 order by o1_0.id offset ? rows fetch first ? rows only
select count(o1_0.id) from orders o1_0
select l1_0.order_id,l1_0.id,l1_0.product_id,l1_0.quantity,l1_0.unit_price from order_lines l1_0 where l1_0.order_id=?
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?
select l1_0.order_id,l1_0.id,l1_0.product_id,l1_0.quantity,l1_0.unit_price from order_lines l1_0 where l1_0.order_id=?

select calls, rows, query from pg_stat_statements where query not ilike '%pg_stat_statements%' order by query;

 calls | rows |                                                          query
-------+------+--------------------------------------------------------------------------------------------------------------------------
     1 |    1 | select count(o1_0.id) from orders o1_0
    20 |   81 | select l1_0.order_id,l1_0.id,l1_0.product_id,l1_0.quantity,l1_0.unit_price from order_lines l1_0 where l1_0.order_id=$1
     1 |   20 | select o1_0.id,o1_0.customer_id,o1_0.placed_at from orders o1_0 order by o1_0.id offset $1 rows fetch first $2 rows only
    61 |   61 | select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=$1

DEBUG org.hibernate.statistics : HHH000117: Query: [CRITERIA] select o1_0.id,o1_0.customer_id,o1_0.placed_at from orders o1_0 order by o1_0.id offset ? rows fetch first ? rows only, time: 13ms, rows: 20
DEBUG org.hibernate.statistics : HHH000117: Query: [CRITERIA] select count(o1_0.id) from orders o1_0, time: 0ms, rows: 1

logging.level.org.hibernate.session.metrics=debug 

DEBUG org.hibernate.session.metrics : HHH000401: Logging session metrics:
	29833 ns acquiring 1 JDBC connections
	0 ns releasing 0 JDBC connections
	613495 ns preparing 83 JDBC statements
	24739799 ns executing 83 JDBC statements
	0 ns executing 0 JDBC batches
	0 ns performing 0 second-level cache puts
	0 ns performing 0 second-level cache hits
	0 ns performing 0 second-level cache misses
	0 ns executing 0 flushes (flushing a total of 0 entities and 0 collections)
	72709 ns executing 2 pre-partial-flushes
	71125 ns executing 2 partial-flushes (flushing a total of 0 entities and 0 collections)

package com.example.demo.common;
 
import java.util.ArrayList;
import java.util.List;
 
import org.hibernate.resource.jdbc.spi.StatementInspector;
 
public class SqlStatementCounter implements StatementInspector {
 
    private static final ThreadLocal<List<String>> STATEMENTS = new ThreadLocal<>();
 
    @Override
    public String inspect(String sql) {
        List<String> statements = STATEMENTS.get();
        if (statements != null) {
            statements.add(sql);
        }
        return sql;
    }
 
    public static void start() {
        STATEMENTS.set(new ArrayList<>());
    }
 
    public static List<String> stop() {
        List<String> statements = STATEMENTS.get();
        STATEMENTS.remove();
        return statements == null ? List.of() : statements;
    }
}

package com.example.demo.common;
 
import org.hibernate.cfg.AvailableSettings;
import org.springframework.boot.hibernate.autoconfigure.HibernatePropertiesCustomizer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
 
@Configuration
class JpaConfig {
 
    @Bean
    HibernatePropertiesCustomizer statementCounter() {
        return properties -> properties.put(AvailableSettings.STATEMENT_INSPECTOR, new SqlStatementCounter());
    }
}

package com.example.demo.common;
 
import java.io.IOException;
import java.util.List;
 
import jakarta.servlet.FilterChain;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.web.filter.OncePerRequestFilter;
 
@Component
public class SqlCountingFilter extends OncePerRequestFilter {
 
    public static final String STATEMENTS = SqlCountingFilter.class.getName() + ".statements";
 
    private static final Logger log = LoggerFactory.getLogger(SqlCountingFilter.class);
 
    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain chain)
            throws ServletException, IOException {
        SqlStatementCounter.start();
        try {
            chain.doFilter(request, response);
        } finally {
            List<String> statements = SqlStatementCounter.stop();
            request.setAttribute(STATEMENTS, statements);
            String query = request.getQueryString() == null ? "" : "?" + request.getQueryString();
            log.info("{} {}{} -> {} SQL statements", request.getMethod(), request.getRequestURI(), query,
                    statements.size());
        }
    }
}

INFO c.example.demo.common.SqlCountingFilter  : GET /api/orders?page=3&size=20 -> 83 SQL statements

package com.example.demo.order;
 
import static org.assertj.core.api.Assertions.assertThat;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;
 
import java.util.List;
 
import com.example.demo.common.SqlCountingFilter;
 
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.webmvc.test.autoconfigure.AutoConfigureMockMvc;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.MvcResult;
 
@SpringBootTest
@AutoConfigureMockMvc
class OrderQueryCountTest {
 
    @Autowired
    MockMvc mockMvc;
 
    @Test
    void orderPageRunsAtMostThreeStatements() throws Exception {
        MvcResult result = mockMvc.perform(get("/api/orders").param("page", "3").param("size", "20"))
                .andExpect(status().isOk())
                .andReturn();
 
        @SuppressWarnings("unchecked")
        List<String> statements = (List<String>) result.getRequest().getAttribute(SqlCountingFilter.STATEMENTS);
        assertThat(statements)
                .as("SQL statements for GET /api/orders?page=3&size=20")
                .hasSizeLessThanOrEqualTo(3);
    }
}

tasks.named('test') {
	useJUnitPlatform()
	testLogging { 
		events 'failed'
		exceptionFormat = 'full'
	} 
}

./gradlew test --tests 'com.example.demo.order.OrderQueryCountTest'

OrderQueryCountTest > orderPageRunsAtMostThreeStatements() FAILED
    java.lang.AssertionError: [SQL statements for GET /api/orders?page=3&size=20]
    Expecting size of:
      ["select o1_0.id,o1_0.customer_id,o1_0.placed_at from orders o1_0 order by o1_0.id offset ? rows fetch first ? rows only",
        "select count(o1_0.id) from orders o1_0",
        "select l1_0.order_id,l1_0.id,l1_0.product_id,l1_0.quantity,l1_0.unit_price from order_lines l1_0 where l1_0.order_id=?",
        "select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?",
        ...
        "select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?"]
    to be less than or equal to 3 but was 83
        at com.example.demo.order.OrderQueryCountTest.orderPageRunsAtMostThreeStatements(OrderQueryCountTest.java:35)
 
1 test completed, 1 failed

@Query(value = "select o from Order o left join fetch o.lines l left join fetch l.product",
        countQuery = "select count(o) from Order o")
Page<Order> findPageWithLines(Pageable pageable);

=== join-fetch | load 2.98 | statements 2 (pg calls 2) | rows 82 | best 1.11 ms of 15 | orders 20 lines 81 totalElements 200
   calls   1 rows    1  select count(o1_0.id) from orders o1_0
   calls   1 rows   81  select o1_0.id,o1_0.customer_id,l1_0.order_id,l1_0.id,l1_0.product_id,p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,l1_0.quantity,l1_0.unit_price,o1_0.placed_at from (select o1_0.id,o1_0.customer_id,o1_0.placed_at from orders o1_0 order by o1_0.id offset $1 rows fetch first $2 rows only) o1_0(id,customer_id,placed_at) left join order_lines l1_0 on o1_0.id=l1_0.order_id left join products p1_0 on p1_0.id=l1_0.product_id order by o1_0.id

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    @BatchSize(size = 20) 
    private List<OrderLine> lines = new ArrayList<>();

=== lazy | load 2.32 | statements 64 (pg calls 64) | rows 163 | best 8.52 ms of 15 | orders 20 lines 81 totalElements 200
   calls   1 rows    1  select count(o1_0.id) from orders o1_0
   calls   1 rows   81  select l1_0.order_id,l1_0.id,l1_0.product_id,l1_0.quantity,l1_0.unit_price from order_lines l1_0 where l1_0.order_id = any ($1)
   calls   1 rows   20  select o1_0.id,o1_0.customer_id,o1_0.placed_at from orders o1_0 order by o1_0.id offset $1 rows fetch first $2 rows only
   calls  61 rows   61  select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=$1

select l1_0.order_id,l1_0.id,l1_0.product_id,l1_0.quantity,l1_0.unit_price from order_lines l1_0 where l1_0.order_id = any (?)
binding parameter (1:ARRAY) <- [[61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80]]

spring.jpa.properties.hibernate.default_batch_fetch_size=50 

=== lazy | load 2.83 | statements 5 (pg calls 5) | rows 163 | best 4.12 ms of 15 | orders 20 lines 81 totalElements 200
   calls   1 rows    1  select count(o1_0.id) from orders o1_0
   calls   1 rows   81  select l1_0.order_id,l1_0.id,l1_0.product_id,l1_0.quantity,l1_0.unit_price from order_lines l1_0 where l1_0.order_id = any ($1)
   calls   1 rows   20  select o1_0.id,o1_0.customer_id,o1_0.placed_at from orders o1_0 order by o1_0.id offset $1 rows fetch first $2 rows only
   calls   2 rows   61  select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id = any ($1)

select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id = any (?)
binding parameter (1:ARRAY) <- [[59, 90, 21, 52, 66, 97, 28, 73, 4, 35, 80, 11, 42, 87, 18, 49, 94, 25, 56, 1, 32, 63, 8, 39, 70, 15, 46, 77, 22, 53, 84, 29, 60, 91, 36, 67, 98, 43, 74, 5, 50, 81, 12, 57, 88, 19, 64, 95, 26, 71]]
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id = any (?)
binding parameter (1:ARRAY) <- [[2, 33, 78, 9, 40, 85, 16, 47, 92, 23, 54]]

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    @Fetch(FetchMode.SUBSELECT) 
    private List<OrderLine> lines = new ArrayList<>();

=== lazy | load 2.65 | statements 5 (pg calls 5) | rows 922 | best 5.76 ms of 15 | orders 20 lines 81 totalElements 200
   calls   1 rows    1  select count(o1_0.id) from orders o1_0
   calls   1 rows  801  select l1_0.order_id,l1_0.id,l1_0.product_id,l1_0.quantity,l1_0.unit_price from order_lines l1_0 where l1_0.order_id in (select o1_0.id from orders o1_0)
   calls   1 rows   20  select o1_0.id,o1_0.customer_id,o1_0.placed_at from orders o1_0 order by o1_0.id offset $1 rows fetch first $2 rows only
   calls   2 rows  100  select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id = any ($1)

@Query(value = "select o from Order o left join fetch o.lines l order by l.unitPrice desc, o.id",
        countQuery = "select count(o) from Order o")
Page<Order> findPageOrderedByLinePrice(Pageable pageable);

WARN org.hibernate.orm.query : HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory
select o1_0.id,o1_0.customer_id,l1_0.order_id,l1_0.id,l1_0.product_id,l1_0.quantity,l1_0.unit_price,o1_0.placed_at from orders o1_0 left join order_lines l1_0 on o1_0.id=l1_0.order_id order by l1_0.unit_price desc,o1_0.id

org.springframework.orm.jpa.JpaSystemException: setFirstResult() or setMaxResults() specified with collection fetch join (in-memory pagination was about to be applied, but 'hibernate.query.fail_on_pagination_over_collection_fetch' is enabled)

@Query("select o from Order o left join fetch o.lines l left join fetch l.product p where p.category.name = :category order by o.id")
List<Order> findWithKeyboardLines(String category);

    order 1 lines 1
    order 7 lines 1
    order 8 lines 1
    order 9 lines 1
    order 10 lines 1
    db: order 1 lines 4
    db: order 7 lines 4
    db: order 8 lines 5
    db: order 9 lines 3
    db: order 10 lines 4

@Query(value = "select o.id from Order o", countQuery = "select count(o) from Order o")
Page<Long> findIds(Pageable pageable);
 
@Query("select o from Order o left join fetch o.lines l left join fetch l.product where o.id in :ids")
List<Order> findWithLinesByIdIn(Collection<Long> ids);

@Transactional(readOnly = true)
public PageResponse<OrderResponse> findPage(Pageable pageable) {
    return PageResponse.from(orders.findAll(pageable).map(OrderResponse::from)); 
    Page<Long> ids = orders.findIds(pageable); 
    Map<Long, Order> byId = orders.findWithLinesByIdIn(ids.getContent()).stream() 
            .collect(Collectors.toMap(Order::getId, Function.identity())); 
    return PageResponse.from(ids.map(id -> OrderResponse.from(byId.get(id)))); 
}

=== two-query | load 2.98 | statements 3 (pg calls 3) | rows 102 | best 1.53 ms of 15 | orders 20 lines 81 totalElements 200
   calls   1 rows    1  select count(o1_0.id) from orders o1_0
   calls   1 rows   20  select o1_0.id from orders o1_0 order by o1_0.id offset $1 rows fetch first $2 rows only
   calls   1 rows   81  select o1_0.id,o1_0.customer_id,l1_0.order_id,l1_0.id,l1_0.product_id,p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,l1_0.quantity,l1_0.unit_price,o1_0.placed_at from orders o1_0 left join order_lines l1_0 on o1_0.id=l1_0.order_id left join products p1_0 on p1_0.id=l1_0.product_id where o1_0.id in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)

@Query(value = "select o.id from Order o where exists (select 1 from OrderLine l where l.order = o and l.product.category.name = :category)",
        countQuery = "select count(o) from Order o where exists (select 1 from OrderLine l where l.order = o and l.product.category.name = :category)")
Page<Long> findIdsContainingCategory(String category, Pageable pageable);

    order 1 lines 4
    order 7 lines 4
    order 8 lines 5
    order 9 lines 3
    order 10 lines 4
    totalElements 99 totalPages 20

entityManager.unwrap(SessionImplementor.class).getPersistenceContextInternal().getNumberOfManagedEntities()

select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price
    13 results, first: Product[65, Keyboard 09, 14.90]
    managed entities in the persistence context: 13
	2453625 ns executing 1 flushes (flushing a total of 13 entities and 0 collections)

package com.example.demo.product;
 
import java.math.BigDecimal;
 
public interface ProductSummary {
 
    Long getId();
 
    String getName();
 
    BigDecimal getPrice();
}

List<ProductSummary> findSummariesByCategoryNameOrderByPrice(String category);

select p1_0.id,p1_0.name,p1_0.price from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price
    13 results, first: ProductSummary[65, Keyboard 09, 14.90]
    result class: jdk.proxy2.$Proxy131
    managed entities in the persistence context: 0
	10875 ns executing 1 flushes (flushing a total of 0 entities and 0 collections)

package com.example.demo.product;
 
import org.springframework.beans.factory.annotation.Value;
 
public interface ProductLabel {
 
    @Value("#{target.name + ' (' + target.sku + ')'}")
    String getLabel();
}

select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price
    13 results, first: ProductLabel[Keyboard 09 (SKU-0065)]
    result class: jdk.proxy2.$Proxy132
    managed entities in the persistence context: 13
	200833 ns executing 1 flushes (flushing a total of 13 entities and 0 collections)

package com.example.demo.product;
 
public interface ProductLabelView {
 
    String getName();
 
    String getSku();
 
    default String getLabel() {
        return getName() + " (" + getSku() + ")";
    }
}

select p1_0.name,p1_0.sku from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price
    13 results, first: ProductLabelView[Keyboard 09 (SKU-0065)]
    managed entities in the persistence context: 0

package com.example.demo.product;
 
import java.math.BigDecimal;
 
public record ProductRow(Long id, String name, BigDecimal price) {
}

List<ProductRow> findRowsByCategoryNameOrderByPrice(String category);
 
@Query("select new com.example.demo.product.ProductRow(p.id, p.name, p.price) from Product p where p.category.name = :category order by p.price")
List<ProductRow> findRowsJpql(String category);
 
@Query("select p.id, p.name, p.price from Product p where p.category.name = :category order by p.price")
List<ProductRow> findRowsJpqlWithoutNew(String category);

select p1_0.id,p1_0.name,p1_0.price from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? order by p1_0.price
    13 results, first: ProductRow[id=65, name=Keyboard 09, price=14.90]
    result class: com.example.demo.product.ProductRow
    managed entities in the persistence context: 0

<T> List<T> findByCategoryName(String category, Class<T> type);

package com.example.demo.product;
 
import java.math.BigDecimal;
 
public interface CategoryStats {
 
    String getCategory();
 
    long getProducts();
 
    BigDecimal getAveragePrice();
}

@NativeQuery("""
        select c.name as category, count(*) as products, round(avg(p.price), 2) as averagePrice
        from products p join categories c on c.id = p.category_id
        group by c.name
        order by c.name""")
List<CategoryStats> categoryStats();

    8 results, first: CategoryStats[Cables, 12, 226.57]
    result class: jdk.proxy2.$Proxy135
    managed entities in the persistence context: 0
	3249 ns executing 2 flushes (flushing a total of 0 entities and 0 collections)

package com.example.demo.order;
 
import java.math.BigDecimal;
import java.time.Instant;
 
public record OrderSummary(Long id, Instant placedAt, String customerEmail, long lines, BigDecimal total) {
}

@Query(value = """
        select new com.example.demo.order.OrderSummary(o.id, o.placedAt, c.email, count(l), sum(l.unitPrice * l.quantity))
        from Order o join o.customer c join o.lines l
        group by o.id, o.placedAt, c.email""",
        countQuery = "select count(o) from Order o")
Page<OrderSummary> findSummaries(Pageable pageable);

@GetMapping("/summaries")
public PageResponse<OrderSummary> findSummaries(@PageableDefault(size = 20, sort = "id") Pageable pageable) {
    return service.findSummaries(pageable);
}

select o1_0.id,o1_0.placed_at,c1_0.email,count(l1_0.id),sum((l1_0.unit_price*l1_0.quantity)) from orders o1_0 join customers c1_0 on c1_0.id=o1_0.customer_id join order_lines l1_0 on o1_0.id=l1_0.order_id group by o1_0.id,o1_0.placed_at,c1_0.email order by o1_0.id offset ? rows fetch first ? rows only
select count(o1_0.id) from orders o1_0
INFO c.example.demo.common.SqlCountingFilter  : GET /api/orders/summaries?page=3&size=20 -> 2 SQL statements

{"content":[{"id":61,"placedAt":"2026-01-19T03:00:00Z","customerEmail":"customer11@example.com","lines":4,"total":2193.00},{"id":62,"placedAt":"2026-01-19T10:00:00Z","customerEmail":"customer12@example.com","lines":5,"total":2236.60},

tx.execute(status -> {
    List<Order> batch = new ArrayList<>(2000);
    for (int i = 0; i < 2000; i++) {
        Order order = new Order(entityManager.getReference(Customer.class, 1L + i % 50),
                Instant.parse("2026-09-01T00:00:00Z").plus(i, ChronoUnit.MINUTES));
        for (int n = 1; n <= 4; n++) {
            long productId = 1 + (i * 7L + n * 31L) % 100;
            order.addLine(new OrderLine(entityManager.getReference(Product.class, productId),
                    1 + (i + n) % 4, prices.get(productId)));
        }
        batch.add(order);
    }
    return orderRepository.saveAll(batch);
});

   calls   8000 rows   8000  insert into order_lines (order_id,product_id,quantity,unit_price) values ($1,$2,$3,$4) RETURNING *
   calls   2000 rows   2000  insert into orders (customer_id,placed_at) values ($1,$2) RETURNING *

	1537914 ns preparing 10000 JDBC statements
	1331657082 ns executing 10000 JDBC statements
	0 ns executing 0 JDBC batches

alter table orders alter column id drop identity;
alter table order_lines alter column id drop identity;
 
create sequence orders_seq increment by 50;
create sequence order_lines_seq increment by 50;
 
select setval('orders_seq', (select max(id) from orders));
select setval('order_lines_seq', (select max(id) from order_lines));

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) 
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "orders_seq") 
    @SequenceGenerator(name = "orders_seq", sequenceName = "orders_seq", allocationSize = 50) 
    private Long id;

   calls   8000 rows   8000  insert into order_lines (order_id,product_id,quantity,unit_price,id) values ($1,$2,$3,$4,$5)
   calls   2000 rows   2000  insert into orders (customer_id,placed_at,id) values ($1,$2,$3)
   calls    200 rows    200  select nextval('orders_seq')

spring.jpa.properties.hibernate.jdbc.batch_size=50 

	535605 ns preparing 4200 JDBC statements
	23395792 ns executing 200 JDBC statements
	695271525 ns executing 4000 JDBC batches

spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true 

	72250 ns preparing 202 JDBC statements
	21294954 ns executing 200 JDBC statements
	110352048 ns executing 200 JDBC batches

spring.datasource.url=jdbc:postgresql://localhost:5505/demo 
spring.datasource.url=jdbc:postgresql://localhost:5505/demo?reWriteBatchedInserts=true 

   calls    200 rows    200  select nextval('orders_seq')
   calls    160 rows   2560  insert into order_lines (order_id,product_id,quantity,unit_price,id) values ($1,$2,$3,$4,$5),($6,$7,$8,$9,$10), ... ,($76,$77,$78,$79,$80)
   calls    160 rows   5120  insert into order_lines (order_id,product_id,quantity,unit_price,id) values ($1,$2,$3,$4,$5),($6,$7,$8,$9,$10), ... ,($156,$157,$158,$159,$160)
   calls    160 rows    320  insert into order_lines (order_id,product_id,quantity,unit_price,id) values ($1,$2,$3,$4,$5),($6,$7,$8,$9,$10)
   calls     40 rows    640  insert into orders (customer_id,placed_at,id) values ($1,$2,$3),($4,$5,$6), ... ,($46,$47,$48)
   calls     40 rows   1280  insert into orders (customer_id,placed_at,id) values ($1,$2,$3),($4,$5,$6), ... ,($94,$95,$96)
   calls     40 rows     80  insert into orders (customer_id,placed_at,id) values ($1,$2,$3),($4,$5,$6)

// newOrder(i, prices) builds one order and its four lines, as in the loop above
for (int i = 0; i < orderCount; i++) {
    entityManager.persist(newOrder(i, prices));
    if ((i + 1) % 50 == 0) {
        entityManager.flush();
        entityManager.clear();
    }
}

List<Long> orderIds = jdbc.sql("select nextval('orders_seq') from generate_series(1, :n)")
        .param("n", 2000).query(Long.class).list();
// order_lines_seq the same way, then build the CSV text for both tables
CopyManager copy = DataSourceUtils.getConnection(dataSource).unwrap(PGConnection.class).getCopyAPI();
copy.copyIn("copy orders (id, customer_id, placed_at) from stdin (format csv)", new StringReader(orderRows));
copy.copyIn("copy order_lines (id, order_id, product_id, quantity, unit_price) from stdin (format csv)", new StringReader(lineRows));