Advertisement
Guest User

Untitled

a guest
Jun 29th, 2016
66
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.61 KB | None | 0 0
  1. package com.github.moonfruit.mybatis.pagehelper;
  2.  
  3. import java.util.*;
  4.  
  5. import com.github.pagehelper.parser.SqlServer;
  6. import net.sf.jsqlparser.expression.Alias;
  7. import net.sf.jsqlparser.expression.Expression;
  8. import net.sf.jsqlparser.expression.LongValue;
  9. import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
  10. import net.sf.jsqlparser.schema.Column;
  11. import net.sf.jsqlparser.statement.select.*;
  12.  
  13. public class SqlServerAnalyzer extends SqlServer {
  14.  
  15. private static final String PAGE_ROW_NUMBER = "ROW_ID";
  16. private static final Column PAGE_ROW_NUMBER_COLUMN = new Column(PAGE_ROW_NUMBER);
  17.  
  18. protected static final String PAGE_COLUMN_ALIAS_PREFIX = "ROW_ALIAS_";
  19.  
  20. /**
  21. * 获取一个外层包装的TOP查询
  22. */
  23. @Override
  24. protected Select getPageSelect(Select select) {
  25. SelectBody selectBody = select.getSelectBody();
  26. if (selectBody instanceof SetOperationList) {
  27. selectBody = wrapSetOperationList((SetOperationList) selectBody);
  28. }
  29. //这里的selectBody一定是PlainSelect
  30. if (((PlainSelect) selectBody).getTop() != null) {
  31. throw new RuntimeException("被分页的语句已经包含了Top,不能再通过分页插件进行分页查询!");
  32. }
  33. //获取查询列
  34. List<SelectItem> selectItems = getSelectItems((PlainSelect) selectBody);
  35. //对一层的SQL增加ROW_NUMBER()
  36. List<SelectItem> autoItems = new ArrayList<>();
  37. SelectItem orderByColumn = getRowNumber((PlainSelect) selectBody, autoItems);
  38. ((PlainSelect) selectBody).addSelectItems(autoItems.toArray(new SelectItem[autoItems.size()]));
  39. //处理子语句中的order by
  40. processSelectBody(selectBody, 0);
  41.  
  42. //中间一层 SQL
  43. PlainSelect innerSelectBody = new PlainSelect();
  44.  
  45. //PAGE_ROW_NUMBER
  46. innerSelectBody.addSelectItems(orderByColumn);
  47. innerSelectBody.addSelectItems(selectItems.toArray(new SelectItem[selectItems.size()]));
  48.  
  49. SubSelect fromInnerItem = new SubSelect();
  50. fromInnerItem.setSelectBody(selectBody);
  51. fromInnerItem.setAlias(PAGE_TABLE_ALIAS);
  52. innerSelectBody.setFromItem(fromInnerItem);
  53.  
  54. //新建一个select
  55. Select newSelect = new Select();
  56. PlainSelect newSelectBody = new PlainSelect();
  57. //设置top
  58. Top top = new Top();
  59. top.setRowCount(Long.MAX_VALUE);
  60. newSelectBody.setTop(top);
  61. //设置order by
  62. List<OrderByElement> orderByElements = new ArrayList<>();
  63. OrderByElement orderByElement = new OrderByElement();
  64. orderByElement.setExpression(PAGE_ROW_NUMBER_COLUMN);
  65. orderByElements.add(orderByElement);
  66. newSelectBody.setOrderByElements(orderByElements);
  67. //设置where
  68. GreaterThan greaterThan = new GreaterThan();
  69. greaterThan.setLeftExpression(PAGE_ROW_NUMBER_COLUMN);
  70. greaterThan.setRightExpression(new LongValue(Long.MIN_VALUE));
  71. newSelectBody.setWhere(greaterThan);
  72. //设置selectItems
  73. newSelectBody.setSelectItems(selectItems);
  74. //设置fromItems
  75. SubSelect fromItem = new SubSelect();
  76. fromItem.setSelectBody(innerSelectBody);
  77. fromItem.setAlias(PAGE_TABLE_ALIAS);
  78. newSelectBody.setFromItem(fromItem);
  79.  
  80. newSelect.setSelectBody(newSelectBody);
  81. if (isNotEmptyList(select.getWithItemsList())) {
  82. newSelect.setWithItemsList(select.getWithItemsList());
  83. }
  84. return newSelect;
  85. }
  86.  
  87. /**
  88. * 获取查询列
  89. */
  90. @Override
  91. protected List<SelectItem> getSelectItems(PlainSelect plainSelect) {
  92. List<SelectItem> selectItems = super.getSelectItems(plainSelect);
  93. for (SelectItem item : selectItems) {
  94. if (item instanceof AllColumns) {
  95. return Collections.<SelectItem>singletonList(new AllColumns());
  96. }
  97. }
  98. return selectItems;
  99. }
  100.  
  101. /**
  102. * 最外层的SQL查询需要增加ROW_NUMBER()
  103. */
  104. private SelectItem getRowNumber(PlainSelect plainSelect, List<SelectItem> autoItems) {
  105. //增加ROW_NUMBER()
  106. StringBuilder orderByBuilder = new StringBuilder();
  107. orderByBuilder.append("ROW_NUMBER() OVER (");
  108. if (isNotEmptyList(plainSelect.getOrderByElements())) {
  109. //注意:order by别名的时候有错,由于没法判断一个列是否为别名,所以不能解决
  110. orderByBuilder.append(PlainSelect.orderByToString(false,
  111. getOrderByElements(plainSelect, autoItems)).substring(1));
  112. } else {
  113. orderByBuilder.append("ORDER BY RAND()");
  114. }
  115. //需要把改orderby清空
  116. if (isNotEmptyList(plainSelect.getOrderByElements())) {
  117. plainSelect.setOrderByElements(null);
  118. }
  119. orderByBuilder.append(") ");
  120. orderByBuilder.append(PAGE_ROW_NUMBER);
  121. return new SelectExpressionItem(new Column(orderByBuilder.toString()));
  122. }
  123.  
  124. private OrderByElement cloneOrderByElement(OrderByElement orig, String alias) {
  125. return cloneOrderByElement(orig, new Column(alias));
  126. }
  127.  
  128. private OrderByElement cloneOrderByElement(OrderByElement orig, Expression expression) {
  129. OrderByElement element = new OrderByElement();
  130. element.setAsc(orig.isAsc());
  131. element.setAscDescPresent(orig.isAscDescPresent());
  132. element.setNullOrdering(orig.getNullOrdering());
  133. element.setExpression(expression);
  134. return element;
  135. }
  136.  
  137. private List<OrderByElement> getOrderByElements(PlainSelect plainSelect,
  138. List<SelectItem> autoItems) {
  139. List<OrderByElement> orderByElements = plainSelect.getOrderByElements();
  140. ListIterator<OrderByElement> iterator = orderByElements.listIterator();
  141. OrderByElement orderByElement;
  142.  
  143. Map<String, SelectExpressionItem> selectMap = new HashMap<>();
  144. Set<String> aliases = new HashSet<>();
  145. boolean allColumns = false;
  146. Set<String> allColumnsTables = new HashSet<>();
  147.  
  148. for (SelectItem item : plainSelect.getSelectItems()) {
  149. if (item instanceof SelectExpressionItem) {
  150. SelectExpressionItem expItem = (SelectExpressionItem) item;
  151. selectMap.put(expItem.getExpression().toString(), expItem);
  152.  
  153. Alias alias = expItem.getAlias();
  154. if (alias != null) {
  155. aliases.add(alias.getName());
  156. }
  157.  
  158. } else if (item instanceof AllColumns) {
  159. allColumns = true;
  160.  
  161. } else if (item instanceof AllTableColumns) {
  162. allColumnsTables.add(((AllTableColumns) item).getTable().getName());
  163. }
  164. }
  165.  
  166. int aliasNo = 1;
  167. while (iterator.hasNext()) {
  168. orderByElement = iterator.next();
  169. Expression expression = orderByElement.getExpression();
  170. SelectExpressionItem selectExpressionItem = selectMap.get(expression.toString());
  171. if (selectExpressionItem != null) {
  172. Alias alias = selectExpressionItem.getAlias();
  173. if (alias != null) {
  174. iterator.set(cloneOrderByElement(orderByElement, alias.getName()));
  175.  
  176. } else {
  177. if (expression instanceof Column) {
  178. ((Column) expression).setTable(null);
  179.  
  180. } else {
  181. throw new RuntimeException("列 \"" + expression + "\" 需要定义别名");
  182. }
  183. }
  184.  
  185. } else {
  186. if (expression instanceof Column) {
  187. String table = ((Column) expression).getTable().getName();
  188. if (table != null) {
  189. if (allColumns || allColumnsTables.contains(table)) {
  190. ((Column) expression).setTable(null);
  191. continue;
  192. }
  193.  
  194. } else {
  195. if (allColumns ||
  196. (allColumnsTables.size() == 1 && plainSelect.getJoins() == null) ||
  197. aliases.contains(((Column) expression).getColumnName())) {
  198. continue;
  199. }
  200. }
  201. }
  202.  
  203. String aliasName = PAGE_COLUMN_ALIAS_PREFIX + aliasNo++;
  204.  
  205. SelectExpressionItem item = new SelectExpressionItem();
  206. item.setExpression(expression);
  207. item.setAlias(new Alias(aliasName));
  208. autoItems.add(item);
  209.  
  210. iterator.set(cloneOrderByElement(orderByElement, aliasName));
  211. }
  212. }
  213.  
  214. return orderByElements;
  215. }
  216. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement