DEV Community

Z. Gao
Z. Gao

Posted on

SQL-First Paradigm: Rethinking Persistence Layer Design from First Principles

#java #database #sql #architecture

Foreword

In the Java backend world, ORM (JPA/Hibernate) and MyBatis have long been the default choices for data persistence. But after years of real‑world development, many engineers eventually run into a recurring tension:

There is a fundamental semantic gap between general‑purpose programming languages (3GL) and SQL (4GL).

Attempting to force‑fit SQL into object‑oriented abstractions — or burying SQL under layers of XML tags — often leads to redundant code, limited expressiveness, and painful debugging.

This article explores the SQL‑First persistence paradigm from first principles. We’ll examine the 3GL vs. 4GL distinction, the inherent trade‑offs in ORM and MyBatis, and how a lightweight approach — using simple-dao as a concrete example — can streamline complex queries while keeping SQL fully transparent.

GitHub Repositories

  1. Core Framework: https://github.com/gzz2017gzz/simple-dao
  2. System Base Starter: https://github.com/gzz2017gzz/simple-dao-starter
  3. Code Generator: https://github.com/gzz2017gzz/simple-dao-coder
  4. Production Demo Project: https://github.com/gzz2017gzz/simple-dao-demo

1. The Root Cause: 3GL vs. 4GL — Two Different Universes

1.1 Two Worlds, Two Mathematical Foundations

Modern software development deals with two fundamentally different language families:

Aspect 3GL (General‑Purpose Languages) 4GL (SQL)
Representatives Java, C#, Go, Python, C++ SQL (relational database language)
Mathematical Basis Graph Theory Set Theory + Predicate Logic
Paradigm Imperative / Object‑Oriented Declarative
Core Elements Objects, references, methods, inheritance, polymorphism Tables, rows, columns, keys, relationships
Mental Model Tells the computer “how” to do something Tells the database “what” to retrieve
Abstraction Level Algorithms + data structures in memory Set operations on disk‑resident structured data

1.2 The Semantic Gap Is Inherent

There is no perfect one‑to‑one mapping between 3GL object models and 4GL relational models — this is not a framework limitation, but a mathematical reality:

  • Object inheritance and polymorphism have no direct relational counterpart.
  • Navigating object references (e.g., user.getOrderList()) is not the same as a database JOIN — performance models are completely different.
  • In‑memory object identity (reference address) ≠ database primary key constraints.

Any framework that tries to fully bridge these worlds will inevitably introduce abstraction leaks, performance surprises, and steep learning curves. The question is not whether the gap exists, but how we choose to manage it.

2. Why SQL and Relational Databases Are Still Relevant

2.1 Relational Databases Remain the Backbone of Enterprise Data

Many modern database types — key‑value stores, time‑series databases, vector databases, graph databases — are essentially specialised variations of the relational model:

  • KV stores are single‑table relational tables with just key and value.
  • Time‑series and vector databases add specific indexes and storage optimisations on top of relational structures.
  • Graph databases can be fully represented using node and edge tables.

For core business domains (users, orders, permissions, campus management, ERP), the relational database remains the primary source of truth. Middleware like Redis, Elasticsearch, and ClickHouse serve as supplementary layers — not replacements.

2.2 SQL: A Universal, Cross‑Language Data Language

  • Strong standardisation: Core SQL syntax has been stable for decades, with only minor dialect variations.
  • Full expressiveness: From simple CRUD to complex joins, subqueries, grouping, aggregation, and window functions — SQL handles it all natively.
  • Universal compatibility: Every mainstream language provides a standard SQL interface — Java/Spring JDBC, Python/DB‑API, C#/Dapper, Go/sqlx, Rust/sqlx, etc.

SQL isn’t going away, and neither are relational databases. A persistence design that embraces SQL natively will remain relevant for the foreseeable future.

3. Mainstream Frameworks: A Balanced Look at Their Trade‑offs

3.1 ORM (JPA/Hibernate): A Noble but Imperfect Abstraction

The ORM promise — map relational data to object models — sounds elegant. But given the 3GL/4GL gap, it inevitably introduces challenges:

  • Semantic distortion: Database set operations get translated into object navigation, forcing developers to mentally switch between OOP and relational thinking.
  • SQL opacity: Auto‑generated SQL can be inefficient, hard to tune, and unpredictable under complex joins.
  • N+1 query problem: Object traversal frequently triggers multiple database round‑trips, becoming a performance bottleneck in high‑traffic scenarios.
  • Double learning curve: Developers must master not only Java/OOP but also HQL/JPQL, caching strategies, lazy loading pitfalls, and framework‑specific “gotchas.”

ORM works well for very simple CRUD, but becomes a liability as soon as you need complex joins, aggregations, or reports.

3.2 MyBatis: A Mapping Tool That Gained Too Much Baggage

MyBatis is often called a “semi‑ORM,” but its core job is straightforward: parameter binding + result set mapping. It does not generate SQL or manage object relationships.

To achieve this, however, it has accumulated significant complexity over time:

  • Dual‑file overhead: Mapper interfaces + XML/annotations, leading to fragmented code.
  • Dynamic tag hell: <if>, <where>, <foreach>, OGNL expressions turn dynamic SQL into a puzzle.
  • Complex interceptor system: Custom extensions are hard to write and maintain; most teams rely on community plugins, which can break across upgrades.

Compare this with Spring JDBC, where a single RowMapper does the mapping job in one line. Much of MyBatis’s additional surface area is overhead, not necessity.

Why Did MyBatis Become So Popular?

  • Historical momentum: Early Hibernate was over‑opinionated and restricted native SQL access; MyBatis offered a middle ground.
  • Bandwagon effect: Widespread adoption made it the “safe” choice in many organisations.
  • Misplaced separation myth: The idea that “SQL should be separated from code” became dogma, even though it often just adds indirection.

4. The SQL‑First Paradigm: Design Principles and Architecture

4.1 Core Insight: Dynamic SQL Has a Gradient

A standard SQL statement can be broken into clauses — and not all clauses change at the same frequency:

Clause Dynamism Typical Behaviour
SELECT / FROM / JOIN / GROUP BY Low Table structures and relationships are stable.
HAVING / ORDER BY Medium Occasionally adjusted for new reporting needs.
WHERE / LIMIT High Business filters and pagination change constantly.

This observation drives the SQL‑First strategy:

  1. Stable parts (SELECT, FROM, JOIN, GROUP BY) → Hand‑written SQL, with no framework‑imposed abstraction.
  2. High‑dynamism parts (WHERE, pagination, sorting) → Encapsulated in a 3GL (Java) condition layer, enabling reusability, type safety, and easy debugging.

The goal is not to translate between 3GL and 4GL, but to let each excel in its domain: SQL stays SQL, Java stays Java, with a thin bridge between them.

4.2 Two Golden Rules of SQL‑First Design

These are non‑negotiable boundaries that distinguish SQL‑First from ORM/MyBatis:

  1. No wrapping of SQL keywords or native syntax

    The framework does not provide its own DSL like eq() or like(). Developers write standard SQL fragments directly (AND field = ?).

    → Zero learning curve, full compatibility with all database‑specific features, and zero migration cost if you switch frameworks.

  2. No shielding of database capabilities or native exceptions

    Stored procedures, DDL, streaming queries, vendor‑specific functions all work out‑of‑the‑box. SQL exceptions are propagated directly, not wrapped in framework‑specific errors.

    → Full power of the database, and intuitive debugging.

4.3 A Three‑Layer Architecture (Implemented in simple-dao)

The SQL‑First paradigm naturally decomposes into three distinct layers, each with a clear responsibility:

Layer 1: Condition Layer (BaseCondition) — for High‑Dynamism Clauses

Purpose: Centralised management of WHERE conditions, pagination, and sorting. This is the heart of the paradigm.

  • Features:

    • Provides semantic methods: and(), in(), like(), conditional add().
    • Automatically collects parameter values for SQL placeholders — no manual parameter list maintenance.
    • All conditions converge in a single addCondition() method, ensuring consistent coding standards across the team.

  • Value:

    • Eliminates the single‑table vs. join‑table mental split: Whether it’s a simple CRUD or a 15‑table report, the same condition API is used.
    • Adding or removing a filter is a one‑line change.
    • Supports condition splitting and parameter merging (mergeParams) for complex, modularised business queries.

Layer 2: Execution Layer (BaseSql) — the Bridge

Purpose: A pure execution channel that takes static SQL + dynamic conditions + parameters and executes them via Spring JDBC.

  • Does not parse or modify SQL — fully respects the two golden rules.
  • Provides cross‑dialect pagination, SQL logging with parameter substitution, and other convenience utilities.
  • Fully leverages Spring’s transaction and connection pool management — no re‑invention.

Layer 3: Single‑Table Helper Layer (BaseDao) — for Stable Scenarios

Purpose: Automates single‑table CRUD by resolving entity metadata via annotations at startup (one‑time reflection).

  • Capabilities: Automatic primary key generation, audit field population (creator/creation time), and global logical delete.
  • Implementation: Reuses BaseCondition and BaseSql — no duplicate logic.
  • Outcome: Zero SQL to write for standard single‑table operations, matching ORM convenience where it actually makes sense.

4.4 From Manual String Concatenation to Semantic Units

Traditional JDBC or MyBatis often rely on manual string concatenation or XML tags — error‑prone and verbose.

SQL‑First introduces semantic condition methods that turn each filter into a clear, self‑contained unit:

  • and(fieldSql, value) → generic equality condition, auto‑prepends AND and adds a placeholder.
  • and(fieldSql, value, matchMode) → built‑in prefix/suffix/full wildcard support — no manual % concatenation.
  • in(fieldSql, array) → auto‑expands to IN (?, ?, ?) and handles array parameters.
  • add(sqlFragment, booleanFlag) → conditionally includes a SQL snippet based on a business rule.

Contrast:

Traditional Approach SQL‑First Approach
Multiple if checks scattered across code Single add() line per condition
Manual parameter list management Automatic parameter collection
String concatenation with risk of syntax errors Semantic, IDE‑friendly method calls

5. Practical Engineering Considerations

5.1 Where to Keep SQL Strings: Constants vs. Inline

Based on the dynamic gradient, we recommend:

  • Static SQL (fixed joins, stable table structures) → static final constants.

    Improves readability and reusability. Performance difference is negligible — JVM string optimisations + JDBC prepared statements + database plan caching make it a non‑issue.

  • Dynamic SQL (branching, conditional assembly) → written directly inside the DAO method, using plain Java logic.

    Java’s imperative capabilities are far more expressive than XML tags or OGNL, making complex dynamic logic much more maintainable.

5.2 Code Volume: Why It’s About 1/3 to 1/4 of Traditional Frameworks

The SQL‑First architecture eliminates three broad categories of boilerplate:

  1. MyBatis’s fragmented XML files, endless dynamic tags, and OGNL expressions.
  2. ORM’s massive annotation lists, chainable DSLs, and relationship configuration.
  3. Repeated null‑checks and parameter‑assembly logic across multiple query methods.

The reduction is not about cutting business logic — it’s about removing accidental complexity imposed by the framework.

5.3 Startup and Runtime Performance

  • Reflection is used only once at application startup to resolve entity metadata. At runtime, there is zero reflection overhead — performance is effectively identical to Spring JDBC.
  • Bean scope: BaseDao is prototype‑scoped to isolate metadata per entity type — a deliberate design choice with no performance penalty.
  • Batch handling: the framework provides basic batch capabilities; for extremely large datasets, applications should implement their own batching strategies (which is standard practice across all persistence tools).

5.4 Extension: Leveraging Spring’s Native Ecosystem

Instead of a custom plugin system, SQL‑First frameworks embrace Spring AOP for cross‑cutting concerns:

  • Data permissions
  • Field masking / desensitisation
  • SQL audit logging
  • Multi‑tenancy

This means:

  • Extensions are written in plain Java + Spring, with no framework‑specific learning curve.
  • Caching, read/write splitting, multi‑datasource, and distributed transaction support all come from Spring and middleware — maintaining clear responsibility boundaries.

6. Beyond Java: A Cross‑Language Paradigm

SQL is language‑agnostic, and the SQL‑First paradigm has been successfully implemented across 8 mainstream backend languages:

Language Underlying Driver Implementation Form
Java Spring JDBC simple‑dao and similar frameworks
C# Dapper Condition + Dao layered pattern
Go / Rust sqlx Semantic condition assembly + native SQL
Python SQLAlchemy Core Condition‑based semantic units
PHP / Node.js / C++ Native DB drivers Same layering philosophy

All cross‑language implementations follow the same core rules:

  1. Condition‑layer methods are semantically consistent across languages.
  2. SQL remains fully transparent; native exceptions are passed through.
  3. Single‑table CRUD is automated; complex SQL is hand‑written.

This demonstrates that SQL‑First is not a single framework, but a reusable software engineering pattern that transcends language boundaries.

7. When to Choose SQL‑First (and When Not To)

7.1 Where It Excels

  • Enterprise applications with heavy multi‑table joins, aggregations, and reporting requirements (e.g., ERP, campus management, government systems, microservices).
  • Teams that value uniform coding standards, low maintenance overhead, and want to avoid XML/DSL sprawl.
  • Long‑lived, multi‑developer projects where SQL expertise is already present and DBA involvement is frequent.
  • Projects already in the Spring ecosystem that prefer minimal third‑party plugin dependencies.

7.2 Comparative Summary

Approach Strengths Weaknesses
ORM (JPA/Hibernate) Quick for simple CRUD; automatic schema generation Semantic mismatch; opaque SQL; N+1 problems; steep learning curve for complex queries
MyBatis Full SQL control; widely known XML/annotation fragmentation; dynamic tag complexity; steep extension barrier
Plain Spring JDBC Maximum control; minimal abstraction Excessive boilerplate for conditions; manual parameter management
SQL‑First (e.g., simple‑dao) Native SQL power + condition automation; single‑table CRUD; Spring‑native Requires developers to be comfortable writing SQL (which, for backend engineers, should be a core skill)

8. Final Thoughts: Back to the Essence of Persistence Design

  1. Acknowledge the gap: 3GL and 4GL are different worlds. Full ORM is an over‑ambitious abstraction; MyBatis adds too much accidental complexity for what it actually does.
  2. Respect both languages: Let SQL handle set operations natively. Let Java handle dynamic logic with its full expressive power. Build a thin bridge, not a wall.
  3. Keep it simple: A three‑layer architecture with semantic condition units unifies single‑table and multi‑table queries, eliminates duplication, and enforces consistency.

The best persistence framework is the one you barely notice while writing business logic.

Let SQL be SQL. Let Java be Java. Let the bridge be minimal and transparent.

That is the ultimate goal of the SQL‑First paradigm.

Final Notes

This article was adapted from internal design discussions and production experience across multiple enterprise projects over several years. The simple-dao family of repositories (links at the top) provides a working implementation of these ideas in Java.

If you’ve encountered similar pain points with ORM or MyBatis, or if you’re simply curious about alternative persistence models, we’d love to hear your feedback. Open an issue, start a discussion, or explore the demos — contributions and ideas are always welcome.

License: Apache 2.0

Originally published: 2026-06-20

Top comments (0)