MyBatis-Flex DB Operations Hands-On Guide: Basics to Advanced

// Basic insert
Row user = new Row();
user.set("name", "张三");
user.set("age", 25);
user.set("email", "zhangsan@example.com");
int result = Db.insert("user", user); // Returns number of affected rows

// SQL-based insert - suitable for scenarios with few fields
int result = Db.insertBySql("INSERT INTO user(name, age) VALUES(?, ?)", "李四", 30);

// Batch insert - efficiently handles multiple records
List<Row> users = Arrays.asList(user1, user2, user3);
int[] results = Db.insertBatch("user", users); // Returns affected rows for each record

// Update by ID
Row updateData = new Row();
updateData.set("name", "李四");
updateData.set("age", 26);
int result = Db.updateById("user", updateData.setId("id", 1));

// SQL-based update - suitable for complex condition updates
int result = Db.updateBySql("UPDATE user SET status = ? WHERE age > ?", 1, 18);

// Update by condition
Map<String, Object> whereCondition = Map.of("status", 0);
int result = Db.updateByMap("user", updateData, whereCondition);

// Delete by ID
int result = Db.deleteById("user", "id", 1);

// Delete by condition
Map<String, Object> condition = Map.of("status", 0);
int result = Db.deleteByMap("user", condition);

// SQL-based delete - suitable for complex condition deletions
int result = Db.deleteBySql("DELETE FROM user WHERE created_at < ?", lastMonth);

boolean success = DbChain.table("user")
    .set("name", "王五")
    .set("age", 30)
    .set("email", "wangwu@example.com")
    .save(); // Inserts and returns whether operation was successful

boolean updated = DbChain.table("user")
    .set("status", 1)
    .set("updated_at", new Date())
    .where("id = ?", 1)
    .update(); // Updates and returns whether operation was successful

boolean deleted = DbChain.table("user")
    .where("status = ? AND created_at < ?", 0, lastMonth)
    .remove(); // Deletes and returns whether operation was successful

// Query single record by ID
Row user = Db.selectOneById("user", "id", 1);

// Query single record by condition
Map<String, Object> condition = Map.of("email", "test@example.com");
Row user = Db.selectOneByMap("user", condition);

// Query list satisfying conditions
List<Row> users = Db.selectListByMap("user", condition);

// Query all records in table
List<Row> allUsers = Db.selectAll("user");

// Direct SQL query - suitable for complex condition queries
String sql = "SELECT * FROM user WHERE status = ? AND age >= ?";
List<Row> users = Db.selectListBySql(sql, 1, 18);

// Query single value - suitable for aggregate function queries
Object maxAge = Db.selectObject("SELECT MAX(age) FROM user WHERE status = ?", 1);

// Count query - quickly get record count
long count = Db.selectCount("SELECT COUNT(*) FROM user WHERE status = ?", 1);

// Single record query
Row user = DbChain.table("user")
    .where("email = ?", "test@example.com")
    .one(); // Get single record

// Optional query (avoids null pointer exceptions)
Optional<Row> userOpt = DbChain.table("user")
    .where("id = ?", 1)
    .oneOpt(); // Get Optional-wrapped record

// List query
List<Row> activeUsers = DbChain.table("user")
    .where("status = ? AND age >= ?", 1, 18)
    .orderBy("created_at DESC")
    .limit(10) // Limit number of returned records
    .list(); // Get record list

// Count records
long count = DbChain.table("user")
    .where("status = ?", 1)
    .count();

// Check if record exists
boolean exists = DbChain.table("user")
    .where("email = ?", "test@example.com")
    .exists();

// Query single value
Object maxSalary = DbChain.table("user")
    .select("MAX(salary)") // Specify query field
    .where("dept_id = ?", 1)
    .obj(); // Get single value

// Two-table join query
String sql = """
    SELECT u.*, r.role_name 
    FROM user u 
    LEFT JOIN role r ON u.role_id = r.id 
    WHERE u.status = ? AND r.status = ?
    """;
List<Row> result = Db.selectListBySql(sql, 1, 1);

// Multi-table join query
String complexSql = """
    SELECT 
        u.name, 
        d.dept_name, 
        COUNT(p.id) as project_count
    FROM user u
    LEFT JOIN department d ON u.dept_id = d.id
    LEFT JOIN user_project up ON u.id = up.user_id
    LEFT JOIN project p ON up.project_id = p.id
    WHERE u.status = ? AND d.status = ?
    GROUP BY u.id, d.id
    HAVING COUNT(p.id) > ?
    ORDER BY project_count DESC
    """;
List<Row> complexResult = Db.selectListBySql(complexSql, 1, 1, 0);

// Build dynamic join query
QueryWrapper query = QueryWrapper.create()
    .select("u.*", "r.role_name", "d.dept_name")
    .from("user u")
    .leftJoin("role r").on("u.role_id = r.id")
    .leftJoin("department d").on("u.dept_id = d.id")
    .where("u.status = ?", 1);

// Dynamically add conditions
if (roleId != null) {
    query.and("u.role_id = ?", roleId);
}
if (deptId != null) {
    query.and("u.dept_id = ?", deptId);
}
if (StringUtil.hasText(keyword)) {
    query.and("u.name LIKE ?", "%" + keyword + "%");
}

query.orderBy("u.created_at DESC");
List<Row> users = Db.selectListByQuery(query); // Execute query

// Method 1: Use as() method to set alias for main table
QueryWrapper query = QueryWrapper.create()
    .select("u.*")
    .from("user")
    .as("u")  // Set alias u for user table
    .where("u.status = ?", 1);

// Generated SQL: SELECT u.* FROM user u WHERE u.status = ?

// Method 2: Set alias directly in from() method
QueryWrapper query = QueryWrapper.create()
    .select("u.*")
    .from(new QueryTable("user").as("u"))
    .where("u.status = ?", 1);

QueryWrapper query = QueryWrapper.create()
    .select("u.*", "r.role_name")
    .from("user u")  // Set main table alias directly
    .leftJoin("role r")  // Set joined table alias directly
    .on("u.role_id = r.id")
    .where("u.status = ?", 1);

// Or using QueryTable object
QueryWrapper query = QueryWrapper.create()
    .select("u.*", "r.role_name")
    .from(new QueryTable("user").as("u"))  // Main table alias
    .leftJoin(new QueryTable("role").as("r"))  // Joined table alias
    .on("u.role_id = r.id");

// Setting alias when using subquery as table
QueryWrapper subQuery = QueryWrapper.create()
    .select("dept_id", "COUNT(*) as user_count")
    .from("user")
    .where("status = ?", 1)
    .groupBy("dept_id");

QueryWrapper mainQuery = QueryWrapper.create()
    .select("d.dept_name", "uc.user_count")
    .from("department d")
    .leftJoin(subQuery).as("uc").on("d.id = uc.dept_id");  // Subquery alias

String subQuerySql = """
    SELECT u.* FROM user u 
    WHERE u.dept_id IN (
        SELECT d.id FROM department d 
        WHERE d.region = ? AND d.status = ?
    ) 
    AND u.salary > (
        SELECT AVG(salary) FROM user WHERE dept_id = u.dept_id
    )
    """;
List<Row> result = Db.selectListBySql(subQuerySql, "华东", 1);

String existsSql = """
    SELECT u.* FROM user u 
    WHERE EXISTS (
        SELECT 1 FROM user_role ur 
        WHERE ur.user_id = u.id AND ur.role_id = ?
    )
    """;
List<Row> usersWithRole = Db.selectListBySql(existsSql, 1);

// Ranking query
String rankSql = """
    SELECT 
        name, 
        salary, 
        dept_id,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank_in_dept,
        RANK() OVER (ORDER BY salary DESC) as overall_rank
    FROM user 
    WHERE status = ?
    """;
List<Row> rankedUsers = Db.selectListBySql(rankSql, 1);

// Cumulative statistics
String cumulativeSql = """
    SELECT 
        DATE(created_at) as date,
        COUNT(*) as daily_count,
        SUM(COUNT(*)) OVER (ORDER BY DATE(created_at)) as cumulative_count
    FROM user 
    WHERE created_at >= ?
    GROUP BY DATE(created_at)
    ORDER BY date
    """;
List<Row> stats = Db.selectListBySql(cumulativeSql, startDate);

// Build query conditions
QueryWrapper query = QueryWrapper.create()
    .select("*")
    .from("user")
    .where("status = ?", 1)
    .orderBy("created_at DESC");

// Execute pagination query - parameters: table name, page number, page size, query conditions
Page<Row> page = Db.paginate("user", 1, 20, query);

// Access pagination results
System.out.println("Total records: " + page.getTotalRow());
System.out.println("Total pages: " + page.getTotalPage());
System.out.println("Current page data: " + page.getRecords());

Page<Row> page = new Page<>(1, 20); // Create pagination object, set page number and page size
Page<Row> result = DbChain.table("user")
    .where("status = ? AND age >= ?", 1, 18)
    .orderBy("salary DESC", "created_at DESC")
    .page(page); // Execute pagination query

// Pagination with known total (performance optimization)
Page<Row> pageWithTotal = new Page<>(2, 20, 1000L); // Specify total to avoid duplicate counting
Page<Row> result = DbChain.table("user")
    .where("status = ?", 1)
    .page(pageWithTotal);

// 1. First query total count
String countSql = """
    SELECT COUNT(*) FROM user u 
    LEFT JOIN department d ON u.dept_id = d.id 
    WHERE u.status = ? AND d.region = ?
    """;
long total = Db.selectCount(countSql, 1, "华东");

// 2. Then query paginated data
String dataSql = """
    SELECT u.*, d.dept_name 
    FROM user u 
    LEFT JOIN department d ON u.dept_id = d.id 
    WHERE u.status = ? AND d.region = ?
    ORDER BY u.salary DESC 
    LIMIT ? OFFSET ?
    """;

int pageNum = 1, pageSize = 20;
int offset = (pageNum - 1) * pageSize;
List<Row> data = Db.selectListBySql(dataSql, 1, "华东", pageSize, offset);

// 3. Manually build pagination object
Page<Row> page = new Page<>(pageNum, pageSize, total);
page.setRecords(data);

// Boolean return value transaction
boolean success = Db.tx(() -> {
    // Insert user
    Row user = new Row();
    user.set("name", "张三");
    user.set("email", "zhangsan@example.com");
    Db.insert("user", user);
    
    // Insert user-role relationship
    Row userRole = new Row();
    userRole.set("user_id", user.get("id"));
    userRole.set("role_id", 1);
    Db.insert("user_role", userRole);