easy-query: The Most Powerful ORM Subquery for Java

Source: Dev.to

Why Are Subqueries So Important?

In real business development, subqueries are everywhere:

• Query “users who have orders”
• Query “authors with more than 10 articles”
• Query “members who have made purchases in the last 30 days”
• Count “each user’s orders, comments, and favorites”

Traditional ORMs either don’t support this, require raw SQL, or generate poorly performing SQL.

easy‑query’s goal: Make subqueries as simple as regular queries while generating high‑performance SQL.

1. Implicit Subqueries: Write Code Like Speaking

1.1 Existence Check: any / none

// Query users who have posts
List users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts().any())
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE EXISTS (SELECT 1 FROM t_post t1 WHERE t1.user_id = t.id)

// Query users who have no posts
List users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts().none())
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE NOT EXISTS (SELECT 1 FROM t_post t1 WHERE t1.user_id = t.id)

1.2 Conditional Existence Check

// Query users who have published posts
List users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts()
        .where(p -> p.status().eq(1))
        .any())
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE EXISTS (
//     SELECT 1 FROM t_post t1 
//     WHERE t1.user_id = t.id AND t1.status = 1
// )

1.3 Universal Quantifier: all / notEmptyAll

// Query users whose all bank cards are savings cards with code starting with "622"
List users = easyEntityQuery.queryable(User.class)
    .where(u -> u.bankCards()
        .where(bc -> bc.type().eq("savings"))
        .all(bc -> bc.code().startsWith("622")))
    .toList();

// Generated SQL (uses NOT EXISTS + NOT logic):
// SELECT * FROM t_user t
// WHERE NOT EXISTS (
//     SELECT 1 FROM t_bank_card t1 
//     WHERE t1.user_id = t.id AND t1.type = 'savings' 
//       AND NOT (t1.code LIKE '622%')
//     LIMIT 1
// )

Difference between all and notEmptyAll:

// notEmptyAll: At least one savings card exists, and all savings cards start with "622"
List users = easyEntityQuery.queryable(User.class)
    .where(u -> u.bankCards()
        .where(bc -> bc.type().eq("savings"))
        .notEmptyAll(bc -> bc.code().startsWith("622")))
    .toList();

// notEmptyAll = any() + all(), meaning: exists AND all match

1.4 Aggregate Subqueries: count / sum / avg / max / min

// Query users with more than 5 posts
List users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts().count().gt(5L))
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE (SELECT COUNT(*) FROM t_post t1 WHERE t1.user_id = t.id) > 5

// Query users with total order amount over 10000
List users = easyEntityQuery.queryable(User.class)
    .where(u -> u.orders()
        .sum(o -> o.amount())
        .gt(new BigDecimal("10000")))
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE (SELECT SUM(amount) FROM t_order t1 WHERE t1.user_id = t.id) > 10000

1.5 Subqueries in ORDER BY

// Order by post count
List users = easyEntityQuery.queryable(User.class)
    .orderBy(u -> u.posts().count().desc())
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// ORDER BY (SELECT COUNT(*) FROM t_post t1 WHERE t1.user_id = t.id) DESC

1.6 Subqueries in SELECT

// Query users with their post count
List users = easyEntityQuery.queryable(User.class)
    .select(u -> new UserDTOProxy()
        .id().set(u.id())
        .username().set(u.username())
        .postCount().set(u.posts().count()))
    .toList();

// Generated SQL:
// SELECT t.id, t.username, 
//        (SELECT COUNT(*) FROM t_post t1 WHERE t1.user_id = t.id) AS post_count
// FROM t_user t

2. Subquery Merge Optimization: From N Scans to 1 Scan

This is the most powerful feature of easy‑query’s subquery handling.

2.1 The Problem: Performance Disaster with Multiple Subqueries

Suppose we want to query each user’s post count and comment count:

// Standard approach
List users = easyEntityQuery.queryable(User.class)
    .select(u -> new UserDTOProxy()
        .id().set(u.id())
        .postCount().set(u.posts().count())
        .commentCount().set(u.comments().count()))
    .toList();

Typical ORMs generate separate sub‑queries for each aggregation, leading to many scans of the child tables.

Problem Statement

With a large data set (e.g., 1 M users, 5 M posts, 10 M comments), a naïve approach that uses a separate sub‑query for each user results in 2 M scans of the child tables.

SELECT t.id,
       (SELECT COUNT(*) FROM t_post WHERE user_id = t.id) AS post_count,
       (SELECT COUNT(*) FROM t_comment WHERE user_id = t.id) AS comment_count
FROM t_user t;

2.2 easy‑query Optimizations

Subquery → GROUP JOIN

easy‑query automatically merges multiple sub‑queries into a single GROUP BY join.

SELECT t.id,
       IFNULL(t1.post_count, 0)      AS post_count,
       IFNULL(t2.comment_count, 0)   AS comment_count
FROM t_user t
LEFT JOIN (
    SELECT user_id, COUNT(*) AS post_count
    FROM t_post
    GROUP BY user_id
) t1 ON t.id = t1.user_id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS comment_count
    FROM t_comment
    GROUP BY user_id
) t2 ON t.id = t2.user_id;

Performance comparison

Potential speed‑up: 100× – 1000×.

Conditional Aggregation → CASE WHEN

Java (easy‑query)

// Count each user's published and draft posts
List users = easyEntityQuery.queryable(User.class)
    .select(u -> new UserDTOProxy()
        .id().set(u.id())
        .publishedCount().set(u.posts()
            .where(p -> p.status().eq(1)).count())
        .draftCount().set(u.posts()
            .where(p -> p.status().eq(0)).count()))
    .toList();

Typical ORM output (two sub‑queries):

SELECT t.id,
       (SELECT COUNT(*) FROM t_post WHERE user_id = t.id AND status = 1),
       (SELECT COUNT(*) FROM t_post WHERE user_id = t.id AND status = 0)
FROM t_user t;

easy‑query optimized SQL (single GROUP BY + CASE WHEN):

SELECT t.id,
       SUM(CASE WHEN t1.status = 1 THEN 1 ELSE 0 END) AS published_count,
       SUM(CASE WHEN t1.status = 0 THEN 1 ELSE 0 END) AS draft_count
FROM t_user t
LEFT JOIN t_post t1 ON t.id = t1.user_id
GROUP BY t.id;

Multiple conditional aggregations on the same table are merged into one join with several CASE WHEN expressions.

2.3 Multi‑Level Nested Subqueries

Nested Navigation Properties

// Users who have posts with liked comments
List users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts()
        .any(p -> p.comments()
            .any(c -> c.likes().any())))
    .toList();

Cross‑Level Aggregation

// Users whose posts have > 100 total comments
List users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts()
        .flatElement()
        .comments()
        .count()
        .gt(100L))
    .toList();

2.4 Combining Subqueries with Explicit Joins

// Users with orders > 1000 who also have comments
List users = easyEntityQuery.queryable(User.class)
    .innerJoin(Order.class, (u, o) -> u.id().eq(o.userId()))
    .where((u, o) -> {
        o.amount().gt(new BigDecimal("1000"));
        u.comments().any();               // Subquery combined with Join
    })
    .select((u, o) -> u)
    .distinct()
    .toList();

2.5 Feature Comparison with Other ORMs

2.6 Why “Most Powerful”?

• Simplest syntax – u.posts().count().gt(5L) reads like natural language.
• Automatic optimization – Subquery → GROUP JOIN, conditional aggregation → CASE WHEN.
• Full scenario support – Subqueries in WHERE, ORDER BY, and SELECT.
• Strongly‑typed – Compile‑time checks, refactor‑friendly.
• Best performance – Generates high‑performance SQL without manual tuning.

Summary

easy‑query’s subquery design philosophy:

1. Make complex simple – Express intricate subquery logic with concise lambdas.
2. Make slow fast – Auto‑optimizes inefficient SQL into performant statements.
3. Make manual automatic – Developers describe what they need; the framework decides how to achieve it.

If your application heavily relies on relational statistics, existence checks, or aggregate queries, give easy‑query a try.

Related Links

• GitHub:
• Documentation: