1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package org.springframework.batch.item.database.support;
18
19 import java.util.Map;
20
21 import org.springframework.batch.item.database.Order;
22 import org.springframework.util.StringUtils;
23
24
25
26
27
28
29
30
31
32
33 public class SqlWindowingPagingQueryProvider extends AbstractSqlPagingQueryProvider {
34
35 @Override
36 public String generateFirstPageQuery(int pageSize) {
37 StringBuilder sql = new StringBuilder();
38 sql.append("SELECT * FROM ( ");
39 sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, ");
40 sql.append("ROW_NUMBER() OVER (").append(getOverClause());
41 sql.append(") AS ROW_NUMBER");
42 sql.append(getOverSubstituteClauseStart());
43 sql.append(" FROM ").append(getFromClause()).append(
44 getWhereClause() == null ? "" : " WHERE " + getWhereClause());
45 sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause());
46 sql.append(getOverSubstituteClauseEnd());
47 sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append(
48 "ROW_NUMBER <= ").append(pageSize);
49 sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
50
51 return sql.toString();
52 }
53
54 protected String getOrderedQueryAlias() {
55 return "";
56 }
57
58 protected Object getSubQueryAlias() {
59 return "AS TMP_SUB ";
60 }
61
62 protected Object extractTableAlias() {
63 String alias = "" + getSubQueryAlias();
64 if (StringUtils.hasText(alias) && alias.toUpperCase().startsWith("AS")) {
65 alias = alias.substring(3).trim() + ".";
66 }
67 return alias;
68 }
69
70 @Override
71 public String generateRemainingPagesQuery(int pageSize) {
72 StringBuilder sql = new StringBuilder();
73 sql.append("SELECT * FROM ( ");
74 sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, ");
75 sql.append("ROW_NUMBER() OVER (").append(getOverClause());
76 sql.append(") AS ROW_NUMBER");
77 sql.append(getOverSubstituteClauseStart());
78 sql.append(" FROM ").append(getFromClause());
79 if (getWhereClause() != null) {
80 sql.append(" WHERE ");
81 sql.append(getWhereClause());
82 }
83
84 sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause());
85 sql.append(getOverSubstituteClauseEnd());
86 sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append(
87 "ROW_NUMBER <= ").append(pageSize);
88 sql.append(" AND ");
89 SqlPagingQueryUtils.buildSortConditions(this, sql);
90 sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
91
92 return sql.toString();
93 }
94
95 @Override
96 public String generateJumpToItemQuery(int itemIndex, int pageSize) {
97 int page = itemIndex / pageSize;
98 int lastRowNum = (page * pageSize);
99 if (lastRowNum <= 0) {
100 lastRowNum = 1;
101 }
102
103 StringBuilder sql = new StringBuilder();
104 sql.append("SELECT ");
105 buildSortKeySelect(sql);
106 sql.append(" FROM ( ");
107 sql.append("SELECT ");
108 buildSortKeySelect(sql);
109 sql.append(", ROW_NUMBER() OVER (").append(getOverClause());
110 sql.append(") AS ROW_NUMBER");
111 sql.append(getOverSubstituteClauseStart());
112 sql.append(" FROM ").append(getFromClause());
113 sql.append(getWhereClause() == null ? "" : " WHERE " + getWhereClause());
114 sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause());
115 sql.append(getOverSubstituteClauseEnd());
116 sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append(
117 "ROW_NUMBER = ").append(lastRowNum);
118 sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
119
120 return sql.toString();
121 }
122
123 private void buildSortKeySelect(StringBuilder sql) {
124 String prefix = "";
125 for (Map.Entry<String, Order> sortKey : getSortKeys().entrySet()) {
126 sql.append(prefix);
127 prefix = ", ";
128 sql.append(sortKey.getKey());
129 }
130 }
131
132 protected String getOverClause() {
133 StringBuilder sql = new StringBuilder();
134
135 sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
136
137 return sql.toString();
138 }
139
140 protected String getOverSubstituteClauseStart() {
141 return "";
142 }
143
144 protected String getOverSubstituteClauseEnd() {
145 return "";
146 }
147 }