ORM vs JDBC Reporting Queries With Hibernate and jOOQ
Compare Hibernate, Spring JdbcClient and jOOQ for reporting queries with grouping, windows and large result sets, and pick one by the shape of the query.

A reporting query is the case where the ORM argument gets loud. A dashboard that sums revenue by region and month, ranks customers by spend and compares each month with the previous one is not a graph of entities. It is a result set that the database computed, and what you want from the Java side is the shortest path from SQL to a typed row. This post builds one such report three ways, with Hibernate, Spring's JdbcClient and jOOQ, so the differences are visible rather than asserted. If you only need the performance side of Hibernate reads, using batch fetching to tame N plus 1 covers the entity case.
The report
The query is deliberately report-shaped: a join, a group, an aggregate and a window function.
select c.region,
date_trunc('month', o.placed_at) as month,
sum(o.total) as revenue,
rank() over (partition by date_trunc('month', o.placed_at)
order by sum(o.total) desc) as region_rank
from orders o
join customer c on c.id = o.customer_id
where o.placed_at >= :from and o.placed_at < :to
group by c.region, date_trunc('month', o.placed_at)
order by month, region_rankThe expected row is (region, month, revenue, regionRank). Everything else in this post is how that row gets from the database to a Java record:
package dev.javahacks.report;
import java.math.BigDecimal;
import java.time.LocalDate;
public record RevenueRow(String region, LocalDate month, BigDecimal revenue, int regionRank) {}Hibernate: a native query and a DTO
Hibernate 6 and 7 can run this. There is no JPQL construct for rank() over (...) that Hibernate guarantees across databases, although HQL in Hibernate 6 does support window functions; the point of this example is the common path, a native query with the result mapped by constructor.
package dev.javahacks.report;
import jakarta.persistence.EntityManager;
import jakarta.persistence.Tuple;
import org.springframework.stereotype.Repository;
import java.time.LocalDate;
import java.util.List;
@Repository
public class HibernateRevenueReport {
private final EntityManager em;
public HibernateRevenueReport(EntityManager em) {
this.em = em;
}
public List<RevenueRow> between(LocalDate from, LocalDate to) {
@SuppressWarnings("unchecked")
List<Tuple> rows = em.createNativeQuery("""
select c.region as region,
cast(date_trunc('month', o.placed_at) as date) as month,
sum(o.total) as revenue,
rank() over (partition by date_trunc('month', o.placed_at)
order by sum(o.total) desc) as region_rank
from orders o join customer c on c.id = o.customer_id
where o.placed_at >= :from and o.placed_at < :to
group by c.region, date_trunc('month', o.placed_at)
order by month, region_rank
""", Tuple.class)
.setParameter("from", from)
.setParameter("to", to)
.getResultList();
return rows.stream()
.map(t -> new RevenueRow(
t.get("region", String.class),
t.get("month", java.sql.Date.class).toLocalDate(),
t.get("revenue", java.math.BigDecimal.class),
((Number) t.get("region_rank")).intValue()))
.toList();
}
}This works, and notice what Hibernate contributed: a connection and a parameter binder. It did not generate SQL, it did not manage identity because there are no entities, and it added a Tuple conversion step in which a type mismatch, say rank() coming back as Long on one database and Integer on another, shows up at runtime as a ClassCastException. The cast on region_rank above is there for that reason. The query string is also an opaque text block: no compiler help, no refactoring when a column is renamed.
Spring JdbcClient: the shortest path
JdbcClient is the Spring Framework 6.1 fluent wrapper over JdbcTemplate. It binds named parameters and maps a row through a lambda or straight to a record.
package dev.javahacks.report;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Repository;
import java.time.LocalDate;
import java.util.List;
@Repository
public class JdbcRevenueReport {
private static final String SQL = """
select c.region,
cast(date_trunc('month', o.placed_at) as date) as month,
sum(o.total) as revenue,
rank() over (partition by date_trunc('month', o.placed_at)
order by sum(o.total) desc) as region_rank
from orders o join customer c on c.id = o.customer_id
where o.placed_at >= :from and o.placed_at < :to
group by c.region, date_trunc('month', o.placed_at)
order by month, region_rank
""";
private final JdbcClient jdbc;
public JdbcRevenueReport(JdbcClient jdbc) {
this.jdbc = jdbc;
}
public List<RevenueRow> between(LocalDate from, LocalDate to) {
return jdbc.sql(SQL)
.param("from", from)
.param("to", to)
.query((rs, n) -> new RevenueRow(
rs.getString("region"),
rs.getObject("month", LocalDate.class),
rs.getBigDecimal("revenue"),
rs.getInt("region_rank")))
.list();
}
}Compared with the Hibernate version this is both shorter and more honest: you wrote SQL, so the code says SQL. The JDBC driver converts types through the standard ResultSet getters, so the rank cast problem goes away. The cost is that nothing checks the column names. If someone renames placed_at, you learn at the first request, not at compile time. For a service with five report queries that is acceptable and JdbcClient is where to start.
jOOQ: when there are many queries
jOOQ generates Java classes from your schema and lets you write SQL as Java expressions. The compiler then knows every column.
package dev.javahacks.report;
import org.jooq.DSLContext;
import org.jooq.Field;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
import static dev.javahacks.jooq.Tables.CUSTOMER;
import static dev.javahacks.jooq.Tables.ORDERS;
import static org.jooq.impl.DSL.*;
@Repository
public class JooqRevenueReport {
private final DSLContext dsl;
public JooqRevenueReport(DSLContext dsl) {
this.dsl = dsl;
}
public List<RevenueRow> between(LocalDate from, LocalDate to) {
Field<LocalDate> month = trunc(ORDERS.PLACED_AT, org.jooq.DatePart.MONTH).as("month");
Field<BigDecimal> revenue = sum(ORDERS.TOTAL).as("revenue");
Field<Integer> rank = rank().over(
partitionBy(trunc(ORDERS.PLACED_AT, org.jooq.DatePart.MONTH))
.orderBy(sum(ORDERS.TOTAL).desc())).as("region_rank");
return dsl.select(CUSTOMER.REGION, month, revenue, rank)
.from(ORDERS)
.join(CUSTOMER).on(CUSTOMER.ID.eq(ORDERS.CUSTOMER_ID))
.where(ORDERS.PLACED_AT.ge(from.atStartOfDay()).and(ORDERS.PLACED_AT.lt(to.atStartOfDay())))
.groupBy(CUSTOMER.REGION, trunc(ORDERS.PLACED_AT, org.jooq.DatePart.MONTH))
.orderBy(month, rank)
.fetchInto(RevenueRow.class);
}
}The generated Tables and ORDERS.PLACED_AT types are the payoff. A rename in the schema regenerates the classes and the build fails where the query breaks. The window function is a first class expression instead of a string. The cost is the code generation step in the build and a licence question: jOOQ's open source edition covers open source databases only, and commercial databases such as Oracle and SQL Server need a commercial licence, which is stated on the jOOQ licensing page. Check that before choosing it for a project on a commercial database.
Comparing them
| Hibernate native | JdbcClient | jOOQ | |
|---|---|---|---|
| Who sees the SQL | You, in a string | You, in a string | The DSL builds it |
| Compile time column check | No | No | Yes, after generation |
| Result mapping | Tuple and casts | Lambda or record | fetchInto a record |
| Build step | None | None | Code generation |
| Fits | Occasional report next to JPA code | A handful of statements | Dozens of queries, many joins |
| Window functions | String, or HQL on newer versions | String | Typed DSL |
Testing a report
A report query is easy to get subtly wrong: a join that duplicates rows, a window function partitioned on the wrong column, a time zone that shifts a month boundary. The test that catches these needs a real database with known rows, so use Testcontainers and assert the exact output. The same test body works against all three implementations, which is also how you compare them honestly.
@SpringBootTest
@Testcontainers
class RevenueReportTest {
@Container
@ServiceConnection
static PostgreSQLContainer<?> db = new PostgreSQLContainer<>("postgres:17");
@Autowired JdbcClient jdbc;
@Autowired JdbcRevenueReport report;
@BeforeEach
void seed() {
jdbc.sql("insert into customer (id, region) values (1, 'APAC'), (2, 'EMEA')").update();
jdbc.sql("""
insert into orders (customer_id, placed_at, total) values
(1, '2026-10-05', 100.00), (1, '2026-10-20', 50.00), (2, '2026-10-07', 400.00)
""").update();
}
@Test
void ranksRegionsByRevenueWithinTheMonth() {
var rows = report.between(LocalDate.of(2026, 10, 1), LocalDate.of(2026, 11, 1));
assertThat(rows).extracting(RevenueRow::region, RevenueRow::regionRank)
.containsExactly(tuple("EMEA", 1), tuple("APAC", 2));
assertThat(rows.get(1).revenue()).isEqualByComparingTo("150.00");
}
}isEqualByComparingTo matters here: new BigDecimal("150.00") and new BigDecimal("150.0") are unequal under equals, and a database may return either scale.
Why reports resist an ORM
It helps to name what an ORM is for before judging it on this job. Hibernate gives you three things: an identity map, so loading the same row twice yields the same object; change tracking, so modifying a managed object writes an update at flush; and navigation, so order.getCustomer() loads a related row on demand. A reporting query uses none of them. Every row is a one-off aggregate with no identity, nothing is modified and nothing is navigated. What remains is parameter binding and result conversion, which every library on this list does equally well.
There is also a correctness argument that has nothing to do with speed. A report is usually defined by its SQL, reviewed by someone who knows the data, and sometimes copied into a SQL console to explain a number to a finance colleague. A query kept as visible SQL, in a text block or in a file on the classpath, can travel that whole path unchanged. A query assembled from a Criteria builder cannot be pasted anywhere.
The balanced view is that Hibernate is not a bad tool here, merely an unnecessary one, and that the dependency you already have is a perfectly fine place to run one native query. It stops being fine when the third and fourth report arrive and each needs its own Tuple conversion and its own date cast.
Pagination and ordering for reports
Reports that feed a screen are paged, and the paging strategy matters more than the library. Offset paging (offset 20000 limit 20) makes the database compute and discard twenty thousand rows on every page, so deep pages get slower. Keyset paging remembers the last row's sort key and asks for rows after it, which stays flat regardless of depth. With JdbcClient it is one extra predicate:
List<RevenueRow> after(LocalDate month, int rankAfter, int limit) {
return jdbc.sql(SQL_PAGED)
.param("month", month)
.param("rankAfter", rankAfter)
.param("limit", limit)
.query(RevenueRow.class)
.list();
}The ordering must be total, meaning the sort key identifies a row uniquely, or two rows tying on rank will be skipped or repeated across pages. Add the primary key as the last sort column to guarantee it.
Large result sets
A report that returns a million rows is a different problem, and the choice of library matters less than how you read. With JDBC, stream instead of collecting: JdbcClient has .query(...).stream(), which must be consumed inside a transaction and closed, and the driver must be told to stream. On PostgreSQL that means a transaction, autoCommit off and a fetch size, otherwise the driver buffers the whole result in memory:
@Transactional(readOnly = true)
public void exportAll(Consumer<RevenueRow> sink) {
try (var rows = jdbc.sql(SQL).param("from", FROM).param("to", TO)
.query(RevenueRow.class).stream()) {
rows.forEach(sink);
}
}Hibernate offers Query.getResultStream() for the same job, and for a huge export [StatelessSession](/library/hibernate-statelesssession-use-bulk-work) skips the persistence context. Whatever you pick, set a fetch size that your driver honours and read inside one read only transaction.
Sharing a transaction
None of this means giving up Hibernate. A Spring Boot application runs JPA and JDBC against one DataSource, and JpaTransactionManager exposes the JPA connection to JdbcClient calls in the same transaction. That is what lets a write through JPA and a read through SQL see the same data in one unit of work:
@Transactional
public void closeMonth(YearMonth month) {
invoices.markClosed(month); // JPA repository
var rows = revenue.between(month.atDay(1), month.plusMonths(1).atDay(1)); // JdbcClient
archive.store(rows);
}One caveat: Hibernate buffers writes in its persistence context until flush. A JDBC read in the same transaction will not see an unflushed JPA change unless you call entityManager.flush() first. Reports that must include the work done a line earlier need that flush.
A recommendation
Keep JPA for the write model and for entity shaped reads. For reports, start with JdbcClient: it is a library you already have, it keeps SQL as SQL and it is trivial to review. Move to jOOQ when the count of queries passes the point where a column rename is a real risk, which in practice is a few dozen. Use a Hibernate native query only for the occasional report in a service that is otherwise all JPA and where adding a second library is not worth it.
When this does not apply
If a report is a view over entity data that your users also edit, one screen listing orders with a customer name, a Spring Data projection (projections to fetch less) is simpler than any of the above. If the report runs against an analytics database or a data warehouse, use whatever that engine's driver and tooling prefer; the ORM question does not arise. For the SQL features used here, the PostgreSQL manual on window functions is the primary source.


