学习笔记

主题

SpringBoot之MyBatis ​

更新: 2/10/2025 字数: 0 字 时长: 0 分钟

MyBatis XML写法 ​

批量操作优化 ​

xml

<insert id="batchInsert" parameterType="java.util.List">
  INSERT INTO user (username, email, create_time) VALUES
  <foreach collection="list" item="item" separator=",">
    (#{item.username}, #{item.email}, #{item.createTime})
  </foreach>
</insert>
xml

<update id="batchUpdate" parameterType="java.util.List">
  <foreach collection="list" item="item" separator=";">
    UPDATE user
    SET username = #{item.username}, email = #{item.email}
    WHERE id = #{item.id}
  </foreach>
</update>
xml

<delete id="batchDelete" parameterType="java.util.List">
  DELETE FROM user WHERE id IN
  <foreach collection="list" item="id" open="(" separator="," close=")">
    #{id}
  </foreach>
</delete>

动态SQL ​

xml
<!-- trim标签可以帮助我们优化生成的SQL语句,避免出现多余的AND或OR关键字 -->
<select id="findUsers" resultType="User">
  SELECT * FROM user
  <trim prefix="WHERE" prefixOverrides="AND |OR ">
    <!-- concat模糊查询 -->
    <if test="username != null and username != ''">
      AND username LIKE CONCAT('%', #{username}, '%')
    </if>
    <if test="email != null and email != ''">
      AND email = #{email}
    </if>
    <!-- 开始时间 >= 传入时间的 00:00:00 -->
    <if test="timeStart != null">
      AND <![CDATA[ time_start >= #{condition.timeStart} + INTERVAL '0' HOUR ]]>
    </if>
    <!-- 结束时间 < 传入时间+1的 00:00:00 -->
    <if test="timeEnd != null">
      AND <![CDATA[ time_end < #{condition.timeEnd} + INTERVAL '1' DAY ]]>
    </if>
    <!-- 班级id in查询 -->
    <if test="ids != null and ids.size() > 0">
      AND class_id IN
      <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
    </if>
  </trim>
</select>
xml

<select id="findUsersByCondition" resultType="User">
  SELECT * FROM user
  <where>
    <choose>
      <when test="searchType == 'username'">
        AND username LIKE CONCAT('%', #{keyword}, '%')
      </when>
      <when test="searchType == 'email'">
        AND email LIKE CONCAT('%', #{keyword}, '%')
      </when>
      <otherwise>
        AND (username LIKE CONCAT('%', #{keyword}, '%') OR email LIKE CONCAT('%', #{keyword}, '%'))
      </otherwise>
    </choose>
  </where>
</select>
xml
<!--在插入操作中,我们经常需要获取数据库自动生成的主键。MyBatis提供了<selectKey>标签来实现这一功能。-->
<insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
  <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
    SELECT 2531020
  </selectKey>
  INSERT INTO user (username, email, create_time)
  VALUES (#{username}, #{email}, #{createTime})
</insert>

注解写法 ​

java

@Mapper
public interface UserMapper {
  @Select("SELECT * FROM user")
  List<User> findAll();

  /**
   * 通过id查询用户的全部信息
   * @param id 用户id
   * @return 用户信息模型
   */
  @Select({"select * from user where id = #{id}"})
  @Results(id = "studentMap", value = {
    @Result(column = "id", property = "id", id = true),
    @Result(column = "username", property = "username"),
    @Result(column = "name", property = "name"),
    @Result(property = "roleList", javaType = List.class, column = "id", many = @Many(select = "top.haijunit.sample.mapper.RoleMapper.getRoleListById"))
  })
  UserVO getUserById(@Param("id") Integer id);
}
java

@Mapper
public interface RoleMapper {
  @Select("SELECT * FROM role WHERE roleName = #{roleName}")
  Role findByUsername(String roleName);

  @Select("SELECT * FROM role WHERE id IN (SELECT role_id FROM user_role_con WHERE user_id = #{id})")
  List<Role> getRoleListById(@Param("id") Integer id);
}
java
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.builder.annotation.ProviderContext;

import java.util.Map;

@Mapper
public interface UserMapper extends BaseMapper<User> {

  @SelectProvider(type = SqlProvider.class, method = "findByCondition")
  List<User> findByCondition(Map<String, Object> conditions);

  class SqlProvider {
    public String findByCondition(Map<String, Object> params, ProviderContext context) {
      StringBuilder sql = new StringBuilder("SELECT * FROM user WHERE 1=1");
      if (params.get("username") != null) {
        sql.append(" AND username = #{username}");
      }
      if (params.get("email") != null) {
        sql.append(" AND email = #{email}");
      }
      return sql.toString();
    }
  }
}

Wrapper查询 ​

java

@Service
@RequiredArgsConstructor
public class UserService {
  private final UserMapper userMapper;

  public List<User> findUsers() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("username", "张");
    queryWrapper.lamba().eq(User::getStatus, 1);
    return userMapper.findUsers(wrapper);
  }
}
java

@Mapper
public interface UserMapper {
  @Select("SELECT * FROM user")
  List<User> findUsers(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
}
xml

<select id="findUsers" resultType="User">
  select * from user
  <where>
    <if test="ew.sqlSegment != null ">
      ${ew.sqlSegment}
    </if>
  </where>
</select>

Wrapper 高级写法 ​

java
// 减少数据库连接的创建和销毁开销
// 批量操作可以在一个事务中完成，提高数据一致性
// 数据库可以优化批量操作的执行计划
// 显著减少网络往返次数，提升吞吐量
class UserService extends ServiceImpl<UserMapper, User> {
  public void saveBatch() {
    // ❌ 不推荐
    for (User user : userList) {
      userMapper.insert(user);
    }

    // ✅ 推荐
    userService.saveBatch(userList, 100);  // 每批次处理100条数据
    // ✅ 更优写法：自定义批次大小
    userService.saveBatch(userList, BatchConstants.BATCH_SIZE);
  }
}
java
// EXISTS是基于索引的快速查询，可以使用到索引
// EXISTS在找到第一个匹配项就会停止扫描
// IN子查询需要加载所有数据到内存后再比较
// 当外表数据量大时，EXISTS的性能优势更明显
class UserService extends ServiceImpl<UserMapper, User> {
  public void getUserList() {
    // ❌ 不推荐
    wrapper.inSql("user_id", "select user_id from order where amount > 1000");

    // ✅ 推荐
    wrapper.exists("select 1 from order where order.user_id = user.id and amount > 1000");
    // ✅ 更优写法：使用LambdaQueryWrapper
    wrapper.exists(orderService.lambdaQuery().gt(Order::getAmount, 1000).apply("order.user_id = user.id"));
  }
}
java
// 直接拼接SQL容易导致SQL注入攻击
// 动态SQL可能破坏SQL语义完整性
// 影响SQL语句的可维护性和可读性
// last会绕过MyBatis-Plus的安全检查机制
class UserService extends ServiceImpl<UserMapper, User> {
  public void getUserList() {
    // ❌ 不推荐：SQL注入风险
    wrapper.last("ORDER BY " + sortField + " " + sortOrder);
    // ❌ 不推荐：直接字符串拼接
    wrapper.last("ORDER BY FIELD(status, 'active', 'pending', 'inactive')");

    // ✅ 推荐：使用 Lambda 安全排序
    wrapper.orderBy(true, true, User::getStatus);
    // ✅ 推荐：多字段排序示例
    wrapper.orderByAsc(User::getStatus).orderByDesc(User::getCreateTime);
  }
}
java
// 优雅处理空值，避免无效条件
// 减少代码中的if-else判断
// 提高代码可读性
// 防止生成冗余的SQL条件
class UserService extends ServiceImpl<UserMapper, User> {
  public void getUserList() {
    // ❌ 不推荐
    if (StringUtils.isNotBlank(name)) {
      wrapper.eq("name", name);
    }
    if (age != null) {
      wrapper.eq("age", age);
    }

    // ✅ 推荐
    wrapper.eq(StringUtils.isNotBlank(name), User::getName, name);
    wrapper.eq(Objects.nonNull(age), User::getAge, age);

    // ✅ 更优写法：结合业务场景
    wrapper.eq(StringUtils.isNotBlank(name), User::getName, name);
    wrapper.eq(Objects.nonNull(age), User::getAge, age);
    wrapper.eq(User::getDeleted, false);  // 默认查询未删除记录
    wrapper.orderByDesc(User::getCreateTime);  // 默认按创建时间倒序
  }
}
java
// 业务代码和性能监控代码完全分离
// try-with-resources 即使发生异常，close() 方法也会被调用，确保一定会记录耗时
// 不需要手动管理计时的开始和结束
// 更优雅
class UserService extends ServiceImpl<UserMapper, User> {
  // ❌ 不推荐：简单计时，代码冗余
  public List<User> listUsers(QueryWrapper<User> wrapper) {
    long startTime = System.currentTimeMillis();
    List<User> users = userMapper.selectList(wrapper);
    long endTime = System.currentTimeMillis();
    log.info("查询耗时：{}ms", (endTime - startTime));
    return users;
  }

  // ✅ 推荐：使用 Try-with-resources 自动计时
  public List<User> listUsersWithPerfTrack(QueryWrapper<User> wrapper) {
    try (PerfTracker.TimerContext ignored = PerfTracker.start()) {
      return userMapper.selectList(wrapper);
    }
  }
}
// 性能追踪工具类
@Slf4j
public class PerfTracker {
  private final long startTime;
  private final String methodName;

  private PerfTracker(String methodName) {
    this.startTime = System.currentTimeMillis();
    this.methodName = methodName;
  }

  public static TimerContext start() {
    return new TimerContext(Thread.currentThread().getStackTrace()[2].getMethodName());
  }

  public static class TimerContext implements AutoCloseable {
    private final PerfTracker tracker;

    private TimerContext(String methodName) {
      this.tracker = new PerfTracker(methodName);
    }

    @Override
    public void close() {
      long executeTime = System.currentTimeMillis() - tracker.startTime;
      if (executeTime > 500) {
        log.warn("慢查询告警：方法 {} 耗时 {}ms", tracker.methodName, executeTime);
      }
    }
  }
}
java
// 类型安全
// 避免手动拼接sql，防止sql注入
// 代码可维护性更强，更清晰
class UserService extends ServiceImpl<UserMapper, User> {
  public void updateUser() {
    // ❌ 不推荐：使用 setSql
    userService.lambdaUpdate().setSql("integral = integral + 10").update();

    // ✅ 推荐：使用 setIncrBy
    userService.lambdaUpdate().eq(User::getId, 1L).setIncrBy(User::getIntegral, 10).update();
    // ✅ 推荐：使用 setDecrBy
    userService.lambdaUpdate().eq(User::getId, 1L).setDecrBy(User::getStock, 5).update();
  }
}