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 = StringUtil.toLowerCase(sql);
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 innerSelectFrom = _getInnerSelectFrom(
060 selectFrom, innerOrderBy, limit);
061
062 StringBundler sb = new StringBundler(12);
063
064 sb.append("select * from (select *, row_number() over (");
065 sb.append(outerOrderBy);
066 sb.append(") as _page_row_num from (");
067 sb.append(innerSelectFrom);
068 sb.append(selectFromWhere);
069 sb.append(innerOrderBy);
070 sb.append(" ) _temp_table_1 ) _temp_table_2 where _page_row_num ");
071 sb.append("between ");
072 sb.append(offset + 1);
073 sb.append(" and ");
074 sb.append(limit);
075 sb.append(" order by _page_row_num");
076
077 return sb.toString();
078 }
079
080 private static String _getInnerSelectFrom(
081 String selectFrom, String innerOrderBy, int limit) {
082
083 String innerSelectFrom = selectFrom;
084
085 if (Validator.isNotNull(innerOrderBy)) {
086 Matcher matcher = _selectPattern.matcher(innerSelectFrom);
087
088 innerSelectFrom = matcher.replaceAll(
089 "select top ".concat(String.valueOf(limit)).concat(
090 StringPool.SPACE));
091 }
092
093 return innerSelectFrom;
094 }
095
096 private static final String[] _splitOrderBy(
097 String selectFrom, String orderBy) {
098
099 StringBundler innerOrderBySB = new StringBundler();
100 StringBundler outerOrderBySB = new StringBundler();
101
102 String[] orderByColumns = StringUtil.split(orderBy, CharPool.COMMA);
103
104 for (String orderByColumn : orderByColumns) {
105 orderByColumn = orderByColumn.trim();
106
107 String orderByColumnName = orderByColumn;
108 String orderByType = "ASC";
109
110 int spacePos = orderByColumn.lastIndexOf(CharPool.SPACE);
111
112 if (spacePos != -1) {
113 int parenPos = orderByColumn.indexOf(
114 CharPool.OPEN_PARENTHESIS, spacePos);
115
116 if (parenPos == -1) {
117 orderByColumnName = orderByColumn.substring(0, spacePos);
118 orderByType = orderByColumn.substring(spacePos + 1);
119 }
120 }
121
122 String patternString = "\\Q".concat(orderByColumnName).concat(
123 "\\E as (\\w+)");
124
125 Pattern pattern = Pattern.compile(
126 patternString, Pattern.CASE_INSENSITIVE);
127
128 Matcher matcher = pattern.matcher(selectFrom);
129
130 if (matcher.find()) {
131 orderByColumnName = matcher.group(1);
132 }
133
134 if (selectFrom.contains(orderByColumnName)) {
135 if (outerOrderBySB.length() == 0) {
136 outerOrderBySB.append(" order by ");
137 }
138 else {
139 outerOrderBySB.append(StringPool.COMMA);
140 }
141
142 matcher = _qualifiedColumnPattern.matcher(orderByColumnName);
143
144 orderByColumnName = matcher.replaceAll("$1");
145
146 outerOrderBySB.append(orderByColumnName);
147 outerOrderBySB.append(StringPool.SPACE);
148 outerOrderBySB.append(orderByType);
149 }
150 else {
151 if (innerOrderBySB.length() == 0) {
152 innerOrderBySB.append(" order by ");
153 }
154 else {
155 innerOrderBySB.append(StringPool.COMMA);
156 }
157
158 innerOrderBySB.append(orderByColumnName);
159 innerOrderBySB.append(StringPool.SPACE);
160 innerOrderBySB.append(orderByType);
161 }
162 }
163
164 if (outerOrderBySB.length() == 0) {
165 outerOrderBySB.append(" order by CURRENT_TIMESTAMP");
166 }
167
168 return new String[] {
169 innerOrderBySB.toString(), outerOrderBySB.toString()
170 };
171 }
172
173 private static final Pattern _qualifiedColumnPattern = Pattern.compile(
174 "\\w+\\.([\\w\\*]+)");
175 private static final Pattern _selectPattern = Pattern.compile(
176 "SELECT ", Pattern.CASE_INSENSITIVE);
177
178 }