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

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

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

-- 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
);

-- 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);

@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
}

@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
}

@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);
    }
}

@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);
    }
}

@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));
    }
}

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

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

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

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

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