easy-query vs jOOQ

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.

---

Links

• easy-query GitHub: https://github.com/dromara/easy-query
• easy-query Documentation: https://www.easy-query.com/easy-query-doc/en
• jOOQ: https://www.jooq.org/