DEV Community: zoro ma

When the Dialect IS the API: Reading Army and jOOQ Side by Side, Through PostgreSQL Source

zoro ma — Sun, 20 Sep 2026 16:58:24 +0000

Army (the subject of this article): https://github.com/PillArmy/army
jOOQ (the comparison target): https://github.com/jOOQ/jOOQ

Versions & methodology: Army at 0.6.8-SNAPSHOT (Java 25); jOOQ from a local source checkout at 3.22.0-SNAPSHOT (commit 41e84edc85, 2026-06-25, git describe = version-3.21.0.RC1-140-g41e84edc85, root pom <java.version>25</java.version>). Every Army code block below is a verbatim excerpt (file path + line numbers given). The jOOQ open-source checkout contains no tests and no example modules (see §7.3), so jOOQ code appears in two flavors: verbatim quotes of source signatures/javadoc (cited) and hand-written sketches against verified API signatures (explicitly marked). Every claim is reproducible from both codebases unless stated otherwise. Data collected 2026-09-20.

0. TL;DR

Both projects let you write SQL in Java, type-safely — with opposite philosophies:

	Army	jOOQ (open-source edition)
What a dialect is	The API itself — dialect capabilities are visible on `Postgres.java`'s method chains	A render parameter — one `DSL` serves all dialects; `SQLDialect.POSTGRES` is just a switch
PostgreSQL statements	RETURNING, ON CONFLICT, MERGE, server-side DECLARE/FETCH/MOVE/CLOSE cursors, DISTINCT ON, SET/SHOW	RETURNING, ON CONFLICT, DISTINCT ON, MERGE (also used as an UPSERT emulation)
Types	54 array types, 6 range + 6 multirange, RECORD, REF_CURSOR (`PgType` fallback catalog; strongly-typed mappings in §3.5 deep dive)	Strongly-typed `Range<T>` family, `Inet`/`Hstore`/`Ltree`/`Citext` (extensions module)
Advanced types	arrays: 54 prefab types; range→Guava `Range`/`RangeSet` (optional module); composite→`@DefinedType` POJO; pgvector built in (`float[]` + 6 distance operators)	arrays: generic lifting; range: built-in value family; composite: codegen `UDTRecord`; no pgvector in OSS (grep-verified)
Shared gaps	COPY and LISTEN/NOTIFY absent on both sides (grep-verified) — the only shared starting line

A delightful coincidence in scale: jOOQ's single DSL.java is 39,746 lines — almost exactly the size of Army's entire army-postgre module: 39,742 lines. One file holds the API for every dialect; one module holds the API for one dialect. That's where this article begins.

1. The Contenders

	Army	jOOQ
Self-description	README: "Army is not an ORM framework. It doesn't generate schemas, manage connections, or cache result sets…"	`README.md:52`: "jOOQ's main feature is typesafe, embedded SQL, allowing for IDE auto completion of SQL syntax…"
Java baseline	25 (compiler release in pom)	25 (root `pom.xml:51` `<java.version>25`) — yes, identical baselines
License	Apache-2.0	Open-source edition Apache-2.0 (commercial editions separate)
Size	army-postgre module: 78 files / 39,742 LOC; dialect grammar-contract package `io.army.criteria.postgre`: 17 files / 2,180 LOC	jOOQ core: 2,126 files / 663,534 LOC; jOOQ-postgres-extensions: 63 files / 4,790 LOC
Stage	0.6.x	3.22.0-SNAPSHOT (a 20+ year project)
Doc anchoring	1,243 links to postgresql.org inside army-postgre sources	Only 18 postgresql.org links in jOOQ core (dialect knowledge lives in code, not comments)

Line counts measured with find + wc (2026-09-20); link counts via grep -rho postgresql.org | wc -l. Neither number says who is "better" — they show two styles: Army stitches the PostgreSQL manual into its Javadoc; jOOQ weaves dialect knowledge into its renderer.

2. Two Worldviews: Dialect-as-API vs Dialect-as-Render-Parameter

2.1 Army: `Postgres.java`, one door per dialect

Every Army database gets its own entry class. PostgreSQL's is Postgres.java with 18 static factories (verified one by one):

singleInsert()   query()            batchQuery()       subQuery()
scalarSubQuery() singleUpdate()     batchSingleUpdate() singleDelete()
batchSingleDelete() valuesStmt()    subValues()        declareStmt()
closeCursor(String) closeAllCursor() singleMerge()     setStmt()
show(String)     showAll()

Two observations:

No multiUpdate()/multiDelete() — grep for multiUpdate|multiDelete|batchMulti across army-postgre returns zero hits. Multi-table UPDATE/DELETE is a MySQL-module-only API; on the PG side the method simply does not exist. Nonexistence = unsupported = the compiler blocks it on the database's behalf.
declareStmt() is PG-exclusive — server-side cursors have no counterpart on the MySQL entry class. Dialect capability shows up directly as "which factories exist".

2.2 jOOQ: one DSL plus one enum switch

All jOOQ dialects share a single DSL facade (org/jooq/impl/DSL.java, 39,746 lines). The dialect is a SQLDialect enum plus render-time branches. In the open-source checkout there is exactly one PostgreSQL constant — the family (SQLDialect.java:425-435, verbatim):

/**
 * The PostgreSQL dialect family.
 * <p>
 * This family behaves like the versioned dialect {@link #POSTGRES_18}.
 ...
 */
POSTGRES("Postgres", false, true, new RequiredVersion(18, null, null), SQLDialectCategory.POSTGRES),

Its class javadoc (SQLDialect.java:70-74) is blunt: "The open source jOOQ distributions only support the dialect family, which corresponds to the latest supported dialect version of the commercial distribution." — the OSS edition ships exactly one PostgreSQL flavor: "latest" (18). Versioned dialects (POSTGRES_9_3…POSTGRES_14 etc.) are a commercial-edition capability (in this checkout those names appear only in javadoc prose, not as enum constants).

2.3 Three corollaries of the philosophy gap

Corollary 1: when errors surface. Army expresses "this dialect doesn't support it" as "this method doesn't exist" — the error stops at compile time. jOOQ expresses it as a render branch: the same statement behaves differently under a different SQLDialect. jOOQ pays with emulation; e.g., ILIKE is rewritten as LOWER(x) LIKE LOWER(y) on dialects that lack it (Like.java:193-198, verbatim):

// [#1423] [#9889] PostgreSQL and H2 support ILIKE natively. Other dialects
// need to emulate this as LOWER(lhs) LIKE LOWER(rhs)
switch (op) {
    case LIKE_IGNORE_CASE:
    case NOT_LIKE_IGNORE_CASE:
        if (NO_SUPPORT_ILIKE.contains(ctx.dialect())) {

On the Army side, ILIKE appears only in the PG keyword table (_PostgreDialectUtils.java:253: keywords.add("ILIKE")) — where there is nothing to emulate, there is no emulation; where the database is native, the SQL goes native.

Corollary 2: portability is paid for differently. With jOOQ one statement can run its unit tests on H2 and its production on PostgreSQL (emulation bridges the gap). With Army, switching from SQLs.query() to Postgres.query() locks the dialect — in exchange for determinism about target-database behavior.

Corollary 3: the shape of documentation. Army anchors 1,243 official-doc links into its Javadoc; jOOQ encodes the same knowledge as dialect-branch code comments. The first favors learners; the second favors maintaining 660k lines.

3. Type Systems: Two Different PG Dictionaries

3.1 Army: `PgType` — broad and literal

The full PG type set lives in PgType.java (in army-core). Its dialect slice, verbatim:

JSON("JSON", ArmyType.JSON, String.class),
JSONB("JSONB", ArmyType.JSONB, String.class),
JSONPATH("JSONPATH", ArmyType.DIALECT_TYPE, String.class),
MACADDR("MACADDR", ArmyType.DIALECT_TYPE, String.class),
MACADDR8("MACADDR8", ArmyType.DIALECT_TYPE, String.class),
INET("INET", ArmyType.DIALECT_TYPE, String.class),
CIDR("CIDR", ArmyType.DIALECT_TYPE, String.class),

INT4RANGE("INT4RANGE", ArmyType.RANGE, String.class),
INT8RANGE("INT8RANGE", ArmyType.RANGE, String.class),
NUMRANGE("NUMRANGE", ArmyType.RANGE, String.class),
TSRANGE("TSRANGE", ArmyType.RANGE, String.class),
TSTZRANGE("TSTZRANGE", ArmyType.RANGE, String.class),
DATERANGE("DATERANGE", ArmyType.RANGE, String.class),

INT4MULTIRANGE("INT4MULTIRANGE", ArmyType.RANGE, String.class),
... (6 multiranges total, L112-117)

(From PgType.java:95-117)

Also present: MONEY, PG_LSN, PG_SNAPSHOT, TSVECTOR/TSQUERY, the geometric family (POINT/CIRCLE/PATH/BOX/LINE/LSEG/POLYGON, all mapped to String), RECORD (→ SqlRecord), REF_CURSOR, ACLITEM.

Arrays are Army's showcase: from ACLITEM_ARRAY to RECORD_ARRAY there are 54 *_ARRAY constants, built by a constructor referencing the element type (PgType.java:126-135):

ACLITEM_ARRAY(ACLITEM),
BOOLEAN_ARRAY(BOOLEAN),
...
TIMESTAMPTZ_ARRAY(TIMESTAMPTZ),
JSONB_ARRAY(JSONB),
INT4RANGE_ARRAY(INT4RANGE),
INT4MULTIRANGE_ARRAY(INT4MULTIRANGE),

Even range/multirange get their own arrays. The parser counterpart is PostgreArrays in army-postgre (parseArrayText handles '{{1 , 2} , { 3, 4}}' and custom-subscript forms like '[-1:0][-2:-1]=...' — see the test comments in PostgreArraysUnitTests.java:38-67).

3.2 jOOQ: an internal core catalog plus a strongly-typed extensions module

jOOQ's PG types come in two layers:

Internal core catalog: org/jooq/impl/SQLDataTypes.java hosts a package-private PostgresDataType (L2606-2608) covering system types like INT2/INT4/INT8/FLOAT8/BOOL/TEXT/TIMESTAMPTZ/BYTEA/INTERVAL/OIDVECTOR — internal implementation, no public PostgreSQLDataType class.
The open-source jOOQ-postgres-extensions module (63 files / 4,790 LOC): the real "PG dictionary" — a Range<T> interface with discrete-range base classes, plus one class per type:

PG type	jOOQ extension class (all in `org.jooq.postgres.extensions.types`)
`int4range`	`IntegerRange`
`int8range`	`LongRange`
`numrange`	`BigDecimalRange`
`daterange`	`LocalDateRange` (alias `DateRange`)
`tsrange`	`LocalDateTimeRange` (alias `TimestampRange`)
`tstzrange`	`OffsetDateTimeRange`
`ltree`	`Ltree`
`inet` / `cidr`	`Inet` / `Cidr` (with IP-prefix semantics)
`hstore`	`Hstore`
`citext`	`Citext` (as converters)

Each type ships a Converter/Binding (including *ArrayBinding variants).

3.3 Range types: `String` vs `Range<T>` — the biggest fork in this comparison

The same tstzrange column:

Army maps it to String at the PgType level (TSTZRANGE(..., String.class)). Pro: zero magic, WYSIWYG. Con: range arithmetic happens at the string/SQL-expression level, by hand. But that is only the fallback layer — object semantics are one optional module away: attach army-guava and you get Guava Range<T> directly (§3.7).
jOOQ maps it to an OffsetDateTimeRange object with a Range<T> interface (contains, discrete stepping, etc.) and a dedicated Binding. Pro: business code manipulates objects. Con: one more abstraction and binding protocol.

A direct clash of design values: Army picks "literal + broad" (54 arrays, RECORD, REF_CURSOR all present); jOOQ picks "deeply typed" (Range family, Inet prefixes, Hstore). It depends on the team — and let me preempt a misconception you get from reading PgType alone: although Army's range/multirange map to String at the SQL-catalog level, there is a strongly-typed channel at the MappingType layer (the optional army-guava module maps them to Guava Range<T>/RangeSet<T>, detailed in §3.7); the jOOQ open-source extensions still have no multirange classes (none exist in the module).

3.4 pgvector: a correction — Army has it; the jOOQ OSS edition does not

The first draft of this article claimed "pgvector is absent on both sides" — a wrong conclusion reached by grepping only the PgType constants. Corrected here, with apologies: Army's PgType enum genuinely has no VECTOR constant (vector is an extension type, not a built-in SQL type), but pgvector is fully supported at three layers — MappingType, the expression DSL, and the Spring AI integration; the jOOQ open-source checkout genuinely lacks it. Evidence and comparison in §3.9.

3.5 Deep-dive map: array / range / composite / vector across four dimensions

The next four sections compare each type family with the same framework: mapping (how Java types correspond to PG types), design (why it is modeled that way), implementation (what actually happens on the JDBC wire), and usage (what user code looks like).

Start with one counter-intuitive finding that runs through all four: neither framework uses the JDBC 4 java.sql.Array / java.sql.Struct channels. A grep for createArrayOf/createStruct across the whole army-jdbc module returns zero hits; jOOQ core does not call them either (matches only in the JDBC wrappers DefaultConnection/MockConnection). The two projects independently made the same choice — PostgreSQL's text input/output protocol (arrays '{1,2,3}', ranges '[1,5)', composites '(a,b)', vectors '[1,2,3]' plus a server-side cast), letting PostgreSQL parse the complex types itself. jOOQ even writes the reason into its code (§3.6). So the comparison is not about which protocol — it is about what kind of type system each side built on top of it.

3.6 ARRAY

Mapping.

Army: a two-tier "prefab catalog + auto-inference" system. The SQL-catalog tier is PgType's 54 *_ARRAY constants (§3.1); the MappingType tier is the standalone army-array module — 43 named array types (IntegerArrayType, TextArrayType, UUIDArrayType, JsonbArrayType, BitSetArrayType, IntervalArrayType… and even CompositeArrayType, VectorArrayType, MappingTypeArrayType). Primitive/primitive-wrapper array fields are inferred automatically (PgMappingHandler.java:134-173 returns XxxArrayType.UNLIMITED per element type); other arrays fall back to StringArrayType (L179-180: type.isArray() → StringArrayType), or are declared explicitly with @Mapping.
jOOQ: one "generic lifting" mechanism. Any DataType<T> becomes DataType<T[]> via getArrayDataType() (ArrayDataType.java:66-81; the constructor takes elementType.getBinding().arrayBinding() and converts elements reflectively with Array.newInstance); DDL rendering emits the suffix "[]" for the POSTGRES family (ArrayDataType.java:227-244). Arrays of Range/Inet/Hstore etc. are covered by the extensions' *ArrayBinding classes.

Design. The catalog gives every PG array flavor (multi-dimensional, fixed bit, interval) a named home with docs; the generic mechanism is orthogonal — a new element type gains arrays automatically, no 44th class needed.

Implementation. Army's text codec is PostgreArrays.java in army-core (note: core, not the dialect module — the text protocol is shared knowledge). The default serializer/deserializer is comma-delimited (L45-53); element decoding follows the PG manual's array I/O syntax (the L68-72 javadoc links Array Input and Output Syntax — double-quote wrapping and backslash escaping). jOOQ's bind site is DefaultBinding.java:1610-1615; the POSTGRES branch, verbatim:

case POSTGRES:
    ctx.statement().setObject(ctx.index(), toPGArrayString(value), Types.OTHER);

Inline (non-prepared) contexts render cast(inline(PostgresUtils.toPGArrayString(value)), arrayType) (DefaultBinding.java:1482-1492); toPGArrayString handles {…} wrapping, quoting non-numbers, escaping, and multi-dim arrays (PostgresUtils.java:546-583). On read, jOOQ calls rs.getArray() and binds element by element; Army's *ArrayTypes restore from text via PostgreArrays.

Usage. Army side — verbatim from the real entity PostgreTypes.java:

// L370-376: 1-D auto-inferred, 2-D declared explicitly
//@Mapping("io.army.mapping.array.IntegerArrayType")
@Column(comment = "int array type")
public int[] intArray;

@Mapping("io.army.mapping.array.IntegerArrayType")
@Column(comment = "int 2d array type")
public int[][] int2dArray;

The same mechanism covers composite arrays and vector arrays (L460-466: @Mapping("...CompositeArrayType") public ProductInfo[] productInfoArray;, @Mapping("...VectorArrayType") public float[][] vectorArray;).

jOOQ side (no tests in the OSS checkout; this quotes the DSL javadoc verbatim, DSL.java:32579-32584):

The PostgreSQL array(select) function. …
Example: {1, 2, 3} = array(select 1 union select 2 union select 3)

The unnest(Object[]) javadoc (DSL.java:13502-13513) states: rendered as UNNEST on Postgres, TABLE on H2, and "emulated using several UNION ALL connected subqueries" elsewhere — one function, three renderings. jOOQ's philosophy in an array-shaped specimen.

3.7 RANGE / MULTIRANGE

Mapping.

jOOQ: the extensions module ships a complete set of dependency-free value types. The Range<T> interface has exactly five methods (Range.java:47-75, verbatim): isEmpty(), lower(), lowerIncluding(), upper(), upperIncluding(); six concrete types IntegerRange/LongRange/BigDecimalRange/LocalDateRange/OffsetDateTimeRange/...; no multirange classes.
Army: it does not invent Range objects — the strongly-typed mapping lives in the optional army-guava module: GuavaRangeType.java:47 — public abstract class GuavaRangeType ... implements MappingType.SqlRange, UnaryGenericsMapping — mapping a column to Guava's Range<T>; multirange maps via GuavaRangeSetType to Guava RangeSet<T>; arrays via GuavaRangeArrayType. Projects without Guava fall back to the PgType String mapping.

Design. Both faithfully implement PG's bound/empty-range semantics. jOOQ hard-codes the rules in AbstractRange.java:51-68: // In PostgreSQL, there is no [,] range, only (,) (null bounds forced open), isEmpty() = lowerIncluding && !upperIncluding && Objects.equals(lower, upper), and toString() renders the [1,5) text. Army reuses Guava Range's mature open/closed-interval semantics, and its deserializer knows PG empty ranges and infinite bounds (allowNothing(true)). An archaeological detail: PgRangeTypeUniteTests.java has its entire body commented out (the referenced PgRangeType/Int4Range classes no longer exist in the source) — a fossil of an attempted in-house value type; the final design choice was to reuse Guava instead of reinventing Range.

Implementation. Text protocol again. Army's range text parser is a configurable builder (GuavaRangeType.java:57-73, verbatim excerpt):

public static final RangeDeserializer PG_DESERIALIZER = RangeDeserializer.builder()
        .dataTypeLabel("PostgreSQL Range")
        .leftBoundaries(new char[]{'[', '('})
        .delim(_Constant.COMMA)
        .rightBoundaries(new char[]{']', ')'})
        .quoteChar(_Constant.DOUBLE_QUOTE)
        .backSlashEscapeOn(true)
        .quoteEscapeOn(true)
        .nullAsNull(false)
        .allowQuote(true)
        .allowNothing(true)   // unbounded
        .allowWhitespace(true)
        .build();

jOOQ's AbstractRangeConverter (L60-96): if ("empty".equals(s)) return empty();; otherwise split on ,, read the first char for [/(, strip double quotes, blank bound → null; the Binding extends AbstractPostgresVarcharBinding (setString/getString), and IntegerRangeBinding.castType() returns "int4range" (IntegerRangeBinding.java:50-67).

Usage. Army side — PostgreTypes.java:192-247, verbatim excerpt:

@Column(comment = "int4range type")
public Range<Integer> int4RangeGuava;

@Mapping("io.army.mapping.guava.GuavaRangeType")
...
public Range<OffsetDateTime> tstzrange;
...
@Mapping("io.army.mapping.guava.array.GuavaRangeArrayType")
public Range<OffsetDateTime>[] tstzrangeArray;
...
@Mapping("io.army.mapping.guava.GuavaRangeSetType")
public RangeSet<Long> int8multirange;

On the jOOQ side you attach IntegerRangeBinding to a column in the codegen configuration and the generated field type becomes Field<IntegerRange> (mechanism description, not a code quote — no examples exist in the OSS checkout).

Army also has a DDL ace: PostgreDdlParser.java:365-378, createType() emits CREATE TYPE/DOMAIN for the four user-defined categories SqlComposite / SqlEnum / SqlDefinedRange / SqlDomain; and the database's "ranges can't be altered" limit is encoded as a deterministic error message (L391-392: "PostgreSQL don't support alter range type").

3.8 COMPOSITE (CREATE TYPE … AS)

Mapping.

Army (Java-first): CompositeType.java:47-62 maps a top-level POJO annotated with @DefinedType(category = COMPOSITE, fieldOrder = {...}) to a PG composite type; from() validates annotation completeness and class placement at build time. The real example ProductInfo.java:14-41 — note composite fields can embed arrays and another composite type:

@MappedSuperclass
@DefinedType(name = "PRODUCT_INFO",
        fieldOrder = {"productId", "productName", "price", "available", "releaseDate", "intArray", "textArray", "managerInfo"})
public class ProductInfo implements FieldAccessPojo {
    @Column public Long productId;
    ...
    @Column public int[] intArray;
    @Column public String[] textArray;
    @Column public ManagerInfo managerInfo;   // nested composite

The annotation processor recognizes FieldType.COMPOSITE and generates the metamodel (MirrorFieldTypeParser, CompositeSourceCodeGen in army-annotation).

jOOQ (database-first): composites are reverse-generated UDTs — UDT.java:53-62: "Instances of this type cannot be created directly. They are available from generated code.", plus the ROW-construction expression Field<R> construct(Field<?>... args) (L99-104, @Support({DUCKDB, POSTGRES, YUGABYTEDB})); the runtime carrier is UDTRecord/UDTRecordImpl, the data type is UDTDataType holding the UDT reference (UDTDataType.java:50-63). Reverse engineering is done by jOOQ-meta's PostgresUDTDefinition (querying information_schema.ATTRIBUTES/pg_catalog) and jOOQ-codegen's JavaGenerator.generateUDTRecords. jOOQ also deliberately distinguishes EmbeddableRecord — "implemented purely as a code generation feature where columns in a Table should be grouped to form a synthetic UDTRecord type" (EmbeddableRecord.java:42-46): a UDT is a real database type; an embeddable is pure client-side column grouping. The two are not conflated.

Implementation. Text protocol again, arrived at independently. Army uses a dedicated PG composite text parser (CompositeType.java:65-80): ( ) boundaries, comma delimiters, double-quote escaping, nothing for null, whitespace kept inside field values. jOOQ's DefaultRecordBinding.set0 does setString(..., PostgresUtils.toPGString(value)) for the PG family (DefaultBinding.java:4629-4633), with a UDT-name cast when inlined (pgRenderRecordCast detecting UDTDataType, L4596-4606).

Metadata & DDL. On the reverse side Army fetches ENUM/RANGE/DOMAIN/COMPOSITE metadata in one big SQL (PostgreParser.java:112-164: CASE(t.typcategory) WHEN 'C' THEN 'COMPOSITE' WHEN 'E' THEN 'ENUM' WHEN 'R' THEN 'RANGE' ..., composite attributes coming from a pg_attribute LATERAL subquery); on the forward side it can not only CREATE TYPE but modifyType() also emits ALTER TYPE ... ADD/DROP/ALTER ATTRIBUTE diffs for composites (the compositeDropFieldList/compositeNewFieldList/compositeModifyFieldList at PostgreDdlParser.java:983/1007/1044) — field-level schema evolution of composite types is inside the DDL sync.

Usage. Army side, PostgreTypes.java:347-348,460-462: public ProductInfo productInfo;, public ManagerInfo managerInfo;, public ProductInfo[] productInfoArray; — inside the domain object they are plain POJO fields, stored and fetched like ordinary columns.

3.9 VECTOR (pgvector)

Army: a built-in citizen, three layers complete.

Mapping: VectorType.java:41-79, verbatim excerpt — one type serves both pgvector and MySQL 9:

public final class VectorType extends _ArmyNoInjectionType
        implements MappingType.SqlUserDefined, MappingType.SqlVector {

    public Class<?> javaType() { return float[].class; }

    public DataType map(final ServerMeta meta) throws UnsupportedDialectException {
        switch (meta.serverDatabase()) {
            case PostgreSQL: dataType = obtainDataType(); break;   // CustomType "VECTOR"
            case MySQL:
                if (meta.meetsMinimum(9, 0, 0)) { dataType = MySQLType.VECTOR; break; }
            default: throw mapError(this, meta);
        }
        return dataType;
    }

The array form is VectorArrayType in army-array (arrayTypeOfThis() → float[][]).

Query operators: TypedField.java:243-277 defines six pgvector distance operators, all @Support({PostgreSQL}): spaceL1Distance (<+> L1), spaceL2Distance (<-> L2), spaceCosineDistance (<=> cosine), spaceHammingDistance (<~> binary-vector Hamming), spaceJaccardDistance (<%> Jaccard), spaceNegDot (<#> negative inner product); implementation in OperationTypedField.
Ecosystem: the army-spring-ai-vector-store module's ArmyVectorStore.java:64 class javadoc says "Built-in pgvector integration for similarity search", with a companion PgVectorFilterExpressionConverter. Real entity field: public float[][] vectorArray; at PostgreTypes.java:464-466.

jOOQ: absent in OSS; do-it-yourself via the Binding SPI. Repo-wide (all modules) greps for pgvector, halfvec, <=>, cosine_distance return zero hits; the jOOQ-postgres-extensions supported-types list (cidr/citext/daterange/hstore/inet/int4range/int8range/ltree/numrange/tsrange/tstzrange) contains no vector (official docs: https://www.jooq.org/doc/latest/manual/code-generation/codegen-extensions/codegen-extension-postgres/ ); the community approach is a hand-written Binding with a ::vector cast in SQL (example: https://gist.github.com/yahorbarkouski/bcfbf2bf1b10ff7757f2c629eab33a46 ). To be precise: this conclusion is based on source plus the official supported-types page; a commercial-roadmap item may exist — but in the open-source checkout under comparison, it is genuinely absent.

Commentary: this contrast shows the two temperaments best — Army treats the post-2024 AI infrastructure as a dialect first-class citizen (type + operators + Spring AI, one stop), while jOOQ leaves the extension point and lets users plug in via the Binding SPI. The former is batteries-included for AI scenarios; the latter refuses to compromise on "stable core, externalized extensions".

3.10 Cross-family summary

Type	Army: mapping / carrier	jOOQ: mapping / carrier	Shared implementation trait
ARRAY	54 `PgType` constants + 43 named `*ArrayType` MappingTypes in army-array; `int[]` auto, `int[][]`/complex via `@Mapping`	generic `DataType<T[]>` lifting + extension `*ArrayBinding`; `array()/unnest()` rendered multi-dialect	neither uses `createArrayOf`; PG text `'{…}'` + cast, each side has its own multi-dim/escape codec
RANGE	army-guava `GuavaRangeType` → Guava `Range<T>` (String without Guava)	built-in `IntegerRange/OffsetDateTimeRange…` implementing `Range<T>`	both parse `[1,5)`/`( , )`/`empty` text, bind via setString
MULTIRANGE	`GuavaRangeSetType` → Guava `RangeSet<T>` (present)	no corresponding classes	—
COMPOSITE	`@DefinedType(COMPOSITE)` POJO + processor metamodel; nested composites/arrays; CREATE/ALTER TYPE in DDL sync	reverse-generated `UDTRecord` (`UDT.construct`); embeddables are a separate client-only path	neither uses `createStruct`; PG record text `(a,b)` + cast
VECTOR	built-in `VectorType` (`float[]`, PG + MySQL 9), 6 `@Support(PostgreSQL)` distance operators, Spring AI integration	absent in OSS; hand-written Binding + `::vector`	Army likewise binds via serialized text

One-line verdict: jOOQ's type extension is "one orthogonal core + external extension modules"; Army's is "an explicit catalog + optional ecosystem modules (guava/array/spring-ai)". Fewer abstractions versus more out-of-the-box types — the same worldview as the statement layer's "dialect-as-parameter vs dialect-as-API" divide, continued into the type layer.

4. Statement Capabilities, Item by Item

Capability	Army (army-postgre)	jOOQ (open-source checkout)
INSERT … RETURNING	✅ `asReturningInsert()` (PostgreInserts.java:1247)	✅ `InsertReturningStep.returning()/returningResult()` (InsertReturningStep.java:111-178)
UPDATE … RETURNING	✅ `asReturningUpdate()` (PostgreUpdates.java:549)	✅ `UpdateReturningStep` (UpdateReturningStep.java:112)
DELETE … RETURNING	✅ `asReturningDelete()` (PostgreDeletes.java:536)	✅ `DeleteReturningStep` (DeleteReturningStep.java:110)
INSERT … ON CONFLICT	✅ `.onConflict()` + conflict target (index columns/collation/operator class/`onConstraint`/`WHERE` predicate) + `doNothing()/doUpdate()`	✅ `onConflict(keys)/onConflictOnConstraint(...)/onConflictDoNothing()/doUpdate()`
MERGE	✅ `Postgres.singleMerge()` (PG15 MERGE) + tests	✅ MERGE API; also used to emulate multi-unique-key UPSERT on PG
DECLARE/FETCH/MOVE/CLOSE cursors	✅ full server-side family (`declareStmt()/closeCursor()/closeAllCursor()` + `SyncStmtCursor` + `Direction`)	❌ no DECLARE CURSOR DSL; `Cursor` is a JDBC fetchSize streaming wrapper
DISTINCT ON	✅ rendered at PostgreDialectParser.java:1170-1190	✅ `SelectDistinctOnStep.distinctOn(...)` (SelectDistinctOnStep.java:163)
Multi-table UPDATE/DELETE	❌ doesn't exist (MySQL-only)	➖ single API, rendered per dialect
pgvector	✅ `VectorType` + 6 distance operators + Spring AI (see §3.9)	❌ absent in OSS (grep-verified; hand-written Binding)
COPY / LISTEN / NOTIFY	❌ (grep-verified)	❌ (`CopyManager`: 0 hits repo-wide, grep-verified)
SET / SHOW	✅ `setStmt()/show()/showAll()` factories	(not audited in this pass)

4.1 ON CONFLICT: different strengths

Army's conflict target goes down to collation and operator classes (PostgreInsert.java:109-160: _ConflictCollateSpec.collation(...), _ConflictOpClassSpec.space(String operatorClass), _ConflictTargetOptionSpec.onConstraint(String), plus indexed columns and a WHERE index predicate inside parens(...)); jOOQ's onConflictOnConstraint accepts typed UniqueKey<R>/Constraint/Name directly (InsertOnDuplicateStep.java:114-161) — stronger when combined with codegen metadata. On the action side the two align (jOOQ signature verbatim):

// InsertOnConflictDoUpdateStep.java:90-99
@NotNull @CheckReturnValue
@Support({ CUBRID, DERBY, DUCKDB, FIREBIRD, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, SQLITE, YUGABYTEDB })
InsertOnDuplicateSetStep<R> doUpdate();
...
InsertReturningStep<R> doNothing();

4.2 MERGE: an entrée vs an emulator

MERGE, introduced in PG 15, is a first-class factory in Army (singleMerge(), javadoc linking the official doc), and MergeTests.java contains real MERGE ... RETURNING tests (L59, L111). jOOQ has MERGE APIs too — but the more interesting part is its cameo on PG: when an UPSERT conflict involves multiple unique keys and there's no RETURNING, it rewrites into MERGE as an emulation (InsertQueryImpl.java:479-494, excerpt):

case POSTGRES:
    ...
    if ((ctx.dialect().supports(POSTGRES) || ctx.dialect().supports(DUCKDB))
        && onConstraint == null && onConflict == null
        && returning.isEmpty() && table().getKeys().size() > 1) {
        acceptMerge(ctx);
    }

Same statement: one is "the user orders it off the menu", the other is "a stage prop inside the renderer" — the two philosophies in miniature.

4.3 Cursors: the deepest block of Army's PG dialect

Army ships a complete server-side cursor statement family: declareStmt() (the PostgreCursor chain) → execute to ResultStates → on SyncStmtCursor: next()/fetchOneObject(Direction...)/move(Direction.LAST)/fetch(Direction.FORWARD_ALL...), plus closeCursor(name)/closeAllCursor(). The declareStmt() javadoc embeds a complete runnable example (Postgres.java:280-317, verbatim excerpt):

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();
states = session.updateAsStates(stmt);
try (SyncStmtCursor cursor = states.nonNullOf(SyncStmtCursor.SYNC_STMT_CURSOR)) {
    while ((region = cursor.next(ChinaRegion_.CLASS)) != null) { ... }
    cursor.move(Direction.LAST);
    cursor.fetch(Direction.FORWARD_ALL, ChinaRegion_.CLASS, ResultStates.IGNORE_STATES)
            .forEach(System.out::println);

jOOQ's counterpart is client-side streaming: no DECLARE CURSOR DSL (0 grep hits repo-wide); the Cursor interface describes itself as "Cursors allow for lazy, sequential access to an underlying JDBC ResultSet." (Cursor.java:59-63), entered via ResultQuery.fetchLazy() (ResultQuery.java:438), with its own javadoc example (Cursor.java:120, verbatim):

 * try (Cursor&lt;TRecord> cursor = ctx.select(T.A, T.B).from(T).fetchLazy()) {

Fairness first: this is not better-vs-worse. jOOQ's fetchSize streaming is sufficient for most business code and portable across dialects; but "explicit DECLARE / named cursors / bidirectional FETCH/MOVE" can only be expressed in Army's API.

5. Head-to-Head: Four Same-Problem Use Cases

Round 1: batch UPSERT + RETURNING (both flexing)

Army — verbatim from InsertTests.java:274-286 (note the row alias as("c") and the existence comment on ignoreReturnIds() — this is real test code):

final ReturningInsert stmt;
stmt = Postgres.singleInsert()
        .ignoreReturnIds()  // required ,because exists doNothing
        .insertInto(ChinaRegion_.T).as("c")
        .parens(s -> s.space(ChinaRegion_.name, ChinaRegion_.parentId)
                .comma(ChinaRegion_.regionGdp)
        )
        .defaultValue(ChinaRegion_.visible, SQLs::literal, Boolean.TRUE)
        .values(regionList)
        .onConflict()
        .doNothing()
        .returningAll()
        .asReturningInsert();

jOOQ — the open-source checkout has no tests, so this is a hand-written sketch against verified API signatures (onConflict(Field...) at InsertOnDuplicateStep.java:114, doUpdate() at :92, set(Field,T) at InsertOnDuplicateSetStep.java:117, excluded(Field) at DSL.java:19243, returningResult at InsertReturningStep.java:178):

// Sketch (hand-written), not a verbatim excerpt; every method signature verified in 3.22.0-SNAPSHOT sources
Result<Record1<Long>> r = ctx.insertInto(REGION)
        .columns(REGION.NAME, REGION.PARENT_ID, REGION.GDP)
        .values("South China", 0, new BigDecimal("78000"))
        .onConflict(REGION.ID)
        .doUpdate().set(REGION.GDP, excluded(REGION.GDP))
        .returningResult(REGION.ID)
        .fetch();

Impressions: equal information density. Army's .as("c") row alias makes the excluded semantics explicit as a reference to c (same design spirit as MySQL 8.0.19 row aliases); jOOQ's excluded() reads closer to the PG manual. Army's staged interfaces mean wrong clause order doesn't compile (e.g., trying .doUpdate() before a conflict target — no such type path exists).

Round 2: cursors (see §4.3 — a shape difference, decisively in Army's favor)

Round 3: range-typed columns

Army: @Column metadata declares PgType.TSTZRANGE; the entity field is String; comparisons (=, &&, @>) happen in SQL expressions.
jOOQ: the record field is OffsetDateTimeRange; the extension Binding converts between PG text and the object.

Round 4: array columns

Army: 54 prefab *_ARRAY static types + the PostgreArrays parser (multi-dimensional and custom-subscript forms included — see §3.1 test comments).
jOOQ: any DataType<T> upgrades to DataType<T[]> via getArrayDataType() (ArrayDataType.java:65-69) plus DSL.array()/unnest() and the extensions' *ArrayBinding. A general mechanism vs a prefab catalog — philosophy again.

6. Version Strategy: Explicit Enum vs Commercial Versioned Dialects

Army's PG version enum, verbatim (PostgreDialect.java:24-36):

POSTGRE11(11),
POSTGRE12(12),
POSTGRE13(13),
POSTGRE14(14),
POSTGRE15(15),
POSTGRE16(16),
POSTGRE18(18);

Three remarks (all reproducible from source):

from(ServerMeta) maps 11–16 explicitly; everything else (including 17) falls to the default branch → POSTGRE18 (L74-99) — there is genuinely no POSTGRE17 constant.
Version constants are referenced barely once inside army-postgre main code (PostgreUtils.DIALECT = POSTGRE16, a parse-time constant) — a contrast with army-mysql's dense version gating; the PG module leans on the static "capability absent = API absent" expression instead.
Version detection happens at runtime (ServerMeta.major()); a mismatched statement errors at render time rather than being quietly emulated.

jOOQ's OSS edition ships only the family constant (≙POSTGRES_18); the RequiredVersion(18,null,null) and precedes() machinery (SQLDialect.java:1438-1500) serve the commercial edition's versioned dialects. For open-source users this means: everything renders as "latest PG"; compatibility with older servers comes from emulation branches, not version switches.

7. Metamodel Direction, Doc Density, and Openness of Tests

7.1 Metamodel: Java-first vs database-first

Army's metamodel comes from an annotation processor: @Table entities → generated ChinaRegion_ static metamodel (T, FieldMeta<T> fields); type safety grows from the Java side. army-postgre also reaches the other way: PostgreParser can query PG custom-type metadata (ENUM/RANGE/DOMAIN/COMPOSITE, reading pg_range subtype/operClass — PostgreParser.java:109-194) for DDL comparison.

jOOQ is classic database-first: jOOQ-codegen + jOOQ-meta reverse-generate Records/metamodels from schema (this checkout contains the jOOQ-codegen* and jOOQ-meta* modules). Opposite directions, different fits: legacy-schema-first teams pick codegen; domain-model-first teams pick annotation processing.

7.2 Doc density: 1,243 vs 18

army-postgre main sources contain 1,243 postgresql.org links — PostgreSyntax and the function classes cite the PG manual function by function (geometry, text search, JSON, windows…). jOOQ core has 18. Not a quality verdict — a maintenance strategy: jOOQ relies on its commercial team and tests; Army nails the evidence into the source where readers can see it.

7.3 Test openness: a difference that matters to this article's methodology

All of Army's use cases are open source: army-example carries a full PG sync-session suite (session/sync/postgre/: InsertTests/UpdateTests/DeleteTests/MergeTests/CursorTests, …), mapping tests (PostgreFullType, PostgreArraysUnitTests), dialect tests (PostgreDdlTests), and criteria unit tests (criteria/postgre/statement/PostgreUnitTests); army-postgre itself has PostgreDialectUtilsTests.

jOOQ's open-source checkout contains no tests at all: no examples module at the top level, jOOQ/src has only main (repo-wide find -type d -name test: zero hits). jOOQ's PostgreSQL integration tests live in its commercial codebase. Hence the signature-level citations plus marked hand-written sketches for jOOQ in this article — not laziness, but an objective difference in open-source transparency that happens to be one of Army's quiet advantages: anyone can clone and run every one of its use cases.

8. Summary Table

Dimension	Army	jOOQ (OSS checkout 3.22.0-SNAPSHOT)
Philosophy	Dialect-as-API, one door per database	One DSL, dialect-as-parameter
PG entry	`Postgres.java`, 18 factories	`DSL` facade + `SQLDialect.POSTGRES`
RETURNING (I/U/D)	✅ all three	✅ all three
ON CONFLICT	target supports collation/operator class/constraint name/WHERE predicate	`onConflict(keys)`/`onConflictOnConstraint(UniqueKey)` — stronger typing
MERGE	first-class (PG15) + RETURNING tests	has the API; doubles as multi-unique-key UPSERT emulation
Server-side cursors	✅ full DECLARE/FETCH/MOVE/CLOSE	❌ (client-side `fetchLazy` streaming)
DISTINCT ON	✅	✅
Range/Multirange	12 constants (String fallback) + Guava `Range<T>`/`RangeSet<T>` (optional module, §3.7)	strongly-typed built-in value family (no multirange classes)
Arrays	54 prefab `*_ARRAY` + 43 named MappingTypes in army-array + dedicated text parser (§3.6)	general `DataType<T[]>` mechanism + extension Bindings
Composite	`@DefinedType` POJO (nestable) + DDL CREATE/ALTER TYPE (§3.8)	codegen `UDTRecord` (+ client-only EmbeddableRecord)
inet/hstore/ltree/citext	INET/CIDR present (String); no hstore/ltree/citext	all present in extensions, strongly typed
pgvector	✅ built in: `float[]` + 6 operators + Spring AI (§3.9)	❌ absent in OSS (grep-verified)
COPY / LISTEN-NOTIFY	❌ (verified)	❌ (verified)
Version strategy	enum 11–16+18 (no 17; default→18), runtime detection	OSS family ≙18 only; versioned dialects are commercial
Metamodel	annotation processor (Java-first) + DDL comparison	codegen (database-first)
Java baseline	25	25
Size	army-postgre 78 files/39,742 LOC	core 2,126 files/663,534 LOC + extensions 63 files/4,790 LOC
Test openness	all tests open source and runnable	no tests in OSS checkout

9. Closing: This Comparison Is Really Army's Self-Introduction

Having used jOOQ as a mirror, here is a fair introduction to Army's PostgreSQL dialect:

It expresses dialect capability through API existence, not emulation — everything PG offers that the module covers (RETURNING, ON CONFLICT, MERGE, DISTINCT ON, named cursors, SET/SHOW, 54 array types, 12 range/multirange, RECORD, REF_CURSOR, pgvector) has a typed place on Postgres's chains; what it doesn't cover (COPY, LISTEN/NOTIFY, multi-table UPDATE/DELETE) simply doesn't exist in the API. The compiler is its first piece of documentation.
On server-side semantics it goes further than the jOOQ open-source edition: the full named-cursor family, conflict targets down to collation and operator classes — evidence of people who know PostgreSQL writing the API.
It is equally honest about being young: strongly-typed ranges live in an optional Guava module rather than as a built-in value type, no version-17 constant, use cases concentrated on the sync session — concrete, checkable "not dones" you can file as issues one by one, not vague beta warnings.
Its biggest lever is transparency: every use case is open source and runnable; every javadoc anchors the official manual. For teams willing to read source, Army's PG dialect is a mine you can assay yourself.

And jOOQ remains the respected yardstick: 660k lines, 20 years, a cross-dialect emulation machine. Choose jOOQ to buy "one API everywhere + commercial versioned dialects"; choose Army to buy "dialect capability fixed at compile time + fully open test cases." If your team lives heavily in PostgreSQL and wants database capability to live explicitly inside the Java type system, Army's army-postgre deserves an afternoon of cloning and running its tests — after all, only claims that can be verified verbatim deserve to be called an introduction.

All Army-side citations are reproducible from https://github.com/PillArmy/army; jOOQ-side citations are based on the locally checked-out open-source sources (3.22.0-SNAPSHOT). Hand-written sketches are marked as such. Corrections welcome.

Army vs Hibernate

zoro ma — Thu, 17 Sep 2026 16:08:13 +0000

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

Every claim about Army below comes from its source (0.6.8-SNAPSHOT, Java 25) and the army-example test suite. Hibernate claims are benchmarked against the current stable release as of September 2026 — 7.4.8.Final (2026-09-13, Jakarta Persistence 3.2) — plus official documentation (hibernate.org, docs.hibernate.org). Line counts were measured locally with wc -l on 2026-09-17.

Source layering: every Army code block is copied from project source/tests; the Hibernate/JPA block in §2.1 is my own hand-written sketch against the standard JPA API (not taken from any project), included only to contrast the programming models.

Bias disclaimer: no winner is declared. The main difference is not "who is stronger" — these two were never trying to do the same job.

0. Meet the Contestants

	Army	Hibernate ORM
What it is	A type-safe SQL DSL ("not an ORM", its words)	Java's oldest, fullest ORM / JPA implementation
Current version	`0.6.8-SNAPSHOT` (0.6.7 latest on Maven Central)	`7.4.8.Final` stable; 8.0 in Beta
Java baseline	Java 25 (`maven.compiler.source=25`, non-negotiable)	Java 17 / 21 / 25 / 26
License	Apache 2.0	Apache 2.0 since 7.0 (6.x was LGPL 2.1)
Standard alignment	None — proprietary API	Most mainstream Jakarta Persistence 3.2 (JPA) implementation — JPA itself was heavily influenced by Hibernate, but the official RI is EclipseLink
Age	A young 0.x project	1.0 series in 2002 — a twenty-something "living fossil"
Ecosystem	army-spring integration + two Spring AI modules (Chat Memory / Vector Store); bring your own pool	Spring Data JPA, Quarkus, WildFly, Envers, Search, Reactive…

Army's README opens with a deliberately stubborn self-definition:

Army is a type-safe, composable SQL DSL for Java — not an ORM, not a code generator, not a template engine. It treats SQL as the right abstraction and gives you an API that matches its power without hiding it.

Hibernate's own "A Short Guide to Hibernate 7" describes its central job differently: reading normalized relational data, then renormalizing it into an object graph, and synchronizing in-memory dirty state back to the database.

The worldview gap in one sentence:

Hibernate wants you to write less SQL — manipulate an object graph, and the framework generates SQL and manages object state for you.
Army wants what you write to be SQL — expressed through a compile-time type-safe Java chain, with every resulting byte of SQL under your control.

1. Size: Hold the "Lightweight" Thought

"SQL DSL = small and cute" is a natural assumption. Measured main-source line counts:

Module	Java files	Main LOC
army-core (DSL contracts + rendering skeleton + metamodel + mapping)	805	120,470
army-postgre (PostgreSQL dialect)	78	39,742
army-mysql (MySQL dialect)	79	30,479
army-jdbc (JDBC executor)	9	8,437
army-sync (synchronous Session API)	35	5,193
army-annotation (annotations + annotation processor)	46	5,099
army-spring (Spring integration)	16	1,903
army-sqlite	13	1,472
army-oracle	5	315 (essentially a placeholder)

So Army is not light in lines — the core alone is 120k. Its "lightness" is a simple runtime model: no persistence context, no proxies, no cache, no dirty checking. The complexity went into a typed transcription of SQL grammar — 70k lines for just the MySQL and PostgreSQL dialects.

hibernate-core is a several-hundred-thousand-line engineering product built over two decades, with complexity spent in entirely different places: object-graph management, caching, fetch strategies, the HQL translator, bytecode enhancement.

Complexity is conserved — the only question is whether it lives on the SQL side or the object side.

2. One Requirement, Two Worldviews

A boring requirement: insert one row, then fetch 20 rows by condition.

2.1 Hibernate / JPA (standard API, implementation-agnostic)

@Entity
class Stock {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String code;
    @Enumerated(EnumType.STRING)
    private StockStatus status;
    // getters/setters...
}

em.getTransaction().begin();
Stock stock = new Stock();                                         // plain new, set fields
stock.setCode("600519");
stock.setStatus(StockStatus.ACTIVE);
em.persist(stock);                                                 // now a managed entity in the persistence context
Stock again = em.find(Stock.class, 1L);                            // by PK (L1 cache first)
List<Stock> list = em.createQuery(
        "select s from Stock s where s.status = :st order by s.offerPrice desc", Stock.class)
        .setParameter("st", StockStatus.ACTIVE)
        .setMaxResults(20)
        .getResultList();
em.getTransaction().commit();                                      // dirty checking flush

You face objects: after persist, the object joins the persistence context; mutate its fields freely, and at commit/flush Hibernate computes the UPDATEs via dirty checking. You don't even need to know how many statements fire — which, of course, is also the birthplace of every N+1 horror story.

2.2 Army (README + real army-example code)

The entity is a plain POJO with just @Table/@Column — no @Entity:

@Table(name = "stock",
        indexes = @Index(name = "uni_stock_exchange_code", fieldList = {"exchange", "code"}, unique = true),
        comment = "stock")
public class Stock {
    @Generator(value = "io.army.generator.snowflake.Snowflake8Generator",
            params = @Param(name = "startTime", value = "1779012232202"))
    @Column
    private long id;
    @Column
    private LocalDateTime createTime;
    @Column(notNull = true, precision = 10, defaultValue = "'NORMAL'")
    private StockStatus status;
    // ...
}

The ArmyMetaModelDomainProcessor annotation processor generates a static metamodel Stock_ at compile time (visually reminiscent of the JPA static metamodel, but carrying something completely different):

public abstract class Stock_ {
    public static final SimpleTableMeta<Stock> T;
    public static final PrimaryFieldMeta<Stock> id = T.id();
    public static final FieldMeta<Stock> name = T.field(NAME);
    // ...
}

For queries you face the structured shape of a SQL statement (from the README):

Select stmt = SQLs.query()
        .select(s -> s.space(Stock_.id, Stock_.code, Stock_.name, Stock_.offerPrice)
                .comma(Exchange_.name, Exchange_.country))
        .from(Stock_.T, AS, "s")
        .join(Exchange_.T, AS, "e")
        .on(Stock_.exchange.equal(Exchange_.code))
        .where(Stock_.status.equal(StockStatus.ACTIVE))
        .and(Stock_.listingDate.greaterEqual(LocalDate.of(2020, 1, 1)))
        .orderBy(Stock_.offerPrice.desc())
        .limit(20)
        .asQuery();

List<StockSummary> results = session.queryObjectList(stmt, StockSummary::new);

Insertion goes through save (see ArmySyncSession.save — it immediately builds and executes an INSERT):

session.save(stock);

save returns the affected-row count; its body hands the domain to SQLStmts.insertStmt(...), executes it, and verifies the count. No managed state, no snapshot, no flush-timing puzzles — you can see exactly when SQL goes out.

3. Session Layer: A Stateful Object World vs a Stateless SQL Channel

This is the most fundamental difference, and the source evidence is hard.

Hibernate's Session/EntityManager is a Unit of Work plus a first-level cache: find/persist/merge/refresh/remove/flush/evict/clear form a full object state machine, on top of an optional second-level cache (JCache), query cache, and EntityGraph fetch control.

I grepped the entire io.army.session / io.army.dao source of both army-core and army-sync for state-machine methods — persist(, merge(, refresh(, remove(, flush(, evict(, detach( — zero hits (the only merge matches are PostgreSQL MERGE statements and an internal optionMap utility). What SyncSession offers:

Queries: queryOne / queryList / queryObjectList(stmt, ctor ref) / queryRecordList / query(Stream) / paging
Writes: update(stmt) / save(domain) / batchSave(domainList) / batchUpdate(stmt)
Transactions: startTransaction / commit / rollback / setSavePoint (on SyncLocalSession)

A fairness note, to avoid overstating it: Army is not unable to fetch by primary key. SyncBaseDao in army-sync (and SyncBaseService in army-spring) offer get(class, id), findById, getByUnique, existsById, rowCount. But look at the implementation — ArmySyncBaseDao.get simply delegates to findByUnique, which runs an immediate SELECT. They return plain POJOs that never enter a persistence context; that is a different thing from Hibernate's managed entities.

Result mapping is equally magic-free: session.queryObjectList(stmt, StockSummary::new) instantiates a plain object with your constructor/Supplier and fills fields by name. The README is visibly proud of this:

No RecordN<T1,T2,...,TN> with value1()/value2(). No attached/detached lifecycle. No lazy-loading proxy that explodes when the session closes. You can serialize it, cache it, pass it across threads — it's just data.

In fairness, Hibernate also knows a heavy state model isn't for everything — it ships StatelessSession: no L1 cache, no dirty checking, no cascades, reserved for batch jobs. The mental model of Army's main session is roughly "what if Hibernate's StatelessSession sideline had been promoted to the main event" — except Army equipped that path with a complete compile-time SQL DSL.

What happens to caching?

Army's README answers "not my problem":

Army deliberately does NOT manage: Database connections → use your preferred connection pool; Transactions → use Spring @Transactional or JTA; Caches → use Redis, Caffeine, or your own; Schema migrations → use Flyway or Liquibase.

You supply the pool (SyncFactoryBuilder.builder().datasource(hikariDataSource)); you bring Redis/Caffeine. Hibernate ships a mandatory L1 cache, optional JCache L2, and a query cache — out of the box, at the price of learning CacheMode, invalidation strategies, and (new in 7.4) even CacheMode.REFRESH_SESSION.

4. The Mapping Model: Seventeen Annotations and Zero Relationships

Army's annotation package contains exactly 17 files:

Codec, Column, DdlMode, DiscriminatorValue, FieldParam, Generator, GeneratorType,
Index, IndexField, Inheritance, MappedSuperclass, Mapping, NullsOrder,
OverrideParams, Param, SortOrder, Table

Look for ManyToOne, OneToMany, ManyToMany, OneToOne, JoinColumn, ElementCollection, Embedded — not there. Army does not map object associations. In the example project, ChinaProvince referencing its parent region is just an unglamorous FK column:

@Table(name = "china_province", ...)
@DiscriminatorValue("PROVINCE")
public class ChinaProvince extends ChinaRegion<ChinaProvince> {
    @Column(defaultValue = "0", notNull = true, updatable = false, comment = "relation Id")
    private Long relationId;
    // ...
}

Want related data? Write a JOIN. Hibernate's entire apparatus — @ManyToOne(fetch=LAZY), join fetch, @EntityGraph, @BatchSize, FetchMode.SUBSELECT for "renormalizing the object graph" — physically does not exist in Army. The N+1 problem is gone with it (but so is lazy-loading convenience; every JOIN is now yours to write).

The ORM-flavored capabilities Army keeps are all within the "single table / table family" scope:

Inheritance + discriminator: @Inheritance("regionType") + @DiscriminatorValue("PROVINCE"); parent ChinaRegion maps to china_region, child ChinaProvince to china_province, managed by the ComplexTableMeta family.
@MappedSuperclass: AbstractChinaRegion pulls up shared columns (id, createTime, version…).
ID generation: @Generator(type = POST) identity, or a configured Snowflake (Snowflake8Generator).
Built-in logical delete: the Visible enum (ONLY_VISIBLE / ONLY_NON_VISIBLE / BOTH) plus ArmyKey.VISIBLE_MODE; the parser appends visibility predicates automatically to domain queries/updates/deletes. This parallels Hibernate's @SoftDelete, only added in 6.4 — Army put it in the SQL rendering layer.
Built-in optimistic locking: domain DML handles version automatically. This real test from army-example's DomainUpdateTests passes a deliberately wrong version and asserts OptimisticLockException:

stmt = SQLs.domainUpdate()
        .update(ChinaRegion_.T, AS, "c")
        .set(ChinaRegion_.regionGdp, SQLs::plusEqual, SQLs::param, gdpAmount)
        .where(ChinaRegion_.id.in(SQLs::rowParam, extractRegionIdList(regionList)))
        .and(ChinaRegion_.createTime.between(SQLs::param, now.minusMinutes(10), AND, now))
        .and(ChinaRegion_.regionGdp.plus(SQLs::param, gdpAmount).greaterEqual(0))
        .and(ChinaRegion_.version.equal(SQLs::param, 20)) // error version
        .asUpdate();

Hibernate's counterpart is @Version with conditions auto-added during dirty-checking flush; Army injects them while rendering an explicit domain DML — same optimistic lock, one via state machine, one via SQL generation.

On type mapping, Army splits DataType (what the database type is) from MappingType (how Java talks to JDBC). The showcase: unsigned integers auto-resolve to "the smallest Java type that can't overflow" (INT UNSIGNED→Long, BIGINT UNSIGNED→BigInteger), and the three enum strategies CodeEnum/LabelEnum/NameEnum work with zero annotations. Hibernate offers the JPA-standard AttributeConverter, @JdbcTypeCode, UserType — a bigger, more configurable system.

5. Querying: An HQL Translator vs Clause-by-Clause SQL Modeling

On Hibernate's side

Four cards to play:

HQL/JPQL: entity-oriented string query language; the 6.0 translator rewrite (SQM) is very capable (fetch joins, subqueries, CTEs, window functions following along). In fairness, HQL is not an unsupervised raw string: the Hibernate Processor can validate HQL syntax and typing at compile time, and IntelliJ IDEA provides live completion/validation — but the language itself remains string-based rather than a Java-typed chain like Army's DSL;
JPA Criteria API: type-safe but famously verbose;
QuerySpecification (7.0) and Jakarta Data repositories: Data Repositories shipped as a tech preview in 6.5 and went GA in 6.6, with an annotation processor generating repositories at compile time to close the type-safety gap;
Native SQL: createNativeQuery, the escape hatch any time.

The abstraction sits higher: from Stock s join s.exchange e derives the join condition from mappings. Code barely moves when dialects or tables change — at the price of understanding HQL-to-SQL translation, fetch timing, and Cartesian-product warnings.

On Army's side

There is no query language string. The DSL is everything, and rendering is direct: a DSL statement is just a data holder; ArmyParser walks it in SQL grammar order (WITH→SELECT→FROM→WHERE→GROUP BY→…→LIMIT→locks), each expression node writes itself into one shared StringBuilder, and parameters land in one ordered list (army-core README documents the whole pipeline).

Dialect differences land as template methods on concrete parsers — backticks and MySQL's LIMIT offset,count, for example:

@Override protected final char identifierDelimitedQuote() { return BACKTICK; }
// MySQL native: LIMIT offset, rowCount (not standard LIMIT n OFFSET m)

Two direct consequences:

Database-specific power is a first-class citizen. MySQL's STRAIGHT_JOIN, USE/FORCE INDEX, the 36 HintType enum constants of /*+ */ optimizer hints, LOAD DATA, ON DUPLICATE KEY UPDATE (including the 8.0.19 row alias), SET @row:=0 user variables; PostgreSQL's 12 range/multirange types, 54 array types, six pgvector distance operators, RETURNING, DECLARE CURSOR — each has a typed place on the chain, and version gating happens at render time (a CTE on 5.7 throws immediately instead of failing at the server). Hibernate is catching these too: JDBC Array support arrived in 6.1, array functions in 6.4, Extended Array Support in 6.6; the hibernate-vector module already existed in 6.4 (SQL Server vectors only landed in 7.2); soft-delete likewise dates to 6.4 — but they surface as HQL functions, extra module dependencies, or native SQL: seams under a unified abstraction.
Portability is one-directional. Build with MySQLs.query() and you are bound to MySQL; portability requires staying on the SQLs.query() common subset — which deliberately removes about half of the goodies above. Hibernate is "portable by default, extra work for specifics." The arrows point opposite ways.

A real query (army-example QueryTests): CTE + JOIN + window function + subquery + bind params in one statement:

stmt = SQLs.query()
        .with("cte").as(s -> s.select(ChinaRegion_.id)
                .from(ChinaRegion_.T, AS, "c")
                .where(ChinaRegion_.id.in(SQLs::rowParam, regionIdList))
                .asQuery())
        .space()
        .select(ChinaProvince_.id, Windows.sum(ChinaRegion_.regionGdp).over("w").as("gdpSum"))
        .from(ChinaProvince_.T, AS, "p")
        .join(ChinaRegion_.T, AS, "c").on(ChinaProvince_.id::equal, ChinaRegion_.id)
        .where(ChinaProvince_.id::in, SQLs.subQuery()
                .select(s -> s.space(SQLs.refField("cte", ChinaRegion_.ID)))
                .from("cte")
                .asQuery())
        .window("w").as(s -> s.partitionBy(ChinaRegion_.name)
                .orderBy(ChinaRegion_.regionGdp::desc).rows(UNBOUNDED_PRECEDING))
        .orderBy(ChinaProvince_.id)
        .limit(SQLs::param, regionSize)
        .asQuery();

List<Map<String, Object>> rowList = session.queryObjectList(stmt, RowMaps::hashMap);

The price is a huge interface surface: the mysql grammar-contract package alone (io.army.criteria.mysql, 23 files) contains 3,519 lines of nested interfaces, and staged chains like _PrimaryOptionSpec → _PartitionSpec → … are a notorious learning cliff. It buys "illegal SQL does not compile" with sheer interface count.

6. Dialect Coverage: Wide Net vs Deep Wells

In 7.4, Hibernate ships 16 officially tested dialects inside hibernate-core (MySQL, PostgreSQL, Oracle, SQL Server, the three DB2 flavors, Sybase, H2, HSQL, MariaDB, HANA, Spanner, etc. — per the official Dialect report), while a separate hibernate-community-dialects module maintains ~30 community/legacy dialects on a best-effort basis (Informix, Ingres, Teradata, TiDB, SQLite…). The mechanism is a unified HQL translator plus dialect classes registering function differences.

Army's Database enum lists only five — MySQL, PostgreSQL, H2, SQLite, Oracle — with very different levels of honesty:

Dialect	Module LOC	Real state
MySQL	30,479	Deep: 5.5–8.0 version gating, LOAD DATA, multi-table DML, full optimizer hints
PostgreSQL	39,742	Deeper: ranges/arrays/composites/pgvector/RETURNING/DECLARE CURSOR
SQLite	1,472	Basically usable (with its own SchemaComparer)
H2	inside core	Mostly serves tests
Oracle	315	Just the `Oracles` entry and a few grammar interfaces — a placeholder
SQL Server	none	Research notes still lying around in `local-doc/sql/SqlServer.sql`

At the executor level, the army-jdbc README itself states support for only MySQL / PostgreSQL / SQLite.

So, objectively: Army is "two ultra-deep dialects + one lightweight + two uncashed checks"; Hibernate is "16 officially backed dialects + ~30 community ones, whose depth bows to a unified abstraction." This table matters more than any marketing line when choosing.

7. Transactions, DDL, Engineering: Where Marketing Can Mislead

Transactions. Army doesn't take over declarative transactions, but its capabilities are solid: SyncLocalSession offers programmatic startTransaction(option, mode) (rendering dialect-specific SET TRANSACTION ISOLATION LEVEL … ; START TRANSACTION …, even multi-statement on MySQL/PG), savepoints, rollback-only marking, plus XA via RmSession. The recommended daily driver is army-spring's ArmySyncLocalTransactionManager (a standard PlatformTransactionManager); tests simply use @Transactional. Hibernate is a long-standing JTA/Jakarta Transactions citizen with near-default Spring declarative transactions. Bottom line: in Spring both end up as @Transactional; you just configure one extra TransactionManager with Army.

DDL. Time to gently contradict Army's own README — it claims "doesn't … generate schemas," yet DdlMode offers five levels: NONE / VALIDATE_UNIQUE / VALIDATE / DROP_CREATE / UPDATE. ArmySyncFactoryBuilder.initializingSchema extracts metadata at factory bootstrap → compares via SchemaComparer → executes DDL automatically; the global ddl.mode key even defaults to UPDATE, and the example's FactoryUtils explicitly sets DdlMode.UPDATE. That maps almost one-to-one to Hibernate's hbm2ddl.auto=validate/update/create/create-drop. The difference: Army's docs recommend Flyway/Liquibase, and Hibernate's docs likewise discourage update in production — both projects actually agree that automated DDL is a dev-time convenience; Army's README is just unusually humble about what the code already does.

Batch writing. Both support JDBC batching. In Army batching is an explicit first-class citizen: separate batchQuery/batchSingleUpdate/batchMultiUpdate/batchSingleDelete/batchMultiDelete factories with namedParamList — SQL rendered once, parameters bound in batches. In Hibernate you tune hibernate.jdbc.batch_size, flush order, and order_inserts; powerful, but you must learn its rhythm.

Reactive. Hibernate has a separate Hibernate Reactive product line, independent of hibernate-core (Vert.x/Netty drivers; one of the default options in Quarkus's reactive data stack). Army's core Javadoc references io.army.reactive.ReactiveSession in several places, and commented-out reactive bootstrap code survives in the example factory — but no reactive module ships in this repository; today the only executable path is synchronous + JDBC.

Enterprise features. Hibernate has the long-standing Envers auditing module; 7.4 additionally introduced @Audited/@Temporal temporal/audited entities in core as an incubating feature (officially a re-implementation of Envers' ideas, with Envers-compatible audit logs), plus Search (full-text), L2 cache, multi-tenancy, and GraalVM. Army's closest neighbors are the two army-spring-ai-* modules (Chat Memory, Vector Store). No auditing, multi-tenancy, or caching.

8. The Big Table

Dimension	Army	Hibernate ORM 7.4
Essence	Type-safe SQL DSL (direct dialect SQL)	Full ORM / JPA 3.2 implementation
Abstraction center	SQL statements	Entity object graph + persistence context
Query language	Staged Java DSL (`SQLs/MySQLs/Postgres`)	HQL/JPQL, JPA Criteria, Jakarta Data repository methods, native SQL
Compile-time safety	Headline feature: illegal syntax doesn't compile	Criteria/static metamodel/Data Repositories fully compile-time; HQL validated at compile time by Hibernate Processor and IDEs, but remains string-based
Object state machine	None (no persist/merge/flush/evict)	Yes, and central (plus the StatelessSession sideline)
L1/L2 cache	None; wire your own	Mandatory L1, JCache L2, query cache
Lazy loading / associations / cascade	No association mappings; relations are FK columns + hand-written JOINs	`@ManyToOne/@OneToMany/…`, fetch joins, EntityGraph, cascade
N+1 exposure	Structurally impossible (but every JOIN is manual)	Continuous governance (EntityGraph/FetchProfile/batch size)
Soft delete / optimistic lock	`Visible` built into renderer; version auto-added to domain DML	`@SoftDelete` (6.4+), `@Version` auto-injection
Inheritance	Discriminator table families (`@Inheritance/@DiscriminatorValue`)	Full SINGLE_TABLE/JOINED/TABLE_PER_CLASS
Dialect depth	MySQL/PG extremely deep (70k LOC), SQLite basic, Oracle placeholder	16 core-supported dialects + ~30 community dialects; depth subordinate to unified HQL abstraction
Proprietary SQL	First-class (LOAD DATA, hints, ranges, vectors…)	Mostly supported, often via HQL extensions/annotations/native SQL
Auto DDL	Yes (VALIDATE/UPDATE/DROP_CREATE), default UPDATE	Yes (five hbm2ddl modes), plus hibernate-tools ecosystem
Pool / transactions	Bring a DataSource; Spring/JTA transactions	Embedded or external pools; deep Spring/JTA integration
Reactive	Interface traces in core, no shipping module	Separate Hibernate Reactive product line
Java baseline	25 required	17+
Standardization	None, single vendor	Jakarta Persistence; massive talent/docs pool
Maturity	0.6.x, API may still move	24 years of evolution; 7.4 stable, 8.0 underway

9. So, Which Do You Pick?

The case for Hibernate is straightforward:

Your team wants standard skills, Stack Overflow coverage for everything, and plug-and-play hiring;
The business is entity-CRUD, form-driven admin/back-office work where object-graph navigation and cascades genuinely remove code;
You need L2 caching, auditing (Envers), multi-tenancy, many database vendors, or a complete reactive stack;
You are willing (and able) to govern its classic traps: N+1, flush timing, equals/hashCode, lazy-loading session boundaries, cache invalidation.

The case for Army is pickier:

Your team knows SQL cold and insists "every SQL statement hitting production must be readable and reviewable," yet is tired of string concatenation and MyBatis XML;
The system leans hard on MySQL/PostgreSQL-specific power: multi-table DML, UPSERT, LOAD DATA, index/optimizer hints, ranges/arrays/pgvector;
You want proxy-free, state-machine-free, freely serializable POJO results, and distinct types for single/batch/multi-table INSERT/UPDATE/DELETE;
You can accept a Java 25 baseline, 0.x API churn, missing Oracle/SQL Server, and a support model that is basically "read the source" (fortunately the READMEs are unusually good and the 120k-line core is densely commented).

Hibernate's own guide offers the fair verdict: "should I use ORM, or plain SQL? The answer is usually: use both." Army happens to fill the long-neglected half of "both" — the half previously improvised with JdbcTemplate, MyBatis, or hand-written SQL — with a compile-time type-safe option that genuinely believes in dialects.

Of course, faith has a price: Army hands you the database's full complexity back, dressed in type-safe clothing. Every Hibernate pitfall you escape gets repaid in the form of a JOIN you write yourself.

Army: https://github.com/PillArmy/army (runnable cases in army-example/src/test/java/io/army/session/sync/)
Hibernate: https://hibernate.org/orm/ (7.4 docs: https://docs.hibernate.org/orm/7.4/)

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
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

When the Dialect IS the API: Reading Army and jOOQ Side by Side, Through PostgreSQL Source

0. TL;DR

1. The Contenders

2. Two Worldviews: Dialect-as-API vs Dialect-as-Render-Parameter

2.1 Army: Postgres.java, one door per dialect

2.2 jOOQ: one DSL plus one enum switch

2.3 Three corollaries of the philosophy gap

3. Type Systems: Two Different PG Dictionaries

3.1 Army: PgType — broad and literal

3.2 jOOQ: an internal core catalog plus a strongly-typed extensions module

3.3 Range types: String vs Range<T> — the biggest fork in this comparison

3.4 pgvector: a correction — Army has it; the jOOQ OSS edition does not

3.5 Deep-dive map: array / range / composite / vector across four dimensions

3.6 ARRAY

3.7 RANGE / MULTIRANGE

3.8 COMPOSITE (CREATE TYPE … AS)

3.9 VECTOR (pgvector)

3.10 Cross-family summary

4. Statement Capabilities, Item by Item

4.1 ON CONFLICT: different strengths

4.2 MERGE: an entrée vs an emulator

4.3 Cursors: the deepest block of Army's PG dialect

5. Head-to-Head: Four Same-Problem Use Cases

Round 1: batch UPSERT + RETURNING (both flexing)

Round 2: cursors (see §4.3 — a shape difference, decisively in Army's favor)

Round 3: range-typed columns

Round 4: array columns

6. Version Strategy: Explicit Enum vs Commercial Versioned Dialects

7. Metamodel Direction, Doc Density, and Openness of Tests

7.1 Metamodel: Java-first vs database-first

7.2 Doc density: 1,243 vs 18

7.3 Test openness: a difference that matters to this article's methodology

8. Summary Table

9. Closing: This Comparison Is Really Army's Self-Introduction

Army vs Hibernate

0. Meet the Contestants

1. Size: Hold the "Lightweight" Thought

2. One Requirement, Two Worldviews

2.1 Hibernate / JPA (standard API, implementation-agnostic)

2.2 Army (README + real army-example code)

3. Session Layer: A Stateful Object World vs a Stateless SQL Channel

What happens to caching?

4. The Mapping Model: Seventeen Annotations and Zero Relationships

5. Querying: An HQL Translator vs Clause-by-Clause SQL Modeling

On Hibernate's side

On Army's side

6. Dialect Coverage: Wide Net vs Deep Wells

7. Transactions, DDL, Engineering: Where Marketing Can Mislead

8. The Big Table

9. So, Which Do You Pick?

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

2.1 Army: `Postgres.java`, one door per dialect

3.1 Army: `PgType` — broad and literal

3.3 Range types: `String` vs `Range<T>` — the biggest fork in this comparison

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