分页插件将SQLServer查询语句转换成分页语句

我就是我 2022-09-30 09:52 248阅读 0赞

分页插件地址：[https://github.com/pagehelper/Mybatis-PageHelper][https_github.com_pagehelper_Mybatis-PageHelper]

本次演示使用jar包版本：[http://repo1.maven.org/maven2/com/github/pagehelper/pagehelper/4.2.0/pagehelper-4.2.0.jar][http_repo1.maven.org_maven2_com_github_pagehelper_pagehelper_4.2.0_pagehelper-4.2.0.jar]

Maven地址：

<dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>4.2.0</version>
    </dependency>

测试：

import com.github.pagehelper.parser.SqlServer;
    
    public class Test{
    	public static final SqlServer sqlServer = new SqlServer();//初始化
    	
    	@Test  
    	public void testSqlUnion() throws JSQLParserException {
    		String originalSql = "select distinct countrycode,countryname from country order by countrycode";
    		System.out.println(sqlServer.convertToPageSql(originalSql, 1, 10));
    	}  
    }

测试结果（已格式化）：

SELECT TOP 10 PAGE_TABLE_ALIAS.countrycode, PAGE_TABLE_ALIAS.countryname  
      FROM (SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY countrycode) PAGE_ROW_NUMBER,  
                            countrycode,  
                            countryname  
              FROM country) AS PAGE_TABLE_ALIAS  
     WHERE PAGE_ROW_NUMBER > 1  
     ORDER BY PAGE_ROW_NUMBER

注意：

1.由于需要提取order by，所以尽可能保证最外层的SQL包含order by  
2.如果没有order by，那么上面调用的convertToPageSql还有第四个参数orderBy

public String convertToPageSql(String sql, int offset, int limit, String orderBy)

如果原来的sql有order by，那么通过该方法指定orderBy之后会覆盖原sql中的order by  
人为指定的时候很难把握字段名字的写法，所以建议在sql中带上order by

[https_github.com_pagehelper_Mybatis-PageHelper]: https://github.com/pagehelper/Mybatis-PageHelper
[http_repo1.maven.org_maven2_com_github_pagehelper_pagehelper_4.2.0_pagehelper-4.2.0.jar]: http://repo1.maven.org/maven2/com/github/pagehelper/pagehelper/4.2.0/pagehelper-4.2.0.jar