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

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 performance