DEV Community: Z. Gao

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

Z. Gao — Sat, 20 Jun 2026 02:29:39 +0000

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

Core Framework: https://github.com/gzz2017gzz/simple-dao

System Base Starter: https://github.com/gzz2017gzz/simple-dao-starter

Code Generator: https://github.com/gzz2017gzz/simple-dao-coder

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:

Stable parts (SELECT, FROM, JOIN, GROUP BY) → Hand‑written SQL, with no framework‑imposed abstraction.
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:

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

MyBatis’s fragmented XML files, endless dynamic tags, and OGNL expressions.
ORM’s massive annotation lists, chainable DSLs, and relationship configuration.
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:

Condition‑layer methods are semantically consistent across languages.
SQL remains fully transparent; native exceptions are passed through.
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

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

2 . SimpleDAO From Zero To Production: Multi-table Joins & Optimized Pagination

Z. Gao — Fri, 19 Jun 2026 11:07:57 +0000

GitHub (Overseas Access Friendly)

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

Supplementary Note

simple-dao-starter & simple-dao-demo Gitee pages failed to parse when fetching details, but the repository URLs remain fully valid for cloning and browsing.
The core framework & code generator Gitee READMEs are complete, containing full feature comparison tables, configuration docs, performance benchmarks and code samples for reference.

Full Tutorial Series: SimpleDAO From Zero To Production

This episode focuses on multi-table join queries + pagination. All SQL is native, zero XML, high-performance, only basic Java knowledge required to get started.

Prerequisites

No advanced framework experience needed. Only the skills below are required:

✅ Must-Have Skills	❌ Not Required At All
Basic Java (Class, Interface, Annotation, Generics)	Any MyBatis / Hibernate experience
Standard SQL (SELECT, JOIN, WHERE clauses)	XML config, dynamic OGNL tags, plugin development
Spring Boot basics (Datasource config, DI)	Spring Boot auto-configuration internals
Run Maven projects in IDE	Advanced Maven modules & parent POM logic

Full Series Roadmap

Episode · Title	Runtime	Core Content
01 · Single-table CRUD + Audit Fields + Soft Delete	~6 mins	Zero-code single-table operations, auto-filled audit columns, built-in soft delete logic
02 · Multi-table Joins + Pagination	~5 mins	Unified API for single/multi-table queries, no ResultMap required
03 · Advanced Conditions: IN Clauses & Subqueries	~5 mins	Drop-in replacement for MyBatis `<foreach>`, clean dynamic SQL assembly
04 · Complex Multi-table Joins & Filter Logic	~5 mins	Reusable template for multi-table range filtering
05 · Reporting Queries: GROUP BY & Aggregate Functions	~6 mins	Full native SQL support for reports, no ORM restrictions
06 · Merge Multiple Condition Groups with mergeParams	~6 mins	Decouple & reuse filter logic for complex statistical dashboards
07 · Multi-tenancy & Data Permissions via AOP	~7 mins	Spring native AOP instead of MyBatis interceptors
08 · Data Masking & Audit Extension — No Framework Lock-In	~7 mins	Fix the flawed layered design of MyBatis persistence-level data masking

Episode Goals

Implement paginated join queries between bus_order (order table) and sys_user (user table)
Learn to pass cross-table filters via the add() utility method
Understand SimpleDAO’s high-performance COUNT calculation logic
Compare with traditional ORMs to see the simplicity of the SQL-First paradigm

Project Setup

1. Standard Project Structure

Standard Spring Boot layout, key files listed below:

demo02_join_query/
├── pom.xml                # Maven dependency config
└── src/main/
    ├── java/example/
    │   ├── DemoApplication.java  # Spring boot main class
    │   └── order/               # Order business module
    │       ├── Order.java       # Database entity
    │       ├── OrderVO.java     # DTO for join query results
    │       ├── OrderCond.java   # Query filter class
    │       └── OrderDao.java    # Data access layer
    └── resources/
        ├── application.yml      # Datasource configuration
        └── schema.sql           # Table DDL & test seed data

2. Minimal Core Dependencies

Only four lightweight dependencies, no bloated transitive libraries, works out of the box:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.simple</groupId>
    <artifactId>simple-dao</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

spring-boot-starter-jdbc: Native Spring JDBC foundation
simple-dao: Core persistence framework for CRUD, pagination & dynamic filters
h2: In-memory database, no external database server needed for local testing
lombok: Reduce boilerplate getter/setter/toString code for entities

3. Datasource Configuration (application.yml)

Standard Spring Boot datasource format — swap URL/driver class to switch MySQL, PostgreSQL, Oracle, etc.

spring:
  datasource:
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1  # In-memory H2 database
    driver-class-name: org.h2.Driver
    username: sa
    password:
  sql:
    init:
      schema-locations: classpath:schema.sql  # Auto-run table creation SQL on startup
      mode: always

4. Table Schema & Test Seed Data (schema.sql)

Table Definitions

Two tables with business columns + auto-managed audit columns (creation timestamps, soft delete flag):

-- User table (sys_user)
CREATE TABLE IF NOT EXISTS sys_user (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,          -- Username
    age INTEGER,                        -- User age
    email VARCHAR(100) UNIQUE NOT NULL, -- Contact email
    phone VARCHAR(20) UNIQUE,           -- Mobile number
    password VARCHAR(64) NOT NULL,      -- Password hash
    sex CHAR(1),                        -- Gender: 1=Male, 2=Female
    status INTEGER DEFAULT 1,           -- Account status: 1=Active, 0=Disabled
    create_time TIMESTAMP,
    create_by INTEGER,
    update_time TIMESTAMP,
    update_by INTEGER,
    dr INTEGER DEFAULT 0                -- Soft delete flag (0 = active)
);

-- Order table (bus_order)
CREATE TABLE IF NOT EXISTS bus_order (
    id INTEGER PRIMARY KEY,
    order_no VARCHAR(50) UNIQUE NOT NULL -- Unique order serial number
    user_id INTEGER NOT NULL,             -- Foreign key linking to sys_user.id
    total_amount DECIMAL(12,2) NOT NULL,  -- Order total value
    order_status INTEGER DEFAULT 0,        -- Order state: 0=Unpaid, 1=Paid
    create_time TIMESTAMP,
    create_by INTEGER,
    update_time TIMESTAMP,
    update_by INTEGER,
    dr INTEGER DEFAULT 0                  -- Soft delete flag
);

Test Seed Data

3 sample users + 4 sample orders for join query testing:

-- Insert test users
INSERT INTO sys_user (id, name, age, email, phone, password, sex, status) VALUES
(1, 'Zhang San', 25, 'zhangsan@test.com', '13800138001', 'e10adc3949ba59abbe56e057f20f883e', 1, 1),
(2, 'Li Si', 30, 'lisi@test.com', '13800138002', 'e10adc3949ba59abbe56e057f20f883e', 1, 1),
(3, 'Wang Wu', 28, 'wangwu@test.com', '13800138003', 'e10adc3949ba59abbe56e057f20f883e', 2, 1);

-- Insert test orders
INSERT INTO bus_order (id, order_no, user_id, total_amount, order_status) VALUES
(1, 'ORD2026001', 1, 1999.00, 1),
(2, 'ORD2026002', 2, 299.00, 1),
(3, 'ORD2026003', 1, 399.00, 1),
(4, 'ORD2026004', 1, 1299.00, 1);

Core Code Implementation

1. Base Entity: Order (Maps to bus_order table)

@Table binds the class to database table name, @Id marks primary key. Audit columns can be hidden from API docs with @Schema(hidden=true):

@Data
@Schema(description = "Order Database Entity")
@Table("bus_order")
public class Order {
    @Id
    @Schema(description = "Primary Key ID")
    private Integer id;

    @Schema(description = "Unique Order Serial Number")
    private String orderNo;

    @Schema(description = "Associated User ID")
    private Integer userId;

    // Omitted other business fields: totalAmount, orderStatus, etc.

    // Auto-managed audit columns, hidden from frontend docs
    @Schema(hidden = true)
    private LocalDateTime createTime;
    @Schema(hidden = true)
    private Integer createBy;
    @Schema(hidden = true)
    private Integer dr; // Soft delete marker
}

2. Join Query DTO: OrderVO

Extends the base Order entity, adds joined user table fields for multi-table query results:

@Getter
@Setter
@ToString(callSuper = true) // Print parent class fields in logs
public class OrderVO extends Order {
    @Schema(description = "Customer Full Name")
    private String userName;  // Maps to aliased u.name from join SQL

    @Schema(description = "Customer Mobile Number")
    private String userPhone; // Maps to aliased u.phone

    @Schema(description = "Customer Contact Email")
    private String userEmail; // Maps to aliased u.email
}

Note: DTO field names must match column aliases defined in your JOIN SQL (e.g. u.name user_name), the framework auto-maps returned columns to VO properties.

3. DAO Layer: OrderDao (Core Join & Pagination Logic)

Core pattern: Raw LEFT JOIN SQL + single page() method to handle full pagination workflow

@Repository
public class OrderDao extends BaseDao<Order> {
    // Static native JOIN SQL: Order table LEFT JOIN User table to fetch combined data
    private final static String SQL = """
        SELECT t.id, t.order_no, t.user_id, t.total_amount, t.order_status,
               t.create_time, t.dr,
               u.name user_name, u.phone user_phone, u.email user_email
        FROM bus_order t
        LEFT JOIN sys_user u ON t.user_id = u.id
        """;

    /**
     * Paginated join query for orders + linked user information
     * @param cond Filter conditions object
     * @return Paginated result wrapped in OrderVO DTO
     */
    public Page<OrderVO> pageJoin(OrderCond cond) {
        // page() auto-applies filters, generates optimized COUNT query, appends LIMIT offset
        return page(SQL, cond, OrderVO.class);
    }
}

Key Features:

Full control over raw SQL — choose LEFT JOIN / INNER JOIN based on business requirements, no hidden framework rewrite logic

page() signature: Raw SQL string + condition object + result DTO class, returns fully paginated data in one call

Works for any number of joined tables; only modify the base SQL text with zero extra code changes

4. Filter Class: OrderCond (Cross-table filter chaining)

The addCondition() method uses the add() utility to safely chain cross-table filters in one line each

@Data
public class OrderCond extends BaseCondition {
    // Filter fields covering both order and user table columns
    private String orderNo;      // Match order serial number
    private Integer userId;      // Match linked user ID on order
    private String userName;     // Fuzzy match customer name
    private String userPhone;    // Exact match customer phone
    private String[] orderNos;   // Multiple order numbers for IN clause

    @Override
    protected void addCondition() {
        // 1. Bidirectional fuzzy match (%value%) for order number, mode=3
        add("AND t.order_no LIKE ?", orderNo, 3);
        // 2. Exact equality filter for linked user ID
        add("AND t.user_id = ?", userId);
        // 3. Negative fuzzy match (NOT LIKE %value%) for username
        add("AND u.name NOT LIKE ?", userName, 3);
        // 4. Exact match for customer phone number
        add("AND u.phone = ?", userPhone);
        // 5. IN clause for array of order serial numbers
        add("AND t.order_no IN ", orderNos);
    }
}

add() Method Core Rules:

First argument: Raw SQL filter fragment (supports any table aliases like t. / u.)

Second argument: Filter value; the entire condition is automatically skipped if the value is null (no manual null checks required)

Optional third parameter: Fuzzy search mode (1 = suffix match xxx%, 2 = prefix match %xxx, 3 = full wrap %xxx%)

IN clause shortcut: Only write AND column IN — the framework auto-expands the array to parameterized IN (?, ?, ...)

Service Layer Usage & Runtime Output

1. Service Invocation Demo

@Service
public class OrderService {
    @Autowired
    private OrderDao orderDao;

    public void demoJoinPage() {
        // Build filter: Username contains "Zhang" + match a set of target order numbers
        OrderCond cond = OrderCond.builder()
                .userName("Zhang")
                .orderNos(new String[]{"ORD2026001", "ORD2026003", "ORD2026004"})
                .build();

        // Run multi-table paginated query (defaults to page 1, 10 records per page)
        Page<OrderVO> page = orderDao.pageJoin(cond);

        // Print pagination output to logs
        log.info("Total matching records: {}", page.getRowCount());
        page.getDataList().forEach(vo -> log.info("Combined Order+User Data: {}", vo));
    }
}

2. Full Generated SQL (Printed in Application Logs)

-- Optimized COUNT query (rewrites SELECT clause instead of wrapping subquery)
[INFO] SELECT COUNT(1) FROM bus_order t LEFT JOIN sys_user u ON t.user_id = u.id WHERE u.name LIKE '%Zhang%' AND t.order_no IN ('ORD2026001', 'ORD2026003', 'ORD2026004')

-- Paginated data query with auto-generated LIMIT offset
[INFO] SELECT t.id, t.order_no, ..., u.name user_name, ... 
FROM bus_order t LEFT JOIN sys_user u ON t.user_id = u.id 
WHERE u.name LIKE '%Zhang%' AND t.order_no IN ('ORD2026001', 'ORD2026003', 'ORD2026004') 
LIMIT 0,10

Performance Benefit for COUNT Queries:
Instead of wrapping your full join SQL inside a subquery SELECT COUNT(*) FROM (...), SimpleDAO directly rewrites the top-level SELECT clause to COUNT(1) FROM. This avoids creating intermediate result sets and leverages database indexes fully — critical performance gain under high concurrent traffic.

Head-To-Head: MyBatis vs SimpleDAO Implementation

Comparison 1: Fuzzy LIKE Filter on Username

MyBatis XML Implementation

<if test="userName != null">
  AND u.name LIKE CONCAT('%', #{userName}, '%')
</if>

Downsides: Switch between XML markup, OGNL null checks, and native SQL functions; scattered code across separate files.

SimpleDAO Implementation

add("AND u.name LIKE ?", userName, 3);

Advantage: Single line pure Java, numeric mode defines wrap logic, condition auto-skipped for null values, no manual null validation.

Comparison 2: IN Clause Multi-value Matching

MyBatis XML Implementation

<if test="orderNos != null and orderNos.size() > 0">
  AND t.order_no IN
  <foreach collection="orderNos" item="no" open="(" separator="," close=")">
    #{no}
  </foreach>
</if>

Downsides: Nested tag boilerplate, many mandatory tag attributes; missing any attribute triggers runtime exceptions, hard to maintain for complex projects.

SimpleDAO Implementation

add("AND t.order_no IN ", orderNos);

Advantage: Pass raw array directly, framework auto-generates parameterized placeholders, no loop tag syntax required.

Episode Recap

Full Native Join Control: Write raw LEFT / INNER JOIN SQL, fully control table relationships with no hidden framework rewriting
Cross-table Filter Chaining: The add() utility safely applies filters for any joined table column, automatically skips null values
Optimized Pagination Count: Rewrites SELECT clause instead of wrapping subqueries for index-friendly counting
Zero Black Box Abstraction: Strict SQL-First design, no XML config, no proprietary dynamic tag syntax
Coexistence Compatibility: Works side-by-side with MyBatis; you can incrementally migrate DAO layers project by project

Next Episode Preview

Advanced Filter Logic: IN Clauses & Subqueries. Master the addIn() helper and reusable filter classes to simplify complicated report queries.

Source Access

All demo source code linked in pinned comment — clone & run with zero extra configuration. If this tutorial helped you, leave a like & save this post, follow for more SimpleDAO production tutorials!

1 . SimpleDAO From Zero To Production: Single-table CRUD, Audit Fields & Soft Delete

Z. Gao — Thu, 18 Jun 2026 03:06:12 +0000

A practical walkthrough of SQL-First persistence: no XML, no Mapper interfaces, no generated queries.

I maintain a Java backend that handles ~1M requests/day. For persistence, we used to run MyBatis. The XML was manageable at first, then it wasn't. Dynamic conditions became <if> tag soup. A simple join query needed three files and two languages.

We switched to a simpler approach. Here's how it works for the most common case: single-table CRUD.

What You Need

Java 21+
Spring Boot (any 3.x)
A database (H2 for the demo, MySQL/PostgreSQL for production)

That's it. No XML parser, no code generator, no annotation processor.

The Project

pom.xml
src/main/java/example/
  DemoApplication.java
  user/
    User.java        -- entity
    UserDao.java     -- data access
    UserCond.java    -- query conditions
src/main/resources/
  application.yml
  schema.sql

Three Java files for a complete CRUD API.

The Entity

@Data @Builder
@Table("sys_user")
public class User {
    @Id private Long id;
    private String name;
    private Integer age;
    private String email;
    // ... other fields

    // These four are auto-managed:
    private LocalDateTime createTime;
    private Long createBy;
    private LocalDateTime updateTime;
    private Long updateBy;
    private Byte dr;  // 0 = active, 1 = soft-deleted
}

@Table maps to the database table. @Id marks the primary key (Snowflake ID by default). The audit fields and soft-delete marker are handled automatically—you don't set them in business code.

The DAO

@Repository
public class UserDao extends BaseDao<User> {
    // Empty. All CRUD methods inherited.
}

BaseDao provides save, saveBatch, update, delete, findById, list, page, count, exists. For single-table operations, this is all you need.

The Conditions

@Getter @Setter @Builder
public class UserCond extends BaseCondition {
    private String name;
    private Integer ageMin;
    private Integer ageMax;
    private Byte dr;
    private Object[] ids;

    @Override
    protected void addCondition() {
        and("name LIKE", name, 3);   // 3 = %value%
        and("age >=", ageMin);
        and("age <=", ageMax);
        and("dr =", dr);
        in("id", ids);
    }
}

Each and() line is one condition. If the parameter is null, the condition is skipped. No <if> tags, no OGNL, no XML.

The 3 in and("name LIKE", name, 3) means "wrap with % on both sides". 1 = suffix, 2 = prefix.

Running It

@Autowired UserDao userDao;

public void demo() {
    // Insert
    User user = User.builder().name("John").age(25).email("john@example.com").build();
    userDao.save(user);  // id, createTime, createBy, dr auto-filled

    // Query by ID
    User found = userDao.findById(user.getId());

    // Paginated search
    Page<User> page = userDao.page(
        UserCond.builder().name("John").ageMin(20).ageMax(30).build()
    );

    // Update
    found.setAge(26);
    userDao.update(found);  // updateTime, updateBy auto-filled

    // Soft delete (dr=1, not DELETE)
    userDao.delete(found.getId());
}

The SQL (from logs)

-- save()
INSERT INTO sys_user (id,name,age,email,create_time,create_by,dr) 
VALUES (3679201737291333632,'John',25,'john@example.com','2026-04-10 13:14:10',1000,0)

-- page()
SELECT COUNT(1) FROM sys_user t 
WHERE t.name LIKE '%John%' AND t.age >= 20 AND t.age <= 30

SELECT t.id,t.name,t.age,t.email FROM sys_user t 
WHERE t.name LIKE '%John%' AND t.age >= 20 AND t.age <= 30 
LIMIT 0,10

-- delete() with dr field present
UPDATE sys_user t SET dr=1 WHERE id IN (3679201737291333632)

The SQL in the logs is the SQL that runs. No hidden transformations, no proxy-generated queries.

What This Isn't

Not an ORM: We don't map object graphs or handle relationships automatically. For joins, you write the SQL.
Not type-safe SQL: If you misspell a column name, you find out at runtime. The tradeoff is transparency.
Not a replacement for everything: If you need complex caching, second-level cache, or distributed transactions, Spring has other tools.

What This Is

A thin bridge between Java and SQL. The framework handles:

Parameter collection and null-checking
Audit field auto-fill
Soft-delete logic
Pagination (count + limit)

You handle:

The SQL
The business logic
The optimization

Episode 02: Multi-table joins with the same API → [link]

Full source: github.com/gzz2017gzz/simple-dao-demo