DEV Community

Li
Li

Posted on

Beyond CRUD: How easy-query Brings OLAP Superpowers to Your Java ORM

#database #java #showdev #sql

Most Java ORMs are built for CRUD. The moment you need window functions, conditional aggregation, or Top-N-per-group queries, you're dropped into raw SQL strings — losing type safety, IDE support, and cross-database portability in one fell swoop.

Easy-Query takes a different approach. It's a Java/Kotlin ORM that treats analytical queries as first-class citizens, with a type-safe, chainable API that covers window functions, CASE WHEN, string aggregation, partition-based filtering, and more — all while generating correct SQL across 12+ database dialects.

This article walks through easy-query's OLAP capabilities with real code examples and the SQL they produce.

Window Functions: Full API, Not String Concatenation

Most ORMs force you to write window functions as raw SQL. Easy-query provides a complete, type-safe API.

ROW_NUMBER / RANK / DENSE_RANK 

entityQuery.queryable(BlogEntity.class)
    .select(b -> Select.PART.of(
        b,
        b.expression().rowNumberOver()
            .partitionBy(b.title())
            .orderBy(b.createTime()),
        b.expression().rankOver()
            .partitionBy(b.title())
            .orderBy(b.createTime()),
        b.expression().denseRankOver()
            .partitionBy(b.title())
            .orderBy(b.createTime())
    )).toList();
Enter fullscreen mode Exit fullscreen mode

Generated SQL:

SELECT t.*,
  (ROW_NUMBER() OVER (PARTITION BY t.title ORDER BY t.create_time ASC)),
  (RANK() OVER (PARTITION BY t.title ORDER BY t.create_time ASC)),
  (DENSE_RANK() OVER (PARTITION BY t.title ORDER BY t.create_time ASC))
FROM t_blog t
Enter fullscreen mode Exit fullscreen mode

LAG / LEAD / FIRST_VALUE / LAST_VALUE / NTH_VALUE 

entityQuery.queryable(SysBankCard.class)
    .select(card -> Select.DRAFT.of(
        card.type(),
        card.type().offset(o -> {
            o.partitionBy(card.bankId());
            o.orderBy(card.type());
        }).prev(1),                          // LAG(type, 1)
        card.openTime().offset(o -> {
            o.orderBy(card.type());
        }).next(1),                          // LEAD(open_time, 1)
        card.openTime().offset(o -> {
            o.partitionBy(card.bankId());
            o.orderBy(card.type());
        }).firstValue(),                     // FIRST_VALUE(open_time)
        card.openTime().offset(o -> {
            o.partitionBy(card.bankId());
            o.orderBy(card.type());
        }).nthValue(2)                       // NTH_VALUE(open_time, 2)
    )).toList();
Enter fullscreen mode Exit fullscreen mode

Every part of this — partitionBy, orderBy, column references — is compile-time checked. Misspell a column name and your IDE catches it immediately.

Aggregate Window Functions (SUM / AVG / COUNT OVER) 

entityQuery.queryable(BlogEntity.class)
    .select(b -> Select.PART.of(
        b,
        b.expression().sumOver(b.star())
            .partitionBy(b.title())
            .orderBy(b.createTime())
    )).toList();
Enter fullscreen mode Exit fullscreen mode 
SELECT t.*,
  (SUM(t.star) OVER (PARTITION BY t.title ORDER BY t.create_time ASC))
FROM t_blog t
Enter fullscreen mode Exit fullscreen mode

This gives you running totals, moving averages, and cumulative counts with zero raw SQL.

Filtering on Window Results: The partitionBy Wrapper

A common pain point: you can't use window function results in WHERE directly — SQL requires wrapping in a subquery. Easy-query handles this automatically.

entityQuery.queryable(BlogEntity.class)
    .where(b -> b.createTime().gt(someDate))
    .select(b -> Select.PART.of(
        b,
        b.expression().countOver(b.star()).partitionBy(b.title()),
        b.expression().rowNumberOver().partitionBy(b.title()).orderBy(b.createTime())
    ))
    // Now you can filter on window results:
    .where(partition -> {
        partition.partColumn1().lt(10L);   // countOver result
        partition.partColumn2().eq(1L);    // rowNumber result
    })
    .toList();
Enter fullscreen mode Exit fullscreen mode 
SELECT *
FROM (
  SELECT t.*,
    (COUNT(t.star) OVER (PARTITION BY t.title)) AS __part__column1,
    (ROW_NUMBER() OVER (PARTITION BY t.title ORDER BY t.create_time ASC)) AS __part__column2
  FROM t_blog t WHERE t.create_time > ?
) t1
WHERE t1.__part__column1 < ? AND t1.__part__column2 = ?
Enter fullscreen mode Exit fullscreen mode

Easy-query automatically wraps the window query in a derived table so you can filter on the results. You never have to think about this.

Top-N Per Group: One Line Instead of Twenty

Getting the "first item per group" is one of the most common analytical patterns — and one of the most tedious to write in SQL.

.first() — Get the First Row Per Group 

entityQuery.queryable(SysUser.class)
    .where(user ->
        user.bankCards()
            .orderBy(card -> card.openTime().asc())
            .first()
            .bank().name().eq("ICBC")
    ).toList();
Enter fullscreen mode Exit fullscreen mode 
SELECT t.* FROM t_sys_user t
LEFT JOIN (
  SELECT t1.*,
    (ROW_NUMBER() OVER (PARTITION BY t1.uid ORDER BY t1.open_time ASC)) AS __row__
  FROM t_bank_card t1
) t3 ON (t3.uid = t.id AND t3.__row__ = 1)
INNER JOIN t_bank t4 ON t4.id = t3.bank_id
WHERE t4.name = ?
Enter fullscreen mode Exit fullscreen mode

One line of Java. Easy-query generates the ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) pattern, wraps it in a derived table, and filters for __row__ = 1 — all automatically.

.elements(from, to) — Get Top-N Per Group 

user.bankCards()
    .orderBy(card -> card.openTime().asc())
    .elements(0, 2)
    .joining(card -> card.type(), ",")
Enter fullscreen mode Exit fullscreen mode

This takes the first 3 cards (indices 0, 1, 2) per user, then aggregates their types with GROUP_CONCAT. The generated SQL uses ROW_NUMBER with a range filter under the hood.

Conditional Aggregation: .count().filter()

SQL's COUNT(CASE WHEN ... THEN 1 ELSE NULL END) pattern is verbose. Easy-query wraps it cleanly:

entityQuery.queryable(SysUser.class)
    .select(user -> Select.DRAFT.of(
        user.id().count()
            .filter(() -> user.address().eq("Hangzhou")),
        user.id().count()
            .filter(() -> user.address().eq("Beijing")),
        user.age().avg()
            .filter(() -> user.address().eq("Beijing"))
    )).toList();
Enter fullscreen mode Exit fullscreen mode 
SELECT
  COUNT((CASE WHEN t.address = 'Hangzhou' THEN 1 ELSE NULL END)),
  COUNT((CASE WHEN t.address = 'Beijing' THEN 1 ELSE NULL END)),
  AVG((CASE WHEN t.address = 'Beijing' THEN t.age ELSE NULL END))
FROM t_sys_user t
Enter fullscreen mode Exit fullscreen mode

Three conditional aggregations, zero raw SQL, full type safety.

CASE WHEN Builder

For more complex conditional logic, easy-query provides a full CASE WHEN builder:

entityQuery.queryable(BlogEntity.class)
    .groupBy(b -> GroupKeys.of(b.title()))
    .select(group -> Select.DRAFT.of(
        group.key1(),
        group.expression()
            .caseWhen(() -> group.groupTable().score().gt(BigDecimal.ZERO))
            .then(group.groupTable().score())
            .elseEnd(null)
            .sum()
    )).toList();
Enter fullscreen mode Exit fullscreen mode 
SELECT t.title,
  SUM(CASE WHEN t.score > 0 THEN t.score ELSE NULL END)
FROM t_blog t
GROUP BY t.title
Enter fullscreen mode Exit fullscreen mode

The builder supports chaining multiple .caseWhen().then() branches, and the result can be used with any aggregate function (.sum(), .count(), .avg(), etc.) or in WHERE clauses.

String Aggregation: Cross-Database .joining()

MySQL has GROUP_CONCAT, PostgreSQL has STRING_AGG, Oracle has LISTAGG. Easy-query abstracts all of them behind .joining():

entityQuery.queryable(BlogEntity.class)
    .groupBy(b -> GroupKeys.of(b.title()))
    .select(group -> Select.DRAFT.of(
        group.key1(),
        group.groupTable().content().joining(",")
    )).toList();
Enter fullscreen mode Exit fullscreen mode

On MySQL:

SELECT t.title, GROUP_CONCAT(t.content SEPARATOR ',') FROM t_blog t GROUP BY t.title
Enter fullscreen mode Exit fullscreen mode

On PostgreSQL:

SELECT t."title", STRING_AGG((t."content")::TEXT, ',') FROM "t_blog" t GROUP BY t."title"
Enter fullscreen mode Exit fullscreen mode

Same Java code, correct SQL for each database. It also supports DISTINCT and ORDER BY:

bank.bankCards()
    .distinct()
    .orderBy(card -> card.openTime().desc())
    .joining(card -> card.type(), ",")
Enter fullscreen mode Exit fullscreen mode 
GROUP_CONCAT(DISTINCT t1.type ORDER BY t1.open_time DESC SEPARATOR ',')
Enter fullscreen mode Exit fullscreen mode

Implicit Collection Aggregation

This is where easy-query's "implicit" philosophy really shines. On a OneToMany/ManyToMany navigation, you can call aggregation methods directly — no manual subquery needed:

entityQuery.queryable(Company.class)
    .where(company -> {
        // EXISTS subquery — auto-generated
        company.users().any(u -> u.name().like("John"));

        // COUNT subquery
        company.users()
            .where(u -> u.age().gt(30))
            .count().ge(5L);

        // MAX subquery
        company.users()
            .max(u -> u.salary())
            .gt(100000);
    }).toList();
Enter fullscreen mode Exit fullscreen mode

Each of these generates the appropriate subquery:

WHERE
  EXISTS (SELECT 1 FROM t_user t1 WHERE t1.company_id = t.id AND t1.name LIKE ?)
  AND (SELECT COUNT(*) FROM t_user t1 WHERE t1.company_id = t.id AND t1.age > ?) >= ?
  AND (SELECT MAX(t1.salary) FROM t_user t1 WHERE t1.company_id = t.id) > ?
Enter fullscreen mode Exit fullscreen mode

You think in objects. Easy-query thinks in SQL.

subQueryToGroupJoin: The N+1 Killer for Analytics

When you reference multiple aggregations on the same collection in one query, easy-query can merge them into a single LEFT JOIN (SELECT ... GROUP BY ...) instead of multiple correlated subqueries:

entityQuery.queryable(SysUser.class)
    .subQueryToGroupJoin(user -> user.bankCards())
    .where(user -> {
        user.bankCards()
            .where(card -> card.bank().name().eq("ICBC"))
            .count().ge(2L);
        user.bankCards()
            .none(card -> card.bank().name().eq("CCB"));
    }).toList();
Enter fullscreen mode Exit fullscreen mode

Without subQueryToGroupJoin, this would generate two separate correlated subqueries. With it:

SELECT t.* FROM t_sys_user t
LEFT JOIN (
  SELECT t1.uid AS __group_key1__,
    COUNT((CASE WHEN t3.name = 'ICBC' THEN 1 ELSE NULL END)) AS __count2__,
    (COUNT((CASE WHEN t3.name = 'CCB' THEN 1 ELSE NULL END)) <= 0) AS __none3__
  FROM t_bank_card t1
  INNER JOIN t_bank t3 ON t3.id = t1.bank_id
  GROUP BY t1.uid
) t2 ON t2.__group_key1__ = t.id
WHERE IFNULL(t2.__count2__, 0) >= 2
  AND IFNULL(t2.__none3__, true) = true
Enter fullscreen mode Exit fullscreen mode

One GROUP BY subquery replaces multiple correlated subqueries. This is a significant performance optimization for analytical workloads.

Date Functions 

// Date arithmetic
o.createTime().plus(2, TimeUnitEnum.DAYS)
o.createTime().plusMonths(12)

// Date difference
o.updateTime().duration(o.createTime()).toDays()
o.updateTime().duration(o.createTime()).toHours()

// Formatting
o.createTime().format("yyyy/MM/dd")
Enter fullscreen mode Exit fullscreen mode

All date functions are dialect-aware. plus(2, DAYS) generates DATE_ADD(t.create_time, INTERVAL 2 DAY) on MySQL and t.create_time + INTERVAL '2 DAY' on PostgreSQL.

Raw SQL Escape Hatch

For the rare cases where the built-in API doesn't cover your needs:

group.expression().rawSQLStatement(
    "round(100 - (sum({0}) + sum({1})) * 100.0 / nullif(sum({2}), 0)::numeric, 3)",
    column2, column3, column4
).asBigDecimal().as("result")
Enter fullscreen mode Exit fullscreen mode

{0}, {1}, {2} are placeholder references to type-safe column expressions. You get raw SQL flexibility without losing parameter binding safety.

Supported Databases

All the OLAP features above work across:

Database Dialect Handling
MySQL 5.x / 8.x GROUP_CONCAT, backtick quoting
PostgreSQL STRING_AGG, ::TEXT casting, double-quote quoting
Oracle LISTAGG, Oracle-specific date arithmetic
SQL Server STRING_AGG, [bracket] quoting
SQLite Limited window function support
ClickHouse ClickHouse-specific aggregation
H2 H2 syntax
DM (达梦) DM-specific dialect
KingbaseES KingbaseES dialect
GaussDB GaussDB dialect
DuckDB DuckDB dialect
DB2 DB2 dialect

You write once. Easy-query generates the correct SQL for each database.

The Big Picture

Here's what makes easy-query's OLAP support stand out compared to other Java ORMs:

Capability Easy-Query JPA/Hibernate JOOQ
Window functions (type-safe API) Yes No Yes
LAG/LEAD/FIRST_VALUE Yes No Yes
Conditional aggregation (.filter()) Yes No Yes
String aggregation (cross-DB) Yes No Yes
Top-N per group (one-liner) Yes No No
Implicit collection aggregation Yes No No
subQueryToGroupJoin optimization Yes No No
CASE WHEN builder Yes Criteria API Yes
Zero-config dialect adaptation Yes Partial Yes

Easy-query matches JOOQ's analytical depth while adding higher-level abstractions (.first(), .elements(), .any(), subQueryToGroupJoin) that JOOQ doesn't have.

Getting Started 

<dependency>
    <groupId>com.easy-query</groupId>
    <artifactId>sql-springboot-starter</artifactId>
    <version>3.2.1</version>
</dependency>
<!-- Add your database dialect -->
<dependency>
    <groupId>com.easy-query</groupId>
    <artifactId>sql-mysql</artifactId>
    <version>3.2.1</version>
</dependency>
Enter fullscreen mode Exit fullscreen mode

Documentation: https://www.easy-query.com/easy-query-doc/en

GitHub: https://github.com/xuejmnet/easy-query

Conclusion

If your Java application does anything beyond basic CRUD — dashboards, reports, leaderboards, time-series analysis, or any form of data aggregation — easy-query gives you a type-safe, cross-database API that eliminates the need for raw SQL in analytical queries. The implicit collection aggregation and subQueryToGroupJoin optimization are particularly unique features that no other Java ORM offers.

Stop writing GROUP_CONCAT by hand. Your ORM should handle that.

Top comments (0)