001
014
015 package com.liferay.portal.dao.orm.hibernate;
016
017 import com.liferay.portal.kernel.util.CharPool;
018 import com.liferay.portal.kernel.util.StringBundler;
019 import com.liferay.portal.kernel.util.StringPool;
020 import com.liferay.portal.kernel.util.StringUtil;
021 import com.liferay.portal.kernel.util.Validator;
022
023 import java.util.regex.Matcher;
024 import java.util.regex.Pattern;
025
026
030 public class SQLServerLimitStringUtil {
031
032 public static String getLimitString(String sql, int offset, int limit) {
033 String sqlLowerCase = sql.toLowerCase();
034
035 int fromPos = sqlLowerCase.indexOf(" from ");
036
037 String selectFrom = sql.substring(0, fromPos);
038
039 int orderByPos = sqlLowerCase.lastIndexOf(" order by ");
040
041 String selectFromWhere = null;
042
043 String orderBy = StringPool.BLANK;
044
045 if (orderByPos > 0) {
046 selectFromWhere = sql.substring(fromPos, orderByPos);
047
048 orderBy = sql.substring(orderByPos + 9);
049 }
050 else {
051 selectFromWhere = sql.substring(fromPos);
052 }
053
054 String[] splitOrderBy = _splitOrderBy(selectFrom, orderBy);
055
056 String innerOrderBy = splitOrderBy[0];
057 String outerOrderBy = splitOrderBy[1];
058
059 String[] splitSelectFrom = _splitSelectFrom(
060 selectFrom, innerOrderBy, limit);
061
062 String innerSelectFrom = splitSelectFrom[0];
063 String outerSelectFrom = splitSelectFrom[1];
064
065 StringBundler sb = new StringBundler(15);
066
067 sb.append(outerSelectFrom);
068 sb.append(" from (");
069 sb.append(outerSelectFrom);
070 sb.append(", row_number() over (");
071 sb.append(outerOrderBy);
072 sb.append(") as _page_row_num from (");
073 sb.append(innerSelectFrom);
074 sb.append(selectFromWhere);
075 sb.append(innerOrderBy);
076 sb.append(" ) _temp_table_1 ) _temp_table_2");
077 sb.append(" where _page_row_num between ");
078 sb.append(offset + 1);
079 sb.append(" and ");
080 sb.append(limit);
081 sb.append(" order by _page_row_num");
082
083 return sb.toString();
084 }
085
086 private static final String[] _splitOrderBy(
087 String selectFrom, String orderBy) {
088
089 StringBundler innerOrderBySB = new StringBundler();
090 StringBundler outerOrderBySB = new StringBundler();
091
092 String[] orderByColumns = StringUtil.split(orderBy, CharPool.COMMA);
093
094 for (String orderByColumn : orderByColumns) {
095 orderByColumn = orderByColumn.trim();
096
097 String orderByColumnName = orderByColumn;
098 String orderByType = "ASC";
099
100 int spacePos = orderByColumn.lastIndexOf(CharPool.SPACE);
101
102 if (spacePos != -1) {
103 int parenPos = orderByColumn.indexOf(
104 CharPool.OPEN_PARENTHESIS, spacePos);
105
106 if (parenPos == -1) {
107 orderByColumnName = orderByColumn.substring(0, spacePos);
108 orderByType = orderByColumn.substring(spacePos + 1);
109 }
110 }
111
112 String patternString = "\\Q".concat(orderByColumnName).concat(
113 "\\E as (\\w+)");
114
115 Pattern pattern = Pattern.compile(
116 patternString, Pattern.CASE_INSENSITIVE);
117
118 Matcher matcher = pattern.matcher(selectFrom);
119
120 if (matcher.find()) {
121 orderByColumnName = matcher.group(1);
122 }
123
124 if (selectFrom.contains(orderByColumnName)) {
125 if (outerOrderBySB.length() == 0) {
126 outerOrderBySB.append(" order by ");
127 }
128 else {
129 outerOrderBySB.append(StringPool.COMMA);
130 }
131
132 matcher = _qualifiedColumnPattern.matcher(orderByColumnName);
133
134 orderByColumnName = matcher.replaceAll("$1");
135
136 outerOrderBySB.append(orderByColumnName);
137 outerOrderBySB.append(StringPool.SPACE);
138 outerOrderBySB.append(orderByType);
139 }
140 else {
141 if (innerOrderBySB.length() == 0) {
142 innerOrderBySB.append(" order by ");
143 }
144 else {
145 innerOrderBySB.append(StringPool.COMMA);
146 }
147
148 innerOrderBySB.append(orderByColumnName);
149 innerOrderBySB.append(StringPool.SPACE);
150 innerOrderBySB.append(orderByType);
151 }
152 }
153
154 if (outerOrderBySB.length() == 0) {
155 outerOrderBySB.append(" order by CURRENT_TIMESTAMP");
156 }
157
158 return new String[] {
159 innerOrderBySB.toString(), outerOrderBySB.toString()
160 };
161 }
162
163 private static String[] _splitSelectFrom(
164 String selectFrom, String innerOrderBy, int limit) {
165
166 String innerSelectFrom = selectFrom;
167
168 if (Validator.isNotNull(innerOrderBy)) {
169 Matcher matcher = _selectPattern.matcher(innerSelectFrom);
170
171 innerSelectFrom = matcher.replaceAll(
172 "select top ".concat(String.valueOf(limit)).concat(
173 StringPool.SPACE));
174 }
175
176 String outerSelectFrom = selectFrom;
177
178 while (outerSelectFrom.charAt(0) == CharPool.OPEN_PARENTHESIS) {
179 outerSelectFrom = outerSelectFrom.substring(1);
180 }
181
182 Matcher matcher = _columnAliasPattern.matcher(outerSelectFrom);
183
184 outerSelectFrom = matcher.replaceAll("$1");
185
186 matcher = _distinctPattern.matcher(outerSelectFrom);
187
188 outerSelectFrom = matcher.replaceAll(StringPool.SPACE);
189
190 matcher = _qualifiedColumnPattern.matcher(outerSelectFrom);
191
192 outerSelectFrom = matcher.replaceAll("$1");
193
194 return new String[] {
195 innerSelectFrom, outerSelectFrom
196 };
197 }
198
199 private static Pattern _columnAliasPattern = Pattern.compile(
200 "[\\w\\.]+(?:\\(.+?\\))? AS (\\w+)", Pattern.CASE_INSENSITIVE);
201 private static Pattern _distinctPattern = Pattern.compile(
202 " DISTINCT ", Pattern.CASE_INSENSITIVE);
203 private static Pattern _qualifiedColumnPattern = Pattern.compile(
204 "\\w+\\.([\\w\\*]+)");
205 private static Pattern _selectPattern = Pattern.compile(
206 "SELECT ", Pattern.CASE_INSENSITIVE);
207
208 }