Springcloud学习笔记33--文件存储信息查询功能(使用mybatis xml 写原生sql,实现分页功能)

1.controller

利用OssListDto实体类接收多个参数，同时利用@RequestParam参数接收单个参数

@Api(tags="bs_file_store")
@RestController
@RequestMapping("/test/bsFileStore")
@Slf4j
public class BsFileStoreController extends JeecgController<BsFileStore, IBsFileStoreService> {
    @Autowired
    private IBsFileStoreService bsFileStoreService;
    
    /**
     * 分页列表查询
     *
     * @param ossListDto
     * @param pageNo
     * @param pageSize
     * @param req
     * @return
     */
    @AutoLog(value = "bs_file_store-分页列表查询")
    @ApiOperation(value="bs_file_store-分页列表查询", notes="bs_file_store-分页列表查询")
    @GetMapping(value = "/list")
    public Result<?> queryOssList(OssListDto ossListDto,
                                   @RequestParam(name="pageNo", defaultValue="1") Integer pageNo,
                                   @RequestParam(name="pageSize", defaultValue="10") Integer pageSize,
                                   HttpServletRequest req) {
return Result.OK(bsFileStoreService.queryOssList(ossListDto,pageNo,pageSize));
    }
}

2.service接口及其实现类

(1)service接口

public interface IBsFileStoreService extends IService<BsFileStore> {

    /**
     * 存储列表
     * @param ossListDto
     * @param pageNo
     * @param pageSize
     * @return
     */
    IPage<OssListDto> queryOssList(OssListDto ossListDto, Integer pageNo, Integer pageSize);


}

(2)实现类

@Service
@DS("multi-datasource1")
@Slf4j
public class BsFileStoreServiceImpl extends ServiceImpl<BsFileStoreMapper, BsFileStore> implements IBsFileStoreService {
    @Autowired
    private BsFileStoreMapper bsFileStoreMapper;private final String LOCAL=StoreTypeEnum.LOCAL.getCode();
    private final String OSS=StoreTypeEnum.OSS.getCode();

    @Override
    public IPage<OssListDto> queryOssList(OssListDto ossListDto, Integer pageNo, Integer pageSize) {
        Page<OssListDto> page=new Page<>(pageNo,pageSize);
        List<OssListDto> ossList=bsFileStoreMapper.queryOssList(page,ossListDto.getOsName());
        List<OssListDto> localList=bsFileStoreMapper.queryLocalList(page,ossListDto.getOsName());
        List<OssListDto> resList=new ArrayList<>();
        if(StringUtils.isNotBlank(ossListDto.getStoreType())){
            if(OSS.equals(ossListDto.getStoreType())){
                resList.addAll(ossList);
            }
            if(LOCAL.equals(ossListDto.getStoreType())){
                resList.addAll(localList);
            }
        }else{
            resList.addAll(ossList);
            resList.addAll(localList);
        }
        return page.setRecords(resList).setTotal(resList.size());

    }
}

3.mapper层接口与mybatis xml中sql编写

public interface BsFileStoreMapper extends BaseMapper<BsFileStore> {

    /**
     * oss存储列表
     */
    List<OssListDto> queryOssList(Page<OssListDto> page, @Param("osName") String osName);

    /**
     * 本地存储列表
     */
    List<OssListDto> queryLocalList(Page<OssListDto> page, @Param("osName") String osName);
}

注意:这个 Page<OssListDto> page 是必须要有的，否则 Mybatis-Plus 无法为你实现分页。

对应的xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.mapper.BsFileStoreMapper">
    
    <!--多表关联查询，推荐直接用传统 mybatis xml-->
    <select id="queryOssList" resultType="org.jeecg.modules.dto.OssListDto">
        select id,os_name as osName,IFNULL(NULL,1) storeType,endpoint_first as storePath
        from pm_os_info
        <where>
            <if test="osName!=null">
                os_name=#{osName}
            </if>
        </where>
    </select>

    <select id="queryLocalList" resultType="org.jeecg.modules.dto.OssListDto">
        select id,store_name as osName,IFNULL(NULL,2) storeType,local_path as storePath
        from pm_localstore_info
        <where>
            <if test="osName!=null">
                store_name=#{osName}
            </if>
        </where>
    </select>

</mapper>

#{osName}是拿取BsFileStoreMapper接口中queryOssList方法所传递的osName变量,将其作为where条件的参数

id="queryOssList" 中queryOssList是BsFileStoreMapper接口中queryOssList的方法名

分页功能可能出现的bug:

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'osName' not found. Available parameters are [arg1, arg0, param1, param2]
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96) ~[mybatis-spring-2.0.6.jar:2.0.6]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) ~[mybatis-spring-2.0.6.jar:2.0.6]
    at com.sun.proxy.$Proxy150.selectList(Unknown Source) ~[na:na]
    
Caused by: org.apache.ibatis.binding.BindingException: Parameter 'osName' not found. Available parameters are [arg1, arg0, param1, param2]
    at org.apache.ibatis.binding.MapperMethod$ParamMap.get(MapperMethod.java:212) ~[mybatis-3.5.7.jar:3.5.7]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6]
    ... 130 common frames omitted

原因: BsFileStoreMapper 接口中osName未加注解@Param("osName")

解决方案:添加@Param("osName")注解

4.postman测试

postman模拟前端:

http://127.0.0.1:7009/test/bsFileStore/list?pageNo=2&pageSize=5&osName=fleplocal

测试结果:

 参考文献:

http://doc.jeecg.com/2043943(分页功能参考)