SQL Forge

English | 中文

SQL Forge — a Spring Boot database framework providing JSON CRUD API, type-safe entity operations, SQL template engine, Apache Calcite cross-database federated queries, Amis low-code visual management, and MCP (Model Context Protocol) server for AI tool integration. Ready to use, import on demand.

---

Quick Start

Import Dependencies

Import the starters you need:

<!-- Core database operations (required) -->
<dependency>
    <groupId>io.github.wb04307201</groupId>
    <artifactId>sql-forge-spring-boot-starter</artifactId>
    <version>1.5.12</version>
</dependency>

<!-- Calcite cross-database federated queries (optional) -->
<dependency>
    <groupId>io.github.wb04307201</groupId>
    <artifactId>sql-forge-calcite-spring-boot-starter</artifactId>
    <version>1.5.12</version>
</dependency>

<!-- Amis templates + Web Console (optional) -->
<dependency>
    <groupId>io.github.wb04307201</groupId>
    <artifactId>sql-forge-web-spring-boot-starter</artifactId>
    <version>1.5.12</version>
</dependency>

For @Id, @Table, @Column annotations in the Entity module, additionally import:

<dependency>
    <groupId>jakarta.persistence</groupId>
    <artifactId>jakarta.persistence-api</artifactId>
    <version>3.2.0</version>
</dependency>

Starter Overview

Starter	Description
sql-forge-spring-boot-starter	Core starter: database executor, JSON CRUD API, type-safe entity operations (Entity), SQL template engine, Record operation aspects, built-in user authentication (Session + ApiKey)
sql-forge-calcite-spring-boot-starter	Apache Calcite-based cross-database federated query executor for MySQL, PostgreSQL, and more
sql-forge-web-spring-boot-starter	Amis low-code template management API, Web Console UI, user/role management
sql-forge-mcp	Model Context Protocol (MCP) server for AI tool integration — exposes database metadata, SQL execution, and Amis template management as MCP tools via stdio

---

1. sql-forge-spring-boot-starter (Core Starter)

Provides core database capabilities including executor management, JSON CRUD API, Entity chain operations, SQL template engine, and Record aspect extensions.

1.1 SQL Executor

sql-forge-spring-boot-starter automatically registers an executor named database, directly using the DataSource already configured in your Spring project.

sql:
  forge:
    schemata:  # Configure schema
      - PUBLIC

Custom Executor

Implement the IExecutor interface and register it as a Spring Bean to extend with custom executors.

@Component
public class MyCustomExecutor implements IExecutor {
    @Override
    public String getExecutorName() {
        return "myCustom";
    }
    // ... implement other methods
}

1.2 Direct Database API

Provides the ability to execute SQL directly (disabled by default, must be manually enabled).

sql:
  forge:
    api:
      database:
        enabled: true       # Enable direct database API
        select-only: true   # true=SELECT only, false=allow all operations

• POST /sql/forge/api/database/execute?executorName=database - Execute SQL

1.3 JSON CRUD API

Allows frontend to operate the database without writing backend code — describe the desired data structure and operations via JSON, and the backend automatically generates and executes the corresponding SQL.

• Request Path: sql/forge/api/json/{method}/{tableName}?executorName={executorName}
• Request Method: POST
• Content Type: application/json
• Path Parameters:
  • {method}: Operation type (delete, insert, select, selectPage, update)
  • {tableName}: Database table name
  • {executorName}: Executor name, defaults to database if not provided

delete method

{
  "@where": [
    {
      "column": "field_name",
      "condition": "condition_type",
      "value": "value"
    }
  ],
  "@with_select": {
    // Query JSON after deletion
  }
}

Parameters

• @where: Delete condition array
  • column: Field name to match
  • condition: Condition type (EQ, NOT_EQ, GT, LT, GTEQ, LTEQ, LIKE, NOT_LIKE, LEFT_LIKE, RIGHT_LIKE, BETWEEN, NOT_BETWEEN, IN, NOT_IN, IS_NULL, IS_NOT_NULL)
  • value: Value to match
• @with_select: Optional, execute a query after deletion

insert method

{
  "@set": {
    "field1": "value1",
    "field2": "value2"
  },
  "@with_select": {
    // Query JSON after insertion
  }
}

Parameters

• @set: Key-value pairs of fields and values to insert, at least one field required
• @with_select: Optional, execute a query after insertion

select method

{
  "@column": ["field1", "field2"],
  "@where": [
    {
      "column": "field_name",
      "condition": "condition_type",
      "value": "value"
    }
  ],
  "@join": [
    {
      "type": "JOIN type",
      "joinTable": "join_table_name",
      "on": "join_condition"
    }
  ],
  "@order": ["field_name ASC", "field_name DESC"],
  "@group": ["field_name"],
  "@distince": false
}

Parameters

• @column: Array of fields to query, queries all fields if empty
• @where: Query condition array
• @join: Join condition array
• @order: Sort field array
• @group: Group by field array
• @distince: Whether to deduplicate

selectPage method

{
  "@column": ["field1", "field2"],
  "@where": [
    {
      "column": "field_name",
      "condition": "condition_type",
      "value": "value"
    }
  ],
  "@page": {
    "pageIndex": 0,
    "pageSize": 10
  },
  "@join": [
    {
      "type": "JOIN type",
      "joinTable": "join_table_name",
      "on": "join_condition"
    }
  ],
  "@order": ["field_name ASC", "field_name DESC"],
  "@distince": false
}

Parameters

• @column: Array of fields to query, queries all fields if empty
• @where: Query condition array
• @page: Pagination parameters (pageIndex starts from 0, pageSize is page size)
• @join: Join condition array
• @order: Sort field array
• @distince: Whether to deduplicate

update method

{
  "@set": {
    "field1": "new_value1",
    "field2": "new_value2"
  },
  "@where": [
    {
      "column": "field_name",
      "condition": "condition_type",
      "value": "value"
    }
  ],
  "@with_select": {
    // Query JSON after update
  }
}

Parameters

• @set: Key-value pairs of fields and new values, at least one field required
• @where: Update condition array
• @with_select: Optional, execute a query after update

Examples

1. Query

POST http://localhost:8080/sql/forge/api/json/select/orders o
Content-Type: application/json

{
  "@column": [
    "u.username",
    "o.total_amount",
    "p.name               AS product_name",
    "oi.unit_price",
    "oi.quantity",
    "p.price"
  ],
  "@where": [
    {
      "column": "u.username",
      "condition": "IS_NOT_NULL",
      "value": null
    }
  ],
  "@join": [
    {
      "type": "JOIN",
      "joinTable": "users u",
      "on": "o.user_id = u.id"
    },
    {
      "type": "JOIN",
      "joinTable": "order_items oi",
      "on": "o.id = oi.order_id"
    },
    {
      "type": "JOIN",
      "joinTable": "products p",
      "on": "oi.product_id = p.id"
    }
  ],
  "@order": [
    "o.order_date"
  ]
}

2. Paginated Query

Simply add the @page parameter to a query JSON:

POST http://localhost:8080/sql/forge/api/json/selectPage/orders o
Content-Type: application/json

{
  "@column": ["o.total_amount", "p.name AS product_name"],
  "@join": [
    {
      "type": "JOIN",
      "joinTable": "products p",
      "on": "o.product_id = p.id"
    }
  ],
  "@order": ["o.order_date"],
  "@page": {
    "pageIndex": 0,
    "pageSize": 5
  }
}

3. Insert

POST http://localhost:8080/sql/forge/api/json/insert/users
Content-Type: application/json

{
  "@set": {
    "id": "26a05ba3-913d-4085-a505-36d40021c8d1",
    "username": "wb04307201",
    "password": "123456",
    "enabled": true,
    "category": "user"
  },
  "@with_select": {
    "@where": [
      {
        "column": "id",
        "condition": "EQ",
        "value": "26a05ba3-913d-4085-a505-36d40021c8d1"
      }
    ]
  }
}

4. Update

POST http://localhost:8080/sql/forge/api/json/update/users
Content-Type: application/json

{
  "@set": {
    "password": "newpassword"
  },
  "@where": [
    {
      "column": "id",
      "condition": "EQ",
      "value": "26a05ba3-913d-4085-a505-36d40021c8d1"
    }
  ],
  "@with_select": {
    "@where": [
      {
        "column": "id",
        "condition": "EQ",
        "value": "26a05ba3-913d-4085-a505-36d40021c8d1"
      }
    ]
  }
}

5. Delete

POST http://localhost:8080/sql/forge/api/json/delete/users
Content-Type: application/json

{
  "@where": [
    {
      "column": "id",
      "condition": "EQ",
      "value": "26a05ba3-913d-4085-a505-36d40021c8d1"
    }
  ],
  "@with_select": {
    "@where": [
      {
        "column": "id",
        "condition": "EQ",
        "value": "26a05ba3-913d-4085-a505-36d40021c8d1"
      }
    ]
  }
}

Pre-execution Aspects

Customize JSON adjustments before method execution by implementing the IBeforeRecordExecutor interface — for password encryption, auto-timestamps, access control, logging, auditing, etc.

For example, logging on Insert:

@Slf4j
@Component
public class LogInsertExecute implements IBeforeRecordExecutor<Insert> {
  @Override
  public Boolean support(String tableName, Insert insert) {
    return true;
  }

  @Override
  public Insert before(String tableName, Insert insert) {
    log.info("LogInsertExecute tableName: {} record: {}", tableName, insert);
    return insert;
  }
}

1.4 Authentication & ApiKey

The core starter includes a built-in user authentication system. All APIs require authentication by default, supporting both Session login and ApiKey — either one is sufficient to access.

ApiKey Authentication

Pass the ApiKey via the X-Api-Key request header to access all APIs without logging in:

sql:
  forge:
    api-keys:                # ApiKey list (empty by default, meaning ApiKey authentication is disabled)
      - sk-your-api-key-here
      - sk-another-key

Request example:

GET http://localhost:8080/sql/forge/api/json/select/users
X-Api-Key: sk-your-api-key-here

Session Login Authentication

Obtain a Session via the login endpoint; subsequent requests automatically carry the Session cookie:

• POST /sql/forge/api/auth/login - User login (Body: {"username": "admin", "password": "admin123"})
• POST /sql/forge/api/auth/logout - User logout
• GET /sql/forge/api/auth/status - Get current login status
• GET /sql/forge/api/auth/user - Get current user info

Default admin account: admin / admin123

Authentication Priority

Request → Valid ApiKey? → Allow
        → Session logged in? → Allow
        → Neither → 401 Denied

Whitelisted paths (login endpoints, static resources) are accessible without authentication.

1.5 SQL Template Engine

Provides SQL template functionality supporting conditionals (<if>), loops (<foreach>), and variable binding (#{var}), dynamically generating and executing SQL based on parameters.

Template Management Endpoints

• PUT /sql/forge/api/template/sql - Save/Update SQL template
  • id: Template ID
  • executorName: Executor name, defaults to database
  • context: Template content
• GET /sql/forge/api/template/sql/{id} - Get SQL template by ID
• GET /sql/forge/api/template/sql - Get SQL template list
• DELETE /sql/forge/api/template/sql/{id} - Delete SQL template by ID
• POST /sql/forge/api/template/sql/{id} - Execute SQL template by ID (Body is a parameter Map)

Example

Template configuration:

PUT http://localhost:8080/sql/forge/api/template/sql
content-type: application/json

{
    "id": "sql-template-database",
    "type": "templateSql",
    "executorName": "database",
    "context": "SELECT * FROM users WHERE 1=1\r\n<if test=\"name != null && name != ''\">AND username = #{name}</if>\r\n<if test=\"ids != null && !ids.isEmpty()\"><foreach collection=\"ids\" item=\"id\" open=\"AND id IN (\" separator=\",\" close=\")\">#{id}</foreach></if>\r\n<if test=\"(name == null || name == '') && (ids == null || ids.isEmpty()) \">AND 0=1</if>\r\nORDER BY username DESC"
}

Execute template:

POST http://localhost:8080/sql/forge/api/template/sql/sql-template-database
content-type: application/json

{
  "name": "alice",
  "ids": null
}

Response:

[
  {
    "ID": "1",
    "USERNAME": "alice"
  }
]

Persistent Templates

Uses in-memory storage by default. Implement ITemplateSqlStorage for custom persistence.

1.6 Entity Module

Provides type-safe entity operation builders with compile-time safe field references via Lambda expressions, supporting chain calls.

• Entity — Static utility class providing select/insert/update/delete/save/selectPage entry points
• EntityExecutor — Executes builder database operations

Features

• Chain calls for concise code
• Lambda expressions for compile-time field reference checking
• Builder pattern for flexible query condition configuration
• Unified database operation entry point

Usage Examples

Define a user entity class:

@Data
@Table(name = "users")
public class User {
    @Id
    private String id;

    @Column(name = "username")
    private String username;

    @Column(name = "password")
    private String password;

    @Column(name = "category")
    private String category;
}

Database operations using Entity:

@Autowired
private EntityExecutor entityExecutor;

// Query operation
EntitySelect<User> select = Entity.select(User.class)
                .distinct(true)
                .columns(User::getId, User::getUsername, User::getCategory)
                .orders(User::getUsername)
                .in(User::getUsername, "alice", "bob");
List<User> users = entityExecutor.run(select);

// Paginated query operation
EntitySelectPage<User> selectPage = Entity.selectPage(User.class)
        .columns(User::getId, User::getUsername, User::getCategory)
        .orders(User::getUsername)
        .page(0, 10);
SelectPageResult<User> result = entityExecutor.run(selectPage);

// Insert operation
EntityInsert<User> insert = Entity.insert(User.class)
        .set(User::getId, UUID.randomUUID().toString())
        .set(User::getUsername, "wb04307201")
        .set(User::getPassword, "123456");
entityExecutor.run(insert);

// Update operation
EntityUpdate<User> update = Entity.update(User.class)
        .set(User::getPassword, "newpassword")
        .eq(User::getId, id);
int count = entityExecutor.run(update);

// Delete operation
EntityDelete<User> delete = Entity.delete(User.class)
        .eq(User::getId, id);
count = entityExecutor.run(delete);

// Object save (auto-detects insert or update)
User user = new User();
user.setUsername("wb04307201");
user.setPassword("123456");
user = entityExecutor.run(Entity.save(user));  // id is null, performs insert

user.setPassword("newpassword");
user = entityExecutor.run(Entity.save(user));  // id is not null, performs update

// Object delete
count = entityExecutor.run(Entity.delete(user));

Query Builder Reference

1. Column Selection

• column(SFunction<T, ?> column) - Select single column
• columns(SFunction<T, ?>... columns) - Select multiple columns

2. Query Conditions

• eq(SFunction<T, ?> column, Object value) - Equals
• neq(SFunction<T, ?> column, Object value) - Not equals
• gt(SFunction<T, ?> column, Object value) - Greater than
• lt(SFunction<T, ?> column, Object value) - Less than
• gteq(SFunction<T, ?> column, Object value) - Greater than or equals
• lteq(SFunction<T, ?> column, Object value) - Less than or equals
• like(SFunction<T, ?> column, Object value) - Like
• notLike(SFunction<T, ?> column, Object value) - Not like (NOT LIKE)
• leftLike(SFunction<T, ?> column, Object value) - Left like
• rightLike(SFunction<T, ?> column, Object value) - Right like
• between(SFunction<T, ?> column, Object value1, Object value2) - Between
• notBetween(SFunction<T, ?> column, Object value1, Object value2) - Not between
• in(SFunction<T, ?> column, Object... value) - In
• notIn(SFunction<T, ?> column, Object... value) - Not in
• isNull(SFunction<T, ?> column) - Is NULL
• isNotNull(SFunction<T, ?> column) - Is NOT NULL

3. Sorting

• orderAsc(SFunction<T, ?> column) - Ascending sort
• orderDesc(SFunction<T, ?> column) - Descending sort
• orders(SFunction<T, ?>... columns) - Multi-column sorting (default ascending)

4. Pagination

• page(Integer pageIndex, Integer pageSize) - Set pagination parameters

5. Deduplication

• distinct(Boolean distinct) - Set whether to deduplicate

Object Save Description

Determines primary key field based on @Id annotation. Throws IllegalArgumentException if no primary key field exists.

• Insert condition: Executes insert when primary key value is null
  • String type primary key: Automatically generates UUID as primary key value
  • Other type primary keys: Uses database auto-generated primary key value
• Update condition: Executes update when primary key value is not null
  • Uses primary key value as update condition

Note: To insert a new record with a pre-set primary key value, use Entity.insert() instead of Entity.save(), because save() performs an update when the primary key is not null.

---

2. sql-forge-calcite-spring-boot-starter (Cross-Database Federated Queries)

Based on Apache Calcite, enables cross-database federated queries — join data from MySQL, PostgreSQL, and other databases in a single SQL statement.

Depends on the core starter (sql-forge-spring-boot-starter), which is automatically included.

Configuration

sql:
  forge:
    calcite:
      enabled: true                          # Enable Calcite
      configuration: classpath:model.json    # Calcite model configuration file path
      schemata:                              # Configure schema
        - MYSQL
        - POSTGRES

model.json describes the Calcite data source connection information. Refer to the Apache Calcite documentation.

Usage

Once enabled, a calcite executor is automatically registered. Use executorName=calcite in any API:

POST http://localhost:8080/sql/forge/api/json/select/orders?executorName=calcite
Content-Type: application/json

{
  "@column": ["o.id", "u.username", "p.name"],
  "@join": [
    {
      "type": "JOIN",
      "joinTable": "MYSQL_DB.users u",
      "on": "o.user_id = u.id"
    },
    {
      "type": "JOIN",
      "joinTable": "POSTGRES_DB.products p",
      "on": "o.product_id = p.id"
    }
  ]
}

---

3. sql-forge-web-spring-boot-starter (Amis + Console)

Provides Amis low-code template management, Web Console UI, and user/role management.

Depends on the core starter (sql-forge-spring-boot-starter), which is automatically included.

3.1 Amis Template API

Use Amis together with JSON API and SQL Template API to rapidly build web pages.

Template Management Endpoints

• PUT /sql/forge/api/template/amis - Save/Update Amis template
  • id: Template ID
  • name: Template name
  • description: Template description
  • context: Amis JSON Schema content
• GET /sql/forge/api/template/amis/{id} - Get template by ID
• GET /sql/forge/api/template/amis - Get template list
• DELETE /sql/forge/api/template/amis/{id} - Delete template by ID

Example

PUT http://localhost:8080/sql/forge/api/template/amis
content-type: application/json

{
    "id": "amis-template-users",
    "name": "User Management",
    "context": "{ \"type\": \"page\", \"body\": { \"type\": \"crud\", ... } }"
}

Rendered page:

Persistent Templates

Uses in-memory storage by default. Implement ITemplateAmisStorage for custom persistence.

3.2 Web Console

Provides a visual web interface at: /sql/forge/web

• Database metadata viewing and SQL debugging (requires sql.forge.api.database.enabled=true)
• JSON API debugging
• SQL template management and debugging
• Amis template management and debugging

3.3 User & Role Management

The authentication system (Session login + ApiKey) is built into the core starter. See 1.4 Authentication & ApiKey for details. This section only describes user/role management features specific to Web Console.

User Management Endpoints (admin required)

• GET /sql/forge/api/user - Get user list
• PUT /sql/forge/api/user - Save/Update user
• DELETE /sql/forge/api/user/{id} - Delete user

Role Management Endpoints

• GET /sql/forge/api/role - Get role list
• PUT /sql/forge/api/role - Save/Update role (admin required)
• DELETE /sql/forge/api/role/{id} - Delete role (admin required)
• GET /sql/forge/api/role-template?role={roleId} - Get template IDs associated with a role
• PUT /sql/forge/api/role-template - Set role-template associations (admin required)
• GET /sql/forge/api/user-role?userId={userId} - Get role IDs for a user (admin required)
• PUT /sql/forge/api/user-role - Set user-role associations (admin required)

Extending Persistent Storage

All storage uses in-memory implementations by default. Replace with database persistence by implementing the following interfaces:

Interface	Description
IUserStorage	User storage
IUserRoleStorage	User-role association storage
IRoleStorage	Role storage
IRoleTemplateStorage	Role-template association storage

Simply implement the interface and register as a Spring Bean to automatically replace the default implementation (@ConditionalOnMissingBean).

---

4. sql-forge-mcp (AI MCP Server)

A Model Context Protocol (MCP) server that exposes sql-forge backend capabilities as AI tools via stdio transport. AI assistants (Claude Desktop, Cursor, Windsurf, etc.) can query database metadata, execute SQL, and manage Amis templates through MCP tool calls.

Requires a running sql-forge backend (with sql.forge.api.database.enabled=true).

MCP Tools

Tool	Description
getSystems	Get all configured system information
getMetaDataDatabase	Get database product name and version for a system
sqlForgeMetaDataTables	Get all tables in a system's database
getMetaDataTableInfo	Get table structure: columns, primary keys, foreign keys, indexes
executeSQL	Execute a SQL query and return the result set
amisTemplateSave	Save an Amis page JSON template configuration

stdio Usage (jbang)

Use jbang to run the MCP server without local installation — configure your MCP client (Claude Desktop, Cursor, etc.) as follows:

{
  "mcpServers": {
    "sql-forge-mcp": {
      "command": "jbang.cmd",
      "args": [
        "io.github.wb04307201:sql-forge-mcp:1.5.12",
        "--sql.forge.mcp.systems[0].name=OrderSystem",
        "--sql.forge.mcp.systems[0].url=http://localhost:8081",
        "--sql.forge.mcp.systems[0].description=Order system containing system tables: users, roles, dictionaries, etc. Business tables: products, orders, payment records, user addresses, inventory, order logistics, product categories, product reviews, etc.",
        "--sql.forge.mcp.systems[0].apiKey=test"
      ]
    }
  }
}

Multiple Systems

Configure multiple systems to connect one MCP server to several sql-forge backends:

{
  "mcpServers": {
    "sql-forge-mcp": {
      "command": "jbang.cmd",
      "args": [
        "io.github.wb04307201:sql-forge-mcp:1.5.12",
        "--sql.forge.mcp.systems[0].name=OrderSystem",
        "--sql.forge.mcp.systems[0].url=http://localhost:8081",
        "--sql.forge.mcp.systems[0].description=Order system",
        "--sql.forge.mcp.systems[0].apiKey=test",
        "--sql.forge.mcp.systems[1].name=InventorySystem",
        "--sql.forge.mcp.systems[1].url=http://localhost:8082",
        "--sql.forge.mcp.systems[1].description=Inventory system",
        "--sql.forge.mcp.systems[1].apiKey=test"
      ]
    }
  }
}

---

Full Configuration Reference

sql:
  forge:
    schemata:                      # Configure schema names
      - PUBLIC
    api-keys:                      # ApiKey list (optional, enables X-Api-Key header access without login)
      - sk-your-api-key
    calcite:
      enabled: true                # Enable Calcite cross-database federated queries
      configuration: classpath:model.json
    api:
      database:
        enabled: true              # Enable direct database API
        select-only: true          # true=SELECT only
      json:
        enabled: true              # Enable JSON CRUD API (default true)
      template:
        sql:
          enabled: true            # Enable SQL template API (default true)
        amis:
          enabled: true            # Enable Amis template API (default true)
    console:
      enabled: true                # Enable Web Console (default true)