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