数据库操作参考

💾 数据库操作：MyBatis-Flex使用指南和最佳实践

📋 目录

• MyBatis-Flex基础
• 实体类映射
• 查询构建器
• 分页查询
• 多数据源配置
• 事务管理

---

MyBatis-Flex基础

核心依赖

<dependency>
    <groupId>com.mybatis-flex</groupId>
    <artifactId>mybatis-flex-spring-boot-starter</artifactId>
    <version>1.10.8</version>
</dependency>

基础配置

# application.yml
mybatis-flex:
  configuration:
    map-underscore-to-camel-case: false  # 不自动转换下划线
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  # SQL日志
  global-config:
    print-banner: true

基本使用流程

// 1. 实体类继承BaseEntity
@Table("business_table")
public class BusinessEntity extends BaseEntity {
    // 字段定义...
}

// 2. Mapper接口继承BaseMapper
@Mapper
public interface BusinessMapper extends BaseMapper<BusinessEntity> {
    // 自动获得基础CRUD方法
}

// 3. Service使用
@Service
@UseDataSource(DbNameConst.JPWISE_DEMO)
public class BusinessServiceImpl extends ServiceImpl<BusinessMapper, BusinessEntity> {
    // 业务逻辑实现
}

---

实体类映射

实体类注解

@Data
@Table("BUSINESS_TABLE")  // 表名映射
@Schema(description = "业务实体")
public class BusinessEntity extends BaseEntity {
    
    // 主键映射
    @Id(value = "ID")
    @Column(value = "ID")
    @JsonProperty("ID")
    private String ID;
    
    // 普通字段映射
    @Schema(description = "业务标题")
    @Column("TITLE")           // 数据库字段名
    @JsonProperty("TITLE")     // JSON序列化字段名
    private String TITLE;
    
    // 忽略字段（不参与数据库操作）
    @Column(ignore = true)
    private String tempField;
    
    // 只插入字段
    @Column(onInsertValue = "now()")
    private Date createTime;
    
    // 只更新字段
    @Column(onUpdateValue = "now()")
    private Date updateTime;
}

字段类型映射

// 数值类型
private Integer intField;       // INT
private Long longField;         // BIGINT
private BigDecimal decimalField; // DECIMAL

// 字符串类型
private String stringField;     // VARCHAR

// 日期时间类型
private Date dateField;         // DATETIME

// 布尔类型
private Boolean boolField;      // TINYINT(1)

// JSON类型（MySQL 5.7+）
private String jsonField;       // JSON

---

查询构建器

QueryWrapper基础用法

QueryWrapper queryWrapper = new QueryWrapper();

// 等于条件
queryWrapper.eq(BusinessEntity::getTITLE, "测试标题");

// 不等于条件
queryWrapper.ne(BusinessEntity::getSTATUS, "DELETED");

// 模糊查询
queryWrapper.like(BusinessEntity::getNAME, "关键字");

// 范围查询
queryWrapper.between(BusinessEntity::getAMOUNT, 100, 1000);
queryWrapper.ge(BusinessEntity::getCREATORTIME, startDate);  // 大于等于
queryWrapper.le(BusinessEntity::getCREATORTIME, endDate);    // 小于等于

// 空值判断
queryWrapper.isNull(BusinessEntity::getDELETEUSER);     // 为空
queryWrapper.isNotNull(BusinessEntity::getDELETEUSER); // 不为空

// IN查询
queryWrapper.in(BusinessEntity::getSTATUS, Arrays.asList("PENDING", "APPROVED"));

// 排序
queryWrapper.orderBy(BusinessEntity::getCREATORTIME, false);  // false = DESC
queryWrapper.orderBy(BusinessEntity::getTITLE, true);        // true = ASC

复杂条件组合

// AND条件组合
queryWrapper.eq(BusinessEntity::getSTATUS, "ACTIVE")
           .like(BusinessEntity::getTITLE, "关键字")
           .ge(BusinessEntity::getAMOUNT, 100);

// OR条件组合（需要显式类型转换）
import java.util.function.Consumer;

queryWrapper.and((Consumer<QueryWrapper>) w -> {
    w.like(BusinessEntity::getTITLE, keyword)
     .or((Consumer<QueryWrapper>) ww -> ww.like(BusinessEntity::getCODE, keyword))
     .or((Consumer<QueryWrapper>) ww -> ww.like(BusinessEntity::getNAME, keyword));
});

// 复杂嵌套条件
queryWrapper.eq(BusinessEntity::getSTATUS, "ACTIVE")
           .and(w -> w.ge(BusinessEntity::getAMOUNT, 100)
                      .le(BusinessEntity::getAMOUNT, 1000))
           .or(w -> w.eq(BusinessEntity::getUSERTYPE, "VIP"));

子查询

// EXISTS子查询
queryWrapper.exists("SELECT 1 FROM detail_table WHERE detail_table.main_id = " + 
                   BusinessEntity.TABLE + ".ID");

// IN子查询
queryWrapper.inSql(BusinessEntity::getDEPARTMENTID, 
    "SELECT id FROM department WHERE type = 'SALES'");

// NOT IN子查询
queryWrapper.notInSql(BusinessEntity::getUserId, 
    "SELECT user_id FROM blacklist");

---

分页查询

基础分页

@Override
public List<BusinessEntity> getList(QueryBuilder pagination) {
    QueryWrapper queryWrapper = new QueryWrapper();
    
    // 构建查询条件
    queryWrapper.eq(BusinessEntity::getENABLEDMARK, "1")
               .isNull(BusinessEntity::getDELETEUSER);
    
    // 创建分页对象
    Page<BusinessEntity> page = new Page<>(
        pagination.getCurrentPage(),  // 当前页，从1开始
        pagination.getPageSize()      // 页大小
    );
    
    // 执行分页查询
    Page<BusinessEntity> result = this.page(page, queryWrapper);
    
    // 设置分页数据
    return pagination.setData(result.getRecords(), result.getTotalRow());
}

分页优化

// 大数据量分页优化（避免深分页）
Page<BusinessEntity> page = new Page<>(
    pagination.getCurrentPage(),
    pagination.getPageSize(),
    false  // 不查询总数，提高性能
);

// 使用JdbcUtil进行分页查询（复杂SQL场景）
public JdbcPageMod<Map<String, Object>> getPageDataWithJdbc(QueryBuilder queryBuilder) {
    Connection conn = ConnUtil.getConn(DbNameConst.JPWISE_DEMO);
    
    String sql = """
        SELECT * FROM business_table 
        WHERE status = ? 
        AND enabled_mark = '1' 
        AND delete_user IS NULL
    """;
    
    PrepareSqlDTO sqlDTO = new PrepareSqlDTO();
    sqlDTO.setConn(conn);
    sqlDTO.setPrepareSql(sql);
    sqlDTO.setDataList(Arrays.asList("ACTIVE"));
    
    return JdbcUtil.queryPage(sqlDTO, queryBuilder);
}

---

多数据源配置

数据源常量

public class DbNameConst {
    public static final String JPWISE_BASE = "jpwise_base";        // 基础数据库
    public static final String JPWISE_DEMO = "jpwise_demo";        // 测试项目
    public static final String JPWISE_HR = "jpwise_hr";            // 人力资源
    public static final String JPWISE_AFFAIR = "jpwise_affair";    // 事务数据库
    public static final String JPWISE_DESIGN = "jpwise_design";    // 设计项目
    public static final String JPWISE_DOCSYSTEM = "jpwise_docsystem"; // 档案系统
}

Service层数据源指定

// 在Service实现类上指定数据源
@Service
@UseDataSource(DbNameConst.JPWISE_DEMO)  // 使用测试数据库
public class BusinessServiceImpl extends ServiceImpl<BusinessMapper, BusinessEntity> {
    
    // 方法级别数据源切换（如果需要）
    @UseDataSource(DbNameConst.JPWISE_BASE)
    public List<SystemEntity> getSystemData() {
        // 这个方法使用基础数据库
    }
}

跨数据源查询

@Service
public class CrossDataSourceService {
    
    @Autowired
    private BusinessMapper businessMapper;
    
    @Autowired
    private SystemMapper systemMapper;
    
    public void crossDataSourceOperation() {
        // 查询业务数据（JPWISE_DEMO）
        List<BusinessEntity> businessList = businessMapper.selectAll();
        
        // 查询系统数据（JPWISE_BASE）
        List<SystemEntity> systemList = systemMapper.selectAll();
        
        // 注意：跨数据源无法使用数据库事务，需要应用层控制
    }
}

---

事务管理

基础事务

@Service
@UseDataSource(DbNameConst.JPWISE_DEMO)
public class BusinessServiceImpl {
    
    @Transactional(rollbackFor = Exception.class)
    public void createBusiness(BusinessEntity entity) {
        // 保存主表
        this.save(entity);
        
        // 保存子表
        detailService.saveBatch(entity.getDetailList());
        
        // 如果出现异常，所有操作都会回滚
        if (someCondition) {
            throw new BusinessException("业务异常，触发回滚");
        }
    }
}

事务传播行为

// 必须在事务中执行
@Transactional(propagation = Propagation.REQUIRED)
public void requiredTransaction() { }

// 创建新事务
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void newTransaction() { }

// 非事务执行
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public void nonTransaction() { }

// 只读事务（优化性能）
@Transactional(readOnly = true)
public List<BusinessEntity> readOnlyQuery() { }

手动事务控制

@Service
public class ManualTransactionService {
    
    @Autowired
    private DataSourceTransactionManager transactionManager;
    
    public void manualTransaction() {
        TransactionStatus status = transactionManager.getTransaction(
            new DefaultTransactionDefinition());
        
        try {
            // 业务操作
            businessOperation();
            
            // 提交事务
            transactionManager.commit(status);
        } catch (Exception e) {
            // 回滚事务
            transactionManager.rollback(status);
            throw e;
        }
    }
}

---

性能优化

查询优化

// 1. 只查询需要的字段
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.select(BusinessEntity::getID, BusinessEntity::getTITLE, BusinessEntity::getSTATUS)
           .eq(BusinessEntity::getENABLEDMARK, "1");

// 2. 使用索引字段作为查询条件
queryWrapper.eq(BusinessEntity::getID, id)  // 主键查询，最快
           .eq(BusinessEntity::getCODE, code); // 如果CODE字段有索引

// 3. 避免函数操作
// ❌ 错误：在字段上使用函数
queryWrapper.apply("DATE(create_time) = '2024-08-14'");
// ✅ 正确：使用范围查询
queryWrapper.ge(BusinessEntity::getCREATORTIME, startOfDay)
           .lt(BusinessEntity::getCREATORTIME, endOfDay);

批量操作优化

// 批量插入
@Override
public void batchInsert(List<BusinessEntity> entities) {
    // 分批处理，避免内存溢出
    int batchSize = 1000;
    for (int i = 0; i < entities.size(); i += batchSize) {
        int end = Math.min(i + batchSize, entities.size());
        List<BusinessEntity> batch = entities.subList(i, end);
        this.saveBatch(batch);
    }
}

// 批量更新
@Override
public void batchUpdate(List<BusinessEntity> entities) {
    this.updateBatchById(entities, entities.size());
}

缓存优化

@Service
public class CachedBusinessService {
    
    @Autowired
    private RedisUtil redisUtil;
    
    public BusinessEntity getWithCache(String id) {
        String cacheKey = "business:" + id;
        
        // 先查缓存
        BusinessEntity cached = (BusinessEntity) redisUtil.getString(cacheKey);
        if (cached != null) {
            return cached;
        }
        
        // 查数据库
        BusinessEntity entity = this.getById(id);
        if (entity != null) {
            // 存入缓存
            redisUtil.insert(cacheKey, entity, RedisUtil.CACHE_HOUR);
        }
        
        return entity;
    }
}

---

复杂SQL操作

使用JdbcUtil处理复杂SQL

对于复杂的SQL查询、动态条件查询、批量操作等，推荐在Service层使用JdbcUtil：

import jpwise.database.util.JdbcUtil;
import jpwise.database.util.ConnUtil;
import jpwise.database.constant.DbNameConst;
import jpwise.database.model.dto.PrepareSqlDTO;
import jpwise.database.model.page.JdbcPageMod;

@Service
@UseDataSource(DbNameConst.JPWISE_DEMO)
public class BusinessServiceImpl extends ServiceImpl<BusinessMapper, BusinessEntity> {
    
    // 复杂关联查询
    public List<Map<String, Object>> getDetailWithRelations(String status) {
        Connection conn = ConnUtil.getConn(DbNameConst.JPWISE_DEMO);
        
        String sql = """
            SELECT 
                b.*,
                u.real_name as creator_name,
                d.name as department_name
            FROM business_table b
            LEFT JOIN sys_user u ON b.creator_user = u.id
            LEFT JOIN sys_department d ON b.organize = d.id
            WHERE b.enabled_mark = '1'
                AND b.delete_user IS NULL
                AND b.status = ?
            ORDER BY b.creator_time DESC
        """;
        
        PrepareSqlDTO sqlDTO = new PrepareSqlDTO();
        sqlDTO.setConn(conn);
        sqlDTO.setPrepareSql(sql);
        sqlDTO.setDataList(Arrays.asList(status));
        
        return JdbcUtil.queryList(sqlDTO);
    }
    
    // 动态条件查询
    public List<Map<String, Object>> dynamicQuery(String status, String keyword, 
                                                   Date startDate, Date endDate) {
        Connection conn = ConnUtil.getConn(DbNameConst.JPWISE_DEMO);
        
        StringBuilder sql = new StringBuilder("""
            SELECT * FROM business_table 
            WHERE enabled_mark = '1' AND delete_user IS NULL
        """);
        List<Object> params = new ArrayList<>();
        
        if (StringUtil.isNotEmpty(status)) {
            sql.append(" AND status = ?");
            params.add(status);
        }
        
        if (StringUtil.isNotEmpty(keyword)) {
            sql.append(" AND (title LIKE ? OR code LIKE ?)");
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
        }
        
        if (startDate != null) {
            sql.append(" AND creator_time >= ?");
            params.add(startDate);
        }
        
        if (endDate != null) {
            sql.append(" AND creator_time <= ?");
            params.add(endDate);
        }
        
        sql.append(" ORDER BY creator_time DESC");
        
        PrepareSqlDTO sqlDTO = new PrepareSqlDTO();
        sqlDTO.setConn(conn);
        sqlDTO.setPrepareSql(sql.toString());
        sqlDTO.setDataList(params);
        
        return JdbcUtil.queryList(sqlDTO);
    }
    
    // 批量更新
    @Transactional(rollbackFor = Exception.class)
    public int batchUpdateStatus(List<String> ids, String status) {
        Connection conn = ConnUtil.getConn(DbNameConst.JPWISE_DEMO);
        
        String inClause = ids.stream().map(id -> "?").collect(Collectors.joining(","));
        String sql = String.format("""
            UPDATE business_table 
            SET status = ?, 
                last_modify_time = NOW()
            WHERE id IN (%s)
        """, inClause);
        
        List<Object> params = new ArrayList<>();
        params.add(status);
        params.addAll(ids);
        
        PrepareSqlDTO sqlDTO = new PrepareSqlDTO();
        sqlDTO.setConn(conn);
        sqlDTO.setPrepareSql(sql);
        sqlDTO.setDataList(params);
        
        return JdbcUtil.executeUpdate(sqlDTO);
    }
    
    // 统计查询
    public Map<String, Object> getStatistics() {
        Connection conn = ConnUtil.getConn(DbNameConst.JPWISE_DEMO);
        
        String sql = """
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) as active_count,
                SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) as pending_count,
                SUM(amount) as total_amount
            FROM business_table
            WHERE enabled_mark = '1' AND delete_user IS NULL
        """;
        
        PrepareSqlDTO sqlDTO = new PrepareSqlDTO();
        sqlDTO.setConn(conn);
        sqlDTO.setPrepareSql(sql);
        sqlDTO.setDataList(new ArrayList<>());
        
        List<Map<String, Object>> result = JdbcUtil.queryList(sqlDTO);
        return result.isEmpty() ? new HashMap<>() : result.get(0);
    }
}

---

调试和监控

SQL日志配置

# application.yml
mybatis-flex:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

# 或使用Logback配置
logging:
  level:
    jpwise.base.mapper: DEBUG  # 输出SQL日志

---

💡 提示：MyBatis-Flex提供了强大而灵活的数据库操作能力。合理使用QueryWrapper可以构建复杂查询，配合分页和缓存机制可以显著提升性能。记住在Service层指定正确的数据源，并合理使用事务管理。