Mybatis 分页查询时的总数优化，采用一次查询方式

cg.family

已于 2022-06-24 15:41:35 修改

阅读量1k

点赞数

文章标签： mybatis

于 2022-06-24 15:39:50 首次发布

本文链接：https://blog.csdn.net/software_0215/article/details/125446718

版权

---------------------------------------------------------------------------------------
1. mysql的SQL_CALC_FOUND_ROWS
2. mybatis的@ResultMap
两个关键点
---------------------------------------------------------------------------------------

/**
 * 数据库
 * @param schema
 * @return
 */
@Results(id = "_schema", value = {@Result(column = "table_schema", property = "table_schema", jdbcType = JdbcType.VARCHAR),
        @Result(column = "table_name", property = "table_name", jdbcType = JdbcType.VARCHAR),
        @Result(column = "table_rows", property = "table_rows", jdbcType = JdbcType.INTEGER)})
@Select({"SELECT t1.* FROM information_schema.tables t1 WHERE t1.table_schema = #{schema}"})
Map<String,Object> schemaForTables(@Param("schema") String schema);

******上面红色粗体部分很重要，返回类型决定了mybatis如何取封装@Results******

/**
 * 数量
 * @return
 */
@Results(id = "_tables", value = {@Result(column = "total", property = "total", jdbcType = JdbcType.INTEGER)})
@Select({"SELECT COUNT(*) AS total FROM information_schema.tables"})
Integer countAll();

/**
 * 获取分页指定数量的数据表
 *
 * @param page
 * @return
 */
@Select({"select SQL_CALC_FOUND_ROWS t1.* from information_schema.tables t1 where t1.table_schema=#{params}",
        " limit ${(page-1) * limit},${limit};"
        ,"SELECT FOUND_ROWS() total;"
        })
@ResultMap({"_schema", "_tables"})
public List<List<Object>> querySchemaTables(PageBean<List<Object>,String> page);