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:

1. Don't create new world, just mapping real world.
2. 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:

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:

1. Postgres.query() is the dialect-specific entry point. A portable statement starts with SQLs.query() instead.
2. ChinaRegion_.T is a generated static-metamodel table reference; ChinaRegion_.id, ChinaRegion_.name are typed field metadata, not strings.
3. 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.
4. 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.
5. 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.