Intelligent conversion of SQL Server query statements to paging statements

The main use of Jsqlparser, in front of a blog introduced:

Java-sql Parsing tool Jsqlparser simple to use

In order to add support for SQL Server to the MyBatis page plug-in, this is a standalone tool that relies only on jsqlparser.

This class is not only for the page plug-in to use, he can also use independently, using it you can easily generate a paging query.

Paging Plugin Address: Mybatis_pagehelper

SQL Server Paging Conversion complete code: Com/github/pagehelper/sqlserver.java

Simply talk about the logic of the process:

By analyzing SQL Server, we make use of jsqlparser to parse, and then modify the structure, and generate the final paging statement.

First, there are usually two cases of SQL, one is a normal select query, and one is a number of queries connected through Union,minus.

When the discovery is multiple queries, will be on the original SQL based on the outside to include a layer of query, so that the original query into a subquery.

The outer query extracts the query columns from the first query in multiple queries (aliases using aliases) because the columns for each query are the same, so find an extract on the row.

In addition, the last one in multiple SQL may have more conditions than others, the main consideration is order BY, and if there is an order BY statement, the order by is moved to the outer SQL.

After finishing the above, it is the same as the first normal select query.

Next, this one select query is processed.

The first step is to get the query columns first, and some special handling of aliases and table names.

The second step adds row_number () to SQL and extracts order by to over

The third step is to process all subqueries, and if the subquery contains an order BY, it will increase the top percent

The fourth step is to outsource a top-level query in select query.

Through the above steps can be a reasonable structure of the paging query.

Some of the details of the Jsqlparser are considered, do not need to go to special treatment, such as distinct.

Here are two examples.

This class is independent and can be initialized when used, and then called directly by the method.

Initialization

public static final SQL Server = new SQL Server ();

First, multiple queries 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 & Lt "Order by code";    System.out.println (Sqlserver.converttopagesql (Originalsql, 1, 10));}

The resulting SQL is as follows (manually formatted):

Select TOP 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 > "UNION all"                (SELECT countryname, CountryCode code from                   cou Ntry                  where ID <) as wrap_outer_table) as Page_table_alias WHERE page_row_number > 1 ORDER by Page_row_number

Second, a simple query

@Testpublic void Testsqldistinct () throws jsqlparserexception {    String originalsql = "SELECT distinct CountryCode, CountryName from country order by CountryCode ";    System.out.println (Sqlserver.converttopagesql (Originalsql, 1, 10));}

The resulting SQL is as follows (manually formatted):

Select TOP 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

Attention:

1. Because you need to extract order by, make sure that the outermost SQL contains the order by

2. If there is no order by, then the converttopagesql called above has a fourth argument

If the original SQL has an order by, then this method specifies that the order by will overwrite the original SQL

It is difficult to grasp the name of a field when it is specified, so it is recommended to bring an order by in SQL

Intelligent conversion of SQL Server query statements to paging statements