智能將SqlServer的查詢語句轉換為分頁語句

標籤：sqlserver   sqlserver分頁   智慧型分頁處理   分頁外掛程式   分頁   

主要用到了jsqlparser，前面有篇部落格介紹過：

JAVA - Sql解析工具jsqlparser簡單使用

為了給Mybatis分頁外掛程式增加對sqlserver的支援，專門寫了這樣一個獨立的工具，只依賴jsqlparser。

這個類不僅是為了給分頁外掛程式使用的，他還能獨立使用，使用它你可以方便的產生一個分頁查詢。

分頁外掛程式地址：Mybatis_PageHelper

SqlServer分頁轉換完整代碼：com/github/pagehelper/SqlServer.java

簡單講一下處理的邏輯：

通過對SqlServer進行分析，利用jsqlparser方便的解析，然後對sql結構進行修改，產生最後的分頁語句。

首先一個sql通常有兩種情況，一種是普通的一個select查詢，一種是通過union,minus等串連的多個查詢。

當發現是多個查詢的時候，會在原來的SQL基礎上在外麵包含一層查詢，讓原來的查詢變成子查詢。

外層的查詢會從多個查詢中的第一個查詢中提取查詢列（有別名的使用別名），因為每個查詢的列都是一樣的，所以找一個提取就行。

另外在多個SQL中的最後一個相比其他來說可能會多一些條件，這裡主要考慮的是order by，如果有order by語句，會把order by移到外層SQL上。

做完上面的處理後，就和第一種普通的一個select查詢一樣了。

接下來處理這一個select查詢。

第一步先擷取查詢列，並且會對別名和表名進行一些特殊處理。

第二步給SQL增加ROW_NUMBER()，將order by提取到OVER中

第三步處理全部子查詢，如果子查詢包含order by，會增加top 100 percent

第四步在select查詢外包一層TOP查詢。

經過上面的步驟就能得到一個合理結構的分頁查詢了。

其中有一些細節性的東西jsqlparser都考慮到了，不需要自己去特殊處理，例如distinct。

下面是兩個例子。

這個類是獨立的，使用的時候可以初始化一個，然後直接調用方法即可。

初始化：

public static final SqlServer sqlServer = new SqlServer();

第一個，多個查詢UNION ALL

@Testpublic void testSqlUnion() throws JSQLParserException {    String originalSql = "select countryname,countrycode code from country where id >170 " +            "union all " +            "select countryname,countrycode code from country where id < 10 order by code";    System.out.println(sqlServer.convertToPageSql(originalSql, 1, 10));}

產生的SQL如下（經過人工格式化）：

SELECT TOP 10 PAGE_TABLE_ALIAS.countryname, PAGE_TABLE_ALIAS.code  FROM (SELECT ROW_NUMBER() OVER(ORDER BY code) PAGE_ROW_NUMBER,               WRAP_OUTER_TABLE.countryname,               WRAP_OUTER_TABLE.code          FROM ((SELECT countryname, countrycode code                   FROM country                  WHERE id > 170) UNION ALL                (SELECT countryname, countrycode code                   FROM country                  WHERE id < 10)) AS WRAP_OUTER_TABLE) AS PAGE_TABLE_ALIAS WHERE PAGE_ROW_NUMBER > 1 ORDER BY PAGE_ROW_NUMBER

第二個，簡單查詢

@Testpublic void testSqlDistinct() throws JSQLParserException {    String originalSql = "select distinct countrycode,countryname from country order by countrycode";    System.out.println(sqlServer.convertToPageSql(originalSql, 1, 10));}

產生的SQL如下（經過人工格式化）：

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

智能將SqlServer的查詢語句轉換為分頁語句