Springboot 注解方式下mybatis分页，动态sql

背景：在开发restful API 接口时，考虑结果太多，引入分页。有时筛选条件有好几个，但访问的是同一个数据库，筛选的有无影响结果，不想为每个条件开发单独接口，此时动态sql派上用场。本帖子涉及分页（使用插件PageHelper），动态sql

1. spring的配置文件中配上PageHelper相关属性，如下

#pagehelper分页插件
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql
pagehelper.row-bounds-with-count=true
pageSizeZero=true

2. 加入依赖 pom

<!-- 分页插件 -->
<dependency>
   <groupId>com.github.pagehelper</groupId>
   <artifactId>pagehelper-spring-boot-starter</artifactId>
   <version>1.2.13</version>
</dependency>

3.单独编写 mapper 接口，和其他mapper分离，后面需要编写实现类。

// LogMetaAndLogDetailListRequest 请求参数，自行设计修改
@Mapper
@Repository
public interface PageMapper {

  @Select("<script>"
      + "SELECT * FROM dte_log_meta"
      + "<where>"
      + "<if test=request!=null and request.type != null and request.type != &quot;&quot;> AND type=#{request.type}</if>"
      + "<if test=request!=null and request.clusterId > 0> AND clusterId=#{request.clusterId}</if>"
      + "<if test=request!=null and request.logMetaId > 0> AND id=#{request.logMetaId}</if>"
      + "<if test=request!=null and request.operator != null and request.operator != &quot;&quot;> AND operator=#{request.operator}</if>"
      + "<if test=request!=null and request.frequency != null and request.frequency != &quot;&quot;> AND frequency=#{request.frequency}</if>"
      + "</where>"
      + "</script>")
  List<LogMeta> selectAllWithPageFromLogMeta(
      @Param("request") LogMetaAndLogDetailListRequest request);

}

动态sql必须包含在<script>标签下，if test用于测试条件，判空串时需使用 &quot;&quot; 注意and大小写

3. 编写类实现mapper接口

@Service
public class PageMapperService {

  @Autowired private PageMapper pageMapper;

  public List<LogMeta> selectAllWithPageFromLogMeta(LogMetaAndLogDetailListRequest request) {
    PageHelper.startPage(request.getPageNum(), request.getPageSize());
    return pageMapper.selectAllWithPageFromLogMeta(request);
  }
}

只需调用 startPage方法，设置页和页大小

4. api接口方法

// LogMeta 数据库相对的 POJO 类
@RestController
@CrossOrigin
@RequestMapping("/api")
public class APIController {
// ...
    @Autowired private PageMapperService pageMapperService;  

    @ApiOperation(value = "数据源及任务信息")
      @ApiImplicitParam(
          name = "request",
          value = "参数",
          required = true,
          dataType = "LogMetaAndLogDetailListRequest")
      @RequestMapping(value = "/dte/list/logMetaAndLogDetails", method = equestMethod.POST, produces = {"application/json;charset=UTF-8"})
      public ResponseEntity<LogMetaAndLogDetailListResponse> listLogMetaAndLogDetails(
          @RequestBody LogMetaAndLogDetailListRequest request) {
         List<LogMeta> logMetaList = pageMapperService.selectAllWithPageFromLogMeta(request);

    }

// ...
}

至此，大功告成。实际中，可能会因为各自使用的 springboot，mybatis版本不同有差异

参考文献