可以使用 MyBatis 的拦截器(Interceptor)机制来实现在 SQL 语句中不写 LIMIT 关键字的分页查询,具体实现步骤如下:
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class PageInterceptor implements Interceptor {
private static final ThreadLocal<Page> PAGE_THREAD_LOCAL = new ThreadLocal<>();
public static void setPage(Page page) {
PAGE_THREAD_LOCAL.set(page);
}
public static Page getPage() {
return PAGE_THREAD_LOCAL.get();
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
BoundSql boundSql = statementHandler.getBoundSql();
Object parameterObject = boundSql.getParameterObject();
if (parameterObject instanceof Map) {
Map<String, Object> paramMap = (Map<String, Object>) parameterObject;
if (paramMap.containsKey("page")) {
Page page = (Page) paramMap.get("page");
setPage(page);
String sql = boundSql.getSql();
String newSql = sql + " LIMIT " + page.getOffset() + ", " + page.getPageSize();
Field field = BoundSql.class.getDeclaredField("sql");
field.setAccessible(true);
field.set(boundSql, newSql);
}
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
// do nothing
}
}
- 在 Spring Boot 的启动类中注册分页插件,如下所示:
@Configuration
public class MybatisConfig {
@Bean
public PageInterceptor pageInterceptor() {
return new PageInterceptor();
}
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource, PageInterceptor pageInterceptor) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
// 添加分页插件
Interceptor[] interceptors = new Interceptor[] { pageInterceptor };
sessionFactory.setPlugins(interceptors);
return sessionFactory.getObject();
}
}
- 在业务代码中使用 PageInterceptor 类的 setPage 方法将分页对象设置到当前线程的 ThreadLocal 中,从而在拦截器中获取到分页信息,如下所示:
@Service
public class UserServiceImpl implements UserService {
@Autowired
private SqlSession sqlSession;
@Override
public Page<User> findPage(int pageNo, int pageSize) {
Page page = new Page(pageNo, pageSize);
PageInterceptor.setPage(page);
List<User> users = sqlSession.selectList("com.example.demo.dao.UserMapper.findPage");
int totalRecords = sqlSession.selectOne("com.example.demo.dao.UserMapper.count");
int totalPages = (totalRecords + pageSize - 1) / pageSize;
page.setTotalRecords(totalRecords);
page.setTotalPages(totalPages);
page.setResults(users);
return page;
}
}
上述代码中,PageInterceptor 类在拦截器中获取到分页对象,并根据分页对象计算出 SQL 中的 LIMIT 子句,然后使用反射将修改后的 SQL 语句设置回 BoundSql 对象中,从而实现在 SQL 语句中不写 LIMIT 关键字的分页查询。此外,需要在 MyBatis 的 XML 映射文件中实现查询 SQL 语句,如下所示:
<select id="findPage" resultType="com.example.demo.entity.User">
SELECT * FROM user
</select>
<select id="count" resultType="int">
SELECT COUNT(*) FROM user
</select>
在上述 XML 映射文件中,findPage 查询语句中不需要写 LIMIT 子句,而是在分页拦截器中动态生成 LIMIT 子句,从而实现了 SQL 语句中不需要写 LIMIT 关键字的分页查询。