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

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!

推荐订阅源

DEV Community

GitHub (Overseas Access Friendly)

Supplementary Note

Full Tutorial Series: SimpleDAO From Zero To Production

Prerequisites

Full Series Roadmap

Episode Goals

Project Setup

1. Standard Project Structure

2. Minimal Core Dependencies

3. Datasource Configuration (application.yml)

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

Table Definitions

Test Seed Data

Core Code Implementation

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

2. Join Query DTO: OrderVO

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

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

Service Layer Usage & Runtime Output

1. Service Invocation Demo

2. Full Generated SQL (Printed in Application Logs)

Head-To-Head: MyBatis vs SimpleDAO Implementation

Comparison 1: Fuzzy LIKE Filter on Username

Comparison 2: IN Clause Multi-value Matching

Episode Recap

Next Episode Preview

Source Access