Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package com.github.moonfruit.mybatis.pagehelper;
- import java.util.*;
- import com.github.pagehelper.parser.SqlServer;
- import net.sf.jsqlparser.expression.Alias;
- import net.sf.jsqlparser.expression.Expression;
- import net.sf.jsqlparser.expression.LongValue;
- import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
- import net.sf.jsqlparser.schema.Column;
- import net.sf.jsqlparser.statement.select.*;
- public class SqlServerAnalyzer extends SqlServer {
- private static final String PAGE_ROW_NUMBER = "ROW_ID";
- private static final Column PAGE_ROW_NUMBER_COLUMN = new Column(PAGE_ROW_NUMBER);
- protected static final String PAGE_COLUMN_ALIAS_PREFIX = "ROW_ALIAS_";
- /**
- * 获取一个外层包装的TOP查询
- */
- @Override
- protected Select getPageSelect(Select select) {
- SelectBody selectBody = select.getSelectBody();
- if (selectBody instanceof SetOperationList) {
- selectBody = wrapSetOperationList((SetOperationList) selectBody);
- }
- //这里的selectBody一定是PlainSelect
- if (((PlainSelect) selectBody).getTop() != null) {
- throw new RuntimeException("被分页的语句已经包含了Top,不能再通过分页插件进行分页查询!");
- }
- //获取查询列
- List<SelectItem> selectItems = getSelectItems((PlainSelect) selectBody);
- //对一层的SQL增加ROW_NUMBER()
- List<SelectItem> autoItems = new ArrayList<>();
- SelectItem orderByColumn = getRowNumber((PlainSelect) selectBody, autoItems);
- ((PlainSelect) selectBody).addSelectItems(autoItems.toArray(new SelectItem[autoItems.size()]));
- //处理子语句中的order by
- processSelectBody(selectBody, 0);
- //中间一层 SQL
- PlainSelect innerSelectBody = new PlainSelect();
- //PAGE_ROW_NUMBER
- innerSelectBody.addSelectItems(orderByColumn);
- innerSelectBody.addSelectItems(selectItems.toArray(new SelectItem[selectItems.size()]));
- SubSelect fromInnerItem = new SubSelect();
- fromInnerItem.setSelectBody(selectBody);
- fromInnerItem.setAlias(PAGE_TABLE_ALIAS);
- innerSelectBody.setFromItem(fromInnerItem);
- //新建一个select
- Select newSelect = new Select();
- PlainSelect newSelectBody = new PlainSelect();
- //设置top
- Top top = new Top();
- top.setRowCount(Long.MAX_VALUE);
- newSelectBody.setTop(top);
- //设置order by
- List<OrderByElement> orderByElements = new ArrayList<>();
- OrderByElement orderByElement = new OrderByElement();
- orderByElement.setExpression(PAGE_ROW_NUMBER_COLUMN);
- orderByElements.add(orderByElement);
- newSelectBody.setOrderByElements(orderByElements);
- //设置where
- GreaterThan greaterThan = new GreaterThan();
- greaterThan.setLeftExpression(PAGE_ROW_NUMBER_COLUMN);
- greaterThan.setRightExpression(new LongValue(Long.MIN_VALUE));
- newSelectBody.setWhere(greaterThan);
- //设置selectItems
- newSelectBody.setSelectItems(selectItems);
- //设置fromItems
- SubSelect fromItem = new SubSelect();
- fromItem.setSelectBody(innerSelectBody);
- fromItem.setAlias(PAGE_TABLE_ALIAS);
- newSelectBody.setFromItem(fromItem);
- newSelect.setSelectBody(newSelectBody);
- if (isNotEmptyList(select.getWithItemsList())) {
- newSelect.setWithItemsList(select.getWithItemsList());
- }
- return newSelect;
- }
- /**
- * 获取查询列
- */
- @Override
- protected List<SelectItem> getSelectItems(PlainSelect plainSelect) {
- List<SelectItem> selectItems = super.getSelectItems(plainSelect);
- for (SelectItem item : selectItems) {
- if (item instanceof AllColumns) {
- return Collections.<SelectItem>singletonList(new AllColumns());
- }
- }
- return selectItems;
- }
- /**
- * 最外层的SQL查询需要增加ROW_NUMBER()
- */
- private SelectItem getRowNumber(PlainSelect plainSelect, List<SelectItem> autoItems) {
- //增加ROW_NUMBER()
- StringBuilder orderByBuilder = new StringBuilder();
- orderByBuilder.append("ROW_NUMBER() OVER (");
- if (isNotEmptyList(plainSelect.getOrderByElements())) {
- //注意:order by别名的时候有错,由于没法判断一个列是否为别名,所以不能解决
- orderByBuilder.append(PlainSelect.orderByToString(false,
- getOrderByElements(plainSelect, autoItems)).substring(1));
- } else {
- orderByBuilder.append("ORDER BY RAND()");
- }
- //需要把改orderby清空
- if (isNotEmptyList(plainSelect.getOrderByElements())) {
- plainSelect.setOrderByElements(null);
- }
- orderByBuilder.append(") ");
- orderByBuilder.append(PAGE_ROW_NUMBER);
- return new SelectExpressionItem(new Column(orderByBuilder.toString()));
- }
- private OrderByElement cloneOrderByElement(OrderByElement orig, String alias) {
- return cloneOrderByElement(orig, new Column(alias));
- }
- private OrderByElement cloneOrderByElement(OrderByElement orig, Expression expression) {
- OrderByElement element = new OrderByElement();
- element.setAsc(orig.isAsc());
- element.setAscDescPresent(orig.isAscDescPresent());
- element.setNullOrdering(orig.getNullOrdering());
- element.setExpression(expression);
- return element;
- }
- private List<OrderByElement> getOrderByElements(PlainSelect plainSelect,
- List<SelectItem> autoItems) {
- List<OrderByElement> orderByElements = plainSelect.getOrderByElements();
- ListIterator<OrderByElement> iterator = orderByElements.listIterator();
- OrderByElement orderByElement;
- Map<String, SelectExpressionItem> selectMap = new HashMap<>();
- Set<String> aliases = new HashSet<>();
- boolean allColumns = false;
- Set<String> allColumnsTables = new HashSet<>();
- for (SelectItem item : plainSelect.getSelectItems()) {
- if (item instanceof SelectExpressionItem) {
- SelectExpressionItem expItem = (SelectExpressionItem) item;
- selectMap.put(expItem.getExpression().toString(), expItem);
- Alias alias = expItem.getAlias();
- if (alias != null) {
- aliases.add(alias.getName());
- }
- } else if (item instanceof AllColumns) {
- allColumns = true;
- } else if (item instanceof AllTableColumns) {
- allColumnsTables.add(((AllTableColumns) item).getTable().getName());
- }
- }
- int aliasNo = 1;
- while (iterator.hasNext()) {
- orderByElement = iterator.next();
- Expression expression = orderByElement.getExpression();
- SelectExpressionItem selectExpressionItem = selectMap.get(expression.toString());
- if (selectExpressionItem != null) {
- Alias alias = selectExpressionItem.getAlias();
- if (alias != null) {
- iterator.set(cloneOrderByElement(orderByElement, alias.getName()));
- } else {
- if (expression instanceof Column) {
- ((Column) expression).setTable(null);
- } else {
- throw new RuntimeException("列 \"" + expression + "\" 需要定义别名");
- }
- }
- } else {
- if (expression instanceof Column) {
- String table = ((Column) expression).getTable().getName();
- if (table != null) {
- if (allColumns || allColumnsTables.contains(table)) {
- ((Column) expression).setTable(null);
- continue;
- }
- } else {
- if (allColumns ||
- (allColumnsTables.size() == 1 && plainSelect.getJoins() == null) ||
- aliases.contains(((Column) expression).getColumnName())) {
- continue;
- }
- }
- }
- String aliasName = PAGE_COLUMN_ALIAS_PREFIX + aliasNo++;
- SelectExpressionItem item = new SelectExpressionItem();
- item.setExpression(expression);
- item.setAlias(new Alias(aliasName));
- autoItems.add(item);
- iterator.set(cloneOrderByElement(orderByElement, aliasName));
- }
- }
- return orderByElements;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement