SQL Generation (SQLConfig / AbstractSQLConfig)

Relevant source files

• APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java
• APIJSONORM/src/main/java/apijson/orm/SQLConfig.java
• APIJSONORM/src/test/java/apijson/orm/AbstractSQLConfigFakeDeleteTest.java

Purpose and Scope

The SQL generation layer in APIJSONORM is responsible for translating structured JSON requests into executable, secure SQL statements across a wide range of relational and non-relational databases. At the heart of this architecture are the SQLConfig interface and its robust abstract implementation, AbstractSQLConfig, which manage table mappings, database dialects, parameters, whitelists, and lifecycle rules.

This parent page introduces the primary configuration contract, factory mechanisms, statement builders, safety whitelists, and fake delete logic. For advanced dialect-specific rules and multi-database constants, see Multi-Database Dialect Support.

Sources: APIJSONORM/src/main/java/apijson/orm/SQLConfig.java:15-18(), APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java:24-28()

---

1. The SQLConfig Contract and Factory

The SQLConfig interface defines the core contract for configuring SQL generation, exposing metadata such as target database types, schemas, tables, versions, URIs, and credentials APIJSONORM/src/main/java/apijson/orm/SQLConfig.java18-200 It provides a comprehensive set of dialect identification flags (e.g., isMySQL(), isPostgreSQL(), isOracle()) and pagination setters APIJSONORM/src/main/java/apijson/orm/SQLConfig.java88-130

AbstractSQLConfig implements this contract, acting as the base class for building queries. It manages global safety configurations including pattern matchers for schemas, ranges, and functions, as well as mapping tables and columns APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java75-112 The static factory method newSQLConfig initializes appropriate configurations based on the incoming request method, table name, and callback parameters.

Figure 1: SQL configuration lifecycle from JSON request to SQL generation.

Sources: APIJSONORM/src/main/java/apijson/orm/SQLConfig.java:18-219(), APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java:24-112()

---

2. CRUD Statement Builders

APIJSON automatically generates SELECT, UPSERT, UPDATE, and DELETE statements depending on the RequestMethod (GET, HEAD, POST, PUT, DELETE). AbstractSQLConfig constructs clauses dynamically by inspecting keys, conditions, column selections, ordering, grouping, and limits specified in the JSON request.

For instance, GET methods construct SELECT statements with projection columns, WHERE criteria, joins, and pagination. POST and PUT methods configure insert and update payloads respectively. The builder inspects table and column key maps (TABLE_KEY_MAP, COLUMN_KEY_MAP) to support schema obfuscation and secure database views without exposing physical column names to clients.

Figure 2: Routing request methods to underlying SQL builders.

Sources: APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java:86-94(), APIJSONORM/src/main/java/apijson/orm/SQLConfig.java:200-205()

---

3. Raw SQL and Function Whitelists

To balance flexibility and security, AbstractSQLConfig maintains strict whitelists for raw SQL fragments and database functions. Direct execution of arbitrary strings is prohibited to prevent SQL injection attacks.

Key whitelist control structures include:

• RAW_MAP: Maps custom raw SQL fragments APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java101-102
• SQL_FUNCTION_MAP and SQL_AGGREGATE_FUNCTION_MAP: Restrict permitted aggregate and scalar SQL functions APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java103-105
• PATTERN_FUNCTION: A regular expression pattern validating that function expressions adhere to safe character limits and prevent comment/delimiter injection (/*, --, ;) APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java78-111

Figure 3: Validation flow for raw SQL and function expressions.

Sources: APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java:75-112()

---

4. Fake Delete Mechanism

APIJSON supports "fake delete" (logical deletion) where incoming DELETE requests are intercepted and transparently rewritten as PUT (update) operations APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java57-60

When a table is configured with fake delete rules in AbstractVerifier.ACCESS_FAKE_DELETE_MAP, calling isFakeDelete() causes the configuration builder to modify the request method to RequestMethod.PUT and inject the configured deleted flag key and value (e.g., deletedFlag = 1) into the update content payload APIJSONORM/src/test/java/apijson/orm/AbstractSQLConfigFakeDeleteTest.java134-145 If a table lacks valid fake delete configuration, execution rejects the request or performs physical deletion depending on access control APIJSONORM/src/test/java/apijson/orm/AbstractSQLConfigFakeDeleteTest.java108-124

Sources: APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java:57-60(), APIJSONORM/src/test/java/apijson/orm/AbstractSQLConfigFakeDeleteTest.java:100-170()

---

5. Multi-Database Dialect Support

APIJSON supports dozens of relational and non-relational databases through database type constants (DATABASE_MYSQL, DATABASE_POSTGRESQL, DATABASE_KINGBASE, etc.) and dialect-specific rendering methods.

For comprehensive details on database constants, dialect flag methods, pagination differences, upsert syntax variations, and Kingbase support, see Multi-Database Dialect Support.

Sources: APIJSONORM/src/main/java/apijson/orm/SQLConfig.java:20-60(), APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java:149-160()