7.1.1 Building the Schema Graph ¶

The schema graph must be built once at application startup. There are several ways to create it.

• From Live Database Metadata
• Programmatic Declaration
• Hybrid
• Cached (for Fast Startup)

SchemaGraph graph = SchemaGraph.fromDatabase(conn);

SchemaGraph graph = new SchemaGraph();
graph.addForeignKey("employee", "department_id",
                    "department", "department_id");
graph.addForeignKey("order_line",
    new String[]{"order_id", "product_id"},
    "order_product",
    new String[]{"order_id", "product_id"});

SchemaGraph graph = SchemaGraph.fromDatabase(conn);
graph.addForeignKey("audit_log", "user_id",
                    "employee", "employee_id");

// First run: read from database, save cache
SchemaGraph graph = SchemaGraph.fromDatabase(conn);
graph.saveToFile("schema-cache.txt");

// Subsequent runs: load from cache (sub-millisecond)
SchemaGraph graph = SchemaGraph.loadFromFile("schema-cache.txt");

7.1.2 Building and Executing a Query ¶

List<Record> records = new QueryBuilder(graph)
    .select("employee.first_name", "employee.last_name")
    .select("department.name AS dept_name")
    .select("building.address")
    .where("project.project_id = ?", projectId)
    .where("employee.active = 'Y'")
    .orderBy("employee.last_name")
    .orderBy("employee.first_name")
    .fetchAll(conn);

for (Record r : records) {
    String name = r.getString("first_name");
    String dept = r.getString("dept_name");
}

The system automatically generates SQL such as:

SELECT employee.first_name, employee.last_name,
       department.name AS dept_name, building.address
FROM project
JOIN project_assignment
  ON project_assignment.project_id = project.project_id
JOIN employee
  ON project_assignment.employee_id = employee.employee_id
JOIN department
  ON employee.department_id = department.department_id
JOIN building
  ON department.building_id = building.building_id
WHERE project.project_id = ?
  AND employee.active = 'Y'
ORDER BY employee.last_name, employee.first_name

7.2.1 Construction ¶

Method: SchemaGraph () ¶

Create an empty schema graph for programmatic population.

Static Method: SchemaGraph.fromDatabase conn ¶

Build a schema graph by reading all tables and foreign keys from JDBC DatabaseMetaData. Called once at application startup.

Parameters:

• conn — a Kiss Connection object

Returns: a populated SchemaGraph

Throws: SQLException if database metadata cannot be read

Static Method: SchemaGraph.loadFromFile path ¶

Load a schema graph from a cache file previously saved with saveToFile().

Parameters:

• path — path to the cache file

Returns: a populated SchemaGraph

Throws: IOException if the file cannot be read or has an invalid format

7.2.2 Populating ¶

Method: addTable tableName ¶

Declare a table in the graph. This is optional — tables are automatically created when referenced by addForeignKey.

Method: addForeignKey fromTable fromColumn toTable toColumn ¶

Declare a single-column foreign key relationship between two tables. Both tables are automatically added to the graph if not already present. Duplicate edges are ignored.

Parameters:

• fromTable — the child table containing the FK column
• fromColumn — the FK column in the child table
• toTable — the parent/referenced table
• toColumn — the referenced column (usually the primary key)

Method: addForeignKey fromTable fromColumns[] toTable toColumns[] ¶

Declare a composite (multi-column) foreign key relationship. Both tables are automatically added to the graph if not already present. Duplicate edges are ignored.

Parameters:

• fromTable — the child table containing the FK columns
• fromColumns — array of FK column names in the child table
• toTable — the parent/referenced table
• toColumns — array of referenced column names

7.2.3 Querying the Graph ¶

Method: hasTable tableName ¶

Check whether a table exists in the graph.

Returns: true if the table has been added

Method: getTables ¶

Return all table names in the graph.

Returns: an unmodifiable Set<String> of table names

Method: getEdges tableName ¶

Return all foreign key edges incident on a given table.

Returns: unmodifiable list of Edge objects, or an empty list if the table is not in the graph

Method: findJoinPath tables rootTable ¶

Find the shortest join path connecting all the given tables using BFS. The algorithm picks the root table as the starting point, then greedily adds the shortest BFS path from the current tree to each remaining table (a standard Steiner tree approximation).

Parameters:

• tables — Set<String> of tables that must be connected
• rootTable — the preferred root table (used as FROM), or null

Returns: an ordered list of Edge objects representing the joins

Throws: SQLException if no path exists between some tables

7.2.4 Schema Caching ¶

Method: saveToFile path ¶

Write the schema graph to a text file for later loading.

The cache file format is line-based text:

# SchemaGraph cache v1
TABLE employee
TABLE department
FK employee department_id department department_id
FK order_line order_id,product_id order_product order_id,product_id

Static Method: SchemaGraph.loadFromFile path ¶

Read a schema graph from a cache file previously saved with saveToFile(). Comments (lines starting with #) and blank lines are allowed.

7.2.5 Edge Inner Class ¶

SchemaGraph.Edge represents a foreign key relationship (single or composite).

Method: getFromTable ¶

Return the child table name.

Method: getToTable ¶

Return the parent/referenced table name.

Method: getFromColumn ¶

Return the first (or only) FK column name.

Method: getToColumn ¶

Return the first (or only) referenced column name.

Method: getFromColumns ¶

Return all FK column names as List<String>.

Method: getToColumns ¶

Return all referenced column names as List<String>.

Method: isComposite ¶

Return true if this is a composite (multi-column) foreign key.

Method: buildOnCondition fromAlias toAlias ¶

Generate the ON condition clause for this edge. For composite keys, column pairs are separated with AND.

Parameters:

• fromAlias — alias for the from table, or null to use the table name
• toAlias — alias for the to table, or null to use the table name

Returns: the ON condition string (e.g. employee.dept_id = department.dept_id)

All table and column names are case-insensitive (stored lowercase internally).

7.3.1 SELECT ¶

Method: select tableColumn ¶

Add a column to the SELECT list. Accepts formats such as "table.column", "table.column AS alias", "COUNT(table.column)", and "SUM(table.column) AS total".

Returns: this for chaining

Method: select tableColumns... ¶

Add multiple columns to the SELECT list.

Returns: this for chaining

Method: distinct ¶

Enable SELECT DISTINCT.

Returns: this for chaining

7.3.2 Aggregate Helpers ¶

The following methods add aggregate function calls to the SELECT list. Each has a variant without an alias and a variant with an alias.

Method: selectCount tableColumn ¶

Method: selectCount tableColumn alias ¶

Add COUNT(table.column) or COUNT(table.column) AS alias.

Method: selectSum tableColumn ¶

Method: selectSum tableColumn alias ¶

Add SUM(table.column) or SUM(table.column) AS alias.

Method: selectAvg tableColumn ¶

Method: selectAvg tableColumn alias ¶

Add AVG(table.column) or AVG(table.column) AS alias.

Method: selectMin tableColumn ¶

Method: selectMin tableColumn alias ¶

Add MIN(table.column) or MIN(table.column) AS alias.

Method: selectMax tableColumn ¶

Method: selectMax tableColumn alias ¶

Add MAX(table.column) or MAX(table.column) AS alias.

7.3.3 WHERE ¶

Method: where condition params... ¶

Add a WHERE condition. Multiple calls at the same level are combined with the current group’s operator (AND by default). Use ? for parameter placeholders.

Example:

.where("employee.active = ?", "Y")
.where("employee.salary > ?", 50000)

7.3.4 OR / AND Grouping ¶

By default, multiple where() calls are combined with AND. Use startOr() and endOr() to group conditions with OR:

String sql = new QueryBuilder(graph)
    .select("employee.first_name", "employee.last_name")
    .startOr()
        .where("employee.department_id = ?", 10)
        .where("employee.department_id = ?", 20)
    .endOr()
    .where("employee.active = 'Y'")
    .build();
// WHERE (department_id = ? OR department_id = ?)
//   AND employee.active = 'Y'

OR groups can be nested with explicit AND groups:

String sql = new QueryBuilder(graph)
    .select("employee.first_name")
    .startOr()
        .where("employee.salary > ?", 100000)
        .startAnd()
            .where("employee.department_id = ?", 5)
            .where("employee.level = ?", "senior")
        .endAnd()
    .endOr()
    .build();
// WHERE (salary > ? OR (department_id = ? AND level = ?))

Conditions can be added conditionally (the most common pattern in real codebases):

QueryBuilder qb = new QueryBuilder(graph)
    .select("employee.first_name", "employee.last_name");
qb.startOr();
if (dept1 != null) qb.where("employee.department_id = ?", dept1);
if (dept2 != null) qb.where("employee.department_id = ?", dept2);
qb.endOr();

Empty OR/AND groups (no conditions inside) are silently omitted from the SQL.

Method: startOr ¶

Begin an OR group — conditions added between startOr() and endOr() are combined with OR.

Method: endOr ¶

End the current OR group and return to the parent group.

Method: startAnd ¶

Begin an AND group. Useful inside an OR group to explicitly group conditions with AND.

Method: endAnd ¶

End the current AND group and return to the parent group.

7.3.5 Subqueries in WHERE ¶

• IN / NOT IN with Subquery
• EXISTS / NOT EXISTS
• Subquery Methods

QueryBuilder sub = new QueryBuilder(graph)
    .select("project_assignment.employee_id")
    .where("project_assignment.project_id = ?", projectId);

String sql = new QueryBuilder(graph)
    .select("employee.first_name", "employee.last_name")
    .whereIn("employee.employee_id", sub)
    .build();
// WHERE employee.employee_id IN
//   (SELECT project_assignment.employee_id ...)

String sql = new QueryBuilder(graph)
    .select("employee.first_name")
    .whereIn("employee.employee_id",
        "SELECT person_id FROM timesheet WHERE work_date = ?",
        20260101)
    .build();

QueryBuilder sub = new QueryBuilder(graph)
    .select("1")
    .where("project_assignment.employee_id = employee.employee_id")
    .where("project_assignment.project_id = ?", 42);

String sql = new QueryBuilder(graph)
    .select("employee.first_name")
    .whereExists(sub)
    .build();
// WHERE EXISTS (SELECT 1 FROM project_assignment WHERE ...)

String sql = new QueryBuilder(graph)
    .select("department.name")
    .whereExists("SELECT 1 FROM employee "
       + "WHERE employee.department_id = department.department_id "
       + "AND employee.active = 'Y'")
    .build();

7.3.6 ORDER BY ¶

Method: orderBy tableColumn ¶

Add an ascending ORDER BY column.

Method: orderByDesc tableColumn ¶

Add a descending ORDER BY column.

7.3.7 GROUP BY / HAVING ¶

Method: groupBy tableColumn ¶

Add a GROUP BY column.

Method: having condition params... ¶

Add a HAVING condition. Multiple calls are combined with AND.

7.3.8 Explicit Joins ¶

Explicit joins give the developer control over exactly which columns are used for a join. They are necessary for self-joins, and useful when multiple foreign key paths exist between two tables and the developer needs to select a specific one.

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN

7.3.9 LIMIT ¶

Method: limit max ¶

Limit the number of rows returned. Uses Connection.limit() to produce database-specific SQL.

7.3.10 Common Table Expressions (CTEs) ¶

Use with() to prepend CTE blocks to the query:

String sql = new QueryBuilder(graph)
    .with("last_timesheet",
          "SELECT person_id, MAX(work_date) work_date "
        + "FROM timesheet WHERE billable = 'Y' "
        + "GROUP BY person_id")
    .select("employee.first_name", "employee.last_name")
    .select("last_timesheet.work_date")
    .leftJoin("employee", "employee_id",
              "last_timesheet", "person_id")
    .build();
// WITH last_timesheet AS (SELECT ...) SELECT ...

Multiple CTEs are supported:

String sql = new QueryBuilder(graph)
    .with("cte1", "SELECT employee_id FROM employee "
        + "WHERE active = 'Y'")
    .with("cte2", "SELECT employee_id, MAX(work_date) "
        + "last_date FROM timesheet GROUP BY employee_id")
    .select("cte1.employee_id")
    .select("cte2.last_date")
    .leftJoin("cte1", "employee_id",
              "cte2", "employee_id")
    .build();
// WITH cte1 AS (...), cte2 AS (...) SELECT ...

A CTE can also use a QueryBuilder as its body:

QueryBuilder cteSub = new QueryBuilder(graph)
    .select("employee.employee_id")
    .where("employee.active = ?", "Y");

String sql = new QueryBuilder(graph)
    .with("active_emps", cteSub)
    .select("active_emps.employee_id")
    .build();

Method: with name rawSQL params... ¶

Add a CTE with a raw SQL body and optional parameters.

Method: with name subquery ¶

Add a CTE whose body is built from a QueryBuilder.

CTE names are treated like aliases — they are excluded from the schema graph path finding. CTE parameters appear before WHERE parameters in the final parameter list.

7.3.11 UNION ¶

Combine multiple queries with union() or unionAll():

QueryBuilder q1 = new QueryBuilder(graph)
    .select("employee.first_name", "employee.last_name")
    .select("'Employee' AS person_type")
    .where("employee.active = 'Y'");

QueryBuilder q2 = new QueryBuilder(graph)
    .select("employee.first_name", "employee.last_name")
    .select("'Contractor' AS person_type")
    .where("employee.active = 'N'");

String sql = q1.unionAll(q2)
    .orderBy("last_name")
    .build();
// SELECT ... UNION ALL SELECT ... ORDER BY last_name

Method: union other ¶

Append a UNION (removes duplicates) with another QueryBuilder.

Method: unionAll other ¶

Append a UNION ALL (keeps duplicates) with another QueryBuilder.

ORDER BY and LIMIT on a UNION apply to the combined result. Multiple unions can be chained. Parameters from all parts are merged in the correct positional order.

7.3.12 Build and Execute ¶

Method: build ¶

Generate the SQL query string. After calling this method, getParameters() returns the ordered parameter values.

Returns: the generated SQL string

Throws: SQLException if no join path exists between the referenced tables, or if no SELECT columns were specified

Method: getParameters ¶

Return the ordered list of ? placeholder values. Must be called after build().

Returns: unmodifiable List<Object>

Throws: IllegalStateException if called before build()

Method: fetchAll conn ¶

Build and execute the query, returning all results as Record objects.

Returns: List<Record>

Throws: Exception on database or query building errors

Method: fetchOne conn ¶

Build and execute the query, returning the first result or null.

Returns: Record or null

Throws: Exception on database or query building errors

Method: fetchAllJSON conn ¶

Build and execute the query, returning results as a JSONArray.

Returns: JSONArray

Throws: Exception on database or query building errors

7.4.1 Basic Multi-Table Query ¶

String sql = new QueryBuilder(graph)
    .select("employee.first_name", "employee.last_name")
    .select("department.name")
    .select("building.address")
    .where("employee.active = ?", "Y")
    .orderBy("employee.last_name")
    .build();

The builder automatically determines the join path from employee through department to building based on the foreign key relationships in the schema graph.

7.4.2 Self-Join (Employee to Manager) ¶

String sql = new QueryBuilder(graph)
    .select("employee.first_name")
    .select("mgr.first_name AS manager_name")
    .join("employee", "manager_id",
          "employee", "employee_id", "mgr")
    .build();
// ... JOIN employee mgr
//       ON employee.manager_id = mgr.employee_id

7.4.3 Multiple Self-Joins ¶

String sql = new QueryBuilder(graph)
    .select("employee.first_name")
    .select("mgr.first_name AS manager")
    .select("mentor.first_name AS mentor_name")
    .join("employee", "manager_id",
          "employee", "employee_id", "mgr")
    .join("employee", "mentor_id",
          "employee", "employee_id", "mentor")
    .build();

7.4.4 LEFT JOIN with Aggregate ¶

String sql = new QueryBuilder(graph)
    .select("department.name")
    .selectCount("employee.employee_id", "emp_count")
    .leftJoin("department", "department_id",
              "employee", "department_id")
    .groupBy("department.name")
    .build();
// SELECT department.name,
//        COUNT(employee.employee_id) AS emp_count
// FROM department
// LEFT JOIN employee
//   ON department.department_id = employee.department_id
// GROUP BY department.name

7.4.5 Multiple Aggregates ¶

String sql = new QueryBuilder(graph)
    .select("department.name")
    .selectCount("employee.employee_id", "emp_count")
    .selectAvg("employee.salary", "avg_salary")
    .selectSum("employee.salary", "total_salary")
    .selectMin("employee.salary", "min_salary")
    .selectMax("employee.salary", "max_salary")
    .groupBy("department.name")
    .having("COUNT(employee.employee_id) > ?", 5)
    .build();

7.4.6 Composite Foreign Key Join ¶

String sql = new QueryBuilder(graph)
    .select("order_line.qty")
    .select("pricing.price")
    .join("order_line",
          new String[]{"product_id", "region_id"},
          "pricing",
          new String[]{"product_id", "region_id"}, null)
    .build();
// ... JOIN pricing
//   ON order_line.product_id = pricing.product_id
//  AND order_line.region_id = pricing.region_id

7.4.7 Picking a Specific Foreign Key Path ¶

When a table has multiple foreign keys pointing to the same table, use an explicit join to select which one:

// employee has both department_id and home_department_id
String sql = new QueryBuilder(graph)
    .select("employee.name")
    .select("department.name AS home_dept")
    .join("employee", "home_department_id",
          "department", "department_id")
    .build();

7.4.8 Schema Caching for Fast Startup ¶

// First run: read from database, save cache
SchemaGraph graph = SchemaGraph.fromDatabase(conn);
graph.saveToFile("schema-cache.txt");

// Subsequent runs: load from cache (sub-millisecond)
SchemaGraph graph = SchemaGraph.loadFromFile("schema-cache.txt");

// Hybrid: load cache, then add extra relationships
SchemaGraph graph = SchemaGraph.loadFromFile("schema-cache.txt");
graph.addForeignKey("audit_log", "user_id",
                    "employee", "employee_id");

7.4.9 OR Conditions with Dynamic Building ¶

QueryBuilder qb = new QueryBuilder(graph)
    .select("employee.first_name", "employee.last_name");
qb.startOr();
if (dept1 != null)
    qb.where("employee.department_id = ?", dept1);
if (dept2 != null)
    qb.where("employee.department_id = ?", dept2);
qb.endOr();
qb.where("employee.active = 'Y'");
String sql = qb.build();
// WHERE (department_id = ? OR department_id = ?)
//   AND employee.active = 'Y'

7.4.10 Subquery in WHERE ¶

QueryBuilder sub = new QueryBuilder(graph)
    .select("project_assignment.employee_id")
    .where("project_assignment.project_id = ?", projId);

String sql = new QueryBuilder(graph)
    .select("employee.first_name")
    .where("employee.active = 'Y'")
    .whereIn("employee.employee_id", sub)
    .build();
// WHERE employee.active = 'Y'
//   AND employee.employee_id IN
//     (SELECT project_assignment.employee_id ...)

7.4.11 CTE with LEFT JOIN ¶

String sql = new QueryBuilder(graph)
    .with("last_ts",
          "SELECT person_id, MAX(work_date) work_date "
        + "FROM timesheet WHERE billable = 'Y' "
        + "GROUP BY person_id")
    .select("employee.first_name")
    .select("last_ts.work_date")
    .leftJoin("employee", "employee_id",
              "last_ts", "person_id")
    .build();

7.4.12 UNION ALL with ORDER BY ¶

QueryBuilder q1 = new QueryBuilder(graph)
    .select("employee.first_name")
    .select("'Active' AS status")
    .where("employee.active = 'Y'");

QueryBuilder q2 = new QueryBuilder(graph)
    .select("employee.first_name")
    .select("'Inactive' AS status")
    .where("employee.active = 'N'");

String sql = q1.unionAll(q2)
    .orderBy("first_name")
    .build();

7.5.1 Join Path Finding (BFS) ¶

When build() is called, the query builder:

1. Collects all distinct table names mentioned in SELECT, WHERE, ORDER BY, GROUP BY, and HAVING clauses.
2. Picks the root table (first WHERE table, or first SELECT table).
3. For each remaining table, runs BFS from the set of already-connected tables to find the shortest path through the schema graph.
4. Merges overlapping paths (deduplicates edges). The result is a tree of joins connecting all required tables.
5. If any table is unreachable, throws SQLException naming the disconnected tables.

BFS is optimal because all foreign key edges have equal weight. It runs in O(V+E) time, which is sub-millisecond even for 300+ tables.

7.5.2 Explicit Join Isolation ¶

Tables that are the target (toTable) of an explicit join(), leftJoin(), or rightJoin() are excluded from the automatic join graph traversal. This means:

• Explicit joins work even for tables not in the schema graph.
• Self-joins are handled correctly (same table as both from and to, with an alias).
• The developer controls exactly how explicit-join tables are reached.

7.5.3 Join Type Rules ¶

• Auto-discovered joins (from schema graph path finding) are always INNER JOIN.
• Explicit joins use the type specified: join() produces JOIN, leftJoin() produces LEFT JOIN, and rightJoin() produces RIGHT JOIN.