In systems with many interrelated SQL tables, writing queries with the correct joins is complex and error-prone. Developers must manually trace foreign key relationships, determine join paths, and construct multi-table queries — work that the schema itself already encodes.
Kiss provides an automatic SQL query generator that solves this problem. The developer specifies what columns to select, what conditions to apply, and how to order the results. The system determines the join path automatically and produces the correct SQL.
All classes are in the org.kissweb.database package:
SchemaGraphModels the database schema as a graph where tables are nodes and foreign key relationships are edges. Finds the shortest join path between tables using BFS (breadth-first search).
QueryBuilderFluent API to specify SELECT, WHERE, ORDER BY, GROUP BY, HAVING, and
explicit joins. Uses a SchemaGraph to automatically determine the
required joins and produce the final SQL.
SchemaGraph.EdgeRepresents a single or composite foreign key relationship between two tables.
The schema graph must be built once at application startup. There are several ways to create it.
SchemaGraph graph = SchemaGraph.fromDatabase(conn);
This reads JDBC DatabaseMetaData for all tables and their foreign
keys. Composite (multi-column) foreign keys are automatically grouped
by constraint name. Works on all five supported databases (PostgreSQL,
MySQL, SQLite, SQL Server, Oracle).
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 call builds from the database and caches
SchemaGraph graph = SchemaGraph.fromDatabase(conn, "mySchema");
// Subsequent calls return the cached instance immediately
SchemaGraph graph = SchemaGraph.fromDatabase(conn, "mySchema");
// Clear cache if schema changes
SchemaGraph.clearSchemaCache("mySchema"); // one entry
SchemaGraph.clearSchemaCache(); // all entries
// 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");
The simplest approach is to create a QueryBuilder directly from
a Connection. The connection’s schema graph is used automatically,
and the no-argument fetchAll(), fetchOne(), and
fetchAllJSON() methods execute against that connection:
List<Record> records = conn.newQueryBuilder()
.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();
for (Record r : records) {
String name = r.getString("first_name");
String dept = r.getString("dept_name");
}
Alternatively, a QueryBuilder can be created with an explicit
SchemaGraph and a connection passed to the execute methods:
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
The QueryBuilder API includes many additional features not
covered in this overview, including:
startOr(), endOr(), startAnd(), endAnd())
whereIn(), whereNotIn(), whereExists(), whereNotExists())
join(), leftJoin(), rightJoin())
selectCount(), selectSum(), selectAvg(), selectMin(), selectMax())
with()
For the complete API reference, detailed examples, and edge case documentation, see the Kiss book and the JavaDocs. (See Creating JavaDocs.)
src/main/core/org/kissweb/database/SchemaGraph.javaSchema graph model: table/edge management, BFS path finding, schema caching.
src/main/core/org/kissweb/database/QueryBuilder.javaFluent query builder: SELECT, WHERE, ORDER BY, GROUP BY, HAVING, explicit joins, aggregate helpers, build, and execute methods.
src/test/core/org/kissweb/database/QueryBuilderTest.java108 unit tests covering all functionality.