This document describes how APIJSON handles SQL dialect differences across 30+ supported database systems. The AbstractSQLConfig class serves as the core component responsible for generating database-specific SQL syntax, including identifier quoting, pagination clauses, function mapping, and data type handling.
For information about database configuration and connection setup, see Database Configuration For details on SQL generation and execution, see SQL Configuration and Generation
APIJSON supports a wide variety of SQL and NoSQL databases by abstracting SQL generation behind the AbstractSQLConfig class APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java27-28 This abstraction layer detects the target database type and generates appropriate SQL syntax for each dialect. The system handles critical differences including:
Sources:
APIJSON supports 30+ database types, organized into functional categories. The list is maintained in the DATABASE_LIST static initializer APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java146-183
| Database | Constant | Description |
|---|---|---|
| MySQL | DATABASE_MYSQL | MySQL and compatible derivatives |
| PostgreSQL | DATABASE_POSTGRESQL | PostgreSQL and compatible systems |
| SQL Server | DATABASE_SQLSERVER | Microsoft SQL Server |
| Oracle | DATABASE_ORACLE | Oracle Database |
| MariaDB | DATABASE_MARIADB | MariaDB fork of MySQL |
| TiDB | DATABASE_TIDB | NewSQL distributed database |
| CockroachDB | DATABASE_COCKROACHDB | Distributed SQL database |
| DB2 | DATABASE_DB2 | IBM DB2 |
| DaMeng | DATABASE_DAMENG | DaMeng Database |
| KingBase | DATABASE_KINGBASE | KingBase Database |
| SQLite | DATABASE_SQLITE | SQLite embedded database |
| Database | Constant | Description |
|---|---|---|
| ClickHouse | DATABASE_CLICKHOUSE | OLAP columnar database |
| Hive | DATABASE_HIVE | Hadoop data warehouse |
| Presto | DATABASE_PRESTO | Facebook PrestoDB |
| Trino | DATABASE_TRINO | PrestoSQL fork |
| Doris | DATABASE_DORIS | Apache Doris MPP database |
| Database | Constant | Description |
|---|---|---|
| InfluxDB | DATABASE_INFLUXDB | Time series database |
| TDengine | DATABASE_TDENGINE | Time series database |
| TimescaleDB | DATABASE_TIMESCALEDB | PostgreSQL extension for time series |
| Elasticsearch | DATABASE_ELASTICSEARCH | Search and analytics engine |
| Milvus | DATABASE_MILVUS | Vector database |
Sources:
The following diagram maps the natural language requirement of "SQL Dialect Handling" to the specific code entities involved in the process.
Diagram: SQL Dialect Resolution Flow
Sources:
The AbstractSQLConfig uses internal methods to branch SQL generation logic based on the database property.
Diagram: Database Detection Logic
APIJSON handles databases using specific constants defined in the AbstractSQLConfig static block APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java146-183 Key families include:
DATABASE_MYSQL, DATABASE_MARIADB, DATABASE_TIDB.DATABASE_POSTGRESQL, DATABASE_COCKROACHDB.DATABASE_SQLSERVER.Sources:
Different databases use different characters to quote identifiers (table names, column names) to avoid keyword conflicts. This is managed within the AbstractSQLConfig generation logic.
| Database Family | Quote Style | Example |
|---|---|---|
| MySQL/MariaDB/TiDB | Backtick (`) | `User` |
| PostgreSQL/Oracle | Double Quote (") | "User" |
| SQL Server | Square Brackets ([]) | [User] |
The system supports multi-layered namespaces through configurable defaults:
DEFAULT_DATABASE: Default database type (MySQL) APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java69DEFAULT_SCHEMA: Default schema name (sys) APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java72TABLE_SCHEMA_MAP: Maps specific tables to their respective schemas APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java112-124Sources:
APIJSON maintains whitelists and regex patterns to ensure generated SQL is both compatible and secure.
SQL_AGGREGATE_FUNCTION_MAP: Standard aggregates like MAX, MIN, AVG, COUNT, SUM APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java103SQL_FUNCTION_MAP: General functions and JSON functions APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java104RAW_MAP: Raw SQL snippets and operators APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java101All SQL fragments must pass strict regex validation to prevent injection:
PATTERN_SCHEMA: Validates schema/table identifiers APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java107PATTERN_RANGE: Validates numeric and operator ranges APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java108PATTERN_FUNCTION: Validates complex function calls APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java110Sources:
The AbstractSQLExecutor executes the generated dialect-specific SQL. It tracks metrics such as:
generatedSQLCount: Total SQL statements generated APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java47executedSQLCount: Total SQL statements executed against the DB APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java49executedSQLDuration: Cumulative time spent on database execution APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java64The executor handles the low-level JDBC interactions, calling statement.executeQuery(sql) or statement.executeUpdate(sql) depending on the request method APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java145-159
execute MethodThe execute method in AbstractSQLExecutor bridges the configuration and the physical database. It retrieves the SQL string via config.gainSQL(false) APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java171 and manages the Statement lifecycle. For non-query methods (POST, PUT, DELETE), it tracks updateCount and handles permissions APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java224-239
Sources:
Refresh this wiki