AISSAM ASSOUIK

Posted on Nov 9

Building Complex SQL Queries in a Type-Safe Way with Java and Querier

#java #webdev #opensource #sql

Building Complex SQL Queries in a Type-Safe Way with Java and Querier

Writing SQL queries in Java is relatively easy when dealing with simple SELECT, WHERE, and JOIN statements.

But what happens when your application starts requiring more advanced analytics?

Think about queries involving multiple Common Table Expressions (CTEs), nested aggregations, window functions, ranking, running totals, revenue contribution percentages, and database-specific functions.

You can certainly write these queries as native SQL strings and execute them through JDBC or JPA. However, maintaining those SQL strings alongside Java entity models introduces another challenge: column names, table names, and query structure are no longer connected to your Java code.

A property rename can leave a native SQL query broken without the Java compiler noticing anything.

This is one of the problems I wanted to address when building Querier, an open-source Java SQL query builder that uses method references to provide type-safe column references while retaining the flexibility required for complex SQL queries.

In this article, we'll explore two practical analytics scenarios and see how to implement them using Querier:

Customer revenue analytics with CTEs, aggregations, and ranking.
Product performance analytics with CTEs, window functions, and cumulative revenue calculations.

These are not simple CRUD examples. They are the kind of queries you might encounter when building reporting systems, business intelligence applications, or analytics features.

1. Introducing Querier

Querier is an open-source Java SQL query builder built around the Builder pattern.

Instead of referencing database columns using strings everywhere, Querier allows you to use Java method references:

Select select = Select.builder()
    .select(Order::getId)
    .select(Order::getTotal)
    .from(Order.class)
    .where(Condition.eq(
        Order::getStatus,
        OrderStatus.PAID
    ))
    .build();

The main idea is straightforward: your query references Java entity properties directly.

Querier supports:

SELECT, WHERE, JOIN, and complex conditions.
GROUP BY, HAVING, and aggregate functions.
Subqueries and Common Table Expressions (CTEs).
Window functions and ranking operations.
UNION, UNION ALL, INTERSECT, and EXCEPT.
Database-specific SQL functions through dialect factories and custom resolvers.
Named, positional, and indexed SQL parameter generation.

It requires Java 17 or later and is available as a Maven dependency.

Maven installation

<dependency>
    <groupId>com.aytmatech</groupId>
    <artifactId>querier-core</artifactId>
    <version>QUERIER_VERSION</version>
</dependency>

Replace QUERIER_VERSION with the version you want to use.

2. Our domain model

For these examples, we'll work with three entities:

Customer
Order
Product

The examples use the following entity properties.

Order.java

@Table("orders")
public class Order {

    private Long id;
    private Long customerId;
    private BigDecimal total;
    private OrderStatus status;
    private LocalDateTime createdAt;
    private Long productId;

    public Long getId() {
        return id;
    }

    @Column("customer_id")
    public Long getCustomerId() {
        return customerId;
    }

    public BigDecimal getTotal() {
        return total;
    }

    public OrderStatus getStatus() {
        return status;
    }

    @Column("created_at")
    public LocalDateTime getCreatedAt() {
        return createdAt;
    }

    @Column("product_id")
    public Long getProductId() {
        return productId;
    }
}

Product.java

@Table("products")
public class Product {

    private Long id;
    private String name;
    private BigDecimal price;

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public BigDecimal getPrice() {
        return price;
    }
}

Customer.java

@Table("customers")
public class Customer {

    private Long id;
    private String name;
    private String email;

    public Long getId() {
      return id;
    }

    public void setId(Long id) {
      this.id = id;
    }

    public String getName() {
      return name;
    }
}

Our order status enum contains the following values:

public enum OrderStatus {
    PAID,
    SHIPPED,
    DELIVERED
}

Notice how Querier resolves Java properties into their corresponding database columns. For example, Order::getCustomerId resolves to orders.customer_id.

This is particularly useful when entity properties evolve over time.

Example 1: Customer Revenue Analytics

Let's start with a customer analytics query.

Our objective is to generate a customer revenue report containing:

Customer information.
Total number of completed orders.
Lifetime revenue.
Average order value.
First and last purchase dates.
Revenue ranking.
Percentage contribution to total revenue.

We also want customers without completed orders to remain in the result.

This requires more than a simple aggregation.

Understanding the SQL

We'll split the query into two CTEs.

CTE 1: customer_metrics

This CTE calculates the customer-level metrics by joining customers with their completed orders.

We'll use:

COUNT() to calculate the total number of orders.
COALESCE(SUM()) to return zero when no orders exist.
AVG() to calculate average order value.
MIN() and MAX() to calculate first and last purchase dates.

CTE 2: ranked_customers

This CTE takes the aggregated customer metrics and adds:

DENSE_RANK() to rank customers by lifetime revenue.
A window aggregation to calculate each customer's percentage contribution to total revenue.

Finally, the main query retrieves the results ordered by revenue ranking.

The complete SQL query

WITH customer_metrics AS (
    SELECT
        customers.id,
        customers.name,
        customers.email,

        COUNT(orders.id) AS total_orders,

        COALESCE(
            SUM(orders.total),
            0
        ) AS lifetime_revenue,

        ROUND(
            AVG(orders.total),
            2
        ) AS average_order_value,

        MIN(orders.created_at) AS first_purchase_date,
        MAX(orders.created_at) AS last_purchase_date

    FROM customers

    LEFT JOIN orders
        ON (
            orders.customer_id = customers.id
            AND orders.status IN (
                'PAID',
                'SHIPPED',
                'DELIVERED'
            )
        )

    GROUP BY
        customers.id,
        customers.name,
        customers.email
),

ranked_customers AS (
    SELECT
        customer_metrics.id,
        customer_metrics.name,
        customer_metrics.email,
        customer_metrics.total_orders,
        customer_metrics.lifetime_revenue,
        customer_metrics.average_order_value,
        customer_metrics.first_purchase_date,
        customer_metrics.last_purchase_date,

        DENSE_RANK() OVER (
            ORDER BY
                customer_metrics.lifetime_revenue DESC
        ) AS revenue_rank,

        ROUND(
            100.0 * NUMERIC_DIV(
                customer_metrics.lifetime_revenue,
                NULLIF(
                    SUM(customer_metrics.lifetime_revenue) OVER (),
                    '0'
                )
            ),
            '2'
        ) AS revenue_contribution_pct

    FROM customer_metrics
)

SELECT
    ranked_customers.id,
    ranked_customers.name,
    ranked_customers.email,
    ranked_customers.total_orders,
    ranked_customers.lifetime_revenue,
    ranked_customers.average_order_value,
    ranked_customers.first_purchase_date,
    ranked_customers.last_purchase_date,
    ranked_customers.revenue_rank,
    ranked_customers.revenue_contribution_pct

FROM ranked_customers

ORDER BY
    revenue_rank ASC,
    ranked_customers.name ASC;

Now let's build exactly the same query using Querier.

Step 1: Building aggregate SQL functions

Before creating our first CTE, let's define the two calculated expressions that we'll reuse.

Lifetime revenue

We need:

COALESCE(SUM(orders.total), 0)

Using DbFunction, we can compose nested SQL functions:

// SUM(orders.total)
DbFunction sumOrderTotal = DbFunction.of(
    "SUM",
    DbFunction.col(Order::getTotal)
);

// COALESCE(SUM(orders.total), 0)
DbFunction lifetimeRevenue = DbFunction.of(
    "COALESCE",
    DbFunction.nested(sumOrderTotal),
    DbFunction.val(BigDecimal.ZERO)
).as("lifetime_revenue");

Notice the use of Order::getTotal rather than "orders.total".

We are still referring to an entity property, which Querier can resolve.

Average order value

Similarly:

// ROUND(AVG(orders.total), 2)
DbFunction averageOrderValue = DbFunction.of(
    "ROUND",
    DbFunction.nested(
        DbFunction.of(
            "AVG",
            DbFunction.col(Order::getTotal)
        )
    ),
    DbFunction.raw("2")
).as("average_order_value");

These expressions demonstrate how we can compose SQL functions without manually concatenating entire SQL statements.

Step 2: Building the customer_metrics CTE

Now that we have our calculated expressions, we can build the first CTE.

Select customerMetrics = Select.builder()

    .select(Customer::getId)
    .select(Customer::getName)
    .select(Customer::getEmail)

    .select(
        Aggregate.count(Order::getId)
            .as("total_orders")
    )

    .select(lifetimeRevenue)

    .select(averageOrderValue)

    .select(
        Aggregate.min(Order::getCreatedAt)
            .as("first_purchase_date")
    )

    .select(
        Aggregate.max(Order::getCreatedAt)
            .as("last_purchase_date")
    )

    .from(Customer.class)

    .leftJoin(
        Order.class,
        Condition.and(
            Condition.eq(
                Order::getCustomerId,
                Customer::getId
            ),
            Condition.in(
                Order::getStatus,
                List.of(
                    OrderStatus.PAID,
                    OrderStatus.SHIPPED,
                    OrderStatus.DELIVERED
                )
            )
        )
    )

    .groupBy(
        Customer::getId,
        Customer::getName,
        Customer::getEmail
    )

    .build();

There are several interesting things happening here.

First, we are using Customer.class as our primary table.

Second, the LEFT JOIN condition combines two conditions using Condition.and():

The order must belong to the current customer.
The order status must be one of our completed statuses.

Third, we use Aggregate.count(), Aggregate.min(), and Aggregate.max() for the corresponding SQL aggregate functions.

Finally, the builder takes care of composing the query structure, including the GROUP BY clause.

One important detail is that the status filter is part of the JOIN condition rather than a WHERE condition. This preserves customers without matching orders.

Step 3: Building the ranked_customers CTE

This is where the query becomes more interesting.

We need to calculate two additional metrics.

Revenue ranking

Our SQL expression is:

DENSE_RANK() OVER (
    ORDER BY customer_metrics.lifetime_revenue DESC
)

Querier provides WindowFunction for building window expressions.

WindowFunction revenueRank = WindowFunction.of(
        DbFunction.of("DENSE_RANK")
    )
    .orderBy(
        OrderBy.desc("customer_metrics.lifetime_revenue")
    )
    .as("revenue_rank");

Revenue contribution percentage

The next expression is more complex:

ROUND(
    100.0 *
    NUMERIC_DIV(
        customer_metrics.lifetime_revenue,
        NULLIF(
            SUM(customer_metrics.lifetime_revenue) OVER (),
            '0'
        )
    ),
    '2'
)

We need:

Calculate total revenue across all customers.
Protect against division by zero using NULLIF.
Divide the current customer's revenue by the total.
Multiply by 100.
Round to two decimal places.

We can construct this expression using DbFunction.custom():

DbFunction revenueContributionPct = DbFunction.custom(
    (strategy, gen) -> {

        WindowFunction sum = WindowFunction.of(
            DbFunction.of(
                "SUM",
                DbFunction.raw(
                    "customer_metrics.lifetime_revenue"
                )
            )
        );

        DbFunction nullif = DbFunction.of(
            "NULLIF",
            DbFunction.raw(sum.toSql()),
            DbFunction.lit("0")
        );

        DbFunction division = DbFunction.of(
            "NUMERIC_DIV",
            DbFunction.raw(
                "customer_metrics.lifetime_revenue"
            ),
            DbFunction.nested(nullif)
        );

        DbFunction round = DbFunction.of(
            "ROUND",
            DbFunction.raw(
                "100.0 * " + division.toSql()
            ),
            DbFunction.lit("2")
        );

        return new DbFunction.Resolved(
            round.toSql(),
            Map.of()
        );

    }
).as("revenue_contribution_pct");

Why use DbFunction.custom() here?

Not every SQL expression can be represented conveniently as a simple nested function call.

Sometimes we need to compose a more elaborate SQL expression involving window functions, custom syntax, or multiple nested expressions.

Querier exposes a custom resolver for these situations, allowing us to generate the required SQL fragment while keeping the rest of the query inside the builder API.

Now we can construct the second CTE:

Select rankedCustomers = Select.builder()

    .select(Expression.raw("customer_metrics.id"))
    .select(Expression.raw("customer_metrics.name"))
    .select(Expression.raw("customer_metrics.email"))

    .select(Expression.raw("customer_metrics.total_orders"))
    .select(Expression.raw("customer_metrics.lifetime_revenue"))
    .select(Expression.raw("customer_metrics.average_order_value"))

    .select(Expression.raw("customer_metrics.first_purchase_date"))
    .select(Expression.raw("customer_metrics.last_purchase_date"))

    .select(revenueRank)

    .select(revenueContributionPct)

    .from(Expression.tableRef("customer_metrics"))

    .build();

Because customer_metrics is a CTE rather than a mapped Java entity, we reference its projected columns using Expression.raw() and its table using Expression.tableRef().

This is an important distinction: method references provide compile-time checking for entity-backed columns, while references to derived CTE columns remain explicit SQL expressions.

Step 4: Assembling the final query

We now have both CTEs.

The last step is to assemble them using the with() method.

var select = Select.builder()

    .with("customer_metrics", customerMetrics)
    .with("ranked_customers", rankedCustomers)

    .select(Expression.raw("ranked_customers.id"))
    .select(Expression.raw("ranked_customers.name"))
    .select(Expression.raw("ranked_customers.email"))

    .select(Expression.raw("ranked_customers.total_orders"))
    .select(Expression.raw("ranked_customers.lifetime_revenue"))
    .select(Expression.raw("ranked_customers.average_order_value"))

    .select(Expression.raw("ranked_customers.first_purchase_date"))
    .select(Expression.raw("ranked_customers.last_purchase_date"))

    .select(Expression.raw("ranked_customers.revenue_rank"))
    .select(Expression.raw("ranked_customers.revenue_contribution_pct"))

    .from(Expression.tableRef("ranked_customers"))

    .orderBy(
        OrderBy.asc("revenue_rank"),
        OrderBy.asc("ranked_customers.name")
    )

    .build();

That's it!

We've successfully reconstructed our original SQL query using Querier.

The interesting part is that each CTE is an independent Select object. We can construct, test, and reuse these query components separately before assembling the final statement.

Example 2: Product Performance Analytics

Let's move to a slightly more advanced example.

This time, we want to generate a product performance report.

For every product, we want to calculate:

Product information and current price.
Total number of completed orders.
Number of unique customers.
Total revenue.
Average order value.
Revenue ranking.
Revenue contribution percentage.
Cumulative revenue percentage.

The last metric is particularly interesting.

Cumulative revenue percentage tells us how much of the total revenue is generated by products up to a given position in the revenue ranking.

This is useful for identifying the products responsible for most of the business revenue and performing Pareto-style analysis.

Understanding the SQL

Again, we'll use two CTEs.

CTE 1: product_metrics

This CTE calculates product-level metrics through a LEFT JOIN between products and completed orders.

We need:

COUNT(orders.id) for the number of orders.
COUNT(DISTINCT orders.customer_id) for unique customers.
SUM(orders.total) for total revenue.
AVG(orders.total) for average order value.

CTE 2: product_analysis

This CTE introduces window functions.

We'll calculate:

DENSE_RANK() to rank products by revenue.
A global revenue contribution percentage.
A cumulative revenue percentage using a running SUM.

The complete SQL query

WITH product_metrics AS (
    SELECT
        products.id,
        products.name,
        products.price,

        COUNT(orders.id) AS total_orders,

        COUNT(DISTINCT orders.customer_id)
            AS unique_customers,

        COALESCE(
            SUM(orders.total),
            0
        ) AS total_revenue,

        ROUND(
            AVG(orders.total),
            2
        ) AS average_order_value

    FROM products

    LEFT JOIN orders
        ON (
            orders.product_id = products.id
            AND orders.status IN (
                'PAID',
                'SHIPPED',
                'DELIVERED'
            )
        )

    GROUP BY
        products.id,
        products.name,
        products.price
),

product_analysis AS (
    SELECT
        product_metrics.id,
        product_metrics.name,
        product_metrics.price,
        product_metrics.total_orders,
        product_metrics.unique_customers,
        product_metrics.total_revenue,
        product_metrics.average_order_value,

        DENSE_RANK() OVER (
            ORDER BY
                product_metrics.total_revenue DESC
        ) AS revenue_rank,

        ROUND(
            100.0 * NUMERIC_DIV(
                product_metrics.total_revenue,
                NULLIF(
                    SUM(product_metrics.total_revenue) OVER (),
                    '0'
                )
            ),
            '2'
        ) AS revenue_contribution_pct,

        ROUND(
            100.0 * NUMERIC_DIV(
                SUM(product_metrics.total_revenue) OVER (
                    ORDER BY
                        product_metrics.total_revenue DESC,
                        product_metrics.id ASC
                ),
                NULLIF(
                    SUM(product_metrics.total_revenue) OVER (),
                    '0'
                )
            ),
            '2'
        ) AS cumulative_revenue_pct

    FROM product_metrics
)

SELECT
    product_analysis.id,
    product_analysis.name,
    product_analysis.price,
    product_analysis.total_orders,
    product_analysis.unique_customers,
    product_analysis.total_revenue,
    product_analysis.average_order_value,
    product_analysis.revenue_rank,
    product_analysis.revenue_contribution_pct,
    product_analysis.cumulative_revenue_pct

FROM product_analysis

ORDER BY
    revenue_rank ASC,
    product_analysis.name ASC;

Let's implement it using Querier.

Step 1: Building aggregate functions

As in our first example, let's start by defining the calculated SQL expressions.

// COALESCE(SUM(orders.total), 0)
DbFunction totalRevenue = DbFunction.of(
    "COALESCE",
    DbFunction.nested(
        DbFunction.of(
            "SUM",
            DbFunction.col(Order::getTotal)
        )
    ),
    DbFunction.raw("0")
).as("total_revenue");


// ROUND(AVG(orders.total), 2)
DbFunction averageOrderValue = DbFunction.of(
    "ROUND",
    DbFunction.nested(
        DbFunction.of(
            "AVG",
            DbFunction.col(Order::getTotal)
        )
    ),
    DbFunction.raw("2")
).as("average_order_value");

We are reusing the same function composition approach from the previous example.

Step 2: Building the product_metrics CTE

Now let's build our first product analytics CTE.

Select productMetrics = Select.builder()

    .select(Product::getId)
    .select(Product::getName)
    .select(Product::getPrice)

    .select(
        Aggregate.count(Order::getId)
            .as("total_orders")
    )

    .select(
        Aggregate.countDistinct(Order::getCustomerId)
            .as("unique_customers")
    )

    .select(totalRevenue)

    .select(averageOrderValue)

    .from(Product.class)

    .leftJoin(
        Order.class,
        Condition.and(
            Condition.eq(
                Order::getProductId,
                Product::getId
            ),
            Condition.in(
                Order::getStatus,
                List.of(
                    OrderStatus.PAID,
                    OrderStatus.SHIPPED,
                    OrderStatus.DELIVERED
                )
            )
        )
    )

    .groupBy(
        Product::getId,
        Product::getName,
        Product::getPrice
    )

    .build();

Compared with the customer analytics example, the main difference is that we introduce Aggregate.countDistinct().

This gives us the number of distinct customers who purchased each product.

Again, we're using method references for the entity-backed columns and a LEFT JOIN to preserve products that have never been purchased.

Step 3: Revenue ranking and contribution percentage

We now need to calculate the product ranking and revenue contribution.

The ranking implementation is almost identical to our customer example:

// DENSE_RANK() OVER (
//     ORDER BY total_revenue DESC
// )
WindowFunction revenueRank = WindowFunction.of(
        DbFunction.of("DENSE_RANK")
    )
    .orderBy(
        OrderBy.desc("product_metrics.total_revenue")
    )
    .as("revenue_rank");

For revenue contribution, we can reuse the same custom expression-building approach:

// ROUND(
//     100.0 * total_revenue /
//     NULLIF(SUM(total_revenue) OVER (), 0),
//     2
// )

DbFunction revenueContributionPct = DbFunction.custom(
    (strategy, gen) -> {

        WindowFunction sum = WindowFunction.of(
            DbFunction.of(
                "SUM",
                DbFunction.raw("product_metrics.total_revenue")
            )
        );

        DbFunction nullif = DbFunction.of(
            "NULLIF",
            DbFunction.raw(sum.toSql()),
            DbFunction.lit("0")
        );

        DbFunction division = DbFunction.of(
            "NUMERIC_DIV",
            DbFunction.raw("product_metrics.total_revenue"),
            DbFunction.nested(nullif)
        );

        DbFunction round = DbFunction.of(
            "ROUND",
            DbFunction.raw(
                "100.0 * " + division.toSql()
            ),
            DbFunction.lit("2")
        );

        return new DbFunction.Resolved(
            round.toSql(),
            Map.of()
        );

    }
).as("revenue_contribution_pct");

Step 4: Calculating cumulative revenue percentage

Now comes the interesting part.

We want to calculate the cumulative contribution of products when sorted by revenue.

Consider the following simplified example:

Product	Revenue	Contribution	Cumulative
Product A	5000	50%	50%
Product B	3000	30%	80%
Product C	1500	15%	95%
Product D	500	5%	100%

The cumulative percentage is calculated using a window SUM with an ORDER BY clause.

Conceptually:

SUM(total_revenue) OVER (
    ORDER BY
        total_revenue DESC,
        product_id ASC
)

The first row contains the revenue of the top product.

The second row contains the combined revenue of the first two products.

The third row contains the combined revenue of the first three products, and so on.

We then divide each running total by the global revenue total.

Here is the implementation:

DbFunction cumulativeRevenuePct = DbFunction.custom(
    (strategy, gen) -> {

        WindowFunction runningSum = WindowFunction.of(
            DbFunction.of(
                "SUM",
                DbFunction.raw(
                    "product_metrics.total_revenue"
                )
            )
        ).orderBy(
            OrderBy.desc(
                "product_metrics.total_revenue"
            ),
            OrderBy.asc(
                "product_metrics.id"
            )
        );

        WindowFunction totalSum = WindowFunction.of(
            DbFunction.of(
                "SUM",
                DbFunction.raw(
                    "product_metrics.total_revenue"
                )
            )
        );

        DbFunction nullif = DbFunction.of(
            "NULLIF",
            DbFunction.raw(totalSum.toSql()),
            DbFunction.lit("0")
        );

        DbFunction division = DbFunction.of(
            "NUMERIC_DIV",
            DbFunction.raw(runningSum.toSql()),
            DbFunction.nested(nullif)
        );

        DbFunction round = DbFunction.of(
            "ROUND",
            DbFunction.raw(
                "100.0 * " + division.toSql()
            ),
            DbFunction.lit("2")
        );

        return new DbFunction.Resolved(
            round.toSql(),
            Map.of()
        );

    }
).as("cumulative_revenue_pct");

Notice that we are creating two separate window functions:

runningSum: SUM with ORDER BY, which produces the cumulative revenue.
totalSum: SUM without ORDER BY, which produces the overall revenue.

This is an important distinction.

The first calculates the accumulated value up to the current row, while the second calculates the global total across all rows.

We also include product_metrics.id ASC as a secondary ordering condition to make the running sequence deterministic when multiple products have the same revenue.

Step 5: Building the product_analysis CTE

With all our expressions ready, we can now build the second CTE.

Select productAnalysis = Select.builder()

    .select(Expression.raw("product_metrics.id"))
    .select(Expression.raw("product_metrics.name"))
    .select(Expression.raw("product_metrics.price"))

    .select(Expression.raw("product_metrics.total_orders"))
    .select(Expression.raw("product_metrics.unique_customers"))

    .select(Expression.raw("product_metrics.total_revenue"))
    .select(Expression.raw("product_metrics.average_order_value"))

    .select(revenueRank)

    .select(revenueContributionPct)

    .select(cumulativeRevenuePct)

    .from(Expression.tableRef("product_metrics"))

    .build();

This CTE brings together all our calculated metrics.

We now have product-level aggregation, ranking, individual revenue contribution, and cumulative revenue contribution in a single query structure.

Step 6: Assembling the final query

Finally, let's assemble everything:

var select = Select.builder()

    .with("product_metrics", productMetrics)
    .with("product_analysis", productAnalysis)

    .select(Expression.raw("product_analysis.id"))
    .select(Expression.raw("product_analysis.name"))
    .select(Expression.raw("product_analysis.price"))

    .select(Expression.raw("product_analysis.total_orders"))
    .select(Expression.raw("product_analysis.unique_customers"))

    .select(Expression.raw("product_analysis.total_revenue"))
    .select(Expression.raw("product_analysis.average_order_value"))

    .select(Expression.raw("product_analysis.revenue_rank"))

    .select(Expression.raw(
        "product_analysis.revenue_contribution_pct"
    ))

    .select(Expression.raw(
        "product_analysis.cumulative_revenue_pct"
    ))

    .from(Expression.tableRef("product_analysis"))

    .orderBy(
        OrderBy.asc("revenue_rank"),
        OrderBy.asc("product_analysis.name")
    )

    .build();

And we have our second complex analytics query implemented using Querier.

3. Generating and executing SQL

One important aspect of Querier is that it does not tie query construction to a particular database execution framework.

Once a query has been built, we can generate SQL and its associated parameters.

For example:

Select.SqlAndParams result = select.toSqlAndParams();

String sql = result.sql();

Map<String, Object> params = result.params();

For applications using Spring's NamedParameterJdbcTemplate:

List<Map<String, Object>> rows =
    namedJdbcTemplate.queryForList(
        result.sql(),
        result.params()
    );

For applications using Spring's JdbcTemplate:

Select.PositionalSqlAndParams result =
    select.toPositionalSql();

List<Map<String, Object>> rows =
    jdbcTemplate.queryForList(
        result.sql(),
        result.params().toArray()
    );

Querier also supports indexed parameters ($1, $2, etc.) for compatible execution clients.

This means we can use the same query-building logic regardless of whether the application uses Spring JDBC, plain JDBC, Hibernate, R2DBC, or another execution layer.

You can also use toPlainSql() for debugging, but this output is intended for logging, not execution.

4. A few important considerations

There are a couple of things worth highlighting from these examples.

Type safety has boundaries.

Method references such as Order::getTotal and Product::getName give us compile-time checking for entity-backed columns.

However, CTE aliases are derived SQL columns rather than Java entity properties. In these examples, we access them through Expression.raw() and Expression.tableRef().

These expressions provide flexibility, but their identifiers are not compile-time validated.

Custom SQL functions provide flexibility.

DbFunction.of() is suitable for ordinary function composition, while DbFunction.custom() allows us to implement more complex SQL expressions.

The NUMERIC_DIV expression used in these examples is preserved from the SQL being demonstrated. It is not a standard SQL function name, it's a PostgreSQL function, so its implementation or translation must be provided by the target database or an appropriate custom resolver. The same consideration applies when adapting database-specific SQL functions between vendors.

Querier also provides dedicated dialect function factories for PostgreSQL, MySQL/MariaDB, SQL Server, and Oracle.

Always inspect the generated SQL.

A query builder makes query construction easier, but it does not eliminate the need to understand SQL semantics.

In particular, when working with window functions, CTEs, aggregation, and database-specific expressions, inspect the generated SQL and test its behavior against your target database.

5. Final thoughts

Building complex SQL queries in Java doesn't have to mean choosing between native SQL strings and sacrificing SQL flexibility.

The two examples demonstrate how we can combine several advanced SQL features using Querier:

Multiple CTEs.
LEFT JOIN operations.
Conditional filtering.
Nested aggregate functions.
COUNT DISTINCT.
DENSE_RANK.
Window-based global aggregations.
Running totals.
Custom SQL function resolution.

More importantly, we can break down complex SQL statements into smaller, reusable Java query components.

This is particularly valuable when building reporting systems and analytics-heavy applications, where SQL complexity tends to grow over time.

Querier is an open-source project, and contributions, feedback, and suggestions are always welcome.

Check out the project:

GitHub – AytmaTech/Querier

For the full API documentation, examples, and supported features, visit the project README.

If you're building complex SQL queries in Java, I'd be interested to hear how you're approaching type safety and query composition in your projects.

Let's discuss!

DEV Community

Building Complex SQL Queries in a Type-Safe Way with Java and Querier

Building Complex SQL Queries in a Type-Safe Way with Java and Querier

1. Introducing Querier

Maven installation

2. Our domain model

Example 1: Customer Revenue Analytics

Understanding the SQL

The complete SQL query

Step 1: Building aggregate SQL functions

Step 2: Building the customer_metrics CTE

Step 3: Building the ranked_customers CTE

Step 4: Assembling the final query

Example 2: Product Performance Analytics

Understanding the SQL

The complete SQL query

Step 1: Building aggregate functions

Step 2: Building the product_metrics CTE

Step 3: Revenue ranking and contribution percentage

Step 4: Calculating cumulative revenue percentage

Step 5: Building the product_analysis CTE

Step 6: Assembling the final query

3. Generating and executing SQL

4. A few important considerations

5. Final thoughts

Top comments (0)