DEV Community: zoro ma

Inside Army's MySQL Dialect: When SQL Dialects Are First-Class Citizens

zoro ma — Wed, 16 Sep 2026 19:03:22 +0000

Inside Army's MySQL Dialect: When SQL Dialects Are First-Class Citizens

GitHub: https://github.com/PillArmy/army

Module: io.qinarmy:army-mysql (built on army-core / army-jdbc) · Supports MySQL 5.5 / 5.6 / 5.7 / 8.0

Every claim in this article comes from the actual army-mysql and army-core source, and every code snippet is copied verbatim from the framework source or the army-example test suite — nothing is invented.

Most Java SQL libraries treat a "dialect" as one enum, one common syntax tree, and a handful of if/else branches. You get portable CRUD — but the moment you need STRAIGHT_JOIN, LOAD DATA, ON DUPLICATE KEY UPDATE, or SET @row := 0, you are back to string concatenation.

Army takes the opposite route. In Army, MySQL is not a flag — it is an independent grammar modeled clause by clause from the MySQL reference manual. The army-mysql module is about 30,000 lines of main code, of which the MySQL-specific SQL rendering layer alone is over 3,700 lines. Its entry class MySQLs exposes 18 statement factories covering SELECT/INSERT/REPLACE/UPDATE/DELETE (single-table, multi-table, and batch variants), VALUES, LOAD DATA, SET, and SHOW.

This article walks through that dialect from the source, showing what "dialect as a first-class citizen" actually means.

1. Module Anatomy: Three Layers

The army-mysql code separates cleanly into three layers:

io.army.criteria.mysql     — Contract layer: MySQLQuery / MySQLInsert / MySQLUpdate /
                            MySQLDelete / MySQLLoadData / MySQLReplace / MySQLShow ...
                            (all nested staged interfaces — they describe only what
                             you are allowed to write)

io.army.criteria.impl     — Builder layer: MySQLQueries / MySQLInserts / MySQLMultiUpdates /
                            MySQLHints / MySQLJsonFunctions / MySQLSpatialFunctions ...
                            (the MySQLs entry class lives here too)

io.army.dialect           — Rendering layer: MySQLParser → MySQLDialectParser (1,292 lines),
                            MySQLDdlParser (507 lines), MySQLIdentifierHandler,
                            MySQLLiteralHandler, MySQLMappingHandler, MySQLParserFactory

Wiring goes through a factory selected only when the target database is MySQL:

@Override
public DialectParser createDialectParser(DialectEnv environment) {
    final MySQLDialect dialect;
    dialect = (MySQLDialect) ParserFactoryUtils.targetDialect(environment, Database.MySQL);
    return MySQLDialectParser.create(environment, dialect);
}

The concrete version comes from ServerMeta — the metadata read from the live connection. MySQLDialect is a four-constant enum with deliberately simple version mapping:

public enum MySQLDialect implements Dialect {
    MySQL55(55), MySQL56(56), MySQL57(57), MySQL80(80);

    public static MySQLDialect from(final ServerMeta meta) {
        switch (meta.major()) {
            case 5:
                switch (meta.minor()) {
                    case 5: return MySQLDialect.MySQL55;
                    case 6: return MySQLDialect.MySQL56;
                    case 7: return MySQLDialect.MySQL57;
                    default: throw Database.unsupportedVersion(meta);
                }
            case 8:
            default:  return MySQLDialect.MySQL80;
        }
    }
}

That Dialect object is not decorative — the rendering layer uses it everywhere for version gating. CTEs, for example (only available in MySQL 8.0), are hard-blocked in MySQLParser.parseWithClause:

@Override
protected final void parseWithClause(final _Statement._WithClauseSpec spec, final _SqlContext context) {
    final List<_Cte> cteList = spec.cteList();
    if (cteList.isEmpty()) {
        return;
    }
    if (!this.asOf80) {
        throw _Exceptions.dontSupportWithClause(this.dialect);
    }
    withSubQuery(spec.isRecursive(), cteList, context, _SQLConsultant::assertStandardCte);
}

asOf80 is a boolean computed once at construction (dialect.compareWith(MySQLDialect.MySQL80) >= 0) and shared across the whole module. Writing .with("cte") against a 5.7 server does not produce SQL that fails on execution — it fails during statement rendering, before anything touches the database.

2. The Entry Point: 18 Factories on `MySQLs`

public abstract class MySQLs extends MySQLSyntax {

    // —— Queries: primary / batch / subquery / scalar subquery ——
    public static MySQLQuery.WithSpec<Select>                          query();
    public static MySQLQuery.WithSpec<Statement._BatchSelectParamSpec> batchQuery();
    public static MySQLQuery.WithSpec<SubQuery>                      subQuery();
    public static MySQLQuery.WithSpec<Expression>                   scalarSubQuery();

    // —— Writes: single-table / multi-table / batch, one factory each ——
    public static MySQLInsert._PrimaryOptionSpec                     singleInsert();
    public static MySQLReplace._PrimaryOptionSpec                    singleReplace();
    public static MySQLUpdate._SingleWithSpec<Update>                singleUpdate();
    public static MySQLUpdate._SingleWithSpec<...Batch...>            batchSingleUpdate();
    public static MySQLUpdate._MultiWithSpec<Update>                  multiUpdate();
    public static MySQLUpdate._MultiWithSpec<...Batch...>            batchMultiUpdate();
    public static MySQLDelete._SingleWithSpec<Delete>                singleDelete();
    public static MySQLDelete._SingleWithSpec<...Batch...>           batchSingleDelete();
    public static MySQLDelete._MultiWithSpec<Delete>                 multiDelete();
    public static MySQLDelete._MultiWithSpec<...Batch...>            batchMultiDelete();

    // —— MySQL-specific statements ——
    public static MySQLLoadData._LoadDataClause<DmlCommand>            loadDataStmt();
    public static MySQLSet._SetSpec                                   setStmt();
    public static MySQLValues.ValuesSpec<Values>                     valuesStmt();
    public static MySQLValues.ValuesSpec<SubValues>                   subValues();
}

Note the design decisions:

Single-table and multi-table DML are different types. singleUpdate() and multiUpdate() return entirely different spec interfaces, because UPDATE t SET ... and UPDATE t JOIN j ON ... SET ... are different statements in MySQL. Army refuses to stuff both into one interface and punish you with a runtime error.
Batch is not a boolean flag, it is a separate factory. batchMultiUpdate() returns Statement._BatchUpdateParamSpec, whose parameter-binding contract differs from a single statement — the distinction is in the type.
REPLACE is a standalone statement, not an insert().replace() modifier — in MySQL it is genuinely different (delete-then-insert, with different auto-increment and trigger behavior).
Every factory is a zero-state static method returning an immutable chained builder.

3. The Type System: MySQL's Quirks Encoded in an Enum

The MySQLType enum in army-core is MySQL's type dictionary; each entry is a triple of (SQL type name, cross-dialect semantic tag (ArmyType), default Java class). The MySQL-specific half is the most revealing:

public enum MySQLType implements SQLType {
    TINYINT_UNSIGNED("TINYINT UNSIGNED",  ArmyType.TINYINT_UNSIGNED,  Short.class),
    SMALLINT_UNSIGNED("SMALLINT UNSIGNED",  ArmyType.SMALLINT_UNSIGNED, Integer.class),
    MEDIUMINT_UNSIGNED("MEDIUMINT UNSIGNED", ArmyType.MEDIUMINT_UNSIGNED, Integer.class),
    INT_UNSIGNED("INT UNSIGNED",           ArmyType.INTEGER_UNSIGNED, Long.class),
    BIGINT_UNSIGNED("BIGINT UNSIGNED",     ArmyType.BIGINT_UNSIGNED,  BigInteger.class),
    DECIMAL_UNSIGNED("DECIMAL UNSIGNED",    ArmyType.DECIMAL_UNSIGNED, BigDecimal.class),

    YEAR("YEAR", ArmyType.YEAR, Year.class),
    ENUM("ENUM", ArmyType.ENUM, String.class),
    SET("SET", ArmyType.DIALECT_TYPE, String.class),
    JSON("JSON", ArmyType.JSON, String.class),
    VECTOR("VECTOR", ArmyType.VECTOR, float[].class),       // MySQL 9.x vector type

    POINT("POINT"), LINESTRING("LINESTRING"), POLYGON("POLYGON"),
    MULTIPOINT("MULTIPOINT"), MULTIPOLYGON("MULTIPOLYGON"),
    MULTILINESTRING("MULTILINESTRING"), GEOMETRYCOLLECTION("GEOMETRYCOLLECTION"),
    GEOMETRY("GEOMETRY", ArmyType.GEOMETRY, byte[].class),              // WKB binary
    // ...
}

Two details worth expanding on.

3.1 Unsigned integers: the smallest Java type that cannot overflow

MySQL's five unsigned integer ranges misalign naturally with Java's signed types. Army's default mapping:

MySQL type	Range	Army's default Java type	Why
`TINYINT UNSIGNED`	0 ~ 255	`Short`	255 does not fit in `byte` (-128~127)
`SMALLINT UNSIGNED`	0 ~ 65,535	`Integer`	65535 does not fit in `short`
`MEDIUMINT UNSIGNED`	0 ~ 16,777,215	`Integer`	Still within int range
`INT UNSIGNED`	0 ~ 4.29 billion	`Long`	Does not fit in `int`
`BIGINT UNSIGNED`	0 ~ 2^64−1	`BigInteger`	Beyond `long`

This is not a marketing promise; both layers enforce it. TinyIntUnsignedType.javaType() returns Short.class, and the dispatch table in AbstractMappingType routes each ArmyType.*_UNSIGNED to its implementation. You write no @TypeHandler — conversion behavior is a property of the type itself.

3.2 Where the proprietary types land

SET maps to Java EnumSet; ENUM works through one of Army's three enum strategies (CodeEnum / LabelEnum / NameEnum).
All eight spatial types bind as WKB (Well-Known Binary) byte[], paired with a spatial function library of 154 static methods (see Section 8).
VECTOR targets the MySQL 9.x native vector type and shares the same ArmyType.VECTOR semantic tag and VectorType mapping class as PostgreSQL's pgvector — cross-dialect vector code stays largely reusable.

4. SELECT: A Typed Transcription of the MySQL Manual

This is where the dialect's depth is most visible. Each feature below maps to a real method (or method family) on the MySQLQuery chain.

4.1 SELECT modifiers: all 16 keywords modeled

MySQLs exposes every SELECT modifier from the manual as a type-safe constant:

public static final Modifier ALL;
public static final Modifier DISTINCTROW;
public static final Modifier HIGH_PRIORITY;
public static final Modifier STRAIGHT_JOIN;
public static final Modifier SQL_SMALL_RESULT;
public static final Modifier SQL_BIG_RESULT;
public static final Modifier SQL_BUFFER_RESULT;
public static final Modifier SQL_NO_CACHE;
public static final Modifier SQL_CALC_FOUND_ROWS;
// plus LOW_PRIORITY / DELAYED / QUICK / IGNORE / CONCURRENT / LOCAL,
// reused by INSERT / UPDATE / DELETE / LOAD DATA

Modifiers are passed as a List<Modifier>, and the renderer validates legality — SQL_CALC_FOUND_ROWS is only valid in SELECT, QUICK only in UPDATE/DELETE. A misplaced one fails at render time rather than producing SQL the server rejects.

4.2 Partition selection: `.partition(p0, p1)`

MySQL supports pruning directly on table references (FROM t PARTITION (p0,p1)). Army models this as a distinct chain stage right after the table reference (and alias). The real example below is from the MySQLCriteriaUnitTests in army-example; it also demonstrates DELETE modifiers and single-table DELETE ORDER BY + LIMIT in one chain:

stmt = MySQLs.singleDelete()
        .delete(hintSupplier, Arrays.asList(MySQLs.LOW_PRIORITY, MySQLs.QUICK, MySQLs.IGNORE))
        .from(ChinaRegion_.T, AS, "r")
        .partition("p1")
        .where(ChinaRegion_.createTime.between(SQLs::literal, map.get("startTime"), AND, map.get("endTIme")))
        .and(ChinaRegion_.updateTime.between(SQLs::param, map.get("startTime"), AND, map.get("endTIme")))
        .ifAnd(ChinaRegion_.version::equal, SQLs::literal, map.get("version"))
        .orderBy(ChinaRegion_.name::desc, ChinaRegion_.id)
        .ifLimit(map.get("rowCount"))
        .asDelete();

Single-table UPDATE, multi-table DML, and LOAD DATA carry the same partition clause.

4.3 Index hints: USE / IGNORE / FORCE INDEX × three purposes

This is the DBA's daily tuning tool, and Army models both of MySQL's orthogonal hint systems in full. The first is the table-level index hint, attached right after a table reference:

// USE / IGNORE / FORCE INDEX, each with optional FOR JOIN / FOR ORDER BY / FOR GROUP BY
.update(ChinaProvince_.T, AS, "p").useIndex(FOR, JOIN, "PRIMARY")
.join(ChinaRegion_.T, AS, "c").useIndex(FOR, JOIN, "PRIMARY")
    .on(ChinaRegion_.id::equal, ChinaProvince_.id)

Those lines are not mine — they are copied verbatim from MultiUpdateTests in army-example. One table can carry multiple hints, and every hint supports three styles: static enumeration, dynamic Consumer, and conditional ifUseIndex(BooleanSupplier).

4.4 Optimizer hints: the full ~30-item `/+ ... /` catalog

The second system is the MySQL 8.0 optimizer hint comment. The MySQLHints.HintType enum is essentially a table of contents of the manual:

enum HintType {
    JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, JOIN_SUFFIX,
    BKA, NO_BKA, BNL, NO_BNL,
    DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN,
    HASH_JOIN, NO_HASH_JOIN, MERGE, NO_MERGE,
    GROUP_INDEX, NO_GROUP_INDEX, INDEX, NO_INDEX, INDEX_MERGE, NO_INDEX_MERGE,
    JOIN_INDEX, NO_JOIN_INDEX, MRR, NO_MRR, NO_ICP, NO_RANGE_OPTIMIZATION,
    ORDER_INDEX, NO_ORDER_INDEX, SKIP_SCAN, NO_SKIP_SCAN,
    SEMIJOIN, NO_SEMIJOIN,
    MAX_EXECUTION_TIME, SET_VAR, RESOURCE_GROUP, QB_NAME
}

Factories are public on MySQLSyntax (joinFixedOrder(qbName), joinOrder(...), indexLevelHint(...), subQueryHint(...), maxExecutionTime(ms), setVar("k=v"), qbName(name), etc.). Each hint is a self-describing node (_SelfDescribed.appendSql) that renders its own syntax and performs its own version check — JOIN_FIXED_ORDER, for example, throws "dontSupportHint" below 8.0.

Hints enter the statement lazily as a Supplier<List<Hint>>, alongside the modifiers. Again from MultiUpdateTests, a real case that hangs both /*+ SET_VAR(foreign_key_checks=OFF) */ and LOW_PRIORITY on one multi-table UPDATE:

final Supplier<List<Hint>> hintSupplier =
        () -> Collections.singletonList(MySQLs.setVar("foreign_key_checks=OFF"));

stmt = MySQLs.multiUpdate()
        .with("cte").as(sw -> sw.select(ChinaRegion_.id)
                .from(ChinaRegion_.T, AS, "c")
                .where(ChinaRegion_.id.in(SQLs::rowParam, extractRegionIdList(regionList)))
                .and(ChinaRegion_.regionType.equal(SQLs::param, RegionType.PROVINCE))
                .asQuery()
        ).space()
        .update(hintSupplier, Collections.singletonList(MySQLs.LOW_PRIORITY))
        .space(ChinaProvince_.T, AS, "p").useIndex(FOR, JOIN, "PRIMARY")
        .join(ChinaRegion_.T, AS, "c").useIndex(FOR, JOIN, "PRIMARY")
            .on(ChinaRegion_.id::equal, ChinaProvince_.id)
        .join("cte").on(ChinaRegion_.id::equal, SQLs.refField("cte", ChinaRegion_.ID))
        .set(ChinaRegion_.regionGdp, SQLs::plusEqual, SQLs::param, gdpAmount)
        .where(ChinaRegion_.id::in, SQLs.subQuery()
                .select(s -> s.space(SQLs.refField("subCte", ChinaRegion_.ID)))
                .from("cte", AS, "subCte")
                .asQuery())
        .and(ChinaRegion_.createTime.between(SQLs::param, now.minusMinutes(10), AND, now.plusSeconds(1)))
        .asUpdate();

One statement demonstrates: CTE → multi-table UPDATE → optimizer hint → legacy modifier → index hint → compound assignment (+= gdp) → subquery → dynamic predicate. This is what dialect depth means: every item in a MySQL DBA's playbook has a typed place in the API.

4.5 STRAIGHT_JOIN, WITH ROLLUP, and locking clauses

STRAIGHT_JOIN: .straightJoin(table).on(...) forces the optimizer to join in the given order; a statement-level STRAIGHT_JOIN SELECT modifier is also available.
WITH ROLLUP: there is a separate .withRollup() after GROUP BY and after ORDER BY (matching MySQL's two positions), each with an ifWithRollup(BooleanSupplier) variant.
Locks: .forUpdate() / .forShare(), with .of(table...) to restrict locked tables, .nowait() / .skipLocked() (8.0), and the legacy .lockInShareMode().
SELECT INTO variables: .into("@id", "@name") maps to SELECT ... INTO @id, @name.
Set operations: MySQL 8.0's INTERSECT / EXCEPT (with ALL/DISTINCT variants) sit on the chain alongside UNION.

4.6 MySQL-ization in the renderer: backticks and `LIMIT m, n`

The shared ArmyParser exposes dialect differences as template methods, overridden by MySQLParser. The two most typical overrides:

abstract class MySQLParser extends _ArmyDialectParser {
    static final char BACKTICK = '`';

    @Override
    protected final char identifierDelimitedQuote() {
        return BACKTICK;                 // MySQL identifiers: backticks; Postgres: double quotes
    }

    @Override
    protected final void standardLimitClause(final _Expression offset,
                                            final _Expression rowCount, _SqlContext context) {
        if (offset != null && rowCount != null) {
            // MySQL native form: LIMIT offset, rowCount (not the standard LIMIT n OFFSET m)
            context.sqlBuilder().append(_Constant.SPACE_LIMIT);
            offset.appendSql(...);
            context.sqlBuilder().append(_Constant.SPACE_COMMA);
            rowCount.appendSql(...);
        }
        ...
    }
}

The constructor also validates escape policy up front: the MySQL dialect accepts only EscapeMode.DEFAULT for identifier escaping and DEFAULT/BACK_SLASH for literals — because the MySQL server defaults to backslash escaping, and allowing anything else would only generate SQL the server cannot parse.

5. DML: UPSERT, REPLACE, and Multi-Table Syntax

5.1 ON DUPLICATE KEY UPDATE — down to the 8.0.19 row alias

MySQLInsert._OnDuplicateKeyUpdateSpec offers two entry styles:

_StaticConflictUpdateClause<I, T> onDuplicateKey();        // field-by-field chaining
_DmlInsertClause<I> onDuplicateKeyUpdate(Consumer<...> c); // batched via Consumer

Real example, verbatim from InsertTests:

stmt = MySQLs.singleInsert()
        .ignoreReturnIds()
        .insertInto(ChinaRegion_.T)
        .parens(s -> s.space(ChinaRegion_.name, ChinaRegion_.regionGdp)
                .comma(ChinaRegion_.population, ChinaRegion_.parentId))
        .defaultValue(ChinaRegion_.regionGdp, SQLs::param, "88888.88")
        .defaultValue(ChinaRegion_.visible, SQLs::param, true)
        .defaultValue(ChinaRegion_.parentId, SQLs::param, 0)
        .values(regionList)
        .as("c")                                    // MySQL 8.0.19+ row alias AS new_row
        .onDuplicateKey()
        .update(ChinaRegion_.population, SQLs.field("c", ChinaRegion_.population))
        .comma(ChinaRegion_.regionGdp, SQLs.field("c", ChinaRegion_.regionGdp))
        .asInsert();

.as("c") corresponds to the ... AS new ON DUPLICATE KEY UPDATE col = new.col syntax introduced in MySQL 8.0.19 — hence the SQLs.field("c", ...) references to the incoming row. The older VALUES(col) function style is supported as well; both eras coexist on the chain.

Even better, the singleInsert() Javadoc turns MySQL's official affected-rows semantics (1 = inserted, 2 = updated, 0 = set to current values — which "actually never 0" in Army because the framework maintains updateTime) into an API contract, and states three limitations explicitly:

if the table has a visible logical-delete field, UPSERT works only in Visible.BOTH mode;
auto-increment PK + multi-row child-table domain insert cannot use ON DUPLICATE (the server cannot return correct multi-row generated keys on conflict);
auto-increment PK + multi-row domain syntax with conflicts requires .ignoreReturnIds() beforehand.

These are not excuses for framework gaps — they are limitations of the MySQL server protocol, surfaced in the API contract before you hit them.

5.2 REPLACE: a standalone statement with its own limits

singleReplace() produces a dedicated MySQLReplace statement (rendered as REPLACE INTO), with its own entry Javadoc — including the same "multi-row domain replace of auto-increment keys requires ignoreReturnIds()" rule and the logical-delete visibility constraint.

5.3 Multi-table UPDATE / DELETE

multiUpdate(): UPDATE t [hint] JOIN j [hint] ON ... SET ... WHERE .... SET supports compound assignment operators (SQLs::plusEqual renders gdp = gdp + ?), and everything FROM supports — CTEs, derived tables, index hints — is available.
multiDelete(): both MySQL multi-table DELETE shapes are modeled — DELETE t1 FROM t1 JOIN t2 ... and DELETE FROM t1 USING t1 JOIN t2 ..., distinguished by how the target tables are chosen.
Single-table UPDATE/DELETE keep MySQL's ORDER BY + LIMIT: the _OrderBySpec → _LimitSpec chain makes UPDATE t SET ... ORDER BY id LIMIT 100 and DELETE FROM t WHERE ... ORDER BY id LIMIT 100 — MySQL's unique batching idiom — a legal path.

On the rendering side, MySQLDialectParser (1,292 lines) has a dedicated method per shape — parseSingleUpdate / parseMultiUpdate / parseSingleDelete / parseMultiDelete — rather than one giant switch. Multi-delete rendering is annotated step by step in the source: WITH → DELETE keyword → hint comment block → modifiers → …

6. LOAD DATA: An "Ops Command" Made Type-Safe

LOAD DATA INFILE is MySQL's ultimate CSV-import weapon, and the area most ORMs refuse to touch. Army's MySQLLoadData interface models the entire manual syntax, and the chain order is the syntax order:

LOAD DATA [LOCAL] INFILE 'file'
    [REPLACE | IGNORE]
    INTO TABLE tbl [PARTITION (...)]
    [CHARACTER SET charset]
    [{FIELDS | COLUMNS} [TERMINATED BY 'x'] [[OPTIONALLY] ENCLOSED BY 'x'] [ESCAPED BY 'x']]
    [LINES [STARTING BY 'x'] [TERMINATED BY 'x']]
    [IGNORE n {LINES | ROWS}]
    [(col1, col2, ...)]
    [SET col = expr, ...]

The example from the entry Javadoc (mirrored by the real LoadDataTests):

stmt = MySQLs.loadDataStmt()
        .loadData(MySQLs.LOCAL)
        .infile(csvFile)
        .ignore()
        .intoTable(ChinaRegion_.T)
        .characterSet("utf8mb4")
        .columns(s -> s.terminatedBy(","))
        .lines(s -> s.terminatedBy("\n"))
        .ignore(1, SQLs.LINES)
        .set(ChinaRegion_.visible, SQLs::literal, true)
        .set(ChinaRegion_.regionType, SQLs::literal, RegionType.NONE)
        .asCommand();

rows = session.update(stmt, SyncStmtOption.preferServerPrepare(false));

Deep details:

Parent/child tables can be imported in a chain: .intoTable(ParentTableMeta) returns a _ChildLoadData spec, after which .child().loadData(LOCAL).infile(childCsv)...intoTable(ChildTableMeta)...asCommand() composes a parent CSV and a child CSV into one command sequence — serving Army's own parent/child domain model, a need no generic ETL framework would anticipate.
Escape policy is forced: the characters in TERMINATED BY / ENCLOSED BY / ESCAPED BY are always parsed with EscapeMode.BACK_SLASH regardless of the global LITERAL_ESCAPE_MODE, and the Javadoc explicitly requires the server's NO_BACKSLASH_ESCAPES SQL mode to be disabled — LOAD DATA escaping rules are hard-wired by MySQL.
Execution prerequisites are a contract: local_infile=ON, the JDBC allowLoadLocalInfile=true property, and the requirement for a client-prepared or static statement (the LOCAL INFILE Request protocol) are all listed in the entry Javadoc with links to the official docs.
The renderer parseLoadData builds the statement in numbered steps: LOAD DATA keyword → modifier validation (anything other than LOCAL/CONCURRENT throws) → INFILE → REPLACE/IGNORE → table and partitions → charset → FIELDS/LINES → IGNORE count → SET.

7. SET Statements and User Variables: Home for MySQL Procedural Tricks

7.1 `setStmt()`: four scopes

MySQL 8.x SET syntax has four scopes — GLOBAL, SESSION, PERSIST, and PERSIST_ONLY. Army distinguishes them with typed VarScope constants (MySQLs.PERSIST / PERSIST_ONLY reuse core's KeyWordVarScope), corresponding to dynamic server-variable persistence.

7.2 User variables and inline `:=` assignment

This is the classic MySQL trick: generating row numbers inside one query with a user variable. Army not only supports it — it is a real test in army-example (VariableTests):

stmt = MySQLs.query()
        .select(s -> s.space(MySQLs.at("my_row_number").increment().as("rowNumber"))
                .comma("t", PERIOD, ChinaRegion_.T))
        .from(ChinaRegion_.T, AS, "t")
        .crossJoin(SQLs.subQuery()
                .select(MySQLs.at("my_row_number", SQLs.COLON_EQUAL, SQLs.LITERAL_0).as("n"))
                .asQuery()
        ).as("s")
        .where(ChinaRegion_.id.in(SQLs::rowParam, extractRegionIdList(regionList)))
        .orderBy(ChinaRegion_.id)
        .asQuery();

The generated SQL is the textbook pattern:

SELECT (@my_row_number := @my_row_number + 1) AS rowNumber, t.*
FROM china_region t
CROSS JOIN (SELECT @my_row_number := 0) s
WHERE t.id IN (...)
ORDER BY t.id

MySQLs.at(name) builds a user-variable reference, MySQLs.at(name, COLON_EQUAL, init) builds inline assignment, and atAtSession("sql_mode") / atAtGlobal(...) reference @@session. / @@global. system variables. The inline comment — "defer SELECT clause, so SELECT clause is executed after FROM clause" — shows the author knows exactly how MySQL's evaluation timing enables this trick. Modeling it type-safely is only possible if the builder understands how the server executes the SQL.

Additionally, the MySQLShow interface turns SHOW BINLOG EVENTS ... FROM ... IN ... LIMIT, SHOW CHARACTER SET LIKE/WHERE, SHOW COLUMNS, and SHOW COLLATION into DQL statements (returning DqlStatement, executable through the session) — bringing MySQL client-style commands into the same unified execution pipeline.

8. The Function Libraries: JSON, Spatial, and Aggregate Depth

Army does not expose functions as a func(String name, Object... args) escape hatch. MySQL-specific functions are statically implemented by family:

Implementation	Static methods	Representative functions
`MySQLJsonFunctions`	52	JSON_TABLE, JSON_VALUE, JSON_SEARCH, JSON_CONTAINS, `->` / `->>` operators
`MySQLSpatialFunctions`	154	ST_DISTANCE / ST_CONTAINS (OpenGIS family) plus the MBR family (mbrContains, mbrWithin, …)
`MySQLStringFunctions`	60	ELT, EXPORT_SET, FROM_BASE64, FIND_IN_SET, …
`MySQLTimeFunctions`	67	MySQL date/time functions and interval units
`MySQLMiscellaneousFunctions`	73	GROUP_CONCAT and friends
`MySQLWindowFunctions`	48	MySQL-side window function bindings

The JSON_TABLE depth deserves its own demonstration. A real example from FunctionTests:

stmt = MySQLs.query()
        .select(s -> s.space("t", PERIOD, ASTERISK))
        .from(jsonTable(jsonDocument, "$[*]", COLUMNS, s -> s
                .space("rowId", FOR_ORDINALITY)
                .comma("ac", MySQLType.VARCHAR.parens(100).characterSet("utf8mb4")
                                .collate("utf8mb4_unicode_ci"),
                        PATH, "$.a", o -> o.spaceDefault("111").onEmpty()
                                          .spaceDefault("999").onError())
                .comma("aj", MySQLType.JSON, PATH, "$.a",
                        o -> o.spaceDefault("{\"x\":333}").onEmpty())
                .comma("bx", MySQLType.INT, EXISTS, PATH, "$.b")
        ))
        .as("t")
        .asQuery();

This covers every JSON_TABLE column kind: FOR ORDINALITY ordinal columns, PATH columns (with SQL type, character set, and collation), EXISTS PATH boolean columns, and the two-level DEFAULT ... ON EMPTY / ON ERROR clauses. Note that MySQLType.VARCHAR.parens(100).characterSet(...).collate(...) returns a TypeDef — DDL-style type definitions are themselves composible chain objects, not concatenated strings.

MySQL's proprietary GROUP_CONCAT is equally complete (DISTINCT, multiple expressions, ORDER BY, SEPARATOR):

select(groupConcat(SQLs.DISTINCT, s -> s.space(ChinaRegion_.name)
                .comma(ChinaRegion_.createTime)
                .comma(ChinaRegion_.regionGdp),
        s -> s.orderBy(ChinaRegion_.name).separator(","))
        .as("nameGroup"))

9. DDL: Table Options Are Part of the Dialect Too

MySQLDdlParser (507 lines) renders schema diffs to DDL, including MySQL-proprietary table options: auto-increment columns render AUTO_INCREMENT, and CREATE TABLE statements carry ENGINE=InnoDB / CHARACTER SET=utf8mb4 table options. Type-name rendering is gated by MySQLParser.typeName — only data types that are instanceof MySQLType may appear in MySQL DDL/DML; sneaking in a PostgreSQL INT4RANGE is rejected at render time with unrecognizedTypeName.

10. Philosophy and Costs

What it gets right

Syntactic correctness is guaranteed by the type system, not by experience. Partitions, index hints, modifiers, locks, ROLLUP, and row aliases each occupy an unskippable chain stage; wrong clauses fail to compile in Java, and wrong versions (CTEs on 5.7, 8.0 hints on 5.7) fail immediately at render time.
Dialect differences live in template methods, not scattered ifs. Backticks, LIMIT m,n, WITH version gates, and lock syntax are final overrides of ArmyParser in MySQLParser; MySQL-specific statements self-render through _SelfDescribed nodes. The boundary between the shared skeleton and dialect flesh is clean.
Server-protocol limits are written as contracts. LOAD DATA's three prerequisites, the ON DUPLICATE conflicts with auto-increment keys and logical deletes, and the hard escape-mode constraints all live in entry Javadoc and build-time validation — not in production deadlocks or corrupted generated keys.
MySQL operational culture is respected. LOW_PRIORITY, QUICK, IGNORE, SET_VAR, SKIP LOCKED, user-variable row numbering, SHOW commands, multi-table deletes — the real DBA's toolbox, which most "database abstraction layers" pretend does not exist.

The costs are equally real

A huge interface surface. The criteria/mysql package alone is roughly 7,200 lines of nested interfaces. Chains like _PrimaryOptionSpec → _PartitionSpec → _ColumnListSpec → _OnAsRowAliasSpec are a high wall for newcomers; the framework trades syntactic correctness for staged interfaces and passes the learning cost straight to the user.
Version span carries baggage. Still supporting MySQL 5.5 means WITH, windows, INTERSECT/EXCEPT, and JSON_TABLE must all exist behind version gates; an 8.0-only baseline would be simpler.
Portability is one-directional. A chain built with MySQLs.query() is bound to MySQL; cross-database code must stay on the SQLs.query() common subset — which deliberately removes about half of the features in this article. That is a conscious trade-off, but know it before adopting.
No string-concatenation escape hatch is a double-edged sword. Every new MySQL syntax addition (newer JSON functions, for example) waits for framework modeling — but the release cadence is controlled by the maintainer.

Closing

Army's MySQL dialect answers a question few take seriously: if you do not aim for "write once, run anywhere," but instead for "write everything MySQL can, on MySQL," how far can a Java SQL DSL go? Its answer spans version awareness from 5.5 to 9.x, rendering overrides from backticks to LIMIT m,n, Java type selection for all five unsigned integers, parent/child LOAD DATA imports, 30 optimizer hints, and all four JSON_TABLE column kinds. The MySQL reference manual's table of contents is, quite literally, this module's package structure.

If your systems depend heavily on MySQL-specific power — multi-table DML, UPSERT, LOAD DATA, index hints, spatial/JSON — and you are not willing to give up compile-time checking, this dialect is worth an afternoon reading the MySQL suites in army-example.

Source code and runnable examples: https://github.com/PillArmy/army (army-mysql module; examples in army-example/src/test/java/io/army/session/sync/mysql/)

Army vs jOOQ

zoro ma — Tue, 15 Sep 2026 17:25:40 +0000

Project on GitHub: github.com/PillArmy/army

Every code sample in this article is copied (with minor import trimming) from Army's own source tree — either the army-example module or generated build output. Every measurement of jOOQ was taken by me on a local checkout of the current jOOQ trunk. You can reproduce all of it.

A SQL DSL where JDBC is just one executor: studying Army against jOOQ

We have had type-safe SQL DSLs in Java for a long time. QueryDSL appeared in 2007, jOOQ in 2009. You would be forgiven for thinking the design space was closed — that everything left to do was polish, dialects, and code generation.

Army is a small, pre-1.0 (0.6.x), single-maintainer Java ORM that disagrees. After reading through its ~237,000 lines of main source across 16 Maven modules, I think it makes four design decisions worth studying even if you never adopt it — including one architecture-level bet I have not seen anywhere else in the Java ORM space.

This is not a "jOOQ is bad" article. jOOQ is an excellent, mature, commercially proven product. The interesting question is narrower: given identical language features, which trade-offs did each project choose, and what do those choices cost?

1. JDBC is not the foundation. It is one pluggable executor

This is the single most important thing in the codebase.

In MyBatis, a session holds a java.sql.Connection. In jOOQ, JDBC is the center of the universe; R2DBC support was added years later as a secondary adapter. In Army:

$ grep -rn "import java.sql" army-core/src/main/java | wc -l
       0

The entire core module contains zero JDBC imports, and its pom.xml has no JDBC dependency. The executor contract is driver-neutral by construction. From the actual Javadoc of io.army.executor.ExecutorFactory in army-core:

/// For example:
///
/// - JDBC
/// - JDBD
/// - ODBC
///
/// @return driver spi name
String driverSpiName();

/// @return JDBC always return false, JDBD always return true.
boolean isResultItemDriverSpi();


/// For example: io.army.jdbc or io.army.jdbd
/// @return executor vendor
String executorVendor();

JDBC is listed as one driver SPI alongside JDBD (a reactive driver) and ODBC. The blocking JDBC implementation — all 3,566 lines of JdbcExecutor — lives in a separate module, army-jdbc, and can be replaced without touching the SQL engine, the dialect layer, or the session API.

Why does this matter? Two reasons:

Reactive was never "a thread pool around JDBC". Army's git history contains a full reactive implementation (reactive session impl v1 → v3, typed reactor sessions, reactive transaction management, reactive MySQL/optimistic-lock test cases) targeting a genuine reactive driver, later removed in two commits (drop reactor module, drop reactive package) when the maintainer could no longer afford to maintain both stacks. Removing the module did not damage core — which is the proof that the abstraction is real rather than decorative.
SQL generation is decoupled from the wire protocol. The dialect renders SQL against an internal context; the executor binds and ships it. The same rendered statement can be consumed by a blocking driver today and a reactive driver tomorrow.

Contrast this with the cost of retrofitting reactiveness onto a JDBC-centric design, and you see why this is a bet, not a decoration.

2. A compile-time clause state machine — hidden behind exactly one public entry point

Here is real user-facing query code, from army-example/.../bank/dao/sync/region/StandardRegionDao.java:

final Select stmt;
stmt = SQLs.query()
        .select(ChinaRegion_.id)
        .from(ChinaRegion_.T, AS, "t")
        .where(ChinaRegion_.name.equal(SQLs::param, regionName))
        .and(ChinaRegion_.regionType.equal(SQLs::literal, regionType))
        .asQuery();

return this.sessionContext.currentSession().queryOne(stmt, Long.class);

SQLs.query() does not return a "wide query object". It returns StandardQuery.WithSpec<Select> — a narrow entry type. Each clause method's return type is parameterized to the next legal stage. From army-core/.../criteria/standard/StandardQuery.java:

// the ONLY public entry; Javadoc: "public interface that developer can directly use"
interface WithSpec<I extends Item> extends _StandardDynamicWithClause<SelectSpec<I>>,
        _StandardStaticWithClause<SelectSpec<I>>,
        SelectSpec<I> {
}

// select() lands you here...
interface _StandardSelectClause<I extends Item>
        extends _ModifierListSelectClause<SQLs.Modifier, _StandardSelectCommaClause<I>>,
        _DynamicModifierSelectClause<SQLs.Modifier, _FromSpec<I>> {
}

// ...from() lands you here, and joins branch/merge as a type-level graph:
interface _JoinSpec<I extends Item> extends _StandardJoinClause<_JoinSpec<I>, _OnClause<_JoinSpec<I>>>,
        _JoinCteClause<_OnClause<_JoinSpec<I>>>,
        _CrossJoinCteClause<_OnClause<_JoinSpec<I>>>,
        _WhereSpec<I> {
}

interface _WhereSpec<I extends Item>
        extends Statement._QueryWhereClause<_GroupBySpec<I>, _WhereAndSpec<I>>, _GroupBySpec<I>> {
}

Follow the chain and you get the complete SQL grammar:

WithSpec → SelectSpec → _FromSpec → _JoinSpec → _WhereSpec
        → _GroupBySpec → _HavingSpec → _WindowSpec
        → _OrderBySpec → _LimitSpec → _LockSpec → terminal

Write a clause out of order and the compiler rejects it, with the same strictness jOOQ offers. The branching is real too: _JoinSpec extends both further-join capability and _WhereSpec, so "keep joining or move to WHERE" is expressed in types, not runtime checks.

The twist: every intermediate spec is a nested interface whose Javadoc reads:

Application developer isn't allowed to directly use this interface... army don't guarantee compatibility to future distribution.

The user sees one entry (WithSpec) and four terminal statement families (Query, Insert, Update, Delete). The ~15-stage state machine exists in full — it is just locked in a black box.

How jOOQ encodes the same grammar

jOOQ uses a public linear inheritance chain. Walking the actual extends clauses on a current trunk checkout:

SelectSelectStep → SelectDistinctOnStep → SelectIntoStep → SelectFromStep
→ SelectWhereStep → SelectConnectByStep → SelectGroupByStep → SelectHavingStep
→ SelectWindowStep → SelectQualifyStep → SelectOrderByStep → SelectLimitStep
→ SelectForUpdateStep → SelectForStep → SelectOptionStep → SelectUnionStep
→ SelectCorrelatedSubqueryStep → SelectFinalStep → Select

That is 19 levels of public interface. There are 68 Select*Step source files. And the god-entry-point DSLContext.java measures:

16,556 lines
  ~848 method declarations
  280 distinct method base names

(The fetch* family alone includes fetch, fetchSingle, fetchOne, fetchLazy, fetchAsync, fetchStream, fetchOptional, fetchGroups, fetchMap... each with many overloads.)

This is not a mistake by stupid people — it is a precise trade-off. The public chain gives human developers the most aggressively narrowed IDE autocomplete in the industry, and every one of those 68 types is a compatibility contract that paying enterprise customers depend on. jOOQ even did one breaking rewrite (3.0, splitting Factory into DSL/DSLContext) and kept the Step chain — they are not incapable of surgery; the chain is load-bearing.

The difference is purely in where the state machine lives:

	QueryDSL	jOOQ	Army
Clause order enforced	At runtime	Compile time (inheritance chain)	Compile time (generic spec graph)
State machine visibility	User's problem	68 public Steps, all contracts	~15 specs, all forbidden; 1 public entry
Cost of adding a clause	Add a method	Insert a level, change all return types	Compose a spec into the right stages

A 2009 fork, not a 2023 discovery

It is tempting to excuse this as "Java didn't allow anything else back then". It did. Generics shipped in 2004; package-private types and multiple interface composition existed since Java 1; Army's clause layer itself uses zero default methods and zero sealed keywords — it would compile on Java 5.

And the alternative was already running in production: QueryDSL (created 2007) shipped a wide-interface fluent SQL API. Lukas Eder himself wrote in 2014: "In the beginning of jOOQ in 2009, QueryDSL was ahead of us." (blog.jooq.org, QueryDSL 1.2.0 manual, ©2007–2009)

Three roads were on the table simultaneously in 2009: wide interfaces (QueryDSL), public inheritance steps (jOOQ), and composed generic specs (the construction Army later chose). All three were legal then.

3. The parameter/literal/type decision happens at expression-node creation

Look again at that WHERE clause:

.where(ChinaRegion_.name.equal(SQLs::param, regionName))
.and(ChinaRegion_.regionType.equal(SQLs::literal, regionType))

equal(SQLs::param, x) binds x as a JDBC ? parameter. equal(SQLs::literal, x) inlines x as a SQL literal. The choice is made where the expression node is born, and the node carries its mapping type with it. There is no separate, parallel list of parameter mappings walking alongside rendered text (as in MyBatis' BoundSql + ParameterMapping) and no eq(Object) escape hatch that silently accepts anything.

This matters most where the database type changes the SQL syntax. A PostgreSQL jsonb value may need a cast, an array literal needs ARRAY[...] construction, an enum has three different storage conventions. Army models those as first-class mapping types in army-core/.../mapping/:

NameEnumType      CodeEnumType      LabelEnumType
JsonType / JsonbType / JsonMappingType / JsonbMappingType
ArrayMappingType  CompositeType     VectorType     XmlType ...

In jOOQ, enums are second-class citizens handled by configuring a Converter/EnumConverter (forced types); the rendering knowledge is not intrinsic to the expression node. And while Field<T>.eq(T) is type-safe, jOOQ's wide surface also offers eq(Object), plain-SQL fields, and DSL.field(String, DataType) — paths through which the wrong operand type compiles and fails at bind time. The generic parameter largely decides what you get out of a fetch; it does not fully police what goes into the AST.

The generated metamodel reinforces this at compile time. This is actual annotation-processor output from the project's build directory:

@Generated(value = "io.army.modelgen.ArmyMetaModelDomainProcessor")
public abstract class ProductInfo_ {

    private ProductInfo_() { throw new UnsupportedOperationException(); }

    public static final CompositeType T;

    static {
        T = CompositeType.from(ProductInfo.class);
        final int fieldSize = T.fieldList().size();
        if (fieldSize != 8) {
            throw _TableMetaFactory.compositeFieldSizeError(ProductInfo.class, fieldSize);
        }
    }

    public static final CompositeField productId   = T.field("productId");
    public static final CompositeField productName = T.field("productName");
    public static final CompositeField price       = T.field("price");
    // ...
}

The processor also rejects illegal table names (camelCase) and index declarations referencing non-existent fields — schema mistakes that cannot reach a running database.

4. The public API is interfaces, sealed; implementations are package-private

Building a factory, verbatim from army-example/src/test/java/io/army/session/FactoryUtils.java:

public static SyncSessionFactory createArmyBankSyncFactory(final Database database) {
    return SyncFactoryBuilder.builder()
            .name(mapDatabaseToFactoryName(database))
            .packagesToScan(Collections.singletonList("io.army.example.bank.domain"))
            .datasource(DataSourceUtils.createDataSource(database))
            .environment(createEnvironment(database))
            .jsonCodec(FastJsonCodec.getInstance())
            .fieldGeneratorFactory(new SimpleFieldGeneratorFactory())
            .build();
}

Static factory entry, fluent options, final immutable factory. The interfaces are sealed and permit exactly one package-private implementation:

public sealed interface SyncSessionFactory permits ArmySyncSessionFactory { ... }
public sealed interface SyncFactoryBuilder  permits ArmySyncFactoryBuilder { ... }

final class ArmySyncFactoryBuilder extends ... { ... }   // package-private
final class ArmySyncSessionFactory implements ... { ... } // package-private

You cannot instantiate the implementation, you cannot write your own implements SyncSessionFactory (the permits clause forbids it), and you cannot subclass the builder. There is no public DefaultXxx class doubling as unofficial API — compare with MyBatis, where DefaultSqlSessionFactory and its constructor are both public under a defaults package that is only a gentleman's agreement.

(Small honest wart: the builder's datasource(...) parameter is typed Object so it can accept both javax.sql.DataSource and Army's own read/write-splitting datasource. That is a real, if small, compile-time-safety leak.)

Executing statements stays narrow too — the whole sync session surface is ~63 methods, with query consumption unified behind ResultItem (rows, update counts, and metadata as one numbered stream), instead of hundreds of fetchXxx variants:

SyncLocalSession session = factory.localSession();

Long id = session.queryOne(stmt, Long.class);                       // one value
List<Map<String, Object>> rows = session.queryObjectList(stmt, HashMap::new);
session.update(insertStmt);                                         // DML
session.save(domain);                                               // active-record-style

5. Why I think this matters for AI — beyond hype

The point is not "AI likes Army". It is that AI coding agents are essentially program transformers under compiler supervision, and their failure mode is generating structurally plausible text.

Facing the three SQL-DSL architectures, an agent gets structurally different feedback:

Wide interfaces (QueryDSL-style): every method is callable in every state; wrong clause order fails latest — at runtime.
19 public Step types (jOOQ-style): autocomplete narrowing is superb for humans, but those 68 SelectConnectByAfterStartWithConditionStep-shaped names are vanishingly rare in training data; an agent must reason about which step am I on across a huge public type graph.
One entry + hidden generic specs (Army-style): the public vocabulary the model must hold is tiny (one entry, four terminal statements, a handful of clause functions), while wrong order is still killed by the compiler. Full constraint strength, roughly an order of magnitude fewer public types to reason about.

When the expression node also carries its mapping type, the agent doesn't merely emit text that looks like PostgreSQL — the renderer produces the required cast/array/enum syntax deterministically. The type system is, in effect, a cheap, unforgiving environment: a compiler error is feedback that does not depend on anyone's language.

6. The honest counter-section

You should not read this as "Army beats jOOQ". It does not, on most axes that matter for production:

Maturity: jOOQ has ~17 years of releases, 30+ dialects, commercial support, enormous manuals, and tens of thousands of deployments. Army is 0.6.x with essentially one user.
Dialect depth is concentrated: PostgreSQL and MySQL are deep (single files like PostgreDocumentFunctions.java run over 6,000 lines), SQLite/H2 are thinner, and Oracle is a five-file skeleton.
Tests are thin relative to scope: ~20.8k lines of test code against ~237k lines of main code. jOOQ's test matrix is larger than its main source.
The reactive stack was removed, not merely unbuilt — consciously, for lack of maintainer resources — so today only the blocking/SPI story is shipped.
Risk: single maintainer, no commercial entity, pre-1.0 API churn is possible despite the sealing discipline.

What Army does provide is existence proof: that a Java SQL DSL can (1) treat JDBC as a swappable detail, (2) enforce full clause order at compile time through a single public entry, (3) bind parameter/literal/mapping semantics into the expression node, and (4) seal the entire runtime surface — all using language features that have existed since 2004.

The strongest sentence I am willing to write is this: jOOQ optimized its public surface for human discovery across two decades of compatibility; Army optimized its public surface for minimality and compiler-enforced structure. In a world where an increasing share of SQL is written by agents and judged by compilers, the second bet deserves to be seen.

Appendix: how to verify

# jOOQ (current trunk checkout)
wc -l jOOQ/src/main/java/org/jooq/DSLContext.java
ls jOOQ/src/main/java/org/jooq | grep -c '^Select.*Step'

# Army
grep -rn "import java.sql" army-core/src/main/java | wc -l        # 0
find . -path '*/src/main/java/*.java' | wc -l                     # 1361

Sources: github.com/PillArmy/army
QueryDSL 1.2.0 reference (© 2007–2009 Mysema Ltd): PDF
Lukas Eder on QueryDSL vs jOOQ (2014): blog.jooq.org
jOOQ 3.0 migration notes (Factory split, Row[N] 8→22): manual

Measurements taken September 2026 on jOOQ trunk and Army 0.6.x source. I have no affiliation with either project.

Army: A Deep Dive into Java's Most Dialect-Aware Type-Safe SQL DSL

zoro ma — Mon, 14 Sep 2026 14:44:59 +0000

Army: A Deep Dive into Java's Most Dialect-Aware Type-Safe SQL DSL

GitHub: https://github.com/PillArmy/army

Apache 2.0 · Java 25 · Maven coordinates io.qinarmy:army-* · current version 0.6.8-SNAPSHOT

If you write Java against relational databases, you have basically four options: hand-written JDBC (verbose, unsafe), a JPA/Hibernate layer (powerful, but SQL becomes an implementation detail), a template engine like MyBatis (full SQL, stringly typed), or a fluent DSL like jOOQ (type-safe, but its dialect-specific story is thin).

Army is a fifth point on that map — and a fairly extreme one. Its README opens with a design philosophy that is almost a manifesto:

Don't create new world, just mapping real world.

We need standard, we need dialect, it's real world.

Army is not an ORM by its own definition. It does not manage connections, transactions, caches, lazy loading, or schema migrations. It does one thing: let you construct SQL statements in Java with the full power of the underlying dialect, checked at compile time, and map the result back to plain POJOs.

I spent time reading the actual source — roughly 220,000 lines of Java across 16 Maven modules, including a 120k-line core and dialect modules that re-implement the PostgreSQL and MySQL grammars separately. This article is what I found: the architecture, the clever type tricks, and the trade-offs.

1. What the Codebase Looks Like

The module layout tells the story before you read a single class:

Module	Java LOC (approx.)	Role
`army-core`	120,000	Criteria interfaces, dialect SPI, type system, sessions, metadata
`army-postgre`	40,000	PostgreSQL grammar: CTE, `RETURNING`, ranges, arrays, `DECLARE CURSOR`, MERGE
`army-mysql`	30,000	MySQL grammar: hints, `STRAIGHT_JOIN`, `LOAD DATA`, unsigned types
`army-array`	7,500	Per-element array mapping for every supported type
`army-sync`	5,200	Blocking session/executor layer
`army-annotation`	5,100	Annotation processor generating the static metamodel
`army-jdbc`	8,400	JDBC binding over any `DataSource`
`army-spring`, `army-spring-ai-*`, `army-guava`	—	Spring tx manager, Spring AI vector store & chat memory integrations

Dialects are not adapters over a common SQL subset — PostgreSQL and MySQL each get their own grammar package (io.army.criteria.postgre, io.army.criteria.mysql) with their own entry class, clause interfaces, and keyword objects. What follows explains why.

2. The Query You Actually Write

This is a real test from the project's example suite (army-example, QuerySuiteTests), slightly trimmed:

final Select stmt;
stmt = Postgres.query()
        .select("c", PERIOD, ChinaRegion_.T)
        .from(ChinaRegion_.T, AS, "c")
        .limit(SQLs::literal, 1)
        .asQuery();

final Supplier<ChinaRegion<?>> constructor = ChinaRegion::new;

syncSession.queryObject(stmt, constructor)
        .forEach(c -> LOG.debug("{}", c.getName()));

Several things are happening here that define Army's style:

Postgres.query() is the dialect-specific entry point. A portable statement starts with SQLs.query() instead.
ChinaRegion_.T is a generated static-metamodel table reference; ChinaRegion_.id, ChinaRegion_.name are typed field metadata, not strings.
AS, PERIOD are not enum literals imported for ceremony — they are typed SQL words. AS is a WordAs; passing ON where AS belongs does not compile.
The chain ends with a terminal method, .asQuery(), which returns a sealed Select statement. Until you call it, you don't have an executable statement — you have a builder state.
The result is your class, built through a method reference (ChinaRegion::new). No Record12<...>, no proxy, no session-attached entity.

Staged interfaces: the clause order is the type system

Look at the factory signatures in Postgres.java:

public static PostgreQuery.WithSpec<Select> query() {
    return PostgreQueries.simpleQuery();
}

public static PostgreInsert._PrimaryOptionSpec singleInsert() {
    return PostgreInserts.singleInsert();
}

public static PgSingleDeleteSpec<Delete, ReturningDelete> singleDelete() {
    return PostgreDeletes.simpleDelete();
}

Each chain method returns a different interface representing a legal state of the statement. The generic parameter (Select, Expression, SubQuery, Statement._BatchSelectParamSpec) tells you what the terminal method will produce. That is how Army encodes the SQL grammar into Java's type system: you literally cannot call .where() before .from(), because the intermediate interface does not declare it, and you cannot accidentally execute a half-built statement, because only the terminal interfaces (...Spec) expose .asQuery() / .asInsert() / .asDelete() / .asReturningDelete().

The README's own example makes the point bluntly:

SQLs.query()
    .from(Stock_.T, AS, "s")     // ✅ WordAs
//  .from(Stock_.T, ON, "s")    // ❌ COMPILE ERROR: wrong type

This is the "phantom builder" pattern carried to the scale of a full SQL grammar. The cost is a very large interface surface — the criteria package alone has ~90 top-level types (_PrimaryOptionSpec, _SingleWithSpec, PgSingleDeleteSpec, ...) — but the payoff is that misuse is mostly unrepresentable.

3. From Fluent Chain to SQL Text: the Parse Pipeline

When you hold a Select, you hold an immutable criteria object — no SQL yet. The rendering pipeline is small conceptually:

Criteria statement (Select / Insert / Update / Delete / Values)
        │
        ▼
DialectParser  ── sealed permits ArmyParser (3,551 lines)
        │
        ▼
Stmt  (SimpleStmt | BatchStmt | GeneratedKeyStmt | PairStmt)
        │
        ▼
StmtExecutor  ── JDBC (army-jdbc) or the reactive driver layer

The SPI is a sealed interface in army-core:

public sealed interface DialectParser permits ArmyParser {

    Stmt insert(InsertStatement insert, SessionSpec sessionSpec);

    Stmt update(UpdateStatement update, boolean useMultiStmt, SessionSpec sessionSpec);

    Stmt delete(DeleteStatement delete, boolean useMultiStmt, SessionSpec sessionSpec);

    Stmt select(SelectStatement select, boolean useMultiStmt, SessionSpec sessionSpec);

    Stmt values(Values values, SessionSpec sessionSpec);

    default Stmt dialectDml(DmlStatement statement, SessionSpec sessionSpec) {
        throw new UnsupportedOperationException();
    }
    // ... printStmt(Stmt, boolean beautify), identifier quoting, typeName(...)
}

Two details matter. First, sealed ... permits ArmyParser: there is exactly one parser implementation, and dialect differences are handled inside it through context objects (_SelectContext, _InsertContext, _ValuesContext, ...), one per statement kind. Second, dialectDml / dialectDql exist precisely so PostgreSQL's RETURNING and MySQL's LOAD DATA don't have to be forced into the portable shape — dialect escape hatches are first-class SPI methods, not reflection-based hacks.

Every parameter is bound (there is no string concatenation path), and printStmt can render the exact SQL with ? placeholders or inlined literals for logging — useful when debugging a 40-line generated query.

4. The Static Metamodel: Compile-Time Table and Field Metadata

jOOQ generates its metamodel from the live database. Army goes the JPA route but without a runtime metamodel: an annotation processor reads your @Table classes and emits a sibling class.

The processor is refreshingly small and direct (ArmyMetaModelDomainProcessor):

@SupportedAnnotationTypes("io.army.annotation.Table")
@SupportedSourceVersion(SourceVersion.RELEASE_25)
public class ArmyMetaModelDomainProcessor extends AbstractProcessor {

    @Override
    public boolean process(Set<? extends TypeElement> annotations, RoundEnvironment roundEnv) {
        final Set<? extends Element> elementSet;
        elementSet = roundEnv.getElementsAnnotatedWith(Table.class);
        if (elementSet.isEmpty()) {
            return false;
        }
        final TableAnnotationHandler handler;
        handler = TableAnnotationHandlerImpl.create(this.processingEnv, new StringBuilder(30));
        generateTableStaticModelClass(elementSet, handler);
        // ... error aggregation, then Filer writes sources in batches of 50
    }
}

@Table carries real schema metadata — name, comment, catalog, schema, indexes, DDL mode — and its Javadoc even documents a placeholder system (${DEFAULT}, ${RUNTIME}) resolved at startup from META-INF/army/TableMeta.properties, so generated names can be overridden per environment without recompiling domain classes.

The output is the X_ class you saw in the query:

public abstract class Stock_ {
    public static final SimpleTableMeta<Stock> T;

    public static final String NAME = "name";
    public static final String OFFER_PRICE = "offerPrice";

    public static final FieldMeta<Stock> name = T.field(NAME);
    public static final PrimaryFieldMeta<Stock> id = T.id();
}

FieldMeta<Stock> is where type safety pays off. Stock_.offerPrice.desc(), Stock_.status.equal(StockStatus.ACTIVE), Stock_.id.in(SQLs::rowParam, ids) — every predicate is a typed expression on a typed field. Rename the Java field, and every query referencing it fails to compile; the string "offer_price" lives in exactly one place.

The primary-key story is also worth a glance: the README example wires a Snowflake generator per field:

@Generator(value = "io.army.generator.snowflake.Snowflake8Generator",
        params = @Param(name = "startTime", value = "1779012232202"))
@Column
private long id;

5. DataType and MappingType: Why Dialects Need Two Layers

This is, in my opinion, the best-designed part of the framework, and it answers the question "why is this not just jOOQ?"

Most tools flatten type mapping into one step: database type name → Java class. Army splits it into two sealed hierarchies with different jobs.

DataType answers "what is this database type?" Every dialect enum implements it:

public enum PgType implements SQLType {
    BOOLEAN("BOOLEAN", ArmyType.BOOLEAN, Boolean.class),
    BIGINT("BIGINT", ArmyType.BIGINT, Long.class),
    DOUBLE("DOUBLE PRECISION", ArmyType.DOUBLE, Double.class),
    TIMESTAMPTZ("TIMESTAMPTZ", ArmyType.TIMESTAMP_WITH_TIMEZONE, OffsetDateTime.class),
    JSONB("JSONB", ArmyType.JSONB, String.class),
    UUID("UUID", ArmyType.DIALECT_TYPE, java.util.UUID.class),

    INT4RANGE("INT4RANGE", ArmyType.RANGE, String.class),
    TSTZRANGE("TSTZRANGE", ArmyType.RANGE, String.class),
    INT4MULTIRANGE("INT4MULTIRANGE", ArmyType.RANGE, String.class),
    // ...

    BOOLEAN_ARRAY(BOOLEAN),
    // dozens more array constants built from their component type
}

Each constant bundles three facts: the SQL type name, a cross-dialect semantic tag (ArmyType), and a default Java class. That middle column is the trick: MySQLType.BIGINT_UNSIGNED and PgType.BIGINT are different database types, but ArmyType lets framework code ask semantic questions — isUnsigned(), isTimeType(), isDecimalType() — without knowing the dialect.

MappingType answers "how does Java talk to JDBC for this value?"

public sealed interface MappingType extends TypeMeta, TypeInfer, TypeItem
        permits AbstractMappingType, StructMappingType {

    Class<?> javaType();

    DataType map(ServerMeta meta) throws UnsupportedDialectException;

    Object beforeBind(DataType dataType, MappingEnv env, Object source);

    Object afterGet(DataType dataType, MappingEnv env, Object source);
}

Given a running server (ServerMeta), map resolves the actual DataType; beforeBind converts a Java object to what the JDBC driver expects, and afterGet converts it back. Conversion behavior is therefore a property of the type, not of a per-column annotation or a per-query call.

The payoff is visible in the unsigned-integer handling, which I checked in source rather than taking the README's word for it:

// TinyIntUnsignedType.java
/// This class representing the mapping from Short to (unsigned TINY) INT.
public Class<?> javaType() {
    return Short.class;
}

MySQL TINYINT UNSIGNED ranges 0–255, which does not fit a Java byte (-128–127), so Army maps it to Short. SMALLINT UNSIGNED → Integer, INT UNSIGNED → Long, BIGINT UNSIGNED → BigInteger — the smallest type that cannot overflow, chosen for you. The dispatch table lives in AbstractMappingType (TINYINT_UNSIGNED → TinyIntUnsignedType, and so on). In a single-layer design this kind of range-aware choice ends up scattered across hand-written converters.

The same mechanism makes enums work with zero ceremony: CodeEnum (integer code), LabelEnum (string label), and NameEnum (Java name) are mapping types — no @Enumerated, no TypeHandler registration.

6. PostgreSQL Is a First-Class Citizen, Not a Setting on a Dialect Enum

The dialect-neutral API gets you SELECT/INSERT/UPDATE/DELETE with joins, CTEs, windows, and predicates. But the PostgreSQL module exposes the whole grammar. Browsing Postgres.java is like reading a PostgreSQL keyword index:

public static final DoubleColon DOUBLE_COLON = PostgreWords.SymbolDoubleColon.DOUBLE_COLON;
public static final SQLs.DualOperator DARROW = DualExpOperator.DARROW;   // ->>
public static final SQLs.DualOperator BI_ARROW = DualExpOperator.BI_ARROW; // ->
public static final SQLs.BiOperator AT_GT = PgDualBoolOperator.AT_GT;      // @>
public static final ExtractTimeField TIMESTAMPTZ = ...;

The example tests exercise features most Java SQL libraries don't reach at all. Recursive CTEs with SQL-standard SEARCH BREADTH FIRST ordering (PostgreSQL 14+):

stmt = Postgres.query()
        .withRecursive("cte").as(sw -> sw.select(ChinaRegion_.id, ChinaRegion_.parentId,
                                ChinaRegion_.name, ChinaRegion_.createTime)
                        .from(ChinaRegion_.T, AS, "t")
                        .where(ChinaRegion_.id.in(SQLs::rowLiteral, extractRegionIdList(regionList)))
                        .union()
                        .select(/* ... */)
                        .from(ChinaRegion_.T, AS, "t")
                        .join("cte").on(ChinaRegion_.id::equal,
                                SQLs.refField("cte", ChinaRegion_.PARENT_ID))
                        .asQuery()
                ).search(s -> s.breadthFirstBy(ChinaRegion_.ID, ChinaRegion_.CREATE_TIME)
                        .set("orderCol"))
        .space()
        .select(s -> s.space("cte", PERIOD, ASTERISK))
        .from("cte")
        .asQuery();

Server-side cursors via DECLARE/FETCH are a statement type, with the cursor surfaced as a SyncStmtCursor from ResultStates:

stmt = Postgres.declareStmt()
        .declare("my_china_region_cursor").cursor()
        .forSpace()
        .select("c", PERIOD, ChinaRegion_.T)
        .from(ChinaRegion_.T, AS, "c")
        .where(ChinaRegion_.id.in(SQLs::rowParam, extractRegionIdList(regionList)))
        .orderBy(ChinaRegion_.id)
        .limit(SQLs::literal, regionList.size())
        .asQuery()
        .asCommand();
// ...
try (SyncStmtCursor cursor = states.nonNullOf(SyncStmtCursor.SYNC_STMT_CURSOR)) {
    ChinaRegion<?> region;
    while ((region = cursor.next(ChinaRegion_.CLASS)) != null) { ... }
}

Domain inserts (parent/child tables in one statement), RETURNING with generated keys, MERGE, and VALUES statements each have their own test class and clause interfaces.

Ranges, arrays, JSONB, and vectors. PgType declares all six range and six multirange types, JSON/JSONB, UUID, geometric types, and arrays built compositionally (BOOLEAN_ARRAY(BOOLEAN), and the pattern extends to INT4RANGE_ARRAY, JSONB_ARRAY, ...). MappingType.arrayTypeOfThis() means every scalar mapping knows how to construct its array counterpart — the 7.5k-line army-array module is the systematic execution of that one method.

For pgvector, VectorType maps float[] and documents both pgvector and MySQL 9.7's native vector type as targets, while the six distance operators are typed methods on TypedField:

Expression spaceL2Distance(BiFunction<TypedField, String, Expression> namedOperator);
Expression spaceCosineDistance(BiFunction<TypedField, String, Expression> namedOperator);
Expression spaceHammingDistance(BiFunction<TypedField, String, Expression> namedOperator);
Expression spaceJaccardDistance(BiFunction<TypedField, String, Expression> namedOperator);

(The BiFunction style — passing SQLs::param or SQLs::literal to choose binding strategy — recurses through the whole API.)

The MySQL side mirrors this with its own vocabulary. MySQLs exposes the modifier keywords as constants (DISTINCTROW, HIGH_PRIORITY, STRAIGHT_JOIN, SQL_CALC_FOUND_ROWS, LOW_PRIORITY, DELAYED, QUICK, IGNORE), a dedicated loadDataStmt() factory for LOAD DATA INFILE, and multi-table UPDATE/DELETE grammars. Nothing here is simulated on a lowest-common-denominator AST.

Dialect compatibility at the call site

For portable code, there's a tiny SOURCE-retention annotation IDEs can surface:

@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.SOURCE)
public @interface Support {
    Database[] value();
}

Mark a pgvector expression @Support(PostgreSQL) and the IDE tells you at the call site what database it requires.

7. Sessions: Plain Objects In, Plain Objects Out

The session API deliberately keeps execution separate from statement construction. Session is sealed (permits LocalSession, RmSession, PackageSession); blocking code uses SyncSession / SyncLocalSession from army-sync, which offers the result-mapping variants you'd expect:

<R> R queryOne(SimpleDqlStatement statement, Class<R> resultClass);
<R> R queryOneObject(SimpleDqlStatement statement, Supplier<R> constructor);
<R> List<R> queryObjectList(DqlStatement statement, Supplier<R> constructor);
<R> Stream<R> queryObject(DqlStatement statement, Supplier<R> constructor);
<T, R> R pagingObject(PagingPair pagingPair, Supplier<T> constructor,
                      PageConstructor<T, R> pageConstructor);

Three mapping modes, deliberately:

Class<R> — scalar/simple mapping;
Supplier<R> constructor reference — your POJO, assembled reflectively through its constructor or accessors, then fully detached;
Function<CurrentRecord, R> — ad-hoc row mapping when the shape is local to the query (the example suite uses RowMaps::hashMap).

Factory construction is a conventional builder, taken verbatim from the example test support:

SyncSessionFactory factory = SyncFactoryBuilder.builder()
        .name(mapDatabaseToFactoryName(database))
        .packagesToScan(Collections.singletonList("io.army.example.bank.domain"))
        .datasource(DataSourceUtils.createDataSource(database))
        .environment(createEnvironment(database))
        .jsonCodec(FastJsonCodec.getInstance())
        .fieldGeneratorFactory(new SimpleFieldGeneratorFactory())
        .build();

Note what is not there: no transaction demarcation API in user code. Spring users get ArmySyncLocalTransactionManager and SpringSyncSessionContext from army-spring, so @Transactional works as usual — Army honors its own boundary ("transactions → use Spring or JTA") even inside its codebase. DDL generation exists as a parser capability (schemaDdl(SchemaResult)), but migrations themselves are left to Flyway/Liquibase.

The ecosystem modules extend the same philosophy upward: army-spring-ai-vector-store implements Spring AI's VectorStore over the JSONB + vector mapping types (ArmyVectorStore, with a dedicated PgVectorFilterExpressionConverter for metadata filters), and army-spring-ai-model-chat-memory persists chat memory through criteria statements rather than raw JDBC.

8. What the Trade-offs Actually Are

A deep-dive should be honest about costs, and Army has clear ones.

The interface surface is enormous. Staged builders for the complete PostgreSQL and MySQL grammars produce hundreds of *Spec / *Clause interfaces. That is inherent to encoding grammar legality in types, but the learning curve is real: you learn the method chain as much as the library. The project maintains extensive internal chain documentation (its .trae/skills directory contains dozens of method-chain guides, e.g. Postgres.singleDelete() → ... → asDelete()), which is both evidence of the depth and of how much depth there is to learn.

Java 25 is required. @SupportedSourceVersion(RELEASE_25), maven.compiler.source = 25. You cannot adopt this on a conservative LTS shop. (The code also uses a project convention worth knowing: types starting with _ are framework-private — _SelectContext, _ArmyNoInjectionType — and user code should never depend on them.)

Version and maturity. 0.6.8-SNAPSHOT, with 0.6.7 the Maven Central release. Oracle and SQLite modules are thin (a few hundred lines) compared with PostgreSQL/MySQL, and the reactive layer is mid-evolution (the example factory contains a commented-out ReactiveFactoryBuilder path driven by an external io.jdbd driver SPI). This is an ambitious single-maintainer project, not a foundation-governed one — evaluate accordingly.

Philosophical. If your objection to ORMs is that they hide SQL, Army may feel like more machinery than writing SQL, not less — you must learn its vocabulary (PERIOD, space(), comma(), SQLs::param vs SQLs::literal, ref-field helpers) to express what sql/ files give you directly. The bet is that the compile-time checking, dialect awareness, and composability repay that vocabulary.

Verdict

Army is one of the most thorough attempts I've seen to answer a specific question: what would a Java SQL API look like if dialect fidelity, rather than portability, were the primary design goal?

Its answers are internally consistent and visible in code rather than just claimed in docs:

grammar legality encoded as staged builder interfaces, with terminal methods producing sealed statements;
a DataType/MappingType split that separates "what the database is" from "how Java binds it," which makes unsigned integers, enums, arrays, JSONB, ranges, and vectors systematic instead of ad hoc;
an annotation-processed metamodel so field references are compiler-checked symbols;
a sealed DialectParser SPI with explicit dialect-only escape hatches;
and a disciplined refusal to become an ORM — connections, transactions, caching, and migrations stay in the surrounding ecosystem.

If you are on modern Java, work heavily in PostgreSQL or MySQL, regularly use features JPA pretends don't exist (RETURNING, multi-table deletes, range types, LOAD DATA, server-side cursors, vector search), and still want compile-time checking and plain POJO results, Army is worth an afternoon with the example module. The bar for "fluent SQL in Java" is high mostly because jOOQ exists — but nobody is going to confuse the two after seeing a withRecursive(...).search(breadthFirstBy(...)) chain that compiles.

Source, specification, and runnable examples: https://github.com/PillArmy/army

Maven: io.qinarmy:army-jdbc + io.qinarmy:army-postgre (or army-mysql). Start with the army-example test suite — it is the most accurate documentation in the repository.

DEV Community: zoro ma

Inside Army's MySQL Dialect: When SQL Dialects Are First-Class Citizens

Inside Army's MySQL Dialect: When SQL Dialects Are First-Class Citizens

1. Module Anatomy: Three Layers

2. The Entry Point: 18 Factories on MySQLs

3. The Type System: MySQL's Quirks Encoded in an Enum

3.1 Unsigned integers: the smallest Java type that cannot overflow

3.2 Where the proprietary types land

4. SELECT: A Typed Transcription of the MySQL Manual

4.1 SELECT modifiers: all 16 keywords modeled

4.2 Partition selection: .partition(p0, p1)

4.3 Index hints: USE / IGNORE / FORCE INDEX × three purposes

4.4 Optimizer hints: the full ~30-item /*+ ... */ catalog

4.5 STRAIGHT_JOIN, WITH ROLLUP, and locking clauses

4.6 MySQL-ization in the renderer: backticks and LIMIT m, n

5. DML: UPSERT, REPLACE, and Multi-Table Syntax

5.1 ON DUPLICATE KEY UPDATE — down to the 8.0.19 row alias

5.2 REPLACE: a standalone statement with its own limits

5.3 Multi-table UPDATE / DELETE

6. LOAD DATA: An "Ops Command" Made Type-Safe

7. SET Statements and User Variables: Home for MySQL Procedural Tricks

7.1 setStmt(): four scopes

7.2 User variables and inline := assignment

8. The Function Libraries: JSON, Spatial, and Aggregate Depth

9. DDL: Table Options Are Part of the Dialect Too

10. Philosophy and Costs

What it gets right

The costs are equally real

Closing

Army vs jOOQ

A SQL DSL where JDBC is just one executor: studying Army against jOOQ

1. JDBC is not the foundation. It is one pluggable executor

2. A compile-time clause state machine — hidden behind exactly one public entry point

How jOOQ encodes the same grammar

A 2009 fork, not a 2023 discovery

3. The parameter/literal/type decision happens at expression-node creation

4. The public API is interfaces, sealed; implementations are package-private

5. Why I think this matters for AI — beyond hype

6. The honest counter-section

Appendix: how to verify

Army: A Deep Dive into Java's Most Dialect-Aware Type-Safe SQL DSL

Army: A Deep Dive into Java's Most Dialect-Aware Type-Safe SQL DSL

1. What the Codebase Looks Like

2. The Query You Actually Write

Staged interfaces: the clause order is the type system

3. From Fluent Chain to SQL Text: the Parse Pipeline

4. The Static Metamodel: Compile-Time Table and Field Metadata

5. DataType and MappingType: Why Dialects Need Two Layers

6. PostgreSQL Is a First-Class Citizen, Not a Setting on a Dialect Enum

Dialect compatibility at the call site

7. Sessions: Plain Objects In, Plain Objects Out

8. What the Trade-offs Actually Are

Verdict

2. The Entry Point: 18 Factories on `MySQLs`

4.2 Partition selection: `.partition(p0, p1)`

4.4 Optimizer hints: the full ~30-item `/+ ... /` catalog

4.6 MySQL-ization in the renderer: backticks and `LIMIT m, n`

7.1 `setStmt()`: four scopes

7.2 User variables and inline `:=` assignment