com.github.pagehelper.parser.defaults

Class DefaultSqlServerSqlParser

java.lang.Object

com.github.pagehelper.parser.defaults.DefaultSqlServerSqlParser

All Implemented Interfaces:

SqlServerSqlParser

public class DefaultSqlServerSqlParser
extends Object
implements SqlServerSqlParser

将sqlserver查询语句转换为分页语句
注意事项：

1. 请先保证你的SQL可以执行
2. sql中最好直接包含order by，可以自动从sql提取
3. 如果没有order by，可以通过入参提供，但是需要自己保证正确
4. 如果sql有order by，可以通过orderby参数覆盖sql中的order by
5. order by的列名不能使用别名
6. 表和列使用别名的时候不要使用单引号(')

该类设计为一个独立的工具类，依赖jsqlparser,可以独立使用

Author:

liuzh

Field Summary

Fields

Modifier and Type	Field and Description
protected static String	PAGE_COLUMN_ALIAS_PREFIX
protected static String	PAGE_ROW_NUMBER
protected static net.sf.jsqlparser.schema.Column	PAGE_ROW_NUMBER_COLUMN
static String	PAGE_SIZE
static net.sf.jsqlparser.expression.Alias	PAGE_TABLE_ALIAS
protected static String	PAGE_TABLE_NAME
static String	START_ROW
protected static net.sf.jsqlparser.statement.select.Top	TOP100_PERCENT
protected static String	WRAP_TABLE

Constructor Summary

Constructors

Constructor and Description
DefaultSqlServerSqlParser()

Method Summary

Modifier and Type	Method and Description
protected net.sf.jsqlparser.statement.select.SelectItem<?>	addRowNumber(net.sf.jsqlparser.statement.select.PlainSelect plainSelect, List<net.sf.jsqlparser.statement.select.SelectItem<?>> autoItems) 获取 ROW_NUMBER() 列
protected net.sf.jsqlparser.statement.select.OrderByElement	cloneOrderByElement(net.sf.jsqlparser.statement.select.OrderByElement orig, net.sf.jsqlparser.expression.Expression expression) 复制 OrderByElement
protected net.sf.jsqlparser.statement.select.OrderByElement	cloneOrderByElement(net.sf.jsqlparser.statement.select.OrderByElement orig, String alias) 复制 OrderByElement
String	convertToPageSql(String sql) 转换为分页语句
String	convertToPageSql(String sql, Integer offset, Integer limit) 转换为分页语句
protected List<net.sf.jsqlparser.statement.select.OrderByElement>	getOrderByElements(net.sf.jsqlparser.statement.select.PlainSelect plainSelect, List<net.sf.jsqlparser.statement.select.SelectItem<?>> autoItems) 获取新的排序列表
protected net.sf.jsqlparser.statement.select.Select	getPageSelect(net.sf.jsqlparser.statement.select.Select select) 获取一个外层包装的TOP查询
protected List<net.sf.jsqlparser.statement.select.SelectItem<?>>	getSelectItems(net.sf.jsqlparser.statement.select.PlainSelect plainSelect) 获取查询列
boolean	isNotEmptyList(List<?> list) List不空
protected void	processFromItem(net.sf.jsqlparser.statement.select.FromItem fromItem, int level) 处理子查询
protected void	processPlainSelect(net.sf.jsqlparser.statement.select.PlainSelect plainSelect, int level) 处理PlainSelect类型的selectBody
protected void	processSelectBody(net.sf.jsqlparser.statement.select.Select select, int level) 处理selectBody去除Order by
protected net.sf.jsqlparser.statement.select.Select	wrapSetOperationList(net.sf.jsqlparser.statement.select.SetOperationList setOperationList) 包装SetOperationList

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

START_ROW

public static final String START_ROW

PAGE_SIZE

public static final String PAGE_SIZE

WRAP_TABLE

protected static final String WRAP_TABLE

See Also:

Constant Field Values

PAGE_TABLE_NAME

protected static final String PAGE_TABLE_NAME

See Also:

Constant Field Values

PAGE_TABLE_ALIAS

public static final net.sf.jsqlparser.expression.Alias PAGE_TABLE_ALIAS

PAGE_ROW_NUMBER

protected static final String PAGE_ROW_NUMBER

See Also:

Constant Field Values

PAGE_ROW_NUMBER_COLUMN

protected static final net.sf.jsqlparser.schema.Column PAGE_ROW_NUMBER_COLUMN

TOP100_PERCENT

protected static final net.sf.jsqlparser.statement.select.Top TOP100_PERCENT

PAGE_COLUMN_ALIAS_PREFIX

protected static final String PAGE_COLUMN_ALIAS_PREFIX

See Also:

Constant Field Values

Constructor Detail

DefaultSqlServerSqlParser

public DefaultSqlServerSqlParser()

Method Detail

convertToPageSql

public String convertToPageSql(String sql)

转换为分页语句

Parameters:

sql -

Returns:

convertToPageSql

public String convertToPageSql(String sql,
                               Integer offset,
                               Integer limit)

转换为分页语句

Specified by:

convertToPageSql in interface SqlServerSqlParser

Parameters:

sql -

offset -

limit -

Returns:

getPageSelect

protected net.sf.jsqlparser.statement.select.Select getPageSelect(net.sf.jsqlparser.statement.select.Select select)

获取一个外层包装的TOP查询

Parameters:

select -

Returns:

wrapSetOperationList

protected net.sf.jsqlparser.statement.select.Select wrapSetOperationList(net.sf.jsqlparser.statement.select.SetOperationList setOperationList)

包装SetOperationList

Parameters:

setOperationList -

Returns:

getSelectItems

protected List<net.sf.jsqlparser.statement.select.SelectItem<?>> getSelectItems(net.sf.jsqlparser.statement.select.PlainSelect plainSelect)

获取查询列

Parameters:

plainSelect -

Returns:

addRowNumber

protected net.sf.jsqlparser.statement.select.SelectItem<?> addRowNumber(net.sf.jsqlparser.statement.select.PlainSelect plainSelect,
                                                                        List<net.sf.jsqlparser.statement.select.SelectItem<?>> autoItems)

获取 ROW_NUMBER() 列

Parameters:

plainSelect - 原查询

autoItems - 自动生成的查询列

Returns:

ROW_NUMBER() 列

processSelectBody

protected void processSelectBody(net.sf.jsqlparser.statement.select.Select select,
                                 int level)

处理selectBody去除Order by

Parameters:

select -

processPlainSelect

protected void processPlainSelect(net.sf.jsqlparser.statement.select.PlainSelect plainSelect,
                                  int level)

处理PlainSelect类型的selectBody

Parameters:

plainSelect -

processFromItem

protected void processFromItem(net.sf.jsqlparser.statement.select.FromItem fromItem,
                               int level)

处理子查询

Parameters:

fromItem -

isNotEmptyList

public boolean isNotEmptyList(List<?> list)

List不空

Parameters:

list -

Returns:

cloneOrderByElement

protected net.sf.jsqlparser.statement.select.OrderByElement cloneOrderByElement(net.sf.jsqlparser.statement.select.OrderByElement orig,
                                                                                String alias)

复制 OrderByElement

Parameters:

orig - 原 OrderByElement

alias - 新 OrderByElement 的排序要素

Returns:

复制的新 OrderByElement

cloneOrderByElement

protected net.sf.jsqlparser.statement.select.OrderByElement cloneOrderByElement(net.sf.jsqlparser.statement.select.OrderByElement orig,
                                                                                net.sf.jsqlparser.expression.Expression expression)

复制 OrderByElement

Parameters:

orig - 原 OrderByElement

expression - 新 OrderByElement 的排序要素

Returns:

复制的新 OrderByElement

getOrderByElements

protected List<net.sf.jsqlparser.statement.select.OrderByElement> getOrderByElements(net.sf.jsqlparser.statement.select.PlainSelect plainSelect,
                                                                                     List<net.sf.jsqlparser.statement.select.SelectItem<?>> autoItems)

获取新的排序列表

Parameters:

plainSelect - 原始查询

autoItems - 生成的新查询要素

Returns:

新的排序列表