Forem: Li

easy-query: A Type-Safe Java ORM That Actually Handles the Hard Parts

Li — Fri, 20 Mar 2026 18:53:42 +0000

There's a class of problems every Java backend developer eventually hits: your ORM handles simple CRUD just fine, but the moment you need sharding, window functions, conditional aggregation, multi-tenancy filtering, or differential updates, you're either writing raw SQL or fighting the framework.

Easy-Query is a Java/Kotlin ORM designed from the ground up to handle these "hard parts" with a type-safe, chainable API. It supports 12+ databases, requires zero XML configuration, and provides capabilities that go well beyond what JPA/Hibernate or JOOQ offer out of the box.

This article is a comprehensive tour of what easy-query can do.

Setup
Entity Definition & Proxy Generation
Querying: The Chainable DSL
Implicit Relationships
Projections & VO Mapping
CRUD Operations
Change Tracking & Differential Updates
OLAP & Analytics
Logical Delete
Interceptors & Multi-Tenancy
Column Encryption
Dynamic Table Names
Sharding
Pagination
Code First
Supported Databases

Setup

Spring Boot:

<dependency>
    <groupId>com.easy-query</groupId>
    <artifactId>sql-springboot-starter</artifactId>
    <version>3.2.1</version>
</dependency>
<dependency>
    <groupId>com.easy-query</groupId>
    <artifactId>sql-mysql</artifactId>
    <version>3.2.1</version>
</dependency>

easy-query:
  enable: true
  database: mysql
  print-sql: true

That's it. No XML mapping files, no persistence.xml, no session factory boilerplate.

Entity Definition & Proxy Generation

Entities are plain Java classes annotated with @Table and @EntityProxy:

@Data
@Table("t_user")
@EntityProxy
public class SysUser implements ProxyEntityAvailable<SysUser, SysUserProxy> {

    @Column(primaryKey = true)
    private String id;
    private String name;
    private Integer age;
    private String phone;
    private LocalDateTime createTime;

    @Navigate(value = RelationTypeEnum.ManyToOne, selfProperty = "companyId")
    private Company company;

    @Navigate(value = RelationTypeEnum.OneToMany, targetProperty = "userId")
    private List<SysBankCard> bankCards;

    @Navigate(value = RelationTypeEnum.ManyToMany,
            mappingClass = UserRole.class,
            selfMappingProperty = "userId",
            targetMappingProperty = "roleId")
    private List<SysRole> roles;
}

At compile time, APT generates a SysUserProxy class that provides type-safe column accessors (user.name(), user.age(), etc.). You never write string-based column references.

Relationship Types

Annotation	Behavior
`@Navigate(ManyToOne)`	Implicit JOIN when you access `user.company().name()`
`@Navigate(OneToMany)`	Implicit subquery: `user.bankCards().any(...)`
`@Navigate(ManyToMany)`	Implicit middle table handling
`@Navigate(OneToOne)`	Implicit JOIN, similar to ManyToOne

You declare relationships once. Easy-query decides whether to use JOINs or subqueries based on the relationship type.

Querying: The Chainable DSL

Basic Query

SysUser user = easyEntityQuery.queryable(SysUser.class)
    .where(u -> u.name().like("John"))
    .orderBy(u -> u.createTime().desc())
    .firstOrNull();

SELECT * FROM t_user t WHERE t.name LIKE '%John%' ORDER BY t.create_time DESC LIMIT 1

Multi-Table JOIN

List<SysUser> users = easyEntityQuery.queryable(SysUser.class)
    .leftJoin(Company.class, (u, c) -> u.companyId().eq(c.id()))
    .where((u, c) -> c.name().like("Acme"))
    .toList();

JOIN with Subquery

Any queryable can be used as a JOIN target — including subqueries and CTEs:

var subQuery = easyEntityQuery.queryable(SysBankCard.class)
    .groupBy(card -> GroupKeys.of(card.bankId()))
    .select(group -> Select.DRAFT.of(
        group.key1(),
        group.count()
    ));

List<Draft2<String, Long>> result = easyEntityQuery.queryable(SysBank.class)
    .leftJoin(subQuery, (bank, cardGroup) -> bank.id().eq(cardGroup.value1()))
    .select((bank, cardGroup) -> Select.DRAFT.of(
        bank.name(),
        cardGroup.value2()
    )).toList();

SELECT t.name, t1.value2
FROM t_bank t
LEFT JOIN (
  SELECT t2.bank_id AS value1, COUNT(*) AS value2
  FROM t_bank_card t2 GROUP BY t2.bank_id
) t1 ON t.id = t1.value1

Implicit Relationships

This is easy-query's most distinctive feature. When you access a navigation property in a query, the framework automatically generates the appropriate SQL pattern:

ManyToOne / OneToOne → Implicit JOIN

easyEntityQuery.queryable(SysUser.class)
    .where(user -> user.company().name().like("Acme"))
    .orderBy(user -> user.company().registerMoney().desc())
    .toList();

SELECT t.* FROM t_user t
INNER JOIN t_company t1 ON t.company_id = t1.id
WHERE t1.name LIKE '%Acme%'
ORDER BY t1.register_money DESC

You wrote user.company().name(). Easy-query generated the JOIN.

OneToMany → Implicit Subquery

easyEntityQuery.queryable(Company.class)
    .where(company -> {
        company.users().any(u -> u.name().like("John"));
        company.users().where(u -> u.age().gt(30)).count().ge(5L);
    }).toList();

SELECT t.* FROM t_company t
WHERE EXISTS (SELECT 1 FROM t_user t1 WHERE t1.company_id = t.id AND t1.name LIKE '%John%')
  AND (SELECT COUNT(*) FROM t_user t1 WHERE t1.company_id = t.id AND t1.age > 30) >= 5

.any() becomes EXISTS. .count() becomes a scalar subquery. No manual SQL required.

Top-N Per Group — `.first()` and `.elements()`

easyEntityQuery.queryable(SysUser.class)
    .where(user ->
        user.bankCards()
            .orderBy(card -> card.openTime().desc())
            .first()
            .type().eq("credit")
    ).toList();

Easy-query automatically generates a derived table with ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) and filters for __row__ = 1. One line of Java replaces ~20 lines of SQL.

Projections & VO Mapping

Select Specific Columns

easyEntityQuery.queryable(SysUser.class)
    .select(u -> u.FETCHER.id().name().phone())
    .toList();

SELECT t.id, t.name, t.phone FROM t_user t

Exclude Columns

.select(u -> u.FETCHER.allFieldsExclude(u.phone(), u.createTime()))

Map to VO

easyEntityQuery.queryable(SysUser.class)
    .leftJoin(SysBankCard.class, (u, card) -> u.id().eq(card.uid()))
    .select(BankCardVO.class, (u, card) -> Select.of(
        card.FETCHER.allFields(),
        u.name().as(BankCardVO.Fields.userName)
    )).toList();

Draft Tuples

For ad-hoc projections without defining a VO class:

List<Draft3<String, Integer, LocalDateTime>> result = easyEntityQuery
    .queryable(BlogEntity.class)
    .groupBy(b -> GroupKeys.of(b.title()))
    .select(group -> Select.DRAFT.of(
        group.key1(),
        group.groupTable().star().sum(),
        group.groupTable().createTime().max()
    )).toList();

Eager Loading with `include` / `selectAutoInclude`

// Explicit include
easyEntityQuery.queryable(SysUser.class)
    .include(user -> user.bankCards())
    .include(user -> user.roles())
    .toList();

// Auto include for VO (loads relations declared on the VO class)
easyEntityQuery.queryable(SysUser.class)
    .selectAutoInclude(SysUserDetailVO.class)
    .toList();

// Include with selective columns
easyEntityQuery.queryable(SysUser.class)
    .include(user -> user.bankCards(), then -> {
        then.select(card -> card.FETCHER.id().type());
    }).toList();

CRUD Operations

Insert

// Single
SysUser user = new SysUser();
user.setId(UUID.randomUUID().toString());
user.setName("John");
user.setAge(30);
easyEntityQuery.insertable(user).executeRows();

// Batch
List<SysUser> users = buildUserList();
easyEntityQuery.insertable(users).batch().executeRows();

Update

// Entity update (updates all non-null fields)
SysUser user = easyEntityQuery.queryable(SysUser.class)
    .where(u -> u.id().eq("123"))
    .firstNotNull("User not found");
user.setName("Jane");
easyEntityQuery.updatable(user).executeRows();

// Expression update
easyEntityQuery.updatable(SysUser.class)
    .setColumns(u -> {
        u.name().set("Jane");
        u.age().set(u.age().plus(1));  // age = age + 1
    })
    .where(u -> u.id().eq("123"))
    .executeRows();

Delete

// By entity
easyEntityQuery.deletable(user).executeRows();

// By condition
easyEntityQuery.deletable(SysUser.class)
    .where(u -> u.name().eq("obsolete"))
    .executeRows();

// By ID
easyEntityQuery.deletable(SysUser.class)
    .whereById("123")
    .executeRows();

Upsert (Insert or Update on Conflict)

easyEntityQuery.insertable(entity)
    .onConflictThen(o -> o.FETCHER.allFields())
    .executeRows();

// With specific conflict columns
easyEntityQuery.insertable(entity)
    .onConflictThen(o -> o.FETCHER.allFields(), o -> o.uniqueKey())
    .executeRows();

Transactions

try (Transaction transaction = easyEntityQuery.beginTransaction()) {
    easyEntityQuery.insertable(user).executeRows();
    easyEntityQuery.insertable(bankCards).batch().executeRows();
    transaction.commit();
}
// Auto-rollback if commit() is not called

Change Tracking & Differential Updates

Easy-query can track entity changes and only update modified columns:

TrackManager trackManager = easyEntityQuery.getRuntimeContext().getTrackManager();
try {
    trackManager.begin();

    SysUser user = easyEntityQuery.queryable(SysUser.class)
        .where(u -> u.id().eq("123"))
        .firstNotNull("not found");

    easyEntityQuery.addTracking(user);

    // Only this field changed
    user.setName("New Name");

    // Generates: UPDATE t_user SET name = ? WHERE id = ?
    // NOT: UPDATE t_user SET name = ?, age = ?, phone = ?, ... WHERE id = ?
    easyEntityQuery.updatable(user).executeRows();

} finally {
    trackManager.release();
}

Only the name column is included in the SET clause. This reduces write amplification and avoids overwriting concurrent changes to other columns.

OLAP & Analytics

Easy-query has first-class support for analytical queries. Here's a quick overview (see the companion OLAP article for the full deep dive).

Window Functions

b.expression().rowNumberOver().partitionBy(b.title()).orderBy(b.createTime())
b.expression().rankOver().partitionBy(b.title()).orderBy(b.createTime())
b.expression().sumOver(b.star()).partitionBy(b.title()).orderBy(b.createTime())
card.type().offset(o -> o.orderBy(card.type())).prev(1)   // LAG
card.type().offset(o -> o.orderBy(card.type())).next(1)   // LEAD

Conditional Aggregation

user.id().count().filter(() -> user.address().eq("Hangzhou"))
// → COUNT(CASE WHEN t.address = 'Hangzhou' THEN 1 ELSE NULL END)

String Aggregation (Cross-Database)

group.groupTable().content().joining(",")
// MySQL → GROUP_CONCAT(t.content SEPARATOR ',')
// PostgreSQL → STRING_AGG(t.content::TEXT, ',')

CASE WHEN Builder

group.expression()
    .caseWhen(() -> group.groupTable().score().gt(0))
    .then(group.groupTable().score())
    .elseEnd(null)
    .sum()

`subQueryToGroupJoin` — Merge Multiple Subqueries into One GROUP BY

easyEntityQuery.queryable(SysUser.class)
    .subQueryToGroupJoin(user -> user.bankCards())
    .where(user -> {
        user.bankCards().where(c -> c.type().eq("credit")).count().ge(2L);
        user.bankCards().none(c -> c.type().eq("debit"));
    }).toList();

Instead of generating two correlated subqueries, easy-query merges them into a single LEFT JOIN (SELECT ... GROUP BY ...).

Logical Delete

Add @LogicDelete to a column, and easy-query automatically filters deleted records in all queries:

@Table("t_blog")
@EntityProxy
public class BlogEntity implements ProxyEntityAvailable<BlogEntity, BlogEntityProxy> {

    @Column(primaryKey = true)
    private String id;
    private String title;

    @LogicDelete(strategy = LogicDeleteStrategyEnum.BOOLEAN)
    private Boolean deleted;
}

Now every query on BlogEntity automatically appends WHERE t.deleted = false. Delete operations set deleted = true instead of issuing DELETE FROM.

You can customize the strategy — timestamp-based, integer-based, or entirely custom:

@LogicDelete(strategy = LogicDeleteStrategyEnum.LOCAL_DATE_TIME)
private LocalDateTime deleteAt;

When "deleted", deleteAt is set to LocalDateTime.now(). Queries filter by WHERE delete_at IS NULL.

Interceptors & Multi-Tenancy

Entity Interceptor (Auto-Fill Fields)

public class MyEntityInterceptor implements EntityInterceptor {

    @Override
    public void configureInsert(Class<?> entityClass,
            EntityInsertExpressionBuilder builder, Object entity) {
        TopicInterceptor topic = (TopicInterceptor) entity;
        if (topic.getCreateTime() == null) {
            topic.setCreateTime(LocalDateTime.now());
        }
        if (topic.getCreateBy() == null) {
            topic.setCreateBy(CurrentUserHelper.getUserId());
        }
    }
}

Every insert automatically fills createTime and createBy without manual code in each service method.

Predicate Interceptor (Multi-Tenancy)

public class MyTenantInterceptor implements PredicateFilterInterceptor {

    @Override
    public void configure(Class<?> entityClass,
            LambdaEntityExpressionBuilder builder,
            WherePredicate<Object> wherePredicate) {
        wherePredicate.eq("tenantId", CurrentUserHelper.getTenantId());
    }
}

Every query on entities with the tenant interceptor automatically appends WHERE tenant_id = ?. You never forget to add the tenant filter.

Column Encryption

Java-Level Encryption

@Table("t_user")
@EntityProxy
public class SysUserEncryption {

    @Encryption(strategy = Base64EncryptionStrategy.class)
    private String phone;

    @Encryption(strategy = MyEncryptionStrategy.class, supportQueryLike = true)
    private String address;
}

Values are encrypted before writing to the database and decrypted after reading. supportQueryLike = true enables LIKE queries on encrypted columns (using a compatible encryption strategy).

SQL-Level Encryption

@Column(sqlConversion = MySQLAesEncryptColumnValueSQLConverter.class)
private String phone;

This generates AES_ENCRYPT / AES_DECRYPT calls in the SQL itself, so encryption happens at the database level.

Dynamic Table Names

For multi-tenancy with table-per-tenant or time-partitioned tables:

easyEntityQuery.queryable(SysUser.class)
    .asTable("t_user_2024")
    .where(u -> u.name().like("John"))
    .toList();

// Or with a function
easyEntityQuery.updatable(SysUser.class)
    .asTable(name -> name + "_archive")
    .whereById("1")
    .executeRows();

Sharding

Easy-query supports table sharding, database sharding, and combined table+database sharding.

Table Sharding

Define the shard initializer:

public class UserShardingInitializer implements EntityShardingInitializer<SysUser> {
    @Override
    public void configure(ShardingEntityBuilder<SysUser> builder) {
        List<String> tables = new ArrayList<>();
        for (int i = 0; i < 4; i++) {
            tables.add("t_user_" + i);
        }
        LinkedHashMap<String, Collection<String>> initTables = new LinkedHashMap<>();
        initTables.put("ds_default", tables);
        builder.actualTableNameInit(initTables);
    }
}

Define the routing rule:

public class UserTableRoute extends AbstractShardingTableRoute<SysUser> {
    @Override
    protected RouteFunction<ActualTable> getRouteFilter(
            TableAvailable table, Object shardingValue,
            ShardingOperatorEnum op, boolean withEntity) {
        int shard = Math.abs(shardingValue.hashCode()) % 4;
        String target = table.getTableName() + "_" + shard;
        return t -> target.equalsIgnoreCase(t.getActualTableName());
    }
}

Queries automatically route to the correct shard table based on the sharding key. Cross-shard queries are supported — easy-query queries all relevant shards and merges the results.

Database Sharding

Same pattern, but with multiple ShardingDataSource instances:

Set<ShardingDataSource> dataSources = new HashSet<>();
dataSources.add(new ShardingDataSource("ds2021", dataSource2021, 20));
dataSources.add(new ShardingDataSource("ds2022", dataSource2022, 20));
dataSources.add(new ShardingDataSource("ds2023", dataSource2023, 20));

EasyQueryShardingOption option = new EasyQueryShardingOption(dataSources);

Pagination

// Simple pagination
EasyPageResult<SysUser> page = easyEntityQuery.queryable(SysUser.class)
    .where(u -> u.age().gt(18))
    .orderBy(u -> u.createTime().desc())
    .toPageResult(1, 20);  // page 1, 20 per page

long total = page.getTotal();
List<SysUser> data = page.getData();

For sharded tables, pagination works correctly across shards — easy-query handles the cross-shard result merging and total count.

Code First

Generate database tables from your entity classes:

// Sync specific classes
CodeFirstCommand command = databaseCodeFirst.syncTableCommand(
    Arrays.asList(SysUser.class, SysBank.class, SysBankCard.class)
);
command.executeWithTransaction(sql -> {
    System.out.println(sql.getSQL());
    sql.commit();
});

// Sync all entities in a package
easyQueryClient.syncTableByPackage(10, "com.myapp.entity");

Easy-query reads the @Table, @Column, and relationship annotations to generate CREATE TABLE / ALTER TABLE DDL for your target database.

Supported Databases

Database	Module
MySQL 5.x / 8.x	`sql-mysql`
PostgreSQL	`sql-pgsql`
Oracle	`sql-oracle`
SQL Server	`sql-mssql`
SQLite	`sql-sqlite`
H2	`sql-h2`
ClickHouse	`sql-clickhouse`
DM (达梦)	`sql-dameng`
KingbaseES	`sql-kingbase-es`
GaussDB	`sql-gauss-db`
DuckDB	`sql-duckdb`
DB2	`sql-db2`

All dialect differences (identifier quoting, pagination syntax, date functions, string aggregation functions, etc.) are handled automatically.

What Makes Easy-Query Different

Here's a summary of what sets easy-query apart from other Java ORMs:

vs. JPA/Hibernate

	Easy-Query	JPA/Hibernate
Query style	Type-safe chainable DSL	JPQL strings or Criteria API
Window functions	Full API	Not supported
Implicit relationships	JOIN/subquery auto-generated	Lazy loading (N+1 prone)
Sharding	Built-in	Requires external solutions
Logical delete	`@LogicDelete`	Manual implementation
Multi-tenancy	Predicate interceptor	`@Filter` (limited)
Conditional aggregation	`.count().filter()`	Not supported
Change tracking	Built-in differential updates	Dirty checking (all columns)

vs. JOOQ

	Easy-Query	JOOQ
Code generation	APT (compile-time)	Requires database connection
Implicit relationships	Yes (auto JOIN/subquery)	No (manual JOINs)
Top-N per group	`.first()` one-liner	Manual window function
`subQueryToGroupJoin`	Yes	No
Sharding	Built-in	Not supported
Logical delete	Built-in	Not supported
Navigation properties	`@Navigate`	Not supported
ORM features (include, tracking)	Yes	No (query builder only)

Easy-query occupies a unique position: it has the ORM convenience of JPA (entities, relationships, eager loading, change tracking) combined with the query power of JOOQ (type-safe DSL, window functions, CTEs), plus features neither has (implicit relationships, sharding, Top-N shortcuts).

Getting Started

<dependency>
    <groupId>com.easy-query</groupId>
    <artifactId>sql-springboot-starter</artifactId>
    <version>3.2.1</version>
</dependency>

Documentation: https://www.easy-query.com/easy-query-doc/en
GitHub: https://github.com/xuejmnet/easy-query
License: Apache 2.0

Conclusion

Easy-query is what happens when an ORM takes analytical queries, sharding, and developer ergonomics seriously from day one. Instead of treating complex SQL as an escape hatch, it makes those patterns first-class citizens of a type-safe API.

If you're building Java applications that go beyond CRUD — dashboards, reports, multi-tenant SaaS, sharded databases, or anything involving window functions and conditional aggregation — easy-query is worth a serious look.

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

Li — Fri, 20 Mar 2026 18:48:42 +0000

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();

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

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();

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();

SELECT t.*,
  (SUM(t.star) OVER (PARTITION BY t.title ORDER BY t.create_time ASC))
FROM t_blog t

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();

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 = ?

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();

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 = ?

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(), ",")

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();

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

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();

SELECT t.title,
  SUM(CASE WHEN t.score > 0 THEN t.score ELSE NULL END)
FROM t_blog t
GROUP BY t.title

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();

On MySQL:

SELECT t.title, GROUP_CONCAT(t.content SEPARATOR ',') FROM t_blog t GROUP BY t.title

On PostgreSQL:

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

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(), ",")

GROUP_CONCAT(DISTINCT t1.type ORDER BY t1.open_time DESC SEPARATOR ',')

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();

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) > ?

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();

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

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")

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")

{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>

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.

Benchmark: easy-query vs jOOQ

Li — Fri, 26 Dec 2025 16:45:43 +0000

This article presents JMH benchmark results comparing easy-query, jOOQ, and Hibernate, with source code analysis explaining why easy-query achieves superior performance.

Test Environment

Configuration	Value
OS	Windows 10 (Build 26200)
JDK	OpenJDK 21.0.9+10-LTS
Database	H2 2.2.224 (In-Memory)
Connection Pool	HikariCP 4.0.3
JMH	1.37
easy-query	3.1.66-preview3
jOOQ	3.19.1
Hibernate	6.4.1.Final

Test Parameters:

Warmup: 10 iterations, 5 seconds each
Measurement: 15 iterations, 5 seconds each
Fork: 3 JVM processes
Threads: Single-threaded

Benchmark Results

Query Operations

Test Scenario	easy-query	jOOQ	Hibernate	Winner
Select by ID	298,303 ops/s	132,786 ops/s	264,571 ops/s	🏆 easy-query (2.25x vs jOOQ)
Select List	247,088 ops/s	68,773 ops/s	141,050 ops/s	🏆 easy-query (3.59x vs jOOQ)
COUNT	382,545 ops/s	197,704 ops/s	385,362 ops/s	Hibernate ≈ easy-query

Complex Queries

Test Scenario	easy-query	jOOQ	Hibernate	Winner
JOIN Query	138,963 ops/s	5,859 ops/s	56,437 ops/s	🏆 easy-query (23.72x vs jOOQ!)
Subquery (GROUP BY + HAVING)	100,725 ops/s	5,296 ops/s	15,594 ops/s	🏆 easy-query (19.01x vs jOOQ!)

Write Operations

Test Scenario	easy-query	jOOQ	Hibernate	Winner
Single Insert	63,866 ops/s	50,257 ops/s	57,385 ops/s	🏆 easy-query (1.27x vs jOOQ)
Batch Insert (1000)	72.06 ops/s	43.54 ops/s	70.66 ops/s	🏆 easy-query (1.66x vs jOOQ)
Update by ID	125,902 ops/s	100,361 ops/s	92,470 ops/s	🏆 easy-query (1.25x vs jOOQ)

Visualization

SELECT BY ID (ops/s - higher is better)
EasyQuery    █████████████████████████████████████████████████ 298,303
Hibernate    ████████████████████████████████████████████      264,571
JOOQ         ████████████████████                              132,786

JOIN QUERY (ops/s - higher is better)
EasyQuery    █████████████████████████████████████████████████ 138,963
Hibernate    ████████████████████                               56,437
JOOQ         █                                                   5,859

Why is easy-query So Fast?

easy-query implements deep optimizations at multiple levels, drawing from techniques used in high-performance frameworks like fastjson2.

1. Lambda Function Mapping Instead of Reflection

This is easy-query's core performance optimization, using the same technique as fastjson2.

The Problem with Traditional Reflection:

Method methodGetId = Bean.class.getMethod("getId");
Bean bean = createInstance();
int value = (Integer) methodGetId.invoke(bean);  // Overhead on every call

Reflection invocation involves:

Entering JVM native method stack
JNI layer transitions
C-level checks and operations

easy-query's Solution:

Use LambdaMetafactory to generate function mappings, converting reflection calls to regular method calls:

// sql-core/src/main/java/com/easy/query/core/common/bean/DefaultFastBean.java

/**
 * Fast implementation of bean get/set methods.
 * Lambda getter calls have virtually zero overhead.
 * Setters are also zero-overhead except for first-time lambda creation and caching.
 */
public class DefaultFastBean implements FastBean {

    private Property<Object, ?> getLambdaProperty(FastBeanProperty prop) {
        Class<?> propertyType = prop.getPropertyType();
        Method readMethod = prop.getReadMethod();
        String getFunName = readMethod.getName();
        final MethodHandles.Lookup caller = MethodHandles.lookup();

        // Generate function mapping with LambdaMetafactory
        final CallSite site = LambdaMetafactory.altMetafactory(caller,
                "apply",
                MethodType.methodType(Property.class),
                methodType.erase().generic(),
                caller.findVirtual(beanClass, getFunName, MethodType.methodType(propertyType)),
                methodType, FLAG_SERIALIZABLE);

        return (Property<Object, ?>) site.getTarget().invokeExact();
    }
}

Performance Difference (from fastjson2 benchmarks):

Method	Time for 10,000 calls
Method.invoke (reflection)	25ms
Lambda function mapping	1ms

25x improvement!

The key advantages of Lambda function mapping:

Generated functions can be cached and reused
Subsequent calls are equivalent to regular Java method calls
Executed entirely within Java stack, no JNI overhead

2. Property Accessor Caching

easy-query generates Lambda function mappings for each entity property at startup (or first access) and caches them:

// Getter cache
public Property<Object, ?> getBeanGetter(FastBeanProperty prop) {
    return getLambdaProperty(prop);
    // In practice, with caching:
    // return EasyMapUtil.computeIfAbsent(propertyGetterCache, prop.getName(), k -> getLambdaProperty(prop));
}

// Setter cache
public PropertySetterCaller<Object> getBeanSetter(FastBeanProperty prop) {
    return getLambdaPropertySetter(prop);
}

// Constructor cache
public Supplier<Object> getBeanConstructorCreator() {
    return getLambdaCreate();
}

This means:

First access: Generate Lambda function (~50μs)
Subsequent access: Use cached function (~0.1μs)

3. Compile-Time Proxy Generation (APT)

easy-query uses APT (Annotation Processing Tool) to generate proxy classes at compile time, not runtime dynamic proxies:

// Compile-time generated proxy class knows all properties
@EntityProxy
public class User {
    private String id;
    private String name;
    private Integer age;
}

// Generated proxy class UserProxy
public class UserProxy {
    public StringProperty id() { ... }
    public StringProperty name() { ... }
    public IntegerProperty age() { ... }
}

4. Direct JDBC Execution

easy-query uses JDBC API directly:

// easy-query directly creates PreparedStatement
PreparedStatement ps = EasyJdbcExecutorUtil.createPreparedStatement(
    connection, sql, parameters, easyJdbcTypeHandler);
ResultSet rs = ps.executeQuery();

5. Lightweight SQL Generation

easy-query's SQL generation is lightweight string concatenation:

// Clean SQL building
easyEntityQuery.queryable(User.class)
    .where(u -> u.age().ge(25))
    .orderBy(u -> u.username().desc())
    .limit(10)
    .toList();

// Generated SQL:
// SELECT id, username, email, age, phone, address FROM t_user WHERE age >= ? ORDER BY username DESC LIMIT ?

No complex abstraction layers, direct SQL generation.

6. ResultSet Mapping Optimization

Combined with Lambda function mapping, ResultSet-to-object conversion is also zero-overhead:

// Using cached Setter Lambda to set property values
PropertySetterCaller<Object> setter = fastBean.getBeanSetter(prop);
setter.call(entity, resultSet.getObject(columnIndex));

Equivalent to directly calling entity.setName(value), no reflection overhead.

easy-query Optimization Summary

Optimization	easy-query
Lambda function mapping instead of reflection	✅
Compile-time proxy classes (APT)	✅
Direct JDBC execution	✅
Property accessor caching	✅
Lightweight SQL generation	✅

Conclusion

easy-query's high performance comes from:

Core Technology: Using LambdaMetafactory to generate function mappings instead of reflection (same as fastjson2)
Compile-Time Optimization: APT-generated proxy classes, avoiding runtime dynamic proxies
Lean Architecture: Direct JDBC, no extra abstraction layers
Caching Strategy: Function mappings and metadata all cached for reuse

As the fastjson2 author states:

Lambda uses LambdaMetafactory to generate function mappings instead of reflection. Generated functions can be cached for reuse, and subsequent calls are entirely within Java stack calls, virtually identical to native method calls.

easy-query applies this philosophy throughout the ORM, achieving near-native JDBC performance while providing advanced features like strong typing, Lambda syntax, and implicit joins.

easy-query vs jOOQ

Li — Mon, 22 Dec 2025 11:11:49 +0000

jOOQ is a popular strongly-typed query framework in the Java ecosystem, known for "typesafe SQL" with syntax that closely mirrors native SQL. This article compares easy-query and jOOQ, exploring two different query DSL design philosophies.

Design Philosophy Comparison

Dimension	jOOQ	easy-query
Core Philosophy	SQL-first	Object-first
Code Generation Source	Database Schema	Entity Class Annotations
Relationship Expression	Tables & Columns	Objects & Navigation Properties
Join Approach	Explicit, SQL-like	Implicit, Object-relationship based

jOOQ believes SQL is the most powerful query language, and frameworks should let developers write SQL in a type-safe manner.

easy-query believes business developers should focus on object relationships, while the framework translates object operations into optimal SQL.

Basic Query Comparison

Query users whose name contains "test".

jOOQ

List<UserRecord> users = dsl
    .selectFrom(USER)
    .where(USER.NAME.like("%test%"))
    .fetchInto(UserRecord.class);

easy-query

List<User> users = entityQuery.queryable(User.class)
    .where(u -> u.name().like("test"))
    .toList();

Differences:

jOOQ uses table constants like USER, syntax close to SQL
easy-query uses Lambda + entity classes, like auto-adds % wildcards

Join Query Comparison

Scenario: Query users where the company name contains "Tech".

jOOQ (Traditional)

List<UserRecord> users = dsl
    .select(USER.fields())
    .from(USER)
    .join(COMPANY).on(USER.COMPANY_ID.eq(COMPANY.ID))
    .where(COMPANY.NAME.like("%Tech%"))
    .fetchInto(UserRecord.class);

jOOQ (3.11+ Implicit Join)

Since version 3.11, jOOQ supports implicit joins via path navigation:

List<UserRecord> users = dsl
    .selectFrom(USER)
    .where(USER.company().NAME.like("%Tech%"))
    .fetchInto(UserRecord.class);

easy-query

List<User> users = entityQuery.queryable(User.class)
    .where(u -> u.company().name().like("Tech"))
    .toList();

Differences:

jOOQ 3.11+ also supports implicit joins with syntax like USER.company().NAME
easy-query uses Lambda syntax u.company().name()
jOOQ generates path methods from database foreign keys, easy-query from entity annotations
easy-query's like auto-adds % wildcards, jOOQ requires manual addition

Subquery Comparison

Scenario: Query users who have comments.

jOOQ (Traditional)

List<UserRecord> users = dsl
    .selectFrom(USER)
    .whereExists(
        dsl.selectOne()
           .from(COMMENT)
           .where(COMMENT.USER_ID.eq(USER.ID))
    )
    .fetchInto(UserRecord.class);

jOOQ (3.19+ Implicit to-many Path)

jOOQ 3.19 enhanced to-many path support:

List<UserRecord> users = dsl
    .selectFrom(USER)
    .where(USER.comment().ID.isNotNull())  // Path access
    .fetchInto(UserRecord.class);

However, for EXISTS semantics, explicit subqueries are still required.

easy-query

List<User> users = entityQuery.queryable(User.class)
    .where(u -> u.comments().any())
    .toList();

Differences:

jOOQ's to-many paths are mainly for aggregation scenarios; EXISTS semantics still need manual construction
easy-query's comments().any() directly expresses "exists" semantics

Aggregate Subquery Comparison

Scenario: Query users with more than 10 comments, and count books per author.

jOOQ (3.19+ Implicit to-many Aggregation)

jOOQ 3.19 supports aggregation via to-many paths:

// Count books per author
Result<?> result = dsl
    .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count(AUTHOR.book().ID))
    .from(AUTHOR)
    .groupBy(AUTHOR.ID)
    .fetch();

But aggregate subqueries in WHERE still require manual construction:

List<UserRecord> users = dsl
    .selectFrom(USER)
    .where(
        dsl.selectCount()
           .from(COMMENT)
           .where(COMMENT.USER_ID.eq(USER.ID))
           .gt(10)
    )
    .fetchInto(UserRecord.class);

easy-query

List<User> users = entityQuery.queryable(User.class)
    .where(u -> u.comments().count().gt(10L))
    .toList();

Differences:

jOOQ's to-many path aggregation is mainly for SELECT/GROUP BY; WHERE still needs subqueries
easy-query allows comments().count() directly in WHERE/ORDER BY/SELECT

Dynamic Query Comparison

Scenario: Dynamically build query conditions based on frontend parameters.

jOOQ

SelectConditionStep<UserRecord> query = dsl
    .selectFrom(USER)
    .where(DSL.trueCondition());

if (StringUtils.hasText(name)) {
    query = query.and(USER.NAME.like("%" + name + "%"));
}
if (minAge != null) {
    query = query.and(USER.AGE.ge(minAge));
}
if (companyName != null) {
    // Need manual join
    query = query.and(
        DSL.exists(
            dsl.selectOne().from(COMPANY)
               .where(COMPANY.ID.eq(USER.COMPANY_ID))
               .and(COMPANY.NAME.eq(companyName))
        )
    );
}

List<UserRecord> users = query.fetchInto(UserRecord.class);

easy-query

List<User> users = entityQuery.queryable(User.class)
    .where(u -> {
        u.name().like(name);                           // Auto-skips if empty
        u.age().ge(minAge);                            // Auto-skips if null
        u.company().name().eq(companyName);            // No join if condition inactive
    })
    .toList();

Differences:

jOOQ requires manual condition concatenation; related queries need subqueries or explicit joins
easy-query auto-skips null/empty conditions, implicit join doesn't generate JOIN when condition is inactive

Group Aggregation Comparison

Scenario: Count users and average age per department.

jOOQ

Result<Record3<String, Integer, BigDecimal>> result = dsl
    .select(USER.DEPARTMENT, DSL.count(), DSL.avg(USER.AGE))
    .from(USER)
    .groupBy(USER.DEPARTMENT)
    .fetch();

for (Record3<String, Integer, BigDecimal> row : result) {
    String dept = row.value1();
    Integer count = row.value2();
    BigDecimal avgAge = row.value3();
}

easy-query

List<Draft3<String, Long, BigDecimal>> result = entityQuery.queryable(User.class)
    .groupBy(u -> GroupBy.of(u.department()))
    .select(u -> Select.DRAFT.of(
        u.key1(),
        u.count(),
        u.age().avg()
    ))
    .toList();

for (var row : result) {
    String dept = row.getValue1();
    Long count = row.getValue2();
    BigDecimal avgAge = row.getValue3();
}

Differences:

Both support strongly-typed returns
jOOQ uses Record3, easy-query uses Draft3
Different syntax styles: jOOQ is SQL-like, easy-query is more object-oriented

Conditional Aggregation Comparison

Scenario: Count users in Department A and Department B separately.

jOOQ

Result<Record2<Integer, Integer>> result = dsl
    .select(
        DSL.count().filterWhere(USER.DEPARTMENT.eq("A")),
        DSL.count().filterWhere(USER.DEPARTMENT.eq("B"))
    )
    .from(USER)
    .fetch();

easy-query

List<Draft2<Long, Long>> result = entityQuery.queryable(User.class)
    .select(u -> Select.DRAFT.of(
        u.id().count().filter(() -> u.department().eq("A")),
        u.id().count().filter(() -> u.department().eq("B"))
    ))
    .toList();

Differences:

Both support filter syntax for conditional aggregation
jOOQ's filterWhere and easy-query's .filter() serve the same purpose

Window Function Comparison

Scenario: Query users with their age rank within their department.

jOOQ

Result<Record3<String, String, Integer>> result = dsl
    .select(
        USER.NAME,
        USER.DEPARTMENT,
        DSL.rowNumber().over(
            DSL.partitionBy(USER.DEPARTMENT)
               .orderBy(USER.AGE.desc())
        ).as("rank")
    )
    .from(USER)
    .fetch();

easy-query

List<Draft3<String, String, Long>> result = entityQuery.queryable(User.class)
    .select(u -> Select.DRAFT.of(
        u.name(),
        u.department(),
        u.age().rowNumber().over(
            o -> o.partitionBy(u.department()).orderBy(u.age().desc())
        )
    ))
    .toList();

Differences:

Both support window functions
Similar structure, jOOQ is closer to SQL keywords

Related Data Loading Comparison

Scenario: Query users and load their roles.

jOOQ

jOOQ is not an ORM and doesn't natively support relationship loading. Options:

// Option 1: Multiple queries + manual assembly
List<UserRecord> users = dsl.selectFrom(USER).fetch();
List<String> userIds = users.stream().map(UserRecord::getId).toList();
Map<String, List<RoleRecord>> rolesMap = dsl
    .selectFrom(USER_ROLE.join(ROLE).on(...))
    .where(USER_ROLE.USER_ID.in(userIds))
    .fetchGroups(USER_ROLE.USER_ID, RoleRecord.class);
// Manually assemble into users

// Option 2: Use jOOQ's multiset (requires jOOQ 3.15+)
List<UserWithRoles> result = dsl
    .select(
        USER.ID,
        USER.NAME,
        DSL.multiset(
            dsl.select(ROLE.ID, ROLE.NAME)
               .from(USER_ROLE)
               .join(ROLE).on(USER_ROLE.ROLE_ID.eq(ROLE.ID))
               .where(USER_ROLE.USER_ID.eq(USER.ID))
        ).as("roles").convertFrom(r -> r.into(RoleDTO.class))
    )
    .from(USER)
    .fetchInto(UserWithRoles.class);

easy-query

List<User> users = entityQuery.queryable(User.class)
    .include(u -> u.roles())
    .toList();

// Supports multiple includes without Cartesian products
List<User> users = entityQuery.queryable(User.class)
    .include(u -> u.roles())
    .include(u -> u.posts())
    .toList();

// Supports deep nesting
List<User> users = entityQuery.queryable(User.class)
    .include2((c, u) -> {
        c.query(u.roles());
        c.query(u.posts().flatElement().comments());
    })
    .toList();

Differences:

jOOQ requires manual relationship handling or multiset (newer feature)
easy-query's include is native and concise

DTO Projection Comparison

Scenario: Query users and return a DTO with associated roles.

jOOQ

// Basic DTO projection
List<UserDTO> users = dsl
    .select(USER.ID, USER.NAME)
    .from(USER)
    .fetchInto(UserDTO.class);

// DTO with associations (requires multiset)
List<UserWithRolesDTO> users = dsl
    .select(
        USER.ID,
        USER.NAME,
        DSL.multiset(
            dsl.select(ROLE.ID, ROLE.NAME)
               .from(USER_ROLE)
               .join(ROLE).on(...)
               .where(USER_ROLE.USER_ID.eq(USER.ID))
        ).as("roles")
    )
    .from(USER)
    .fetchInto(UserWithRolesDTO.class);

easy-query

@Data
public class UserDTO {
    private String id;
    private String name;

    @Navigate(value = RelationTypeEnum.ManyToMany)
    private List<RoleDTO> roles;  // Supports nested associations
}

List<UserDTO> users = entityQuery.queryable(User.class)
    .selectAutoInclude(UserDTO.class)
    .toList();
// Roles auto-populated

Differences:

jOOQ requires multiset to manually construct nested structures
easy-query's selectAutoInclude handles it automatically based on DTO structure

Feature Comparison Table

Feature	jOOQ	easy-query
Design Philosophy	SQL-first	Object-first
Strongly-typed queries	✅	✅
Code Generation Source	Database Schema	Entity Classes (APT)
Syntax Style	SQL-like	Object-oriented Lambda
Implicit Join (to-one)	✅ 3.11+	✅
Implicit to-many Aggregation	✅ 3.19+ (SELECT/GROUP BY)	✅ (WHERE/ORDER BY/SELECT)
Implicit Subquery (any/none)	❌	✅
Dynamic condition skip	Manual	Automatic
Window Functions	✅	✅
Conditional Aggregation	✅ `filterWhere`	✅ `.filter()`
Relationship Loading	multiset (3.15+)	✅ `include`
DTO Nested Associations	multiset	✅ `selectAutoInclude`
Subquery Optimization	❌	✅ GROUP JOIN
Commercial License	Some DBs require paid	Completely free

Use Cases

jOOQ is better for:

Complex SQL (CTEs, recursive queries, advanced window functions)
Teams with strong SQL expertise who want full control over queries
Existing database schemas requiring reverse code generation
Reporting, data analysis, OLAP scenarios

easy-query is better for:

Business application development focused on object relationships
Frequent relationship queries and data loading
Cleaner code that's closer to business semantics
Heavy dynamic query scenarios

Summary

jOOQ and easy-query represent two different design philosophies:

jOOQ: SQL is a first-class citizen; the framework helps you write SQL in a type-safe way
easy-query: Objects are first-class citizens; the framework translates object operations into SQL

jOOQ suits SQL experts who want complete control over queries; easy-query suits business developers who want the framework to handle tedious SQL details.

They're not mutually exclusive — in the same project, you can use easy-query for everyday CRUD and relationship queries, and jOOQ for complex reporting SQL.

Subquery to GROUP JOIN — A Method for Merging Multiple Subqueries

Li — Tue, 16 Dec 2025 13:45:17 +0000

Problem Scenario

Consider the following entity relationships:

User: has many comments
Comment: belongs to a user, references a post
Post: belongs to a category
Category

Business requirement: Find the top 5 users with the most comments in the ".NET" category over the past 7 days.

Standard Approach: Multiple Subqueries

ORMs typically generate a separate subquery for each condition:

SELECT * FROM t_user t 
WHERE EXISTS (SELECT 1 FROM t_comment WHERE user_id = t.id)
ORDER BY (
    SELECT COUNT(*) FROM t_comment c
    JOIN t_post p ON p.id = c.post_id
    JOIN t_category cat ON cat.id = p.category_id
    WHERE c.user_id = t.id 
      AND c.create_at > '7 days ago'
      AND cat.name = '.NET'
) DESC
LIMIT 5

This query contains two subqueries:

EXISTS subquery: checks if a user has any comments
COUNT subquery: counts comments matching the criteria

Each user requires both subqueries to execute, causing the comment table to be scanned repeatedly. More conditions mean more subqueries and worse performance.

Optimization Strategy

Key Insight: One-to-Many Can Only Do Aggregation

For a one-to-many relationship (User → Comments), the only operations possible in WHERE and ORDER BY are:

Operation	Essence
Has comments?	EXISTS = COUNT > 0
How many comments?	COUNT
Latest comment time?	MAX
No comments of type X?	NOT EXISTS = COUNT = 0

All aggregations. Since they're all aggregations, why compute them separately?

Merge Strategy: One GROUP BY, Multiple CASE WHENs

Group the comment table by user ID and compute all required values in a single aggregation:

SELECT 
    user_id,
    (COUNT(*) > 0) AS has_comments,
    COUNT(CASE WHEN create_at > '7 days ago' AND category.name = '.NET' THEN 1 END) AS net_count
FROM t_comment
LEFT JOIN t_post ON ...
LEFT JOIN t_category ON ...
GROUP BY user_id

This produces an "aggregation table" with one row per user, containing all aggregated values needed for the conditions.

Then LEFT JOIN back to the user table and use the aggregated results in WHERE and ORDER BY:

SELECT t.* 
FROM t_user t
LEFT JOIN (aggregation subquery above) agg ON agg.user_id = t.id
WHERE IFNULL(agg.has_comments, false) = true
ORDER BY IFNULL(agg.net_count, 0) DESC
LIMIT 5

The comment table is scanned only once.

Why Doesn't the Database Do This Automatically?

Because the database sees two independent subqueries and doesn't know:

Both subqueries operate on the same table (t_comment)
They share the same join condition (user_id = t.id)
They can be merged into a single GROUP BY

Database optimizers don't understand "object relationships" — they only see SQL text.

The ORM Advantage

The ORM knows that user.comments() is a one-to-many relationship with user_id as the foreign key.

When developers write:

u.comments().any();                           // Condition 1: has comments
u.comments().where(...).count().desc();       // Condition 2: order by count

The ORM can recognize: these are two aggregation operations on the same comments relationship — they can be merged.

Implementation in easy-query

Standard Approach

LocalDateTime dateTime = LocalDateTime.now().plusDays(-7);
List<User> list = entityQuery.queryable(User.class)
    .where(u -> {
        u.comments().any();
    })
    .orderBy(u -> {
        u.comments().where(c -> {
            c.createAt().isAfter(dateTime);
            c.post().category().name().eq(".NET");
        }).count().desc();
    })
    .limit(5).toList();

Enabling GROUP JOIN Optimization

Just add one line of configuration:

List<User> list = entityQuery.queryable(User.class)
    .configure(s -> s.getBehavior().add(EasyBehaviorEnum.ALL_SUB_QUERY_GROUP_JOIN))  // Add this line
    .where(u -> {
        u.comments().any();
    })
    .orderBy(u -> {
        u.comments().where(c -> {
            c.createAt().isAfter(dateTime);
            c.post().category().name().eq(".NET");
        }).count().desc();
    })
    .limit(5).toList();

Generated SQL:

SELECT t.`id`, t.`username` 
FROM `t_user` t 
LEFT JOIN (
    SELECT 
        t1.`user_id` AS `userId`, 
        (COUNT(*) > 0) AS `__any2__`, 
        COUNT(CASE 
            WHEN t1.`create_at` > '2025-10-19 22:30:12' AND t4.`name` = '.NET' 
            THEN 1 ELSE NULL 
        END) AS `__count3__` 
    FROM `t_comment` t1 
    LEFT JOIN `t_post` t3 ON t3.`id` = t1.`post_id` 
    LEFT JOIN `t_category` t4 ON t4.`id` = t3.`category_id` 
    GROUP BY t1.`user_id`
) t2 ON t2.`userId` = t.`id` 
WHERE IFNULL(t2.`__any2__`, false) = true 
ORDER BY IFNULL(t2.`__count3__`, 0) DESC 
LIMIT 5

Other Ways to Enable

// Option 1: Query-level configuration
.configure(s -> s.getBehavior().add(EasyBehaviorEnum.ALL_SUB_QUERY_GROUP_JOIN))

// Option 2: Specify the relationship
.subQueryToGroupJoin(user -> user.comments())

// Option 3: Annotation
@Navigate(subQueryToGroupJoin = true)
private List<Comment> comments;

More Complex Scenarios

If you add another subquery in SELECT (e.g., total comment count), GROUP JOIN still requires only one scan:

entityQuery.queryable(User.class)
    .configure(s -> s.getBehavior().add(EasyBehaviorEnum.ALL_SUB_QUERY_GROUP_JOIN))
    .where(u -> u.comments().any())
    .orderBy(u -> {
        u.comments().where(c -> {
            c.createAt().isAfter(dateTime);
            c.post().category().name().eq(".NET");
        }).count().desc();
    })
    .limit(5)
    .select(u -> Select.DRAFT.of(
        u.id(),
        u.username(),
        u.comments().count()  // Additional: total comment count
    )).toList();

All three aggregations (any, conditional count, total count) are computed in a single GROUP BY subquery.

Performance Test: easy-query vs EF Core

Test Environment

Database: MySQL 8 (local)
Java 8 / EF Core 9
Data: 16 users, 9 categories, 150K posts, 1M comments
Source code: https://github.com/xuejmnet/eqefcoresamples

easy-query with Standard Subqueries

LocalDateTime dateTime = LocalDateTime.now().plusDays(-7);
List<User> list = entityQuery.queryable(User.class)
    .where(u -> {
        u.comments().any();
    })
    .orderBy(u -> {
        u.comments().where(c -> {
            c.createAt().isAfter(dateTime);
            c.post().category().name().eq(".NET");
        }).count().desc();
    })
    .limit(5).toList();

Generated SQL:

SELECT t.`id`, t.`username` 
FROM `t_user` t 
WHERE EXISTS (
    SELECT 1 FROM `t_comment` t1 
    WHERE t1.`user_id` = t.`id` LIMIT 1
) 
ORDER BY (
    SELECT COUNT(*) FROM `t_comment` t2 
    LEFT JOIN `t_post` t3 ON t3.`id` = t2.`post_id` 
    LEFT JOIN `t_category` t4 ON t4.`id` = t3.`category_id` 
    WHERE t2.`user_id` = t.`id` 
      AND t2.`create_at` > '2025-10-19 22:28:18' 
      AND t4.`name` = '.NET'
) DESC 
LIMIT 5

Duration: ~11 seconds

EF Core

var dateTime = DateTime.Now.AddDays(-7);
var users = context.Set<User>()
    .AsNoTracking()
    .Where(u => u.Comments.Any())
    .OrderByDescending(u => u.Comments
        .Count(c =>
            c.CreatedAt >= dateTime &&
            c.Post.Category.Name == ".NET")
    )
    .Take(5)
    .ToList();

Generated SQL:

SELECT `t`.`id`, `t`.`username`
FROM `t_user` AS `t`
WHERE EXISTS (
    SELECT 1 FROM `t_comment` AS `t0`
    WHERE `t`.`id` = `t0`.`user_id`
)
ORDER BY (
    SELECT COUNT(*)
    FROM `t_comment` AS `t1`
    INNER JOIN `t_post` AS `t2` ON `t1`.`post_id` = `t2`.`id`
    INNER JOIN `t_category` AS `t3` ON `t2`.`category_id` = `t3`.`id`
    WHERE (`t`.`id` = `t1`.`user_id`) 
      AND ((`t1`.`create_at` >= @__dateTime_0) AND (`t3`.`name` = '.NET'))
) DESC
LIMIT @__p_1

Duration: ~11 seconds

easy-query with GROUP JOIN

List<User> list = entityQuery.queryable(User.class)
    .configure(s -> s.getBehavior().add(EasyBehaviorEnum.ALL_SUB_QUERY_GROUP_JOIN))
    .where(u -> u.comments().any())
    .orderBy(u -> {
        u.comments().where(c -> {
            c.createAt().isAfter(dateTime);
            c.post().category().name().eq(".NET");
        }).count().desc();
    })
    .limit(5).toList();

Duration: ~2.7 seconds

Performance Comparison

Framework	Approach	Duration
easy-query	Standard subqueries	~11s
EF Core	Subqueries	~11s
easy-query	GROUP JOIN	~2.7s

4x performance improvement!

Summary

The essence of Subquery to GROUP JOIN:

Identify: Multiple subqueries operating on the same one-to-many relationship
Merge: Use CASE WHEN to compute all conditions in a single GROUP BY
Join: LEFT JOIN the aggregated result back to the main table

This optimization requires semantic information at the ORM level — database optimizers can't do it. You write subqueries the same way as before, but the framework automatically merges multiple subqueries (in WHERE / ORDER BY / SELECT) into one — this is "Implicit Group", solving both readability and performance issues.

Forem: Li

easy-query: A Type-Safe Java ORM That Actually Handles the Hard Parts

Table of Contents

Setup

Entity Definition & Proxy Generation

Relationship Types

Querying: The Chainable DSL

Basic Query

Multi-Table JOIN

JOIN with Subquery

Implicit Relationships

ManyToOne / OneToOne → Implicit JOIN

OneToMany → Implicit Subquery

Top-N Per Group — .first() and .elements()

Projections & VO Mapping

Select Specific Columns

Exclude Columns

Map to VO

Draft Tuples

Eager Loading with include / selectAutoInclude

CRUD Operations

Insert

Update

Delete

Upsert (Insert or Update on Conflict)

Transactions

Change Tracking & Differential Updates

OLAP & Analytics

Window Functions

Conditional Aggregation

String Aggregation (Cross-Database)

CASE WHEN Builder

subQueryToGroupJoin — Merge Multiple Subqueries into One GROUP BY

Logical Delete

Interceptors & Multi-Tenancy

Entity Interceptor (Auto-Fill Fields)

Predicate Interceptor (Multi-Tenancy)

Column Encryption

Java-Level Encryption

SQL-Level Encryption

Dynamic Table Names

Sharding

Table Sharding

Database Sharding

Pagination

Code First

Supported Databases

What Makes Easy-Query Different

vs. JPA/Hibernate

vs. JOOQ

Getting Started

Conclusion

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

Window Functions: Full API, Not String Concatenation

ROW_NUMBER / RANK / DENSE_RANK

LAG / LEAD / FIRST_VALUE / LAST_VALUE / NTH_VALUE

Aggregate Window Functions (SUM / AVG / COUNT OVER)

Filtering on Window Results: The partitionBy Wrapper

Top-N Per Group: One Line Instead of Twenty

.first() — Get the First Row Per Group

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

Conditional Aggregation: .count().filter()

CASE WHEN Builder

String Aggregation: Cross-Database .joining()

Implicit Collection Aggregation

subQueryToGroupJoin: The N+1 Killer for Analytics

Date Functions

Raw SQL Escape Hatch

Supported Databases

The Big Picture

Getting Started

Conclusion

Benchmark: easy-query vs jOOQ

Test Environment

Benchmark Results

Query Operations

Complex Queries

Write Operations

Visualization

Why is easy-query So Fast?

Top-N Per Group — `.first()` and `.elements()`

Eager Loading with `include` / `selectAutoInclude`

`subQueryToGroupJoin` — Merge Multiple Subqueries into One GROUP BY

Filtering on Window Results: The `partitionBy` Wrapper

`.first()` — Get the First Row Per Group

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

Conditional Aggregation: `.count().filter()`

String Aggregation: Cross-Database `.joining()`

`subQueryToGroupJoin`: The N+1 Killer for Analytics