Query Builder

Table of contents

  1. Overview
  2. Setting the table
  3. Selecting columns
  4. Filtering with where*
    1. OR conditions
  5. Ordering
  6. Grouping
    1. HAVING
  7. LIMIT and OFFSET
  8. Building the result
    1. build() (returns a Query)
    2. buildSql() (returns a SqlResult)
  9. Subquery methods
  10. Security

Overview

QueryBuilder is the main entry point for SELECT queries. Its fluent API lets you compose any SELECT statement by chaining method calls and then calling build() (returns a Query object) or buildSql() (returns a SqlResult ready for execution).

import com.github.ezframework.javaquerybuilder.query.builder.QueryBuilder;
import com.github.ezframework.javaquerybuilder.query.sql.SqlDialect;
import com.github.ezframework.javaquerybuilder.query.sql.SqlResult;

SqlResult result = new QueryBuilder()
    .from("users")
    .select("id", "name", "email")
    .whereEquals("status", "active")
    .orderBy("name", true)
    .limit(50)
    .buildSql(SqlDialect.MYSQL);

String sql     = result.getSql();
List<?> params = result.getParameters();

QueryBuilder is also the gateway to all DML builders via its static factory methods. See DML Builders.

Setting the table 

new QueryBuilder().from("orders")

Selecting columns 

// SELECT * (default - no columns specified)
new QueryBuilder().from("users")

// SELECT id, name
new QueryBuilder().from("users").select("id", "name")

// DISTINCT
new QueryBuilder().from("users").distinct().select("country")

Filtering with where*

All where* methods are joined with AND by default. Use the orWhere* variants to join with OR.

// WHERE status = 'active'
.whereEquals("status", "active")

// WHERE status != 'banned'
.whereNotEquals("status", "banned")

// WHERE age > 18
.whereGreaterThan("age", 18)

// WHERE age >= 18
.whereGreaterThanOrEquals("age", 18)

// WHERE price < 100
.whereLessThan("price", 100)

// WHERE price <= 100
.whereLessThanOrEquals("price", 100)

// WHERE name LIKE '%Alice%'
.whereLike("name", "Alice")

// WHERE name NOT LIKE '%bot%'
.whereNotLike("name", "bot")

// WHERE deleted_at IS NULL
.whereNull("deleted_at")

// WHERE verified_at IS NOT NULL
.whereNotNull("verified_at")

// WHERE country IS NOT NULL  (alias for whereNotNull)
.whereExists("country")

// WHERE status IN ('active', 'pending')
.whereIn("status", List.of("active", "pending"))

// WHERE status NOT IN ('banned', 'deleted')
.whereNotIn("status", List.of("banned", "deleted"))

// WHERE price BETWEEN 10 AND 99
.whereBetween("price", 10, 99)

OR conditions

Every where* method has an orWhere* counterpart:

new QueryBuilder()
    .from("users")
    .whereEquals("role", "admin")
    .orWhereEquals("role", "moderator")
// → WHERE role = ? OR role = ?

Ordering 

// ORDER BY name ASC
.orderBy("name", true)

// ORDER BY created_at DESC
.orderBy("created_at", false)

// Multiple columns: ORDER BY level DESC, name ASC
.orderBy("level", false)
.orderBy("name", true)

Grouping 

// GROUP BY country
.groupBy("country")

// GROUP BY country, city
.groupBy("country", "city")

HAVING

Pass a raw SQL fragment with no value interpolation. Use static expressions only:

.groupBy("category")
.havingRaw("COUNT(*) > 5")

havingRaw accepts a raw SQL string. Never pass user-supplied input here. Use only static, known-safe expressions.

LIMIT and OFFSET 

// First 20 rows
.limit(20)

// Rows 41–60 (page 3 of 20)
.limit(20).offset(40)

Building the result

build() (returns a Query)

build() produces a Query object which can be passed to a SqlDialect later, used for in-memory filtering with QueryableStorage, or inspected directly:

Query q = new QueryBuilder()
    .from("products")
    .whereGreaterThan("stock", 0)
    .build();

buildSql() (returns a SqlResult)

buildSql() renders the Query immediately using the standard ANSI dialect. Use the overloads to specify a table or dialect explicitly:

// Uses table set via from(), standard dialect
SqlResult r1 = builder.buildSql();

// Explicit table, standard dialect
SqlResult r2 = builder.buildSql("orders");

// Explicit table and dialect
SqlResult r3 = builder.buildSql("orders", SqlDialect.MYSQL);

See SQL Dialects for the dialect options and the rendered identifier differences.

Subquery methods

QueryBuilder also exposes methods for embedding subqueries:

Method What it adds
whereInSubquery(col, subquery) WHERE col IN (SELECT ...)
whereEqualsSubquery(col, subquery) WHERE col = (SELECT ...)
whereExistsSubquery(subquery) WHERE EXISTS (SELECT ...)
whereNotExistsSubquery(subquery) WHERE NOT EXISTS (SELECT ...)
fromSubquery(subquery, alias) FROM (SELECT ...) AS alias
joinSubquery(subquery, alias, on) INNER JOIN (SELECT ...) AS alias ON ...
selectSubquery(subquery, alias) (SELECT ...) AS alias in SELECT clause

See Subqueries for full examples.

Security

Every value passed to a where* method is placed in the ? bind-parameter list of the rendered SqlResult. It is never concatenated into the SQL string.

// Safe even if userInput contains SQL metacharacters
String userInput = "'; DROP TABLE users; --";

SqlResult r = new QueryBuilder()
    .from("users")
    .whereEquals("name", userInput)
    .buildSql();

// r.getSql()        → "SELECT * FROM users WHERE name = ?"
// r.getParameters() → ["'; DROP TABLE users; --"]

Column names and table names are not parameterized. Always use static, known-safe strings for those arguments. Never forward user input as a column or table name.