stream = mapper.selectStream()) { stream.forEach(article -> { // 处理每条数据 process(article); }); } } } ``` --- ## MyBatis-Plus 核心篇 ### 10. MyBatis-Plus 相比 MyBatis 的优势？ **回答要点：** - 零 XML 配置，CRUD 零代码 - 内置通用 Mapper 和 Service - 强大的条件构造器 QueryWrapper - 分页插件、代码生成器、性能分析 - 支持多租户、数据权限、字段加密 **代码对比：** | 功能 | MyBatis | MyBatis-Plus | | -------- | --------------- | --------------------------- | | 单表查询 | 写 XML/注解 | `baseMapper.selectById()` | | 条件查询 | 写动态 SQL | `new QueryWrapper<>().eq()` | | 分页 | 手动 LIMIT/插件 | `pageHelper.selectPage()` | | 批量操作 | 手写 SQL | `saveBatch()/updateBatch()` | | 代码生成 | 手动/第三方 | 内置 Generator | ### 11. 通用 Mapper 和 Service 的使用？ **代码示例：** ```java // 实体类 @TableName("user") @Data public class User { @TableId(type = IdType.AUTO) // 主键自增 private Long id; private String name; private Integer age; private String email; @TableField(fill = FieldFill.INSERT) // 自动填充 private LocalDateTime createTime; @TableField(fill = FieldFill.INSERT_UPDATE) private LocalDateTime updateTime; @TableLogic // 逻辑删除 private Integer deleted; @Version // 乐观锁 private Integer version; } // Mapper 接口 @Mapper public interface UserMapper extends BaseMapper { // 继承 BaseMapper，获得 17 种 CRUD 方法 // selectById, selectList, selectPage, insert, updateById, deleteById... } // Service 接口 public interface UserService extends IService { // 继承 IService，获得更多便捷方法 } // Service 实现 @Service public class UserServiceImpl extends ServiceImpl implements UserService { // 可以直接使用 baseMapper 或 this 的方法 } // 使用示例 public class UserServiceTest { @Autowired private UserService userService; // 基础 CRUD public void basicCrud() { // 插入 User user = new User(); user.setName("张三"); user.setAge(25); userService.save(user); // INSERT // 查询 User found = userService.getById(user.getId()); // SELECT // 更新 user.setAge(26); userService.updateById(user); // UPDATE // 删除 userService.removeById(user.getId()); // DELETE } // 批量操作 public void batchOperation() { List users = Arrays.asList( new User("张三", 25), new User("李四", 30), new User("王五", 35) ); // 批量插入 userService.saveBatch(users); // 批量更新 users.forEach(u -> u.setAge(u.getAge() + 1)); userService.updateBatchById(users); // 批量删除 List ids = users.stream().map(User::getId).toList(); userService.removeByIds(ids); } // 条件查询 public void queryWithCondition() { // 查询年龄大于 25 的用户 List users = userService.listQuery( new QueryWrapper().gt("age", 25) ); // 查询姓名包含"张"且年龄 20-30 的用户 List users2 = userService.listQuery( new QueryWrapper() .like("name", "张") .between("age", 20, 30) ); // 分页查询 Page page = userService.page( new Page<>(1, 10), new QueryWrapper().orderByDesc("create_time") ); } } ``` ### 12. 条件构造器 QueryWrapper 的使用？ **代码示例：** ```java // 基础条件 QueryWrapper wrapper = new QueryWrapper<>(); wrapper.eq("age", 25); // age = 25 wrapper.ne("status", 0); // status != 0 wrapper.gt("age", 18); // age > 18 wrapper.ge("age", 18); // age >= 18 wrapper.lt("age", 60); // age < 60 wrapper.le("age", 60); // age <= 60 wrapper.between("age", 18, 60); // age BETWEEN 18 AND 60 wrapper.notBetween("age", 18, 60); // age NOT BETWEEN 18 AND 60 wrapper.like("name", "张"); // name LIKE '%张%' wrapper.likeLeft("name", "张"); // name LIKE '%张' wrapper.likeRight("name", "张"); // name LIKE '张%' wrapper.notLike("name", "张"); // name NOT LIKE '%张%' wrapper.isNull("email"); // email IS NULL wrapper.isNotNull("email"); // email IS NOT NULL wrapper.in("status", 1, 2, 3); // status IN (1,2,3) wrapper.notIn("status", 0, 4); // status NOT IN (0,4) wrapper.eqSql("age", "select avg(age) from user"); // 子查询 // 排序 wrapper.orderByAsc("create_time"); // ORDER BY create_time ASC wrapper.orderByDesc("age"); // ORDER BY age DESC wrapper.orderBy(true, true, "age"); // 动态排序 // 逻辑组合 wrapper.and(w -> w.eq("name", "张三").eq("age", 25)); // AND (name = '张三' AND age = 25) wrapper.or(w -> w.eq("name", "李四").eq("age", 30)); // OR (name = '李四' AND age = 30) wrapper.nested(w -> w.eq("name", "张三").or().eq("name", "李四")); // AND (name = '张三' OR name = '李四') // 链式调用 List users = userService.query() .eq("age", 25) .like("name", "张") .orderByDesc("create_time") .last("LIMIT 10") // 原生 SQL .list(); // Lambda 表达式 (类型安全) LambdaQueryWrapper lambda = new LambdaQueryWrapper<>(); lambda.eq(User::getAge, 25); lambda.like(User::getName, "张"); lambda.in(User::getStatus, 1, 2, 3); lambda.orderByDesc(User::getCreateTime); // 更简洁的 Wrappers 工具类 List users = userService.list( Wrappers.lambdaQuery() .eq(User::getAge, 25) .like(User::getName, "张") ); // 复杂条件示例 public List searchUsers(SearchCondition condition) { return userService.list( Wrappers.lambdaQuery() .eq(condition.getStatus() != null, User::getStatus, condition.getStatus()) .like(StringUtils.isNotBlank(condition.getName()), User::getName, condition.getName()) .between(condition.getMinAge() != null, User::getAge, condition.getMinAge(), condition.getMaxAge()) .in(condition.getDeptIds() != null && !condition.getDeptIds().isEmpty(), User::getDeptId, condition.getDeptIds()) .orderByDesc(User::getCreateTime) ); } ``` ### 13. 自动填充功能如何实现？ **代码示例：** ```java // 1. 定义枚举 public enum FieldFill { DEFAULT, // 默认不处理 INSERT, // 插入时填充 UPDATE, // 更新时填充 INSERT_UPDATE // 插入和更新都填充 } // 2. 实现 MetaObjectHandler @Component public class MyMetaObjectHandler implements MetaObjectHandler { @Autowired private UserService userService; @Override public void insertFill(MetaObject metaObject) { // 获取当前登录用户 LoginUser currentUser = SecurityUtil.getCurrentUser(); // 创建时间 this.strictInsertFill(metaObject, "createTime", LocalDateTime::now, LocalDateTime.class); // 创建人 this.strictInsertFill(metaObject, "createBy", () -> currentUser != null ? currentUser.getId() : null, Long.class); // 更新时间 (同时填充) this.strictInsertFill(metaObject, "updateTime", LocalDateTime::now, LocalDateTime.class); // 更新人 this.strictInsertFill(metaObject, "updateBy", () -> currentUser != null ? currentUser.getId() : null, Long.class); } @Override public void updateFill(MetaObject metaObject) { // 更新时间 this.strictUpdateFill(metaObject, "updateTime", LocalDateTime::now, LocalDateTime.class); // 更新人 LoginUser currentUser = SecurityUtil.getCurrentUser(); this.strictUpdateFill(metaObject, "updateBy", () -> currentUser != null ? currentUser.getId() : null, Long.class); } // 自定义填充策略 private void strictInsertFill(MetaObject metaObject, String fieldName, Supplier supplier, Class fieldType) { // 如果字段已有值，不覆盖 if (getFieldValByName(fieldName, metaObject) == null) { setFieldValByName(fieldName, supplier.get(), metaObject); } } } // 3. 实体类标注 @TableName("user") @Data public class User { @TableId(type = IdType.AUTO) private Long id; private String name; @TableField(fill = FieldFill.INSERT) private LocalDateTime createTime; @TableField(fill = FieldFill.INSERT) private Long createBy; @TableField(fill = FieldFill.INSERT_UPDATE) private LocalDateTime updateTime; @TableField(fill = FieldFill.INSERT_UPDATE) private Long updateBy; } // 4. 使用 (自动填充) public void testAutoFill() { User user = new User(); user.setName("张三"); // 未设置 createTime、updateTime 等字段 userService.save(user); // 插入后，自动填充 createTime、createBy、updateTime、updateBy System.out.println(user.getCreateTime()); // 自动填充的值 System.out.println(user.getCreateBy()); // 当前用户 ID } ``` ### 14. 逻辑删除的实现？ **代码示例：** ```java // 1. 全局配置 (application.yml) mybatis-plus: global-config: db-config: logic-delete-field: deleted # 全局逻辑删除字段名 logic-delete-value: 1 # 已删除值 logic-not-delete-value: 0 # 未删除值 // 2. 实体类标注 @TableName("user") @Data public class User { @TableId private Long id; private String name; @TableLogic // 标注逻辑删除字段 private Integer deleted; // 0: 未删除，1: 已删除 } // 3. 使用效果 public void testLogicDelete() { // 普通删除 → 变为更新 userService.removeById(1L); // 执行 SQL: UPDATE user SET deleted = 1 WHERE id = 1 AND deleted = 0 // 查询时自动添加条件 userService.getById(1L); // 执行 SQL: SELECT * FROM user WHERE id = 1 AND deleted = 0 userService.list(); // 执行 SQL: SELECT * FROM user WHERE deleted = 0 // 物理删除 (强制) userService.removeByIds(Arrays.asList(1L, 2L), true); // 自定义方法 // 执行 SQL: DELETE FROM user WHERE id IN (1, 2) } // 4. 自定义逻辑删除值 @TableName("user") @Data public class User { @TableId private Long id; @TableLogic(deleteValue = "1", notDeleteValue = "0") private Integer deleted; // 使用删除时间 @TableLogic(deleteValue = "CURRENT_TIMESTAMP") private LocalDateTime deleteTime; } // 5. 恢复已删除数据 public void restoreDeleted(Long id) { User user = new User(); user.setId(id); user.setDeleted(0); // 设置为未删除 // 需要直接更新，绕过逻辑删除 baseMapper.update(user, new UpdateWrapper() .eq("id", id) .setSql("deleted = 0")); } ``` ### 15. 乐观锁的实现？ **代码示例：** ```java // 1. 配置乐观锁插件 @Configuration public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); // 乐观锁插件 interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor()); return interceptor; } } // 2. 实体类标注 @TableName("product") @Data public class Product { @TableId private Long id; private String name; private Integer stock; private BigDecimal price; @Version // 乐观锁版本号 private Integer version; } // 3. 使用场景：库存扣减 @Service public class ProductService { @Autowired private ProductMapper productMapper; @Transactional(rollbackFor = Exception.class) public boolean deductStock(Long productId, Integer quantity) { // 第一次查询 Product product = productMapper.selectById(productId); if (product.getStock() < quantity) { throw new BusinessException("库存不足"); } // 模拟业务处理 try { Thread.sleep(100); } catch (InterruptedException e) { e.printStackTrace(); } // 更新库存 product.setStock(product.getStock() - quantity); // 更新时会自动添加 version 条件 // UPDATE product SET stock = ?, version = version + 1 // WHERE id = ? AND version = ? boolean success = productMapper.updateById(product) > 0; if (!success) { throw new BusinessException("更新失败，数据已被修改"); } return true; } // 使用 UpdateWrapper 更新 public boolean updatePrice(Long productId, BigDecimal newPrice) { Product updateProduct = new Product(); updateProduct.setId(productId); updateProduct.setPrice(newPrice); return productMapper.update(updateProduct, new UpdateWrapper() .eq("id", productId) // version 会自动处理 ) > 0; } } // 4. 乐观锁重试机制 @Service public class OrderService { @Autowired private ProductService productService; public void createOrderWithRetry(OrderDTO order) { int maxRetries = 3; int retryCount = 0; while (retryCount < maxRetries) { try { // 扣减库存 productService.deductStock(order.getProductId(), order.getQuantity()); // 创建订单 createOrder(order); return; // 成功 } catch (BusinessException e) { if (e.getMessage().contains("更新失败")) { retryCount++; log.warn("乐观锁冲突，重试 {}/{}", retryCount, maxRetries); if (retryCount >= maxRetries) { throw new BusinessException("下单失败，请重试"); } // 短暂等待后重试 try { Thread.sleep(100 * retryCount); } catch (InterruptedException ie) { Thread.currentThread().interrupt(); } } else { throw e; // 其他异常直接抛出 } } } } } // 5. CAS 风格更新 public boolean compareAndSet(Long productId, Integer oldVersion, Integer newStock) { Product update = new Product(); update.setStock(newStock); return productMapper.update(update, new UpdateWrapper() .eq("id", productId) .eq("version", oldVersion) ) > 0; } ``` --- ## MyBatis-Plus 进阶篇 ### 16. 分页插件的配置和使用？ **代码示例：** ```java // 1. 配置分页插件 @Configuration public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); // 分页插件 PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor(DbType.MYSQL); // 设置最大单页限制 (防止恶意查询) paginationInterceptor.setMaxLimit(1000L); // 溢出总页数后是否进行处理 (true: 抛异常，false: 不处理) paginationInterceptor.setOverflow(false); interceptor.addInnerInterceptor(paginationInterceptor); return interceptor; } // 可选：SQL 性能分析插件 (开发环境) @Bean @Profile({"dev", "test"}) public MybatisPlusInterceptor performanceInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PerformanceInterceptor()); return interceptor; } } // 2. 基础分页查询 public void basicPagination() { // 第 1 页，每页 10 条 Page page = new Page<>(1, 10); Page result = userService.page(page, new QueryWrapper() .eq("status", 1) .orderByDesc("create_time") ); // 获取数据 List records = result.getRecords(); // 获取分页信息 long current = result.getCurrent(); // 当前页 long size = result.getSize(); // 每页大小 long total = result.getTotal(); // 总记录数 long pages = result.getPages(); // 总页数 boolean hasPrevious = result.hasPrevious(); boolean hasNext = result.hasNext(); } // 3. 无 count 查询 (优化性能) public void paginationWithoutCount() { Page page = new Page<>(1, 10, false); // 第三个参数：是否 count // 不执行 count 查询，适用于不需要总页数的场景 Page result = userService.page(page); } // 4. 自定义 count 查询 public void customCount() { Page page = new Page<>(1, 10); // 使用自定义 count 查询 (复杂场景) Page result = userMapper.selectPage(page, new QueryWrapper() .eq("status", 1) ); } // 5. 多表联查分页 @Mapper public interface UserOrderMapper extends BaseMapper { // XML 方式 @Select("SELECT u.*, o.order_no, o.amount " + "FROM user u " + "LEFT JOIN orders o ON u.id = o.user_id " + "${ew.customSqlSegment}") IPage selectUserOrders(IPage page, @Param("ew") Wrapper wrapper); } // 使用 public void joinTablePagination() { Page page = new Page<>(1, 10); Page result = userOrderMapper.selectUserOrders(page, new QueryWrapper() .eq("u.status", 1) .ge("o.amount", 100) ); } // 6. 使用 PageHelper 兼容 public void pageHelperStyle() { // MyBatis-Plus 也支持 PageHelper 风格 PageHelper.startPage(1, 10); List users = userMapper.selectList(null); Page page = (Page) users; System.out.println("总数：" + page.getTotal()); } // 7. 前端传参分页 @RestController @RequestMapping("/users") public class UserController { @Autowired private UserService userService; @GetMapping("/page") public R> getPage( @RequestParam(defaultValue = "1") long current, @RequestParam(defaultValue = "10") long size, @RequestParam(required = false) String name, @RequestParam(required = false) Integer age) { Page page = new Page<>(current, size); Page result = userService.page(page, Wrappers.lambdaQuery() .like(StringUtils.isNotBlank(name), User::getName, name) .eq(age != null, User::getAge, age) .orderByDesc(User::getCreateTime) ); return R.ok(result); } } // 8. 分页优化技巧 public void paginationOptimization() { // 深分页优化：LIMIT 100000, 10 → 效率低 // 优化方案 1：使用 ID 范围 Page page1 = new Page<>(1, 10); Page result1 = userService.page(page1, Wrappers.lambdaQuery() .gt(User::getId, lastMaxId) // 上次查询的最大 ID .orderByAsc(User::getId) ); // 优化方案 2：延迟关联 // SELECT * FROM user // INNER JOIN (SELECT id FROM user LIMIT 100000, 10) t // ON user.id = t.id } ``` ### 17. 多租户数据隔离如何实现？ **代码示例：** ```java // 1. 配置多租户插件 @Configuration public class MybatisPlusConfig { @Autowired private TenantProperties tenantProperties; @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); // 多租户插件 TenantLineInnerInterceptor tenantInterceptor = new TenantLineInnerInterceptor(); tenantInterceptor.setTenantLineHandler(new TenantLineHandler() { @Override public Expression getTenantId() { // 获取当前租户 ID Long tenantId = TenantContext.getCurrentTenantId(); return new LongValue(tenantId); } @Override public boolean ignoreTable(String tableName) { // 某些表不需要租户隔离 return Arrays.asList("sys_user", "sys_tenant") .contains(tableName.toLowerCase()); } @Override public boolean ignoreInsert(Table table) { // 插入时自动填充租户 ID return false; } }); interceptor.addInnerInterceptor(tenantInterceptor); // 分页插件 (多租户 + 分页) interceptor.addInnerInterceptor(new PaginationInnerInterceptor()); return interceptor; } } // 2. 实体类添加租户字段 @TableName("product") @Data public class Product { @TableId private Long id; private String name; private BigDecimal price; @TableField("tenant_id") private Long tenantId; // 租户 ID 字段 } // 3. 自动填充租户 ID @Component public class TenantMetaObjectHandler implements MetaObjectHandler { @Override public void insertFill(MetaObject metaObject) { Long tenantId = TenantContext.getCurrentTenantId(); if (tenantId != null) { this.strictInsertFill(metaObject, "tenantId", () -> tenantId, Long.class); } } @Override public void updateFill(MetaObject metaObject) { // 更新时不允许修改租户 ID } } // 4. 租户上下文 @Component public class TenantContext { private static final ThreadLocal TENANT_ID = new InheritableThreadLocal<>(); public static void setCurrentTenantId(Long tenantId) { TENANT_ID.set(tenantId); } public static Long getCurrentTenantId() { return TENANT_ID.get(); } public static void clear() { TENANT_ID.remove(); } } // 5. Web 请求拦截器 @Component public class TenantInterceptor implements HandlerInterceptor { @Override public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) { // 从 Header 或 Token 中获取租户 ID String tenantId = request.getHeader("X-Tenant-ID"); if (StringUtils.isNotBlank(tenantId)) { TenantContext.setCurrentTenantId(Long.valueOf(tenantId)); } return true; } @Override public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) { TenantContext.clear(); } } // 6. SQL 效果 // 查询自动添加租户条件 // SELECT * FROM product WHERE tenant_id = 1 AND ... // 插入自动填充租户 ID // INSERT INTO product (name, price, tenant_id) VALUES (?, ?, 1) // 更新/删除自动添加租户条件 // UPDATE product SET name = ? WHERE id = ? AND tenant_id = 1 // DELETE FROM product WHERE id = ? AND tenant_id = 1 ``` ### 18. 数据权限如何实现？ **代码示例：** ```java // 1. 配置数据权限插件 @Configuration public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); // 数据权限插件 interceptor.addInnerInterceptor(new DataPermissionInterceptor( new DataPermissionHandler() { @Override public Expression getSqlSegment(Expression where, Table table, MappedStatement ms) { // 获取当前用户的数据权限 DataScope dataScope = getDataScope(); if (dataScope == null) { return where; // 无限制 } // 根据权限类型生成 SQL switch (dataScope.getType()) { case ALL: return where; // 全部数据 case DEPT: // 本部门及子部门 return buildDeptSql(where, dataScope.getDeptIds()); case DEPT_AND_CHILD: // 本部门 return buildDeptOnlySql(where, dataScope.getDeptId()); case SELF: // 仅本人 return buildSelfSql(where, dataScope.getUserId()); case CUSTOM: // 自定义 return buildCustomSql(where, dataScope.getDeptIds()); default: return where; } } } )); return interceptor; } private Expression buildDeptSql(Expression where, List deptIds) { // dept_id IN (1, 2, 3, ...) InExpression inExpr = new InExpression(); inExpr.setLeftExpression(new Column("dept_id")); inExpr.setRightItemsList(new LongValueList(deptIds)); if (where == null) { return inExpr; } return new AndExpression(where, inExpr); } private Expression buildSelfSql(Expression where, Long userId) { // create_by = ? EqualsTo equals = new EqualsTo(); equals.setLeftExpression(new Column("create_by")); equals.setRightExpression(new LongValue(userId)); if (where == null) { return equals; } return new AndExpression(where, equals); } } // 2. 数据权限注解 @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) public @interface DataScope { // 部门别名 String deptAlias() default ""; // 用户别名 String userAlias() default ""; } // 3. 使用注解 @Service public class UserService { // 仅本人数据 @DataScope(userAlias = "u") public List selectMyUsers() { return userMapper.selectList(null); // SQL: SELECT * FROM user u WHERE u.create_by = 当前用户 ID } // 本部门数据 @DataScope(deptAlias = "d") public List selectDeptUsers() { return userMapper.selectList(null); // SQL: SELECT * FROM user d WHERE d.dept_id IN (本部门及子部门) } // 全部数据 (管理员) public List selectAllUsers() { return userMapper.selectList(null); // SQL: SELECT * FROM user (无限制) } } // 4. 数据权限类型枚举 public enum DataScopeType { ALL, // 全部数据权限 DEPT_AND_CHILD, // 本部门及以下 DEPT_ONLY, // 仅本部门 SELF, // 仅本人 CUSTOM // 自定义 } // 5. 用户数据权限模型 @Data public class SysUser { private Long id; private String username; private Long deptId; private DataScopeType dataScopeType; private List customDeptIds; // 自定义部门 } @Data public class SysDept { private Long id; private Long parentId; private String name; private String ancestors; // 祖级列表 (用于查询子部门) } ``` ### 19. 字段加密如何实现？ **代码示例：** ```java // 1. 字段加密注解 @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface ApiEncrypt { // 加密算法 EncryptType type() default EncryptType.AES; // 密钥 (可选，使用默认密钥) String key() default ""; } public enum EncryptType { AES, // AES 加密 MD5, // MD5 摘要 BASE64, // Base64 编码 CUSTOM // 自定义 } // 2. 实体类使用 @TableName("user") @Data public class User { @TableId private Long id; private String name; @ApiEncrypt(type = EncryptType.AES) // 手机号加密存储 private String phone; @ApiEncrypt(type = EncryptType.AES) // 身份证加密存储 private String idCard; @ApiEncrypt(type = EncryptType.MD5) // 密码 MD5 private String password; } // 3. 加密类型处理器 public class EncryptTypeHandler implements TypeHandler { @Override public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException { if (parameter != null) { String encrypted = EncryptUtil.encrypt(parameter); ps.setString(i, encrypted); } else { ps.setNull(i, jdbcType.TYPE_CODE); } } @Override public String getResult(ResultSet rs, String columnName) throws SQLException { String encrypted = rs.getString(columnName); if (encrypted != null) { return EncryptUtil.decrypt(encrypted); } return null; } // ... 其他方法 } // 4. 自动识别加密字段 @Component public class EncryptMetaObjectHandler implements MetaObjectHandler { @Override public void insertFill(MetaObject metaObject) { // 扫描字段，自动加密 Class clazz = metaObject.getOriginalObject().getClass(); for (Field field : clazz.getDeclaredFields()) { if (field.isAnnotationPresent(ApiEncrypt.class)) { ApiEncrypt annotation = field.getAnnotation(ApiEncrypt.class); field.setAccessible(true); try { Object value = field.get(metaObject.getOriginalObject()); if (value instanceof String) { String encrypted = EncryptUtil.encrypt( (String) value, annotation.type() ); setFieldValByName(field.getName(), encrypted, metaObject); } } catch (IllegalAccessException e) { throw new RuntimeException("加密失败", e); } } } } @Override public void updateFill(MetaObject metaObject) { // 类似处理 } } // 5. 加密工具类 @Component public class EncryptUtil { @Value("${encrypt.aes.key:default-key-123456}") private String aesKey; public static String encrypt(String plain, EncryptType type) { switch (type) { case AES: return aesEncrypt(plain); case MD5: return DigestUtils.md5Hex(plain); case BASE64: return Base64.getEncoder().encodeToString(plain.getBytes()); default: return plain; } } public static String decrypt(String encrypted, EncryptType type) { switch (type) { case AES: return aesDecrypt(encrypted); case MD5: throw new UnsupportedOperationException("MD5 不可逆"); case BASE64: return new String(Base64.getDecoder().decode(encrypted)); default: return encrypted; } } private static String aesEncrypt(String plain) { // AES 加密实现 } private static String aesDecrypt(String encrypted) { // AES 解密实现 } } ``` ### 20. 代码生成器的使用？ **代码示例：** ```java // 1. 添加依赖 // pom.xml com.baomidou mybatis-plus-generator 3.5.3 org.apache.velocity velocity-engine-core 2.3 // 2. 代码生成器配置 public class CodeGenerator { public static void generate(String... tableNames) { // 快速构建 FastAutoGenerator.create( "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8", "root", "password" ) .globalConfig(builder -> { builder.author("your-name") // 作者 .outputDir("src/main/java") // 输出目录 .commentDate("yyyy-MM-dd") // 注释日期 .enableSwagger() // 开启 Swagger .disableOpenDir(); // 不打开输出目录 }) .packageConfig(builder -> { builder.parent("com.example") // 父包名 .moduleName("system") // 模块名 .entity("entity") // Entity 包 .service("service") // Service 包 .serviceImpl("service.impl") // ServiceImpl 包 .mapper("mapper") // Mapper 包 .xml("mapper.xml") // XML 包 .controller("controller") // Controller 包 .pathInfo(Collections.singletonMap( PathInfo.xml, "src/main/resources/mapper")); }) .templateConfig(builder -> { builder.disable(TemplateType.SERVICE) // 禁用 Service .disable(TemplateType.SERVICEIMPL); // 禁用 ServiceImpl }) .strategyConfig(builder -> { builder.addInclude(tableNames) // 指定表 .addTablePrefix("t_", "sys_") // 表名前缀 .entityBuilder() .enableLombok() // Lombok .enableTableFieldAnnotation() // 字段注解 .addIgnoreColumns("create_time", // 忽略字段 "update_time") .addTableFills(new Column("create_time", FieldFill.INSERT)) .addTableFills(new Column("update_time", FieldFill.INSERT_UPDATE)) .formatFileName("%sEntity") // 文件名格式 .mapperBuilder() .enableMapperAnnotation() // Mapper 注解 .formatMapperFileName("%sMapper") .formatXmlFileName("%sMapper") .serviceBuilder() .formatServiceFileName("%sService") .formatServiceImplFileName("%sServiceImpl") .controllerBuilder() .enableRestStyle() // REST 风格 .formatFileName("%sController"); }) .templateEngine(new VelocityTemplateEngine()) // 使用 Velocity .execute(); } public static void main(String[] args) { generate("sys_user", "sys_role", "sys_menu"); } } // 3. 自定义模板 // resources/templates/entity.java.vm package ${package.Entity}; import com.baomidou.mybatisplus.annotation.*; import lombok.Data; import lombok.EqualsAndHashCode; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import java.io.Serializable; import java.time.LocalDateTime; /** * ${table.comment!} * * @author ${author} * @since ${date} */ @Data @EqualsAndHashCode(callSuper = false) @TableName("${table.name}") @ApiModel(value="${entity}对象", description="${table.comment!}") public class ${entity} implements Serializable { private static final long serialVersionUID = 1L; #foreach($column in $table.fields) #if($column.keyFlag) @TableId(value = "${column.annotationColumnName}", type = IdType.${column.keyType}) #elseif($column.logicDeleteField) @TableLogic #endif @ApiModelProperty("${column.comment}") #if($column.columnType.contains("LocalDateTime")) private LocalDateTime ${column.propertyName}; #elseif($column.columnType.contains("BigDecimal")) private BigDecimal ${column.propertyName}; #else private ${column.type} ${column.propertyName}; #endif #end } // 4. 生成结果 /* src/main/java/ ├── com/example/system/ │ ├── entity/ │ │ └── SysUserEntity.java │ ├── mapper/ │ │ ├── SysUserMapper.java │ │ └── xml/ │ │ └── SysUserMapper.xml │ ├── service/ │ │ ├── SysUserService.java │ │ └── impl/ │ │ └── SysUserServiceImpl.java │ └── controller/ │ └── SysUserController.java */ ``` --- ## 框架对比与选型 ### 21. MyBatis vs MyBatis-Plus vs JPA | 特性 | MyBatis | MyBatis-Plus | Spring Data JPA | | --------------- | ---------------- | ------------------ | --------------- | | **学习成本** | 中 | 低 | 中 | | **灵活性** | 高 | 中高 | 低 | | **SQL 控制** | 完全控制 | 可控制 | 自动生成 | | **CRUD 代码量** | 多 | 极少 | 极少 | | **动态 SQL** | 优秀 | 优秀 | 较弱 | | **复杂查询** | 优秀 | 优秀 | 一般 | | **分页** | 需插件 | 内置 | 内置 | | **多租户** | 需自实现 | 内置 | 需自实现 | | **代码生成** | 需第三方 | 内置 | 需第三方 | | **适用场景** | 复杂 SQL、高定制 | 快速开发、企业应用 | 领域模型、DDD | ### 22. 技术选型建议 ``` 项目类型推荐框架 ───────────────────────────────────── 传统企业应用/后台管理 MyBatis-Plus 复杂金融/报表系统 MyBatis 领域驱动设计 (DDD) Spring Data JPA 微服务 (多租户) MyBatis-Plus 高并发/性能敏感 MyBatis (精细优化) 快速原型/MVP MyBatis-Plus 遗留系统维护保持原有框架 ``` --- ## 场景实战与代码示例 ### 23. 复杂查询场景：多条件动态搜索 ```java // 场景：用户搜索，支持 10+ 个可选条件 @Data public class UserSearchCondition { private String keyword; // 关键词 (姓名/手机/邮箱) private Integer status; // 状态 private Integer minAge; private Integer maxAge; private List deptIds; // 部门 private LocalDateTime startTime; // 注册时间范围 private LocalDateTime endTime; private List roles; // 角色 private String orderBy; // 排序字段 private String orderDirection; // 排序方向 } @Service public class UserService { public IPage searchUsers(UserSearchCondition condition, Page page) { return userMapper.selectUserPage(page, buildWrapper(condition)); } private LambdaQueryWrapper buildWrapper(UserSearchCondition c) { return Wrappers.lambdaQuery() // 关键词模糊搜索 .and(StringUtils.isNotBlank(c.getKeyword()), w -> w .like(User::getName, c.getKeyword()) .or() .like(User::getPhone, c.getKeyword()) .or() .like(User::getEmail, c.getKeyword()) ) // 状态精确匹配 .eq(c.getStatus() != null, User::getStatus, c.getStatus()) // 年龄范围 .ge(c.getMinAge() != null, User::getAge, c.getMinAge()) .le(c.getMaxAge() != null, User::getAge, c.getMaxAge()) // 部门列表 .in(c.getDeptIds() != null && !c.getDeptIds().isEmpty(), User::getDeptId, c.getDeptIds()) // 注册时间范围 .ge(c.getStartTime() != null, User::getCreateTime, c.getStartTime()) .le(c.getEndTime() != null, User::getCreateTime, c.getEndTime()) // 角色 (关联表) .apply(c.getRoles() != null && !c.getRoles().isEmpty(), "id IN (SELECT user_id FROM user_role WHERE role_id IN ({0}))", String.join(",", c.getRoles().stream() .map(String::valueOf).toList()) ) // 动态排序 .orderBy(StringUtils.isNotBlank(c.getOrderBy()), "asc".equalsIgnoreCase(c.getOrderDirection()), c.getOrderBy()); } } // Mapper XML (复杂关联查询) ``` ### 24. 批量操作场景：导入 Excel 数据 ```java @Service public class UserImportService { @Autowired private UserService userService; @Autowired private UserMapper userMapper; @Transactional(rollbackFor = Exception.class) public ImportResult importUsers(MultipartFile file) { List importList = parseExcel(file); ImportResult result = new ImportResult(); List errors = new ArrayList<>(); // 1. 数据校验 for (int i = 0; i < importList.size(); i++) { ImportUserDTO dto = importList.get(i); List dtoErrors = validate(dto, i + 1); if (!dtoErrors.isEmpty()) { errors.addAll(dtoErrors); } } if (!errors.isEmpty()) { result.setSuccess(false); result.setErrors(errors); return result; } // 2. 检查重复 (手机号、邮箱) List phones = importList.stream() .map(ImportUserDTO::getPhone).toList(); List emails = importList.stream() .map(ImportUserDTO::getEmail).toList(); List existing = userMapper.selectList( Wrappers.lambdaQuery() .in(User::getPhone, phones) .or() .in(User::getEmail, emails) ); if (!existing.isEmpty()) { result.setSuccess(false); result.setErrors(existing.stream() .map(u -> "重复数据：" + u.getName() + "(" + u.getPhone() + ")") .toList()); return result; } // 3. 批量插入 (分批处理，每批 1000 条) int batchSize = 1000; List usersToInsert = importList.stream() .map(this::convertToEntity) .toList(); for (int i = 0; i < usersToInsert.size(); i += batchSize) { int end = Math.min(i + batchSize, usersToInsert.size()); List batch = usersToInsert.subList(i, end); // 使用 BATCH 模式 try (SqlSession session = sqlSessionFactory.openSession( ExecutorType.BATCH, false)) { UserMapper mapper = session.getMapper(UserMapper.class); batch.forEach(mapper::insert); session.commit(); } } result.setSuccess(true); result.setTotal(importList.size()); return result; } private List validate(ImportUserDTO dto, int rowNum) { List errors = new ArrayList<>(); if (StringUtils.isBlank(dto.getName())) { errors.add("第" + rowNum + "行：姓名不能为空"); } if (!StringUtils.isNumeric(dto.getPhone())) { errors.add("第" + rowNum + "行：手机号格式错误"); } if (!EmailValidator.isValid(dto.getEmail())) { errors.add("第" + rowNum + "行：邮箱格式错误"); } if (dto.getAge() != null && (dto.getAge() < 1 || dto.getAge() > 150)) { errors.add("第" + rowNum + "行：年龄超出范围"); } return errors; } private User convertToEntity(ImportUserDTO dto) { User user = new User(); user.setName(dto.getName()); user.setPhone(dto.getPhone()); user.setEmail(dto.getEmail()); user.setAge(dto.getAge()); user.setStatus(1); // 其他字段自动填充 return user; } } ``` ### 25. 审计日志场景：记录数据变更 ```java // 1. 审计日志注解 @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) public @interface AuditLog { String module(); // 模块 String operation(); // 操作 String bizKey() default ""; // 业务 key (SpEL) } // 2. 审计日志切面 @Aspect @Component public class AuditLogAspect { @Autowired private AuditLogMapper auditLogMapper; @Around("@annotation(auditLog)") public Object around(ProceedingJoinPoint pjp, AuditLog auditLog) throws Throwable { // 记录操作前 AuditLogEntity log = new AuditLogEntity(); log.setModule(auditLog.module()); log.setOperation(auditLog.operation()); log.setRequestTime(LocalDateTime.now()); log.setRequestParams(JSON.toJSONString(pjp.getArgs())); log.setOperator(getCurrentUserId()); Object result = null; try { result = pjp.proceed(); log.setStatus("SUCCESS"); log.setResponseData(JSON.toJSONString(result)); // 提取业务 key if (StringUtils.isNotBlank(auditLog.bizKey())) { String bizKeyValue = parseSpEL(auditLog.bizKey(), pjp, result); log.setBizKey(bizKeyValue); } } catch (Exception e) { log.setStatus("FAIL"); log.setErrorMsg(e.getMessage()); throw e; } finally { log.setResponseTime(LocalDateTime.now()); log.setDuration(System.currentTimeMillis() - log.getRequestTime().atZone(ZoneId.systemDefault()) .toInstant().toEpochMilli()); // 异步保存日志 saveLogAsync(log); } return result; } @Async public void saveLogAsync(AuditLogEntity log) { auditLogMapper.insert(log); } } // 3. 使用示例 @Service public class UserService { @AuditLog(module = "用户管理", operation = "创建用户", bizKey = "#result.id") public User createUser(UserDTO dto) { User user = convert(dto); userMapper.insert(user); return user; } @AuditLog(module = "用户管理", operation = "更新用户", bizKey = "#user.id") public boolean updateUser(@Param("user") User user) { return userMapper.updateById(user) > 0; } @AuditLog(module = "用户管理", operation = "删除用户", bizKey = "#id") public boolean deleteUser(Long id) { return userMapper.deleteById(id) > 0; } } // 4. 数据变更对比 (记录变更前后的值) public class DataChangeLogger { public void logChange(String entityName, T oldEntity, T newEntity) { List changes = compareFields(oldEntity, newEntity); if (!changes.isEmpty()) { DataChangeLog log = new DataChangeLog(); log.setEntityName(entityName); log.setEntityId(extractId(newEntity)); log.setChanges(JSON.toJSONString(changes)); log.setOperator(getCurrentUserId()); log.setChangeTime(LocalDateTime.now()); dataChangeLogMapper.insert(log); } } private List compareFields(T oldEntity, T newEntity) { List changes = new ArrayList<>(); Field[] fields = oldEntity.getClass().getDeclaredFields(); for (Field field : fields) { field.setAccessible(true); try { Object oldValue = field.get(oldEntity); Object newValue = field.get(newEntity); if (!Objects.equals(oldValue, newValue)) { FieldChange change = new FieldChange(); change.setFieldName(field.getName()); change.setOldValue(String.valueOf(oldValue)); change.setNewValue(String.valueOf(newValue)); changes.add(change); } } catch (IllegalAccessException e) { // ignore } } return changes; } } // 5. 使用数据变更日志 @Transactional public boolean updateUser(User user) { // 查询原数据 User oldUser = userMapper.selectById(user.getId()); // 执行更新 boolean success = userMapper.updateById(user) > 0; if (success) { // 记录变更 dataChangeLogger.logChange("User", oldUser, user); } return success; } ``` ### 26. 软删除 + 数据恢复场景 ```java @Service public class DataRecoveryService { @Autowired private UserMapper userMapper; // 1. 查询已删除数据 (绕过逻辑删除) public List selectDeleted() { return userMapper.selectList( Wrappers.lambdaQuery() .eq(User::getDeleted, 1) // 直接查询已删除 ); } // 2. 恢复数据 @Transactional public boolean recover(Long id) { User user = selectDeletedById(id); // 自定义方法，绕过逻辑删除 if (user == null) { throw new BusinessException("记录不存在"); } user.setDeleted(0); user.setRecoveryTime(LocalDateTime.now()); user.setRecoveryBy(getCurrentUserId()); // 直接更新，绕过逻辑删除 return userMapper.update(user, Wrappers.lambdaUpdate() .eq(User::getId, id) ) > 0; } // 3. 批量恢复 @Transactional public int batchRecover(List ids) { AtomicInteger count = new AtomicInteger(); ids.forEach(id -> { if (recover(id)) { count.incrementAndGet(); } }); return count.get(); } // 4. 数据归档 (删除超过一定时间的数据) @Scheduled(cron = "0 0 2 * * ?") // 每天凌晨 2 点 @Transactional public void archiveOldData() { LocalDateTime threshold = LocalDateTime.now().minusMonths(6); // 查询 6 个月前删除的数据 List oldDeleted = userMapper.selectList( Wrappers.lambdaQuery() .eq(User::getDeleted, 1) .le(User::getDeleteTime, threshold) ); if (!oldDeleted.isEmpty()) { // 导出到归档表 archiveToHistoryTable(oldDeleted); // 物理删除 List ids = oldDeleted.stream() .map(User::getId) .toList(); // 使用 SQL 直接删除 (绕过逻辑删除) userMapper.deleteBatchIds(ids); log.info("归档并删除 {} 条历史数据", oldDeleted.size()); } } private void archiveToHistoryTable(List users) { // 插入到 user_history 表 for (User user : users) { UserHistory history = new UserHistory(); BeanUtils.copyProperties(user, history); history.setArchiveTime(LocalDateTime.now()); userHistoryMapper.insert(history); } } } // Mapper 中绕过逻辑删除的方法 @Mapper public interface UserMapper extends BaseMapper { // 查询已删除 @Select("SELECT * FROM user WHERE deleted = 1 AND id = #{id}") User selectDeletedById(Long id); // 物理删除 @Delete("DELETE FROM user WHERE id = #{id}") int physicalDelete(Long id); // 批量物理删除 @Delete("") int physicalDeleteBatch(List ids); } ``` ### 27. 读写分离场景 ```java // 1. 多数据源配置 @Configuration public class DataSourceConfig { @Bean @ConfigurationProperties("spring.datasource.master") public DataSource masterDataSource() { return DataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("spring.datasource.slave") public DataSource slaveDataSource() { return DataSourceBuilder.create().build(); } @Bean public DataSource routingDataSource() { Map targetDataSources = new HashMap<>(); targetDataSources.put("master", masterDataSource()); targetDataSources.put("slave", slaveDataSource()); RoutingDataSource routingDataSource = new RoutingDataSource(); routingDataSource.setDefaultTargetDataSource(masterDataSource()); routingDataSource.setTargetDataSources(targetDataSources); return routingDataSource; } @Bean public SqlSessionFactory sqlSessionFactory(DataSource routingDataSource) throws Exception { SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean(); factoryBean.setDataSource(routingDataSource); return factoryBean.getObject(); } } // 2. 数据源切换注解 @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) public @interface DataSource { String value() default "master"; } // 3. 数据源切面 @Aspect @Order(1) // 优先于事务切面 public class DataSourceAspect { @Around("@annotation(dataSource)") public Object around(ProceedingJoinPoint pjp, DataSource dataSource) throws Throwable { String dsKey = dataSource.value(); DataSourceContextHolder.set(dsKey); try { return pjp.proceed(); } finally { DataSourceContextHolder.clear(); } } } // 4. 数据源上下文 public class DataSourceContextHolder { private static final ThreadLocal CONTEXT = new InheritableThreadLocal<>(); public static void set(String dsKey) { CONTEXT.set(dsKey); } public static String get() { return CONTEXT.get(); } public static void clear() { CONTEXT.remove(); } } // 5. 路由数据源 public class RoutingDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DataSourceContextHolder.get(); } } // 6. 使用示例 @Service public class UserService { // 写操作 → 主库 @DataSource("master") @Transactional public void createUser(User user) { userMapper.insert(user); } // 读操作 → 从库 @DataSource("slave") public User getUserById(Long id) { return userMapper.selectById(id); } // 读操作 → 从库 @DataSource("slave") public List listUsers() { return userMapper.selectList(null); } // 复杂查询 → 从库 @DataSource("slave") public IPage searchUsers(UserSearchCondition condition) { return userMapper.selectUserPage(new Page<>(), buildWrapper(condition)); } } // 7. MyBatis-Plus 多数据源插件 @Configuration public class DynamicDataSourceConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); // 动态数据源插件 interceptor.addInnerInterceptor(new DynamicDatasourceInterceptor()); return interceptor; } } ``` --- ## 性能优化与最佳实践 ### 28. N+1 查询问题及解决方案 ```java // 问题场景：查询用户列表 + 每个用户的订单 // 错误做法 public List getUsersWithOrders() { List users = userMapper.selectList(null); // 1 次查询 List result = new ArrayList<>(); for (User user : users) { UserWithOrdersVO vo = new UserWithOrdersVO(); vo.setUser(user); // N 次查询 (N+1 问题) List orders = orderMapper.selectList( Wrappers.lambdaQuery() .eq(Order::getUserId, user.getId()) ); vo.setOrders(orders); result.add(vo); } return result; } // SQL 执行：1 + N 次 // 解决方案 1：使用 IN 查询 public List getUsersWithOrders() { List users = userMapper.selectList(null); List userIds = users.stream() .map(User::getId) .toList(); // 1 次查询获取所有订单 List orders = orderMapper.selectList( Wrappers.lambdaQuery() .in(Order::getUserId, userIds) ); // 内存分组 Map> orderMap = orders.stream() .collect(Collectors.groupingBy(Order::getUserId)); // 组装结果 return users.stream().map(user -> { UserWithOrdersVO vo = new UserWithOrdersVO(); vo.setUser(user); vo.setOrders(orderMap.getOrDefault(user.getId(), new ArrayList<>())); return vo; }).toList(); } // SQL 执行：2 次 // 解决方案 2：使用 JOIN 查询 @Mapper public interface UserOrderMapper extends BaseMapper { @Select("SELECT u.*, o.id as order_id, o.order_no, o.amount " + "FROM user u " + "LEFT JOIN orders o ON u.id = o.user_id") List selectUserOrders(); } // 解决方案 3：使用 MyBatis 延迟加载 // 见前面 resultMap 示例 ``` ### 29. 索引优化建议 ```sql -- 1. 常见查询场景的索引设计 -- 场景：按状态 + 时间范围查询 CREATE INDEX idx_status_create_time ON user (status, create_time); -- WHERE status = ? AND create_time BETWEEN ? AND ? -- 场景：按手机号查询 (唯一) CREATE UNIQUE INDEX idx_phone ON user (phone); -- 场景：模糊搜索姓名 CREATE INDEX idx_name ON user (name); -- LIKE '张%' (左前缀匹配) -- 注意：LIKE '%张' 无法使用索引 -- 场景：多条件组合查询 CREATE INDEX idx_dept_status_age ON user (dept_id, status, age); -- WHERE dept_id = ? AND status = ? AND age > ? -- 遵循最左前缀原则 -- 场景：排序 + 分页 CREATE INDEX idx_create_time_id ON user (create_time DESC, id); -- ORDER BY create_time DESC LIMIT ?, ? -- 场景：覆盖索引 (避免回表) CREATE INDEX idx_status_name ON user (status, name, id); -- SELECT id, name FROM user WHERE status = ? -- 索引包含所有查询字段，无需回表 -- 2. 避免索引失效的场景 -- ❌ 对索引列使用函数 SELECT * FROM user WHERE DATE(create_time) = '2024-01-01'; -- ✅ 改为范围查询 SELECT * FROM user WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'; -- ❌ 隐式类型转换 SELECT * FROM user WHERE phone = 13800138000; -- phone 是 VARCHAR -- ✅ 使用字符串 SELECT * FROM user WHERE phone = '13800138000'; -- ❌ LIKE 左通配符 SELECT * FROM user WHERE name LIKE '%张%'; -- ✅ 使用全文索引或搜索引擎 -- ❌ OR 条件包含未索引列 SELECT * FROM user WHERE status = 1 OR name = '张三'; -- ✅ 改为 UNION SELECT * FROM user WHERE status = 1 UNION SELECT * FROM user WHERE name = '张三'; -- 3. 使用 EXPLAIN 分析 SQL EXPLAIN SELECT * FROM user WHERE status = 1 AND create_time > '2024-01-01' ORDER BY create_time DESC LIMIT 10; -- 关注字段： -- type: system > const > eq_ref > ref > range > index > ALL -- key: 实际使用的索引 -- rows: 扫描行数 -- Extra: Using index (覆盖索引), Using filesort (文件排序), Using temporary (临时表) ``` ### 30. 最佳实践清单 ```markdown ## MyBatis/MyBatis-Plus 最佳实践 ### 代码规范 - [ ] 使用 LambdaQueryWrapper 避免字段名硬编码 - [ ] 实体类使用 Lombok 减少样板代码 - [ ] Service 层统一返回 Result/R 包装类 - [ ] Controller 层不做业务逻辑 - [ ] Mapper 接口与 XML 保持一致命名 ### 性能优化 - [ ] 批量操作使用 BATCH 模式 - [ ] 列表查询避免 SELECT * - [ ] 分页查询注意深分页优化 - [ ] 大字段延迟加载或单独查询 - [ ] 合理使用缓存 (一级/二级/Redis) ### 安全建议 - [ ] 使用 #{} 防止 SQL 注入 - [ ] 敏感字段加密存储 - [ ] 逻辑删除代替物理删除 - [ ] 分页限制最大页数 - [ ] SQL 审计记录慢查询 ### 可维护性 - [ ] 复杂 SQL 写在 XML 中 - [ ] 使用代码生成器减少重复工作 - [ ] 统一异常处理 - [ ] 完善的日志记录 - [ ] 数据库变更使用迁移脚本 ### 测试 - [ ] Mapper 层单元测试 - [ ] Service 层集成测试 - [ ] 使用 H2 内存数据库测试 - [ ] 测试数据工厂模式 - [ ] 事务回滚测试 ``` --- **文档版本：** 1.0 **最后更新：** 2026-06-08 **适用对象：** Java 后端开发工程师面试准备 **技术栈：** MyBatis 3.5+, MyBatis-Plus 3.5+, MySQL 8.0+