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:

  1. Customer revenue analytics with CTEs, aggregations, and ranking.
  2. 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():

  1. The order must belong to the current customer.
  2. 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:

  1. Calculate total revenue across all customers.
  2. Protect against division by zero using NULLIF.
  3. Divide the current customer's revenue by the total.
  4. Multiply by 100.
  5. 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 ...
Source：https://dev.to/aissam_assouik/building-complex-sql-queries-in-a-type-safe-way-with-java-and-querier-4b92
My favorites