# How Chat2DB Handles SQL Query Generation: A Deep Dive into DefaultSqlBuilder

> Discover how Chat2DB crafts SQL queries using DefaultSqlBuilder. Learn its fluent builder pattern for DDL, DML, and pagination statements combining metadata and dialect constants.

- Repository: [OtterMind/Chat2DB](https://github.com/OtterMind/Chat2DB)
- Tags: deep-dive
- Published: 2026-07-27

---

**Chat2DB centralizes SQL query generation through a fluent builder pattern implemented in `DefaultSqlBuilder`, which constructs DDL, DML, and pagination statements by combining metadata objects with dialect-specific constants.**

Chat2DB is an open-source database management tool developed by OtterMind that requires robust, database-agnostic SQL generation capabilities. The project implements a comprehensive SQL builder architecture in the `chat2db-community-spi` module, leveraging interfaces like `IDmlSqlBuilder` and `IDqlSqlBuilder` to standardize statement construction across supported database engines.

## The DefaultSqlBuilder Architecture

At the core of Chat2DB's SQL generation lies the **`DefaultSqlBuilder`** class found at [`chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlBuilder.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlBuilder.java). This class implements a comprehensive suite of builder interfaces including `ISqlBuilder`, `IIdentifierSqlBuilder`, `IDqlSqlBuilder`, `IDmlSqlBuilder`, `IDdlSqlBuilder`, `IDatabaseSqlBuilder`, `ISchemaSqlBuilder`, `ITableSqlBuilder`, and `IViewSqlBuilder`.

The implementation follows a **fluent API pattern** where accessor methods such as `identifier()`, `dql()`, `dml()`, and `ddl()` return `this`, allowing chained construction of SQL statements. This design enables a unified entry point for generating any statement type while maintaining type safety through interface segregation.

## DDL Generation: Creating and Modifying Schema Objects

The builder generates Data Definition Language (DDL) statements by concatenating static fragments defined in **[`DefaultSqlBuilderConstants.java`](https://github.com/OtterMind/Chat2DB/blob/main/DefaultSqlBuilderConstants.java)** with metadata from model objects like `Table`, `Column`, and `Database`.

For **table creation**, the `buildCreateTable()` method combines the `SQL_CREATE_TABLE` constant with column and index definitions. The implementation at `DefaultSqlBuilder.java:141‑182` handles complex scenarios including unique indexes and primary key constraints.

For **schema modifications**, `buildAlterTable()` delegates structural diffing to **[`DBStructUtils.java`](https://github.com/OtterMind/Chat2DB/blob/main/DBStructUtils.java)**. This utility compares old and new table structures to generate precise `ALTER TABLE` statements that add, drop, or modify columns while preserving existing data.

## DML Generation: Query and Data Manipulation

Data Manipulation Language (DML) construction follows a templated approach. The `buildTemplate()` method (implemented at `DefaultSqlBuilder.java:665‑779`) selects appropriate helper methods—`getInsertSql()`, `getUpdateSql()`, `getDeleteSql()`, or `getSelectSql()`—based on the requested operation type.

The **`buildByQueryResult()`** method (lines 222‑259) provides advanced functionality by reconstructing mutating SQL from `QueryResponse` objects. It inspects primary key columns using `Chat2DBContext.getDbMetaData()` to generate `UPDATE` or `DELETE` statements that reproduce data changes observed in query results.

### Pagination and Ordering

Chat2DB handles result set pagination through **`buildPageLimit()`**, which appends `LIMIT offset, pageSize` clauses (or `LIMIT pageSize` when offset is zero) as implemented at lines 241‑267.

For dynamic ordering, **`buildOrderBy()`** utilizes **JSqlParser** to parse existing SELECT statements, inject `OrderByElement` objects built from metadata, and return modified SQL. Error handling at lines 315‑321 captures parsing failures using the defined key `ERROR_KEY_SQL_BUILDER_ORDER_BY_FAILED` for consistent exception management.

## Dialect Extensibility

While `DefaultSqlBuilder` provides database-agnostic logic, specific dialects extend this base through inheritance. For example, **`MysqlSqlBuilder`** and **`PostgreSQLSqlBuilder`** override methods in their respective plugin modules to emit dialect-specific syntax—such as MySQL's backtick quoting or PostgreSQL's `LIMIT`/`OFFSET` variations—while reusing the common construction logic.

This architecture allows Chat2DB to support new database engines by implementing only the divergent portions of SQL generation, maintaining consistency in the core builder patterns.

## Practical Implementation Examples

### Building a Simple SELECT Statement

```java
DefaultSqlBuilder sqlBuilder = new DefaultSqlBuilder();
String sql = sqlBuilder.dql()
                       .buildSelectTable("test_db", "public", "users");

```

This returns `SELECT * FROM test_db.public.users`, demonstrating the fluent API for basic query construction.

### Creating a Table with Constraints

```java
Table table = new Table()
    .setName("employee")
    .setColumnList(Arrays.asList(
        new TableColumn()
            .setName("id")
            .setColumnType("BIGINT")
            .setNullable(0),
        new TableColumn()
            .setName("name")
            .setColumnType("VARCHAR(255)")
            .setNullable(1)
    ))
    .setIndexList(Collections.singletonList(
        new TableIndex()
            .setName("idx_name")
            .setType(IndexTypeEnum.UNIQUE.getName())
            .setColumnList(Collections.singletonList(
                new TableIndexColumn().setColumnName("name")
            ))
    ));

String createSql = new DefaultSqlBuilder()
    .dml()
    .buildCreateTable(table, new TableBuilderConfig());

```

This generates a complete `CREATE TABLE` statement with a unique index definition.

### Generating UPDATE from Query Results

```java
QueryResponse response = /* result of a SELECT */;
String updateSql = new DefaultSqlBuilder()
    .dml()
    .buildByQueryResult(response);

```

The method inspects row data and primary keys to produce `UPDATE ... SET ... WHERE ...` statements for each changed row.

### Adding Dynamic Ordering

```java
String original = "SELECT id, name FROM employee";
List<OrderBy> orderBys = Collections.singletonList(
    new OrderBy().setColumnName("name").setAsc(true)
);
String ordered = new DefaultSqlBuilder()
    .dql()
    .buildOrderBy(original, orderBys);

```

The resulting SQL becomes `SELECT id, name FROM employee ORDER BY name ASC`, with parsing handled by JSqlParser.

### Implementing Pagination

```java
String baseSql = "SELECT * FROM employee";
PageLimitRequest request = new PageLimitRequest()
    .setSql(baseSql)
    .setOffset(20)
    .setPageSize(10);
String paged = new DefaultSqlBuilder()
    .dql()
    .buildPageLimit(request);

```

This produces `SELECT * FROM employee LIMIT 20,10`.

## Summary

- **Chat2DB uses a centralized builder pattern** through `DefaultSqlBuilder` to generate all SQL statement types consistently across the application.
- **Interface segregation** allows specific handling of identifiers, DDL, DML, and database objects while maintaining a fluent API.
- **Helper utilities** like `DBStructUtils` handle complex schema diffs for ALTER TABLE generation.
- **JSqlParser integration** enables safe modification of existing SQL for ordering and pagination without string manipulation risks.
- **Dialect inheritance** allows database-specific plugins to override only necessary methods while inheriting common SQL construction logic.

## Frequently Asked Questions

### What is DefaultSqlBuilder in Chat2DB?

`DefaultSqlBuilder` is the core implementation class located at [`chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlBuilder.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlBuilder.java) that implements multiple SQL builder interfaces. It provides a fluent API for constructing DDL, DML, and DQL statements by combining metadata objects with SQL fragments defined in [`DefaultSqlBuilderConstants.java`](https://github.com/OtterMind/Chat2DB/blob/main/DefaultSqlBuilderConstants.java).

### How does Chat2DB build ALTER TABLE statements?

ALTER TABLE generation delegates structural comparison to **[`DBStructUtils.java`](https://github.com/OtterMind/Chat2DB/blob/main/DBStructUtils.java)**. This utility diffs the old and new `Table` metadata objects to determine which columns to add, drop, or modify, then constructs the appropriate ALTER statements. The base `DefaultSqlBuilder.buildAlterTable()` method orchestrates this process and handles the final SQL assembly.

### Can Chat2DB generate SQL from query results?

Yes, through the **`buildByQueryResult()`** method in `DefaultSqlBuilder`. This method accepts a `QueryResponse` object containing result set data and reconstructs INSERT, UPDATE, or DELETE statements that would reproduce the observed data state. It uses `Chat2DBContext.getDbMetaData()` to resolve column types and primary keys for accurate WHERE clause generation.

### How does Chat2DB support multiple database dialects?

Chat2DB supports multiple dialects through an inheritance model where dialect-specific classes like **`MysqlSqlBuilder`** and **`PostgreSQLSqlBuilder`** extend `DefaultSqlBuilder`. These subclasses override specific methods to emit dialect-specific syntax—such as identifier quoting styles or pagination clauses—while inheriting the core SQL construction logic from the base class.