Mybatis查询优化

最新推荐文章于 2025-07-10 22:50:18 发布

原创

最新推荐文章于 2025-07-10 22:50:18 发布 · 1.7k 阅读

3 ·

CC 4.0 BY-SA版权

文章讲述了作者如何将一个复杂的Mybatis查询进行优化，原本的查询由于频繁连接数据库导致效率低下，优化后将其拆分为3个独立的查询，显著减少了数据库交互次数，提高了查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

纪念我这一块非常复杂的mybatis查询可以优化成多个逻辑
这样写不仅复杂而且查询效率慢，因为要根据时间去数据库查，没一天都要查一次，连接数据库很多次

<!--获取某天的日报-->
    <select id="getDetails" resultMap="attendDetailMap">
      with leave_chiefs as (
        select count(1), grid_chief_id
        from patrol_grid_chief_attendance where state = 'LEAVE' and date = #{date}
        group by grid_chief_id
        ),
      complete_chiefs as (
          select  count(1), grid_chief_id
          from patrol_patrol_log where state = 'COMPLETE' and date = #{date}
          group by grid_chief_id
      )
       select
          pgc.id as gridChiefId,
          pgc.name as gridChiefName,
          pgc.grid_id as gridId,
          #{date} as date,
          case (select 100 from leave_chiefs lc where lc.grid_chief_id = pgc.id) when 100 then 'LEAVE' else 'ATTEND' end as state,
          case (select 100 from complete_chiefs cc where cc.grid_chief_id = pgc.id) when 100 then 'true' else 'false' end as hasComplete
        from patrol_grid_chief pgc
    </select>

 @Override
    public List<GridChiefAttendLog> getAttendLogs(LocalDate from, LocalDate to) {
   
   
        List<LocalDate> dates = Stream.iterate(from, date -> date.plusDays(1))
                .limit(ChronoUnit.DAYS.between(from, to.plusDays(1)))
                .collect(Collectors.toList());
        List<GridChiefAttendLog> gridChiefAttendLogs = patrolLogDao.getPatrolChiefs();